html5-img
1 / 43

IT-3101 Database Management Systems

IT-3101 Database Management Systems. By- Jesmin Akhter Assistant Professor, IIT, Jahangirnagar University. Lecture 05 Relational Database Design Normalization-Part-2. Outline. Overview of Relational DBMS Normalization. Part_ID  Description Part_ID  Price Part_ID, Comp_ID  No.

peri
Download Presentation

IT-3101 Database Management Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IT-3101 Database Management Systems By- JesminAkhter Assistant Professor, IIT, Jahangirnagar University

  2. Lecture 05 Relational Database DesignNormalization-Part-2

  3. Outline • Overview of Relational DBMS • Normalization

  4. Part_ID  Description Part_ID  Price Part_ID, Comp_ID  No Example 3: Determine NF Comp_ID and No are not determined by the primary key; therefore, the relation is NOT in 1 NF. No sense in looking at partial or transitive dependencies.

  5. Part_ID Description Part_ID  Price Part_ID, Comp_ID No Example 3: Determine NF • In your solution you will write the following justification: • There are M/V attributes; therefore, not 1NF • Conclusion: The relation is not normalized.

  6. Bringing a Relation to 1NF

  7. Option 1: Make a determinant of the repeating group (or the multivalued attribute) a part of the primary key. Composite Primary Key Bringing a Relation to 1NF

  8. Option 2: Remove the entire repeating group from the relation. Create another relation which would contain all the attributes of the repeating group, plus the primary key from the first relation. In this new relation, the primary key from the original relation and the determinant of the repeating group will comprise a primary key. Bringing a Relation to 1NF

  9. Bringing a Relation to 1NF

  10. Composite Primary Key Bringing a Relation to 2NF

  11. Goal: Remove Partial Dependencies Composite Primary Key Partial Dependencies Bringing a Relation to 2NF

  12. Remove attributes that are dependent from the part but not the whole of the primary key from the original relation. For each partial dependency, create a new relation, with the corresponding part of the primary key from the original as the primary key. Bringing a Relation to 2NF

  13. Bringing a Relation to 2NF

  14. Goal: Get rid of transitive dependencies. Bringing a Relation to 3NF Transitive Dependency

  15. Remove the attributes, which are dependent on a non-key attribute, from the original relation. For each transitive dependency, create a new relation with the non-key attribute which is a determinant in the transitive dependency as a primary key, and the dependent non-key attribute as a dependent. Bringing a Relation to 3NF

  16. Bringing a Relation to 3NF

  17. ORDER (order-no, order-date, cust-no, cust-name, cust-add, (prod-no, prod-desc, unit-price, ord-qty, line-total)*, order-total Unnormalised Normal Form (UNF)

  18. Definition: A relation is in 1NF if, and only if, all its underlying attributes contain atomic values only. Remove repeating groups into a new relation A repeating group is shown by a pair of brackets within the relational schema. Steps from UNF to 1NF: • Remove the outermost repeating group (and any nested repeated groups it may contain) and create a new relation to contain it. • Add to this relation a copy of the PK of the relation immediately enclosing it. • Name the new entity (appending the number 1 to indicate 1NF) • Determine the PK of the new entity • Repeat steps until no more repeating groups. ORDER (order-no, order-date, cust-no, cust-name, cust-add, (prod-no, prod-desc, unit-price, ord-qty, line-total)*,order-total First Normal Form (1NF)

  19. 1. Remove the outermost repeating group (and any nested repeated groups it may contain) and create a new relation to contain it. (rename original to indicate 1NF) ORDER-1 (order-no, order-date, cust-no, cust-name, cust-add, order-total (prod-no, prod-desc, unit-price, ord-qty, line-total) 2. Add to this relation a copy of the PK of the relation immediately enclosing it. (order-no, prod-no, prod-desc, unit-price, ord-qty, line-total) 3. Name the new entity (appending the number 1 to indicate 1NF) ORDER-LINE-1 (order-no, prod-no, prod-desc, unit-price, ord-qty, line-total) ORDER (order-no, order-date, cust-no, cust-name, cust-add, (prod-no, prod-desc, unit-price, ord-qty, line-total)*,order-total ORDER-1 (order-no, order-date, cust-no, cust-name, cust-add, order-total 4. Determine the PK of the new entity ORDER-LINE-1 (order-no, prod-no, prod-desc, unit-price, ord-qty, line-total) Example - UNF to 1NF

  20. Definition: A relation is in 2NF if, and only if, it is in 1NF and every non-key attribute is fully dependent on the primary key. Steps from 1NF to 2NF: • Remove the offending attributes that are only partially functionally dependent on the composite key, and place them in a new relation. • Add to this relation a copy of the attribute(s) which are the determinants of these offending attributes. These will automatically become the primary key of this new relation. • Name the new entity (appending the number 2 to indicate 2NF) • Rename the original entity (ending with a 2 to indicate 2NF) Second Normal Form (2NF) Remove partial functional dependencies into a new relation

  21. 1. Remove the offending attributes that are only partially functionally dependent on the composite key, and place them in a new relation. ORDER-LINE-1 (order-no, prod-no, ord-qty, line-total) ORDER-LINE-1 (order-no, prod-no, ord-qty, line-total) (prod-desc, unit-price) 2. Add to this relation a copy of the attribute(s) which determines these offending attributes. These will automatically become the primary key of this new relation.. (prod-no, prod-desc, unit-price) 3. Name the new entity (appending the number 2 to indicate 2NF) PRODUCT-2 (prod-no, prod-desc, unit-price) 4. Rename the original entity (ending with a 2 to indicate 2NF) ORDER-LINE-2 (order-no, prod-no, ord-qty, line-total) Example - 1NF to 2NF ORDER-LINE-1 (order-no, prod-no, prod-desc, unit-price, ord-qty, line-total)

  22. Definition: A relation is in 3NF if, and only if, it is in 2NF and every non-key attribute is non-transitively dependent on the primary key. Remove transitive dependencies into a new relation Steps from 2NF to 3NF: • Remove the offending attributes that are transitively dependent on non-key attribute(s), and place them in a new relation. • Add to this relation a copy of the attribute(s) which are the determinants of these offending attributes. These will automatically become the primary key of this new relation. • Name the new entity (appending the number 3 to indicate 3NF) • Rename the original entity (ending with a 3 to indicate 3NF) Third Normal Form (3NF)

  23. 1. Remove the offending attributes that are transitively dependent on non-key attributes, and place them in a new relation. (cust-name, cust-add ) 2. Add to this relation a copy of the attribute(s) which determines these offending attributes. These will automatically become the primary key of this new relation.. ORDER-2 (order-no, order-date, cust-no, order-total (cust-no, cust-name, cust-add ) 3. Name the new entity (appending the number 3 to indicate 3NF) CUSTOMER-3 (cust-no, cust-name, cust-add ) ORDER-2 (order-no, order-date, cust-no, cust-name, cust-add, order-total ORDER-2 (order-no, order-date, cust-no, order-total 4. Rename the original entity (ending with a 3 to indicate 3NF) ORDER-3 (order-no, order-date, cust-no, order-total Example - 2NF to 3NF

  24. prod-no PRODUCT order-no ORDER shows contains places belongs to placed by PRODUCT-2 (prod-no, prod-desc, unit-price) CUSTOMER-3 (cust-no, cust-name, cust-add ) order-no, prod-no cust-no ORDER-LINE part of CUSTOMER ORDER-LINE-2 (order-no, prod-no, ord-qty, line-total) ORDER-3 (order-no, order-date, cust-no, order-total Example - Relations in 3NF

  25. Case Study on Normalization • Consider the table EMP_DEPT_PROJ • And the following dependencies which exist in the above table:

  26. Steps to Normalize the database • D • Thus we will have 3 tables

  27. Steps to Normalize the database • Step 2 • Let us now identify the transitive dependency and remove it.

  28. Steps to Normalize the database • Step 3: • Let us now identify the non key determinants and remove them. • Thus we will have 5 tables

  29. Case Study on Normalization •  Suppose The Gill Art Gallery wishes to maintain data on their customers, artists and paintings. They may have several paintings by each artist in the gallery at one time. Paintings may be bought and sold several times. In other words, the gallery may sell a painting, then buy it back at a later date and sell it to another customer. • UNF: • customer [ custno, cust_name, cust_addr, cust_phone, ( artist_id, artist_name, art_title, pur_date, price) ] •  1NF: • customer [ custno, cust_name, cust_addr, cust_phone] • cust_art [ custno, art_code, pur_date, artist_id, artist_name, art_title, price ]

  30. Case Study on Normalization • UNF: • customer [ custno, cust_name, cust_addr, cust_phone, ( artist_id, artist_name, art_title, pur_date, price) ] •  1NF: • customer [ custno, cust_name, cust_addr, cust_phone] • cust_art [ custno, art_code, pur_date, artist_id, artist_name, art_title, price ] • note: the key chosen for the repeating group is the piece of art itself (a code was assigned), however because a piece of art may be bought by a customer more than once, the purchase date was added as part of the key to make the rows unique.

  31. Case Study on Normalization •  1NF: • customer [ custno, cust_name, cust_addr, cust_phone] • cust_art [ custno, art_code, pur_date, artist_id, artist_name, art_title, price ] • 2NF: • customer [ custno, cust_name, cust_addr, cust_phone] • cust_art [ custno, art_code, pur_date, price ] • art [ art_code, art_title, artist_id, artist_name ]

  32. Case Study on Normalization • 2NF: • customer [ custno, cust_name, cust_addr, cust_phone] • cust_art [ custno, art_code, pur_date, price ] • art [ art_code, art_title, artist_id, artist_name ] • 3NF: • customer [ custno, cust_name, cust_street, cust_city, cust_prov, cust_pstlcd, cust_phone] • cust_art [ custno, art_code, pur_date, price ] • art [ art_code, art_title, artist_id(FK) ] • artist [ artist_id, artist_fname, artist_lname ]

  33. Case Study on Normalization • 3NF: • customer [ custno, cust_name, cust_street, cust_city, cust_prov, cust_pstlcd, cust_phone] • cust_art [ custno, art_code, pur_date, price ] • art [ art_code, art_title, artist_id(FK) ] • artist [ artist_id, artist_fname, artist_lname ]

  34. Review Class on Relational Algebra

  35. Relations r, s: r s Natural Join Operation – Example B D E A B C D 1 3 1 2 3 a a a b b           1 2 4 1 2      a a b a b r s r.A, r.B, r.C, r.D, s.E (r.B = s.Br.D = s.D (r x s)) A B C D E      1 1 1 1 2      a a a a b     

  36. The branch Relation The customer Relation Account Relation The depositor Relation

  37. borrower Relation • The Loan Relation

  38. Result of customer-name, branch-name(depositor account) Result of customer-name, loan-number, amount(borrower loan)

  39. Division Operation – Example A B Relations r, s: B            1 2 3 1 1 1 3 4 6 1 2 1 2 s r  s: A r  

  40. Another Division Example Relations r, s: A B C D E D E         a a a a a a a a         a a b a b a b b 1 1 1 1 3 1 1 1 a b 1 1 s r A B C r  s:   a a  

  41. Find all customers who have an account at all branches located in Brooklyn city. customer-name, branch-name(depositoraccount)  branch-name (branch-city = “Brooklyn” (branch)) Example Queries depositor account branch

  42. Result of customer-name, branch-name(depositor account) Result of branch-name(branch-city = “Brooklyn”(branch)) customer-name, branch-name(depositoraccount)  branch-name (branch-city = “Brooklyn” (branch))

  43. Thank You

More Related