A data model is not just a way of structuring data: it also defines a set of operations that can be performed on the data. The relational model, for example, defines operations such as select, project, and join. Although these operations may not be explicit in a particular query language, they provide the foundation on which a query language is built. A data model is a model that describes how data are represented and used in an abstract way. ... A SELECT statement in SQL returns a result set of records from one or more tables. ... A join combines records from two tables in a relational database and results in a new (temporary) table, also called joined table. ... Query languages are computer languages used to make queries into databases and information systems. ...

Models

Various techniques are used to model data structure. Most database systems are built around one particular data model, although it is increasingly common for products to offer support for more than one model. For any one logical model various physical implementations may be possible, and most products will offer the user some level of control in tuning the physical implementation, since the choices that are made have a significant effect on performance. An example of this is the relational model: all serious implementations of the relational model allow the creation of indexes which provide fast access to rows in a table if the values of certain columns are known. In mathematics, model theory is the study of the representation of mathematical concepts in terms of set theory, or the study of the models which underlie mathematical systems. ... The relational model for database management is a database model based on predicate logic and set theory. ...

Flat model

This may not strictly qualify as a data model, as defined above. The flat (or table) model consists of a single, two-dimensional array of data elements, where all members of a given column are assumed to be similar values, and all members of a row are assumed to be related to one another. For instance, columns for name and password that might be used as a part of a system security database. Each row would have the specific password associated with an individual user. Columns of the table often have a type associated with them, defining them as character data, date or time information, integers, or floating point numbers. This model is, incidentally, a basis of the spreadsheet. A simple diagram depicting conversion of a CSV-format flat file database table into a relational database table. ... For other uses, see Data (disambiguation). ... Screenshot of a spreadsheet made with OpenOffice. ...

Hierarchical model

Main article: Hierarchical model

In a hierarchical model, data is organized into a tree-like structure, implying a single upward link in each record to describe the nesting, and a sort field to keep the records in a particular order in each same-level list. Hierarchical structures were widely used in the early mainframe database management systems, such as the Information Management System (IMS) by IBM, and now describe the structure of XML documents. This structure allows one 1:N relationship between two types of data. This structure is very efficient to describe many relationships in the real world; recipes, table of contents, ordering of paragraphs/verses, any nested and sorted information. However, the hierarchical structure is inefficient for certain database operations when a full path (as opposed to upward link and sort field) is not also included for each record. In a hierarchical data model, data are organized into a tree-like structure. ... A hierarchical database is a kind of database management system that links records together in a tree data structure such that each record type has only one owner, e. ...

One limitation of the hierarchical model is its inability to efficiently represent redundancy in data.

Network model

Main article: Network model

The network model (defined by the CODASYL specification) organizes data using two fundamental constructs, called records and sets. Records contain fields (which may be organized hierarchically, as in the programming language COBOL). Sets (not to be confused with mathematical sets) define one-to-many relationships between records: one owner, many members. A record may be an owner in any number of sets, and a member in any number of sets. The network model is a database model conceived as a flexible way of representing objects and their relationships. ... A network model database management system has a more flexible structure than the hierarchical model or relational model, but pays for it in processing time and specialization of types. ... CODASYL (often spelt Codasyl) is an acronym for COnference on DAta SYstems Languages. This was a IT industry consortium formed in 1959 to guide the development of a standard programming language that could be used on many computers. ... COBOL is a third-generation programming language, and one of the oldest programming languages still in active use. ... ...

The network model is a variation on the hierchical model, to the extent that it is built on the concept of multiple branches (lower-level structures) emanating from one or more nodes (higher-level structures), while the model differs from the hierchical model in that branches can be connected to multiple nodes. The network model is able to represent redundancy in data more efficiently than is the hierarchical model.

The operations of the network model are navigational in style: a program maintains a current position, and navigates from one record to another by following the relationships in which the record participates. Records can also be located by supplying key values.

Although it is not an essential feature of the model, network databases generally implement the set relationships by means of pointers that directly address the location of a record on disk. This gives excellent retrieval performance, at the expense of operations such as database loading and reorganization. It has been suggested that Software pointer be merged into this article or section. ...

