An Introduction to
the Relational Model and SQL

by K. Yue

1. Introduction

• There are many data models employed by database systems.
• Data models are the fundamental and power concept (FPC) organizing how we think about using a particular type of database system.
• It is important to recognize the basic data structures used by these models.
• Examples:
  1. Hierarchical model (older): tree
  2. Network model (older): graph
  3. Object-Oriented model: directed graph
  4. XML: tree with many different types of nodes, plus sets of attributes.
  5. mongoDB: document
  6. Excel: table, and to be more exact, a two dimensional array.
• Some advantages of the relational model and relational DBMS:
  1. Strong mathematical foundation
  2. Simple
  3. Data independence: separation of the logical and physical databases.
  4. Strong design theory
  5. Strong practical support and community
  6. High popularity
• Some disadvantages of the relational model and relational DBMS:
  1. The data model may not match the problem requirements well.
  2. May not scale well.
  3. May be too restrictive.
• The basic relational data model in layman terms:
  1. A database is composed of a collection of tables (relations).
  2. A table contains many rows (tuples) and columns (attributes)
  3. Each row contains many column values.
  4. Every row of a table has the same columns.
  5. Values of the same column have the same data type.
  6. A candidate key of a table is a minimal identifier of a row in the table.
  7. A primary key is a selected candidate key (for storing the table).
  8. A foreign key of a relation refers to a primary key of another relation (known as the parent table).

Example:

Toyu: A drastically simplified university.

Relationship Diagram (in MS Access):

ER Diagram (Entity-Relationship Diagram).

MS Access: toyu.accdb

MySQL creation script: Createtoyu.sql.txt.

• A simple SELECT statement in SQL an be used to retrieve data from a table.

mysql> SELECT * FROM grade;
+-------+------------+
| grade | gradePoint |
+-------+------------+
| A     |     4.0000 |
| A-    |     3.6667 |
| B     |     3.0000 |
| B+    |     3.3333 |
| B-    |     2.6667 |
| C     |     2.0000 |
| C+    |     2.3333 |
| C-    |     1.6667 |
| D     |     1.0000 |
| D+    |     1.3333 |
| D-    |     0.6667 |
| F     |     0.0000 |
| WX    |       NULL |
+-------+------------+
13 rows in set (0.00 sec)

mysql> SELECT * FROM school;
+------------+-------------------------------+
| schoolCode | schoolName                    |
+------------+-------------------------------+
| BUS        | Business                      |
| EDU        | Education                     |
| HSH        | Human Sciences and Humanities |
| CSE        | Science and Engineering       |
+------------+-------------------------------+
4 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 * 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 course;
+----------+--------+--------+-------------------------------------+---------+
| courseId | rubric | number | name                                | credits |
+----------+--------+--------+-------------------------------------+---------+
|     2000 | CSCI   | 3333   | Data Structures                     |       3 |
|     2001 | CSCI   | 4333   | Design of Database Systems          |       3 |
|     2002 | CSCI   | 5333   | DBMS                                |       3 |
|     2020 | CINF   | 3321   | Introduction to Information Systems |       3 |
|     2021 | CINF   | 4320   | Web Application Development         |       3 |
|     2040 | ITEC   | 3335   | Database Development                |       3 |
|     2041 | ITEC   | 3312   | Introduction to Scripting           |       3 |
|     2060 | ENGL   | 1410   | English I                           |       4 |
|     2061 | ENGL   | 1311   | English II                          |       3 |
|     2080 | ARTS   | 3311   | Hindu Arts                          |       3 |
|     2090 | ACCT   | 3333   | Managerial Accounting               |       3 |
+----------+--------+--------+-------------------------------------+---------+
11 rows in set (0.00 sec)

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 |
+---------+----------+----------+------+-------+------+
14 rows in set (0.00 sec)

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 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)
 

Example:

Consider the following instance of the table 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)

• The name of the table is 'department'.
• There are currently six rows in the relation instance.
• Each row in the table 'department' has the same columns: deptCode, deptName, schoolCode and numFaculty. This is the structure of the relation.
• The relation schema for class: department(deptCode, deptName, schoolCode, numFaculty).
• Each column has a specific data type. Examples:
  1. deptCode: VARCHAR(4)
  2. deptName: VARCHAR(30)
  3. schoolCode: VARCHAR(3)
  4. numFaculty: TINYINT
• We said the domain of the column deptCode is varchar(4).
• Domain may be understood as the values allowed by the data type.
• Thus, you may consider the relation schema and column domains the 'structure' of the database.
• The structures usually do not change much, just like the structure of a building.
• However, the structure can be changed (just like the structure of a building: remodeling).
• On the other hand, the content of a table (called a relation instance) can be changed from time to time.

