1 / 64

Theory of Normalization

Theory of Normalization. There are many types of normalized forms existing in a data base environment. First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Boyce-Codd Normal Form (BCNF) Fourth Normal Form (4NF) Fifth Normal Form (5NF).

jam
Download Presentation

Theory of Normalization

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. Theory of Normalization Learners Support Publications www.lsp4you.com

  2. There are many types of normalized forms existing in a data base environment • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • Boyce-Codd Normal Form (BCNF) • Fourth Normal Form (4NF) • Fifth Normal Form (5NF) Learners Support Publications www.lsp4you.com

  3. What is the need of Normalization ? Learners Support Publications www.lsp4you.com

  4. Normalization – The Need • to reduce redundancy => No inconsistencies • No loss of information from database over the life time of database Learners Support Publications www.lsp4you.com

  5. STUDENTS Relation • Roll No • Name • Course • Phone Number • Major • Professor • Grade Learners Support Publications www.lsp4you.com

  6. Different constraints • Students Roll No should be unique • A student can join many courses • A particular student has a single phone number, major • A course is taught by only one Professor • One grade is given to a student for a particular course Learners Support Publications www.lsp4you.com

  7. Constraints • Restricts the design of the Data Base Management System Learners Support Publications www.lsp4you.com

  8. A Relational State Learners Support Publications www.lsp4you.com

  9. A Relational State The instance in which the data base is currently Learners Support Publications www.lsp4you.com

  10. A Relational State Learners Support Publications www.lsp4you.com

  11. PROBLEMS • Redundancy • Update Anomalies • Insertion Anomalies • Deletion Anomalies Learners Support Publications www.lsp4you.com

  12. STUDENTS Relation Redundancy Of Data Learners Support Publications www.lsp4you.com

  13. STUDENTS Relation Redundancy Of Data Learners Support Publications www.lsp4you.com

  14. STUDENTS Relation Update Anomalies 696453 Learners Support Publications www.lsp4you.com

  15. STUDENTS Relation Insertion Anomalies Learners Support Publications www.lsp4you.com

  16. STUDENTS Relation Deletion Anomalies Learners Support Publications www.lsp4you.com

  17. Functional Dependence • FDs are constraints on Legal Relations • Express facts about the enterprise that we are modeling with the database Learners Support Publications www.lsp4you.com

  18. Identifying Functional Dependence There is no algorithmic method of identifying dependency. We have to use our commonsense and judgement to specify dependencies. Learners Support Publications www.lsp4you.com

  19. Dependency Diagram Phone Name Major Grade Course Prof. Key Attributes Learners Support Publications www.lsp4you.com

  20. Problems in the STUDENT relation scheme can be remedied by DECOMPOSITION Learners Support Publications www.lsp4you.com

  21. Desirable Decomposition • Lossless Join Decomposition • On taking JOIN of the decomposed relations have the same set of tuples as Original relation • Dependency Preservation • Constraints should not be violated Learners Support Publications www.lsp4you.com

  22. Normalization • Is an attempt to eliminate/minimize the problems occurring due to data redundancy over the life time of the database. • Is an attempt for Good Database Design using Decomposition. Learners Support Publications www.lsp4you.com

  23. Normalization • Is an attempt to eliminate/minimize the problems occurring due to data redundancy over the life time of the database. • Is an attempt for Good Database Design using Decomposition. States of the database Learners Support Publications www.lsp4you.com

  24. FIRST NORMAL FORM (1NF) • This is also called a flat file and shown as 1NF. • There are no composite attributes and describes one property. • A Database Scheme is in 1NF if every Relational Scheme included in the Database Scheme is in 1NF. Learners Support Publications www.lsp4you.com

  25. This relation is in 1NF STUDENTS Relation Learners Support Publications www.lsp4you.com

  26. SECOND NORMAL FORM(2NF) • A relation is said to be in 2NF if it is in 1NF and non-key attributes are fully functionally dependent on the key attribute(s). • If the key has more than one attribute then no non-key attributes should be functionally dependent on a part of the key attributes. Learners Support Publications www.lsp4you.com

  27. Dependency Diagram Phone Name Major Grade Course Prof. Key Attributes Learners Support Publications www.lsp4you.com

  28. This leads Data Redundancy and Anomalies Dependency Diagram Phone Name Major Grade Course Prof. Key Attributes Learners Support Publications www.lsp4you.com

  29. This leads Data Redundancy and Anomalies Dependency Diagram Phone Name Major Grade Course Prof. Key Attributes This can be solved by means of DECOMPOSITION Learners Support Publications www.lsp4you.com

  30. Dependency Diagram Phone Name Major Grade Course Prof. Key Attributes Learners Support Publications www.lsp4you.com

  31. Dependency Diagram Phone Name Major Grade Course Prof. Key Attributes Learners Support Publications www.lsp4you.com

  32. Dependency Diagram Phone Name Major Grade Course Prof. Key Attributes Learners Support Publications www.lsp4you.com

  33. Dependency Diagram Phone Name Major Grade Course Prof. Key Attributes Learners Support Publications www.lsp4you.com

  34. STUDENT Relation IN 2NF • Student_Info (Name, Phone, Major) • Result (Name, Course, Grade) • C_T (Course, Prof.) Learners Support Publications www.lsp4you.com

  35. STUDENT Relation IN 2NF • Student_Info (Name, Phone, Major) • Result (Name, Course, Grade) • C_T (Course, Prof.) By combining these three relations, using common attributes, we will get the original relation Learners Support Publications www.lsp4you.com

  36. STUDENT Relation IN 2NF • Student_Info (Name, Phone, Major) Phone Name Major Name to Phone dependency and Name to Major dependency are being preserved in the STUDENT_INFO relation. Grade Course Prof. Key Attributes Learners Support Publications www.lsp4you.com

  37. STUDENT Relation IN 2NF • C_T (Course, Prof.) Course to Prof. is being preserved in the C_T relation Phone Name Major Grade Course Prof. Key Attributes Learners Support Publications www.lsp4you.com

  38. STUDENT Relation IN 2NF • Result (Name, Course, Grade) Name & Course to Grade is being preserved in the RESULT relation Name Grade Course Key Attributes Learners Support Publications www.lsp4you.com

  39. STUDENT Relation IN 2NF • Student_Info (Name, Phone, Major) • Result (Name, Course, Grade) • C_T (Course, Prof.) Learners Support Publications www.lsp4you.com

  40. Consider the following relation Learners Support Publications www.lsp4you.com

  41. Consider the following relation Roll No uniquely determines the Name Learners Support Publications www.lsp4you.com

  42. Consider the following relation Roll No uniquely determines the Depart. Learners Support Publications www.lsp4you.com

  43. Consider the following relation Roll No uniquely determines the Year Learners Support Publications www.lsp4you.com

  44. Consider the following relation Roll No uniquely determines the Hostel Learners Support Publications www.lsp4you.com

  45. Consider the following relation So this relation is in 2NF Learners Support Publications www.lsp4you.com

  46. Non-Key Attributes Key Attribute STUDENT Relation Learners Support Publications www.lsp4you.com

  47. Non-Key Attributes Key Attribute STUDENT Relation Functionally determines Learners Support Publications www.lsp4you.com

  48. Non-Key Attributes Key Attribute STUDENT Relation Functionally determines So a Dependency existing between the Year and the Hostel Learners Support Publications www.lsp4you.com

  49. Dependency Diagram Name Depart. Roll No Year Key Attribute We are in trouble Hostel Learners Support Publications www.lsp4you.com

  50. STUDENT Relation Redundancy of Data Learners Support Publications www.lsp4you.com

More Related