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.
1 NF (First Normal Form)
2 NF (Second Normal Form)
3 NF (Third Normal Form)
BCNF (Boyce Codd Normal Form)
5NF (Fifth NF)
1NF :
The table should not contain any multivalued Attribute.
It means the table should be atomic.
| Student Code | Student Name | Student Phone Number |
|---|---|---|
| 1 | Dev | 89766672 |
| 2 | Shyam | 98765667 |
| 3 | Ram | 78567897 |
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 Code | Student Name | Student Phone Number |
|---|---|---|
| 1 | Dev | 89766672 |
| 2 | Shyam | 98765667 |
| 3 | Ram | 78567897 |
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:
- 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 :
- The table must already be in 2NF
- All non-key attributes should be functionally dependent on the primary key
- 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 |
| 5 | Bob | Headphones | Electronics | 100 |
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 |
| Headphones | Electronics | 100 |
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:
- The table must already be in 3NF
- 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.