Most Object databases use the navigational concept to provide fast navigation across networks of objects, generally using Object Identifiers as "smart" pointers to related objects. Objectivity/DB, for instance, implements named 1:1, 1:many, Many:1 and Many:Many named relationships that can cross databases. Many object databases also support SQL, combining the strengths of both models. In an object oriented database, information is represented in the form of objects as used in Object-Oriented Programming. ... Objectivity/DB is a commercial object oriented database management system ODBMS produced by Objectivity, Inc. ... In an object oriented database, information is represented in the form of objects as used in Object-Oriented Programming. ... The related Category:SQL statements has been nominated for deletion, merging, or renaming. ...

Relational model

Main article: Relational model

The relational model was introduced in an academic paper by E. F. Codd in 1970 as a way to make database management systems more independent of any particular application. It is a mathematical model defined in terms of predicate logic and set theory. The relational model for database management is a database model based on predicate logic and set theory. ... The relational model for database management is a database model based on predicate logic and set theory. ... Edgar F. Ted Codd (August 23, 1923 - April 18, 2003) was a British computer scientist who made seminal contributions to the theory of relational databases. ... ... Set theory is the mathematical theory of sets, which represent collections of abstract objects. ...

The products that are generally referred to as relational databases in fact implement a model that is only an approximation to the mathematical model defined by Codd. Three key terms are used extensively in relational database models: relations, attributes, and domains. A relation is a table with columns and rows. The named columns of the relation are called attributes, and the domain is the set of values the attributes are allowed to take. A relational database is a database that conforms to the relational model, and refers to a databases data and schema (the databases structure of how that data is arranged). ...

