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
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback