MITM, Jamshedpur Module: 02

Unit 2: Relational Database Design Features of Good Relational Designs

A good relational database design ensures data integrity, reduces redundancy, and enhances performance. Below are
the key features of good relational design:

1. Normalization

Normalization is a systematic approach to organize data in a database to eliminate redundancy, avoid anomalies and
ensure data consistency. The process involves breaking down large tables into smaller, well-structured ones and
defining relationships between them. This not only reduces the chances of storing duplicate data but also improves
the overall efficiency of the database.

• 1NF (First Normal Form): Ensures atomicity (each column contains indivisible values).
• 2NF (Second Normal Form): Eliminates partial dependencies (every non-key column depends on the entire
primary key).
• 3NF (Third Normal Form): Eliminates transitive dependencies (non-key attributes depend only on the
primary key).
• BCNF (Boyce-Codd Normal Form) : Eliminates all anomalies remaining in 3NF by ensuring that every
determinant is a super key. This form is especially useful when a table has multiple candidate keys and
complex dependencies. BCNF ensures that every determinant in a relation is a super key.

2. Proper Use of Primary Keys and Foreign Keys

• Primary Key: A unique identifier for each record in a table. It ensures that each row is uniquely identified.
• Foreign Key: A reference to a primary key in another table, ensuring referential integrity and maintaining
relationships between tables.

3. Data Integrity and Consistency

• Entity Integrity: Every table must have a unique primary key, and it should not be NULL.
• Referential Integrity: Foreign keys should correctly reference existing values in the related table.
• Domain Integrity: Ensures values in a column follow defined constraints (e.g., age should be a positive
number).

4. Minimal Data Redundancy

A well-designed relational database avoids storing duplicate data by properly normalizing tables. This improves
efficiency and reduces storage space.

5. Efficient Query Performance

A good relational design ensures fast retrieval of data using:

• Proper indexing (e.g., clustered and non-clustered indexes).

• Optimized queries (using joins, subqueries, and views efficiently).
• Partitioning large tables to enhance performance.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 1
MITM, Jamshedpur Module: 02
6. Scalability and Flexibility

• The database should allow:

• Easy addition of new tables and relationships.
• Modifications to data structure without affecting existing applications.
• Handling of large datasets efficiently.

7. Security and Access Control

A relational database should implement:

• User authentication and authorization.

• Role-based access control (RBAC).
• Encryption for sensitive data.

8. Concurrency Control and Transaction Management

Ensures that multiple users can access and modify data simultaneously without conflicts. Features include:

• ACID properties (Atomicity, Consistency, Isolation, Durability).

• Locking mechanisms to prevent data inconsistencies.

9. Use of Constraints and Triggers

• Constraints (e.g., UNIQUE, NOT NULL, CHECK, DEFAULT) help enforce business rules.
• Triggers execute predefined actions automatically when certain conditions are met.

10. Backup and Recovery Mechanisms

A well-designed relational database should have:

• Automated backups (full, incremental, and differential).

• Disaster recovery plans to restore lost data.

Atomic Domains and First Normal Form (1NF) –

1. Atomic Domains Definition:

An atomic domain is a domain in which values are indivisible and cannot be further broken down. In other words,
each attribute (column) in a table should have values that are atomic, meaning they represent a single unit of data.

Examples of Atomic and Non-Atomic Domains:

Column Name Non-Atomic (Not Atomic Domain) Atomic (Atomic Domain)

Phone_Number 9876543210, 9123456789 9876543210 (One per row)
Address "House No 12, Delhi, India" "House No 12" (Separate columns for Street, City,
Country)
Subjects "Maths, Science, English" Maths (Separate records for each subject)

Key Characteristics of Atomic Domains:

• Each attribute should contain a single value, not a set of values or lists.
• If a value can be further divided, it should be broken into separate attributes or records.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 2
MITM, Jamshedpur Module: 02
First Normal Form (1NF)

A relation (table) is said to be in First Normal Form (1NF) if:

1. All attributes contain atomic values (no multivalued or composite attributes).

2. Each column contains values of a single type (homogeneous data type).
3. Each column has a unique name.
4. The order of rows and columns does not matter.

Steps to Convert a Table to 1NF:

• Identify Multi-Valued Attributes – Check for columns containing multiple values in a single cell.
• Remove Multi-Valued Attributes – Split them into separate rows or create a new table.
• Ensure Atomicity – Each column should have a single value per row (no repeating groups).
• Define a Primary Key – Select a unique identifier (may use a composite key if needed).
• Maintain Referential Integrity – If necessary, create separate tables and use foreign keys.
• Verify 1NF Compliance – Ensure all columns contain atomic values, and the table structure is correct.

Example of 1NF Transformation :

Non-1NF Table (Before Transformation)

Employee_ID Name Skills Phone Numbers

201 Ankit Java, Python 9876543210, 9123456789
202 Meera SQL, C++ 9988776655
203 Rohan HTML, CSS, JavaScript 8765432109, 7654321987

Issues:

• The "Skills" column contains multiple values in a single cell.

• The "Phone Numbers" column also contains multiple values.

1. Main Employee Table (Primary Table)

Employee_ID (Primary Key) Name

201 Ankit
202 Meera
203 Rohan

