Ans Doc441


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.
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
Anomaly Deletion
Anomaly Insertion
Anomaly None of these.

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).