- Relational Model:
- Relational Model = a collection of relations that store information about some micro-world
Example:
- In the previous example,
we saw 3 tables that store
data on
students,
courses and
and their grades:
The micro-world in this example is a university
- The relations that
correspond to these
3 tables (of data) are
as follows:
A relation consists of
- A relation name (in the example: Student, Course and GradeReport)
- A list of attributes for each relation
- In summary, the
relations in
this example are:
- Consider the follow
relational model about
a company:
Meaning of the relations:
- employee =
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
- department =
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
- department_loc =
stores information of the
locations of
departments in the company
- dnumber = department number of the department
- dlocation = the location (address) of this department
- project =
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
- works_on =
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
- dependent =
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
- employee =
stores information of the
employees
- Example of the content
of the Company Data model:
- Employee:
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
- Department:
dname dnumber mgrssn mgrstartdate --------------- ----------- --------- ------------ Research 5 333445555 22-MAY-78 Administration 4 987654321 01-JAN-85 Headquarters 1 888665555 19-JUN-71
Notice that:
- dno in
Employee is
a foreign key
- John Smith is an employee in the deparment #5
The value in this foreign key attribute is used to identity a tuple in the Department relation
- The department #5 is
the Research department
(So John Smith works in the Research department !!!)
- dno in
Employee is
a foreign key
- Department_Loc:
dnumber dlocation ----------- ---------- 1 Houston 4 Stafford 5 Bellaire 5 Sugarland 5 Houston
- Project:
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
- Works_on:
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
- Dependent:
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
- Employee: