Physical schema: Difference between revisions

Content deleted Content added

Inline

Latest revision as of 13:53, 18 May 2024

A physical data model (or database design) is a representation of a data design as implemented, or intended to be implemented, in a database management system. In the lifecycle of a project it typically derives from a logical data model, though it may be reverse-engineered from a given database implementation. A complete physical data model will include all the database artifacts required to create relationships between tables or to achieve performance goals, such as indexes, constraint definitions, linking tables, partitioned tables or clusters. Analysts can usually use a physical data model to calculate storage estimates; it may include specific storage allocation details for a given database system.

As of 2012^[update] seven main databases dominate the commercial marketplace: Informix, Oracle, Postgres, SQL Server, Sybase, IBM Db2 and MySQL. Other RDBMS systems tend either to be legacy databases or used within academia such as universities or further education colleges. Physical data models for each implementation would differ significantly, not least due to underlying operating-system requirements that may sit underneath them. For example: SQL Server runs only on Microsoft Windows operating-systems (Starting with SQL Server 2017, SQL Server runs on Linux. It's the same SQL Server database engine, with many similar features and services regardless of your operating system^[2]), while Oracle and MySQL can run on Solaris, Linux and other UNIX-based operating-systems as well as on Windows. This means that the disk requirements, security requirements and many other aspects of a physical data model will be influenced by the RDBMS that a database administrator (or an organization) chooses to use.

Physical schema

Physical schema is a term used in data management to describe how data is to be represented and stored (files, indices, et al.) in secondary storage using a particular database management system (DBMS) (e.g., Oracle RDBMS, Sybase SQL Server, etc.).

In the ANSI/SPARC Architecture three schema approach, the internal schema is the view of data that involved data management technology. This is as opposed to an external schema that reflects an individual's view of the data, or the conceptual schema that is the integration of a set of external schemas.

Subsequently^{[citation needed]} the internal schema was recognized to have two parts:

The logical schema was the way data were represented to conform to the constraints of a particular approach to database management. At that time the choices were hierarchical and network. Describing the logical schema, however, still did not describe how physically data would be stored on disk drives. That is the domain of the physical schema. Now logical schemas describe data in terms of relational tables and columns, object-oriented classes, and XML tags.

A single set of tables, for example, can be implemented in numerous ways, up to and including an architecture where table rows are maintained on computers in different countries.

References

^ "FEA Consolidated Reference Model Document" (PDF). Office of Management and Budget. May 2005. p. 91. Archived from the original (PDF) on July 5, 2010 – via National Archives.
^ rothja. "Overview of SQL Server on Linux - SQL Server". docs.microsoft.com. Retrieved 2019-04-28.

External links

FEA Consolidated Reference Model Document (whitehouse.gov) Oct 2007.

[WH05-1] "FEA Consolidated Reference Model Document" (PDF). Office of Management and Budget. May 2005. p. 91. Archived from the original (PDF) on July 5, 2010 – via National Archives.

[2] rothja. "Overview of SQL Server on Linux - SQL Server". docs.microsoft.com. Retrieved 2019-04-28.

[1]

[2]

@@ Line 1: / Line 1: @@
+{{Short description|Representation of a data design}}
-[[File:Physical Data Model Options.jpg|thumb|320px|Physical Data Model Options.<ref name="WH05">[http://georgewbush-whitehouse.archives.gov/omb/egov/documents/CRM.PDF FEA Consolidated Reference Model Document]. whitehouse.gov May 2005. p.91.</ref>]]
+{{Refimprove|date=April 2008}}
-A '''physical data model''' (a.k.a. [[database design]]) is a representation of a data design which takes into account the facilities and constraints of a given [[database management system]].  In the [[lifecycle of a project]] it is typically derived from a [[logical data model]], though it may be [[reverse-engineer]]ed from a given [[database]] implementation.  A complete physical data model will include all the [[database artifact]]s required to create [[relationships between table]]s or achieve performance goals, such as [[index]]es, constraint definitions, linking tables, [[partitioned table]]s or [[cluster]]s.  The physical data model can usually be used to calculate storage estimates and may include specific storage allocation details for a given database system.
+[[File:Physical Data Model Options.jpg|thumb|320px|Physical data model options.<ref name="WH05">{{cite web |url=https://georgewbush-whitehouse.archives.gov/omb/egov/documents/CRM.PDF |title=FEA Consolidated Reference Model Document |date=May 2005 |page=91 |archive-url=https://web.archive.org/web/20100705040628/http://georgewbush-whitehouse.archives.gov/omb/egov/documents/CRM.PDF |via=[[NARA|National Archives]] |work=[[Office of Management and Budget]] |archive-date=July 5, 2010 }}</ref>]]
+A '''physical data model''' (or '''[[database design]]''') is a representation of a data design as implemented, or intended to be implemented, in a [[database management system]]. In the [[Project lifecycle|lifecycle of a project]] it typically derives from a [[logical data model]], though it may be [[reverse-engineer]]ed from a given [[database]] implementation. A complete physical data model will include all the [[database artifact]]s required to create [[Relational database|relationships between tables]] or to achieve performance goals, such as [[index (database)|index]]es, constraint definitions, [[Associative entity|linking tables]], [[Partition (database)|partitioned tables]] or [[Data cluster|cluster]]s. Analysts can usually use a physical data model to calculate storage estimates; it may include specific storage allocation details for a given database system.
-At present, there are seven main databases in the business market; [[Informix Dynamic Server|Informix]], [[Oracle Database|Oracle]], [[PostgreSQL|Postgres]], [[Microsoft SQL Server|SQL Server]], [[Sybase]], [[IBM DB2|DB2]] and [[MySQL]]. There are a great many other RDBMS systems out there, but these tend either to be legacy databases or used within academia such as universities or further education colleges. A physical data model on each implementation would be significantly different, not least due to the underlying OS requirements that sit underneath them.  Examples would be SQL Server which only run on Microsoft Windows operating systems, while Oracle and MySQL can run on Solaris, Linux and other UNIX-based operating systems as well as Windows.
+{{As of | 2012}} seven main databases dominate the commercial marketplace: [[Informix Dynamic Server|Informix]], [[Oracle Database|Oracle]], [[PostgreSQL|Postgres]], [[Microsoft SQL Server|SQL Server]], [[Sybase]], [[IBM Db2]] and [[MySQL]]. Other RDBMS systems tend either to be legacy databases or used within academia such as universities or further education colleges. Physical data models for each implementation would differ significantly, not least due to underlying [[operating system | operating-system]] requirements that may sit underneath them. For example: SQL Server runs only on [[Microsoft Windows]] operating-systems (Starting with SQL Server 2017, SQL Server runs on Linux. It's the same SQL Server database engine, with many similar features and services regardless of your operating system<ref>{{Cite web|url=https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-overview|title=Overview of SQL Server on Linux - SQL Server|last=rothja|website=docs.microsoft.com|language=en-us|access-date=2019-04-28}}</ref>), while Oracle and MySQL can run on Solaris, Linux and other UNIX-based operating-systems as well as on Windows. This means that the disk requirements, security requirements and many other aspects of a physical data model will be influenced by the RDBMS that a [[database administrator]] (or an organization) chooses to use.
-This means that the disk requirements, security requirements and many other aspects of a physical data model will be influenced entirely by the RDBMS that a database administrator (or their organization) chooses to use.
+==Physical schema==
-Whilst there is increasingly debate surrounding which [[RDBMS]] is better within various domains, it was generally accepted {{By whom|date=December 2009}} that Oracle's architecture is best suited to enterprise & larger implementations, SQL Server better for SME's and MySQL adequate for SME's and small businesses.  A useful resource for such debate (which contains useful case studies) can be found at the IT QUEST<ref>IT QUEST</ref> web site.
+{{Unreferenced section|date=February 2017}}
+''Physical schema'' is a term used in [[data management]] to describe how [[data]] is to be represented and stored (files, indices, ''et al.'') in [[secondary storage]] using a particular [[database management system]] (DBMS) (e.g., [[Oracle Database|Oracle RDBMS]], Sybase SQL Server, etc.).
+In the [[ANSI-SPARC Architecture|ANSI/SPARC Architecture]] [[three schema approach]], the ''internal schema'' is the view of data that involved data management technology. This is as opposed to an ''external schema'' that reflects an individual's view of the data, or the ''[[conceptual schema]]'' that is the integration of a set of external schemas.
+Subsequently{{Citation needed|date=June 2012}} the internal schema was recognized to have two parts:
+The [[logical schema]] was the way data were represented to conform to the constraints of a particular approach to database management.  At that time the choices were [[Hierarchical database model|hierarchical]] and [[Network model (database)|network]]. Describing the logical schema, however, still did not describe how physically data would be stored on disk drives.  That is the domain of the ''physical schema''. Now logical schemas describe data in terms of relational ''tables and columns'', object-oriented ''classes'', and [[XML]] ''tags''.
+A single set of tables, for example, can be implemented in numerous ways, up to and including an architecture where table rows are maintained on computers in different countries.
+==See also==
+*[[Database schema]]
+*[[Conceptual data model]]
+*[[Logical data model]]
 ==References==
 {{Reflist}}
-{{Refimprove|date=April 2008}}
+==External links==
+* [https://obamawhitehouse.archives.gov/sites/default/files/omb/assets/fea_docs/FEA_CRM_v23_Final_Oct_2007_Revised.pdf FEA Consolidated Reference Model Document] (whitehouse.gov) Oct 2007.
 {{DEFAULTSORT:Physical Data Model}}
 [[Category:Data modeling]]
-[[Category:Article Feedback 5]]
+[[Category:Data management]]
+[[ja:スキーマ (データベース)]]