2304 TMA & Record Book for 242 Term

1. Define Relational DBMS?

Ans: A Relational Database Management System (RDBMS) is a type of database software
that stores and manages data in a structured format using tables (relations). Each table consists
of rows (records) and columns (fields), and relationships between tables are established using
keys. So we can stated that an RDBMS is a system that manages data by organizing it into related
tables and allows users to interact with that data using SQL. Popular RDBMS software includes:
MySQL, Oracle Database, Microsoft SQL Server, PostgreSQL. Some features of RDBMS are
mentioned below:
 Table-based structure: Data is organized into tables.
 Relationships: Tables are linked using primary keys and foreign keys.
 SQL support: Uses Structured Query Language (SQL) to insert, update, delete, and
retrieve data.
 Data integrity: Ensures accuracy and consistency through constraints.
 Normalization: Reduces data redundancy and improves efficiency.

2. What is the role of normalization in reducing anomalies in a database?

Answer: Database normalization is a process used in database design to organize data efficiently.
It can help to reduce data redundancy (duplicate data) and improve data integrity (data accuracy
and consistency). It's like organizing a messy filing cabinet: instead of having the same
information in multiple places, you put each piece of information in one spot and then use a
system of cross-references to connect them. The primary objective for normalizing the relations
is to eliminate the below anomalies. Failure to reduce anomalies results in data redundancy, which
may threaten data integrity and cause additional issues as the database increases. Role of
normalization in reducing anomalies in databases are described below:
 Insertion Anomalies: Insertion anomalies occur when it is not possible to insert data into
a database because the required fields are missing or because the data is incomplete. For
example, if a database requires that every record has a primary key, but no value is provided
for a particular record, it cannot be inserted into the database.
 Deletion anomalies: Deletion anomalies occur when deleting a record from a database and
can result in the unintentional loss of data. For example, if a database contains information

1|Page
 about customers and orders, deleting a customer record may also delete all the orders
associated with that customer.
 Updating anomalies: Updating anomalies occur when modifying data in a database and
can result in inconsistencies or errors. For example, if a database contains information
about employees and their salaries, updating an employee’s salary in one record but not in
all related records could lead to incorrect calculations and reporting.
So, we can simply state that, Database normalization is the process of organizing the attributes
of the database to reduce or eliminate data redundancy.

3. What are the key components of a database schema?

Answer: A database schema defines the structure and organization of data within a database. It
outlines how data is logically stored, including the relationships between different tables and other
database objects. The schema serves as a blueprint for how data is stored, accessed, and
manipulated, ensuring consistency and integrity throughout the system.
Key components of a database schema include:
 Tables: These are the fundamental structures that store data in rows (records) and columns
(fields). Each table typically represents a specific entity, such as "Customers" or
"Products".
 Columns (Fields): These define the attributes of the data within a table. Each column is
assigned a specific data type (e.g., integer, string, date, boolean) that dictates the kind of
data it can hold.
 Relationships: These link data across different tables. Relationships (one-to-one, one-to-
many, or many-to-many) are vital for organizing data logically and avoiding redundancy.
 Keys:
o Primary Keys: A unique identifier for each record within a table, ensuring that no
two rows have the same value.
o Foreign Keys: A field in one table that references the primary key in another table,
establishing a link and maintaining referential integrity between them.
 Constraints: These are rules that enforce the validity and integrity of the data. Examples
include NOT NULL (ensures a column cannot be empty), UNIQUE (ensures all values in
a column are different), and CHECK (ensures values are within a specified range).
 Indexes: These are data structures that improve the speed of data retrieval operations by
allowing for quick searches without scanning the entire table.
2|Page
  Views: These are virtual tables based on the result-set of an SQL query. They simplify
complex queries and can be used to enhance security by limiting data access to specific
columns or rows.
 Stored Procedures and Functions: For SQL databases, a schema can also include pre-
compiled sets of SQL statements (stored procedures) that perform specific tasks, which can
be reused to ensure consistency and improve performance.
At last, we can reveal that, database schema acts as a blueprint, outlining tables, fields,
relationships, and rules that govern the data.

