For the most part, I understand first, second, and third normal form. I would just like clarification on a few normalization examples I found on the net. The first example is as follows:
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
3NF:
Supplier: supplier_id, supplier_name
Product: prod_code, prod_desc, dept_cd (FK), supplier_id (FK), cost, markup
dept: dept_cd, dept_name, aisle_no
Source: http://cs.mvnu.edu/twiki/bin/view/Main/Lab8DB2013
When moving from UNF to 1NF, are we simply moving the values that make up repeating groups to the new entity along with the primary key from the original entity? In the above example, products and departments are considered repeating groups. Thus, both are moved to the second entity.
This can also be demonstrated in the following example:
0NF
ORDER(order#, customer#, name, address, orderdate(product#, description, quantity, unitprice))
1NF
ORDER(order#, customer#, name, address, orderdate)
ORDER_LINE(order#, product#, description, quantity, unitprice)
2NF
ORDER(order#, customer#, name, address, orderdate)
ORDER_LINE(order#, product#, quantity)
PRODUCT(product#, description, unitprice)
3NF
ORDER(order#, customer#, orderdate)
CUSTOMER(customer#, name, address)
ORDER_LINE(order#, product#, quantity)
PRODUCT(product#, description, unitprice)
Source: http://www4.comp.polyu.edu.hk/~cstyng/data.98/tutorials/norm5.html
In this case, only products are considered to be a repeating group, not customers. Therefore, customer attributes are left in the original entity. Do I understand this correctly?
EDIT: An additional Example
A software contract and consultancy firm maintain details of all the various projects in which its employees are currently involved. These details comprise: Normalize this data to Third Normal Form.
• Employee Number
• Employee Name
• Date of Birth
• Department Code
• Department Name
• Project Code
• Project Description
• Project Supervisor Assume the following and normalize this data into 3NF:
• Each employee number is unique.
• Each department has a single department code.
• Each project has a single code and supervisor.
• Each employee may work on one or more projects.
• Employee names need not necessarily be unique.
• Project Code, Project Description, and Project Supervisor are repeating fields.
UNF
Employee Number, Employee Name, Date of Birth, Department Code, Department Name, Project Code, Project Description, Project Supervisor
1NF
Employee Number, Employee Name, Date of Birth, Department Code, Department Name
Employee Number, Project Code, Project Description, Project Supervisor
2NF
Employee Number, Employee Name, Date of Birth, Department Code, Department Name
Employee Number, Project Code,
Project Code, Project Description, Project Supervisor
3NF
EmployeeNumber,Employee Name, Date of Birth, *Department Code
Department Code, Department Name
Employee Number, Project Code
Project Code, Project Description, Project Supervisor
