The database schema is the structure of a

database In computing, a database is an organized collection of data or a type of data store based on the use of a database management system (DBMS), the software that interacts with end users, applications, and the database itself to capture and a ...

described in a

formal language In logic, mathematics, computer science, and linguistics, a formal language is a set of strings whose symbols are taken from a set called "alphabet". The alphabet of a formal language consists of symbols that concatenate into strings (also c ...

supported typically by a

relational database management system A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970. A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...

(RDBMS). The term "

schema Schema may refer to: Science and technology * SCHEMA (bioinformatics), an algorithm used in protein engineering * Schema (genetic algorithms), a set of programs or bit strings that have some genotypic similarity * Schema.org, a web markup vocab ...

" refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of

relational database A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970. A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...

s). The formal definition of a

schema is a set of formulas (sentences) called integrity constraints 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. The states of a created conceptual schema 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 A database administrator (DBA) manages computer databases. The role may include capacity planning, installation, configuration, database design, migration, performance monitoring, security, troubleshooting, as well as backup and data re ...

'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 administrato ...

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 A mathematical object is an abstract concept arising in mathematics. Typically, a mathematical object can be a value that can be assigned to a Glossary of mathematical symbols, symbol, and therefore can be involved in formulas. Commonly encounter ...

. 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

, the schema defines the tables, fields, relationships, views,

index Index (: indexes or 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 the Halo Array in the ...

es, packages, procedures, functions, queues, triggers, 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 cal ...

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 summa ...

synonym A synonym is a word, morpheme, or phrase that means precisely 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 a ...

s, database links, directories,

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. These constrai ...

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 Corporation, ...

. 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 proprietary multi-model database management system produced and marketed by Oracle Corporation. It is a database commonly used for ru ...

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

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 * views *

s *

synonyms A synonym is a word, morpheme, or phrase that means precisely 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 a ...

indexes Index (: indexes or indices) may refer to: Arts, entertainment, and media Fictional entities * Index (A Certain Magical Index), Index (''A Certain Magical Index''), a character in the light novel series ''A Certain Magical Index'' * The Index, a ...

* clusters * database links * snapshots * 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 proprietary multi-model database management system produced and marketed by Oracle Corporation. It is a database commonly used for ru ...

s store schema objects logically within a tablespace of the database. The data of each object is physically contained in one or more of the tablespace's datafiles. For some objects (such as tables, indexes, and clusters) a

can specify how much disk space the Oracle RDBMS 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.

Microsoft SQL Server

Microsoft SQL Server Microsoft SQL Server is a proprietary relational database management system developed by Microsoft using Structured Query Language (SQL, often pronounced "sequel"). As a database server, it is a software product with the primary function of ...

, the default schema of every database is the dbo schema.

References

External links