4. Write the SQL for deleting a Table.

Answer: To delete a table in SQL, we may use the DROP TABLE statement. The DROP
TABLE command permanently removes a table from the database. It deletes the table’s structure,
data, and related objects such as indexes and triggers. The operation is irreversible and recovery
is possible only if a backup exists.
Syntax: DROP TABLE table_name;
Example: DROP TABLE Students;

5. List the available SQL Aggregate Functions.

Answer: Aggregate functions perform a calculation on a set of values and return a single value.
The most commonly available SQL aggregate functions across different database systems are used to perform
calculations on a set of values and return a single result.

The standard functions include:

 AVG() : Calculates the average (arithmetic mean) of a set of non-NULL values.

 COUNT() : Returns the number of rows that match a specified criterion. COUNT(*) counts all rows,
including those with NULL values, while COUNT(column_name) only counts non-NULL values.

 MAX() : Retrieves the largest value from a column. This function can operate on numeric, string, and
date/time data types.

 MIN() : Retrieves the smallest value from a column. Like MAX() , it works with various data types
including numeric, string, and date/time.

3|Page
  SUM() : Calculates the total sum of all non-NULL values in a numeric column.

Aggregate functions ignore null values (except for COUNT(*)).

6. What are the differences between DROP, TRUNCATE and DELETE commands?

Answer: In SQL, the DELETE, DROP, and TRUNCATE commands is important for efficient
data management. While these commands are all used to remove data, they differ significantly in
functionality, usage, and performance. The following table lists all the major differences between
DELETE, DROP, and TRUNCATE:

Parameter DELETE DROP TRUNCATE

Type DML DDL DDL

Deletes specific rows based on a Deletes the entire table or Deletes all rows but retains table
Purpose
condition database structure

DELETE FROM table_name WHERE

Syntax DROP TABLE table_name; TRUNCATE TABLE table_name;
condition;

Rollback Support Can be Rollback Cannot be Rollback Cannot be Rollback

Removes table and data

Data Removal Removes selected rows Removes all rows
completely

Slower, as each row is processed Instant removal, affecting Faster than DELETE but slower than
Efficiency
individually schema DROP

Triggers Fires triggers Does not fire triggers Does not fire triggers

7. How does a transaction ensure the consistency of a database?

Answer: Consistency is one of the core principles in DBMS, ensures that every transaction is made according to
predefined rules and limits thus preserving the accuracy and authenticity of data kept within. The change to the
database must take it from one consistent state into another. A database transaction ensures consistency
by enforcing ACID properties (Atomicity, Consistency, Isolation, Durability), treating multiple operations as a

4|Page
single, indivisible unit of work. It guarantees that the database moves from one valid state to another,
automatically rolling back changes if any part of the transaction fails, thus preventing partial updates and violating
data integrity rules.
Key ways a transaction ensures consistency include:
 Atomicity (All or Nothing): The transaction ensures that if any part of a multi-step process fails, all prior
actions within that transaction are reverted (rolled back) to maintain the original valid state.

 Consistency Constraints: Before and after the transaction, the database enforces rules, such as FOREIGN
KEY constraints, UNIQUE indexes, and data type checks, ensuring the database remains valid.

 Isolation (Concurrent Control): Through locking mechanisms and isolation levels, transactions are
isolated from each other, preventing intermediate, invalid data from being read or written by concurrent
operations.

 Durability: Once a transaction is committed, the changes are permanent, often using write-ahead logging
to survive system failures.

For example, in a bank transfer, a transaction ensures that the deduction from one account and the deposit to
another either both succeed, or both fail, maintaining the total balance integrity.

8. What is a functional dependency in the DBMS?

Answer: A functional dependency (FD) in a Database Management System (DBMS) is a constraint that describes
the relationship between two sets of attributes in a relation.
A functional dependency is written as X → Y, where:
If two tuples (rows) have the same value for attribute set X, then they must have the same value for attribute set
Y. Here

 X is called the determinant

 Y is called the dependent
 It means X uniquely determines Y

