Page 23 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes 2.1.4 Application to Databases

A type as used in a typical relational database might be the set of integers, the set of character
strings, the set of dates, or the two boolean values true and false, and so on. The corresponding
type names for these types might be the strings “int”, “char”, “date”, “boolean”, etc. It is important
to understand, though, that relational theory does not dictate what types are to be supported;
indeed, nowadays provisions are expected to be available for user-defined types in addition to
the built-in ones provided by the system.
Attribute is the term used in the theory for what is commonly referred to as a column. Similarly,
table is commonly used in place of the theoretical term relation (though in SQL the term is by no
means synonymous with relation). A table data structure is specified as a list of column definitions,
each of which specifies a unique column name and the type of the values that are permitted for
that column. An attribute value is the entry in a specific column and row, such as “John Doe”
or “35”.
A tuple is basically the same thing as a row, except in an SQL DBMS, where the column values in
a row are ordered. (Tuples are not ordered; instead, each attribute value is identified solely by
the attribute name and never by its ordinal position within the tuple.) An attribute name might
be “name” or “age”.
A relation is a table structure definition (a set of column definitions) along with the data appearing
in that structure. The structure definition is the heading and the data appearing in it is the body,
a set of rows. A database relvar (relation variable) is commonly known as a base table.
The heading of its assigned value at any time is as specified in the table declaration and its body
is that most recently assigned to it by invoking some update operator (typically, Insert, Update,
or Delete). The heading and body of the table resulting from evaluation of some query are
determined by the definitions of the operators used in the expression of that query.

Notes In SQL the heading is not always a set of column definitions as described above,
because it is possible for a column to have no name and also for two or more columns to
have the same name. Also, the body is not always a set of rows because in SQL it is possible
for the same row to appear more than once in the same body.

2.1.5 SQL and the Relational Model

SQL, initially pushed as the standard language for relational databases, deviates from the relational
model in several places. The current ISO SQL standard doesn’t mention the relational model or
use relational terms or concepts. However, it is possible to create a database conforming to the
relational model using SQL if one does not use certain SQL features.
The following deviations from the relational model have been noted in SQL. Note that few
database servers implement the entire SQL standard and in particular do not allow some of
these deviations. Whereas NULL is nearly ubiquitous, for example, allowing duplicate column
names within a table or anonymous columns is uncommon.

Duplicate Rows

The same row can appear more than once in an SQL table. The same tuple cannot appear more
than once in a relation.

16 LOVELY PROFESSIONAL UNIVERSITY