The Relational Model

by K. Yue

1. Introduction

• The (theoretical) relational model is based on the concept of a relation.
• It is a set-theoretic model.
• Note that practical DBMS do not implement the pure relational model precisely.
• An attribute is a name.
• A domain is a set of values an attribute can take.
  • The values of the data type of the attribute.
• The value of an attribute should be atomic (cannot be divided into smaller components with individual meanings): First Normal Form.
• Null may or may not be an acceptable value for an attribute.
• A relation schema, R, is a set of attributes A₁, A₂,…,A_n with their domains D₁, D₂,…, D_n.
• A tuple is a set of mapping of a set of attributes to a set of values: A_i -> d_i where d_i belongs to D_i.
• A relation (instance) is a set of tuples.
• The degree (or arity) of a relation is the number of attributes in its schema

Example:

MariaDB [toyu]> select * from student;
+--------+-----------+---------+-------+-------+---------+---------+
| stuId  | lname     | fname   | major | minor | credits | advisor |
+--------+-----------+---------+-------+-------+---------+---------+
| 100000 | Tony      | Hawk    | CSCI  | CINF  |      40 |    1011 |
| 100001 | Mary      | Hawk    | CSCI  | CINF  |      35 |    1011 |
| 100002 | David     | Hawk    | CSCI  | ITEC  |      66 |    1011 |
| 100003 | Catherine | Lim     | ITEC  | CINF  |      20 |    1017 |
| 100004 | Larry     | Johnson | ITEC  | NULL  |      66 |    1017 |
| 100005 | Linda     | Johnson | CINF  | ENGL  |      13 |    1015 |
| 100006 | Lillian   | Johnson | CINF  | ITEC  |      18 |    1015 |
| 100007 | Ben       | Zico    | NULL  | NULL  |      16 |    1014 |
| 100008 | Bill      | Ching   | ARTS  | ENGL  |      90 |    1018 |
| 100009 | Linda     | King    | ARTS  | CSCI  |     125 |    1018 |
+--------+-----------+---------+-------+-------+---------+---------+
10 rows in set (0.00 sec)

• Some important properties of a relation:
  • There is no duplicate tuple.
    • Because a relation is a set.
    • Consequence: does not support 'object identity' directly.
  • The relational model is known to be 'value-oriented'.
  • A table and a relation is not exactly the same. "Table" is a more generic term.
• Tuples within a relation are unordered.
  • Changing the order of displaying the tuples does not change (the meaning of) the relation.
• Attributes within a relation schema are unordered.
  • Changing the order showing the attributes within a relation schema does not change the relation.

Example:

+--------+-----------+---------+-------+-------+---------+---------+
| stuId  | lname     | fname   | major | minor | credits | advisor |
+--------+-----------+---------+-------+-------+---------+---------+
| 100000 | Tony      | Hawk    | CSCI  | CINF  |      40 |    1011 |
| 100001 | Mary      | Hawk    | CSCI  | CINF  |      35 |    1011 |
| 100002 | David     | Hawk    | CSCI  | ITEC  |      66 |    1011 |
| 100003 | Catherine | Lim     | ITEC  | CINF  |      20 |    1017 |
| 100004 | Larry     | Johnson | ITEC  | NULL  |      66 |    1017 |
| 100005 | Linda     | Johnson | CINF  | ENGL  |      13 |    1015 |
| 100006 | Lillian   | Johnson | CINF  | ITEC  |      18 |    1015 |
| 100007 | Ben       | Zico    | NULL  | NULL  |      16 |    1014 |
| 100008 | Bill      | Ching   | ARTS  | ENGL  |      90 |    1018 |
| 100009 | Linda     | King    | ARTS  | CSCI  |     125 |    1018 |
+--------+-----------+---------+-------+-------+---------+---------+

and

