![MediaWiki 1](https://upload.wikimedia.org/wikipedia/commons/9/94/MediaWiki_1.28.0_database_schema.svg)
The database schema is the structure of a
database
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases sp ...
described in a
formal language
In logic, mathematics, computer science, and linguistics, a formal language consists of words whose letters are taken from an alphabet and are well-formed according to a specific set of rules.
The alphabet of a formal language consists of symb ...
supported by the
database management system
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases span ...
(DBMS). The term "
schema
The word schema comes from the Greek word ('), which means ''shape'', or more generally, ''plan''. The plural is ('). In English, both ''schemas'' and ''schemata'' are used as plural forms.
Schema may refer to:
Science and technology
* SCHEMA ...
" refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of
relational databases
A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relation ...
). The formal definition of a
database
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases sp ...
schema is a set of formulas (sentences) called
integrity constraints
Data integrity is the maintenance of, and the assurance of, data accuracy and consistency over its entire life-cycle and is a critical aspect to the design, implementation, and usage of any system that stores, processes, or retrieves data. The ter ...
imposed on a database. These integrity constraints ensure compatibility between parts of the schema. All constraints are expressible in the same language. A database can be considered a structure in realization of the
database language
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases span ...
.
The states of a created
conceptual schema
A conceptual schema is a high-level description of informational needs underlying the design of a database. It typically includes only the main concepts and the main relationships among them. Typically this is a first-cut model, with insufficient ...
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 administrators (DBAs) use specialized software to store and organize data. The role may include capacity planning, installation, configuration, database design, migration, performance monitoring, security, troubleshooting, as well as back ...
's knowledge of possible applications, the facts that can enter the database, or those of interest to the possible
end-user
In product development, an end user (sometimes end-user) is a person who ultimately uses or is intended to ultimately use a product. The end user stands in contrast to users who support or maintain the product, such as sysops, system administrat ...
s."
The notion of a database schema plays the same role as the notion of theory in
predicate calculus
Predicate or predication may refer to:
* Predicate (grammar), in linguistics
* Predication (philosophy)
* several closely related uses in mathematics and formal logic:
**Predicate (mathematical logic)
**Propositional function
**Finitary relation, o ...
. A model of this "theory" closely corresponds to a database, which can be seen at any instant of time as a
mathematical object
A mathematical object is an abstract concept arising in mathematics.
In the usual language of mathematics, an ''object'' is anything that has been (or could be) formally defined, and with which one may do deductive reasoning and mathematical pr ...
. Thus a schema can contain formulas representing
integrity constraints
Data integrity is the maintenance of, and the assurance of, data accuracy and consistency over its entire life-cycle and is a critical aspect to the design, implementation, and usage of any system that stores, processes, or retrieves data. The ter ...
specifically for an application and the constraints specifically for a type of database, all expressed in the same database language.
In a
relational database
A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relatio ...
, the schema defines the
tables
Table may refer to:
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (landform), a flat area of land
* Table (information), a data arrangement with rows and columns
* Table (database), how the table data ...
,
fields
Fields may refer to:
Music
*Fields (band), an indie rock band formed in 2006
*Fields (progressive rock band), a progressive rock band formed in 1971
* ''Fields'' (album), an LP by Swedish-based indie rock band Junip (2010)
* "Fields", a song by ...
,
relationships,
views,
index
Index (or its plural form indices) may refer to:
Arts, entertainment, and media Fictional entities
* Index (''A Certain Magical Index''), a character in the light novel series ''A Certain Magical Index''
* The Index, an item on a Halo megastru ...
es,
packages,
procedures,
function
Function or functionality may refer to:
Computing
* Function key, a type of key on computer keyboards
* Function model, a structured representation of processes in a system
* Function object or functor or functionoid, a concept of object-oriente ...
s,
queue __NOTOC__
Queue () may refer to:
* Queue area, or queue, a line or area where people wait for goods or services
Arts, entertainment, and media
*''ACM Queue'', a computer magazine
* The Queue (Sorokin novel), ''The Queue'' (Sorokin novel), a 198 ...
s,
trigger
Trigger may refer to:
Notable animals and people
;Mononym
* Trigger (horse), owned by cowboy star Roy Rogers
;Nickname
* Trigger Alpert (1916–2013), American jazz bassist
* "Trigger Mike" Coppola (1900–1966), American gangster
;Surname
* Bru ...
s,
types,
sequence
In mathematics, a sequence is an enumerated collection of objects in which repetitions are allowed and order matters. Like a set, it contains members (also called ''elements'', or ''terms''). The number of elements (possibly infinite) is calle ...
s,
materialized view
In computing, a materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary ...
s,
synonym
A synonym is a word, morpheme, or phrase that means exactly or nearly the same as another word, morpheme, or phrase in a given language. For example, in the English language, the words ''begin'', ''start'', ''commence'', and ''initiate'' are all ...
s, database links,
directories
Directory may refer to:
* Directory (computing), or folder, a file system structure in which to store computer files
* Directory (OpenVMS command)
* Directory service, a software application for organizing information about a computer network's u ...
,
XML schema
An XML schema is a description of a type of Extensible Markup Language, 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 ...
s, and other elements.
A database generally stores its schema in a
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". ''Oracle'' defines it ...
. 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 (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a multi-model database management system produced and marketed by Oracle Corporation.
It is a database commonly used for running online t ...
system, the term "schema" has a slightly different connotation.
Ideal requirements for schema integration
The requirements listed below influence the detailed structure of schemas that are produced. Certain applications will not require that all of these conditions are met, but these four requirements are the most ideal.
; 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.
Example of two schema integrations
Suppose we want a mediated schema to
integrate two travel databases, Go-travel and Ok-flight.
Go-travel
has two relations:
Go-flight(flight-number, time, meal(yes/no))
Go-price(flight-number, date, price)
Ok-flight
has just one relation:
Ok-flight(flight-number, date, time, price, nonstop(yes/no))
The overlapping information in Go-travel’s and Ok-flight’s schemas could be represented in a mediated schema:
Flight(flight-number, date, time, price)
Oracle database specificity
In the context of
Oracle Database
Oracle Database (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a multi-model database management system produced and marketed by Oracle Corporation.
It is a database commonly used for running online t ...
s, a schema object is a logical
data storage structure.
An Oracle database associates a separate schema with each database user.
A schema comprises a collection of schema objects. Examples of schema objects include:
*
tables
Table may refer to:
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (landform), a flat area of land
* Table (information), a data arrangement with rows and columns
* Table (database), how the table data ...
*
views
A view is a sight or prospect or the ability to see or be seen from a particular place.
View, views or Views may also refer to:
Common meanings
* View (Buddhism), a charged interpretation of experience which intensely shapes and affects thou ...
*
sequence
In mathematics, a sequence is an enumerated collection of objects in which repetitions are allowed and order matters. Like a set, it contains members (also called ''elements'', or ''terms''). The number of elements (possibly infinite) is calle ...
s
*
synonyms
A synonym is a word, morpheme, or phrase that means exactly or nearly the same as another word, morpheme, or phrase in a given language. For example, in the English language, the words ''begin'', ''start'', ''commence'', and ''initiate'' are all ...
*
indexes
Index (or its plural form indices) may refer to:
Arts, entertainment, and media Fictional entities
* Index (''A Certain Magical Index''), a character in the light novel series ''A Certain Magical Index''
* The Index, an item on a Halo megastru ...
* clusters
* database links
*
snapshot
Snapshot, snapshots or snap shot may refer to:
* Snapshot (photography), a photograph taken without preparation
Computing
* Snapshot (computer storage), the state of a system at a particular point in time
* Snapshot (file format) or SNP, a file ...
s
*
procedures
* functions
* packages
On the other hand, non-schema objects may include:
* users
* roles
* contexts
* directory objects
Schema objects do not have a one-to-one correspondence to physical files on disk that store their information. However,
Oracle database
Oracle Database (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a multi-model database management system produced and marketed by Oracle Corporation.
It is a database commonly used for running online t ...
s store schema objects logically within a
tablespace
A tablespace is a storage location where the actual data underlying database objects can be kept. It provides a layer of abstraction between physical and logical data,
and serves to allocate storage for all DBMS managed segments. (A database se ...
of the database. The data of each object is physically contained in one or more of the tablespace's
datafile
A data file is a computer file which stores data to be used by a computer application or system, including input and output data. A data file usually does not contain instructions or code to be executed (that is, a computer program).
Most of the ...
s. For some objects (such as tables, indexes, and clusters) a
database administrator
Database administrators (DBAs) use specialized software to store and organize data. The role may include capacity planning, installation, configuration, database design, migration, performance monitoring, security, troubleshooting, as well as back ...
can specify how much disk space the Oracle
RDBMS
A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relation ...
allocates for the object within the tablespace's datafiles.
There is no necessary relationship between schemas and tablespaces: a tablespace can contain objects from different schemas, and the objects for a single schema can reside in different tablespaces. Oracle database specificity does, however, enforce platform recognition of nonhomogenized sequence differentials, which is considered a crucial limiting factor in virtualized applications.
See also
*
Data element
In metadata, the term data element is an atomic unit of data that has precise meaning or precise semantics. A data element has:
# An identification such as a data element name
# A clear data element definition
# One or more representation terms
# ...
*
Data mapping
In computing and data management, 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:
* Data transformatio ...
*
Data model
A data model is an abstract model that organizes elements of data and standardizes how they relate to one another and to the properties of real-world entities. For instance, a data model may specify that the data element representing a car be co ...
*
Database design
Database design is the organization of data according to a database model. The designer determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model.Teorey, ...
*
Entity–relationship model
An entity–relationship model (or ER model) describes interrelated things of interest in a specific domain of knowledge. A basic ER model is composed of entity types (which classify the things of interest) and specifies relationships that can ex ...
*
Knowledge representation and reasoning
Knowledge representation and reasoning (KRR, KR&R, KR²) is the field of artificial intelligence (AI) dedicated to representing information about the world in a form that a computer system can use to solve complex tasks such as Computer-aided diag ...
*
Object-role modeling
Object-role modeling (ORM) is used to model the semantics of a universe of discourse. ORM is often used for data modeling and software engineering.
An object-role model uses graphical symbols that are based on first order predicate logic and se ...
*
Olog
The theory of ologs is an attempt to provide a rigorous mathematical framework for knowledge representation, construction of scientific models and data storage using category theory, linguistic and graphical tools. Ologs were introduced in 2010 ...
*
Schema matching
The terms schema matching and '' mapping'' are often used interchangeably for a database process. For this article, we differentiate the two as follows: Schema matching is the process of identifying that two objects are semantically related (scope ...
*
Three-schema approach
The three-schema approach, or three-schema concept, in software engineering is an approach to building information systems and systems information management that originated in the 1970s. It proposes three different views in systems development, wi ...
References
External links
Tip/Trick: Online Database Schema Samples LibraryDatabase Schema SamplesDesigning the Star Schema Database
{{DEFAULTSORT:Database Schema
Data management
Data modeling