Editing Database schema

{{Short description|Visual representation of database system relationships}}
{{Use mdy dates|date=January 2019}}
{{Use American English|date=January 2019}}
[[File:MediaWiki 1.28.0 database schema.svg|thumb|upright=1.32|[[MediaWiki]] 1.28.0 database schema. Many [[FOSS]] software tools allow modelling of DB layout/schemes like this. Visual representation often may also be exported as a production-ready source code made in DB-compatible languages like [[SQL]].]]
The '''database schema''' is the structure of a [[database]] described in a [[formal language]] supported typically by a [[relational database management system]] (RDBMS). The term "[[wikt:schema|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 database]]s). The formal definition of a [[database]] schema is a set of formulas (sentences) called [[integrity constraints]] imposed on a database.{{citation needed|date=January 2016}} 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]].<ref name="source1" /> The states of a created [[conceptual schema]] are transformed into an explicit mapping, the database schema. This describes how real-world entities are [[Data modeling|modeled]] in the database.

"A database schema specifies, based on the [[database administrator]]'s knowledge of possible applications, the facts that can enter the database, or those of interest to the possible [[end-user]]s."<ref name="source3"/> 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]]. Thus a schema can contain formulas representing [[Data integrity#Types of integrity constraints|integrity constraints]] specifically for an application and the constraints specifically for a type of database, all expressed in the same database language.<ref name="source1" /> In a [[relational database]], the schema defines the [[Table (database)|tables]], [[Field (computer science)|fields]], [[Relational model|relationship]]s, [[View (database)|view]]s, [[Index (database)|index]]es, [[Software package (installation)|package]]s, [[stored procedure|procedure]]s, [[subroutine|function]]s, [[Queue (data structure)|queue]]s, [[Database trigger|trigger]]s, [[Data type|type]]s, [[sequence]]s, [[materialized view]]s, [[Synonym (database)|synonym]]s, database links, [[Directory (file systems)|directories]], [[XML schema]]s, and other elements.

A database generally stores its schema in a [[data dictionary]]. 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]] 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.<ref name="source2" />

; Extended overlap preservation
: Source-specific elements that are associated with a source’s overlapping elements are passed through to the database schema.<ref name="source2" />

; 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.<ref name="source2" />

; Minimality
: If any elements of the database schema are dropped then the database schema is not ideal.<ref name="source2" />

==Example of two schema integrations==
Suppose we want a mediated schema to [[data integration|integrate]] two travel databases, Go-travel and Ok-flight.

'''<code>Go-travel</code>''' has two relations:
<syntaxhighlight lang="text">
Go-flight(flight-number, time, meal(yes/no))
Go-price(flight-number, date, price)
</syntaxhighlight>

'''<code>Ok-flight</code>''' has just one relation:
<syntaxhighlight lang="text">
Ok-flight(flight-number, date, time, price, nonstop(yes/no))
</syntaxhighlight>

The overlapping information in Go-travel’s and Ok-flight’s schemas could be represented in a mediated schema:<ref name="source2" />
<syntaxhighlight lang="text">
Flight(flight-number, date, time, price)
</syntaxhighlight>

==Oracle database specificity==
In the context of [[Oracle Database]]s, a '''schema object''' is a logical data storage structure.<ref>{{cite book
 |first1       = Lance
 |last1        = Ashdown
 |first2       = Tom
 |last2        = Kyte
 |others       = ''et al''.
 |title        = Oracle Database Concepts 11g Release 2 (11.2)
 |url          = http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/tablecls.htm#CNCPT111
 |access-date   = 2010-04-14
 |date         = February 2010
 |publisher    = Oracle Corporation
 |quote        = A database schema is a logical container for data structures, called schema objects. Examples of schema objects are tables and indexes.
 |archive-url  = https://web.archive.org/web/20100129054802/http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/tablecls.htm#CNCPT111
 |archive-date = January 29, 2010
 |url-status     = dead
}}</ref>

An Oracle database associates a separate schema with each database '''user'''.<ref>
{{cite book
|title= Oracle Database Concepts 10g Release 2 (10.2)Part Number B14220-02 
|url= http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm
|access-date= 2012-11-26
|quote= A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL. 
}}</ref>
A schema comprises a collection of schema objects. Examples of schema objects include:

