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