The Relational Model
by K. Yue
1. Introduction
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)
Null values:
Example:
Consider the attribute SpouseName. A Null value may mean:
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
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)
Exercise:
(1) Give a realistic example of a relation with two candidate keys. State the assumptions you have made.
More about keys:
Consider the example above. The attribute deptCode is a foreign key in the relation faculty, referencing department(deptCode)
Example:
MariaDB [toyu]> select s.*
-> from student s, department d
-> where s.minor = d.deptCode
-> and d.deptName = 'Information Technology';
+--------+---------+---------+-------+-------+---------+---------+
| stuId | lname | fname | major | minor | credits | advisor |
+--------+---------+---------+-------+-------+---------+---------+
| 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 |
| 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1015 |
+--------+---------+---------+-------+-------+---------+---------+
2 rows in set (0.00 sec)