+--------+-----------+---------+---------+---------+-------+-------+
| stuId  | fname     | lname   | credits | advisor | major | minor |
+--------+-----------+---------+---------+---------+-------+-------+
| 100008 | Bill      | Ching   |      90 |    1018 | ARTS  | ENGL  |
| 100002 | David     | Hawk    |      66 |    1011 | CSCI  | ITEC  |
| 100001 | Mary      | Hawk    |      35 |    1011 | CSCI  | CINF  |
| 100000 | Tony      | Hawk    |      40 |    1011 | CSCI  | CINF  |
| 100004 | Larry     | Johnson |      66 |    1017 | ITEC  | NULL  |
| 100006 | Lillian   | Johnson |      18 |    1015 | CINF  | ITEC  |
| 100005 | Linda     | Johnson |      13 |    1015 | CINF  | ENGL  |
| 100009 | Linda     | King    |     125 |    1018 | ARTS  | CSCI  |
| 100003 | Catherine | Lim     |      20 |    1017 | ITEC  | CINF  |
| 100007 | Ben       | Zico    |      16 |    1014 | NULL  | NULL  |
+--------+-----------+---------+---------+---------+-------+-------+

stores the same information.
 

Null values:

• Generally used for representing missing information.
• DBSM provides a method to test whether a value is null or not (IS NULL and IS NOT NULL)
• Three possible interpretations:
  1. Not applicable.
  2. Missing value.
  3. No information at all.

Example:

Consider the attribute SpouseName. A Null value may mean:

1. not applicable: the person is not married.
2. missing information: the person is married but we do not have the name of the spouse.
3. no information at all: we do not know whether the person is married or not.

How do we distinguish between the three meanings of the null value in this case?

By using an extra attribute, such as MaritalStatus.

2. Keys

• A set of attributes K is a candidate key of a relation R if it minimally identifies a tuple at any time:
  1. Uniqueness: No two tuples of R have the same value of K.
  2. Minimality: No proper subset of K has the uniqueness property.
• A candidate key is a property of the semantic (meaning) of a relation.
• In other words, a candidate key is the result of the requirements of an application.
• A relation always has at least one candidate keys. Why?
• A set of attributes may be a candidate key for a relation R but not a candidate key of another relation S.
• For any relation instance, the candidate key of a tuple must have an unique value.

Example:

Consider:

MariaDB [toyu]> select * from department;
+----------+------------------------------+------------+------------+
| deptCode | deptName                     | schoolCode | numFaculty |
+----------+------------------------------+------------+------------+
| ACCT     | Accounting                   | BUS        |         10 |
| ARTS     | Arts                         | HSH        |          5 |
| CINF     | Computer Information Systems | CSE        |          5 |
| CSCI     | Computer Science             | CSE        |         12 |
| ENGL     | English                      | HSH        |         12 |
| ITEC     | Information Technology       | CSE        |          4 |
+----------+------------------------------+------------+------------+
6 rows in set (0.00 sec)

MariaDB [toyu]> select * from faculty;
+-------+----------+----------+----------+---------------------+
| facId | lname    | fname    | deptCode | rank                |
+-------+----------+----------+----------+---------------------+
|  1011 | Paul     | Smith    | CSCI     | Professor           |
|  1012 | Mary     | Tran     | CSCI     | Associate Professor |
|  1013 | David    | Love     | CSCI     | Associate Professor |
|  1014 | Sharon   | Mannes   | CSCI     | Assistant Professor |
|  1015 | Daniel   | Kim      | CINF     | Professor           |
|  1016 | Andrew   | Byre     | CINF     | Associate Professor |
|  1017 | Deborah  | Gump     | ITEC     | Professor           |
|  1018 | Art      | Allister | ARTS     | Assistant Professor |
|  1019 | Benjamin | Yu       | ITEC     | Lecturer            |
|  1020 | Katrina  | Bajaj    | ENGL     | Lecturer            |
|  1021 | Jorginlo | Neymar   | ACCT     | Assistant Professor |
+-------+----------+----------+----------+---------------------+
11 rows in set (0.00 sec)

• deptCode is a candidate key of the relation department. Assumptions made:
  1. Each tuple in the department relation represents an unique department.
  2. Each department has an unique code or no two departments can have the same code.
