Normalization 2. NIU trucking wants to keep track of all of its truck and their

Question

Normalization

2. NIU trucking wants to keep track of all of its truck and their base cities. Given the following relation, produce a normalized set of relations which are normalized up through 3rd normal form.

TRUCK (TruckNum, TruckType, TypeDesc, TruckMiles, DatePurchased, TruckSerialNum, BaseCity, BaseState, BasePhone, BaseManagerName, ManagerPhone, BasePhone)

Business rules:

1. A truck is based at a single base.

2. A base can be the base for many trucks.

2a. List ALL the FDs for the table that hold based upon the descriptions of the constraints above.

2b. Is the table definition in 1NF? Why or why not? If not, convert the table to 1NF table(s).

2c. Is/are the table(s) in 2NF? Why or why not? If not, convert the table(s) to table(s) that are in 2NF. Explain why they are now in 2NF?

2d. Are the tables in 3NF? Why or why not? If not, convert the table to 3NF. Explain why they are now in 3NF?

2e. List all of the foreign keys in question 2d. Identify them by the table name and state which table(s) and corresponding attribute(s) they relate to.

Explanation / Answer

The given relation:

TRUCK (TruckNum, TruckType, TypeDesc, TruckMiles, DatePurchased, TruckSerialNum, BaseCity, BaseState, BasePhone, BaseManagerName, ManagerPhone, BasePhone)

2a.

Identify a primary key to identify functional dependencies. A primary key identifies each row uniquely.

In the given table TruckNum is the primary key. Using TruckNum any row in the table can be identified uniquely. ( underline the primary key)

Now, identify the functional dependencies.

Full functional dependencies:

TruckNumàTruckType,TypeDesc,TruckMiles,DatePurchased,TruckSerialNum, BaseCity, BaseState, BasePhone, BaseManagerName, ManagerPhone

Note: there is no partial depndencies

Transitive dependencies:

TruckNumàBaseState, BasePhone, BaseMangerName ( Since, TruckNumàBaseCity and BaseCity à BaseState,BasePhone,BaseMangerName)

TruckNumà MangaerPhone (Since, TruckNumàBaseManagerName and BaseManagrNameàManagerPhone)

2b.

To say a table is in 1NF, the table must not have repeating groups and all attibutes must depend on the primary key.

The table has “Base Phone” twice in the table.

To make the given relation 1NF, remove one BasePhone.

Now the following is the 1NF table:

TRUCK (TruckNum, TruckType, TypeDesc, TruckMiles, DatePurchased, TruckSerialNum, BaseCity, BaseState, BasePhone, BaseManagerName, ManagerPhone)

TruckNumà TruckType, TypeDesc, TruckMiles, DatePurchased, TruckSerialNum, BaseCity, BaseState, BasePhone, BaseManagerName, ManagerPhone

2c.

To say a table is in 2NF, it must be in 1NF and there exist no partial dependencies in the table.

Observe the 1NF table, in which there are no partial dependencies. Therefore, the 1NF table is also in 2NF.

TRUCK (TruckNum, TruckType, TypeDesc, TruckMiles, DatePurchased, TruckSerialNum, BaseCity, BaseState, BasePhone, BaseManagerName, ManagerPhone)

TruckNumà TruckType, TypeDesc, TruckMiles, DatePurchased, TruckSerialNum, BaseCity, BaseState, BasePhone, BaseManagerName, ManagerPhone

2.d

To say a table is in 3NF, it must be in 2NF and there exist no transitive dependencies in the table. Therefore the above 2NF table is not in 3NF.

Identify the following transitive dependencies:

TruckNumàBaseState, BasePhone, BaseMangerName ( Since, TruckNumàBaseCity and BaseCity à BaseState,BasePhone,BaseMangerName)

TruckNumà MangaerPhone (Since, TruckNumàBaseManagerName and BaseManagrNameàManagerPhone)

To convert the table into 3NF, split the table as follows:

Table 1: TruckNum , TruckType, TypeDesc, TruckMiles, DatePurchased, TruckSerialNum, BaseCity

Table 2: BaseCity, BaseState,BasePhone, BaseManagrName

Table 3: BaseManagrName, ManagerPhone

2e.

A foreign key is an attribute in a table , whose values matches with the primary key values in some other table.

For Table 1 : BaseCity is the foreign key to table 2

For table 2: Base ManagerName is the foreign key to Table 3

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.