CS457 Syllabus & Progress

Representing the "other" (ordinary) relationship in a Relational Data Model

We still have to represent the "other" relationships....

Recall the ER-model for the Company database in diagram format:

Representing the Works-For relationship

In order to store the relationship Works-For, we must store tuples of this format:

(EmployeeSSN, DNumber) (They are primary keys of each entity)

Recall: the properties of the Works-For relationship:

Solution 1: We expand the Department relation.

Tables of the Entities:

Employee ( SSN, FName, MI, LName, Sex, Address, BDate, Salary ) Department ( DName, DNumber ) <---- extend THIS relation

Notice that the relationship Works-For tuples are:
The relation (table) Department already has the DNumber attribute:
To represent the relationship Works-For by extending Department:
Result:

Notice that:

EmployeeSSN is multi-valued !!!!
(Because 1 department can have multiple employees)

Not acceptable - violates the relational database atomic domain principle...

Solution 2: Expand the Employee relation.

Sample tuples to show you that we successfully represented the Works-for relationship:

SSN FName MI LName ... DNo --------------------------------------------------- 123-45-6789 John X Smith 4 111-11-1111 Kate Y Smith 1 222-22-2222 Peter Z Pan 1

We can see from the tuples stored that:

Employee 123-45-6789 works in department 4
Employee 111-11-1111 works in department 1
Employee 222-22-2222 works in department 1

Design rule 1:
Relationships that can be represented by EXPANDING an existing relation
- Controls-Project: Department:Project = 1:N
  by adding (DNum) to the Project relation
- Supervisor: Employee(supervisor):Employee(supervisee) = 1:N
  by adding (SupervisorSSN) to the Employee relation - this SSN plays the role of the supervisor
  (NOTE: if you add (SuperviseeSSN) - an SSN number that plays the role of the supervisee, then this attribute is multivalued....)
Representing the Manager relationship
- Properties of the Manager relationship:
  - Employee:Department = 1:1
  - Employee participation = Partial, Department participation = Total.
- Solution 1:
- Solution 2:
- $6,000,000 Question: is there any advantage of one solution over the other ???
  Yes, definitely. Look at the participation constraints:
  - Employee participation = Partial
  - Department participation = Total
- Solution 1:
```
  Employee(SSN, FName, MI, LName, ....,  ManagedDNum)
```
  The attribute ManagedDNum will contain NULL values because the participation of Employee is partial, i.e., no every employee will have a department to manage.... - employees that do not manage departments will have ManagedDNum = NULL
- Solution 2:
```
  Department(DName, DNumber, ....,  ManagerSSN)
```
  The attribute ManagerSSN will NEVER contain NULL values because the participation of Department is TOTAL, i.e., every department will have a manager !!!
- Design rule:
  NULL values can be a headach in formulation of some queries.

Attributes of a relationship

Recall that:
Attributes of a relationship:

Result:

Department(DName, DNumber, ...., ManagerSSN, StartDate )

Sample tuples to show you that we successfully represented the Manage relationship:

DName DNumber .... ManagerSSN StartDate ------------------------------------------------------------------------- Payroll 4 123-45-6789 1/1/2000 Adm. 1 111-11-1111 3/1/2010

Can you tell which employee is the manager of the Payroll department ?

Employee (whose SSN is) 123-45-6789

Can you tell when this employee started managing the Payroll department ?

1/1/2000

Representing the Works-On relationship by CREATING a NEW relation
- Properties of the Works-On relationship:
  - Employee:Project = M:N
  - Employee participation = Partial, Project participation = Partial.
- Solution 1: Expand the Employee relation with the attribute "WorkedOnProjects" representing the projects that the employee works on.
  Since the relationship is Employee:Project = M:N, "WorkedOnProjects" will be multi-valued.
- Solution 2: Expand the Project relation with the attribute "WorkerSSN" representing the SSN of the employees who work on the project.
  Again, the relationship is Employee:Project = M:N, so "WorkerSSN" will also be multi-valued....
- So the "Expand an existing relation" does not work....
- Fear not, recall (see above) there is another way: create a new relation:
- NOTE: Works-On relationship has an attribute "Hours" - this attribute must also be include into the relation
The Relational Model for the Company Database
When you put everything together (as I have shown in class), you will get the following relational model:

How the relational model stores information about the Company

Sample content on the relations in the Company Data Base model (from the text book):
Here is an copy of the page from the textbook: click here

Can you figure out the following information:

Which department does John Smith work for ?
Who are the dependents of John Smith ?
What projects does John Smith work on (and for how many hours each week) ?
Where is the Headquaters of the company ?
Who manages Headquaters of the company ?