What are the different normal forms in DBMS? Explain with example.
Database Management System
Computer Science Engineering
568
Dashboard
In database management systems (DBMS), the concept of normal forms is used to ensure data integrity and minimize data redundancy in a relational database. There are several normal forms, each representing a specific level of normalization.
The commonly known normal forms are:
First Normal Form (1NF):
Example: Consider a table called "Students" with the following columns: StudentID, Name, Subjects.
The table violates 1NF because the "Subjects" column contains multiple values for each student. To normalize it, you would split it into two tables: "Students" (with columns: StudentID, Name) and "Subjects" (with columns: StudentID, Subject).
Second Normal Form (2NF):
Example: Suppose we have a table called "Orders" with columns: OrderID, ProductID, ProductName, Quantity, and Price.
The table violates 2NF if ProductName and Price depend on ProductID but not on OrderID. To normalize it, you would create two tables: "Orders" (with columns: OrderID, ProductID, Quantity) and "Products" (with columns: ProductID, ProductName, Price).
Third Normal Form (3NF):
Example: Consider a table called "Employees" with columns: EmployeeID, DepartmentID, DepartmentName, Manager.
The table violates 3NF if DepartmentName depends on DepartmentID and Manager depends on DepartmentName. To normalize it, you would create three tables: "Employees" (with columns: EmployeeID, DepartmentID), "Departments" (with columns: DepartmentID, DepartmentName), and "Managers" (with columns: DepartmentName, Manager).
These are the first three normal forms, but there are higher normal forms as well, such as Boyce-Codd Normal Form (BCNF), Fourth Normal Form (4NF), and Fifth Normal Form (5NF). Each subsequent normal form addresses more complex normalization issues, but the first three forms are the most commonly used and understood.
It's important to note that normalization should be applied based on the specific requirements and characteristics of the data model, and sometimes denormalization is intentionally done for performance optimization.
In database management systems (DBMS), the concept of normal forms is used to define the level of organization and optimization of a relational database schema. There are several normal forms, each building upon the previous one, with the ultimate goal of minimizing data redundancy and improving data integrity. Let's discuss the most commonly known normal forms:
First Normal Form (1NF):
Example: Consider a table called "Employees" with the following attributes: Employee_ID, Name, and Skills. To satisfy 1NF, we need to ensure that each attribute contains atomic values. If the Skills attribute is a multivalued attribute, we can separate it into a separate table "Employee_Skills" with columns Employee_ID and Skill.
Second Normal Form (2NF):
Example: Let's say we have a table called "Orders" with attributes: Order_ID, Product_ID, Product_Name, and Product_Price. To satisfy 2NF, we can break this table into two separate tables: "Orders" (Order_ID and Product_ID) and "Products" (Product_ID, Product_Name, and Product_Price). The Product_Name and Product_Price are fully functionally dependent on the Product_ID, which is the primary key of the "Products" table.
Third Normal Form (3NF):
Example: Suppose we have a table called "Students" with attributes: Student_ID, Course_ID, Course_Name, and Instructor. Here, Course_Name and Instructor are functionally dependent on the Course_ID, not directly on the Student_ID. To satisfy 3NF, we can split this table into three separate tables: "Students" (Student_ID), "Courses" (Course_ID and Course_Name), and "Instructors" (Course_ID and Instructor). This eliminates the transitive dependency between the Student_ID and the attributes related to the Course.
These are the first three normal forms (1NF, 2NF, and 3NF) commonly used in database normalization.
There are higher normal forms like Boyce-Codd Normal Form (BCNF) and Fourth Normal Form (4NF), which deal with more complex situations and dependencies. The goal of normalization is to minimize redundancy, ensure data consistency, and improve the overall efficiency of a database schema.