Database schema

<h2 id="ideal-requirements-for-schema-integration">Ideal requirements for schema integration</h2>
<p>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.
</p>
Overlap preservation
Each of the overlapping elements specified in the input mapping is also in a database schema relation.<a class="footnote-ref" id="fnref:4" href="#fn:4"><sup>4</sup></a>
Extended overlap preservation
Source-specific elements that are associated with a source’s overlapping elements are passed through to the database schema.<a class="footnote-ref" id="fnref:5" href="#fn:5"><sup>5</sup></a>
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.<a class="footnote-ref" id="fnref:6" href="#fn:6"><sup>6</sup></a>
Minimality
If any elements of the database schema are dropped then the database schema is not ideal.<a class="footnote-ref" id="fnref:7" href="#fn:7"><sup>7</sup></a>
<h2 id="example-of-two-schema-integrations">Example of two schema integrations</h2>
<p>Suppose we want a mediated schema to <a href="/facts/Data_integration/BU47JR28">integrate</a> two travel databases, Go-travel and Ok-flight.
</p><p>Go-travel has two relations:
</p>
Go-flight(flight-number, time, meal(yes/no))
Go-price(flight-number, date, price)

<p>Ok-flight has just one relation:
</p>
Ok-flight(flight-number, date, time, price, nonstop(yes/no))

<p>The overlapping information in Go-travel’s and Ok-flight’s schemas could be represented in a mediated schema:<a class="footnote-ref" id="fnref:8" href="#fn:8"><sup>8</sup></a>
</p>
Flight(flight-number, date, time, price)

<h2 id="oracle-database-specificity">Oracle database specificity</h2>
<p>In the context of <a href="/facts/Oracle_Database/FHv5g732">Oracle Databases</a>, a schema object is a logical <a href="/facts/Database_storage_structures/UxsT0h7u">data storage structure</a>.<a class="footnote-ref" id="fnref:9" href="#fn:9"><sup>9</sup></a>
</p><p>An Oracle database associates a separate schema with each database user.<a class="footnote-ref" id="fnref:10" href="#fn:10"><sup>10</sup></a>
A schema comprises a collection of schema objects. Examples of schema objects include:
</p>
<ul><li><a href="/facts/Table_(database)/5INvK1Vd">tables</a></li>
<li><a href="/facts/View_(database)/6B00yj6A">views</a></li>
<li><a href="/facts/Sequence/gV2S4uqp">sequences</a></li>
<li><a href="/facts/Synonym_(database)/g7F5vukm">synonyms</a></li>
<li><a href="/facts/Index_(database)/TRqmk9It">indexes</a></li>
<li>clusters</li>
<li>database links</li>
<li><a href="/facts/Snapshot_(computer_storage)/LKm3NX2A">snapshots</a></li>
<li><a href="/facts/Stored_procedure/50xRehaI">procedures</a></li>
<li>functions</li>
<li>packages</li></ul>
<p>On the other hand, non-schema objects may include:<a class="footnote-ref" id="fnref:11" href="#fn:11"><sup>11</sup></a>
</p>
<ul><li>users</li>
<li>roles</li>
<li>contexts</li>
<li>directory objects</li></ul>
<p>Schema objects do not have a one-to-one correspondence to physical files on disk that store their information. However, <a href="/facts/Oracle_database/FHv5g732">Oracle databases</a> store schema objects logically within a <a href="/facts/Tablespace/Jexvom3K">tablespace</a> of the database. The data of each object is physically contained in one or more of the tablespace's <a href="/facts/Datafile/S9oFvf2z">datafiles</a>. For some objects (such as tables, indexes, and clusters) a <a href="/facts/Database_administrator/3SJoq4ma">database administrator</a> can specify how much disk space the Oracle <a href="/facts/RDBMS/pEKGmJLy">RDBMS</a> allocates for the object within the tablespace's datafiles.
</p><p>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.<a class="footnote-ref" id="fnref:12" href="#fn:12"><sup>12</sup></a>
</p>
<h2 id="microsoft-sql-server">Microsoft SQL Server</h2>
<p>In <a href="/facts/Microsoft_SQL_Server/eCdt2JBL">Microsoft SQL Server</a>, the default schema of every database is the dbo schema.<a class="footnote-ref" id="fnref:13" href="#fn:13"><sup>13</sup></a>
</p>
<h2 id="see-also">See also</h2>
<ul><li><a href="/facts/Data_element/jtyoAIAd">Data element</a></li>
<li><a href="/facts/Data_mapping/1kTMajeS">Data mapping</a></li>
<li><a href="/facts/Data_model/j1YyzpJ3">Data model</a></li>
<li><a href="/facts/Database_design/Hdb9LRFX">Database design</a></li>
<li><a href="/facts/Database_model/1okm9rHu">Database model</a></li>
<li><a href="/facts/Entity%E2%80%93relationship_model/U8A0mwYe">Entity–relationship model</a></li>
<li><a href="/facts/Knowledge_representation_and_reasoning/9lnzyILD">Knowledge representation and reasoning</a></li>
<li><a href="/facts/Object-role_modeling/eUsGGWKF">Object-role modeling</a></li>
<li><a href="/facts/Olog/PQnhm3rp">Olog</a></li>
<li><a href="/facts/Schema_matching/BfE0ESmb">Schema matching</a></li>
<li><a href="/facts/Three-schema_approach/Wkma7WW9">Three-schema approach</a></li></ul>

