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.
• 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:

CREATE TABLE IF NOT EXISTS Class (
    classId     INT NOT NULL AUTO_INCREMENT,
    courseId    INT NOT NULL,
    semester    VARCHAR(10) NOT NULL,
    year        DECIMAL(4,0) NOT NULL,
    facId       INT NOT NULL,
    room        VARCHAR(6),
    CONSTRAINT Class_classId_pk PRIMARY KEY (classId),
    CONSTRAINT Class_courseId_fk FOREIGN KEY (courseId)
        REFERENCES Course(courseId) ON DELETE CASCADE,
    CONSTRAINT Class_facId_fk FOREIGN KEY (facId)
        REFERENCES Faculty (facId) ON DELETE CASCADE
);

INSERT INTO Class VALUES
    (10000,2000,'Fall',2019,1011,'D241'),
    (10001,2001,'Fall',2019,1011,'D242'),
    (10002,2002,'Fall',2019,1012,'D136'),
    (10003,2020,'Fall',2019,1014,'D241'),
    (10004,2021,'Fall',2019,1014,'D241'),
    (10005,2040,'Fall',2019,1015,'D237'),
    (10006,2041,'Fall',2019,1019,'D217'),
    (10007,2060,'Fall',2019,1020,'B101'),
    (10008,2080,'Fall',2019,1018,'D241'),
    (11000,2000,'Spring',2020,1011,'D241'),
    (11001,2001,'Spring',2020,1013,'D242'),
    (11002,2002,'Spring',2020,1013,'D136'),
    (11003,2020,'Spring',2020,1016,'D217'),
    (11004,2061,'Spring',2020,1018,'B101');

mysql> SELECT * FROM class;
+---------+----------+----------+------+-------+------+
| classId | courseId | semester | year | facId | room |
+---------+----------+----------+------+-------+------+
|   10000 |     2000 | Fall     | 2019 |  1011 | D241 |
|   10001 |     2001 | Fall     | 2019 |  1011 | D242 |
|   10002 |     2002 | Fall     | 2019 |  1012 | D136 |
|   10003 |     2020 | Fall     | 2019 |  1014 | D241 |
|   10004 |     2021 | Fall     | 2019 |  1014 | D241 |
|   10005 |     2040 | Fall     | 2019 |  1015 | D237 |
|   10006 |     2041 | Fall     | 2019 |  1019 | D217 |
|   10007 |     2060 | Fall     | 2019 |  1020 | B101 |
|   10008 |     2080 | Fall     | 2019 |  1018 | D241 |
|   11000 |     2000 | Spring   | 2020 |  1011 | D241 |
|   11001 |     2001 | Spring   | 2020 |  1013 | D242 |
|   11002 |     2002 | Spring   | 2020 |  1013 | D136 |
|   11003 |     2020 | Spring   | 2020 |  1016 | D217 |
|   11004 |     2061 | Spring   | 2020 |  1018 | B101 |
+---------+----------+----------+------+-------+------+
 

{classId: 10004, courseId: 2021, semester: 'Fall', year: 2019, facId: 1014, room: 'D241'} is a tuple.

It can also be represented as:

{classId: 10004, facId: 1014, room: 'D241', semester: 'Fall', year: 2019, courseId: 2021}

or

{10004, 2021, 'Fall', 2019, 1014, 'D241'} if the attribute names are assumed.

or using a more computer science-style notation.

(10004, 2021, 'Fall', 2019, 1014, 'D241')

Identify some examples of the terms in the class table above as much as possible.

• 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:

+---------+----------+----------+------+-------+------+
| classId | courseId | semester | year | facId | room |
+---------+----------+----------+------+-------+------+
|   10000 |     2000 | Fall     | 2019 |  1011 | D241 |
|   10001 |     2001 | Fall     | 2019 |  1011 | D242 |
|   10002 |     2002 | Fall     | 2019 |  1012 | D136 |
|   10003 |     2020 | Fall     | 2019 |  1014 | D241 |
|   10004 |     2021 | Fall     | 2019 |  1014 | D241 |
|   10005 |     2040 | Fall     | 2019 |  1015 | D237 |
|   10006 |     2041 | Fall     | 2019 |  1019 | D217 |
|   10007 |     2060 | Fall     | 2019 |  1020 | B101 |
|   10008 |     2080 | Fall     | 2019 |  1018 | D241 |
|   11000 |     2000 | Spring   | 2020 |  1011 | D241 |
|   11001 |     2001 | Spring   | 2020 |  1013 | D242 |
|   11002 |     2002 | Spring   | 2020 |  1013 | D136 |
|   11003 |     2020 | Spring   | 2020 |  1016 | D217 |
|   11004 |     2061 | Spring   | 2020 |  1018 | B101 |
+---------+----------+----------+------+-------+------+

and

+---------+------+----------+----------+------+-------+
| classId | year | semester | courseId | room | facId |
+---------+------+----------+----------+------+-------+
|   11004 | 2020 | Spring   |     2061 | B101 |  1018 |
|   10007 | 2019 | Fall     |     2060 | B101 |  1020 |
|   10002 | 2019 | Fall     |     2002 | D136 |  1012 |
|   11002 | 2020 | Spring   |     2002 | D136 |  1013 |
|   11003 | 2020 | Spring   |     2020 | D217 |  1016 |
|   10006 | 2019 | Fall     |     2041 | D217 |  1019 |
|   10005 | 2019 | Fall     |     2040 | D237 |  1015 |
|   10000 | 2019 | Fall     |     2000 | D241 |  1011 |
|   11000 | 2020 | Spring   |     2000 | D241 |  1011 |
|   10003 | 2019 | Fall     |     2020 | D241 |  1014 |
|   10004 | 2019 | Fall     |     2021 | D241 |  1014 |
|   10008 | 2019 | Fall     |     2080 | D241 |  1018 |
|   10001 | 2019 | Fall     |     2001 | D242 |  1011 |
|   11001 | 2020 | Spring   |     2001 | D242 |  1013 |
+---------+------+----------+----------+------+-------+


stores the same information.

The second talbe can be obtained by:

SELECT classId, year, semester, courseId, room, facId
FROM class
ORDER BY room, facId;

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.

• SQL provides a mechanism to check whether an attribute is null: IS 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.

Classroom Exercise:

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

(2) A relation R has an arity of 4. What are the possible minimum and maximum number of superkeys of R? . What are the possible minimum and maximum number of candidate keys of R?

What about the general case?

More about keys:

• Whether a set of attributes 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 (of how the relation is stored physically) 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 superkey of a relation is a set of attributes that uniquely identify a row (uniqueness). It may not be minimal. (A Super key may have attributes not needed for unique identification.)
• 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) 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)