Normalization In SQL | SQL Normalization Tutorial | Database Normalization Forms

2. What’s in it for You? • What is Normalization? • What is a Key? • Types of Keys • Types of Normal Forms • 1NF • 2NF • 3NF • BCNF

3. Normalization in DBMS Normalization aims to eliminate redundant (duplicate) data and ensure data is stored logically It eliminates unwanted characteristics like Insertion, Update and Deletion Anomalies

4. Click here to watch the video

5. What is a Key? A KEY is an attribute used to uniquely distinguish a record in a table. A KEY could be an individual column or a combination of multiple columns Primary Key Foreign Key Student Table

6. Types of Keys Primary Key Super Key Foreign Key Candidate Key

7. Primary Key • Primary key is a set of one or more fields/columns of a table that uniquely identify a record in a database table • It can not accept null or duplicate values Primary Key Primary Key Department Table Student Table

8. Foreign Key Foreign Key is a field in a database table that is Primary key in another table Foreign Key Primary Key Department Table Student Table

9. Super Key • Super key is a set of one or more than one key that can be used to identify a record uniquely in a table • Super key can have extra attributes that are redundant for distinct identification {id, stu_name} {roll_no, stu_name} {roll_no, dept_id} (id, stu_name, city} {roll_no, city, dept_id} Student Table

10. Candidate Key • A Candidate Key is the minimal super key that can identify a record uniquely in a table. • Each Candidate Key can work as a Primary Key {id, roll_no} {id, stu_name} {roll_no, stu_name, city} (id, stu_name, dept_id} Student Table

11. Types of Normal Forms

12. Types of Normal Forms 3NF 1 NF 2 NF BCNF Boyce-Codd Normal Form Second Normal Form First Normal Form Third Normal Form

13. First Normal Form 1 NF Each table cell should contain a single value Each record needs to be unique

14. First Normal Form 1 NF Not in 1 NF Follows 1 NF

15. Second Normal Form 2 NF Table should be in First Normal Form It should not have Partial Dependency

16. Partial Dependency If the proper subset of any candidate key determines the non-prime attributes, it is called partial dependency Candidate key {Prof_ID and Course_Name } Course_Fee Non prime attribute Course Table

17. 2 NF Table Course Table Prof_Course Course Fee

18. Third Normal Form 3 NF Table should be in Second Normal Form It should not have Transitive Functional Dependency

19. Transitive Functional Dependency  If column entry is dependent on any other entry (value) other than the key of the table, then Transitive Functional Dependency exists Emp_ID = primary key But Emp_City_Zip depends on Emp_City which leads to transitive functional dependency

20. 3 NF Table Emp Table Emp Details City Details

21. Boyce-Codd Normal Form BCNF Table should be in Third Normal Form A relation is in BCNF if for every functional dependency X –> Y, X is a super key

22. Boyce-Codd Normal Form Emp Table • Functional Dependencies • Emp_ID -> Country • Department -> Dept_Type • Department -> Dept_No • Emp_ID -> Department {Emp_ID, Department} is the candidate key

23. Boyce-Codd Normal Form Emp Table T2 T3 T1 LHS of each Functional dependency should be a candidate or super key, for the table to be in BCNF

24. Join us to learn more! UNITED STATES Simplilearn Solutions Pvt. Limited 201 Spear Street, Suite 1100 San Francisco, CA 94105 Phone: (415) 741-3319 INDIA Simplilearn Solutions Pvt. Limited #53/1C, 24th Main, 2nd Sector HSR Layout, Bangalore 560102 Phone: +91 8069999471 UNITED STATES Simplilearn Solutions Pvt. Limited 801 Corporate Center Drive, Suite 138 Raleigh, NC 27607 Phone: (919) 205-5565