<h2 id="external-links">External links</h2>
<ul><li><a href="https://weblogs.asp.net/scottgu/Tip_2F00_Trick_3A00_-Online-Database-Schema-Samples-Library">Tip/Trick: Online Database Schema Samples Library</a></li>
<li><a href="https://web.archive.org/web/20081217074637/http://msdn.microsoft.com/en-us/library/bb187299%28SQL.80%29.aspx">Database Schema Samples</a></li>
<li><a href="https://web.archive.org/web/20080828210315/http://ciobriefings.com/Publications/WhitePapers/DesigningtheStarSchemaDatabase/tabid/101/Default.aspx">Designing the Star Schema Database</a></li></ul>

<h2 id="references">References</h2>

<ol>
<li id="fn:1"><p>Rybinski, H. (1987). "On First-Order-Logic Databases". ACM Transactions on Database Systems. 12 (3): 325–349. doi:10.1145/27629.27630. S2CID 2439329. <a href="https://doi.org/10.1145%2F27629.27630" target="_blank">https://doi.org/10.1145%2F27629.27630</a> <a href="#fnref:1" class="footnote-back-ref">↩</a></p></li>
<li id="fn:2"><p>Imielinski, T.; Lipski, W. (1982). "A systematic approach to relational database theory". Proceedings of the 1982 ACM SIGMOD international conference on Management of data - SIGMOD '82. New York, NY: ACM. pp. 8–14. doi:10.1145/582353.582356. ISBN 978-0897910736. S2CID 2034345. <a href="978-0897910736" target="_blank">978-0897910736</a> <a href="#fnref:2" class="footnote-back-ref">↩</a></p></li>
<li id="fn:3"><p>Rybinski, H. (1987). "On First-Order-Logic Databases". ACM Transactions on Database Systems. 12 (3): 325–349. doi:10.1145/27629.27630. S2CID 2439329. <a href="https://doi.org/10.1145%2F27629.27630" target="_blank">https://doi.org/10.1145%2F27629.27630</a> <a href="#fnref:3" class="footnote-back-ref">↩</a></p></li>
<li id="fn:4"><p>Pottinger, P.; Berstein, P. (2008). "Schema merging and mapping creation for relational sources". Proceedings of the 11th international conference on Extending database technology: Advances in database technology. New York, NY: ACM. pp. 73–84. CiteSeerX 10.1.1.405.2990. doi:10.1145/1353343.1353357. ISBN 9781595939265. S2CID 15742995. <a href="9781595939265" target="_blank">9781595939265</a> <a href="#fnref:4" class="footnote-back-ref">↩</a></p></li>
<li id="fn:5"><p>Pottinger, P.; Berstein, P. (2008). "Schema merging and mapping creation for relational sources". Proceedings of the 11th international conference on Extending database technology: Advances in database technology. New York, NY: ACM. pp. 73–84. CiteSeerX 10.1.1.405.2990. doi:10.1145/1353343.1353357. ISBN 9781595939265. S2CID 15742995. <a href="9781595939265" target="_blank">9781595939265</a> <a href="#fnref:5" class="footnote-back-ref">↩</a></p></li>
<li id="fn:6"><p>Pottinger, P.; Berstein, P. (2008). "Schema merging and mapping creation for relational sources". Proceedings of the 11th international conference on Extending database technology: Advances in database technology. New York, NY: ACM. pp. 73–84. CiteSeerX 10.1.1.405.2990. doi:10.1145/1353343.1353357. ISBN 9781595939265. S2CID 15742995. <a href="9781595939265" target="_blank">9781595939265</a> <a href="#fnref:6" class="footnote-back-ref">↩</a></p></li>
<li id="fn:7"><p>Pottinger, P.; Berstein, P. (2008). "Schema merging and mapping creation for relational sources". Proceedings of the 11th international conference on Extending database technology: Advances in database technology. New York, NY: ACM. pp. 73–84. CiteSeerX 10.1.1.405.2990. doi:10.1145/1353343.1353357. ISBN 9781595939265. S2CID 15742995. <a href="9781595939265" target="_blank">9781595939265</a> <a href="#fnref:7" class="footnote-back-ref">↩</a></p></li>
<li id="fn:8"><p>Pottinger, P.; Berstein, P. (2008). "Schema merging and mapping creation for relational sources". Proceedings of the 11th international conference on Extending database technology: Advances in database technology. New York, NY: ACM. pp. 73–84. CiteSeerX 10.1.1.405.2990. doi:10.1145/1353343.1353357. ISBN 9781595939265. S2CID 15742995. <a href="9781595939265" target="_blank">9781595939265</a> <a href="#fnref:8" class="footnote-back-ref">↩</a></p></li>
<li id="fn:9"><p>Ashdown, Lance; Kyte, Tom (February 2010). Oracle Database Concepts 11g Release 2 (11.2). et al. Oracle Corporation. Archived from the original on January 29, 2010. Retrieved April 14, 2010. A database schema is a logical container for data structures, called schema objects. Examples of schema objects are tables and indexes. <a href="https://web.archive.org/web/20100129054802/http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/tablecls.htm#CNCPT111" target="_blank">https://web.archive.org/web/20100129054802/http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/tablecls.htm#CNCPT111</a> <a href="#fnref:9" class="footnote-back-ref">↩</a></p></li>
<li id="fn:10"><p>Oracle Database Concepts 10g Release 2 (10.2)Part Number B14220-02. Retrieved November 26, 2012. 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. <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm" target="_blank">http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm</a> <a href="#fnref:10" class="footnote-back-ref">↩</a></p></li>
<li id="fn:11"><p>Ashdown, Lance; Kyte, Tom (February 2010). Oracle Database Concepts 11g Release 2 (11.2). et al. Oracle Corporation. Archived from the original on January 29, 2010. Retrieved April 14, 2010. 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. <a href="https://web.archive.org/web/20100129054802/http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/tablecls.htm#CNCPT111" target="_blank">https://web.archive.org/web/20100129054802/http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/tablecls.htm#CNCPT111</a> <a href="#fnref:11" class="footnote-back-ref">↩</a></p></li>
<li id="fn:12"><p>McDougall, R (2010). "Virtualization performance: perspectives and challenges ahead". ACM SIGOPS Operating Systems Review. 44 (4). doi:10.1145/1899928.1899933. S2CID 16112550. <a href="/wiki/Doi_(identifier)" target="_blank">/wiki/Doi_(identifier)</a> <a href="#fnref:12" class="footnote-back-ref">↩</a></p></li>
<li id="fn:13"><p>VanMSFT (May 9, 2024). "Ownership and user-schema separation in SQL Server - SQL Server". learn.microsoft.com. Retrieved October 31, 2024. <a href="https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/ownership-and-user-schema-separation?view=sql-server-ver16" target="_blank">https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/ownership-and-user-schema-separation?view=sql-server-ver16</a> <a href="#fnref:13" class="footnote-back-ref">↩</a></p></li>
</ol>

Database schema open-in-new

Database schema