mysql> desc department;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| deptCode   | varchar(4)  | NO   | PRI | NULL    |       |
| deptName   | varchar(30) | YES  | UNI | NULL    |       |
| schoolCode | varchar(3)  | YES  | MUL | NULL    |       |
| numFaculty | tinyint(4)  | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
 

Example:

After inserting a new row:

INSERT INTO department VALUES ('PHYS', 'Physics','CSE',3); 

The new relation instance of the table department:

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 |
| PHYS     | Physics                      | CSE        |          3 |
+----------+------------------------------+------------+------------+
8 rows in set (0.00 sec)

After:

DELETE FROM department WHERE deptCode = 'PHYS';

the relation instance reverts back.

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)
 

• Note the terms table and relation can be ambiguous. They may mean:
  1. the relation schema: the 'structure' of the table.
  2. the relation instance: actual data in the relation at a specific time.
• Note that relations are a logical concept to reason with.
• Relations must eventually stored in the file systems to ensure persistence.
• A row may be stored as a record, in which a column is a field of the record.
• Conceptually, a record may be used to represent (or store information about) an entity, a relationship, or a concept
  • A student record may represent a student (entity)
  • An enrollment record may represent a relationship: a student taking a class.
• Two tables may be 'connected' through unique identifiers, known as foreign keys. Example:
  • The enrollment table is related to the student table through stuId:

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 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)

2.SQL and MySQL

• The standard query language for RDBMS is Structured Query Language (SQL).
• We use MySQL 8.0 (MariaDB is mostly compatible) in this class.

MySQL

• MySQL is the most popular open source RDBMS.
• Install the latest version (8.0) from https://www.mysql.com/. This is preferred.
• MySQL uses a client server model:
  • The MySQL server listens to a port (by default 3306) for SQL statements.
  • Users use a SQL client to submit SQL to the MySQL server for execution.
• MySQL Client examples:
  1. MySQL Command Prompt: text-based
  2. MySQL Workbench: GUI based MySQL client
  3. HeidiSQL: general purpose SQL client work with many RDBMS servers.

Simple SQL

• SQL is a rich high level language.
• For the time being, we focus on the basic SELECT statement to get some taste of SQL and RDBMS.

select distinct <<result_columns>>
from <<source_tables>> -- conceptually join to form a large table
where <<conditions_for_inclusion>>

1. <<source_tables>>: the source tables to gather the result data
2. <<conditions_for_inclusion>>: the conditions to be satisfied for results to be included and the conditions the tables should be connected together.
3. <<result_columns>>: the result columns or expressions desired to be displayed.

Examples:

Using toyu:

mysql> -- Department codes and their names
mysql> SELECT DISTINCT deptCode, deptName
    -> FROM department;
+----------+------------------------------+
| deptCode | deptName                     |
+----------+------------------------------+
| ACCT     | Accounting                   |
| ARTS     | Arts                         |
| CINF     | Computer Information Systems |
| CSCI     | Computer Science             |
| ENGL     | English                      |
| ITEC     | Information Technology       |
| MATH     | Mathematics                  |
+----------+------------------------------+
7 rows in set (0.00 sec)

mysql>
mysql> -- Faculty information from the department 'CSCI'
mysql> SELECT DISTINCT *
    -> FROM faculty
    -> WHERE deptCode = 'CSCI';
+-------+--------+--------+----------+---------------------+
| 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 |
+-------+--------+--------+----------+---------------------+
4 rows in set (0.00 sec)

mysql>
mysql> -- Faculty names from the department 'CSCI'
mysql> SELECT DISTINCT fname, lname
    -> FROM faculty
    -> WHERE deptCode = 'CSCI';
+--------+--------+
| fname  | lname  |
+--------+--------+
| Paul   | Smith  |
| Mary   | Tran   |
| David  | Love   |
| Sharon | Mannes |
+--------+--------+
4 rows in set (0.00 sec)

mysql>
mysql> -- Associate professor names from the department 'CSCI'
mysql> SELECT DISTINCT fname, lname
    -> FROM faculty
    -> WHERE deptCode = 'CSCI'
    -> AND `rank` = 'Associate Professor';
+-------+-------+
| fname | lname |
+-------+-------+
| Mary  | Tran  |
+-------+-------+
1 row in set (0.00 sec)

mysql>
mysql> -- Department names and numbers of faculty with the numbers
mysql> SELECT DISTINCT deptName, numFaculty
    -> FROM department
    -> WHERE numFaculty >= 10;