Primary Key: Employee_ID (Unique for each employee)

2. Employee Skills Table

Employee_ID (Foreign Key) Skill

201 Java
201 Python
202 SQL
202 C++
203 HTML
203 CSS
203 JavaScript

Foreign Key: Employee_ID (References Employee_ID from the Employee table)

Primary Key: Combination of (Employee_ID, Skill)

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 3
MITM, Jamshedpur Module: 02
3. Employee Phone Numbers Table

Employee_ID (Foreign Key) Phone Number

201 9876543210
201 9123456789
202 9988776655
203 8765432109
203 7654321987

Foreign Key: Employee_ID (References Employee_ID from the Employee table)

Primary Key: Combination of (Employee_ID, Phone Number)

Final Foreign Key Relationships

• Employee_ID in Employee_Skills table → Foreign Key referencing Employee_ID in

Employee table.
• Employee_ID in Employee_PhoneNumbers table → Foreign Key referencing Employee_ID in Employee table.

Summary

• Foreign Keys: Employee_ID in both the Skills Table and Phone Numbers Table.
• Primary Keys:
• Employee_ID in Employee Table.
• (Employee_ID, Skill) in Skills Table.
• (Employee_ID, Phone Number) in Phone Numbers Table.

These relationships ensure referential integrity and keep the database properly normalized

in First Normal Form (1NF).

Decomposition using Functional Dependencies

Functional Dependency (FD)

• A functional dependency (FD) is a relationship between two attributes, where one attribute uniquely
determines another.
• If X → Y, it means that for a given value of X, there is a unique value of Y. Example:
• Roll_No → Student_Name (A roll number uniquely determines a student’s name).
• Employee_ID → Department (An employee ID uniquely determines the department).

Second Normal Form (2NF)

A table is in Second Normal Form (2NF) if:

1. It is already in First Normal Form (1NF).

2. It does not have Partial Dependency, meaning non-key attributes must depend on the entire primary key
and not just a part of it.

Understanding Partial Dependency:

Partial dependency occurs when a non-key attribute depends only on a part of a composite primary key rather than
the whole primary key.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 4
MITM, Jamshedpur Module: 02
Example:

Consider the following table:

Student_ID Course_ID Student_Name Course_Name Instructor

S1 C101 Rahul DBMS Prof. Sharma
S2 C102 Anjali OS Prof. Mehta
S1 C102 Rahul OS Prof. Mehta
• Primary Key: (Student_ID, Course_ID) (Composite key).
• Issue:
• Student_Name depends only on Student_ID.
• Course_Name and Instructor depend only on Course_ID.
• These are partial dependencies, violating 2NF.

Converting to 2NF:

To remove partial dependencies, we split the table into separate tables:

Students Table:

Student_ID Student_Name
S1 Rahul
S2 Anjali

Courses Table:

Course_ID Course_Name Instructor

C101 DBMS Prof. Sharma
C102 OS Prof. Mehta

Enrollment Table (Mapping Student and Course):

Student_ID Course_ID
S1 C101
S2 C102
S1 C102
Now, each non-key attribute depends on the whole primary key, ensuring 2NF compliance.

Third Normal Form (3NF) –

A table is said to be in Third Normal Form (3NF) if:

1. It is in Second Normal Form (2NF) (i.e., no partial dependencies).

2. It has no transitive dependency, meaning that all non-key attributes should depend only on the primary
key and not on another non-key attribute.

Understanding Transitive Dependency:

A transitive dependency occurs when a non-key attribute depends on another non-key attribute, which in turn
depends on the primary key.

Example of Transitive Dependency:

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 5
MITM, Jamshedpur Module: 02
Consider the following table:

Student_ID Student_Name Course_ID Course_Name Instructor Instructor_Office

S1 Rahul C101 DBMS Prof. Sharma Room 101
S2 Anjali C102 OS Prof. Mehta Room 202
S3 Ramesh C101 DBMS Prof. Sharma Room 101
Identifying Issues in 2NF Table:

• Primary Key: (Student_ID, Course_ID) (Composite Key).

• Partial Dependencies Removed? ✅ Yes (Each attribute depends on the whole key).
• Transitive Dependencies Present? ❌ Yes:
• Instructor_Office depends on Instructor rather than directly on Student_ID, Course_ID.
• Course_Name depends on Course_ID rather than directly on the primary key.

Since Instructor_Office is indirectly dependent on Course_ID, which is not part of the primary key, this violates 3NF.

Converting the Table to 3NF

To remove transitive dependencies, we separate the related data into multiple tables.

Step 1: Create a Students Table

Student_ID Student_Name
S1 Rahul
S2 Anjali
S3 Ramesh

Step 2: Create a Courses Table

Course_ID Course_Name Instructor

C101 DBMS Prof. Sharma
C102 OS Prof. Mehta

Step 3: Create an Instructors Table

Instructor Instructor_Office
Prof. Sharma Room 101
Prof. Mehta Room 202

Step 4: Create an Enrollments Table (Mapping Students to Courses)

Student_ID Course_ID
S1 C101
S2 C102
S3 C101

Final Database Structure (3NF Compliant)

Now, all non-key attributes depend only on the primary key and not on other non-key attributes.

Thus, we have successfully removed transitive dependencies, making our database 3NF compliant.

Advantages of 3NF:

✔ Reduces redundancy: Eliminates unnecessary data duplication.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 6
MITM, Jamshedpur Module: 02
✔ Ensures data integrity: Each table stores only relevant data.

✔ Easier data maintenance: Updates and modifications become efficient.

✔ Prevents update anomalies: Avoids inconsistent data updates.

Boyce-Codd Normal Form (BCNF)

Definition:

A table is in Boyce-Codd Normal Form (BCNF) if:

1. It is already in Third Normal Form (3NF).

2. For every functional dependency (A → B), A should be a super key (i.e., A must be able to determine all
attributes of the table uniquely).

Why is BCNF Needed?

Even after achieving 3NF, anomalies can still exist in certain cases, especially when a candidate key has multiple
attributes and there are dependencies between those attributes.

Example of BCNF Violation:

Consider the following table for students and the courses they take, along with their instructors:

Student_ID Course_ID Instructor

S1 C101 Prof. Sharma
S2 C102 Prof. Mehta
S3 C101 Prof. Sharma

Functional Dependencies:

1. (Student_ID, Course_ID) → Instructor ✅ (Primary key is Student_ID + Course_ID)

2. Instructor → Course_ID ❌ (Instructor uniquely determines Course_ID, but Instructor is not a super key)

Here, the Instructor determines Course_ID, but Instructor is not a super key, violating BCNF.

Converting to BCNF:

To fix this, we split the table into two:

1. Instructors Table

Instructor Course_ID
Prof. Sharma C101
Prof. Mehta C102

2. Enrollment Table

Student_ID Course_ID
S1 C101
S2 C102
S3 C101
Now, all dependencies are on super keys, making the tables BCNF compliant.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 7
MITM, Jamshedpur Module: 02
Fourth Normal Form (4NF)

Definition:

A table is in Fourth Normal Form (4NF) if:

1. It is in BCNF.
2. It has no multi-valued dependencies.

Understanding Multi-Valued Dependency (MVD):

Multi-valued dependencies occur when:

• One attribute in a table determines multiple values of another attribute, independently of other attributes.

Example of 4NF Violation:

Student_ID Course Hobby

S1 DBMS Reading
S1 DBMS Music
S1 OS Reading
S1 OS Music

Here, two independent relationships exist:

1. Student_ID →→ Course
2. Student_ID →→ Hobby

Each student can take multiple courses independent of their hobbies, causing redundancy.

Converting to 4NF:

We separate the two independent relationships:

1. Student_Course Table

Student_ID Course
S1 DBMS
S1 OS

2. Student_Hobby Table

Student_ID Hobby
S1 Reading
S1 Music
Now, the redundancy is eliminated, and the tables are in 4NF.

Advantages of Achieving 4NF

1. Eliminates Multi-Valued Dependencies

a. Ensures that each attribute set depends only on the primary key.
2. Reduces Data Redundancy
a. Avoids duplicate data storage, improving efficiency.
3. Prevents Data Anomalies
a. Reduces insertion, update, and deletion anomalies.
4. Enhances Data Integrity
a. Ensures accurate and consistent data.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 8
MITM, Jamshedpur Module: 02
Example (Before 4NF - Violation)

Employee_ID Skill Project

E101 Java Alpha
E101 Java Beta
E101 Python Alpha
E101 Python Beta
• Here, Skill and Project are independent, leading to redundancy.

Example (After 4NF - Solution)

Employee_Skill Table

Employee_ID Skill
E101 Java
E101 Python

Employee_Project Table

Employee_ID Project
E101 Alpha
E101 Beta
Now, the redundancy is removed, and data integrity is maintained.

5. Optimizes Storage
• Saves disk space by avoiding repeated values.
6. Improves Query Performance
• Queries execute faster with smaller, well-structured tables.

7. Better Scalability

• Makes the database easier to maintain as data grows.

Functional Dependency Theory –

Introduction to Functional Dependency

Functional Dependency (FD) is a fundamental concept in relational database design. It describes the relationship
between attributes in a relation and is used to ensure data consistency and minimize redundancy.

Definition of Functional Dependency

A functional dependency (FD) is a constraint between two sets of attributes in a relation.

• If X and Y are subsets of attributes in a relation R, then X → Y (X determines Y) means that for every unique value
of X, there is only one corresponding value of Y.

Example:

Consider a relation Student with attributes (Student_ID, Name, Age, Department).

• Student_ID → Name, Age, Department

• This means that for a given Student_ID, there is a unique Name, Age, and Department.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 9
MITM, Jamshedpur Module: 02
Types of Functional Dependencies

1. Trivial Functional Dependency

A functional dependency X → Y is trivial if Y is a subset of X.

Example:

In a table R(A, B):

• {A, B} → A is trivial because A is already part of {A, B}.

General Rule:

If X → Y and Y ⊆ X, then it is trivial.

2. Non-Trivial Functional Dependency

A functional dependency X → Y is non-trivial if Y is NOT a subset of X.

Example:

In a Student table with attributes (Student_ID, Name, Age):

• Student_ID → Name, Age is non-trivial because Name and Age are not part of Student_ID.

3. Completely Non-Trivial Functional Dependency

