1 / 17

Database

Database . Normalization. 7. Normal Forms. Unnormalized data. 1NF. 2NF. 3NF. BCNF. 4NF. 5NF (PJNF)‏. DKNF. Normalized data. Steps in normalization . Remove. Table with repeating groups. Repeating Groups. 1 st Normal Form. Partial Dependencies. 2 nd Normal Form.

kuniko
Download Presentation

Database

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. Database Normalization

  2. 7 Normal Forms Unnormalized data 1NF 2NF 3NF BCNF 4NF 5NF (PJNF)‏ DKNF Normalized data

  3. Steps in normalization Remove Table with repeating groups Repeating Groups 1st Normal Form Partial Dependencies 2nd Normal Form Transitive Dependencies 3rd Normal Form Determinant / Candidate Keys Boyce-Codd Normal Form Multivalued Dependencies 4th Normal Form Removing Remaining anomalies 5th Normal Form (Project Join NF)‏ Removing Remaining anomalies Domain Key Normal Form

  4. Boyce-CoddNormal Form Determinant: “A determinant is a simple or composite attribute that functionally determines one or more other attributes”. A relation is in BCNF if and only if “Every determinant is a candidate key”. Y = f(X)‏ or X = f(Y)‏ Determinant is X or Y Y = mX + C Dependent is X or Y

  5. Boyce-CoddNormal Form Process All the determinants are identified. If the determinants are candidate keys, the relation is in BCNF.

  6. DNO DNAME EMPS MGR_NO MGR_NAME 1 ACCT 10 100 ALI 2 ADMN 15 200 HASAN 3 FINANCE 20 300 KHAN 4 PROD 25 400 RAZA BCNF Example with more than one candidate keys Consider DEPARTMENT relation with following attributes: This relation has three determinants: DNO, DNAME and MGR_NO. Each of the determinants is thus a candidate key. So, the relation is in BCNF.

  7. STID MAJOR ADVISOR 1 Database Asad 1 S/w Engineering Faisal 2 Comp. Communication Imran 3 Database Zahid 4 Database Asad BCNF Example 2 Consider SAM relation with following attributes: In this relation, no single attribute is a candidate key. That is, no single attribute is a determinant for the remaining two attributes.

  8. BCNF Example 2 • This relation is not in BCNF because: • Although ADVISOR is a determinant, it is not a candidate key. • Although STID is a determinant, it is not a candidate key. • The combination (STID, ADVISOR) is a candidate key for this relation. • To convert into BCNF, divide this relation into two relations. The attribute that is a determinant but not a candidate key, must be placed in a separate relation and must be the key of that relation.

  9. BCNF Relations in BCNF STID MAJOR ADVISOR MAJOR ADVISOR 1 Database Asad S/w Engineering Faisal 2 Comp. Communication Imran 3 Database Zahid 4 Database Asad

  10. STID ADVISOR ADVISOR MAJOR STID MAJOR ADVISOR 1 Asad Asad Database 1 Database Asad 1 Faisal Faisal S/w Engineering 1 S/w Engineering Faisal 2 Imran Imran Comp. Communication 2 Comp. Communication Imran 3 Zahid Zahid Database 3 Database Zahid 4 Asad 4 Database Asad BCNF Relations in BCNF

  11. 4th Normal Form Multivalued Dependencies Let R be a relation with A, B and C as the attributes. There is a multivalued dependency of attribute B on attribute A if and only if the set of B values associated with a given A value is independent of the set of C values. Easy A type of dependency that exists when there are at least three attributes (Let A, B and C be the attributes) in a relation, and for each value of A there is a well defined set of values for B and a well defined set of values for C, but the set of values of B is independent of set of values of C.

  12. 4th Normal Form Emp_no Salary Child_Name 1 10000 Ali 1 10000 Akram 2 20000 Babar 2 20000 Elahi Example • Here Child_Name is dependent on Emp_no and is independent of Salary. • Emp_no determines salary because there is only one value of salary for each Emp_no. Salary depends on Emp_no. • Emp_no multidetermines Child_Name, because there can be several values of Child_name for each Emp_no. • Emp_no  Child_Name Emp_no  Salary

  13. 4th Normal Form COURSE INSTRUCTOR TEXTBOOK DBMS Ali Akram Babar XYZ ABC C++ Sajid PQR JKL COURSE INSTRUCTOR TEXTBOOK DBMS Ali XYZ DBMS Akram XYZ DBMS Babar XYZ DBMS Ali ABC DBMS Akram ABC DBMS Babar ABC C++ Sajid PQR C++ Sajid JKL Example Table Relation

  14. 4NF • A relation is in 4th Normal Form if • It is in BCNF • and • It contains no multivalued dependencies.

  15. 4NF Process Relation is in BCNF. Divide the relation containing m/v dependencies into new relations. Each of these relations contains attributes that have a multivalued relationship in the original relation.

  16. 4th Normal Form Emp_no Child_Name Salary 1 Ali 10000 1 Akram 10000 2 Babar 20000 2 Elahi 20000 Emp_no Child_Name Emp_no Salary 1 Ali 1 10000 1 Akram 2 20000 2 Babar 2 Elahi Example

  17. 4th Normal Form COURSE INSTRUCTOR TEXTBOOK DBMS Ali XYZ DBMS Akram XYZ DBMS Babar XYZ DBMS Ali ABC DBMS Akram ABC DBMS Babar ABC C++ Majid PQR C++ Majid JKL COURSE INSTRUCTOR COURSE TEXTBOOK DBMS Ali DBMS XYZ DBMS Akram DBMS ABC DBMS Babar C++ PQR C++ Majid C++ JKL Example

More Related