Question

Question?

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.

Instructions: Convert this table to an equivalent collection of tables, fields, and keys that are in the third normal form. Represent your exercise answers in DBDL design from the database normalization phases.

The 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

Figure 1: 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.

if this answer is useful please give positive rating. if any doubts please give comments i will try to resolve it