Prerequisite: Anomalies in DBMS: Insertion, deletion, updation Anomalies in DBMS Normalization: Normalization is a process of organizing the data in the database to avoid insert anomaly, update anomaly and deletion anomaly. It is mainly used to avoid data redundancy in the database. It mainly divides the larger table into the smaller table and link them using relationship. Theory: 1. database-normalization-introduction 2. database-normalization-normal-forms Video on Normalization: Normalization Types of Normal Forms (NF): 1. 1NF 2. 2NF 3. 3NF 4. BCNF 1. First Normal Form (1NF): According to the 1NF, an attribute of a relation cannot hold multiple values i.e. only atomic values are allowed. Video: First Normal Form 2. Second Normal Form (2NF): A relation is in 2NF if - a. A relation must be in 1NF. b. No partial dependency. Partial dependency: when non-prime attribute is dependent on the proper subset of any candidate key of relation. Prime attributes: those which are part of any candidate key. Non-prime attributes: those which are not part of any candidate key. Problem: one teacher can teach more than one subjects, then relation can have multiple tuples for the same teacher. Normalize relation using 2NF: Second Normal Form Reason for 2NF: Reason for 2NF Decompose a relation into 2NF: Decompose a relation into 2NF 3. Third Normal Form (3NF): A relation is in 3NF if - a. A relation must be in 2NF. b. No transitive dependency. If R is a relation and A -> B, then Relation R is in 3NF if any of the following condition holds- a. A is super key. b. B is a prime attribute. Theory: normalization-normal-forms Video: 3NF Third Normal Form 3NF Decomposition 3NF Decomposition 4. Boyce Codd Normal Form (BCNF): It is also referred as 3.5NF as it is advanced version of 3NF. A relation is in BCNF if - a. Relation is in 3NF b. For every functional dependency A -> B, A should be the super key of the relation. Video: 1. Boyce Codd Normal Form Boyce Codd Normal Form 2. BCNF identification: BCNF identification 4th and 5th Normal Form: introduction-of-4th-and-5th-normal-form How to find normal form? 1. How to find normal form1 2. How to find normal form2 3. How to find normal form3 Lossless join Decomposition: It is central in removing redundancy safely from the databases while preserving the original data. It can also be non-additive join decomposition. A decomposition (R1, R2, ..., Rn) of a relation R is called a lossless decomposition for relation R if the natural join of R1, R2, ..., Rn produces exactly the relation R. If R is a relation that split into A and B then, Decomposition is lossless if: Theory: 1. lossless-decomposition 2. lossless-join-and-dependency-preserving-decomposition Video: lossless-join-decomposition Example: practice-problem-on-lossless-join-decomposition Dependency preserving decomposition: If relation R is decomposed into relation A and B, then all dependencies (productions) of R either must be a part of A or B must be derivable from combination of FDs of A and B. Functional dependency set = F Theory: dependency-preserving-decomposition Video: dependency-preserving-decomposition How to Normalize a relation table: How to Normalize a relation table