Exercise 9
CS122, Spring 2004


Thursday, May 27

Chapter 0 and 11

Use the Players table with the following assumptions:
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)
(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