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 !!!

 Each tuple in the relationship contains the key attributes of the entities (Because the key attribute can identify an entity uniquely !!!)

Example: the works_for relationship in the above figure are these tuples:

 ``` (123-45-6789, 4) (= john works in payroll) (111-11-1111, 1) (= kate works in administration) (222-22-2222, 1) (= peter works in administration) ```

• 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):

1. We can create a new relation (table)

 The tuples of the relationship are stored in this new 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 !!!

2. 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:

 Represent a relationship by extending an existing relation with one or more atomic valued attribues

• 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:

 Employee:Department = N:1 Employee participation = Total,       Department participation = partial.

• 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:

 ``` (DNumber, SSN) ```

The relation (table) Department already has the DNumber attribute:

 ``` Department ( DName, DNumber ) ```

• To represent the relationship Works-For by extending Department:

 we just have to add the SSN attribute to the relation 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.

Notice that the relationship tuples is of the format (DNumber, SSN)

Again, the relation Employee already has the SSN attribute....

So we just have to add the DNumber attribute from the department to Employee....

Result:

This expansion will work, because DNo is single-valued !!!

• 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:

 To represent a relationship ` Entity1:Entity2 = 1:N ` expand the relation that represents Entity2

• 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:

Expand the Employee relation with the attribute "ManagedDNum" representing the managed department

Since the relationship is Employee:Department = 1:1, "ManagedDNum" will have atomic attribute values.

• Solution 2:

Expand the Department relation with the attribute "MgrSSN" representing the SSN of the manager of the department

Again, the relationship is Employee:Department = 1:1, so "MgrSSN" will also have atomic attribute values.

• \$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:

 Avoid having attribute that can take on NULL values

NULL values can be a headach in formulation of some queries.

• Attributes of a relationship

• Recall that:

 A relationship can have attributes Example: StartDate in the Manage relationship

• Attributes of a relationship:

 We must put the attributes of a relationship in the same relation that we used to store the entity

• 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 ?