As for example, In a table Student(Roll_No, determines the Student_Name, Dept):

If RollNo → Name, Dept, then: Each Roll_No uniquely determines the student's Name and Department

9. Write short notes on “Relational Operators”.

Answer: Relational operators in a DBMS are a set of operations from relational algebra that take one or more
relations (tables) as input and produce a new relation as output, allowing users to query and manipulate data. They
form the theoretical foundation for query languages like SQL.
The operators are generally divided into two categories:
5|Page
Fundamental Relational Operators
These five operations are considered the basic building blocks of relational algebra.
 Select (Sigma): This unary operator (operates on a single relation) filters tuples (rows) based on a
specified condition or predicate. It yields a horizontal subset of the table. The predicate can use comparison
operators (e.g., =, ≠,>,<) and logical connectors (AND, OR, NOT).
 Project (π): This unary operator extracts only the specified attributes (columns) from a relation. It yields
a vertical subset of the table, and duplicate rows are automatically eliminated in the result because a
relation is a set of unique tuples.
 Union (υ): This binary operator combines the tuples from two union-compatible relations (relations with
the same number and types of attributes) and removes duplicates.
 Set Difference (—): This binary operator returns the tuples present in the first relation but not in the
second. The relations must be union-compatible.
 Cartesian Product (X): This binary operator combines every tuple of the first relation with every tuple
of the second relation. It is often used as a step in the join operation to combine data from different tables,
resulting in all possible combinations of rows.
Additional Relational Operators:
 Join (\bowtie): This highly useful operation combines related tuples from different relations based on a
common condition, often the values of shared attributes. The general form is the Theta Join, which uses
any comparison operator (ᶿ) in its condition (e.g., = ,>,<). A common type is the Natural Join, which
joins on all common attributes and automatically eliminates duplicate columns.

 Intersection (∩): This binary operator returns only the tuples that appear in both union-compatible
relations.

 Rename (ρ): This unary operator is used to rename the output relation or its attributes, which is
particularly useful in complex queries involving multiple operations or self-joins.

 Division (÷): A more complex operation used for queries that require all tuples of one relation to be
associated with every tuple of another.

10. What is the significance of a database backup and recovery plan?

Answer: A database backup and recovery plan is critical for ensuring business continuity, preventing data loss,
and maintaining data integrity against threats like cyberattacks, hardware failures, and human error. It minimizes
downtime, protecting against financial loss and reputation damage, while ensuring compliance with legal data
protection regulations.
Key significances include:
 Business Continuity and Reduced Downtime: A robust plan allows for quick restoration of services,
ensuring operations can continue with minimal disruption during a disaster.

 Protection Against Ransomware and Cyberattacks: If data is locked by malicious attacks, a secure,
offline, or immutable backup allows you to restore systems without paying ransoms.
6|Page
  Data Loss Prevention: It ensures that in the event of hardware failure, software corruption, or human
error, critical files can be recovered to a consistent state.

 Customer Trust and Compliance: Regularly updating and testing backups ensures data integrity,
satisfying legal, industry, and regulatory requirements (e.g., GDPR), and maintaining customer
confidence.

 Cost Efficiency: While requiring an investment, a recovery plan is far less costly than the massive
financial hit caused by prolonged business downtime and lost critical information.

11. Explain Data Manipulation Language (DML) and Data Definition Language (DDL)
with examples.
Answer:
Data Definition Language(DDL):
Data Definition Language(DDL) is used to define data structures. For example: creating a table, and altering a
table are instructions in SQL. DDL is used to specify a database's structure, which includes its tables, views,
indexes, and constraints. DDL commands come in the following types: CREATE, ALTER, DROP, RENAME,
and TRUNCATE. DDL statements only modify the database's schema; they have no direct effect on the data
within the database. DDL declarations are irreversible and difficult to undo. As for example:
1. CREATE (Create a table)
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);
2. ALTER (Modify table structure)
ALTER TABLE students
ADD email VARCHAR(100);
3. DROP (Delete table)
DROP TABLE students;
4. TRUNCATE (Delete all records quickly)
TRUNCATE TABLE students;
So, it could be stated that, DDL deals with the structure/schema, not the actual data content.