A functional dependency X → Y is completely non-trivial if X and Y have no common attributes.

Example:

In a Product table (Product_ID, Price, Category):

• Product_ID → Price is completely non-trivial because Product_ID and Price have no overlap.

4. Multivalued Dependency (MVD)

A multi-valued dependency (MVD) X ↠ Y exists when, for a given X, multiple values of Y exist independently of other
attributes.

Example:

Consider a Student table (Student_ID, Course, Hobby):

• Student_ID ↠ Hobby

• Student_ID ↠ Course

Here, a student's hobbies are independent of their courses, forming an MVD.

4NF is used to eliminate MVDs.

5. Transitive Functional Dependency

A transitive dependency exists when X → Y and Y → Z, then X → Z.

Example:

In an Employee table (Emp_ID, Department, Manager):

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 10
MITM, Jamshedpur Module: 02
• Emp_ID → Department
• Department → Manager

Thus, Emp_ID → Manager (transitive dependency).

3NF is used to remove transitive dependencies.

6. Partial Functional Dependency

A partial dependency exists when a non-prime attribute is dependent on part of a candidate key, not the whole key.

Example:

Consider a Student_Course table (Student_ID, Course_ID, Course_Name) with

(Student_ID, Course_ID) as the primary key.

• Course_ID → Course_Name (Partial Dependency)

Since Course_Name depends only on Course_ID, not both Student_ID and Course_ID, it violates 2NF.

7. Join Dependency (JD)

A join dependency exists when a relation R(A, B, C) can be reconstructed by joining its

projections (subsets of columns) without loss of information.

Example:

A table R(A, B, C) can be decomposed into R1(A, B) and R2(A, C).

• If joining R1 and R2 gives the original table without loss, then JD exists.

• Eliminated using 5NF.

Summary of Functional Dependencies

Type Definition Example Normalization Used

Trivial FD Y is a subset of X {A, B} → A No Normalization Needed
Non-Trivial FD Y is not a subset of X Student_ID → Name, Age Used in Normal

Algorithm for Decomposition in DBMS

Decomposition in DBMS is used to break down a large relation into smaller and more manageable relations while
maintaining data integrity, lossless join, and dependency [Link] are different types of decomposition,
including functional dependency-based decomposition and multi-valued dependency-based decomposition.

1. Algorithm for Functional Dependency-Based Decomposition

This algorithm is used to decompose a relation while ensuring lossless decomposition and dependency preservation.

Algorithm Steps:

Step 1: Find Candidate Keys

• Identify the functional dependencies (FDs) in the given relation.

• Determine the candidate keys to ensure uniqueness of records.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 11
MITM, Jamshedpur Module: 02
Step 2: Check for BCNF (Boyce-Codd Normal Form)

• Check if the given relation is already in BCNF.

• If a relation is not in BCNF, identify the violating FD (i.e., the FD that does not satisfy BCNF conditions).

Step 3: Decompose the Relation

• Split the relation into two based on the violating FD:

• R1 = The determinant and dependent attributes of the violating FD.
• R2 = The remaining attributes (including the determinant).

Step 4: Repeat Until BCNF is Achieved

• Continue checking each decomposed relation.

• If any relation still violates BCNF, repeat the decomposition process.
• Stop when all relations satisfy BCNF.

Step 5: Verify Lossless Join and Dependency Preservation

• Ensure that the decomposition satisfies the lossless join condition using the attribute closure method.
• Ensure that all original functional dependencies are still enforceable in the decomposed relations.

Example:

Given Relation R(A, B, C, D) with FDs:

1. A→B
2. B→C

Step 1: Identify Candidate Key

• Closure of A+: {A, B, C} (Does not contain D)

• Closure of D+: {D} (Does not contain other attributes)
• Candidate key = {A, D}

Step 2: Check for BCNF Violation

• The FD B → C violates BCNF because B is not a superkey.

• The FD A → B also violates BCNF because A is not a superkey.

Step 3: Decompose the Relation

• Decompose R into two relations:

• R1(A, B) (Because A → B)
• R2(B, C, D) (Remaining attributes)

Step 4: Repeat Check for BCNF

• R1(A, B) is in BCNF because A is a superkey.

• R2(B, C, D): The FD B → C still violates BCNF because B is not a superkey.
• Further decompose R2(B, C, D) into:
• R3(B, C) (Because B → C)
• R4(B, D) (Remaining attributes)

Final Decomposed Relations:

• R1(A, B)
• R3(B, C)

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 12
MITM, Jamshedpur Module: 02
• R4(B, D)

Now, all relations are in BCNF, and the decomposition is lossless.

2. Algorithm for Multi-Valued Dependency-Based Decomposition (4NF Decomposition)

This algorithm is used when a relation contains multi-valued dependencies (MVDs).

Algorithm Steps:

Step 1: Identify Multi-Valued Dependencies (MVDs)

• A multi-valued dependency (MVD) occurs when two independent sets of attributes depend on the same key.
• Identify all MVDs in the given relation.

Step 2: Check for Fourth Normal Form (4NF)

• A relation is in 4NF if:

o It is in BCNF.
o There are no non-trivial MVDs (i.e., MVDs where the determinant is not a superkey).

Step 3: Decompose Based on MVD

