List the non-trivial functional dependencies in the table shown in Figure 1. Sta
ID: 3866853 • Letter: L
Question
List the non-trivial functional dependencies in the table shown in Figure 1. State any assumptions you make about the data shown in this table.
Modify the table to be in Boyce-Codd normal form.
Display the data in Figure 1 using the tables in Boyce-Codd normal form.
15 points] An agency called Instant Cover supplies part-time/temporary staff to hotels within Scotland. The table shown in Figure 1 lists the time spent by agency staff working at various hotels. The National Insurance Number (NIN) is unique for every member of staff. NIN | contract o | hours | eName hNo hLoc 1135 C1024 1057 C1024 1068 C1025 1135 C1025 16 Smith H25 East Kilbride 24 Hocine D H25 East Kilbride 28 White T H4 Glasgow 15 SmithH4 Glasgow Figure 1: Instant Cover's contractsExplanation / Answer
A relational schema R is considered to be in Boyce–Codd normal form (BCNF) if, for every one of its dependencies X Y, one of the following conditions holds true:
X Y is a trivial functional dependency (i.e., Y is a subset of X)
X is a superkey for schema R
Boyce-Codd normal form is expressed as “Each attribute must represent a fact about the key, the whole key, and nothing but the key.”
The nontrivial functional dependencies in the table are:
List the non-trivial functional dependencies in the table shown
NIN -> contractNo,hours
eName -> hNo,hLoc
We can easily see that the only key is the set {NIN, eName}.
The same data can be stored in a BCNF schema. However, this time we would need three tables.
NIN ContractNo Hours
1135 C1024 16
eName hNo hLoc
smith j H25 East Kilbride
NIN eName
1135 smith J
The key of the first table is {NIN}. The key of the second table is {eName}. The key of the third table is {NIN, eName}. There are no functional dependencies violating the BCNF rules, so the schema is in Boyce-Codd normal form.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.