Data Manipulation Language (DML):

DML is a Data Manipulation Language that is used to manipulate data itself. For example: insert, update, and
delete are instructions in SQL. Inserting, updating, removing, and retrieving data from a database are all
possible with DML. DML commands come in the following types: SELECT, INSERT, UPDATE, DELETE,
and MERGE. DML statements have a direct impact on the database's data. In the event of an error, data can be
recovered thanks to the reversibility of DML statements. As for example:
7|Page
1. INSERT (Add a record)
INSERT INTO students (id, name, age)
VALUES (1, 'Rahim', 20);
2. SELECT (View data)
SELECT * FROM students;
3. UPDATE (Modify data)
UPDATE students
SET age = 21
WHERE id = 1;
4. DELETE (Remove data)
DELETE FROM students
WHERE id = 1;
So it could be said that, DML deals only with data, not the structure of the database.

12. Explain the concept of data integrity and how it is maintained in a relational database.
Answer:
Data integrity in a relational database means ensuring that data accuracy, consistency, and reliability throughout
its lifecycle, preventing unauthorized or accidental changes. It is maintained through constraints (primary keys,
foreign keys, check constraints) and transactional rules (ACID properties) that enforce data rules at the database
level. It prevents errors, duplication, and corruption so that the database remains trustworthy.

Data integrity in Relational database through few mechanisms. These are described below:
Relational Database Management Systems (RDBMS) use four primary types of integrity constraints:

1. Entity Integrity: Uses primary keys to ensure each row in a table is uniquely identifiable and not null,
preventing duplicate records.

2. Referential Integrity: Maintains relationships between tables using foreign keys. It ensures that a child
record cannot exist without a parent record (e.g., an order cannot exist without a customer).

3. Domain Integrity: Defines valid values for a column, enforcing data types, formats, ranges, and constraints
(e.g., ensuring an age column is always positive).

4. User-Defined Integrity: Enforces specific business rules created by users that do not fall under other
categories.

5. ACID Transactions: Ensures that transactions are Atomic, Consistent, Isolated, and Durable, preventing
data corruption during concurrent operations.

8|Page
 6. Data Validation: Checks data against predefined criteria (like constraints) before allowing insertion or
updates.

7. Normalization: Organizes database structures to reduce redundancy and anomalies.

So, it is revealed that, data integrity ensures database reliability and compliance. Without it, data becomes
untrustworthy and can lead to faulty decisions.

13. Difference between OLAP and OLTP databases.

Answer: Online Analytical Processing (OLAP) refers to software tools used for the analysis of data in business
decision-making processes. Online Transaction Processing, commonly known as OLTP, is a data processing
approach emphasizing real-time execution of transactions. Difference between OLAP & OLTP are as follows:
Category OLAP (Online Analytical Processing) OLTP (Online Transaction Processing)
Data Source Historical data from multiple databases. Current operational data.
Purpose Used for analysis and decision-making. Used for day-to-day transactions.
Method Used Uses a data warehouse. Uses a standard DBMS.
Normalization Tables are not normalized. Tables are normalized (3NF).
Query Type Complex, read-heavy queries (slow). Simple, read/write queries (fast).
Data Volume Large (TB–PB). Small (MB–GB).
Update Frequency Updated periodically in batches. Updated frequently by users.
Backup &
Periodic backup. Continuous and rigorous backup.
Recovery
Used by analysts, managers and
Users Used by clerks and operational staff.
executives.
Application-oriented (operation-
Focus Subject-oriented (analysis-focused).
focused).
Example ATM withdrawal, Online shopping. Annual sales report, Market trends.

14. Write the names of all types of queries with example.

