# Database Lab 8 Solution

### Step 1

UNF:

• dept [dept, aisle_no (prod_code, , price, um) ]
1NF:
• dept [dept_id, dept_name, aisle_no]
• dept_product [dept, prod_code, price, um ]
2NF:
• dept [dept_id, dept_name, aisle_no ]
• product [prod_code, price, um, dept_id (FK) ]
3NF: Same

Note:

1. examing the relationship between department and product, we discover that it is a 1:M, therefore we do not need the composite table, dept_product. So it is eliminated and the foreign key placed in the product table.
2. it may be debatable whether department actually determines aisle number. This may be true in a small grocery store but in a large grocery store, products from a department may be found in multiple aisles. This sho

### Step 2

UNF:

• supplier [supplier_id, supplier_name, (prod_code, prod_desc, cost, markup, dept_cd ) ]
1NF:

• supplier [supplier_id, supplier_name]
• supplier_product [supplier_id, prod_code, prod_desc, cost, markup, dept_cd ]
2NF:

• supplier [supplier_id, supplier_name]
• supplier_product [supplier_id, prod_code]
• product [prod_code, prod_desc, cost, markup, dept_cd ]
note: if we were getting a product from more than 1 supplier, then the cost attribute would go ino the supplier_product table.

3NF:

• supplier [supplier_id, supplier_name]
• product [prod_code, prod_desc, cost, markup, dept_cd, supplier_id (FK) ]
note: examing the relationship between supplier and product, we discover that it is a 1:M, therefore we do not need the composite table, supplier_product. So it is eliminated and the foreign key placed in the product table.

### Step 3

View 1 solution:

3NF:

• dept [dept_id, dept_name, aisle_no ]
• product [prod_code, price, um, dept_id (FK) ]
View 2 Solution:

3NF:

• supplier [supplier_id, supplier_name]
• product [prod_code, prod_desc, cost, markup, dept_cd, supplier_id (FK) ]
Merged 3NF solution:

• product [ prod_code, prod_desc, um, dept_cd (FK), supplier_id (FK), cost, markup ]
• dept [ dept_cd, dept_name, aisle_no ]
• supplier [ supplier_id, supplier_name ]
Topic revision: r2 - 2013-11-27 - JimSkon

TWiki

* Webs

Copyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback