normalization examples - Example 1 Assumption A customer can have multiple orders and an order can include multiple products 0NF CUSTOMER ORDER(CustName

normalization examples - Example 1 Assumption A customer...

This preview shows page 1 - 4 out of 7 pages.

Example 1: Assumption: A customer can have multiple orders and an order can include multiple products. 0NF CUSTOMER ORDER (CustName, Order No, Prod No, ProdDesc, Qty, Cust Address, Date Ordered) 1NF - remove multi valued attributes CUSTOMER (CustName, CustAddress) CUSTOMER ORDER (CustName, Order No, Prod No, ProdDesc, Qty, Date Ordered) 2NF - remove partial dependencies CUSTOMER (CustName, CustAddress) ORDER LINE (Order No, Prod No, Qty, Date Ordered) PRODUCT (ProdNo, ProdDesc) ORDER (OrderNo, CustName) 3NF, BCNF As above Assumption: A customer can have multiple orders but an order can be for only 1 product. CustName and OrderNo preassigned as keys. 0NF CUSTOMER ORDER (CustName, OrderNo, ProdNo, ProdDesc, Qty, CustAddress, DateOrdered) 1NF - remove multi valued dependencies CUSTOMER (CustName, CustAddress) ORDER (CustName, OrderNo, ProdNo, ProdDesc, Qty, DateOrdered) 2NF - remove partial dependencies CUSTOMER (CustName, CustAddress) CUSTOMER ORDER (CustName, OrderNo) ORDER (OrderNo, ProdNo, ProdDesc, Qty, DateOrdered) 3NF - remove transitive dependencies CUSTOMER (CustName, CustAddress) CUSTOMER ORDER (CustName, OrderNo) ORDER (OrderNo, ProdNo, Qty, DateOrdered) PRODUCT (ProdNo, ProdDesc)
Image of page 1
BCNF - resolve intra key dependencies CUSTOMER (CustName, CustAddress) CUSTOMER ORDER (CustName, OrderNo) - CustName becomes just a foreign key ORDER (OrderNo, ProdNo, Qty, DateOrdered) PRODUCT (ProdNo, ProdDesc) Example 2 Unnormalized Table Staff No. Name Address Tel No. Position S123 Morgan Russell 23A George Street 01506-67676 Nurse S098 Carol Cummings 15 High Street Edinburgh 0131-334-5677 Staff Nurse When moving from an Unnormalized table to 1NF you need to take out repeating groups. One approach is to fill in empty cells. A second is to split out the repeating elements into a new table. 1NF Table Staff No. Staff Name Address Tel No. Position S098 Carol Cummings 15 High Street Edinburgh 0131-334-5677 Staff Nurse S123 Morgan Russell 23A George Street 01506-67676 Nurse Ward No. Ward Name Location Tel Extn. Charge Nurse Ward 11 Orthopaedic Block E 7711 Moira Samuel OR either of these approaches is acceptable for removing repeating groups. 1NF Table Staff No. Staff Name Address Tel No. Position S098 Carol Cummings 15 High Street Edinburgh 0131-334-5677 Staff Nurse S123 Morgan Russell 23A George Street 01506-67676 Nurse When moving from 1NF to 2NF you remove partial dependencies. Staff and Ward info are partially dependent on the PK 2NF Table Staff No. Ward No. Week beginning Shift
Image of page 2
S098 Ward 11 1/9/1998 Late S123 Ward 11 1/9/1998 Late The Shift is dependent on the StaffNo, WardNo, and Week beginning Staff No. Staff Name Address Tel No. Position S098 Carol Cummings 15 High Street Edinburgh 0131-334-5677 Staff Nurse S123 Morgan Russell 23A George Street 01506-67676 Nurse Ward No. Ward Name Location Tel Extn. Charge Nurse Ward 11 Orthopaedic Block E 7711 Moira Samuel When moving from 2NF to 3NF one must remove transitive dependencies A transitive dependency exists with the charge nurse information.
Image of page 3
Image of page 4

You've reached the end of your free preview.

Want to read all 7 pages?

  • Summer '12
  • Database normalization, Ward, Steve Smith, Third normal form, Second normal form, High Street Edinburgh

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes