05RelationalDataModel

The Relational Model of Data

Outline

Basic Concepts
Relational Table
Principles of Relational Model
Consistency Constraints
Summary

At the very beginning it is good to return to a concept of "data model". What is a data model ?

As it has been explained earlier, a data model provides a database user with an abstract view of data. Such abstract view of data, can be used for the purpose of: data definition, data manipulation, data retrieval, and data administration in a database system. A data model organises data elements, and standardises how the data elements relate to one another. A data model also provides a set of operations, that can be used to manipulate and to retrieve data. Because a data model provides an abstract view, it is also called as an abstract view of data.

In the past, we discussed the following views of data.

At a hardware level, we talked about sectors, tracks, and cylinders.

At a physical level, we talked about sequences of data blocks.

At a file level, we talked about records, files and file systems.

At a logical level, we talked about tables, hierarchies, and networks.

At a conceptual level we talked about classes of objects, associations, attributes, and generalisation hierarchies.

A data model always comes with a collection of languages that allow of definitions of abstract units of data, manipulation on abstract units of data, retrieval of abstract units of data, and administration of abstract units of data.

Basic Concepts

Data model ? What is it ?
A data model provides an abstract view of data that can be used for data definition, data manipulation, data retrieval, and data administration
Accordingly to Wikipedia) a data model organises data elements and standardises how the data elements relate to one another
Because a data model provides an abstract view it is also commonly called as a view of data
In the past we talked about the following views of data:
- Sector, track, cylinder
- Sequence of data blocks
- Record, file, file system
- Two dimensional tables (tabular view), Hierarchies (tree view), Networks (graph view)
- Classes of objects, associations, attributes

In 1970 Edgar Frank Codd from IBM Corporation defined a model of data based on a tabular view of data and called it as Relational Model of Data. These days IBM Corporation owned more than 90% of IT market and all strategic decisions made by IBM were almost compulsory for the other IT companies. Therefore, everyone agreed that tabular view of data is a wonderful idea and everyone followed IBM. The next 20 years was an absolute domination of The Relational Model of Data

In the future we refer to it as to The Relational Data Model.

At the moment approximately 95% of all database systems is based on The Relational Data Model.

However, it is important to say, that in the past and now Relational Data Model had and still has few serious contenders like Object-Oriented Model, Object-Relational Model, XML Data Model, and recently JSON Data Model.

In the past The Relational Data Model survived the attempts to be replaced by Object-Oriented and later on Object-Relational data model, XML-data model and the most recently JSON Data Model.

Basic Concepts

In 1970 Edgar Frank Codd from IBM Corporation defined a model of data based on a tabular view and called it as Relational Model of Data or simply Relational Model
At the moment (early 2021) ~95% of all database systems is based on Relational Model of Data
However, it is important to say that in the past and now, Relational Model of Data had and still has a few serious contenders like Object-Oriented Model, Object-Relational Model, XML Data Model, and recently JSON Data Model

What view of data provides the relational data model?

The model provides a tabular view of data, where a database consists of so called relational tables. It will be explained later why the tables are called "relational" even the tables do no represent any relations. A relational table consists of a header, and theoretically unlimited number of rows.

A header consists of a sequence of attribute names. An order of attributes in a header is immaterial. It means, the contents of a relational tables does not change when the locations of the columns with attributes are changed.

A row consists of a sequence of values of attributes. A relational table consists of one header and theoretically unlimited number of rows. A structure of each row must be consistent with a structure of a header.

A vertically oriented sequence, that starts from an attribute name in a header, and it is followed by the attribute values in the rows, is called as a column. As we mentioned earlier, and order of columns in a relational table is immaterial.

A header, is also called as a relational schema.

A set of all values of an attribute, is called as a domain of such attribute.

A database is a set of the relational tables.

Basic Concepts

