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
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.
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).
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.
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.