Question

24. Figure 4-38 shows an EER diagram for a university dining service organization that provides dining services to a major university.

    a. Transform the EER diagram to a set of relations and develop a     relational schema.

    b. Diagram the functional dependencies and determine the normal form     for each relation.

    c. Convert all relations to third normal form, if necessary, and draw a     revised relational schema.

Answer 1

a.

Transform EER diagram in Figure 4-38 to set of relations and develop a relational schema:

Step 1: Mapping of regular entities

The regular entities are transformed into relations.

• The regular entities in the EER diagram in Figure 4-38 are MENU, EVENT, DISH and STAFF.

• Create a relation for each regular entity and include all its simple attributes.

• The entity DISH contains multivalued attribute Ingredient. So, two relations DISH and DISH INGREDIENT are created.

• The entity STAFF contains multivalued attribute Skill. So, two relations STAFF and STAFF SKILL are created.

• The resultant set of relations follows:

Step 2: Mapping of weak entities

The weak entities are identified and transformed into relations.

• There is only one weak entity WORK SCHEDULE in the EER diagram in Figure 4-38.

• Create a relation WORK SCHEDULE with all the simple attributes and include the primary key of EVENT and STAFF as the primary key of the relation WORK SCHEDULE.

• The resultant set of relations follows:

Step 3: Mapping of binary relationships

• There exists a binary 1:N relationship Served at between MENU and EVENT. So, include the primary key of MENU as a foreign key in relation EVENT.

• There exists a binary M:N relationship Contains between MENU and DISH. So, create a new relation MENU DISH include the primary key of MENU and DISH as a foreign key in relation MENU DISH.

• The resultant set of relations follows:

Step 4: Mapping of Unary relationships

• There exists a unary relationship Supervises with the entity STAFF. So, include a foreign key Supervisor in relation STAFF that references EmpID.

• The resultant set of relations follows:

The relational schema of the EER diagram in Figure 4-38 is as follows:

Explanation:

• In the MENU relation,

o MenuID, MenuType, and MenuDescription are the attributes.

o MenuID is the primary key attribute.

• In the DISH relation,

o DishID, DishName, and PrepTime are the attributes.

o DishID is the primary key attribute.

• In the DISH INGREDIENT relation,

o DishID and IngredientID are the attributes.

o DishID and IngredientID are the foreign key attributes which refer DISH and INGREDIENT relations to get any values.

• In the MENU DISH relation,

o MenuID and DishID are the attributes.

o MenuID and DishID are the foreign key attributes which refer MENU and DISH relations to get any values.

• In the EVENT relation,

o EventID, EventDate, EventLocation, EventTime and MenuID are the attributes.

o EventID is the primary key attribute.

o MenuID is the foreign key attribute which refer MENU relation to get any values.

• In the STAFF relation,

o EmpID, Name, Salary and SupervisorID are the attributes.

o EmpID is the primary key attribute.

• In the STAFF SKILL relation,

o EmpID and Skill are the attributes.

o EmpID and Skill are the primary key attributes.

• In the WORK SCHEDULE relation,

o EventID, EmpID, Position, StartTime and EndTime are the attributes.

o EventID and EmpID are the foreign key attributes which refer MENU and STAFF relation to get any values.

b.

Functional dependency:

The following diagram shows the relational schema as well as functional dependencies. The dependency shows constraint between set of attributes.

MENU relation:

• The functional dependency is represented as follows:

• MenuType and MenuDescription are functionally dependent on MenuID.

• The relation MENU is in third normal form.

DISH relation:

• The functional dependency is represented as follows:

• DishName, and PrepTime are the attributes functionally dependent on DishID.

• The relation DISH is in third normal form.

EVENT relation:

• The functional dependency is represented as follows:

• EventDate, EventLocation, EventTime and MenuID are the attributes functionally dependent on EventID.

• The relation EVENT is in third normal form.

WORK SCHEDULE relation:

• The functional dependency is represented as follows:

• Position, StartTime and EndTime are the attributes functionally dependent on EventID and EmpID.

• The relation WORK SCHEDULE is in third normal form.

STAFF relation:

• The functional dependency is represented as follows:

• Name, Salary and SupervisorID are the attributes functionally dependent on EmpID.

• The relation STAFF is in third normal form.

The relations DISH INGREDIENT, MENU DISH, STAFF SKILL are in third normal form.

c.

Third normal form (3NF):

• In order for a relation to be in third normal form, all nontrivial functional dependencies must be fully dependent on the primary key and any non-key attribute should not describe any non-key attribute. In other words, there should not be any partial dependency and transitive dependency.

• The relations do not contain any repeated attributes and the relations do not have transitive dependency. Hence, the relations are already in 3NF.