A domain is a set of atomic values. An atomic value is a
value that is indivisible; i.e. it cannot be broken down into smaller values.
This depends partly on how the value is to be used. A Name
attribute (ex: "David B. Brown") may be considered atomic if only the entire
name value is used. However a Name could be broken down into
Surname, Forename, and
Initial should there be a use for the individual attributes.
On the other hand a student ID would be irreducibly atomic in that it cannot
be broken down further.
If an attribute is multivalued it should be represented by
separate relations. Thus if a Supervisor has many
Employees this information cannot be stored in a single
table because Employees is multivalued. The relationship
between the two must be broken up into at least two tables.
Keys and Key Constraints
Generally all tuples in a relation should be distinct
this distinctiveness can be based upon some subset of the relation's
attributes: this set of attributes is a superkey
A key is a minimal superkey: it is the smallest set of
attributes that can uniquely identify a tuple in a relation.
A relation may have more than one key - each of these possible keys is
a candidate key.
One of a relation's keys can be designated as the primary
key.
Ex: a Student table may contain the attributes
Student_ID, OHIP_Number and
SIN. Taken together these three attributes form a
superkey. As well any two of these attributes taken together form a
superkey. Individually all three are candidate keys because each can be
used to uniquely identify a tuple in Student. Only one can
be chosen to be the primary key.
Ex: In the case of a lookup table, like that between
member and keyword the primary key is a
combination of the attributes member_id and
keyword_ID because only the combination of these two
attributes uniquely identifies a tuple in the
member_keyword table.
No primary key value should be null. (This is the entity integrity
constraint).
Key constraints between tables are called referential integrity
constraints.
A tuple in one relation may refer to an existing tuple in another
relation.
An attribute in one table is called a foreign key if its
values refer to the values of a primary key in another table.
A foreign key may or may not be allowed to have a null value.
These key constraints must hold when table data is inserted, deleted,
or updated.
Data Definition Language (DDL)
specifies schemas
may be different DDLs for external, conceptual and internal
schemas
meta-data is stored in a data dictionary
Data Manipulation Language (DML)
specifies data queries and updates
two general ways of querying and updating a database
through "stand alone" DML facilities
from within application programs
two kinds of DMLs: result sets may be
navigational (one record at a time)
non-navigational
SQL (Structured Query Language) is used as both a DDL and a DML.
A referential key constraint can protect data in one table from deletions
or updates in another table.
If a tuple in a parent table has children (i.e. tuples related by a
foreign key) then attempts to delete the parent before the children are
deleted fail. This is because the children are linked to the parent's
primary key by their foreign key value, and if the primary key is deleted,
their foreign key values become invalid. Thus the deletion is rejected.
Similarly this can prevent the primary key of a parent from being updated.
Once all the children are deleted, however, the parent tuple may also be
deleted.
A referential key constraint can also make deletions and updates
easier.
Rather than rejecting the deletion of a parent, the deletion can be
cascaded. In this case when a parent tuple is deleted all child
tuples with matching foreign keys are deleted as well. Thus a single
command can be used to delete large amounts of data. Similarly an update to
a particular primary key value can be cascaded to all child foreign
keys.
The database designer must choose whether a particular relation rejects or
cascades deletions and updates.