• deptName is also a candidate key of the relation department. Assumptions made:
  1. Each tuple in the department relation represents an unique department.
  2. Each department has an unique name or no two departments can have the same name.
• deptCode is not a candidate key of the relation faculty. Assumptions made:
  1. Each tuple in the faculty relation represents a faculty member.
  2. Each faculty serves only one department.
  3. A department may have many faculty members.
• Note that candidate keys are a property of a relation.
• A set of attributes can be a candidate key for relation R1, but not R2.

Exercise:

(1) Give a realistic example of a relation with two candidate keys. State the assumptions you have made.

More about keys:

• Whether a set of an attribute is a key or not depend on the semantic (meaning) of the relation on modeling the application.
• A primary key is a selected candidate key for a relation in the DBMS. It is used for practical purpose and does not have a special meaning in the theory of the relational model.
• An alternate key is a candidate key that is not the primary key.
• A key is simple if it has only one attribute
• A key is composite if it has more than one attributes.
• Every relation has at least one candidate key.
• An attribute that appears in a candidate key is a prime attribute (or key attribute).
• An attribute that does not appears in any candidate key is a non-prime (non-key) attribute.
• A foreign key of a relation is a set of attributes that is a candidate key in a foreign relation. That foreign relation is sometimes called the parent (or referenced, home) table of the foreign key.
• A foreign key may or may not have null value. It depends on the problem requirements.

Consider the example above. The attribute deptCode is a foreign key in the relation faculty, referencing department(deptCode)

• A deptCode in the department relation must be referring to a department in the database, identified by deptCode in the relation department (referential integrity).
• In Relational DB, rows (data) from different tables are linked together using foreign key.
• A foreign key of a child table links to a primary key in the parent table.

Example:

MariaDB [toyu]> select * from faculty;
+-------+----------+----------+----------+---------------------+
| facId | fname    | lname    | deptCode | rank                |
+-------+----------+----------+----------+---------------------+
|  1011 | Paul     | Smith    | CSCI     | Professor           |
|  1012 | Mary     | Tran     | CSCI     | Associate Professor |
|  1013 | David    | Love     | CSCI     | Associate Professor |
|  1014 | Sharon   | Mannes   | CSCI     | Assistant Professor |
|  1015 | Daniel   | Kim      | CINF     | Professor           |
|  1016 | Andrew   | Byre     | CINF     | Associate Professor |
|  1017 | Deborah  | Gump     | ITEC     | Professor           |
|  1018 | Art      | Allister | ARTS     | Assistant Professor |
|  1019 | Benjamin | Yu       | ITEC     | Lecturer            |
|  1020 | Katrina  | Bajaj    | ENGL     | Lecturer            |
|  1021 | Jorginlo | Neymar   | ACCT     | Assistant Professor |
+-------+----------+----------+----------+---------------------+
11 rows in set (0.00 sec)

MariaDB [toyu]> select * from department;
+----------+------------------------------+------------+------------+
| deptCode | deptName                     | schoolCode | numFaculty |
+----------+------------------------------+------------+------------+
| ACCT     | Accounting                   | BUS        |         10 |
| ARTS     | Arts                         | HSH        |          5 |
| CINF     | Computer Information Systems | CSE        |          5 |
| CSCI     | Computer Science             | CSE        |         12 |
| ENGL     | English                      | HSH        |         12 |
| ITEC     | Information Technology       | CSE        |          4 |
+----------+------------------------------+------------+------------+
6 rows in set (0.00 sec)

MariaDB [toyu]> select f.fname, f.lname, f.deptCode, d.deptName
    -> from faculty f join department d on (f.deptCode = d.deptCode);
