1.
First Normal Form (1NF)
Definition:
A relation is in First Normal Form if it contains only atomic (indivisible) values and each column contains
values of a single type.
Rules:
There are no repeating groups or arrays.
All entries in a column are of the same data type.
Each record is unique and identifiable by a primary key.
Example (Before 1NF):
Student Subjects
Raj Math, Science
After 1NF:
Student Subject
Raj Math
Raj Science
Importance:
It ensures that data is stored in a tabular format with no multivalued attributes
2. Second Normal Form (2NF)
Definition:
A relation is in Second Normal Form if it is in 1NF and all non-prime attributes are fully functionally
dependent on the entire primary key.
Rules:
Must be in 1NF.
No partial dependency (where a non-key attribute depends only on part of a composite key).
Example (Before 2NF):
StudentID CourseID StudentName
StudentName depends only on StudentID.
After 2NF:
Split into two tables:
1. Student Table: (StudentID, StudentName)
2. Enrollment Table: (StudentID, CourseID)
Importance:
Removes partial dependencies and improves data integrity.
3. Third Normal Form (3NF)
Definition:
A relation is in Third Normal Form if it is in 2NF and all attributes are only dependent on the primary key,
not on other non-prime attributes (i.e., no transitive dependency).
Rules:
Must be in 2NF.
No transitive dependencies.
Example (Before 3NF):
StudentID DeptID DeptName
Here, DeptName depends on DeptID, which is not a primary key.
After 3NF:
1. Student Table: (StudentID, DeptID)
2. Department Table: (DeptID, DeptName)
Importance:
Improves clarity and reduces data anomalies.
4. Boyce-Codd Normal Form (BCNF)
Definition:
A relation is in BCNF if it is in 3NF and every determinant is a candidate key.
Rules:
Must be in 3NF.
For every functional dependency X → Y, X must be a super key.
Example (Before BCNF):
TeacherID Subject Department
Assume: Subject → TeacherID and TeacherID → Department
Subject is not a candidate key, violating BCNF.
After BCNF:
Split into:
1. Subject-Teacher Table: (Subject, TeacherID)
2. Teacher-Department Table: (TeacherID, Department)
Importance:
BCNF resolves anomalies not handled by 3NF by eliminating all redundancy due to functional dependency.
5. Fourth Normal Form (4NF)
Definition:
A relation is in Fourth Normal Form if it is in BCNF and has no multi-valued dependencies.
Rules:
Must be in BCNF.
A multi-valued dependency occurs when one attribute in a table uniquely determines another
attribute, independently of all other attributes.
Example (Before 4NF):
Student Language Hobby
Raj Hindi Painting
Raj Hindi Dancing
Raj English Painting
Raj English Dancing
Student →→ Language
Student →→ Hobby
After 4NF:
1. Student-Language Table: (Student, Language)
2. Student-Hobby Table: (Student, Hobby)
Importance:
Eliminates redundancy due to multi-valued facts and ensures data independence.
6. Fifth Normal Form (5NF) / Project-Join Normal Form (PJNF)
Definition:
A relation is in Fifth Normal Form if it is in 4NF and cannot be decomposed further into smaller tables
without loss of data or meaning (i.e., lossless join).
Rules:
Must be in 4NF.
Handles join dependencies and ensures all possible join paths preserve the original data.
Example:
Consider a table with attributes: Supplier, Part, Project
A supplier can supply parts to projects, and the data may need to be decomposed into three separate
relations:
1. Supplier-Part
2. Part-Project
3. Supplier-Project
If these can be joined back without loss, the relation is in 5NF.
Importance:
Used in complex databases to avoid anomalies during data reconstruction using joins.
Key Constraints in DBMS
Definition:
Key constraints are rules applied to attributes (columns) in a database table to uniquely identify tuples
(rows). They ensure that data can be uniquely accessed and maintain integrity by avoiding duplication and
inconsistencies.
Types of Key Constraints
1. Primary Key
o A primary key is a column or combination of columns that uniquely identifies each row in a
table.
o It cannot have NULL values and must be unique.
o There can only be one primary key per table.
o Example:
sql
CopyEdit
CREATE TABLE Students (
RollNo INT PRIMARY KEY,
Name VARCHAR(50)
);
2. Candidate Key
o A candidate key is any column or set of columns that can qualify as a unique key in the
database.
o There can be multiple candidate keys, but only one becomes the primary key.
o Example: If both RollNo and Email are unique, both are candidate keys, but only one is
selected as the primary key.
3. Alternate Key
o A candidate key that is not chosen as the primary key is called an alternate key.
o It still maintains uniqueness and can be used for querying.
o Example: If Email is unique but not chosen as the primary key, it becomes an alternate key.
4. Foreign Key
o A foreign key is an attribute that creates a relationship between two tables.
o It is a field in one table that refers to the primary key in another table.
o It allows referential integrity between records.
o Example:
sql
CopyEdit
CREATE TABLE Enrollments (
StudentID INT,
FOREIGN KEY (StudentID) REFERENCES Students(RollNo)
);
5. Super Key
o A super key is a set of attributes that can uniquely identify a record.
o Every primary key is a super key, but not all super keys are minimal.
o It can contain extra attributes not necessary for uniqueness.
o Example: (RollNo, Name) is a super key if RollNo is unique.
6. Composite Key
o A composite key is a primary key made of two or more attributes that together uniquely
identify a record.
o It is used when no single column can uniquely identify a row.
o Example:
sql
CopyEdit
CREATE TABLE Results (
StudentID INT,
SubjectID INT,
PRIMARY KEY (StudentID, SubjectID)
);
Importance of Key Constraints
They ensure uniqueness of data in a table.
They help in defining relationships between tables.
They enforce data integrity and prevent duplication.
They are crucial in designing normalized and efficient databases.
Conclusion
Key constraints form the foundation of relational database design. Understanding and properly applying
them ensures the accuracy, consistency, and reliability of stored data. Proper use of keys also supports
indexing, query optimization, and ensures robust data relationships across tables.
Domain Constraints in DBMS
Definition:
Domain constraints are a type of integrity constraint that define the permissible values an attribute (column)
can hold in a database table. These constraints ensure that the data entered into a field falls within a
specified domain (data type, range, or format).
Explanation:
Every attribute in a relational table is associated with a domain, which is a set of valid values. A domain
constraint restricts the values of an attribute to a specific data type and optionally to a specific set or range.
Key Characteristics of Domain Constraints:
1. Data Type Constraint:
o Ensures the value matches the declared data type (e.g., INT, VARCHAR, DATE).
o Example:
sql
CopyEdit
Age INT;
2. Value Range Constraint:
o Restricts values to fall within a specific range.
o Example:
sql
CopyEdit
CHECK (Age BETWEEN 18 AND 60);
3. Format Constraint:
o Enforces a specific format (especially useful for strings or dates).
o Example:
Phone numbers or email formats can be checked via patterns or triggers.
4. NULL Constraint:
o Ensures that a column must or must not have NULL values.
o Example:
sql
CopyEdit
Email VARCHAR(50) NOT NULL;
5. Enumerated Values (ENUM or IN):
o Restricts a column to a limited set of values.
o Example:
sql
CopyEdit
Gender ENUM('Male', 'Female', 'Other');
Example in SQL:
sql
CopyEdit
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK (Age >= 18 AND Age <= 60),
Email VARCHAR(100) UNIQUE,
Gender VARCHAR(10) CHECK (Gender IN ('Male', 'Female', 'Other'))
);
In this example:
Age is constrained to 18–60.
Email must be unique and not null.
Gender is limited to a predefined list.
Importance of Domain Constraints:
Data Accuracy: Prevents invalid entries (e.g., a string in a numeric field).
Consistency: Enforces standard formats and value types.
Security: Helps avoid malicious or erroneous data inputs.
Validation: Acts as the first level of validation before application logic.
Conclusion:
Domain constraints are essential for maintaining data integrity at the database level. By ensuring that every
field in a table contains only valid, expected values, domain constraints contribute to a reliable and clean
database design, reducing the risk of data anomalies and inconsistencies.
Data Definition Language (DDL) in DBMS
Introduction
Data Definition Language (DDL) is a subset of SQL used to define and manage the structure of database objects. It
focuses on the schema and structure of the database rather than the data itself.
Key DDL Commands
1. CREATE: Used to create new database objects such as tables, indexes, or views. For example:
sql
CopyEdit
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT
);
2. ALTER: Modifies existing database objects. For instance, adding a new column:
sql
CopyEdit
ALTER TABLE Students ADD Email VARCHAR(100);
3. DROP: Deletes existing database objects. For example:
sql
CopyEdit
DROP TABLE Students;
4. TRUNCATE: Removes all records from a table without deleting the table itself. For example:
sql
CopyEdit
TRUNCATE TABLE Students;
5. RENAME: Changes the name of a database object. For example:
sql
CopyEdit
RENAME TABLE Students TO Alumni;
Characteristics of DDL
Schema Definition: DDL defines the structure and schema of the database, including tables, fields, data
types, and constraints.
No Data Manipulation: Unlike Data Manipulation Language (DML), DDL does not handle data retrieval or
modification.
Auto-Commit: DDL statements often result in an implicit commit, making changes permanent.
Importance of DDL in DBMS
Database Initialization: Essential for setting up the initial structure of a database.
Schema Evolution: Facilitates modifications to the database structure as requirements change.
Integrity Enforcement: Allows the definition of constraints to maintain data integrity.
Conclusion
DDL plays a crucial role in the design and maintenance of a database's structure. Understanding DDL commands is
fundamental for database administrators and developers to effectively manage and evolve database systems.
Data Manipulation Language (DML) in DBMS
Introduction
Data Manipulation Language (DML) is a subset of SQL that deals with the manipulation of data present in a database.
It allows users to retrieve, insert, update, and delete data within database tables. DML is essential for performing
operations that modify the data stored in relational databases.
Key DML Commands
1. SELECT: Retrieves data from one or more tables.
sql
CopyEdit
SELECT * FROM Employees;
2. INSERT: Adds new records to a table.
sql
CopyEdit
INSERT INTO Employees (EmployeeID, Name, Department)
VALUES (101, 'John Doe', 'Sales');
3. UPDATE: Modifies existing data within a table.
sql
CopyEdit
UPDATE Employees
SET Department = 'Marketing'
WHERE EmployeeID = 101;
4. DELETE: Removes existing records from a table.
sql
CopyEdit
DELETE FROM Employees
WHERE EmployeeID = 101;
Characteristics of DML
Non-Structural Operations: DML commands do not alter the structure of the database; they only affect the
data.
Transactional Control: DML operations can be grouped into transactions, allowing for commit and rollback
functionalities to maintain data integrity.
Conditional Manipulation: DML commands often use conditions (e.g., WHERE clause) to specify which
records should be affected.
Importance of DML in DBMS
Data Retrieval: Enables users to query and extract specific information from large datasets.
Data Modification: Allows for the insertion, updating, and deletion of data, facilitating dynamic data
management.
Application Development: Essential for backend operations in applications that require data interaction,
such as CRUD (Create, Read, Update, Delete) functionalities.
Conclusion
Data Manipulation Language is a critical component of SQL that empowers users to interact with the data stored in
relational databases effectively. Mastery of DML commands is fundamental for database administrators and
developers to ensure accurate and efficient data management.
