Question

Task 6

Using the following table structure, identify all functional dependences and then decompose this table into a set of 3NF relations. Your answer should:

• List the functional dependences
• Contain the relational schema for the relations in 3NF.

CharId	CharName	ActorName	FigId	OwnerId	Pseudonym
Vader	Darth Vader	David Prowse	f14	Bill	Jabba the Hoot
Vader	Darth Vader	David Prowse	f22	Amy	Don’t Blame Me
Yoda	Yoda	Frank Oz	f16	Lucy	Xena Warrior
Leia	Princess Leia	Carrie Fisher	f45	Bill	Jabba the Hoot
Leia	Princess Leia	Carrie Fisher	f99	Amy	Don’t Blame Me

Answer 1

Given Schema:

Let the given table be CharacterFigure:

CharacterFigure: (CharId, CharName, ActorName, FigId, OwnerId, Pseudonym)

where primary key is a composite key of CharId and OwnerId

The following functional dependencies exist:

(CharId,OwnerId) ( CharName, ActorName, FigId, Pseudonym)

CharId CharName, ActorName

OwnerId Pseudonym

First Normal Form(1NF) - 1NF disallows multi-valued attributes , composite attributes and their combinations. The domain of attributes must include only atomic values hence it disallows sets of values, tuple of values or a combination of both as an attribute value for a single tuple.

Since the above schema, doesn't contain any attribute with sets of values or tuple of values or any combination of both, the schema is in 1NF.

After 1NF:

CharacterFigure: (CharId, CharName, ActorName, FigId, OwnerId, Pseudonym)

where primary key is a composite key of CharId and OwnerId

The following functional dependencies exist:

(CharId,OwnerId) ( CharName, ActorName, FigId, Pseudonym)

CharId ( CharName, ActorName)

OwnerId Pseudonym

Second Normal Form (2NF) - A Relational Schema, R, is in 2NF if every non-prime attribute , A in R is fully functionally dependent on the primary key of R.

• A prime attribute is a part of a key whereas non-prime attribute is not part of any key of R.
• A functional dependency XY, is a full functional dependency if removal of any attribute A from X means that the dependency doesn't hold anymore.
• A functional dependency, XY is a partial dependency is some attribute A X can be removed and the functional dependency still holds.

In the above Schema, CharName and ActorName are partially dependent on CharId and Pseudonym is partially dependent on OwnerId. In order to convert the Schema to 2NF , we remove CharId, CharName and ActorName from the table CharacterFigure and create a new table, say Character with CharId, CharName and ActorName where CharId is the primary key of Character which in CharacterFigure forms the foreign key of Character table.

Similarly, we remove OwnerId and Pseudonym from CharacterFigure and create another table say, Owner with fields OwnerId and Pseudonym where OwnerId is the primary key of Owner table and forms foreign key of Owner table in CharacterFigure table.

After 2NF:

Character : (CharId, CharName, ActorName)

where CharId is the primary key

The following functional dependencies exist:

CharId (CharName, ActorName)

Owner : (OwnerId, Pseudonym)

where OwnerId is the primary key

The following functional dependencies exist:

OwnerId Pseudonym

CharacterFigure: (CharId, FigId, OwnerId)

where CharId is the foreign key of Character table

OwnerId is the foreign key of Owner table

composite key of (CharId, OwnerId) is the primary key of CharacterFigure table

The following functional dependencies exist:

(CharId,OwnerId) FigId

Third Normal Form(3NF) : A Relational Schema, R, is in 3NF if it is in 2NF and no non-prime attribute of R is transitively dependent on the primary key. A Relational Schema, R, is in 3NF if whenever a functional dependency XA holds in R either:

• X is the super key of R, or
• A is a prime attribute of R

A functional dependency XY in a Relational schema R, is a transitive dependency if there is a set of attributes Z that is not a subset of any key of R and both XZ and ZY holds.

In the above schema, no tables contains any transitive dependency.

After 3NF:

Character : (CharId, CharName, ActorName)

where CharId is the primary key

The following functional dependencies exist:

CharId (CharName, ActorName)

Owner : (OwnerId, Pseudonym)

where OwnerId is the primary key

The following functional dependencies exist:

OwnerId Pseudonym

CharacterFigure: (CharId, FigId, OwnerId)

where CharId is the foreign key of Character table

OwnerId is the foreign key of Owner table

composite key of (CharId, OwnerId) is the primary key of CharacterFigure table

The following functional dependencies exist:

(CharId,OwnerId) FigId