Introduction to the relational model

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: ? (will discuss in the class)
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.

Example:

Toyu: A drastically simplified university.

ER Diagram:

MS Access: toyu.accdb

MySQL creation script: Createtoyu.sql.txt.

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 | 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 course;
+----------+--------+--------+-------------------------------------+
| courseId | rubric | number | name                                |
+----------+--------+--------+-------------------------------------+
|     2000 | CSCI   | 3333   | Data Structures                     |
|     2001 | CSCI   | 4333   | Design of Database Systems          |
|     2002 | CSCI   | 5333   | DBMS                                |
|     2020 | CINF   | 3321   | Introduction to Information Systems |
|     2021 | CINF   | 4320   | Web Application Development         |
|     2040 | ITEC   | 3335   | Database Development                |
|     2041 | ITEC   | 3312   | Introduction to Scripting           |
|     2060 | ENGL   | 1310   | English I                           |
|     2061 | ENGL   | 1311   | English II                          |
|     2080 | ARTS   | 3311   | Hindu Arts                          |
|     2090 | ACCT   | 3311   | Managerial Accounting               |
+----------+--------+--------+-------------------------------------+
11 rows in set (0.00 sec)

MariaDB [toyu]> select * from class;
+---------+----------+----------+------+-------+------+
| classId | courseId | semester | year | facId | room |
+---------+----------+----------+------+-------+------+
|   10000 |     2000 | Fall     | 2017 | 1011 | D241 |
|   10001 |     2001 | Fall     | 2017 | 1011 | D242 |
|   10002 |     2002 | Fall     | 2017 | 1012 | D136 |
|   10003 |     2020 | Fall     | 2017 | 1014 | D241 |
|   10004 |     2021 | Fall     | 2017 | 1014 | D241 |
|   10005 |     2040 | Fall     | 2017 | 1015 | D237 |
|   10006 |     2041 | Fall     | 2017 | 1019 | D217 |
|   10007 |     2060 | Fall     | 2017 | 1020 | B101 |
|   10008 |     2080 | Fall     | 2017 | 1018 | D241 |
|   11000 |     2000 | Spring   | 2018 | 1011 | D241 |
|   11001 |     2001 | Spring   | 2018 | 1013 | D242 |
|   11002 |     2002 | Spring   | 2018 | 1013 | D136 |
|   11003 |     2020 | Spring   | 2018 | 1016 | D217 |
|   11004 |     2061 | Spring   | 2018 | 1020 | B101 |
+---------+----------+----------+------+-------+------+
14 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 |
+--------+-----------+---------+-------+-------+---------+---------+
12 rows in set (0.00 sec)

MariaDB [toyu]> select * from enroll;
+--------+---------+-------+
| stuId | classId | grade |
+--------+---------+-------+
| 100000 |   10000 | A     |
| 100001 |   10000 | NULL |
| 100002 |   10000 | B-    |
| 100000 |   10001 | A     |
| 100001 |   10001 | A-    |
| 100000 |   10002 | B+    |
| 100002 |   10002 | B+    |
| 100000 |   10003 | A     |
| 100002 |   10003 | B-    |
| 100004 |   10003 | A     |
| 100005 |   10003 | NULL |
| 100000 |   10004 | A-    |
| 100004 |   10004 | B+    |
| 100005 |   10004 | A-    |
| 100006 |   10004 | C+    |
| 100005 |   10005 | A-    |
| 100006 |   10005 | A     |
| 100005 |   10006 | B+    |
| 100007 |   10007 | B+    |
| 100008 |   10007 | C-    |
| 100007 |   10008 | A     |
+--------+---------+-------+
21 rows in set (0.00 sec)

SQL is used to retrieve the content of a table.

Consider

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: deparment(deptCode, deptName, schoolCode and 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.

Example:

After inserting a new row:

insert into department values
('MATH', 'Mathematics','CSE',11);

The new relation instance of the table department:

After:

delete from department where deptCode = 'MATH';

the relation instance reverts back.

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 an entity, a relationship, or a property of the problem. Example:
- 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:

2.SQL and MySQL

The standard query language for RDBMS is Structured Query Language (SQL).
There are many RDBMS:
- MS Access
- MS SQL Server
- Oracle
- DB2
- MySQL
We use MySQL (or MariaDB) in this class.
MySQL is the most popular open source RDBMS.
Two download options:
- MySQL only: select the latest version from https://www.mysql.com/.
- XAMPP: from https://www.apachefriends.org/index.html
  - Easier to install.
  - The package contains MariaDB, Apache, PHP and Perl for development (not for production)
  - Recommended for this course.

Simple SQL

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

SELECT DISTINCT <<result_columns>>
FROM <<source_tables>>
WHERE <<conditions_for_inclusion>>

<<result_columns>>: result columns to be displayed.
<<source_tables>>: tables that contain necessary data.
<<conditions_for_inclusion>>: conditions for rows to be included in the result.

Examples:

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. All department names with a Professor as a faculty members.