Question

3. Identify the relationships of Player and Player fields including PKs, CKs, and FDs. While using the entities and fields found in Player, create a DBDL example of tables, fields, and key fields that are in third normal form. Convert this table to an equivalent collection of tables, fields and keys that are in third normal form. Represent your exercise answers in DBDL design from the database normalization phases explained in class.Player contains information about players and their teams. Player has attributes playerId, first, last, gender, teamId, teamName, teamCity where playerId is the only CK and the FDs are:

playerId  first

playerId  last

playerId gender

playerId  teamId

playerId  teamName

playerId  teamCity

teamId  teamName

teamId  teamCity

Player – sample data
playerId	first	last	gender	teamId	teamName	teamCity
1	Jim	Jones	M	1	Flyers	Winnipeg
2	Betty	Smith	F	5	OilKings	Calgary
3	Jim	Smith	M	10	Oilers	Edmonton
4	Lee	Mann	M	1	Flyers	Winnipeg
5	Samantha	McDonald	F	5	OilKings	Calgary
6	Jimmy	Jasper	M	99	OilKings	Winnipeg

Answer 1

This Data base is not in 3rd normal form as there is transitive dependency exist between :

These two are tansitive dependency in our database:

teamId  teamName

teamId  teamCity

Condition for a database to be in 3rd normal form is that the database must be in 2nd Normal form + no any transitive Dependencies in table

Transitive dependency : A dependency is said to be transitive if a functional dependency A->B where A,B both belongs to Non-prime attribute(attribute which is not part of candidate key)

Now to remove the transitive dependencies we have to decompose the table Like this :

so this is the finally how our main data base is decomposed into two tables inorder to remove the transitive dependency from data base so that we can satisfy the condition of 3rd Nomal form.