Answer: A database query is a structured request for information or action sent to a database. Unlike casual
questions, a query must follow strict syntax so the database can interpret and process it correctly. When executed,
it either returns the requested data or performs the specified operation. At its core, a query is code used to interact
with a database. It allows you to ask precise questions or issue commands that the database understands. The
result is either a dataset matching your criteria or a confirmation that the requested changes have been applied.
Database queries can be categorized based on their purpose, whether they view, modify, or analyze data. These
are discussed below:

9|Page
1. Select queries
Select queries are the most basic and widely used type. They retrieve data from a database and present it as a
result set without altering the underlying records. Example: SELECT * FROM Products;

2. Action queries
Action queries modify data or the database structure and are executed with care. Common subtypes include:
 Update queries: Change existing records (e.g., give all employees a 5% raise).
 Append queries: Add new records from one table to another.
 Delete queries: Remove records permanently based on criteria.
 Make-table queries: Create a new table from existing data.
Example: DELETE FROM Orders WHERE Status = 'Cancelled';

3. Parameter queries
Parameter queries are dynamic. Instead of using fixed criteria, they prompt the user for input before running.
Example: Searching for a student by entering their specific Roll Number.

4. Aggregate queries
Aggregate (summary) queries perform calculations on groups of records to extract insights:
 Sum: Total sales or revenue.
 Count: Number of customers or transactions.
 Average: Mean value of orders or scores.
 Min/Max: Lowest or highest values in a dataset.
Example: SELECT Name FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

5. Crosstab queries
Crosstab queries restructure data into a matrix format to analyze relationships between variables. Similar to an
Excel PivotTable, they display data in rows and columns for easier comparison and reporting. Example: Like
Summery Table.

10 | P a g e
15. Explain UPDATE and INSERT command with example.

UPDATE statement in the PL/SQL is used to the modify the existing records in table. It is allowed us to
specify the table name, columns to updated new values for these columns and an optional conditional to the
filter which rows are to be updated.
Syntax for UPDATE Statement
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Explanation:
 table_name is the name of the table, which we want to update.
 SET column1 = value1, column2 = value2, ... are specified the columns to the updated with their new
values.
 WHERE condition is specify which rows are to be update. if we not use where condition all rows in
the table will be update.
Examples on UPDATE Statement
Example 1: Let's update the last name of the students as Anderson whose student_id is 3.
UPDATE students
SET last_name = 'Anderson'
WHERE student_id = 3;
Output:
student_id first_name last_name

1 John Doe

2 Jane Smith

3 Michael Anderson

4 Emily Williams

INSERT statement in the PL/SQL is used to add the new records or rows of data into the table. It allows u to
specify the table name, columns where the data will be inserted and the corresponding values to be inserted
into the columns.
Syntax for INSERT Statement
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
Explanation:
 table_name is the name of the table, which we want to insert the data.
 (column1, column2, ....) are optional lists of the columns in table. If specified, you need to be
provide the values for those columns in the same order.
 VALUES (value1, value2, ...) are values to be inserted into specified columns. These values must
match the data types of the provided columns.

11 | P a g e
Examples on INSERT Statement

Example 1: Adding Data to the students Table

Let' insert some data into the students table.
INSERT INTO students (student_id, first_name, last_name)
VALUES (1, 'John', 'Doe');

INSERT INTO students (student_id, first_name, last_name)

VALUES (2, 'Jane', 'Smith');
Output:
student_id first_name last_name

1 John Doe

2 Jane Smith

16. Concept of Deadlock. How can it be prevented or resolved?

Answer: A Deadlock in a DBMS refers to the situation in which two or more transactions are in a state such that
none of them can proceed because each of them is waiting for the other to release some resource. In other words,
A Deadlock is a situation where two or more transactions are waiting for each other to release locks, and none of
them can proceed. It is a "freeze" in the system. Deadlock primarily arises in multi-transactional systems where
multiple processes are running concurrently.

Preventing Deadlocks
Deadlock prevention focuses on ensuring at least one of the conditions above never occurs.
 Eliminate Mutual Exclusion: Make resources shareable (e.g., read-only files), though this is