• If a MVD X →→ Y violates 4NF, split the relation into two:

o R1(X, Y)
o R2(X, Z) (Where Z = All other attributes excluding Y)

Step 4: Repeat Until 4NF is Achieved

• Check each decomposed relation for further MVD violations.

• Continue decomposition until all relations satisfy 4NF.

Example:

Given Relation: R(A, B, C) with Multi-Valued Dependency:

• A →→ B (A determines multiple values of B independently of C)

Step 1: Identify Candidate Key

• A is a superkey because A uniquely determines both B and C.

Step 2: Check for 4NF Violation

• The MVD A →→ B violates 4NF because A is not a superkey.

Step 3: Decompose the Relation

• Split R into two relations:

o R1(A, B)
o R2(A, C)

Now, both relations satisfy 4NF, and the decomposition is lossless.

Database Design Process and Its Issues

The database design process is crucial for creating a structured and efficient database that meets business
requirements. It involves multiple steps, from requirement gathering to normalization, ensuring data integrity and
security.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 13
MITM, Jamshedpur Module: 02
Database Design Process

1. Requirement Analysis

• Understand the purpose of the database.

• Identify business rules and data needs.
• Gather user requirements through interviews, surveys, and document analysis.

2. Conceptual Design

• Create an Entity-Relationship Diagram (ERD) to visualize entities, attributes, and relationships.

• Identify primary keys (PK) and foreign keys (FK).
• Define relationships (one-to-one, one-to-many, many-to-many).

3. Logical Design

• Convert the ERD into a Relational Model.

• Normalize the data to avoid redundancy.
• Define tables, columns, data types, and constraints.

4. Normalization

• Apply normalization rules to remove data redundancy:

o 1NF (First Normal Form): Remove duplicate columns and create separate tables for related data.
o 2NF (Second Normal Form): Remove partial dependencies.
o 3NF (Third Normal Form): Remove transitive dependencies.
o BCNF (Boyce-Codd Normal Form): Ensure strict normalization where necessary.

5. Physical Design

• Choose a database management system (DBMS) like MySQL, SQL Server, or PostgreSQL.
• Define indexing strategies for fast retrieval.
• Optimize storage and partitioning for large databases.

6. Implementation

• Create database schemas and tables using SQL.

• Populate tables with sample data.
• Define views, triggers, and stored procedures.

7. Testing and Validation

• Test database performance and integrity.

• Conduct functional testing with real-world scenarios.
• Verify security measures (encryption, access control).

8. Deployment and Maintenance

• Deploy the database for production use.

• Perform regular maintenance, backups, and monitoring.
• Optimize queries and indexing as data grows.

Issues in Database Design

Designing a database is a complex process that requires careful planning. If not done properly, it can lead to various
problems that affect performance, security, and data integrity. Below are the most common issues encountered in
database design, along with their causes and solutions.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 14
MITM, Jamshedpur Module: 02
1. Poor Requirement Analysis Issue:

• Many databases are designed without a clear understanding of business requirements.

• Missing fields or tables lead to difficulties in adding new functionalities later.
• Lack of scalability planning results in an inability to handle large datasets in the future.

Example:

A university database is designed only for student records but later needs to store faculty information. If this
requirement was not considered initially, the database structure may need significant modifications later.

Solution:

✅ Conduct detailed requirement analysis before designing the database.

✅ Consult stakeholders, business analysts, and end-users to understand all use cases.

✅ Design the database with future scalability in mind.

2. Data Redundancy and Anomalies Issue:

• Redundant data leads to wasted storage space and inconsistencies.

• Without proper normalization, data modification issues arise, such as:
o Update anomaly: Changes made to one record do not update others correctly.
o Insertion anomaly: Some data cannot be inserted without adding unnecessary information.
o Deletion anomaly: Deleting a record removes other valuable information unintentionally.

Example:

A hospital database stores patient and doctor details in one table:

Patient_ID Patient_Name Doctor_ID Doctor_Name Specialization

P101 John Doe D01 Dr. Smith Cardiology
P102 Alice Brown D02 Dr. Jane Neurology
P103 Bob Wilson D01 Dr. Smith Cardiology
• If Dr. Smith leaves, deleting Doctor_ID = D01 will also remove John Doe's and Bob Wilson's records, causing a
deletion anomaly.

Solution:

✅ Normalize the database using 1NF, 2NF, 3NF, and BCNF to eliminate redundancy.

✅ Use separate tables for patients and doctors, linked through foreign keys.

3. Scalability Issues

Issue:

• Databases designed without scalability struggle to handle large datasets.

• Queries become slow, and storage gets overloaded due to unoptimized data structures.
• High user traffic can lead to server crashes if the database is not designed for scalability.

Example:

An e-commerce website starts with 1,000 products, but as the business grows, the number reaches 10 million. If the
database was not optimized for large-scale operations, performance issues arise.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 15
MITM, Jamshedpur Module: 02
Solution:

✅ Use indexing to speed up data retrieval.

✅ Implement database partitioning to distribute the load.

✅ Choose scalable database technologies like MySQL, PostgreSQL, or NoSQL (MongoDB, DynamoDB).

4. Poor Normalization Issue:

