The Process of Eliminating the data redundancy and Enhance the data integrity is known as Normalisation.

It means That there should  be less duplicacy in the data.

Edgar F. Codd defined the First Normal Form in 1970.

### Types of Normal Forms

There are Following types of Normal Forms.

### 1NF :

The table should not contain any multivalued Attribute.

It means the table should be atomic.

Student CodeStudent NameStudent Phone Number
1Dev89766672
2Shyam98765667
3Ram78567897

### Functional Dependancy:

It is the relation Between Two attribute in a table.

Generally Primary key defines the Non key attributes.

X —> Y

Here X is known as determinant and Y is known as dependent.

Student CodeStudent NameStudent Phone Number
1Dev89766672
2Shyam98765667
3Ram78567897

Here in this table student code determines the student name as well as student phone number.

Student Code —> Student name

### Transitive Dependency

If    A—> B and B—>C so A—>C

if student code determines student name and student name determines the student phone number so clearly student code determines the student phone number also and this is known as Transitive Dependency.

### 2NF:

1. The table must already be in 1NF

2. Single Column Primary Key

3. Eliminate Partial Dependencies

What is Partial Dependencies?

All non-key attributes (attributes that are not part of the primary key) must be fully functionally dependent on the entire primary key.

if any non-key attribute depends on only part of the primary key, it indicates a partial dependency, which needs to be resolved.

To illustrate the concept of Second Normal Form (2NF), let's consider a simple database of library books, authors, and the book copies. We'll start with an unnormalized table and then normalize it to 2NF.

Unnormalized Table: LibraryBooks

BookID Title Author Author's Birthdate CopyID Copy Location
101 "To Kill a Mockingbird" Harper Lee April 28, 1926 001 Shelf A
102 "1984" George Orwell June 25, 1903 002 Shelf B
101 "To Kill a Mockingbird" Harper Lee April 28, 1926 003 Shelf C
103 "Pride and Prejudice" Jane Austen December 16, 1775 004 Shelf D
102 "1984" George Orwell June 25, 1903 005 Shelf E

In this unnormalized table, we have repeated information about books and authors. The BookID is not unique in the table, and information about authors is repeated for books with multiple copies. We need to normalize this table to 2NF.

Step 1: 1NF (First Normal Form)

Ensure that each column contains atomic (indivisible) values, and each row is unique. Our initial table is not in 1NF because it contains repeating groups of data.

Step 2: 2NF (Second Normal Form)

In 2NF, we need to eliminate partial dependencies. To do this, we identify the primary key. In this case, the primary key could be a composite key composed of BookID and CopyID.

The partial dependencies in the initial table are on the Author and Author's Birthdate columns. They depend on BookID, but BookID is only part of the primary key. Therefore, there are partial dependencies, which violate 2NF.

To resolve this, we split the table into two:

Table: Books

BookID Title Author Author's Birthdate
101 "To Kill a Mockingbird" Harper Lee April 28, 1926
102 "1984" George Orwell June 25, 1903
103 "Pride and Prejudice" Jane Austen December 16, 1775

Table: BookCopies

BookID CopyID Copy Location
101 001 Shelf A
102 002 Shelf B
101 003 Shelf C
103 004 Shelf D
102 005 Shelf E

Now, each table has its own primary key, and all non-key attributes are fully functionally dependent on their respective primary keys. This is a 2NF-compliant representation of the data.

### 3NF :

Rules :

1. The table must already be in 2NF
2. All non-key attributes should be functionally dependent on the primary key
3. Eliminate Transitive Dependencies

Initial Unnormalized Table

Suppose we start with an unnormalized table that combines information about customers, their orders, and the products they purchased. The table might look like this:

Table: SalesTransactions

TransactionID CustomerName ProductName ProductCategory Price
1 Alice Laptop Electronics 1000
2 Bob Smartphone Electronics 500
3 Alice Tablet Electronics 300
4 Carol Camera Electronics 600

Step 1: 1NF (First Normal Form)

To achieve 1NF, we need to ensure that each column contains atomic (indivisible) values and that each row is unique. Our initial table is already in 1NF, so we move on to 2NF.

Step 2: 2NF (Second Normal Form)

In 2NF, we ensure that all non-key attributes are fully functionally dependent on the primary key. The primary key in this case could be a combination of columns like TransactionID and ProductName.

TransactionID is unique, and ProductName is not guaranteed to be unique (e.g., multiple transactions may involve the same product).

Now, let's identify the partial dependencies:

• TransactionID → CustomerName (Partial Dependency)
• ProductName → ProductCategory, Price (Full Dependency)

To resolve the partial dependency, we split the table into two:

Table: Customers

TransactionID CustomerName
1 Alice
2 Bob
3 Alice
4 Carol
5 Bob

Table: Products

ProductName ProductCategory Price
Laptop Electronics 1000
Smartphone Electronics 500
Tablet Electronics 300
Camera Electronics 600

Now, each table has a primary key (Customers: TransactionID, Products: ProductName), and all non-key attributes are fully functionally dependent on their respective primary keys.

Step 3: 3NF (Third Normal Form)

