Question

View 4

Seneca Courses Inc.

Customer Forms

All customer information for SCI is maintained on file forms. The following are sample customer information forms:

CUST_ID	78		CUST _ID	54
NAME	YORK UNIVERSITY		NAME	SENECA COLLEGE
LOCATION	NORTH YORK		LOCATION	NORTH YORK
SALES_REP	ANDRE MARTIN		SALES_REP	PARVEZ RAI

CUST _ID	74		CUST _ID	43
NAME	RYERSON		NAME	INDIGO BOOKS
LOCATION	TORONTO		LOCATION	TORONTO
SALES_REP	PARVEZ RAI		SALES_REP	ANDRE MARTIN

Business Rules:

1. NAME must always be different for each customer.

1. SALES_REP must always have a value.

SALES_REP must be an existing employee.

A customer only has one sales rep, but the sales_rep may have more than one customer.

Normalize the above user view. Document all steps including UNF, 1NF, Dependencies, 2NF, and 3NF and explain or list the primary key and foreign

Answer 1

1). ANSWER :

GIVENTHAT :

1. Find all functional dependencies.

2. Check for partial dependencies and divide relation in 2NF.

3. Check for transitive dependencies and divide relation in 3NF.

Functional Dependencies:

Cust_ID -> Name, Location, Sales_Rep

Name -> Cust_ID, Location, Sales_Rep

Use Cust_ID as primary key.

There is no partial dependency. Thus table alredy in 2NF.

Functional dependency 2 is transitive dependency. Thus,

Customer(Cust_ID, Location, Sales_Rep)

Cust_Name(Cust_ID, Name)

These relations are in 3NF and satisfy required rules. Each customer can have single Sales_Rep but each Sales_Rep cn have many customers.