• Over-normalization leads to too many small tables, causing excessive joins and slow queries.
• Under-normalization causes data redundancy, making updates inefficient.

Example:

An over-normalized order management system may store data in too many tables:

1. Orders Table → Stores only Order_ID

2. Order_Items Table → Stores items linked to Order_ID
3. Customers Table → Stores Customer_ID, linked to Order_ID

If too many JOIN operations are needed to fetch order details, it can slow down query performance.

Solution:

✅ Normalize only up to 3NF or BCNF for most cases.

✅ Denormalize certain tables for faster query execution when necessary.

5. Security Concerns

Issue:

• Lack of user access control allows unauthorized users to modify data.

• No encryption makes sensitive data vulnerable to hacking.
• SQL injection attacks can occur due to poorly written queries.

Example:

A banking database stores customer passwords in plain text, making it easy for hackers to steal login credentials.
Solution:

✅ Implement role-based access control (RBAC) to restrict user permissions.

✅ Encrypt sensitive data like passwords using hashing (SHA-256, bcrypt).

✅ Use parameterized queries to prevent SQL injection attacks.

6. Poor Indexing and Query Optimization

Issue:

• No indexes lead to slow query performance.

• Too many indexes slow down INSERT, UPDATE, and DELETE operations.

Example:

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 16
MITM, Jamshedpur Module: 02
A table with 1 million rows has no index on the "customer_name" column. Running: SELECT * FROM Customers
WHERE customer_name = 'John Doe';
will scan all 1 million rows, making it very slow.

Solution:

Use indexes on columns that are frequently searched.

Avoid over-indexing—use only necessary indexes.

7. Lack of Backup and Recovery Planning Issue:

• No regular backups can lead to permanent data loss in case of system failure.
• No disaster recovery plan increases downtime during a crash.

Example:

A hospital database crashes, and all patient records are lost because no backup was maintained.

Solution:

Schedule automated backups (daily, weekly, monthly). Implement cloud-based storage for backup redundancy. Set up
a disaster recovery plan for quick restoration.

8. Data Integrity Issues Issue:

• Poorly defined constraints allow invalid or duplicate data.

• Orphan records occur when a foreign key references a deleted primary key.

Example:

A university database allows students to register for courses that do not exist, causing inconsistency.

Solution:

Use PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK constraints to enforce integrity.

Enable ON DELETE CASCADE to automatically remove orphan records.

9. Poor Handling of Transactions & Concurrency Issue:

• Deadlocks occur when multiple transactions block each other.

• Dirty Reads happen when one transaction reads uncommitted changes from another.

Example:

Two bank transactions:

1. User A transfers ₹10,000 to User B

2. User B withdraws ₹10,000 immediately

If the first transaction fails before committing, User B still gets ₹10,000, causing inconsistency.

Solution:

Use ACID-compliant transactions to ensure Atomicity, Consistency, Isolation, Durability.

Implement LOCKING mechanisms to prevent concurrency issues.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 17
MITM, Jamshedpur Module: 02
Review of SQL (Structured Query Language)
SQL (Structured Query Language) is a standard language used to create, manage, and manipulate databases. It allows
users to perform various operations like inserting, updating, deleting, retrieving data, and managing database
structures.

Introduction to SQL

• SQL (Structured Query Language) is a language used to interact with Relational Database Management
Systems (RDBMS) like MySQL, SQL Server, PostgreSQL, and Oracle.
• SQL allows users to store, retrieve, modify, delete, and manage
• data efficiently.
• SQL follows ACID (Atomicity, Consistency, Isolation, Durability) properties to ensure data integrity.

1. Types of SQL Commands

SQL commands are categorized into five main types:

Command Type Purpose Examples

DDL (Data Definition Language) Defines database structure CREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation Language) Manipulates data in tables INSERT, UPDATE, DELETE
DQL (Data Query Language) Retrieves data from tables SELECT
DCL (Data Control Language) Manages access permissions GRANT, REVOKE
TCL (Transaction Control Language) Manages transactions COMMIT, ROLLBACK, SAVEPOINT

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 18
MITM, Jamshedpur Module: 02
2. SQL Basic Syntax

A) Creating a Database and Table

B) Inserting Data into a Table

C) Retrieving Data (SELECT Statement)

SELECT * FROM Students;

D) Filtering Data (WHERE Clause)

SELECT Name, Course FROM Students WHERE Age > 20;

E) Updating Data

UPDATE Students SET Age = 22 WHERE Student_ID = 101;

F) Deleting Data

DELETE FROM Students WHERE Student_ID = 101;

SQL Data Types

SQL has different data types categorized into:

• Numeric Data Types:

o TINYINT, SMALLINT, INT, BIGINT
o DECIMAL, FLOAT, DOUBLE
• String Data Types:
o CHAR(n), VARCHAR(n), TEXT
• Date & Time Data Types:
o DATE, TIME, DATETIME, TIMESTAMP
• Boolean Data Type:
o BIT

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 19
MITM, Jamshedpur Module: 02
SQL Constraints

Constraints ensure data integrity.

• PRIMARY KEY: Ensures uniqueness and non-null values.

• FOREIGN KEY: Maintains referential integrity.

• UNIQUE: Ensures unique values in a column.

• NOT NULL: Prevents null values.