In 3NF, we ensure that there are no transitive dependencies within each table. In this case, there are no transitive dependencies remaining, as each non-key attribute depends directly on the primary key.

Our normalized tables are in 3NF, and we've eliminated partial and transitive dependencies, resulting in a more organized and structured database.

### BCNF:

Rules:

1. The table must already be in 3NF
2. Every non-prime attribute should be functionally dependent on the super key.

Unnormalized Table: Courses

CourseID CourseName Instructor Instructor Office StudentID Student Name
101 Introduction to Programming Dr. Smith Office A 001 Alice Johnson
101 Introduction to Programming Dr. Smith Office A 002 Bob Anderson
102 Database Management Prof. Lee Office B 001 Alice Johnson
103 Calculus I Prof. Brown Office C 003 Carol Williams
102 Database Management Prof. Lee Office B 004 David Clark

Step 1: 1NF (First Normal Form)

To achieve 1NF, we need to ensure that each column contains atomic (indivisible) values and that each row is unique. Our initial table is not in 1NF because it contains repeating groups of data.

Step 2: 2NF (Second Normal Form)

In 2NF, we need to eliminate partial dependencies. To do this, we identify the primary key, which might be a combination of columns such as CourseID and StudentID. In this case, CourseID is part of the primary key, and StudentID is another part of the primary key.

The partial dependencies in the initial table are on the Instructor and Instructor Office columns. They depend on CourseID, but CourseID is only part of the primary key. Therefore, there are partial dependencies, which violate 2NF.

To resolve this, we split the table into two:

Table: Courses

CourseID CourseName Instructor Instructor Office
101 Introduction to Programming Dr. Smith Office A
102 Database Management Prof. Lee Office B
103 Calculus I Prof. Brown Office C

Table: Enrollments

CourseID StudentID Student Name
101 001 Alice Johnson
101 002 Bob Anderson
102 001 Alice Johnson
103 003 Carol Williams
102 004 David Clark

Now, both tables are in 2NF.

Step 3: 3NF (Third Normal Form)

In 3NF, we need to eliminate transitive dependencies. In our tables, there are no transitive dependencies left, so they are already in 3NF.

Step 4: BCNF (Boyce-Codd Normal Form)

To ensure BCNF, we need to make sure that every non-prime attribute (non-key attribute) is functionally dependent on the super key. In our tables, the super key is the combination of CourseID and StudentID, and all non-prime attributes depend on the super key. Therefore, our tables are in BCNF.

The process has resulted in well-structured tables that are free from anomalies and conform to BCNF.

The final output, after normalizing the given tables into Boyce-Codd Normal Form (BCNF), will be two separate tables, each in BCNF. Let's recap the normalized tables:

Table: Courses

CourseID CourseName Instructor Instructor Office
101 Introduction to Programming Dr. Smith Office A
102 Database Management Prof. Lee Office B
103 Calculus I Prof. Brown Office C

This table contains information about courses, with CourseID as the primary key. The Instructor and Instructor Office attributes are fully functionally dependent on the primary key, adhering to BCNF.

Table: Enrollments

CourseID StudentID Student Name
101 001 Alice Johnson
101 002 Bob Anderson
102 001 Alice Johnson
103 003 Carol Williams
102 004 David Clark

This table contains information about course enrollments, with a composite primary key of CourseID and StudentID. The Student Name attribute is fully functionally dependent on the primary key, adhering to BCNF.

These two tables, Courses and Enrollments, are now in Boyce-Codd Normal Form. They are well-structured and free from data anomalies, making them suitable for efficient data storage and retrieval in a relational database.

### What is Transaction in DBMS??

Ans :  a transaction is a sequence of one or more database operations that are treated as a single, indivisible unit of work.

### ACID Properties:

A ---> Atomicity

C ---> Consistency

I —> Isolation

D —> Durability

Atomicity :

• Atomicity ensures that a transaction is treated as a single, indivisible unit of work. It means that either all the operations within a transaction are successfully completed, or none of them are. There is no "in-between" state.
• If any part of the transaction fails (e.g., due to an error, system crash, or power outage), the entire transaction is rolled back to its initial state, ensuring data consistency.

Consistency:

• The consistency property ensures that a transaction brings the database from one consistent state to another. In other words, a transaction should follow certain integrity constraints and business rules.
• Before and after a transaction, the database must satisfy specific conditions, and the integrity constraints should not be violated. If a transaction violates these constraints, it is rolled back.

Isolation:

• Isolation ensures that concurrent transactions do not interfere with each other. Transactions run as if they are the only ones being executed, even in a multi-user environment.
• Isolation levels, such as Read Uncommitted, Read Committed, Repeatable Read, and Serializable, define the degree to which transactions are isolated from each other. Higher isolation levels provide stronger isolation guarantees but may lead to more locking and reduced concurrency.

Durability:

• Durability guarantees that once a transaction is committed (i.e., it has been successfully completed and acknowledged by the DBMS), its effects are permanent and will survive system failures, including crashes or restarts.
• This property ensures that data changes are safely stored in non-volatile storage (e.g., disk) and are recoverable even in the event of a system failure.