none
Clarification on First Normal Form

    Question

  • 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 Project Code, Project Description, and Project Supervisor are repeating fields.

    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 to demonstrate my point:

    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

    Wednesday, May 23, 2018 9:45 PM

All replies

  • Hi,

    It would help to know where you found those examples.

    1NF does specify "no repeating groups," but the first example could be wrong if a product can come from multiple suppliers, and in the second example, the customer info should have been moved at 2NF.

    Wednesday, May 23, 2018 10:06 PM
  • I have added sources for clarity
    Wednesday, May 23, 2018 11:01 PM