Database schema

Last updated

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. MediaWiki 1.28.0 database schema.svg
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 "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). The formal definition of a database schema is a set of formulas (sentences) called integrity constraints imposed on a database.[ citation needed ] 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. [1] 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.

Contents

"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-users." [2] 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 integrity constraints specifically for an application and the constraints specifically for a type of database, all expressed in the same database language. [1] In a relational database, the schema defines the tables, fields, relationships, views, indexes, packages, procedures, functions, queues, triggers, types, sequences, materialized views, synonyms, database links, directories, XML schemas, 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. [3]
Extended overlap preservation
Source-specific elements that are associated with a source’s overlapping elements are passed through to the database schema. [3]
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. [3]
Minimality
If any elements of the database schema are dropped then the database schema is not ideal. [3]

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: [3] 

Flight(flight-number, date, time, price)

Oracle database specificity

In the context of Oracle Databases, a schema object is a logical data storage structure. [4]

An Oracle database associates a separate schema with each database user. [5] A schema comprises a collection of schema objects. Examples of schema objects include:

On the other hand, non-schema objects may include: [6]

Schema objects do not have a one-to-one correspondence to physical files on disk that store their information. However, Oracle databases 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 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. [7]

See also

Related Research Articles

<span class="mw-page-title-main">Database</span> Organized collection of data in computing

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 analyze the data. The DBMS additionally encompasses the core facilities provided to administer the database. The sum total of the database, the DBMS and the associated applications can be referred to as a database system. Often the term "database" is also used loosely to refer to any of the DBMS, the database system or an application associated with the database.

A relational database is a 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 relational database systems are equipped with the option of using SQL for querying and updating the database.

Structured Query Language (SQL) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.

<span class="mw-page-title-main">Object–relational database</span> Database management system

An object–relational database (ORD), or object–relational database management system (ORDBMS), is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language. In addition, just as with pure relational systems, it supports extension of the data model with custom data types and methods.

<span class="mw-page-title-main">Data model</span> Model that organizes elements of data and how they relate to one another and to real-world entities.

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 composed of a number of other elements which, in turn, represent the color and size of the car and define its owner.

In the context of SQL, data definition or data description language (DDL) is a syntax for creating and modifying database objects such as tables, indices, and users. DDL statements are similar to a computer programming language for defining data structures, especially database schemas. Common examples of DDL statements include CREATE, ALTER, and DROP.

<span class="mw-page-title-main">Entity–relationship model</span> Model or diagram describing interrelated things

An entity–relationship model describes interrelated things of interest in a specific domain of knowledge. A basic ER model is composed of entity types and specifies relationships that can exist between entities.

<span class="mw-page-title-main">Data modeling</span> Creating a model of the data in a system

Data modeling in software engineering is the process of creating a data model for an information system by applying certain formal techniques. It may be applied as part of broader Model-driven engineering (MDD) concept.

A logical data model or logical schema is a data model of a specific problem domain expressed independently of a particular database management product or storage technology but in terms of data structures such as relational tables and columns, object-oriented classes, or XML tags. This is as opposed to a conceptual data model, which describes the semantics of an organization without reference to technology.

Object–relational impedance mismatch creates difficulties going from data in relational data stores to usage in domain-driven object models. Object-orientation (OO) is the default method for business-centric design in programming languages. The problem lies in neither relational nor OO, but in the conceptual difficulty mapping between the two logic models. Both are logical models implementable differently on database servers, programming languages, design patterns, or other technologies. Issues range from application to enterprise scale, whenever stored relational data is used in domain-driven object models, and vice versa. Object-oriented data stores can trade this problem for other implementation difficulties.

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. Once created, a tablespace can be referred to by name when creating database segments.

An entity–attribute–value model (EAV) is a data model optimized for the space-efficient storage of sparse—or ad-hoc—property or data values, intended for situations where runtime usage patterns are arbitrary, subject to user variation, or otherwise unforeseeable using a fixed design. The use-case targets applications which offer a large or rich system of defined property types, which are in turn appropriate to a wide set of entities, but where typically only a small, specific selection of these are instantiated for a given entity. Therefore, this type of data model relates to the mathematical notion of a sparse matrix.

<span class="mw-page-title-main">Oracle Designer</span>

Oracle Designer was Oracle's CASE tool for designing an information system and generating it. After generating the information system one is able to edit the generated code with Oracle Developer Suite.

In relational database management systems, a unique key is a candidate key. All the candidate keys of a relation can uniquely identify the records of the relation, but only one of them is used as the primary key of the relation. The remaining candidate keys are called unique keys because they can uniquely identify a record in a relation. Unique keys can consist of multiple columns. Unique keys are also called alternate keys. Unique keys are an alternative to the primary key of the relation. In SQL, the unique keys have a UNIQUE constraint assigned to them in order to prevent duplicates. Alternate keys may be used like the primary key when doing a single-table select or when filtering in a where clause, but are not typically used to join multiple tables.

Database administration is the function of managing and maintaining database management systems (DBMS) software. Mainstream DBMS software such as Oracle, IBM Db2 and Microsoft SQL Server need ongoing management. As such, corporations that use DBMS software often hire specialized information technology personnel called database administrators or DBAs.

<span class="mw-page-title-main">Database model</span> Type of data model

A database model is a type of data model that determines the logical structure of a database. It fundamentally determines in which manner data can be stored, organized and manipulated. The most popular example of a database model is the relational model, which uses a table-based format.

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 while mapping refers to the transformations between the objects. For example, in the two schemas DB1.Student and DB2.Grad-Student ; possible matches would be: DB1.Student ≈ DB2.Grad-Student; DB1.SSN = DB2.ID etc. and possible transformations or mappings would be: DB1.Marks to DB2.Grades.

<span class="mw-page-title-main">Keyspace (distributed data store)</span> Digital object that holds together all column families of a design

A keyspace in a NoSQL data store is an object that holds together all column families of a design. It is the outermost grouping of the data in the data store. It resembles the schema concept in Relational database management systems. Generally, there is one keyspace per application.

The following is provided as an overview of and topical guide to databases:

GQL is a proposed standard graph query language. In September 2019 a proposal for a project to create a new standard graph query language was approved by a vote of national standards bodies which are members of ISO/IEC Joint Technical Committee 1(ISO/IEC JTC 1). JTC 1 is responsible for international Information Technology standards. GQL is intended to be a declarative database query language, like SQL.

References

  1. 1 2 Rybinski, H. (1987). "On First-Order-Logic Databases". ACM Transactions on Database Systems. 12 (3): 325–349. doi: 10.1145/27629.27630 . S2CID   2439329.
  2. ↑ 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.
  3. 1 2 3 4 5 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.
  4. ↑ 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.
  5. ↑ 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.
  6. ↑ 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.
  7. ↑ McDougall, R (2010). "Virtualization performance: perspectives and challenges ahead". ACM SIGOPS Operating Systems Review. 44 (4). doi:10.1145/1899928.1899933. S2CID   16112550.
This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.