What view of data provides Relational Model of Data ?
- The model provide a tabular view of data
- A relational table consists of a header and theoretically an unlimited number of rows
- A header consists of a sequence of attribute names
- A row consists of a sequence of values of attributes
- A vertical sequence of attribute name followed by the attribute values is called a column
- A header is also called a relational schema
- A set of all values of an attribute is called a domain of an attribute
- A database is a set of relational tables

A picture in the slide shows a sample relational table.

The relational table contains information about the employees.

A header of the relational table contains the names of attributes describing the employees.

An employee is described by a unique number (anum), first name (fname), last name (lname), date of birth (dob), and address, that consists of city name (city), and state (state), and phone number (phone).

The rows below a header, contain the values of attributes describing employees.

A single row describes a single employee.

A symbol: NULL, represents an empty slot in a table.

An empty slot in a table, can be interpreted as a lack of value of an attribute. It may happen, that a value of an attribute is unknown, or an attribute is not applicable to a description of a real world object. In such a case, NULL symbol can be interpreted as a fact that an attribute does not apply to a description a real worlds object. Then, it is impossible to record a value of an attribute.The Relational Model of Data does not enforce any particular meaning of NULL symbol. So, at the end, interpretation of NULL symbol is up to a database designer.

A vertical sequence, that start from a name of attribute in a header and the values of an attribute below is called as a column.

Basic Concepts

A sample relational table:

Step 2

Why a relational table is called as "relational" ?

This is due to the following original Codd's definition of a relational table.

Let A1, A2, An, be the names of attributes.

Let dom(A1), dom(A2), ... , dom(An) be the domains of the attributes, domains mean the sets of values of attributes A1 , A2, ..., An.

A relational table is defined as a subset of the following Cartesian product:

dom(A1) x dom(A2) cx ... x dom(An_

In mathematics, a subset of Cartesian product is called as a "relation" ... and it is why a relational table is called as "relational".

The original definition of a relational table is not completely correct. It is because two relational tables with the different orders of columns contain the same information. However, two relations with a different order of domains involved in a Cartesian product are different.

Next, we show a definition of a relational table, that more precisely matches the reality.

Basic Concepts

Why a relational table is called as a "relational" ?
This is because of the following original E.F. Codd's definition of a relational table:
- Let A₁, A₂, ... A_n be the names of attributes
- Let dom(A₁), dom(A₂), ... dom(A_n) be the domains of the attributes, A₁, A₂, ... A_n, it means, the sets of values of each attribute A₁, A₂, ... A_n
- A relational table is defined as a subset of the Cartesian Product
  dom(A₁) x dom(A₂) x … x dom(A_n)
In mathematics a subset of the Cartesian Product is known as a relation
This is why a relational table is called "relational"
The original definition of a relational table is not completely correct because two tables with the different orders of columns contain the same information ...
... however two relations, that have a different order of domains are different

The Relational Model of Data

Relational Table

A correct definition of relational tables is the following:
- Let A₁, A₂, ... A_n be the names of attributes
- Let dom(A₁), dom(A₂), ... dom(A_n) be the domains of attributes A₁, A₂, ... A_n, it means, the sets of values of each attribute A₁, A₂, ... A_n
- A row r is a full mapping r: {A₁, A₂, ...A _n} -> dom(A₁) U dom(A₂) U ... dom(A_n) such that for all A in {A₁, A₂, ... A_n}, r(A) is in dom(A)
- A relational table is defined as a set of rows
Now, an order of columns is immaterial, but ... a name "relational" is not well justified ;)

The Relational Model of Data

Outline

Basic Concepts
Relational Table
Principles of Relational Model
Consistency Constraints
Summary

A relational table, that has no multivalued, and composite attributes, is in the first normal form, it is also denoted as 1NF.

For example, a relational table in the slide is NOT in 1NF because it contains composite values of attributes in a column: car used and also sets of values in a column: car used. For example, a composite value is a pair Toyota, PKR 234 in a column: car used.

A set of values consists if two composite values in a column: car used.

We can also say, that such table is in zeroth normal form, 0NF, or, that it is a nested table. We shall not use the nested relational tables in this subject.

