Homework assignment

The following table records the results for would-be actors who are undergoing a preliminary screening audition for a particular role in a play. Any actor who is turned down for a role, in the preliminary screening, is not allowed to re-audition for that role again. (Everyone who passes will be re-auditioned later along with others who passed the preliminary screening, and that data will be put in a separate table.)

  

Actor 

Role 

AuditionDate

Result 

 

AaryanChaudary

Julius Caesar 

2018-09-13 

reject 

 

AaryanChaudary

Brutus 

2018-09-14 

pass 

 

Barry Evans 

Julius Caesar 

2018-09-13 

pass 

 

Isaac Biko 

Cassius 

2018-09-13 

pass 

 

Isaac Biko 

Julius Caesar 

2018-09-13 

pass 

The primary key of this table is Actor + Role.

Question 1

What bad consequences could follow if, in creating the table – before we added the data – we defined the primary key of the table as:

(a) Actor alone?

(b) Role alone?

(c) AuditionDate Alone?

(d) Actor + Role + AuditionDate?

(e) Actor + Role + AuditionDate + Result?

Question 2

Suppose it is decided to allow actors to re-audition for roles for which they were initially rejected, at a later date. Would we need to change the definition of the primary key? If we didn’t change it, what problem might arise? If we changed it, what would the new key be? Explain your answer.

********************************************************************************************************

Normalizing an Unnormalized Relation 

The following table holds information about the annually-recorded weight of particular prize sheep, and about the veterinarians (vets) who weigh them. Each sheep belongs to a particular owner. No sheep is owned by more than one owner. Each sheep’s birthdate and current owner is recorded. Every year, a veterinarian weighs each prize sheep and records its weight. The date of the weighing, and the ID number of the vet, plus the mobile phone number of the vet, is recorded. Vets have only one mobile phone number. The Primary Key of the table recording this information is SheepID+WeighingDate. The table has not been normalized beyond First Normal Form. That is, there are no ‘repeating groups’, but there may be Partial and Transitive Dependencies.

  

SheepID

Owner 

Birthdate   

WeighingDate

Vet 

Weight 

VetPhoneNum

 

K3922 

McNab013 

2013-05-12 

2013-08-14 

M330 

22 

7633088852 

 

K3922 

McNab013 

2013-05-12 

2014-06-02 

S929 

34 

7609865463 

 

K3922 

McNab013 

2013-05-12 

2015-08-02 

M330 

43 

7633088852 

 

K3922 

McNab013 

2013-05-12 

2016-07-30 

P301 

53 

7682907965 

 

K3922 

McNab013 

2013-05-12 

2017-08-12 

P301 

52 

7682907965 

 

K3922 

McNab013 

2013-05-12 

2018-07-22 

S929 

51 

7609865463 

 

T8832 

McNab013 

2012-03-26 

2012-08-14 

K339 

19 

7602907550 

 

T8832 

McNab013 

2012-03-26 

2013-09-01 

S929 

26 

7609865463 

 

T8832 

McNab013 

2012-03-26 

2014-08-15 

K339 

32 

7602907550 

 

T8832 

McNab013 

2012-03-26 

2015-07-28 

K339 

40 

7602907550 

 

T8832 

McNab013 

2012-03-26 

2016-08-11 

T975 

42 

7646746741 

 

P9742 

Smith002 

2014-05-10 

2014-09-11 

K339 

14 

7602907550 

 

P9742 

Smith002 

2014-05-10 

2015-08-10 

S300 

25 

7629920821 

 

P9742 

Smith002 

2014-05-10 

2016-08-29 

K339 

35 

7602907550 

 

P9742 

Smith002 

2014-05-10 

2017-09-01 

S929 

44 

7609865463 

 

P9742 

Smith002 

2014-05-10 

2018-08-22 

S929 

51 

7609865463 

 

M3110 

Smith002 

2015-05-12 

2015-09-12 

S300 

15 

7629920821 

 

M3110 

Smith002 

2015-05-12 

2016-09-10 

S300 

26 

7629920821 

 

M3110 

Smith002 

2015-05-12 

2017-08-17 

K339 

34 

760290755 

Question 3

Identify the Functional Dependencies in this table. Your answer should conform to the following format: If, taken together, attributes A and B determine C, show it this way:

A + B → C 

Question 4

The above table in question 3 is susceptible to update, deletion, and insertion anomalies. Examine the ten possible problems. (Some of these problems are examples of normalization anomalies, and some are not.) For each of the 10 problems (a to j) below state whether there are 

  

Update 

Anomaly 

Deletion 

Anomaly 

Insertion 

Anomaly 

None of these. 

  

Problem 

 

a. Instead of   entering ‘2014-08-15’ for one of the weighing dates, ‘2104-08-15’ could be   entered instead. 

 

b. We cannot   insert information about a new vet (such as their mobile phone number) until   they have weighed a sheep. 

 

c. If we delete   all the tuples about a particular sheep, we lose all the information about   that sheep. 

 

d. If we delete   all the tuples for sheep K3922, we lose information about Vet M330’s mobile   phone number. 

 

e. We cannot   enter ownership and birthdate information about a sheep until it has been   weighed. 

 

f. A Vet could   record a sheep’s weight incorrectly. 

 

g. If a Vet   changes their mobile phone number, we could record the change in some of the   tuples with thier ID, and not in others. 

 

h. If a Vet   changes their mobile phone number, we could record the new number   incorrectly. 

 

i. If we delete   information about a Vet, we lose their mobile phone number. 

 

j. If sheep is   sold to a new owner, we could change some of its Owner values but not others.   

Question 5

Split the above  table from question 3 above into tables in BCNF, specifying the Primary Key of each table, and showing the first and last tuples for each table (following the order of tuples in the original table).