impossible for non-sharable resources like printers.

 Eliminate Hold and Wait: Require processes to request all resources at once before execution,
or force them to release held resources before requesting new ones.

 Eliminate No Preemption: If a process holding resources requests another that cannot be

immediately allocated, all currently held resources are forcibly preempted (released).

 Eliminate Circular Wait: Impose a unique, ordered numbering system on all resource types
and require processes to request resources in increasing order.

Resolving Deadlocks
When prevention is not feasible, systems use detection and recovery methods:
 Detection Algorithms: The system periodically checks for cycles in the Resource Allocation
Graph (RAG).

12 | P a g e
  Process Termination: Abort one or all deadlocked processes. This can be done by killing all
processes involved or terminating them one by one until the cycle breaks.

 Resource Preemption: Forcibly take resources away from one or more processes and give
them to others until the deadlock is resolved.

 Rollback: Revert a process to a previous "safe state" and restart it, which prevents permanent
data corruption.
Stopping Deadlocks before they occur is often better than recovering from them. Deadlock avoidance is an
example of careful resource allocation.

7. Describe Generalization and Aggregation with example.

Answer:
Generalization and Aggregation are ER modeling techniques used to manage complexity. Generalization is a
bottom-up approach that extracts common attributes from multiple entities to form a higher-level "super-class"
(e.g., Student/Teacher _Person). Aggregation treats a relationship between entities as a higher-level entity,
allowing relationships to connect to other relationships.
Generalization:
A generalization is a Process of extracting common properties from a set of entities and creating a generalized
entity from it. It is also defined as a bottom-up approach in which two lower-level entities combine to form a
higher-level entity. In generalization, the higher-level entity can also combine with other lower-level entities to
make further higher-level entities. It’s more like a Superclass and Subclass system, but the only difference is the
approach, which is bottom-up. Hence, entities are combined to form a more generalized entity, in other words,
sub-classes are combined to form a super-class. For example, Saving and Current account types entities can be
generalized and an entity with name Account can be created, which covers both.

Aggregation:
Aggregation is an abstraction process where a relationship between two entities is treated as a single, high-level
entity. It is used when an entity needs to relate to a relationship rather than just another entity. In other words,

13 | P a g e
Aggregation is a process when relation between two entities is treated as a single entity. Aggregation in
database management systems helps at ensuring that all entities are utilized within the system. Without this
operation, the trivial entities may become inoperative.
Example: A "Center" and a "Course" have a relationship. If we want to track "Inquiry" for that specific Center-
Course pair, we aggregate Center and Course into one unit.

8. Types of Normal Forms and Conversion from 2NF to 3NF.

Answer: Normal forms are a set of progressive rules (or design checkpoints) for relational schemas that reduce
redundancy and prevent data anomalies. Each normal form - 1NF, 2NF, 3NF, BCNF, 4NF, 5NF - is stricter than
the previous one: meeting a higher normal form implies the lower ones are satisfied. Few benefits of using normal
form like as reduce duplicate data and wasted storage, prevent insert, update, and delete anomalies, improve data
consistency and integrity, make the schema easier to maintain and evolve. Types of normal forms are explained
below:
1. First Normal Form (1NF): Eliminating Duplicate Records
A table is in 1NF if it satisfies the following conditions:
 All columns contain atomic values (i.e., indivisible values).
 Each row is unique (i.e., no duplicate rows).
 Each column has a unique name.
 The order in which data is stored does not matter.
