Table (database)
Encyclopedia
In relational database
s and flat file database
s, a table is a set of data elements (values) that is organized using a model of vertical columns
(which are identified by their name) and horizontal rows
. A table has a specified number of columns, but can have any number of rows . Each row is identified by the values appearing in a particular column subset which has been identified as a candidate key
.
Table is another term for relation
s; although there is the difference in that a table is usually a multi-set (bag) of rows whereas a relation is a set and does not allow duplicates. Besides the actual data rows, tables generally have associated with them some meta-information
, such as constraints
on the table or on the values within particular columns.
The data in a table does not have to be physically stored in the database. Views
are also relational tables, but their data are calculated at query time. Another example are nicknames, which represent a pointer to a table in another database.
, the distant counterpart of a table is a structured file
, representing the rows of a table in each record of the file and each column in a record.
Unlike a spreadsheet
, the datatype of field is ordinarily defined by the schema
describing the table. Some relational systems are less strict about field datatype definitions.
of databases, a table can be considered a convenient representation of a relation
, but the two are not strictly equivalent. For instance, an SQL
table can potentially contain duplicate rows, whereas a true relation cannot contain duplicate tuple
s. Similarly, representation as a table implies a particular ordering to the rows and columns, whereas a relation is explicitly unordered. However, the database system does not guarantee any ordering of the rows unless an
clause is specified in the
statement that queries the table.
An equally valid representations of a relation is as an n-dimensional chart, where n is the number of attributes (a table's columns). For example, a relation with two attributes and three values can be represented as a table with two columns and three rows, or as a two-dimensional graph with three points. The table and graph representations are only equivalent if the ordering of rows is not significant, and the table has no duplicate rows.
In SQL, use the
Relational database
A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
s and flat file database
Flat file database
A flat file database describes any of various means to encode a database model as a single file .- Overview :...
s, a table is a set of data elements (values) that is organized using a model of vertical columns
Column (database)
In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
(which are identified by their name) and horizontal rows
Row (database)
In the context of a relational database, a row—also called a record or tuple—represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields...
. A table has a specified number of columns, but can have any number of rows . Each row is identified by the values appearing in a particular column subset which has been identified as a candidate key
Candidate key
In the relational model of databases, a candidate key of a relation is a minimal superkey for that relation; that is, a set of attributes such that# the relation does not have two distinct tuples In the relational model of databases, a candidate key of a relation is a minimal superkey for that...
.
Table is another term for relation
Relation (database)
In relational model:A relation value, which is assigned to a certain relation variable, is time-varying. By using a Data Definition Language , it is able to define relation variables.The following is an example of a heading which consists of three attributes....
s; although there is the difference in that a table is usually a multi-set (bag) of rows whereas a relation is a set and does not allow duplicates. Besides the actual data rows, tables generally have associated with them some meta-information
Metadata
The term metadata is an ambiguous term which is used for two fundamentally different concepts . Although the expression "data about data" is often used, it does not apply to both in the same way. Structural metadata, the design and specification of data structures, cannot be about data, because at...
, such as constraints
Check Constraint
A check constraint is a condition that defines valid data when adding or updating an entry in a table of a relational database. A check constraint is applied to each row in the table. The constraint must be a predicate. It can refer to a single or multiple columns of the table...
on the table or on the values within particular columns.
The data in a table does not have to be physically stored in the database. Views
View (database)
In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions...
are also relational tables, but their data are calculated at query time. Another example are nicknames, which represent a pointer to a table in another database.
Comparisons with other data structures
In non-relational systems, hierarchical databasesHierarchical model
A hierarchical database model is a data model in which the data is organized into a tree-like structure. The structure allows representing information using parent/child relationships: each parent can have many children, but each child has only one parent...
, the distant counterpart of a table is a structured file
Computer file
A computer file is a block of arbitrary information, or resource for storing information, which is available to a computer program and is usually based on some kind of durable storage. A file is durable in the sense that it remains available for programs to use after the current program has finished...
, representing the rows of a table in each record of the file and each column in a record.
Unlike a spreadsheet
Spreadsheet
A spreadsheet is a computer application that simulates a paper accounting worksheet. It displays multiple cells usually in a two-dimensional matrix or grid consisting of rows and columns. Each cell contains alphanumeric text, numeric values or formulas...
, the datatype of field is ordinarily defined by the schema
Logical schema
A Logical Schema is a data model of a specific problem domain expressed in terms of a particular data management technology. Without being specific to a particular database management product, it is in terms of either relational tables and columns, object-oriented classes, or XML tags...
describing the table. Some relational systems are less strict about field datatype definitions.
Tables versus relations
In terms of the relational modelRelational model
The relational model for database management is a database model based on first-order predicate logic, first formulated and proposed in 1969 by Edgar F...
of databases, a table can be considered a convenient representation of a relation
Relation (database)
In relational model:A relation value, which is assigned to a certain relation variable, is time-varying. By using a Data Definition Language , it is able to define relation variables.The following is an example of a heading which consists of three attributes....
, but the two are not strictly equivalent. For instance, an SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
table can potentially contain duplicate rows, whereas a true relation cannot contain duplicate tuple
Tuple
In mathematics and computer science, a tuple is an ordered list of elements. In set theory, an n-tuple is a sequence of n elements, where n is a positive integer. There is also one 0-tuple, an empty sequence. An n-tuple is defined inductively using the construction of an ordered pair...
s. Similarly, representation as a table implies a particular ordering to the rows and columns, whereas a relation is explicitly unordered. However, the database system does not guarantee any ordering of the rows unless an
ORDER BYOrder by (SQL)
An ORDER BY clause in SQL specifies that a SQL SELECT statement returns a result set with the rows being sorted by the values of one or more columns. The sort criteria do not have to be included in the result set. The sort criteria can be expressions, including – but not limited to – column...
clause is specified in the
SELECTSelect (SQL)
The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command...
statement that queries the table.
An equally valid representations of a relation is as an n-dimensional chart, where n is the number of attributes (a table's columns). For example, a relation with two attributes and three values can be represented as a table with two columns and three rows, or as a two-dimensional graph with three points. The table and graph representations are only equivalent if the ordering of rows is not significant, and the table has no duplicate rows.
Table types
Two types of tables exist.- A relational table, which is the basic structure to hold user data in a relational databaseRelational databaseA relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
. - An object table, which is a table that uses an object type to define a column. It is defined to hold instances of objects of a defined type.
In SQL, use the
CREATE TABLE statement to create these tables.