The Relational Model

Relational Model:
Relational Model = a collection of relations that store information about some micro-world
Example Relational Model of a University (micro-world):

Relations used to represent the University micro-world

The relations that correspond to the 3 tables (of data) storing information of the University:

Relations used to represent the University micro-world

The relations that correspond to the 3 tables (of data) storing information of the University:

A relation consists of
A relation name (in the example: Student, Course and GradeReport)

The list of attributes in each relation

A relational model of a company

A relational model about a company:

Meaning of the employee and the department relations/tables

The employee relation stores information of the employees:

fname = first name of employee
minit = middle initial of employee
lname = last name of employee
SSN = social security number of employee (key, or unique identifier)
bdate = birthdate of employee
address = address of employee
sex = social security number of employee's supervisor
DNO = department number of the department where employee works

the department relation stores information of the departments in the company

dname = name of the department
dnumber = department number of the department (key, or unique identifier)
mgrSSN = SSN of employee that manages the department
mgrStartDate = date that manager employer starts managing the department

Example content of the employee and department relations

Example content of the employee relation:

fname minit lname ssn bdate address sex salary superssn dno ------ ----- -------- --------- ---------- ------------------------- --- --------- --------- ----------- John B Smith 123456789 09-JAN-55 731 Fondren, Houston, TX M 30000.00 333445555 5 Frankl T Wong 333445555 08-DEC-45 638 Voss, Houston, TX M 40000.00 888665555 5 Alicia J Zelaya 999887777 19-JUL-58 3321 Castle, Spring, TX F 25000.00 987654321 4 Jennif S Wallace 987654321 20-JUN-31 291 Berry, Bellair3, TX F 43000.00 888665555 4 Ramesh K Narayan 666884444 15-SEP-52 975 Fire Oak, Humble, TX M 38000.00 333445555 5 Joyce A English 453453453 31-JUL-62 5631 Rice, Houston, TX F 25000.00 333445555 5 Ahmad V Jabbar 987987987 29-MAR-59 980 Dallas, Houston, TX M 25000.00 987654321 4 James E Borg 888665555 10-NOV-27 450 Stone, Houston, TX M 55000.00 null 1

Example content of the department relation:

dname dnumber mgrssn mgrstartdate --------------- ----------- --------- ------------ Research 5 333445555 22-MAY-78 Administration 4 987654321 01-JAN-85 Headquarters 1 888665555 19-JUN-71

Example of the use of a foreign key

dno in the employee relation is the primary key in the department relation:

fname minit lname ssn bdate address sex salary superssn dno ------ ----- -------- --------- ---------- ------------------------- --- --------- --------- ----------- John B Smith 123456789 09-JAN-55 731 Fondren, Houston, TX M 30000.00 333445555 5 Frankl T Wong 333445555 08-DEC-45 638 Voss, Houston, TX M 40000.00 888665555 5 Alicia J Zelaya 999887777 19-JUL-58 3321 Castle, Spring, TX F 25000.00 987654321 4 Jennif S Wallace 987654321 20-JUN-31 291 Berry, Bellair3, TX F 43000.00 888665555 4 Ramesh K Narayan 666884444 15-SEP-52 975 Fire Oak, Humble, TX M 38000.00 333445555 5 Joyce A English 453453453 31-JUL-62 5631 Rice, Houston, TX F 25000.00 333445555 5 Ahmad V Jabbar 987987987 29-MAR-59 980 Dallas, Houston, TX M 25000.00 987654321 4 James E Borg 888665555 10-NOV-27 450 Stone, Houston, TX M 55000.00 null 1

The primary key of the department relation is dnumber:

dname dnumber mgrssn mgrstartdate --------------- ----------- --------- ------------ Research 5 333445555 22-MAY-78 Administration 4 987654321 01-JAN-85 Headquarters 1 888665555 19-JUN-71

Example of the use of a foreign key

The employee John Smith works in the dnum = 5:

fname minit lname ssn bdate address sex salary superssn dno ------ ----- -------- --------- ---------- ------------------------- --- --------- --------- ----------- John B Smith 123456789 09-JAN-55 731 Fondren, Houston, TX M 30000.00 333445555 5 Frankl T Wong 333445555 08-DEC-45 638 Voss, Houston, TX M 40000.00 888665555 5 Alicia J Zelaya 999887777 19-JUL-58 3321 Castle, Spring, TX F 25000.00 987654321 4 Jennif S Wallace 987654321 20-JUN-31 291 Berry, Bellair3, TX F 43000.00 888665555 4 Ramesh K Narayan 666884444 15-SEP-52 975 Fire Oak, Humble, TX M 38000.00 333445555 5 Joyce A English 453453453 31-JUL-62 5631 Rice, Houston, TX F 25000.00 333445555 5 Ahmad V Jabbar 987987987 29-MAR-59 980 Dallas, Houston, TX M 25000.00 987654321 4 James E Borg 888665555 10-NOV-27 450 Stone, Houston, TX M 55000.00 null 1

The department Research has dnumber = 5:

dname dnumber mgrssn mgrstartdate --------------- ----------- --------- ------------ Research 5 333445555 22-MAY-78 Administration 4 987654321 01-JAN-85 Headquarters 1 888665555 19-JUN-71

Therefore: employee John Smith works in the Research department

Meaning of the department_loc and the project relations/tables

The department_loc relation stores information of the locations of departments in the company
dnumber = department number of the department
dlocation = the location (address) of this department

The project relation stores information of the projects of the company

pname = name of the project
pnumber = project number of the project (key, or unique identifier)
plocation = the location (address) where project is performed
dnum = department number of the department that manages the project

Example content of the department_loc and project relations

Example content of the department_loc relation:

dnumber dlocation ----------- ---------- 1 Houston 4 Stafford 5 Bellaire 5 Sugarland 5 Houston

Example content of the project relation:

pname pnumber plocation dnum --------------- ----------- ---------- ----------- ProductX 1 Bellaire 5 ProductY 2 Sugarland 5 ProductZ 3 Houston 5 Computerization 10 Stafford 4 Reorganization 20 Houston 1 Newbenefits 30 Stafford 4

Meaning of the works_on and the dependent relations/tables

The works_on relation stores information on "which employee works on which project":

ESSN = social security number of employee that works on a project
pno = project number of the project that the employee works on
hours = # hours that the employee works on the project

The dependent relation stores information on dependents of the employees

ESSN = social security number of employee that has a dependent
dep_name = name of the employee's dependent
sex = sex of the employee's dependent
bdate = birthdate of the employee's dependent
relationship = relationship (son, daugther, father, etc) between employee and his/her dependent

Example content of the works_on and dependent relations

Example content of the works_on relation:

essn pno hours --------- ----------- ------- 123456789 1 32.5 123456789 2 7.5 333445555 2 10.0 999887777 30 30.0 999887777 10 10.0 987654321 30 20.0 666884444 3 40.0 453453453 1 20.0 987987987 10 35.0 888665555 20 1.0

Example content of the dependent relation:

essn name sex bdate relationship --------- ---------- --- ---------- ------------ 333445555 Alice F 05-APR-76 DAUGHTER 333445555 Theodore M 25-OCT-73 SON 333445555 Joy F 03-MAY-48 SPOUSE 987654321 Abner M 29-FEB-32 SPOUSE 123456789 Micheal M 01-JAN-78 SON 123456789 Alice F 31-DEC-78 DAUGHTER 123456789 Elizabeth F 05-MAY-57 SPOUSE