Example of 1NF Violation: If a table has a column "Phone Numbers" that stores multiple phone numbers in a
single cell, it violates 1NF. To bring it into 1NF, you need to separate phone numbers into individual rows.
2. Second Normal Form (2NF): Eliminating Partial Dependency
A relation is in 2NF if it satisfies the conditions of 1NF and additionally. No partial dependency exists, meaning
every non-prime attribute (non-key attribute) must depend on the entire primary key, not just a part of it.
Example: For a composite key (StudentID, CourseID), if the "StudentName" depends only on "StudentID" and
not on the entire key, it violates 2NF. To normalize, move StudentName into a separate table where it depends
only on "StudentID".
14 | P a g e
3. Third Normal Form (3NF): Eliminating Transitive Dependency
A relation is in 3NF if it satisfies 2NF and additionally, there are no transitive dependencies. In simpler terms,
non-prime attributes should not depend on other non-prime attributes.
Example: Consider a table with (StudentID, CourseID, Instructor). If Instructor depends on "CourseID", and
"CourseID" depends on "StudentID", then Instructor indirectly depends on "StudentID", which violates 3NF. To
resolve this, place Instructor in a separate table linked by "CourseID".

4. Boyce-Codd Normal Form (BCNF): The Strongest Form of 3NF

BCNF is a stricter version of 3NF where for every non-trivial functional dependency (X → Y), X must be
a superkey (a unique identifier for a record in the table).
Example: If a table has a dependency (StudentID, CourseID) → Instructor, but neither "StudentID" nor
"CourseID" is a superkey, then it violates BCNF. To bring it into BCNF, decompose the table so that each
determinant is a candidate key.

5. Fourth Normal Form (4NF): Removing Multi-Valued Dependencies

A table is in 4NF if it is in BCNF and has no multi-valued dependencies. A multi-valued dependency occurs when
one attribute determines another, and both attributes are independent of all other attributes in the table.
Example: Consider a table where (StudentID, Language, Hobby) are attributes. If a student can have multiple
hobbies and languages, a multi-valued dependency exists. To resolve this, split the table into separate tables for
Languages and Hobbies.

6. Fifth Normal Form (5NF): Eliminating Join Dependency

5NF is achieved when a table is in 4NF and all join dependencies are removed. This form ensures that every table
is fully decomposed into smaller tables that are logically connected without losing information.
Example: If a table contains (StudentID, Course, Instructor) and there is a dependency where all combinations of
these columns are needed for a specific relationship, you would split them into smaller tables to remove
redundancy.

Conversion from 2NF to 3NF:

Database normalization is a very important concept in database design. To convert a table from Second Normal
Form (2NF) to Third Normal Form (3NF), must eliminate all transitive dependencies. A transitive
dependency occurs when a non-key attribute depends on another non-key attribute, rather than depending directly
on the primary key.
Final Rule for 3NF
A relation R is in 3NF if, for every non-trivial functional dependency X>Y:
1. X is a superkey, OR
2. Y is a prime attribute (part of a candidate key).

15 | P a g e
Step-by-Step Conversion Process
1. Identify Transitive Dependencies
Check for functional dependencies where a non-prime attribute (not part of the key) determines another
non-prime attribute.
2. Separate the Dependent Attributes
Remove the attributes that are transitively dependent (the "right side" of the dependency) from the
original table.
3. Create a New Table
Place the removed attributes into a new table. The determinant (the attribute they depended on)
becomes the Primary Key of this new table.
4. Establish a Relationship
Keep a copy of the determinant in the original table to act as a Foreign Key, linking the two tables
together.

Example: Employee Department Data

2NF Table (Employee_Dept)
 Primary Key: Emp_ID
 Dependencies: Emp_ID
Emp_Name, Dept_ID, Dept_Name
 Transitive Dependency: Emp_ID
Dept_ID and Dept_ID
Dept_Name. (Because Dept_Name depends on Dept_ID, which is not a key).

Emp_ID (PK) Emp_Name Dept_ID Dept_Name

101 Alice D01 HR

102 Bob D02 IT

103 Charlie D01 HR

Limitation: If Department D01 changes its name, we must update multiple rows (Update Anomaly).

16 | P a g e
Conversion to 3NF
To achieve 3NF, move the transitive dependency (Dept_ID _Dept_Name) to a new table.
1. Employee Table

Emp_ID (PK) Emp_Name Dept_ID (FK)

101 Alice D01

102 Bob D02

103 Charlie D01

2. Department Table

Dept_ID (PK) Dept_Name

D01 HR

D02 IT

17 | P a g e