A database schema of a database system

Database system

A database system is a term that is typically used to encapsulate the constructs of a data model, database Management system and database....

 is its structure described in a formal language

Formal language

A formal language is a set of words—that is, finite strings of letters, symbols, or tokens that are defined in the language. The set from which these letters are taken is the alphabet over which the language is defined. A formal language is often defined by means of a formal grammar...

 supported by the database management system

Database management system

A database management system is a software package with computer programs that control the creation, maintenance, and use of a database. It allows organizations to conveniently develop databases for various applications by database administrators and other specialists. A database is an integrated...

 (DBMS) and refers to the organization of data to create a blueprint of how a database will be constructed (divided into database tables). The formal definition of database

Database

A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...

 schema is a set of formulas (sentences) that specify integrity constraints

Integrity constraints

Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity...

 imposed on the database. All constraints are expressible in the same language. A database can be seen in terms of logic as a structure in a realization of the database language. The states of a created conceptual schema

Conceptual schema

A conceptual schema or conceptual data model is a map of concepts and their relationships. This describes the semantics of an organization and represents a series of assertions about its nature...

 are transformed into an explicit mapping, the database schema. This describes how real world entities are modeled in the database.


"A database schema specifies, based on the database administrator

Database administrator

A database administrator is a person responsible for the design, implementation, maintenance and repair of an organization's database. They are also known by the titles Database Coordinator or Database Programmer, and is closely related to the Database Analyst, Database Modeller, Programmer...

's knowledge of possible applications, the facts that can enter the database, or those of interest to the possible end-user

End-user

Economics and commerce define an end user as the person who uses a product. The end user or consumer may differ from the person who purchases the product...

s." The notion of a database schema plays the same role as the notion of theory in predicate calculus. A model of this “theory” closely corresponds to a database, which can be seen at any instant of time as a mathematical object

Mathematical object

In mathematics and the philosophy of mathematics, a mathematical object is an abstract object arising in mathematics.Commonly encountered mathematical objects include numbers, permutations, partitions, matrices, sets, functions, and relations...

. Thus a schema can contain formulas representing integrity constraints specifically for an application and the constraints specifically for a type of database, all expressed in the same database language. In a relational database

Relational database

A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...

, the schema defines the tables

Table (database)

In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...

, fields

Field (computer science)

In computer science, data that has several parts can be divided into fields. Relational databases arrange data as sets of database records, also called rows. Each record consists of several fields; the fields of all records form the columns....

, relationship

Relational model

The relational model for database management is a database model based on first-order predicate logic, first formulated and proposed in 1969 by Edgar F...

s, view

View (database)

In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions...

s, index

Index (database)

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space...

es, package

Software package (installation)

In package management systems, which are commonly used with Linux-based operating systems, a package is a specific piece of software which the system can install and uninstall....

s, procedure

Stored procedure

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary.Typical uses for stored procedures include data validation or access control mechanisms...

s, function

Subroutine

In computer science, a subroutine is a portion of code within a larger program that performs a specific task and is relatively independent of the remaining code....

s, queues, trigger

Database trigger

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database...

s, type

Data type

In computer programming, a data type is a classification identifying one of various types of data, such as floating-point, integer, or Boolean, that determines the possible values for that type; the operations that can be done on values of that type; the meaning of the data; and the way values of...

s, sequence

Sequence

In mathematics, a sequence is an ordered list of objects . Like a set, it contains members , and the number of terms is called the length of the sequence. Unlike a set, order matters, and exactly the same elements can appear multiple times at different positions in the sequence...

s, materialized view

Materialized view

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables, are also known as snapshots...

s, synonym

Synonym (database)

A Synonym is an alias or alternate name for a table, view, Sequence, or other Schema object. They are used mainly to make it easy for users to access database objects owned by other users. They hide the underlying object's identity and make it harder for a malicious program or user to target the...

s, database links, directories

Directory (file systems)

In computing, a folder, directory, catalog, or drawer, is a virtual container originally derived from an earlier Object-oriented programming concept by the same name within a digital file system, in which groups of computer files and other folders can be kept and organized.A typical file system may...

, Java

Java

Java is an island of Indonesia. With a population of 135 million , it is the world's most populous island, and one of the most densely populated regions in the world. It is home to 60% of Indonesia's population. The Indonesian capital city, Jakarta, is in west Java...

, XML schema

XML schema

An XML schema is a description of a type of XML document, typically expressed in terms of constraints on the structure and content of documents of that type, above and beyond the basic syntactical constraints imposed by XML itself...

s, and other elements.

Schemata are generally stored in a data dictionary

Data dictionary

A data dictionary, or metadata repository, as defined in the IBM Dictionary of Computing, is a "centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format." The term may have one of several closely related meanings pertaining to...

. Although a schema is defined in text database language, the term is often used to refer to a graphical depiction of the database structure. In other words, schema is the structure of the database that defines the objects in the database.

In an Oracle Database

Oracle Database

The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....

 system, the term "schema" has a slightly different connotation. For the interpretation used in an Oracle Database, see schema object

Schema object

In database lore, a schema object is a logical data storage structure. This possibly originates from the use of the term in the context of Oracle databases. The term "schema" can have other meanings when talking about non-Oracle databases. This article discusses the Oracle use of the term...

