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 (column) 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 (row) 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:

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

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:

mysql> SELECT * FROM faculty;
+-------+----------+----------+----------+---------------------+
| facId | fname    | lname    | deptCode | rank                |
+-------+----------+----------+----------+---------------------+
|  1011 | Paul     | Smith    | CSCI     | Professor           |
|  1012 | Mary     | Tran     | CSCI     | Associate Professor |
|  1013 | David    | Love     | CSCI     |                     |
|  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)

mysql> 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 |
| MATH     | Mathematics                  | CSE        |          7 |
+----------+------------------------------+------------+------------+
7 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.

Consider also:

mysql> SELECT * FROM enroll;
+--------+---------+-------+----------+
| stuId  | classId | grade | n_alerts |
+--------+---------+-------+----------+
| 100000 |   10000 | A     |        0 |
| 100001 |   10000 | NULL  |     NULL |
| 100002 |   10000 | B-    |        3 |
| 100000 |   10001 | A     |        2 |
| 100001 |   10001 | A-    |        0 |
| 100000 |   10002 | B+    |        1 |
| 100002 |   10002 | B+    |        2 |
| 100000 |   10003 | C     |        0 |
| 100002 |   10003 | D     |        4 |
| 100004 |   10003 | A     |        0 |
| 100005 |   10003 | NULL  |     NULL |
| 100000 |   10004 | A-    |        1 |
| 100004 |   10004 | B+    |     NULL |
| 100005 |   10004 | A-    |        0 |
| 100006 |   10004 | C+    |     NULL |
| 100005 |   10005 | A-    |        0 |
| 100006 |   10005 | A     |     NULL |
| 100005 |   10006 | B+    |     NULL |
| 100007 |   10007 | F     |        4 |
| 100008 |   10007 | C-    |        0 |
| 100007 |   10008 | A-    |        0 |
| 100000 |   11001 | D     |        4 |
+--------+---------+-------+----------+
22 rows in set (0.00 sec)

• stuid is a candidate key of the relation student. Assumptions made:
  1. Each tuple in the student relation represents an unique student.
  2. Each student has an unique student id, or no two students can have the same stuid.
• stuid is not a candidate key of the relation enroll. Assumptions made:
  1. Each tuple in the Enroll relation represents the enrollment of a student in a class, ending up with a grade.
  2. A student can take many classes.
• Note again 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.

Likewise, stuid is a foreign key in the relation Enroll: stuid references student(stuid)

• A stuid in the Enroll relation must be referring to a student in the database, identified by stuid in the relation student.
• 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.

Note that the student table has three foreign keys.

• The INNER JOIN operation can be used to join tables through foreign keys.

Example:

mysql> SELECT * FROM faculty;
+-------+----------+----------+----------+---------------------+
| facId | fname    | lname    | deptCode | rank                |
+-------+----------+----------+----------+---------------------+
|  1011 | Paul     | Smith    | CSCI     | Professor           |
|  1012 | Mary     | Tran     | CSCI     | Associate Professor |
|  1013 | David    | Love     | CSCI     | NULL                |
|  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)

mysql> 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 |
| MATH     | Mathematics                  | CSE        |          7 |
+----------+------------------------------+------------+------------+
7 rows in set (0.00 sec)

mysql> SELECT s.fname, s.lname, s.advisor, f.`rank`
    -> FROM student AS s INNER JOIN faculty AS 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           |
| Bill      | Ching   |    1018 | Assistant Professor |
| Linda     | King    |    1018 | Assistant Professor |
+-----------+---------+---------+---------------------+

SELECT * FROM student;
SELECT * FROM faculty;
SELECT s.fname, s.lname, s.advisor, f.`rank`
FROM student AS s INNER JOIN faculty AS f
    ON (s.advisor = f.facId);

mysql> 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  |       |      66 |    1017 |
| 100005 | Linda     | Johnson | CINF  | ENGL  |      13 |    1015 |
| 100006 | Lillian   | Johnson | CINF  | ITEC  |      18 |    1015 |
| 100007 | Ben       | Zico    | NULL  | NULL  |      16 |    NULL |
| 100008 | Bill      | Ching   | ARTS  | ENGL  |      90 |    1018 |
| 100009 | Linda     | King    | ARTS  | CSCI  |     125 |    1018 |
+--------+-----------+---------+-------+-------+---------+---------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM faculty;
+-------+----------+----------+----------+---------------------+
| facId | fname    | lname    | deptCode | rank                |
+-------+----------+----------+----------+---------------------+
|  1011 | Paul     | Smith    | CSCI     | Professor           |
|  1012 | Mary     | Tran     | CSCI     | Associate Professor |
|  1013 | David    | Love     | CSCI     |                     |
|  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)

mysql> SELECT s.fname, s.lname, s.advisor, f.`rank`
    -> FROM student AS s INNER JOIN faculty AS 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           |
| Bill      | Ching   |    1018 | Assistant Professor |
| Linda     | King    |    1018 | Assistant Professor |
+-----------+---------+---------+---------------------+
9 rows in set (0.00 sec)