Are there any higher normal forms like for example 2NF or 3NF ? Yes ! However, we shall not discuss this topic now !

Is a nested relational table in 0NF completely useless ? No ! However, we shall not discuss this topic in this subject !

Principles of Relational Model

A relational table that has no multivalued attributes and composite attributes is in the first normal form (1NF)
For example, a relational table below is NOT in 1NF

0NF

Sometimes we say, that such table is in 0NF or that it is a nested table
Are there any higher normal forms like for example 2NF, 3NF, ... ?
YES ! However, we shall not discuss this topic now !
Is 0NF completely useless ?
NO ! However, we shall not discuss this topic in this subject !

An important principle of the relational data model, is "access to the rows by the contents only rule". The rule says, that we can only retrieve the rows by their contents, and we cannot refer to their physical locations in a relational table.

It means, that it is NOT allowed to say, give me the second row from a relational table given in the current slide. Instead we can say: give me a row such that a value of an attribute a num equals 2, or such that a value of attribute f name equals to 'Johnny', and a value of attribute l name equals to 'Walker'.

Such rule is consistent with a definition of a relational table as a set of rows. If a table is a set of rows that the rows are not ordered in. any way and it is impossible to ask about the first, the second, etc element in a set.

Typically when we insert the rows into a relational table, that is initially empty, the rows are listed in the same order as they have been inserted. It creates an illusion, that whenever a new row is inserted it is always added at the end of a table. In fact, a database system may insert a new row in any location in a relational table assuming that there is enough space in a data block. For example, if some rows inserted at the very beginning are deleted, then a space occupied by such rows can re-used by the news rows. Then, the new rows are inserted at the very beginning of a relational table.

Principles of Relational Model

Access to the rows by the contents rule:
- We can only retrieve rows by their contents

It is NOT allowed to say: give me the second row from the following table:


+------+----------+--------------+------------+------------+-------------------+
| anum | fname    | lname        | dob        | city       | state             |
+------+----------+--------------+------------+------------+-------------------+
|    1 | Harry    | Potter       | 1980-12-12 | Perth      | Western Australia |
|    2 | Johnny   | Walker       | 1990-01-13 | Geelong    | Victoria          |
|    3 | Mary     | Poppins      | 1950-01-01 | Melbourne  | Victoria          |
|    4 | Michael  | Collins      | 1960-05-25 | Brisbane   | Queensland        |
|    5 | Margaret | Finch        | 1953-12-07 | Sydney     | New South Wales   |
|    6 | Claudia  | Kowalewski   | 1959-05-03 | Hobart     | Tasmania          |
|    7 | James    | Bond         | 1960-01-01 | Perth      | Western Australia |
|    8 | Stephen  | Staunton     | 1977-10-23 | Freemantle | Western Australia |
|    9 | Joseph   | Staunton     | 1977-10-23 | Newcastle  | New South Wales   |
|   10 | John     | Spiderman    | 1990-06-21 | Sydney     | New South Wales   |
+------+----------+--------------+------------+------------+-------------------+

We have to say: give me a row such that anum = 2 or such that
fname = 'Johnny' and lname = 'Walker'

Principles of Relational Model

Unique rows rule:
- A relational table cannot contain two identical rows
This rule is violated by all commercial Database Management Systems !

The Relational Model of Data

Outline

Basic Concepts
Relational Table
Principles of Relational Model
Consistency Constraints

Keys
NULL
Referential Integrity Constraints
Domain Constraints

Summary

In the present slide we define a concepts of key, minimal key and superkey.

Let R(A1, A2, ..., An) be a relational table with a relational schema (header), that consists of the attributes A1, A2, ..., An.

A key in a relational table R is a set of attributes K equal to Ak1, Ak2, ..., Akm, such that:

(1) K is included in a set of attributes A1, A2, ..., An, and it means, that K is a subset of the schema of a relational table R. Every attribute that belongs to key must be a member of a relational schema R,

