CS457 Syllabus & Progress

Designing a Relational Model using a ER-model - Part 2

We still have to represent the relationships....

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

How to represent Relationships using Relations

Recall what exactly is a relationship:

Conclussion:

A relationship (such as Works-For in the figure) is a set of tuples !!!
Example: the works_for relationship in the above figure are these tuples:

Therefore:

We can always represent a relationship using a new relation (= table)

Important design rule:

We must not create too many relations
(Because the data model will get too complicated and inefficient)
We must try to minimize the number of relations (= tables) in the Relational Model

Different ways to represent a relationship using relations

There are in general two (2) ways to represent a relationship using relations (tables):

We can create a new relation (table)
This technique can always be used, but it is a technique that is only used when method 2 fails to work !!!
You must try method 2 first !!!

We can expand a existing relation (table) to store the tuples of the relationship

The tuples of the relationship are stored inside the table of an (existing) entity !!!
We will make use of the key of that entity to store the tuples of the relationship !!!

The first method is always possible

However: this will increase the number of relations (= number of files)

The second method is preferred:

Because it will not increase the number of relations (= files)
It will only make an existing relation a little bit larger

but.... as we will see soon: method 2 does not work all the time

When does method 2 "work"
- Method 2 does "work" if we can:
- We will now go through the relationship one-by-one and see how they can be represented by relations

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 ?