.

Levels of database schema

• Conceptual schema
  Conceptual schema
  A conceptual schema or conceptual data model is a map of concepts and their relationships. This describes the semantics of an organization and represents a series of assertions about its nature...
  
  , a map of concepts and their relationships.
• Logical schema
  Logical schema
  A Logical Schema is a data model of a specific problem domain expressed in terms of a particular data management technology. Without being specific to a particular database management product, it is in terms of either relational tables and columns, object-oriented classes, or XML tags...
  
  , a map of entities and their attributes and relations
• Physical schema
  Physical schema
  Describes how data are to be represented and stored in secondary storage using a particular DBMS .Physical Schema is a term used in relation to data management....
  
  , a particular implementation of a logical schema
• Schema object
  Schema object
  In database lore, a schema object is a logical data storage structure. This possibly originates from the use of the term in the context of Oracle databases. The term "schema" can have other meanings when talking about non-Oracle databases. This article discusses the Oracle use of the term...
  
  , Oracle database object

Overlap preservation

Each of the overlapping elements specified in the input mapping is also in a database schema relation.

Extended overlap preservation

Source-specific elements that are associated with a source’s overlapping elements are passed through to the database schema.

Normalization

Independent entities and relationships in the source data should not be grouped together in the same relation in the database schema. In particular, source specific schema elements should not be grouped with overlapping schema elements, if the grouping co-locates independent entities or relationships.

Minimality

If any elements of the database schema are dropped then the database schema is not ideal.

These requirements influence the detailed structure of schemas that are produced. Certain applications will not require that all of these conditions are met, but these five requirements are the most ideal.

Example of two schema integrations

Example: Suppose we want a mediated (database) schema to integrate two travel databases, Go-travel and Ok-travel.

Go-travel has three relations:

Go-flight(f-num, time, meal)

Go-price(f-num, date, price)

Go-airline(airline, phone)

The attribute f-num is the flight number and meal is a boolean. The other attributes are self-explanatory.

Ok-travel has just one relation:

Ok-flight(f-num, date, time, price, nonstop)

'nonstop' is a boolean.

The overlapping information in Ok-travel’s and Go-travel’s schemas could be represented in a mediated schema:

Flight(f-num, date, time, price)

See also

• Core Architecture Data Model
  Core Architecture Data Model
  Core architecture data model in enterprise architecture is a logical data model of information used to describe and build architectures....
  
  
• Data Definition Language
  Data Definition Language
  A data definition language or data description language is a syntax similar to a computer programming language for defining data structures, especially database schemas.-History:...
  
  
• Data Design
• Data dictionary
  Data dictionary
  A data dictionary, or metadata repository, as defined in the IBM Dictionary of Computing, is a "centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format." The term may have one of several closely related meanings pertaining to...
  
  
• Data model
  Data model
  A data model in software engineering is an abstract model, that documents and organizes the business data for communication between team members and is used as a plan for developing applications, specifically how data is stored and accessed....
  
  
• Data modeling
  Data modeling
  Data modeling in software engineering is the process of creating a data model for an information system by applying formal data modeling techniques.- Overview :...
  
  
• Data mapping
  Data mapping
  Data mapping is the process of creating data element mappings between two distinct data models. Data mapping is used as a first step for a wide variety of data integration tasks including:...
  
  
• Database
  Database
  A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
  
  
• Database integrity
  Database integrity
  Database integrity ensures that data entered into the database is accurate, valid, and consistent. Any applicable integrity constraints and data validation rules must be satisfied before permitting a change to the database....
  
  
• entity-relationship model
  Entity-relationship model
  In software engineering, an entity-relationship model is an abstract and conceptual representation of data. Entity-relationship modeling is a database modeling method, used to produce a type of conceptual schema or semantic data model of a system, often a relational database, and its requirements...
  
  
• Knowledge Representation
  Knowledge representation
  Knowledge representation is an area of artificial intelligence research aimed at representing knowledge in symbols to facilitate inferencing from those knowledge elements, creating new elements of knowledge...
  
  
• Object-Role Modeling
• Relational algebra
  Relational algebra
  Relational algebra, an offshoot of first-order logic , deals with a set of finitary relations that is closed under certain operators. These operators operate on one or more relations to yield a relation...
  
  
• Schema Matching
  Schema matching
  The terms schema matching and mapping are often used interchangeably. For this article, we differentiate the two as follows: Schema matching is the process of identifying that two objects are semantically related while mapping refers to the transformations between the objects...
  
  
• Schemelessness
• SQL
  SQL
  SQL is a programming language designed for managing data in relational database management systems ....
  
  
• Three schema approach
  Three schema approach
  The three-schema approach, or the Three Schema Concept, in software engineering is an approach to building information systems and systems information management from the 1970s...
  
  

External links

• http://www.databaseanswers.org/data_models/
• http://weblogs.asp.net/scottgu/archive/2006/07/12/Tip_2F00_Trick_3A00_-Online-Database-Schema-Samples-Library.aspx
• http://msdn.microsoft.com/en-us/library/bb187299%28SQL.80%29.aspx
• http://www.ciobriefings.com/Publications/WhitePapers/DesigningtheStarSchemaDatabase/tabid/101/Default.aspx