(2) for any two rows v, w, in a relational table R(A1, A2, ..., An), their k-values must be different. It means that a projection of a row v on a set of attributes k must be different from a projection of a row w on the same set of attributes k. Projection of a row v on a set of attributes k means that we remove from a row v the values of all attributes that do not belong to a set of attributes k.

(3) no proper subset of k, satisfies a property above. It means, that if we remove any attribute from a set of attributes k and we create a news set k', then a property (2) is not true for a set of attributes k'.

A key, that does not satisfy property (3), is called a superkey.

A key, that satisfies all conditions, is called a minimal key. In the other words a minimal key is a key such that no attributes can be removed such that we can still preserve the properties (1) and (2). As a consequence, it is the smallest key.

Keys

Let R(A₁, A₂, ... A_n) be a relational table with a relational schema (header) {A₁, A₂, ... A_n}
A key for a table R is a set of attributes K = {A_k1, A_k2, ... A_km} such that:
- (1) K is included in {A₁, A₂, ... A_n}, it means, that K is a subset of the relational schema {A₁, A₂, ... A_n}
- (2) for any two rows v, w in R(A₁, A₂, ... A_n) their k-values must be different,
  i.e. v[k] ≠ w[k]
- (3) no proper subset of K satisfies a property (2) above
A key that does not satisfy a condition (3) is called as superkey
A key that satisfies the conditions (1) and (2) and (3) is called as minimal key

Let us look at few examples of minimal keys, and superkeys, valid in the relational schemas.

First, we consider relational schema: students, that consists of the attributes: student number (snum), first name, last name and date of birth. A set of attributes, that contains only a student number, is a minimal key in a relational schema: student. It is, because every student has a unique student number given by university administration when he/she is enrolled. It is a minimal key, because it is impossible to remove an attribute, such that a remaining set of attributes is not empty.

A set of attributes, that contains only the attributes: student number and last name, is a superkey in a relational schema: student. It is a key, because every student has a unique pair of values of attributes: student number and last name. It is a superkey, because if we remove an attribute: last name, a remaining set of attributes, that contains only a student number, is still a key. Well, it is even a minimal key.

Next, consider a relational schema: enrolment, that consists of the attributes: student number (s num), subject code (code) enrolment date, and enrolment time measured with a precision up to a single milisecond. A set of attributes: student number (s num), code, enrolment date and enrolment-time, is a minimal key in a relational schema: enrolment. It is so, because a student can enrol in only one subject, on a given day, and in a given moment in time, It is a minimal key, because we cannot remove any attributes such, that the remaining set of attributes, still remain a minimal key. For example, if we remove enrolment time, then it is possible that a student enrolled in a subject, dropped the same subject, and enrolled in it again on the same day.

