Introduction to database normalization process

Introduction to Database Normalization

1. Database design & development

2. Introduction As a   Database Developer , you work with huge amounts of data stored in different tables that are present inside multiple databases. The data needs to be correctly organized. Using Normalization, you can solve the problem of data redundancy and organize the data using different forms. 

3. Normalization in DBMS Normalization is the process of structuring data in a database. It involves creating tables and defining relationships between them based on rules that safeguard the data and enhance the database's flexibility by reducing redundancy and preventing inconsistent dependencies. Normalization in a DBMS  eliminates data redundancy and enhances data integrity in the table.

4. Normalization in DBMS This multi-step process sets the data into tabular form and removes duplicate data from the relational tables. Normalization organizes the columns and tables of a database to ensure that database integrity constraints properly execute their dependencies. It is a technique of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update, and Deletion anomalies .

5. Anomalies in DBMS The term anomaly is used to describe a inconsistency between two parts of a database. In a retail database, for example, you may have a customer and an invoice table. If you are no longer selling to customers, you may want to purge them from your database periodically. If you delete a customer but keep their invoices for the purchases they have made, you have an anomaly. There is an invoice for a customer that doesn't exist anymore. By deleting all of a customer's invoices when you delete a customer, this anomaly can be avoided.

6. Anomalies in DBMS A database anomaly is an inconsistency in the data resulting from an operation like an update, insertion, or deletion. There can be inconsistencies when a record is held in multiple places and not all of the copies are updated. Anomalies occur when the data present in the database has too much redundancy and if the tables making up the database are poorly constructed .

7. Anomalies in DBMS How do you define " poor construction "? A poorly designed table becomes noticeable if, when a designer is creating the database, he fails to identify the entities which are interdependent, such as rooms of a hostel and the hostel, and then minimizes the chances of one entity being independent of another. A database anomaly is a fault within a database, which can occur because of poor planning or when everything is stored in a flat database. 

8. Type of Anomalies There are different types of anomalies that can occur in a Database. Database anomalies fall into these three major categories. Insert anomaly Update anomaly Delete anomaly

9. Type of Anomalies Insertion Anomalies Cause : Insertion anomalies occur when specific attributes cannot be inserted into the database without the presence of other attributes. Example: Consider a university database where student details are stored along with their enrolled courses. Suppose a new student joins but has yet to enroll in any courses. In that case, you cannot insert their basic information into the database without leaving the course-related fields empty or inserting null values. This creates an insertion anomaly because student information should be able to be entered independently of course enrollment.

10. Type of Anomalies Update Anomalies Cause : Update anomalies arise when updating data inconsistently across a database, leading to discrepancies. Example: In a company's employee database, employee details, including their department, are stored in one table, and project assignments are stored in another. If an employee changes departments, updating their department in one table but not in the other can result in consistency . For instance, if an employee moves from Department A to Department B, but their project assignments still show them as part of Department A, this creates an update anomaly where the employee's current information is not reflected consistently across all relevant tables .

11. Type of Anomalies Deletion Anomalies Cause : Deletion anomalies occur when deleting data inadvertently removes other unintended data. Example: If an employee who is the last member of a department resigns and their record is deleted from the employee table, all information about that department (such as its name, budget, etc.) might be lost if no other employees are currently assigned to that department. This deletion anomaly results in the unintended loss of critical data related to the department, affecting data integrity and completeness .

12. Removal of Data Anomalies Data normalization techniques are employed in database design to remove or minimize data anomalies. Normalization involves organizing data into multiple related tables to reduce redundancy and dependency. Here are some key normalization steps: First Normal Form (1NF): It ensures that each table contains atomic, indivisible values and each record is unique. Second Normal Form (2NF): Builds on 1NF by ensuring that all non-key attributes are fully functional and dependent on the primary key. Third Normal Form (3NF): Further refines 2NF by ensuring that all attributes depend only on the primary key, not other non-key attributes .

13. Functional Dependencies Functional Dependencies We say an attribute, B, has a functional dependency on another attribute, A, if for any records , which have the same value for A, then the values for B in these two records must be the same. We illustrate this as: A  B Example : Suppose we keep track of employee email addresses, and we only track one email address for each employee. Suppose each employee is identified by their unique employee number. We say there is a functional dependency of email address on employee number: employee number  email address

14. Functional Dependencies EmpNum EmpEmail EmpFname EmpLname 123 [email protected] John Doe 456 [email protected] Peter Smith 555 [email protected] Alan Lee 633 [email protected] Peter Doe 787 [email protected] Alan Lee If EmpNum is the PK then the FDs: EmpNum  EmpEmail EmpNum  EmpFname EmpNum  EmpLname must exist.

15. Functional Dependencies EmpNum  EmpEmail EmpNum  EmpFname EmpNum  EmpLname EmpNum EmpEmail EmpFname EmpLname EmpNum EmpEmail EmpFname EmpLname 3 different ways you might see FDs depicted

16. Determinant Functional Dependency EmpNum  EmpEmail Attribute on the LHS is known as the determinant EmpNum is a determinant of EmpEmail

17. Transitive dependency Transitive dependency Consider attributes A, B, and C, and where A  B and B  C. Functional dependencies are transitive, which means that we also have the functional dependency A  C We say that C is transitively dependent on A through B.

18. Transitive dependency EmpNum EmpEmail DeptNum DeptNname EmpNum EmpEmail DeptNum DeptNname DeptName is transitively dependent on EmpNum via DeptNum EmpNum  DeptN ame EmpNum  DeptNum DeptNum  DeptN ame

19. Partial dependency A partial dependency exists when an attribute B is functionally dependent on an attribute A, and A is a component of a multipart candidate key. InvNum LineNum Qty InvDate Candidate keys: {InvNum, LineNum} InvDate is partially dependent on {InvNum, LineNum} as InvNum is a determinant of InvDate and InvNum is part of a candidate key

20. T ypes of Normal Forms  

21. Thanks