SQL database

Schema, table, row and column

The SQL database consists of  tables specified by one or more schemas. A table concurs with the relation in a relation model, with the following qualification

• the table allows similar rows to be repeated several times, especially in query results, thus it is not theoretically a mathematical  relation, but a so-called multiset. 

A table consists of  rows. The rows concur with the tuples in the relation model. There is at least one column in each table. The columns must have names. A domain is attached to each column. The domains are based on the basic SQL data types. According to the SQL-92 standard, the values in the columns are atomic, like in the relation model. The SQL-99 standard, on the other hand, also allows structured values, i.e. such values that can divide into named or indexed parts. The SQL-99 also enables the specification of user's own data types.

Example:     structured columns in SQL-99

• column address may divided into parts country, postal code, city name and street address 
• column authors of an article may be defined as an array where the elements are referred as authors[1], authors[2], etc.

SQL provides two kinds of tables: base tables and derived tables, or views. The base tables are stored in the secondary storage. Derived tables are specified by queries. They can be used similarly to base tables in many database operations.

Table owners, roles and access privileges

Each schema has an owner who also owns the tables specified by the schema. The owner is identified by his or her user account. The user account may belong to several user groups. The term user group is not actually used in SQL, instead there are roles. Users sharing the same role are here considered to form a user group. Users may have many roles. Both the users and the user groups can be granted varied access rights in the database.

An owner has all the rights to his or her tables. By default, other users (except super users, called database administrators) are not allowed access to the owner's tables. However, the owner may grant other users or user groups the right to access his or her tables.

A database may be accompanied with constraints. These may concern individuals rows of a table, the entire table or even multiple tables. Such modifications of the database are not accepted that would violate the constraints.