The basic data structure of the relational model is the table, where information about a particular entity (say, an employee) is represented in columns and rows (also called tuples). Thus, the "relation" in "relational database" refers to the various tables in the database; a relation is a set of tuples. The columns enumerate the various attributes of the entity (the employee's name, address or phone number, for example), and a row is an actual instance of the entity (a specific employee) that is represented by the relation. As a result, each tuple of the employee table represents various attributes of a single employee. In mathematics, a tuple is a finite sequence (also known as an ordered list) of objects, each of a specified type. ...

All relations (and, thus, tables) in a relational database have to adhere to some basic rules to qualify as relations. First, the ordering of columns is immaterial in a table. Second, there can't be identical tuples or rows in a table. And third, each tuple will contain a single value for each of its attributes.

A relational database contains multiple tables, each similar to the one in the "flat" database model. One of the strengths of the relational model is that, in principle, any value occurring in two different records (belonging to the same table or to different tables), implies a relationship among those two records. Yet, in order to enforce explicit integrity constraints, relationships between records in tables can also be defined explicitly, by identifying or non-identifying parent-child relationships characterized by assigning cardinality (1:1, (0)1:M, M:M). Tables can also have a designated single attribute or a set of attributes that can act as a "key", which can be used to uniquely identify each tuple in the table. A database model is a theory or specification describing how a database is structured and used. ...

A key that can be used to uniquely identify a row in a table is called a primary key. Keys are commonly used to join or combine data from two or more tables. For example, an Employee table may contain a column named Location which contains a value that matches the key of a Location table. Keys are also critical in the creation of indexes, which facilitate fast retrieval of data from large tables. Any column can be a key, or multiple columns can be grouped together into a compound key. It is not necessary to define all the keys in advance; a column can be used as a key even if it was not originally intended to be one.

A key that has an external, real-world meaning (such as a person's name, a book's ISBN, or a car's serial number) is sometimes called a "natural" key. If no natural key is suitable (think of the many people named Brown), an arbitrary or surrogate key can be assigned (such as by giving employees ID numbers). In practice, most databases have both generated and natural keys, because generated keys can be used internally to create links between rows that cannot break, while natural keys can be used, less reliably, for searches and for integration with other databases. (For example, records in two independently developed databases could be matched up by social security number, except when the social security numbers are incorrect, missing, or have changed.) The International Standard Book Number, or ISBN (sometimes pronounced is-ben), is a unique[1] identifier for books, intended to be used commercially. ... Sample United States Social Security Card In the United States, a Social Security number (or SSN) is a 9 digit number issued to citizens, permanent residents, and temporary (working) residents under section 205(c)(2) of the Social Security Act, codified as . ...

Relational operations

Users (or programs) request data from a relational database by sending it a query that is written in a special language, usually a dialect of SQL. Although SQL was originally intended for end-users, it is much more common for SQL queries to be embedded into software that provides an easier user interface. Many web sites, such as Wikipedia, perform SQL queries when generating pages. In general, a query is a form of questioning, in a line of inquiry. ... The related Category:SQL statements has been nominated for deletion, merging, or renaming. ...

In response to a query, the database returns a result set, which is just a list of rows containing the answers. The simplest query is just to return all the rows from a table, but more often, the rows are filtered in some way to return just the answer wanted.

Often, data from multiple tables are combined into one, by doing a join. Conceptually, this is done by taking all possible combinations of rows (the Cartesian product), and then filtering out everything except the answer. In practice, relational database management systems rewrite ("optimize") queries to perform faster, using a variety of techniques. A join combines records from two tables in a relational database and results in a new (temporary) table, also called joined table. ... In mathematics, the Cartesian product is a direct product of sets. ... The query optimizer is a component of database management system that is used to analyzes queries submitted to database server for execution, and then determines the optimal way to execute the query. ...

There are a number of relational operations in addition to join. These include project (the process of eliminating some of the columns), restrict (the process of eliminating some of the rows), union (a way of combining two tables with similar structures), difference (which lists the rows in one table that are not found in the other), intersect (which lists the rows found in both tables), and product (mentioned above, which combines each row of one table with each row of the other). Depending on which other sources you consult, there are a number of other operators - many of which can be defined in terms of those listed above. These include semi-join, outer operators such as outer join and outer union, and various forms of division. Then there are operators to rename columns, and summarizing or aggregating operators, and if you permit relation values as attributes (RVA - relation-valued attribute), then operators such as group and ungroup. The SELECT statement in SQL serves to handle all of these except for the group and ungroup operators. ...

The flexibility of relational databases allows programmers to write queries that were not anticipated by the database designers. As a result, relational databases can be used by multiple applications in ways the original designers did not foresee, which is especially important for databases that might be used for decades. This has made the idea and implementation of relational databases very popular with businesses.

Normal Forms

Main article: Database normalization

Relations are classified based upon the types of anomalies to which they're vulnerable. A database that's in the first normal form is vulnerable to all types of anomalies, while a database that's in the domain/key normal form has no modification anomalies. Normal forms are hierarchical in nature. That is, the lowest level is the first normal form, and the database cannot meet the requirements for higher level normal forms without first having met all the requirements of the lesser normal forms.^[1] Database normalization is a table design technique and industry best practice. ...

Dimensional model

The dimensional model is a specialized adaptation of the relational model used to represent data in data warehouses in a way that data can be easily summarized using OLAP queries. In the dimensional model, a database consists of a single large table of facts that are described using dimensions and measures. A dimension provides the context of a fact (such as who participated, when and where it happened, and its type) and is used in queries to group related facts together. Dimensions tend to be discrete and are often hierarchical; for example, the location might include the building, state, and country. A measure is a quantity describing the fact, such as revenue. It's important that measures can be meaningfully aggregated - for example, the revenue from different locations can be added together. A dimensional database is one which, rather than storing data in multiple two dimensional tables (as a relational databases does), represents key data entities as different dimensions. ... This article or section does not cite its references or sources. ... On Line Analytical Processing, or OLAP, is an approach to quickly provide answers to analytical queries that are dimensional in nature. ...

In an OLAP query, dimensions are chosen and the facts are grouped and added together to create a summary.

The dimensional model is often implemented on top of the relational model using a star schema, consisting of one table containing the facts and surrounding tables containing the dimensions. Particularly complicated dimensions might be represented using multiple tables, resulting in a snowflake schema. The star schema (sometimes referenced as star join schema) is the simplest data warehouse schema, consisting of a single fact table with a compound primary key, with one segment for each dimension and with additional columns of additive, numeric facts. ... The introduction to this article provides insufficient context for those unfamiliar with the subject matter. ...

A data warehouse can contain multiple star schemas that share dimension tables, allowing them to be used together. Coming up with a standard set of dimensions is an important part of dimensional modeling.

Object database models

Main article: Object-relational model

Main article: Object model

In recent years, the object-oriented paradigm has been applied to database technology, creating a new programming model known as object databases. These databases attempt to bring the database world and the application programming world closer together, in particular by ensuring that the database uses the same type system as the application program. This aims to avoid the overhead (sometimes referred to as the impedance mismatch) of converting information between its representation in the database (for example as rows in tables) and its representation in the application program (typically as objects). At the same time, object databases attempt to introduce the key ideas of object programming, such as encapsulation and polymorphism, into the world of databases. An object-relational database (ORD) or object-relational database management system (ORDBMS) is a relational database management system that allows developers to integrate the database with their own custom data types and methods. ... In the computing discipline object model has two related but distinct meanings: 1. ... Object-oriented programming (OOP) is a computer programming paradigm in which a software system is modeled as a set of objects that interact with each other. ... In an object oriented database, information is represented in the form of objects like in object oriented programming. ... In computer science, a type system defines how a programming language classifies values and expressions into types, how it can manipulate those types and how they interact. ... The Object-Relational Impedance Mismatch refers to a set of conceptual and technical difficulties which are often encountered when a relational database is being used by a program written in an object-oriented programming language or style; often in such way that objects and/or class definitions are mapped in... Encapsulation may refer to: information hiding and separation of concerns, in software engineering, the process of enclosing programming elements inside larger, more abstract entities integrated circuit encapsulation, in electronics the design and manufacture of protective packages micro-encapsulation, means to confine material molecular encapsulation, means to confine molecules encapsulation (pharmacology... In computer science, polymorphism means allowing a single definition to be used with different types of data (specifically, different classes of objects). ...

A variety of these ways have been tried for storing objects in a database. Some products have approached the problem from the application programming end, by making the objects manipulated by the program persistent. This also typically requires the addition of some kind of query language, since conventional programming languages do not have the ability to find objects based on their information content. Others have attacked the problem from the database end, by defining an object-oriented data model for the database, and defining a database programming language that allows full programming capabilities as well as traditional query facilities. Persistence is the term used in computer science to describe a capability used by a computer programmer to store data structures in non-volatile storage such as a file system or a relational database. ...

Object databases suffered because of a lack of standardization: although standards were defined by ODMG, they were never implemented well enough to ensure interoperability between products. Nevertheless, object databases have been used successfully in many applications: usually specialized applications such as engineering databases or molecular biology databases rather than mainstream commercial data processing. However, object database ideas were picked up by the relational vendors and influenced extensions made to these products and indeed to the SQL language. The Object Data Management Group (ODMG) was formed to deal with the issue of storing objects persistently in a standardized way. ... The related Category:SQL statements has been nominated for deletion, merging, or renaming. ...

Post-relational database models

Several products have been identified as post-relational because the data model incorporates relations but is not constrained by the Information Principle, requiring that all information is represented by data values in relations. Products using a post-relational data model typically employ a model that actually pre-dates the relational model. These might be identified as a directed graph with trees on the nodes. In mathematics, a relation is a generalization of arithmetic relations such as = and < which occur in statements such as 5 < 6 or 2 + 2 = 4. See relation (mathematics), binary relation and relational algebra. ... The ASCII codes for the word Wikipedia represented in binary, the numeral system most commonly used for encoding computer information. ... The relational model for database management is a database model based on predicate logic and set theory. ... This article just presents the basic definitions. ... In computer science, a tree is a widely-used computer data structure that emulates a tree structure with a set of linked nodes. ... A binary tree, a simple type of branching linked data structure. ...

Post-relational databases could be considered a sub-set of object databases as there is no need for object-relational mapping when using a post-relational data model. In spite of many attacks on this class of data models, with designations of being hierarchical or legacy, the post-relational database industry continues to grow as a multi-billion dollar industry, even if the growth stays below the relational database radar. In an object oriented database, information is represented in the form of objects as used in Object-Oriented Programming. ... Object-Relational mapping (aka O/RM, ORM, and O/R mapping), is a programming technique that links databases to object-oriented language concepts, creating (in effect) a virtual object database. ... A relational database is a database that conforms to the relational model, and refers to a databases data and schema (the databases structure of how that data is arranged). ...

Examples of models that could be classified as post-relational are PICK aka MultiValue, and MUMPS, aka M. This article or section is not written in the formal tone expected of an encyclopedia article. ... It has been suggested that this article or section be merged with Dimensional database. ...

References

See Also

• List of Database Models