Relational model cont. #

Weak entity #

Notice that the employee will be the owner of the depending weak entity. The primary key will be part of the foreign key and the weak attribute.

Remember,

• primary keys are underlined
• foreign keys have arrows pointing to the other reference

Multivalues attributes #

Consider this ER-diagram,

We can handle this in our relational model by creating another table called dept_locations,

Note that the primary key of dept_locations is the combination of both dnumber and dlocation.

A bigger example #

Consider

Its good to start at a strong entity, so we’ll start with employee

Then we’ll tackle the recursive supervision relationship, by creating a foreign key superssn that references to the ssn,

Next we’ll do department

• we need to make dept_location to handle the multivalued attribute
• to handle the derived attribute we can either
  1. use a trigger to handle the derived attribute (like number_of_employees), (taught in more depth in CS174)
  2. Ignore the derived attributes for now, which is what we’ll do

Now that we have both of those entities setup, we’re ready to do the 2 relationships works_for and manages,

Note: The colors don’t have any meanings in relational models, just a preference from the Professor.

Next we need to do the entity project before we can do the relationship controls

Note: The order of your attributes don’t have any meaning, you can list pnumber first if preferable.

We need a foreign key in project to note which department controls it

Next we can add the many-to-many relationship works_on with a new table

• we added the primary key of works_on to be both essn and pno

We can now tackle the weak entity dependent

• we added a foreign key essn to reference to the employee
• primary key is essn and d_name

Overall,

University ER exercise #

Consider this ER-diagram

Note: This is part of the weekly homework.

Relational algebra #

File: Relational algebra slides

Studying relational algebra will make our SQL skills better. It also gives a deeper understanding on how queries are processed. It is low level algebra presentation of a high level query.

Recall that relational model is set based and bag based, so we don’t allow duplicates. It enables users to specify basic retrieval requests.

SELECT #

For example if we’re looking for the employees from department number 5,

and the result it stored.

Consider that result for this table

The result emp_5 will be a new table (though not physically saved on the disk, just as a return result). For select, the structure of the new relation is the same as the input (this is not true for all operators).

So for each given tuple in the input, we are looking to see if it satisfies the condition dno = 5,

The result is a subset of the tuples.

There can be many conditions in each select operation. Multiple conditions can be put together with boolean operations.

For example if we want the department of the employee to be number 5, with salary > 30000

2 of the employees are returned (in blue).

We can do more complex selections, ie