Microsoft SQL Server

Chapter 8 - RDBMS Concepts

Normalization

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The first step in this process is known as First Normal Form (1NF). In 1NF, a table is structured so that each column contains atomic (indivisible) values, and each entry in a column must contain a single value. This ensures that the data is stored in a simple, organized manner without repeating groups or arrays of values.

Key Concepts of First Normal Form (1NF):

Atomic Values
- Each column must contain atomic, indivisible values. This means there should be no sets, lists, or arrays of values in any column.
- For example, if a customer buys multiple products, instead of storing them in one column like Product1, Product2, you should have a separate row for each product.
Example SQL (violating 1NF):
```
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    Products VARCHAR(255) -- 'Product1, Product2'
);
```
Single Value per Column
- Each cell in a table should contain a single value, not multiple values. This is achieved by splitting repeating information into multiple rows.
Example SQL (correcting the violation):
```
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    ProductName VARCHAR(100)
);
```
Unique Records
- There should be no duplicate rows in the table. Each record (row) must be unique, which is usually enforced by a primary key.
Example SQL (ensuring uniqueness with a primary key):
```
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    ContactNumber VARCHAR(15)
);
```

Eliminate Repeating Groups

In 1NF, there should be no repeating groups of related data within the same row. For instance, instead of having multiple columns for different products in the same order (e.g., Product1, Product2), each product should be on its own row.

Example SQL (repeating group violation):

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    Product1 VARCHAR(100),
    Product2 VARCHAR(100)
);

Example SQL (corrected to 1NF):

CREATE TABLE Orders (
    OrderID INT,
    CustomerName VARCHAR(100),
    ProductName VARCHAR(100),
    PRIMARY KEY (OrderID, ProductName)
);

Separation of Concerns

Each table should represent only one entity or subject. For instance, if you’re storing customer information, it should be in one table, while product information should be in another. This helps ensure data is organized and reduces redundancy.

Example SQL:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Achieving First Normal Form (1NF) is the foundation for building a well-structured relational database, where data is efficiently organized, eliminating redundancy and improving data integrity.

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:

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.
No Duplicates: There should be no duplicate rows in a table. This means that each row in the table should be unique.
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.
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.
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.