Exercise 9
CS122, Spring 2004

Thursday, May 27

Chapter 0 and 11

Use the Players table with the following assumptions:

• A Playerid uniquely identifies a player, and a Teamid uniquely identifies a team.
  
• A player wears the same jersey number and plays for the same team during a year.
  
• A player may wear different jersey numbers and/or plays for different teams during different years
  

1.(10pt) Write down or print the answers to the following questions on a piece of paper:

(a)  What are the key(s) of the table?

(b) If there're more than one key in the table, choose one of them as you primary key, and list all partial dependencies and transitive dependencies.

(c) Is the table in 1NF? If not, normalize it to 1NF.

(d) Is the table in 2NF? If not, normalize it to 2NF.

(e) Is the table in 3NF? If not, normalize it to 3NF.

2.(10pt) Create a blank database Player.mdb in MS Acccess, and

(a) Create all tables in your 3NF design with proper NOT NULL, PRIMARY KEY, and/or UNIQUE constraints using CREATE TABLE statements.

(b) Add proper FOREING KEY constraints using ALTER TABLE statements.

(c) Create an index on the primary key of each table using CREATE INDEX statement.

Discussion

1.
(a) (Playerid, Year)

(b)

• Full dependencies: (Playerid, Year) -> (JerseyNum, PointsScoredinYear, GamesPlayed)
• Partial dependencies: (Playerid) -> (Playername, BirthDate)
• Transitive dependencies: (Teamid) -> (TeamName, TeamLoc)

(c) With primary key defined, it is 1NF.

(d) It's not 2NF. 2NF design as follows:

Player: <Playerid, Playername, BirthDate>  -- primary key (Playerid)
PlayerInfo: <Playerid, Year, JerseyNum, PointsScoredinYear, GamesPlayed, Teamid, TeamName, TeamLoc> -- primary key (Playerid, Year), foreign key (Playerid) references (Playerid) in Player

(e) It's not 3NF. 3NF design as follows:

Player: <Playerid, Playername, BirthDate> -- primary key (Playerid)
Team: <Teamid, TeamName, TeamLoc> -- prmary key (Teamid)
PlayerInfo: <Playerid, Year, JerseyNum, PointsScoredinYear, GamesPlayed, Teamid> -- primary key (Playerid, Year), foreign key (Playerid) references (Playerid) in Player, and foreign key (Teamid) references (Teamid) in Team