• CHECK: Defines conditions for values.

• DEFAULT: Sets a default value.

Example:

SQL Operators

Operators are used in SQL queries for filtering and calculations.

1) Comparison Operators:

=, !=, >, <, >=, <=, BETWEEN, IN, LIKE

2) Logical Operators:

AND, OR, NOT

Intermediate SQL
1. Joins

Joins in SQL are used to combine data from two or more tables based on a related column. They help retrieve
meaningful information by linking records from different tables.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 20
MITM, Jamshedpur Module: 02

2. SUBQUERIES (Nested Queries)

A subquery is a query inside another query, used for filtering or calculations.

Types of Subqueries:

• Scalar Subquery – Returns a single value.

• Multiple-row Subquery – Returns multiple rows.
• Correlated Subquery – Uses values from the outer query.

Example - Using Subquery in WHERE Clause

– This query returns employees earning more than the average salary.

3. Aggregate Functions in SQL

Aggregate Functions in SQL are used to perform calculations on multiple rows of a column and return a single
summarized value.

Common Aggregate Functions:

1. COUNT() – Counts the total number of records

SELECT COUNT(*) FROM Employees;

This query returns the total number of rows in the Employees table.

2. SUM() – Calculates the total sum of a numeric column

SELECT SUM(Salary) FROM Employees;

This returns the sum of all salaries in the Employees table.

3. AVG() – Calculates the average value of a numeric column

SELECT AVG(Salary) FROM Employees;

This returns the average salary of all employees.

4. MIN() – Finds the minimum value in a column

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 21
MITM, Jamshedpur Module: 02
SELECT MIN(Salary) FROM Employees;

This returns the lowest salary.

5. MAX() – Finds the maximum value in a column

SELECT MAX(Salary) FROM Employees;

This returns the highest salary.

6. GROUP BY with Aggregate Functions – Used to group data before applying an aggregate function

SELECT Department, AVG(Salary)

FROM Employees
GROUP BY Department;

This query returns the average salary for each department.

4. Transactions – Ensuring ACID Compliance

SQL transactions follow ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure reliable database
operations.

If an issue occurs, we can rollback to the last safe state:

ROLLBACK;

Advanced SQL – Optimization and Automation

Advanced SQL focuses on optimizing queries and automating tasks using various techniques such as stored
procedures, triggers, window functions, and materialized views. Here’s a breakdown of each concept:

1. Stored Procedures

• Stored procedures are precompiled SQL queries that execute a set of commands.
• They improve performance by reducing network traffic and reusing execution plans.
• Example:

2. Triggers

• Triggers are automatic responses to specific database events such as INSERT, UPDATE, or DELETE.
• They help enforce business rules and maintain data integrity.
• Example :

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 22
MITM, Jamshedpur Module: 02

3. Window Functions

• Window functions perform calculations across a set of table rows related to the current row.
• They do not collapse rows like aggregate functions but maintain row-level details.
• Common functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG().
• Example:

4. Materialized Views

• Materialized views store query results physically and can be refreshed periodically.
• They improve performance for complex queries by reducing computation time.
• Example:

intermediate and advanced SQL features improve database functionality

Intermediate and advanced SQL features significantly enhance database functionality by improving performance,
security, maintainability, and scalability. Here’s how:

1. Data Integrity and Consistency

• Constraints (CHECK, UNIQUE, FOREIGN KEY, PRIMARY KEY) ensure valid data entry.
• Triggers automatically enforce rules and maintain consistency between related tables.

2. Performance Optimization

• Indexes (Clustered & Non-Clustered) speed up queries by reducing the search time.
• Partitioning divides large tables into smaller, manageable sections for faster access.
• Query Optimization Techniques (EXPLAIN PLAN, ANALYZE, CTEs, Hints) help improve execution speed.

3. Advanced Querying Capabilities

• Common Table Expressions (CTEs) and Recursive Queries simplify complex queries by breaking them into
manageable parts.
• Window Functions (RANK, ROW_NUMBER, LEAD, LAG) allow advanced analytical calculations over result
sets.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 23
MITM, Jamshedpur Module: 02
• Subqueries and Correlated Subqueries enable dynamic data retrieval.

4. Enhanced Security and Access Control

• Roles and Permissions restrict data access based on user roles.

• Row-Level Security (RLS) controls which rows a user can access.
• Dynamic Data Masking & Encryption protect sensitive data.

5. Automation and Efficiency

• Stored Procedures automate repetitive tasks and improve performance.

• Functions (Scalar, Table-Valued) encapsulate reusable logic.
• Triggers enable automated actions when specific conditions are met.

6. Scalability and High Availability

• Replication ensures data is available across multiple servers.

• Sharding distributes large datasets across multiple databases.
• Materialized Views improve read performance by storing query results

Role of Security in Relational Databases

Security in relational databases is essential to protect data from unauthorized access, corruption, and loss. It ensures
that sensitive information remains confidential, maintains data integrity, and allows only authorized users to access
or modify the data. Below are the key aspects of database security:

1. Authentication and Authorization

• Authentication ensures that only legitimate users can access the database by requiring login credentials such
as a username and password.
• Authorization defines the level of access for each user, ensuring that they can only perform actions permitted
by their role.
• Role-Based Access Control (RBAC) and Mandatory Access Control (MAC) are commonly used mechanisms.

