Name: __________________________________
Score:
Normalize 4 of the following 6. First complete the functional dependencies then produce the table(s) in third normal form. Neatly print your answer in the space provided. Remember that { } inside of ( ) means a repeating group.
1. Employee (EmplID#, EmpName, Dept#, InsCode#, InsDescrpt, {DepdName, DepdAge})
InsCode# is the insurance code number
- a unique item.
DepdName is the dependent's name.
The employee name is not unique.
There may be more than one employee who
is in a department.
There may be more than one employee who
has an insurance code.
Insurance descriptions (like HMO or PPO)
will not be unique but the insurance code numbers will.
EmplID# --->
EmpName --->
Dept# --->
InsCode# --->
InsDescrpt --->
DepdName --->
DepdAge --->
2. BBPlayer(PlayerName, PlayerAdd, {PlayerYear, PlayerPosition, PlayerAvg, PlayerFldPct, PlayerSal, PlayerRuns, PlayerHR, PlayerUnif#}) - Assume PlayerName is Unique.
A ballplayer's statistics will be unique
based upon their name and the year.
Since players on different teams may have
the same uniform numbers, the uniform number even combined with the year
can not be used to identify a specific player.
There is not enough information given
to derive any of the items listed here.
PlayerName --->
PlayerAdd --->
PlayerYear --->
PlayerPosition --->
PlayerAvg --->
PlayerFldPct --->
PlayerSal --->
PlayerRuns --->
PlayerHR --->
PlayerUnif# --->
PlayerName + PlayerYear --->
3. Sales (SaleID#, SalesName, Location#,
{CustID#, CustName, CustAddr})
Sales names are not unique.
There can be multiple sales persons at
any location.
Customer names are not unique.
Customer addresses are not unique.
SaleID# --->
SalesName --->
Location# --->
CustID# --->
CustName --->
CustAddr --->
4. Mechanic (MechID#, MechName, Shop#,
ShopCity, Shophours { Skill#, SkillCat, SkillLevel})
A mechanic's name is not unique.
Multiple mechanics work at any given shop.
There may be more than one shop in the
same city.
The shop hours are the hours that
a particular shop is open for business.
A skill category is not unique.
A skill level is the skill that a particular
mechanic has for that particular skill.
MechID# --->
MechName --->
Shop# --->
ShopCity --->
Shophours --->
Skill# --->
SkillCat --->
SkillLevel --->
5. Department(DepID, DepName, DepLoc,
{EmpID, EmpName, EmpWage, EmpPos})
More than one department may have the
same name and be in the same location - i.e. there may be more than one
sales department and there may be several departments in the downtown location.
The employee names are not unique.
More than one employee may have the same
position.
DepID --->
DepName --->
DepLoc --->
EmpID --->
EmpName --->
EmpWage --->
EmpPos --->
6. Student(StdID, StdName, {CourseID, CourseName,
Grade})
Student name will not be unique.
The course name will not be unique.
StdID --->
StdName --->
CourseID --->
CourseName --->
Grade --->