Reference no: EM13333053

There are two problems and they are about functional dependencies and normalization.

In each problem, you are given a set of functional dependencies. If necessarily, you have to derive and use additional functional dependencies based on the given functional dependencies.

These additional functional dependencies must be solely based on the given functional dependencies and you should not arbitrarily create additional functional dependencies. In other words, an additional functional dependency you create must be a logical consequence of given functional dependencies

Problem 1

The following is a part of information that can be obtained from a typical motor vehicle registration card kept in a DMV (or RMV).
RegNo: registration number
RegYear: registration year
DOB: owner's date of birth
RegExpDate: registration expiration date
TagNo: license plate number
TitleNo: vehicle title number
VIN: vehicle identification number
Year: year vehicle was made
Weight: vehicle weight
Make: vehicle manufacturer
Address: owner address
Name: owner name
RegIssueDate: registration issue date
Type: vehicle type
DL: owner driver's license number
TitleFeeAmt: title fee amount
The following functional dependencies were identified by a database designer.
FD1: VIN → {Year, Weight, Make, Type}

FD2: TitleNo → {Year, Weight, TitleFeeAmt}
FD3: DL → {DOB, Name, Address}
FD4: RegNo → {RegYear, DOB, RegExpDate, TagNo, RegIssueDate}
FD5: RegNo → {TitleNo, VIN, DL}
FD6: TitleNo → {VIN, DL}
Using the information given above, create a set of relational tables that are normalized to BCNF.

Problem 2

Consider the following relation R and four functional dependencies on it:
R = {A, B, C, D, E, F, G, H}
FD1: A → {B, C, D}
FD2: B → D
FD3: E → {F, G, H}
FD4: G → {H, E}
Find all keys of the relation R.
Recall that a key is a minimal set of attributes that uniquely identifies individual tuples.