2. Access Control and Privileges

• Databases implement User Roles and Privileges to restrict access. For example, a database administrator
(DBA) may have full access, while regular users may only have read or write permissions.
• SQL commands like GRANT and REVOKE are used to assign and remove privileges for database objects.

3. Data Encryption

• Encrypting stored data (at rest) and data in transit (when transferred over networks) prevents unauthorized
access even if data is intercepted.
• Common encryption techniques include AES (Advanced Encryption Standard) and SSL/TLS (Secure Sockets
Layer/Transport Layer Security) for secure communication.

4. Auditing and Logging

• Database audit logs record all database activities, including login attempts, queries, and modifications.
• These logs help in detecting security breaches, unauthorized access, or suspicious activities.
• Tools like SQL Server Audit and MySQL Binary Logs assist in tracking database changes.

5. Data Integrity and Constraints

• Data integrity ensures that stored data is accurate, consistent, and reliable.
• Constraints (e.g., PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL) prevent invalid or duplicate
data entry.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 24
MITM, Jamshedpur Module: 02
6. SQL Injection Prevention

• SQL injection is a major security threat where attackers manipulate SQL queries to gain unauthorized access.
• Prevention techniques include:
o Using Prepared Statements to sanitize user input.
o Implementing Input Validation to reject malicious data.
o Restricting direct database access for applications.

7. Database Patching and Updates

• Regularly updating the database software ensures that security vulnerabilities are patched.
• Database vendors release security updates to fix known exploits.

By implementing these security measures, organizations can protect their databases from cyber threats, data leaks,
and unauthorized access, ensuring compliance with data protection laws such as GDPR (General Data Protection
Regulation) and HIPAA (Health Insurance Portability and Accountability Act).

b) How Backup and Recovery Mechanisms Help in Database Disaster Management

Backup and recovery are critical components of database disaster management. They help organizations restore lost
or corrupted data in case of failures, cyber-attacks, or accidental deletions. Below are the key mechanisms used:

1. Types of Database Backups

To ensure data availability, different types of backups are used:

a) Full Backup

• Creates a complete copy of the entire database.

• Example: A company takes a full backup every Sunday night to ensure they have a complete dataset.
• Pros: Reliable and easy to restore.
• Cons: Requires more storage and time.

b) Incremental Backup

• Stores only the data that has changed since the last backup.
• Example: If a full backup was taken on Sunday, an incremental backup on Monday will only store Monday’s
changes.
• Pros: Saves storage space and time.
• Cons: Recovery can take longer since multiple backups need to be restored.

c) Differential Backup

• Stores all changes made since the last full backup (not incremental).
• Example: A full backup is taken on Sunday, and a differential backup is taken on Wednesday. The Wednesday
backup contains all changes from Sunday to Wednesday.
• Pros: Faster recovery than incremental backups.
• Cons: Requires more storage than incremental backups.

d) Transaction Log Backup

• Captures all database transactions that occurred after the last full or incremental backup.
• Helps in point-in-time recovery by rolling back to a specific moment before a crash.
• Common in SQL Server, Oracle, and PostgreSQL.

2. Recovery Mechanisms in Disaster Management

When a disaster occurs, backup data is restored using one of the following recovery methods:

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 25
MITM, Jamshedpur Module: 02
a) Cold Backup and Recovery

• The database is completely shut down before the backup is taken.

• Used in situations where downtime is acceptable.

b) Hot Backup and Recovery

• Backup is taken while the database is still running.

• Used in high-availability systems that cannot afford downtime.

c) Point-in-Time Recovery (PITR)

• Uses transaction log backups to restore the database to a specific moment before an error or failure
occurred.
• Useful in cases where data corruption or accidental deletion occurs.

3. Disaster Recovery Strategies

To minimize downtime and data loss, organizations use the following strategies:

a) Database Replication

• Keeps a real-time copy of the database on a secondary server.

• If the primary database fails, the secondary database takes over.
• Example: MySQL Master-Slave Replication.

b) Cloud-Based Backup and Recovery

• Stores backups in cloud services like AWS, Google Cloud, or Azure.

• Ensures data is safe even if on-premise servers fail.

c ) Automated Backup Scheduling

• Automates the backup process using database tools like:

o SQL Server Agent Jobs
o MySQL mysqldump Utility
o Oracle RMAN (Recovery Manager)

d) Disaster Recovery Plan (DRP)

• A documented plan that includes:

o Steps for restoring databases.
o Estimated recovery time objectives (RTOs) and recovery point objectives (RPOs).
o Responsibilities of the IT team in case of failure.

4. Importance of Backup and Recovery in Database Disaster Management

• Minimizes Data Loss – Ensures that critical data is not lost due to system failures or cyber-attacks.
• Reduces Downtime – Quick recovery means businesses can continue operations with minimal disruption.
• Protects Against Ransomware Attacks – Having an offline backup ensures data can be restored without
paying ransom.
• Ensures Business Continuity – Organizations can recover from disasters and continue normal operations
without losing critical information.

Mr. Koushik Dey (Assistant professor)

Department of Computer Science And Engineering Page No. 26