Oracle

Normalization is a process in database design used to reduce redundancy and improve data integrity. By organizing data into separate tables and defining relationships between them, normalization ensures that the database is efficient and that data is stored logically. It involves dividing a database into two or more tables and defining relationships between the tables to eliminate redundancy and improve data integrity.

Here’s an overview of the normalization process:

1. First Normal Form (1NF)

   • Ensures that each column contains atomic, indivisible values.
   • Eliminates duplicate columns from the same table.
   • Example: A Books table where each book's details are stored in separate columns (e.g., Book_ID, Title, Author_ID).

2. Second Normal Form (2NF)

   • Builds on 1NF by ensuring that all non-key attributes are fully functionally dependent on the primary key.
   • Addresses partial dependencies where non-key attributes depend on only part of a composite primary key.
   • Example: A Book_Author table where each record links a Book_ID to an Author_ID, ensuring that each book and author relationship is recorded.

3. Third Normal Form (3NF)

   • Ensures that all the columns in a table are not only functionally dependent on the primary key but also free from transitive dependencies (i.e., non-key attributes depend only on the primary key).
   • Example: In a Library table, having a separate Authors table rather than storing author details in the Library table to avoid redundancy.

4. Boyce-Codd Normal Form (BCNF)

   • A stricter version of 3NF where every determinant is a candidate key.
   • Addresses certain types of anomalies not handled by 3NF.
   • Example: A Membership table where membership types and fees are related, and any dependencies on non-candidate keys are addressed.

5. Fourth Normal Form (4NF)

   • Ensures that multi-valued dependencies are eliminated.
   • Aims to separate independent multi-valued facts.
   • Example: A Rentals table where rental transactions are recorded separately from rental terms to avoid multi-valued dependencies.

6. Fifth Normal Form (5NF)

   • Ensures that every join dependency is a consequence of the candidate keys.
   • Aims to break down tables into smaller tables to ensure that every fact is recorded only once.
   • Example: Splitting a Book_Rentals table into separate tables for books, rental transactions, and rental dates.

Example Code:

-- Creating a normalized Books table
CREATE TABLE Books (
    Book_ID INT PRIMARY KEY,
    Title VARCHAR(100),
    Author_ID INT
);

-- Creating a normalized Authors table
CREATE TABLE Authors (
    Author_ID INT PRIMARY KEY,
    Name VARCHAR(100)
);

-- Creating a normalized Book_Author table to link books and authors
CREATE TABLE Book_Author (
    Book_ID INT,
    Author_ID INT,
    PRIMARY KEY (Book_ID, Author_ID),
    FOREIGN KEY (Book_ID) REFERENCES Books(Book_ID),
    FOREIGN KEY (Author_ID) REFERENCES Authors(Author_ID)
);

-- Creating a normalized Library table
CREATE TABLE Library (
    Library_ID INT PRIMARY KEY,
    Library_Name VARCHAR(100)
);

This structured approach helps in maintaining data integrity and minimizing redundancy by creating relationships between tables.

FIRST NORMAL FORM (1NF)

The First Normal Form (1NF) is a property of a relation (table) in a relational database. A relation is said to be in First Normal Form if it satisfies the following rules:

1. Unique Identifier: A unique identifier, often referred to as a primary key, is a column or set of columns whose values uniquely identify each row in a table. In 1NF, it's essential to have a unique identifier for each row.
2. No Duplicates: There should be no duplicate rows in a table. This means that each row in the table should be unique.
3. Atomicity: Each cell in the table must contain only atomic (indivisible) values. In other words, the value in each column must be indivisible as far as the relational model is concerned. Complex data types such as arrays, lists, or composite objects are not allowed.
4. No Repeating Groups: A table should not contain repeating groups of columns. If a table contains columns that repeat the same kind of information (e.g., phone1, phone2, phone3 for multiple phone numbers), it violates 1NF. Instead, such data should be stored in a separate table with a relationship between the two tables.
5. Consistent Schema: The schema or structure of the table (i.e., the columns and data types) must be consistent in all rows. Each column must have a unique name, and the data type of values within each column must be the same.

EXAMPLE 1 - Unique identifier

EXAMPLE 2 - DUPLICATES

EXAMPLE 3 - ATOMICITY & REPEATING GROUPS

EXAMPLE 4

EXAMPLE 5 - REPEATING COLUMNS