Next, we consider a relational schema: room, that consists of the attributes: building number (bldg#), room number (room#), and room area. A set of attributes: building number and room number, is a minimal key in a relational schema: room. It is so, because all rooms in a building must have unique numbers. A set of attributes: building number, room number, and room area is a superkey. It is so, because we can remove an attribute area: and a pair of attributes left: building number and room number is still a key.

Finally, let us consider a relational schema: part, that consists of the attributes part number (p#), part name, price, and part manufacturer. A set of attributes, that consists of: part number, manufacturer, and price, is a superkey in a relational schema: part. It is so, because if we remove an attribute: price, then a pair of attributes: part number and manufacturer, is a key in a relational schema: part. A set of attributes that consists of: part number, and manufacturer, is a superkey in a relational schema: part. It is so because if we remove manufacturer then a part number is a key in a relational schema part. Finally, a set of attributes, that consists only of: part number, is a minimal key in a relational schema: part.

Keys

Examples:
- A set of attributes {snum} is a minimal key in a relational schema
  STUDENT={snum, first-name, last-name, date-of-birth}
- A set of attributes {snum, last-name} is a superkey in a relational schema STUDENT={snum, first-name, last-name, date-of-birth}
- A set of attributes {snum, code, enrolment-date, enrolment-time} is a minimal key in a relational schema
  ENROLMENT={snum, code, enrolment-date, enrolment-time}
- A set of attributes {bldg#, room#} is a minimal key in a relational schema ROOM={bldg#, room#, area}
- A set of attributes {p#, manufacturer, price} is a superkey in a relational schema PART={p#, name, price, manufacturer}
- A set of attributes {p#, manufacturer} is a superkey in a relational schema PART={p#, name, price, manufacturer}
- A set of attributes {p#} is a minimal key in a relational schema
  PART={p# name, price, manufacturer}

Let us look at more examples of superkeys and minimal keys.

A relational schema: player, consists of the attributes: player number (pnumber), first name, last name, date of birth (dob ), and team name.

A set of attributes, that consists of: player number, first name, last name, date of birth, and team, is a superkey in a relational schema: player. It is so, because a team never has two players with the same first name, last name and date of birth.

A set of attributes, that consists of: player number, first name, last name, and date of birth, is a superkey in a relational schema: player. It is so, because we do not have two or more playes participating in the competition such first name, last name, and date of birth is the same fro two or more players.

A set of attributes, that consists of: first name, last name, and date of birth, is a minimal key in a relational schema: player. It is so, because it is possible to have two players with the same first name and date of birth, two players with the same last name and date of birth and two players with the same first name and last name.

A relational schema shipment consists of the attributes: supplier number, (supplier num), part number (part num), delivery date, and delivery address. A set of attributes, that consists of: supplier number, part number, delivery date, and delivery address, is a minimal key in a relational schema: shipment.

Keys

More examples:
- A set of attributes {pnum, first-name,last-name, dob, team} is a superkey in a relational schema
  PLAYER={pnum, first-name, last-name, dob, team}
- A set of attributes {pnum, first-name,last-name, dob} is a superkey in a relational schema
  PLAYER={pnum, first-name, last-name, dob, team}
- A set of attributes {first-name,last-name, dob} is a minimal key in a relational schema
  PLAYER={pnum, first-name, last-name, dob, team}
- A set of attributes {supplier-num, part-num, delivery-date, delivery-address} is a minimal key in a relational schema
  SHIPMENT={supplier-num, part-num, delivery-date, delivery-address}

All minimal keys valid in a relational schema are also called as candidate keys.

A primary key is one of the candidate keys arbitrarily chosen by a database designer, to uniquely identify the rows in a relational table.

For example, a set of attributes that consists of: student number (snum) is a candidate key in a relational schema: student. A set of attributes, that consists of: first name, last name, and date of birth is a composite candidate key in a relational schema: student.

Then, a candidate key: student number, can be selected by a database designer as a primary key. It is also possible, that a candidate key that consists of the attributes: first name, last name, and date of birth, is selected by a database designer as a primary key.

Usually, when we have two or more candidate keys we pick as a primary kery the one that consists of the smaller number of attributes. It is because such primary key may be a foreign key in another relational table and then we minimise the redundancies created by the repetitions of the values of primary key as the values of foreign key in another relational table. On the other hand a longer primary key indicates a longer foreign key and it may reduce the number of join operations on the relational tables.

In the future, a relational schema will be denoted by R(A1, A2, ... An), and any key: Ai1, Ai2, ..., Aim, included in R will be denoted by (Ai1, Ai2, ..., Aim).

Keys

All minimal keys valid in a relational schema are also called as candidate keys
A primary key is one of the candidate keys arbitrarily chosen by a database designer to uniquely identify the rows in a relational table
Examples:
- A set of attributes {snum} and a set of attributes {first-name, last-name, date-of-birth} are the candidate keys in a relational schema STUDENT={snum, first-name, last-name, date-of-birth}
- A candidate key {snum} can be selected by a database designer as a primary key
- It is also possible that a candidate key {first-name, last-name, date-of-birth} can be selected by a database designer as a primary key
In the future a relational schema R ={A₁, A₂, ... A_n} will be denoted by R(A₁, A₂, ... A_n) and any sort of key {A_i1, A_i2, ... A_im} included in R will be denoted by (A_i1, A_i2, ... A_im)

The Relational Model of Data

Outline

Basic Concepts
Relational Table
Principles of Relational Model
Consistency Constraints

Keys
NULL
Referential Integrity Constraints
Domain Constraints

Summary

A NULL constraint says, that an attribute in a relational table may have no values at all.

Consider a conceptual schema, where a class: DEPARTMENT, is related to class: CHAIR, through an association: DEPARTMENT Has CHAIR. Because a multiplicity of the association on a chair class side is zero or one, then it may happen, that a department has no chairperson.

In such situation, some values in a column: chairperson, in a relational table: Department can be missing.

A lack of value in a slot of a relational table, is represented by a symbol: NULL.

An attribute: budget, is an optional attribute in a description of a class: DEPARTMENT. If a department has no budget, then such situation is represented by a NULL symbol in a column: budget, in a relational table: Department.

An Entity Integrity constraint says, that no column belonging to a primary key, or candidate key is allowed to take on NULL for any row. All commercial Database Management Systems allow NULLs for the candidate keys.

All commercial DBMSs do not allow for NULL within primary keys. It means that if a column belongs to a primary key then its NULL/NOT NULL constraint must be NOT NULL.

In practice, it is recommended to use NULL constraint as rarely as it is possible. A lack of values leads to three valued logic when search conditions are evaluated over the rows in a relational table.

NULL

A NULL constraint says that an attribute in a relational table may have no values at all

Step 2

With an exception saying that no column belonging to a primary key or candidate key is allowed to take on NULL for any row (it is also called as Entity Integrity constraint)
All commercial Database Management Systems allow NULL for candidate keys

The Relational Model of Data

Outline

Basic Concepts
Relational Table
Principles of Relational Model
Consistency Constraints

Keys
NULL
Referential Integrity Constraints
Domain Constraints

Summary

A referential integrity constraint says, that a set of attributes F, in a relational schema R, is called a foreign key, if a combination of values of attributes in F, in any row is required to either contain NULLs or else to match the value combination of a set of columns P, representing a candidate or primary key in some other relational schema S.

For example, an attribute supplier number (s#), in a relational schema: Shipment, is a foreign key referencing a primary key: supplier number (s#), in a relational schema Supplier. It means, that a column: supplier number (s#), in a relational table: Shipment, must contain only the values already inserted into a column: supplier number (s#,) in a relational table: Supplier. In such a case, a value in a column: supplier number (s#), in a table: Shipment, cannot be missing ( NULL ), because an attribute: supplier number, is a component of a primary key in a relational table: Shipment.

In another example, an attribute" part number (p#), in a relational schema: Shipment, is a foreign key referencing a primary key: part number (p#), in a relational schema Part. It means, that a column: part number (p#) in a relational table: Shipment, must contain only the values already inserted into a column: part number (p#), in a relational table: part. In such a case, a value in column: part number (p#), in a table: shipment, cannot be missing ( NULL ), because an attribute: supplier number, is a component of a primary key in a relational table: Shipment.

A relational database system uses enforces referential integrity constraints to avoid so called "dangling pointer references" commonly known from procedural programming languages like C, C++, Modula, etc that allow fro dynamic memory allocation. A dangling pointer reference happens when a fragment of transient memory addressed by a pointer is released and after that the pointer points to a memory address not available to an application. In a relational database system it would mean a value of foreign such that it is not a value of a referenced primary key. For example, a shipment where a value of an attribute part number (p#) is recorde in Shipment table and it is not recorded in Part table.

Referential Integrity Constraints

A set of attributes F in a relational schema R is a called a foreign key if the combination of values of attributes in F in any row is required to either contain NULLs or else to match the value combination of a set of columns P representing a candidate or primary key in some other relational schema S

A referential integrity rule is in force, if the columns of foreign keys in any relational table, either:

have NULLs in at least one column, that allows NULLs, or have no NULLs, and a combination of all its values is equal to the combination of primary key values in the other relational table.

It means, that the values in the columns: supplier number (s#) and part number (p#) in Shipment table, must be earlier inserted into the columns: supplier number (s#) and part number (p#), in the relational tables: Supplier and Part.

A value of a foreign key can be NULL. However, in this case the columns: supplier number (s#) and part number(p#), cannot have empty slots (NULLs) because both attributes are the members of a primary key, that consists of the attributes: supplier number and part number.

Referential Integrity Constraints

A referential integrity rule is in force if the columns of a foreign key in any relational table either:
- (1) have NULLs in at least one column that allows NULLs
- (2) have no NULLs and a combination of all its values is equal to the combination of primary key values in the other relational table

Now, we present two examples related to the referential integrity constrains.

First, assume, that a relational schema: BUILDING, consists of the attributes building number (bldg#), floor number (floor#), and name, has a primary key building number. A relational schema: ROOM that consists of the attributes building number (bldg#), room number (room#), and area, has a composite primary key that consists of building number and room number. Then, a set of attributes, that consists of: building number, and included a relational schema: ROOM, is a foreign key, that references a primary key: building number, in a relational schema: BUILDING.

In another example, assume, that a relational schema: STUDENT, consists of the attributes: student number (s#), first name, last name, and date of birth (dob). A relational schema: STUDENT, has a primary key: student number. Next, assume that a relational schema: SUBJECT, consists of the attributes: subject code (code), title, and total number of credits (credits). A relational schema: SUBJECT, has a primary key: subject code. Then, a relational schema: ENROLMENT, that consists of: student number (s#), subject code (code), and enrolment date (e date), has a foreign key: student number, referencing a primary key: student number, in a relational table: STUDENT.

A relational schema: ENROLMENT has another foreign key: subject code, that refers to a primary key: subject code, in a relational table: SUBJECT. It means, that we cannot add information about an enrolment of a subject, performed by a student, unless we first enter information about the student and about the subject.

Referential Integrity Constraints

Example of referential integrity constraint:
- A relational schema BUILDING(bldg#, floor#, name) has a primary key (bldg#)
- A relational schema ROOM(bldg#, room#, area} has a primary key (bldg#, room#)
- Then a set of attributes (bldg#) included in a schema ROOM is a foreign key that references a primary key (bldg#) in a schema BUILDING
Another example of referential integrity constraint:
- A relational schema STUDENT(s#, first-name, last-name, dob) has a primary key (s#)
- A relational schema SUBJECT(code, title, credits) has a primary key (code)
- Then a relational schema ENROLMENT(s#, code, edate) has a foregin key (s#) referencing primary key {s#} in a schema STUDENT and ...
- ... a relational schema ENROLMENT(s#, code, edate) has a foreign key (code) referencing primary key (code) in a schema SUBJECT

In a yet another example, related to referential integrity constraints, we consider a relational schema: ROOM, that consists of attributes: building number (bldg#), room number (room#), and area. A relational schema: ROOM, has a primary key, that consists of: building number and room number.

Another relational schema: LECTURER, consists of the attributes: employee number (emp#), first name, last name, building number, and room number. The values of attributes: building number and room number, determine a location of a lecturer.

Then, a set of attributes, that consists of: building number and room number, included in a relational schema: LECTURER, is a foreign key, that references a primary key in a relational schema: BUILDING, that consists of: building number and room number, in a relational schema: ROOM. It means, that we cannot assign a lecturer to a room, that is not described in a relational table: room.

In this case, it is still possible to enter information about a lecturer who is not assigned to any room yet. To do so, we remove the values of the attributes: building number and room number, from a description of a lecturer. We represent such fact with NULLs in the columns: building number and employee number, in a row that describes a lecture in a relational table: LECTURER.

Referential Integrity Constraints

Yet another example of referential integrity constraint:
- A relational schema ROOM=(bldg#, room#, area) has a primary key (bldg#, room#)
- A relational schema LECTURER=(emp#, first-name, last-name, bldg#, room#) has a primary key (emp#)
- Then a set of attributes (bldg#, room#) included in a relational schema LECTURER is a foreign key that references a primary key (bldg#, room#) in a schema ROOM

The Relational Model of Data

Outline

Basic Concepts
Relational Table
Principles of Relational Model
Consistency Constraints

Keys
NULL
Referential Integrity Constraints
Domain Constraints

Summary

A domain constraint is a condition imposed on the values of an attribute: A, that determines the values of dom(A), it means, a domain of attribute A.

For example, an attribute: student-number, is a sequence of 7 digits.

An attribute date of birth cannot have a value greater then todays date.

An attribute salary is a positive real number, it cannot have negative values.

Accordingly to the traditional believes, value of an attribute gender can be either, female, or male.

A value of an attribute credits can be either 6 or 12.

A value of an attribute first name, is a string of letters and blanks that starts from a capital letter.

A domain constraints can be used to eliminate the simple errors preformed at data entry. For example, a wrong position of a decimal dot, too many digits, too long string of characters, a value out of a given set of values and so on.

It is also possible to implement the domain constraints on the finite domain sets using referential integrity constraints. To do so we create a single column table and we insertt into such table all values that belong to a domain of an attribute. Then we make such column a primary key in a single column table. Then if a domain of another attribute in another relational table is the same as a domain created in a single column table the we nominate such as attribute as a foreign key referencing a primary key in a single column table.

Domain Constraints

A domain constraint is a condition imposed on the values of an attribute A that determines the values of dom(A), i.e. a domain of attribute A.
Examples:
- An attribute student-number is a sequence of 7 digits
- An attribute date-of-birth cannot have a value greater then todays date
- An attribute salary is a positive real number
- A value of an attribute gender can be either 'female' or 'male'
- A value of an attribute credits can be either 6 or 12
- A value of an attribute first-name is a string of letters an blanks that starts from a capital letter

The Relational Model of Data

Outline

Basic Concepts
Relational Table
Principles of Relational Model
Consistency Constraints
Summary

Accordingly to the principles of the relational data model, a database is a collection of relational tables.

A relational table consists of rows, also known as: tuples, and columns, also known as: attributes. A relational table is a set of rows. Such constraint can be abolished in a commercial database system when a table does not have enforced primary and candidate key constraints.

All attributes must have atomic values. Each attribute has a domain, a domain of an attribute is a set of acceptable values of the attribute. It means, that the values cannot have any internal structures, that can be used when storing or retrieving data from a relational table. In the commercial database systems it is possible to have columns that store complex values, e.g. dates, addresses, even XML or JSON documents.

A row in a relational table represents a relationship among a set of attributes.

A relational table is a subset of a Cartesian product of the attribute domains. Such definition is not completely consistent with the properties of relational tables. For example it does not allow for changing of an order of columns in a relational tables.

An attribute may have no value and a lack of value is denoted by the symbol NULL.

A relational table implements either a class of objects, or an association.

All identifiers in a conceptual schema are implemented as the keys in the relational tables.

Summary

A database is a collection of relational tables
A relational table consists of rows (tuples) and columns (attributes)
All attributes have atomic values
Each attribute has a domain, it means, that a set of acceptable values
A row represents a relationship among a set of attributes
A relational table is a subset of the Cartesian Product of attribute domains
An attribute may have no value (NULL)
A relational table implements either a class of objects or an association
All identifiers in a conceptual schema are implemented as the keys in the relational tables

Summary

A "tourist guide" through a "land of keys"
- Minimal key => the smallest key
- Superkey => minimal key + other attribute(s)
- Candidate key => any minimal key
- Primary key => one of candidate keys
- Foreign key => an attribute or set of attributes referencing a primary key or a candidate key in another or the same relational table

References

C. Coronel, S. Morris, A. Basta, M. Zgola, Data Management and Security, Chapter 2 Cengage Compose eBook, 2018, eBook: Data Management and Security, 1st Edition
T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 4 The Relational Model, Pearson Education Ltd, 2015