Problem

Transform Figure 2-15a, attribute version, to 3NF relations. Transform Figure 2-15b, rel...

Transform Figure 2-15a, attribute version, to 3NF relations. Transform Figure 2-15b, relationship version, to 3NF relations. Compare these two sets of 3NF relations with those in Figure 4-10. What observations and conclusions do you reach by comparing these different sets of 3NF relations?

Reference figure 2-15

Figure 4-10

Transform Figure 2-15a, attribute version, to 3NF relations. Transform Figure 2-15b, relationship version, to 3NF relations. Compare these two sets of 3NF relations with those in Figure 4-10. What observations and conclusions do you reach by comparing these different sets of 3NF relations?

Reference figure 2-15

Figure 4-10

Solution 1

Step #1 of 3

3NF relations

Attribute Version of figure 2-15a to 3NF relation:

The attribute version is converted to 3NF relation to provide relationship between course and prerequisites.

• It has two relations COURSE and PREREQUISITE.

• In the relation COURSE,

o CourseID and CourseTitle are the attributes

o CourseID is the primary key attribute in the relation.

• In the relation PREREQUISITE,

o CourseID and PrereqID are the attributes.

o CourseID is the foreign key attribute which is used to refer the course table.

Note:

• In the diagrams, to indicate primary and foreign keys notational conventions are used.

o The attributes noted with underlined name are primary key values.

o The attributes noted with italics letters are foreign key values.

Here, a new relation PREREQUISITE is created to change the given attribute version to 3NF relations.

Step #2 of 3

Relationship Version of figure 2-15b to 3NF relation:

• The diagram given below describes the details of an employee, possessions and skill. It also shows their inter dependency.

• The ER diagram is transformed to three relations EMPLOYEE, POSSESSES and SKILL.

• In the relation EMPLOYEE,

o EmployeeID and EmployeeName are the attributes.

o EmployeeID is the primary key attribute and it acts as a composite key.

• In the relation POSSESSES,

o EmployeeID and SkillCode are the attributes.

o EmployeeID is the foreign key to refer EMPLOYEE relation.

o SkillCode is the foreign key to refer SKILL relation.

o SkillCode is the multi-valued attribute in the relationship.

o Since an employee can have multiple skills.

• In the relation SKILL,

o SkillCode, SkillType and SkillTitle are the attributes.

o SkillCode is the primary key of the relation.

• Each relation are associated with accurate values, there is no transitive dependency in the relations.

• Therefore, it is third normal form (3NF).

$C:\Users\300722\Desktop\23.jpg$

Note:

• In the diagrams, to indicate primary and foreign keys notational conventions are used.

o The attributes noted with underlined name are primary key values.

o The attributes noted with italics letters are foreign key values.

Step #3 of 3

Observations and comparisons:

The observations and comparisons which are made from these different sets of third normal form are as given below:

• It gives detailed information about the primary key.

• The attributes such as “SkillTitle” as well as the “SkillType” is not required to store the values. It can be stored under one attribute known as “Skill”.

• There are no anomalies in the relationship version of the relation; hence modifications in the “Skill” will not have any effect.

Add your Solution

Textbook Solutions and Answers Search

Solutions For Problems in Chapter 4

Transform Figure 2-15a, attribute version, to 3NF relations. Transform Figure 2-15b, rel...

Step-by-Step Solution