Question

Consider the following relation for art works displayed in an art gallery: ARTWORK(Title, ArtistName, Type, ListPrice, ArtistAffil, Dealer) where ArtistAffil refers to the affiliation of the artist.

Suppose the following dependencies exist:

Title -> Dealer, Type

Type -> ListPrice

ArtistName -> ArtistAffil

(a) What is a candidate key of this relation? Explain your answer.

(b) Apply normalization until your relations are in 3NF. State the reasons behind each decomposition.

Answer 1

Solution:

.................................................................................................................................................................

a)

The candidate key is the minimal set of attributes in a relation that uniquely identifies a tuple in a relation.

For the given relation from the list of FDs the attribute Title uniquely identifies a tuple in the given relation, hence 'Title' is the candidate key.

b)

Normalization is the process of decomposing a relation in order to eliminate data redundancy and insert, update and delate anamolies.

The given relation is already in first normal form, so we decompose it into second normal form. According to 2nd normal form-

1. It should be 1st normal form and there should be no partial dependancy.

In the given table 'Title' which is a candidate key of the given relation contains two dependancy of Dealer as well as Type. We need to remove this partial dependancy-

ARTWORK(Title, ArtistName, Type, ListPrice, ArtistAffil, DealerID)

Dealer(DealerID, DealerName)

Now the given relation in 2NF. So we need to decompose it into 3NF.

A relation is said to be in 3NF, if it is in 2NF and does not contain any transitive dependancy.

Dealer(DealerID, DealerName)

ARTIST(ArtistID, ArtistName, ArtistAffill)

ARTWORK(Title, Type, ListPrice, ArtistID, DealerID)