Normalization

Normalisation in Database Systems

Introduction and Motivation

  • Normalisation process aims to design a database for easy user access and minimal storage space.

    • Updates should be achieved with minimal operations.

    • Reducing redundancy is crucial.

  • Normalisation involves producing a suitable set of relations meeting these objectives.

Design Process and Approaches

  • Use top-down approach like ER modeling to map to relations based on user requirements.

  • Utilize normalization as a validation technique for well-designed forms and reports.

  • Employ normalization as a bottom-up technique to create relations.

Data Redundancy and Anomalies

Insertion Anomalies

  • Correct customer names must be inserted for each stay to avoid anomalies.

  • Challenges in inserting information about new rooms or customers not yet stayed.

Deletion Anomalies

  • Deleting a row may lead to loss of related information, like room type in a stay.

Modification Anomalies

  • Changes in customer details require updates in all related rows for stays.

Causes of Problems

  • Combining data from different entities like hotel stays, customers, rooms, and room types into one relation leads to issues.

Page 13: The Cause of the Problems

  • Data includes information on customer stays

    • Room number, date, customer details, room type, and price

  • Different room types have different prices

    • Single room: 895, Double room: 1295, VIP room: 2395

Page 14: Dealing with the Problem

  • Data normalization process involves:

    • Removing repeating groups

    • Eliminating partial and transitive dependencies

  • Forms and reports are generated based on user requirements and data sources

Page 15: Normalization Overview

  • Introduction to normalization process

  • Covers First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF)

Page 16: Primary Key and Stay Relation

  • Primary key: (id, date)

  • Stay relation includes customer details, room number, date, room type, and price

Page 17: Key Attributes

  • Prime key attributes are part of the primary key

  • Non-prime key attributes are other attributes in the relation

Page 18: Functional Dependencies

  • Functional dependency (A→B) in a relation

  • Full functional dependency when B depends on A without any subset of A

Page 19: Functional Dependencies in Stay Relation

  • Examples of functional dependencies in the stay relation

  • Dependencies include id, date, room number, room type, and room price

Page 20: Transitive Functional Dependencies

  • Transitive dependencies occur when A→B and B→C

  • Example relation: Stay with various functional dependencies

Page 21: Stay Relation Details

  • Repetition of customer stay information with room details and prices

Page 22: Normalization Overview

  • Introduction to normalization and its stages

  • Includes 1NF, 2NF, 3NF, and BCNF

Page 23: Lossless-Join Decomposition

  • Lossless-join decomposition is reversible through inner joins

  • Data on customer stays and room details

Page 24: Unnormalised Form (UNF)

  • Unnormalised Form contains repeating groups

  • Example: Customer data with multiple car registrations

Note

Page 25

  • First Normal Form (1NF)

    • Relation with no repeating groups in cells

    • Process for UNF → 1NF conversion

      • Move repeating groups to a separate relation

      • Include key values for lossless joins

Page 26

  • Dealing with the Problem

    • Data sources, users, requirements

    • Forms/reports used or generated by the enterprise

  • Normalization Process

    • Transfer attributes into table format

    • UNF, 1NF, 2NF, 3NF normalization steps

Page 27

  • Second Normal Form (2NF) - 1

    • Definition and criteria for 2NF

    • Example of a relation not in 2NF

Page 28

  • Second Normal Form (2NF) - 2

    • Process for 1NF → 2NF conversion

    • Moving partially dependent attributes to a new relation

    • Ensuring the result is in 2NF

Page 29

  • Reiteration of normalization steps

  • Transfer of attributes into table format

  • UNF, 1NF, 2NF, 3NF normalization process

Page 30

  • Overview of normalization levels

  • Introduction to Boyce-Codd Normal Form (BCNF)

Page 31

  • Third Normal Form (3NF) - 1

    • Definition and criteria for 3NF

    • Example of a relation not in 3NF

Page 32

  • Example of the "Stay" relation

    • Data showing id, names, room details, and prices

Page 33

  • Third Normal Form (3NF) - 2

    • Process for 2NF → 3NF conversion

    • Moving transitively dependent attributes to a new relation

Page 34

  • Reiteration of normalization steps

  • Transfer of attributes into table format

  • UNF, 1NF, 2NF, 3NF normalization process

Page 35

  • Example data in the 3NF schema

    • Data for "Stay," "Customer," "Room," and "Room_type" entities

Page 36

  • Overview of normalization levels

  • Introduction to Boyce-Codd Normal Form (BCNF)

Page 37

  • Boyce-Codd Normal Form (BCNF)

    • Definition and criteria for BCNF

    • Comparison with 3NF

Page 38

  • BCNF Sample Check (1)

    • Evaluation of sample schema in 1NF for BCNF criteria

Page 39

  • BCNF Sample Check (2)

    • Evaluation of sample schema in