+----------+----------+----------+------------------------------+
| fname    | lname    | deptCode | deptName                     |
+----------+----------+----------+------------------------------+
| Paul     | Smith    | CSCI     | Computer Science             |
| Mary     | Tran     | CSCI     | Computer Science             |
| David    | Love     | CSCI     | Computer Science             |
| Sharon   | Mannes   | CSCI     | Computer Science             |
| Daniel   | Kim      | CINF     | Computer Information Systems |
| Andrew   | Byre     | CINF     | Computer Information Systems |
| Deborah  | Gump     | ITEC     | Information Technology       |
| Art      | Allister | ARTS     | Arts                         |
| Benjamin | Yu       | ITEC     | Information Technology       |
| Katrina  | Bajaj    | ENGL     | English                      |
| Jorginlo | Neymar   | ACCT     | Accounting                   |
+----------+----------+----------+------------------------------+
11 rows in set (0.00 sec)

MariaDB [toyu]> select * from student;
+--------+-----------+---------+-------+-------+---------+---------+
| stuId  | fname     | lname   | major | minor | credits | advisor |
+--------+-----------+---------+-------+-------+---------+---------+
| 100000 | Tony      | Hawk    | CSCI  | CINF  |      40 |    1011 |
| 100001 | Mary      | Hawk    | CSCI  | CINF  |      35 |    1011 |
| 100002 | David     | Hawk    | CSCI  | ITEC  |      66 |    1011 |
| 100003 | Catherine | Lim     | ITEC  | CINF  |      20 |    1017 |
| 100004 | Larry     | Johnson | ITEC  | NULL  |      66 |    1017 |
| 100005 | Linda     | Johnson | CINF  | ENGL  |      13 |    1015 |
| 100006 | Lillian   | Johnson | CINF  | ITEC  |      18 |    1015 |
| 100007 | Ben       | Zico    | NULL  | NULL  |      16 |    1014 |
| 100008 | Bill      | Ching   | ARTS  | ENGL  |      90 |    1018 |
| 100009 | Linda     | King    | ARTS  | CSCI  |     125 |    1018 |
+--------+-----------+---------+-------+-------+---------+---------+
10 rows in set (0.00 sec)

MariaDB [toyu]> select * from faculty;
+-------+----------+----------+----------+---------------------+
| facId | fname    | lname    | deptCode | rank                |
+-------+----------+----------+----------+---------------------+
|  1011 | Paul     | Smith    | CSCI     | Professor           |
|  1012 | Mary     | Tran     | CSCI     | Associate Professor |
|  1013 | David    | Love     | CSCI     | Associate Professor |
|  1014 | Sharon   | Mannes   | CSCI     | Assistant Professor |
|  1015 | Daniel   | Kim      | CINF     | Professor           |
|  1016 | Andrew   | Byre     | CINF     | Associate Professor |
|  1017 | Deborah  | Gump     | ITEC     | Professor           |
|  1018 | Art      | Allister | ARTS     | Assistant Professor |
|  1019 | Benjamin | Yu       | ITEC     | Lecturer            |
|  1020 | Katrina  | Bajaj    | ENGL     | Lecturer            |
|  1021 | Jorginlo | Neymar   | ACCT     | Assistant Professor |
+-------+----------+----------+----------+---------------------+
11 rows in set (0.00 sec)

MariaDB [toyu]> select s.fname, s.lname, s.advisor, f.rank
    -> from student s join faculty f on (s.advisor = f.facId);
+-----------+---------+---------+---------------------+
| fname     | lname   | advisor | rank                |
+-----------+---------+---------+---------------------+
| Tony      | Hawk    |    1011 | Professor           |
| Mary      | Hawk    |    1011 | Professor           |
| David     | Hawk    |    1011 | Professor           |
| Catherine | Lim     |    1017 | Professor           |
| Larry     | Johnson |    1017 | Professor           |
| Linda     | Johnson |    1015 | Professor           |
| Lillian   | Johnson |    1015 | Professor           |
| Ben       | Zico    |    1014 | Assistant Professor |
| Bill      | Ching   |    1018 | Assistant Professor |
| Linda     | King    |    1018 | Assistant Professor |
+-----------+---------+---------+---------------------+
10 rows in set (0.00 sec)