Question

Assignment 7

Activities:

Using the order form below identify entities to be used in a relational database. Normalize to first, second, and third normal form. Clearly identify within each form with the tables in each form and specify any assumptions made.

Utilize the following format for each table: TableName(attribute,etc)

         Indicate Primary Key by underlining the attribute name. List Foreign Keys as such FK=attribute, below the table listing.

Example:

Sales Rep (EmployeeID, MTD Sales, YTD Sales, MTD Commission, YTD Commission, Commission Rate, Territory Number)

FK Territory Number Territory

FK EmployeeID Employee

Hint: Use a dependency diagram (Watch video in Instructor Notes) to see the normalization, then list out the different entities in each form of normalization . Begin with first normal form. Then complete second and then third. Remember that each normal form should be able to work without further normalization.

$EeHINC2heyn8TKkw1oGDWJOfiJF1D_Z3JLxcRLy0$

Deliverables:
1. A single Google Doc containing the following forms of normalization:

1. Entities with attribute list and key indication in 1st Normal form.

2. Entities with attribute list and key indication in 2nd Normal form.

3. Entities with attribute list and key indication in 3rd Normal form.

Answer 1

Answer:

There are three sub-forms Order From, Customer from and Product from, hence there will three entities and three tables for each entity.

Since each customer can have many products, the products table will have the primary key of the customer table as it's foreign key.

Also since each customer fills only one order from, the customer table will have the primary key of the order from as it's foreign key.

Entities with attribute list and key indication 1st normal form

OrderForm(order number, order date, promised date, sales person)

Customer (customer number , order_number,  name, address, city, state, zip) FK order_number

Product (product_number, customer_number, description, qty, price, item, total) FK customer_number

The above tables/relations are in 2NF and 3NF as well as there are no partial or transitive functional dependencies.

--------------------------------------------------------------------------------------------------