NIELIT A Level Introduction to Database Management System Assignment

NIELIT A Level

Introduction to Database Management System

Assignment 8

Normalization of the CAR_SALE Table.

The purpose of this exercise is for you to demonstrate your ability to take a database schema and convert it up through the Third Normal Form. Upon completion of this exercise you will have:

a) Listed the functional dependencies for a database schema
b) Explained why a specific schema is not in Second or Third Normal Form
c) Normalized a given schema into the Third Normal Form

The below scenario has been created to help you determine the table structures r

equired for each of the subsequent normalized tables.

Scenario
You are given the database schema for a car sales database as follows:

Table Name: CAR_SALE

ColumnName Car_num Date_sold Salesman Commission_percent Discount_amt
KeyType Primary Primary

Assuming that a car can be sold by multiple salesmen and, therefore, the attributes of ar_num and Salesman {Car_num, Salesman} taken together are the primary key for the relation. In addition, you are told that the date the car sells determines the discount amount and that each

salesman has a unique commission rate.

Directions
To complete exercise one, you should do the following:

a) Read and complete each of the three steps identified under exercise two
b) Create a response for each step listed under exercise two
c) Create your response using MS Word
d) When appropriate, use the table feature within Word to create your tables.

e) Save the document as identified in the “Labs” section of the roadmap and upload the file in the course communication space drop-box.

Step 1

List the functional dependencies in the relation CAR_SALE. Based on the given primary key, decide if the dependency is

a) completely dependent on the primary key (primary keydependency),
b) partially dependent on the primary key (partial key dependency), or

c)dependent on a non-key column (transitive dependency) for each of the Functional Dependencies you list.

Step 2
Explain why the relation CAR_SALE is not in 2NF or 3NF

Step 3
Normalize the relation CAR_SALE into 3NF. Show your results by providing the resulting
table schemas.

Next Assignment