+------------------+------------+
| deptName         | numFaculty |
+------------------+------------+
| Accounting       |         10 |
| Computer Science |         12 |
| English          |         12 |
+------------------+------------+
3 rows in set (0.00 sec)

mysql>
mysql> -- Names of all faculty members and their
mysql> -- department names and ranks.
mysql> SELECT DISTINCT faculty.fName, faculty.lname,
    ->     department.deptName, faculty.`rank`
    -> FROM department, faculty
    -> WHERE faculty.deptCode = department.deptCode;
+----------+----------+------------------------------+---------------------+
| fName    | lname    | deptName                     | rank                |
+----------+----------+------------------------------+---------------------+
| Jorginlo | Neymar   | Accounting                   | Assistant Professor |
| Art      | Allister | Arts                         | Assistant Professor |
| Daniel   | Kim      | Computer Information Systems | Professor           |
| Andrew   | Byre     | Computer Information Systems | Associate Professor |
| Paul     | Smith    | Computer Science             | Professor           |
| Mary     | Tran     | Computer Science             | Associate Professor |
| David    | Love     | Computer Science             |                     |
| Sharon   | Mannes   | Computer Science             | Assistant Professor |
| Katrina  | Bajaj    | English                      | Lecturer            |
| Deborah  | Gump     | Information Technology       | Professor           |
| Benjamin | Yu       | Information Technology       | Lecturer            |
+----------+----------+------------------------------+---------------------+
11 rows in set (0.00 sec)

mysql>
mysql> -- Names of all faculty members and their
mysql> -- department names and ranks in the school CSE
mysql> SELECT DISTINCT faculty.fName, faculty.lname,
    ->     department.deptName, faculty.`rank`
    -> FROM department, faculty
    -> WHERE faculty.deptCode = department.deptCode
    -> AND department.schoolCode = 'CSE';
+----------+--------+------------------------------+---------------------+
| fName    | lname  | deptName                     | rank                |
+----------+--------+------------------------------+---------------------+
| Daniel   | Kim    | Computer Information Systems | Professor           |
| Andrew   | Byre   | Computer Information Systems | Associate Professor |
| Paul     | Smith  | Computer Science             | Professor           |
| Mary     | Tran   | Computer Science             | Associate Professor |
| David    | Love   | Computer Science             |                     |
| Sharon   | Mannes | Computer Science             | Assistant Professor |
| Deborah  | Gump   | Information Technology       | Professor           |
| Benjamin | Yu     | Information Technology       | Lecturer            |
+----------+--------+------------------------------+---------------------+
8 rows in set (0.00 sec)


Classroom Exercises:

Example. Show all student names.

Expected Result:

+---------+-----------+
| lname   | fname     |
+---------+-----------+
| Hawk    | Tony      |
| Hawk    | Mary      |
| Hawk    | David     |
| Lim     | Catherine |
| Johnson | Larry     |
| Johnson | Linda     |
| Johnson | Lillian   |
| Zico    | Ben       |
| Ching   | Bill      |
| King    | Linda     |
+---------+-----------+
10 rows in set (0.00 sec)

Analysis:

[1] Sources: student
[2] Conditions: none
[3] Output fields: lname, fname

SELECT DISTINCT s.lname, s.fname -- [3]
FROM student s; -- [1]

Example. List the last names and first names of students minoring in CINF and having 1011 as faculty advisor.

+-------+-------+
| lname | fname |
+-------+-------+
| Hawk  | Tony  |
| Hawk  | Mary  |
+-------+-------+
2 rows in set (0.01 sec)

[Analysis]

[1] Source tables: student

[2] Conditions:

• minor = 'CINF'
• advisor = 1011

[3] Output columns:

• lname
• fname

[suggested solution of sample question]

SELECT DISTINCT s.lname, s.fname
FROM student s
WHERE minor = 'CINF'
AND advisor = 1011;

1. All student names and the major codes.
2. All student names and the major department names.
3. All student names enrolled in the class with id 10003.
4. Show all information of students majoring in ‘MATH’.
5. Show the names and credits of students majoring in 'MATH'.
6. Show the names and credits of students majoring in 'MATH' and having 40 or more credits.
7. Show the id of students enrolled in 'CSC201A'.
8. Show the code of departments with faculty in the rank of 'Professor'.
9. Show the names of departments with faculty in the rank of 'Professor'.
10. Show the names of students who have enrolled in 'CSC201A'.
11. Show the names and major names of every student.
12. Show the names, major names, and advisor names of every student.
11. Show the student names and their major names for all students who have received a grade A in a class offered by a faculty from the MATH department.