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