* [[Table (database)|tables]]
* [[View (database)|views]]
* [[sequence]]s
* [[Synonym (database)|synonyms]]
* [[Index (database)|indexes]]
* clusters
* database links
* [[Snapshot (computer storage)|snapshot]]s
* [[stored procedure|procedure]]s
* functions
* packages

On the other hand, non-schema objects may include:<ref>{{cite book
|first1= Lance
|last1= Ashdown
|first2= Tom
|last2= Kyte
|others= et al.
|title= Oracle Database Concepts 11g Release 2 (11.2)
|url= http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/tablecls.htm#CNCPT111
|access-date= 2010-04-14
|date= February 2010
|publisher= Oracle Corporation
|quote= Other types of objects are also stored in the database and can be created and manipulated with SQL statements but are not contained in a schema. These objects include database users, roles, contexts, and directory objects.
|archive-url= https://web.archive.org/web/20100129054802/http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/tablecls.htm#CNCPT111
|archive-date= January 29, 2010
|url-status= dead
}}</ref>

* 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]]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 [[datafile]]s. For some objects (such as tables, indexes, and clusters) a [[database administrator]] 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.<ref>{{cite journal |last1=McDougall |first1=R |title=Virtualization performance: perspectives and challenges ahead |journal=ACM SIGOPS Operating Systems Review |date=2010 |volume=44 |issue=4|doi=10.1145/1899928.1899933 |s2cid=16112550 }}</ref>

== Microsoft SQL Server ==
In [[Microsoft SQL Server]], the default schema of every database is the dbo schema.<ref>{{Cite web |last=VanMSFT |date=2024-05-09 |title=Ownership and user-schema separation in SQL Server - SQL Server |url=https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/ownership-and-user-schema-separation?view=sql-server-ver16 |access-date=2024-10-31 |website=learn.microsoft.com |language=en-us}}</ref>

== See also ==
* [[Data element]]
* [[Data mapping]]
* [[Data model]] 
* [[Database design]]
* [[Database model]]
* [[Entity–relationship model]]
* [[Knowledge representation and reasoning]]
* [[Object-role modeling]]
* [[Olog]]
* [[Schema matching]]
* [[Three-schema approach]]

==References==
{{reflist|refs=
<ref name="source1">{{cite journal |last=Rybinski |first=H. |year=1987|title=On First-Order-Logic Databases |journal=ACM Transactions on Database Systems |volume=12 |issue=3 |pages=325–349 |doi= 10.1145/27629.27630|s2cid=2439329 |doi-access=free }}</ref>
<ref name="source2">{{cite book |last1= Pottinger |first1=P. |last2=Berstein |first2=P. |title=Proceedings of the 11th international conference on Extending database technology: Advances in database technology |chapter=Schema merging and mapping creation for relational sources |year=2008 |publisher=ACM |location= New York, NY |pages=73–84 |doi= 10.1145/1353343.1353357|isbn=9781595939265 |citeseerx=10.1.1.405.2990 |s2cid=15742995 }}</ref>
<ref name="source3">{{cite book |last1= Imielinski |first1=T. | author-link= Tomasz Imieliński | last2=Lipski |first2=W. |title=Proceedings of the 1982 ACM SIGMOD international conference on Management of data - SIGMOD '82 |chapter=A systematic approach to relational database theory |year=1982 |publisher=ACM |location= New York, NY |pages=8–14 |doi= 10.1145/582353.582356|isbn=978-0897910736 |s2cid=2034345 }}</ref>
}}

== External links ==
* [https://weblogs.asp.net/scottgu/Tip_2F00_Trick_3A00_-Online-Database-Schema-Samples-Library Tip/Trick: Online Database Schema Samples Library]
* [https://web.archive.org/web/20081217074637/http://msdn.microsoft.com/en-us/library/bb187299%28SQL.80%29.aspx Database Schema Samples]
* [https://web.archive.org/web/20080828210315/http://ciobriefings.com/Publications/WhitePapers/DesigningtheStarSchemaDatabase/tabid/101/Default.aspx Designing the Star Schema Database]

{{Database}}

{{DEFAULTSORT:Database Schema}}
[[Category:Data management]]
[[Category:Relational model]]
[[Category:Data modeling]]