There are two problems and they are about functional

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.