III unit :

Unit III: Relational Database Design: Features of Good Relational

Designs, Atomic Domains and First Normal Form, Functional Dependencies,
Closure set of Functional dependencies, Procedure for Computing F+, Boyce
Codd Normal form, BCNF Decomposition Algorithm, Third Normal Form,
Third Normal Form Decomposition Algorithm
Transactions: Transaction Concept, A Simple Transaction Model, Storage
Structure, Transaction Atomicity and Durability, Serializability.

A good relational design means the database structure is efficient, consistent, and easy to maintain.
Key features are usually discussed in terms of data integrity, minimal redundancy, and ease of use.
Here are the main features of a good relational design:

1. Minimal Data Redundancy

 Avoids storing the same data in multiple places.
 Reduces storage wastage.
 Prevents update anomalies (inconsistencies when data is modified).
2. Elimination of Anomalies
A good design avoids:
 Insertion anomaly – inability to insert data without unwanted side effects.
 Deletion anomaly – loss of important data when deleting records.
 Update anomaly – inconsistent data after updates.
This is achieved through normalization.
3. Proper Normalization
 Tables are structured using normal forms (1NF, 2NF, 3NF, BCNF).
 Ensures attributes depend on the key, the whole key, and nothing but the key.
 Improves consistency and clarity.
4. Data Integrity
 Entity integrity: Primary keys are unique and not null.
 Referential integrity: Foreign keys correctly reference primary keys.
 Domain integrity: Attribute values follow defined data types and constraints.
5. Clear and Meaningful Keys
 Each relation has a well-defined primary key.
 Keys uniquely identify tuples (rows).
  Supports efficient indexing and searching.
6. Logical Structure and Simplicity
 Relations represent real-world entities or relationships clearly.
 Easy to understand and use by developers and users.
 Avoids overly complex table structures.
7. Flexibility and Scalability
 Design allows easy addition of new attributes or relations.
 Minimal changes required when business rules evolve.
8. Efficient Query Performance
 Well-designed relations reduce the need for complex joins.
 Supports indexing strategies for faster data access.
9. Consistent Naming Conventions
 Tables and attributes have clear, meaningful names.
 Improves readability and maintainability.
10. Support for Security and Access Control
 Sensitive data is separated where possible.
 Easier to apply permissions at table or column level.

1. Minimal Data Redundancy (with example)

Meaning:
Minimal data redundancy means avoiding unnecessary repetition of the same data in multiple
tables. Each piece of information should be stored only once, and other tables should refer to it using
keys.
Bad Design (High Redundancy)
STUDENT table

StudentID StudentName DeptName DeptLocation

101 Rahul CSE Block A

102 Ananya CSE Block A

103 Kiran ECE Block B

Here:
 DeptName and DeptLocation are repeated for every student in the same department.
 If the CSE department location changes, it must be updated in multiple rows.
 This can cause inconsistencies if some rows are not updated.
Good Design (Minimal Redundancy)
STUDENT table

StudentID StudentName DeptID

101 Rahul D1

102 Ananya D1

103 Kiran D2

DEPARTMENT table

DeptID DeptName DeptLocation

D1 CSE Block A

D2 ECE Block B

Here:
 Department details are stored only once in the DEPARTMENT table.
 STUDENT table refers to the department using DeptID.
 If a department’s location changes, it is updated in one place only.

Benefits
 Saves storage space.
 Prevents update anomalies.
 Maintains data consistency.
 Makes the database easier to maintain.

In one line (for exams):

Minimal data redundancy ensures that each data item is stored only once, reducing inconsistency and
update problems.
one more clear example of Minimal Data Redundancy-
Minimal Data Redundancy – Company Example
Poor Design (Redundant Data)
EMPLOYEE table

EmpID EmpName DeptName DeptPhone

E01 Ravi HR 044-2345

E02 Meena HR 044-2345

EmpID EmpName DeptName DeptPhone

E03 Suresh IT 044-6789

Problems
 DeptPhone is repeated for every employee in the same department.
 If HR phone number changes, it must be updated in multiple rows.
 Missing an update causes inconsistent data.
Good Design (Minimal Redundancy)
EMPLOYEE table

EmpID EmpName DeptID

E01 Ravi D10

E02 Meena D10

E03 Suresh D20

DEPARTMENT table

DeptID DeptName DeptPhone

D10 HR 044-2345

D20 IT 044-6789

Why This Design Is Better

 Department details are stored once.
 EMPLOYEE table references the department using DeptID.
 Updates happen in one place only.

2. Elimination of Anomalies (in Good Relational Design)

Meaning:
Anomalies are problems that occur in a poorly designed database due to data redundancy.
A good relational design eliminates these anomalies by properly normalizing the tables.

Example Table (Poor Design)

STUDENT_COURSE table

StudentID StudentName CourseID CourseName Faculty

1 Aman C1 DBMS Dr. Rao

 StudentID StudentName CourseID CourseName Faculty

2 Neha C1 DBMS Dr. Rao

3 Ravi C2 OS Dr. Mehta

There are three main types of anomalies:

1. Insertion Anomaly
Problem:
You cannot insert course information unless at least one student enrolls.
➡ Example:
You want to add a new course AI taught by Dr. Kumar, but no student has enrolled yet.
You can’t insert it without student data.
2. Deletion Anomaly
Problem:
Deleting a record causes loss of important information.
➡ Example:
If student Ravi (Course OS) is deleted,
course OS and its faculty details are also lost, even though the course still exists.
3. Update Anomaly
Problem:
Updating data in multiple rows can cause inconsistency.
➡ Example:
If Dr. Rao’s name changes to Dr. Raghav,
it must be updated in all DBMS rows.
If one row is missed → inconsistent data.

Good Design (Eliminates Anomalies)

STUDENT table

StudentID StudentName

1 Aman

2 Neha

3 Ravi

COURSE table

CourseID CourseName Faculty

C1 DBMS Dr. Rao

CourseID CourseName Faculty

C2 OS Dr. Mehta

ENROLLMENT table

StudentID CourseID

1 C1

2 C1

3 C2

Why Anomalies Are Eliminated

 Courses can be added without students → no insertion anomaly.
 Deleting a student does not delete course details.
 Faculty name updated once only → no update anomaly.

Atomic Domains:
An atomic domain refers to a set of values where each individual value is indivisible (atomic) with
respect to the relational model. It ensures that columns (attributes) in a table contain only single, non-
composite, and non-multi-valued data points, fulfilling the requirement for First Normal Form (1NF).
 Examples:
o Atomic: A Gender column containing 'M' or 'F', or an Age column with numbers.

o Non-Atomic (Composite): A full address column (123 Main St, Springfield) is not
atomic; it should be broken into Street, City, State.
o Non-Atomic (Multi-valued): A Phone column storing multiple numbers (555-1212,
555-1313).

What is normalization?
Normalization is a process used in database design to organize data efficiently.

Simple definition
👉 Normalization is the process of structuring a database to reduce data redundancy and avoid
data anomalies.
👉 Normalization = organizing data to reduce redundancy and maintain integrity
Why normalization is needed
Normalization helps to:
 Remove duplicate data
 Improve data consistency
 Prevent insert, update, and delete anomalies
 Make the database easier to maintain

Normal Forms (levels of normalization)

 1NF – No multi-valued attributes-
 2NF – No partial dependency
 3NF – No transitive dependency
 BCNF – Stronger version of 3NF

1NF (First Normal Form) is the first level of database normalization.

Definition (simple)
A table is in 1NF if:
1. Each field contains atomic (indivisible) values
2. There are no repeating groups or multi-valued attributes
3. Each record can be uniquely identified (primary key)

❌ example Table NOT in 1NF

Because one column has multiple values.

StudentID Name Subjects

1 Alice Math, Physics

2 Bob Chemistry

Problem:
Subjects contains more than one value in a single cell.

✅ Table IN 1NF
Split multi-valued attributes into separate rows.
StudentID Name Subject

1 Alice Math

1 Alice Physics

2 Bob Chemistry

Now:
 Each field has one value
 No repeating groups
 Table follows 1NF

One-line summary
👉 1NF = no multi-valued columns, only single (atomic) values
2NF (Second Normal Form) builds on 1NF.

2NF (Second Normal Form) Definition (easy)

A table is in 2NF if:
1. It is already in 1NF
2. No partial dependency exists
→ Non-key attributes must depend on the entire primary key, not just part of it
⚠️Partial dependency happens only when the primary key is composite (more than one column).

❌ Table in 1NF but NOT in 2NF

Primary Key: (StudentID, CourseID)

StudentID CourseID StudentName CourseName

1 C101 Alice Math

1 C102 Alice Physics

2 C101 Bob Math

Problems:
 StudentName depends only on StudentID
 CourseName depends only on CourseID
 They do not depend on the full composite key
👉 This violates 2NF
✅ Convert to 2NF
Split the table to remove partial dependencies.
Student Table

StudentID StudentName

1 Alice

2 Bob

Course Table

CourseID CourseName

C101 Math

C102 Physics

Enrollment Table

StudentID CourseID

1 C101

1 C102

2 C101

Now:
 Every non-key attribute depends on the whole primary key
 No partial dependency
 Table is in 2NF

One-line summary
👉 2NF = 1NF + no partial dependency

One more slightly complex, exam-friendly 2NF example with multiple partial dependencies.

❌ Table in 1NF but NOT in 2NF

Composite Primary Key: (OrderID, ProductID)

OrderID ProductID OrderDate CustomerName ProductName Price

O1 P101 2024-01-10 Alice Laptop 800

O1 P102 2024-01-10 Alice Mouse 20

O2 P101 2024-01-12 Bob Laptop 800

OrderID ProductID OrderDate CustomerName ProductName Price

Identify the dependencies

 OrderDate, CustomerName → depend only on OrderID
 ProductName, Price → depend only on ProductID
 Primary Key = (OrderID + ProductID)
👉 These are partial dependencies, so the table violates 2NF.

✅ Convert to 2NF (Decomposition)

Order Table

OrderID OrderDate CustomerName

O1 2024-01-10 Alice

O2 2024-01-12 Bob

Product Table

ProductID ProductName Price

P101 Laptop 800

P102 Mouse 20

Order_Product Table

OrderID ProductID

O1 P101

O1 P102

O2 P101

✅ Why this is now in 2NF

 Every table is in 1NF
 All non-key attributes depend on the entire primary key
 No partial dependency exists
Quick exam tip 🧠
If you see:
 Composite key
 Attributes depending on only one part
👉 It’s a 2NF violation

Few more examples on 2NF:

Example 1: ORDER Table (Not in 2NF) Convert to 2NF

ORDER_DETAILS 1️⃣ ORDER
OrderID ProductID OrderDate ProductName Price OrderID OrderDate
Primary Key: 2️⃣ PRODUCT
(OrderID, ProductID) → composite key ProductID ProductName Price
Functional Dependencies: 3️⃣ ORDER_PRODUCT
 OrderID → OrderDate
OrderID ProductID
 ProductID → ProductName, Price
✅ Now all non-key attributes
🚫 Partial Dependency
depend on the whole key
 OrderDate depends only on OrderID
➡️2NF achieved
 ProductName, Price depend only on ProductID
❌ Table is not in 2NF

Example 2: EMPLOYEE_PROJECT (Not in 2NF) Convert to 2NF

EMP_PROJECT 1️⃣ EMPLOYEE
EmpID ProjectID EmpName ProjectName Hours EmpID EmpName
Primary Key: 2️⃣ PROJECT
(EmpID, ProjectID) ProjectID ProjectName
Functional Dependencies: 3️⃣ WORKS_ON
 EmpID → EmpName EmpID ProjectID Hours
 ProjectID → ProjectName
✅ Fully dependent on whole key
 (EmpID, ProjectID) → Hours
➡️2NF
🚫 Partial dependencies:
 EmpName depends only on EmpID
 ProjectName depends only on ProjectID
❌ Not in 2NF

Example 3: SALES Table (Already in 2NF)

SALES
SaleID ItemID Quantity
Primary Key:
(SaleID, ItemID)
Functional Dependency:
 (SaleID, ItemID) → Quantity
✅ No attribute depends on part of the key
➡️This table IS in 2NF

Example 4: COLLEGE Table (Not in 2NF) Convert to 2NF

COLLEGE STUDENT
StudentID SubjectID StudentName SubjectName Marks | StudentID | StudentName |
Primary Key: SUBJECT
(StudentID, SubjectID) | SubjectID | SubjectName |
 Functional Dependencies: RESULT
 StudentID → StudentName | StudentID | SubjectID | Marks |
 SubjectID → SubjectName ✅ In 2NF
 (StudentID, SubjectID) → Marks
🚫 Partial dependency exists
❌ Not in 2NF

Super-Quick Rule to Remember 🧠

 Single primary key? → Automatically in 2NF
 Composite key? → Check for partial dependency

Functional Dependency (FD)

1. What is a Functional Dependency?
In Database Management Systems (DBMS), a Functional Dependency describes a relationship
between attributes in a table.
👉 It tells us which attribute determines another attribute.
2. Definition
A functional dependency is written as: X → Y

This means:
If two rows have the same value of X, they must have the same value of Y
📌 X = Determinant
📌 Y = Dependent attribute

3. Simple Example
Consider a table STUDENT:

StudentID StudentName Department

101 Alice CS

102 Bob IT

103 Charlie CS

Here:
StudentID → StudentName
StudentID → Department
Why?
Because StudentID uniquely identifies both StudentName and Department.

4. Important Rule (Very Exam-Oriented ⭐)

If X → Y, then X functionally determines Y
Meaning:
 One value of X → only one value of Y
 But one value of Y → many values of X (not guaranteed)

5. Types of Functional Dependencies

(a) Trivial Functional Dependency
If Y is a subset of X:
{StudentID, StudentName} → StudentID
✔ Always true
(b) Non-Trivial Functional Dependency
If Y is not a subset of X:
StudentID → StudentName
(c) Fully Functional Dependency
Y depends on whole X, not part of it.
Example:
(StudentID, CourseID) → Grade
Grade depends on both, not just one.

(d) Partial Dependency

Y depends on part of a composite key.
Example:
(StudentID, CourseID) → StudentName
StudentName depends only on StudentID ❌
(e) Transitive Dependency
If:
A→B&B→C
Then:
 A→C
StudentID → DepartmentID and DepartmentID → DepartmentName
So:
StudentID → DepartmentName

6. Why Functional Dependencies are Important?

Functional Dependencies help us:
✔ Find keys
✔ Remove data redundancy
✔ Avoid update, insert, delete anomalies
✔ Normalize tables (1NF, 2NF, 3NF, BCNF)

7. One-Line Summary (Good for Revision)

Functional Dependency shows how one attribute uniquely determines another attribute in a
relation.

Attribute Closure (X⁺)

1. What is Attribute Closure?

The attribute closure of a set of attributes X (written as X⁺) is the set of all attributes that can be
functionally determined from X, using a given set of functional dependencies.

📌 In simple words:

X⁺ = all attributes we can find starting from X

2. Why Attribute Closure is Important?

We use attribute closure to:

✔ Find candidate keys
✔ Check if X → Y is valid
✔ Test normal forms (2NF, 3NF, BCNF)

3. Attribute Closure Algorithm (Exam Standard ⭐)

To find X⁺:

1. Start with

2. X⁺ = X

3. Look at each FD A → B

4. If A ⊆ X⁺, then add B to X⁺

 5. Repeat until no new attributes can be added

Example (Very Important)

Given Relation: R(A, B, C, D, E)

Given Functional Dependencies: F = { A → B, B → C, C → D }

Find: A⁺

Step-by-Step Solution :

Step 1: Start

A⁺ = {A}

Step 2: Apply FDs

A ∈ A⁺ ✔
 A→B

Add B

 A⁺ = {A, B}

B ∈ A⁺ ✔
 B→C

Add C

 A⁺ = {A, B, C}

C ∈ A⁺ ✔
 C→D

Add D

 A⁺ = {A, B, C, D}

Step 3: Stop

No FD can add E, so stop.

✅ Final Answer:

A⁺ = {A, B, C, D}

4. Checking Candidate Key Using Closure

in this example, if

A⁺ = {A, B, C, D, E} → that means A is a candidate key (R{A, B, C, D, E} )

But here: A⁺ ≠ R → that means A is NOT a candidate key

Another Quick Example (Composite Attribute)

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

F = { AB → C, C → D }

Find: (AB)⁺

Sol : Step 1:

(AB)⁺ = {A, B}

Step 2:

 AB → C ✔

 {A, B, C}

 C→D✔

 {A, B, C, D}

Final:

(AB)⁺ = {A, B, C, D} therefore (AB)⁺ is a candidate key.

5. Key Exam Tip 🧠

If X⁺ contains all attributes of the relation, then X is a candidate/super key.

6. One-Line Exam Answer ✍️

Attribute closure (X⁺) is the set of all attributes that can be functionally determined from X using
the given functional dependencies.

 Candidate key using attribute closure

 Normalization (2NF, 3NF, BCNF) step-by-step

4. Another Example (Composite Candidate Key)

Given: R(A, B, C, D, E)

F = { AB → C, C → D } find the composite / super key.

Step 1: Attributes on RHS

RHS = {C, D}

So: left side : A, B, E

Step 2: Try (A, B, E)

Find (ABE)⁺:

ABE⁺ = {A, B, E}

AB → C → add C
C → D → add D

ABE⁺ = {A, B, C, D, E} => ABE is a super key.

Step 3: Check Minimality

 (AB)⁺ = {A, B, C, D} ❌ missing E

 (AE)⁺ = {A, E} ❌

 (BE)⁺ = {B, E} ❌

✅ Final Answer:

Candidate Key = {A, B, E}

Few more examples to find the candidate key:

5. Exam Tip 🧠

Always prove a candidate key by showing its closure equals the full relation.

6. One-Line Exam Definition ✍️

A candidate key is a minimal set of attributes whose attribute closure contains all attributes of the
relation.

Now you can check for :

 Multiple candidate keys

 Primary key selection

 BCNF checking using closures

Armstrong’s Axioms (Functional Dependency Rules)

Def: Armstrong’s axioms are a set of inference rules (Reflexivity, Augmentation, and Transitivity)
used to derive all functional dependencies from a given set of functional dependencies.

Armstrong’s axioms are a set of inference rules used to derive all functional dependencies from a
given set of functional dependencies.

The Three Armstrong’s Axioms

1️⃣ Reflexivity Rule

If Y is a subset of X, then: X → Y
🔹 Meaning: A set of attributes always determines its own subsets.

Example: AB→ A , ABC → BC

2️⃣ Augmentation Rule

If: X → Y
then for any attribute set Z: XZ →YZ
🔹 Meaning: Adding the same attributes to both sides keeps the dependency valid.

Example:
If: A → B then: AC → BC

3️⃣ Transitivity Rule

If: X → Y and Y → Z then: X →Z

🔹 Meaning: Dependency can be passed through another dependency.

Example: A → B , B→ C ⇒ A → C

Common Derived Rules (from Armstrong’s axioms)

These are not axioms, but are frequently used because they save time:

🔸 Union Rule : If: X → Y and X → Z then: X → YZ

🔸 Decomposition Rule: If: X → YZ then: X → Y and X → Z
🔸 Pseudotransitivity Rule: If: X → Y and WY → Z then: WX → Z

Why Armstrong’s Axioms Matter

They are used to:
+¿¿
 Compute closure F

 Find candidate keys

 Check equivalence of FD sets

 Normalize relations (3NF, BCNF)

Procedure for Computing F+

The standard procedure for computing F+ :

What is (F+)?
F+ is the set of all functional dependencies that can be logically inferred from a given set of
functional dependencies (F), using Armstrong’s axioms.

Procedure to Compute Closure F+:

Step 1: Start with the given FD set
Let F = { X 1→ Y1 ; X2 →Y2; …… }

Initialize: F+ = F
Step 2: Apply Armstrong’s Axioms repeatedly
Step 3: Add newly derived FDs
Each time you infer a new FD, add it to F+.
Continue applying the axioms until no more new FDs can be generated.
Step 4: Stop when closure stabilizes
When repeated application of the axioms produces no additional dependencies, the set
you have is F+

Practical Note (Very Important)

In exams and design problems, we do not list all of (F+) (it can be huge).
Instead, we usually compute:
 Attribute closure (X+) → to find keys
 Minimal cover
 Check normalization (3NF / BCNF)
 +¿¿
Quick Example to calculate F

Given: F = { A → B,; B →C }

Using Transitivity: A → C
So: F+ = { A →B,; B →C,; A →C,; plus all trivial FDs }

One-Line Exam Definition

Closure F+ is the set of all functional dependencies that can be derived from (F) using
Armstrong’s axioms.
Third Normal Form (3NF)
Why do we need 3NF?
The main purpose of Third Normal Form is to:
 Reduce data redundancy
 Eliminate update, insert, and delete anomalies
 Ensure data consistency

Formal Definition
A relation (table) is said to be in Third Normal Form (3NF) if:
1. The relation is already in Second Normal Form (2NF), and
2. There is no transitive dependency in the relation

What is Transitive Dependency?

A transitive dependency occurs when:
 A non-key attribute depends on another non-key attribute
 Instead of depending directly on the primary key
In notation:
A→B
B→C
Therefore, A → C
If A is the primary key and C is a non-key attribute, this violates 3NF.

Example (Not in 3NF)

Consider the following table:
STUDENT

StudentID StudentName DeptID DeptName

1 John D10 Computer Science

2 Mary D20 Mathematics

Functional Dependencies
 StudentID → StudentName, DeptID
 DeptID → DeptName
Here:
 DeptName depends on DeptID
 DeptID depends on StudentID
So:
StudentID → DeptName (Transitive dependency)
❌ This table is not in Third Normal Form

Conversion to 3NF
To remove the transitive dependency, we decompose the table.

STUDENT

StudentID StudentName DeptID

DEPARTMENT

DeptID DeptName

Why is this in 3NF now?

 Every non-key attribute depends directly on the primary key
 No non-key attribute depends on another non-key attribute
 No transitive dependency exists
✅ Hence, the tables are in Third Normal Form

Important Statement to Remember (Exam Point)

A table is in Third Normal Form if every non-key attribute is non-transitively dependent on the
primary key.

Summary (Blackboard Recap)

 1NF → Remove repeating groups
 2NF → Remove partial dependency
 3NF → Remove transitive dependency

Real-Life Analogy for Third Normal Form (3NF)

Scenario: College ID Cards
Imagine a college issues ID cards to students.
Each ID card contains:
 Student Roll Number
 Student Name
 Department Code
 Department Name
So the record looks like this:
Roll No → Student Name → Department Code → Department Name

What’s the problem here?

 The department name is written on every student’s ID card
 Many students belong to the same department
 If the department name changes (say Computer Science becomes Computer Engineering),
👉 you must update hundreds of ID cards
This is exactly what we call a transitive dependency problem.

Translate to Database Terms

 Roll No = Primary Key
 Department Name does NOT depend directly on Roll No
 It depends on Department Code
So:
Roll No → Department Code
Department Code → Department Name
❌ Not in 3NF

How do we fix it? (3NF Solution)

Instead of writing everything on the ID card:
Student Record
 Roll No
 Student Name
 Department Code
Department Record (Office Register)
 Department Code
 Department Name
Now:
 Each student card stores only what belongs to the student
 Department details are stored once
 If the department name changes, update it in one place only
✅ This follows Third Normal Form

One-Line Analogy for Students

Don’t store department details in student records—store them in a department file and just refer
to them.

Classroom Takeaway
 3NF = No indirect dependency
 A non-key fact should not depend on another non-key fact
 Store information where it actually belongs
Some more examples:

2. Hospital & Doctor 3NF Solution:

Situation:  Patient Table: Patient ID, Name,
Patient records include: Doctor ID
 Patient ID  Doctor Table: Doctor ID, Name,
 Patient Name Specialization
 Doctor ID
 Doctor Name
 Doctor Specialization
Problem:
Doctor name and specialization repeat for every
patient.
Meaning:
 Patient ID → Doctor ID
 Doctor ID → Doctor Name,
Specialization
❌ Transitive dependency

3. Company & Employee 3NF Solution:

Situation:  Employee Table: Emp ID, Name, Dept
Employee records store: ID
 Employee ID  Department Table: Dept ID, Location
 Employee Name
 Department ID
 Department Location
 Problem:
Department location repeats for all employees in
that department.
Meaning:
 Employee ID → Department ID
 Department ID → Department Location
❌ Not in 3NF

4. Banking System 3NF Solution:

Situation:  Account Table: Account No, Customer
Account records include: Name, Branch Code
 Account Number  Branch Table: Branch Code, Branch
 Customer Name Address
 Branch Code
 Branch Address
Problem:
Branch address is repeated for every account in
the same branch.
Meaning:
 Account No → Branch Code
 Branch Code → Branch Address
❌ Transitive dependency

5. Library Management System 3NF Solution:

Situation:  Issue Table: Issue ID, Book ID
Book issue records include:  Book Table: Book ID, Title, Author
 Issue ID
 Book ID
 Book Title
 Author Name
Problem:
Book title and author repeat every time the book
is issued.
Meaning:
 Issue ID → Book ID
 Book ID → Book Title, Author
❌ Not in 3NF

6. Online Shopping (Orders & Products) 3NF Solution:

Situation:  Order Table: Order ID, Product ID
Order records store:  Product Table: Product ID, Name,
 Order ID Price
 Product ID
 Product Name
 Product Price
Problem:
Product details repeat for every order.
Meaning:
 Order ID → Product ID
 Product ID → Product Name, Price
❌ Transitive dependency

To remove transitive dependency-

Do not store information about one entity inside another entity’s record—store it separately and
reference it.
Example problems for Decomposition into 3NF

One more example:

BCNF Decomposition Algorithm:
BCNF Decomposition Algorithm
1. Input: Relation schema R and functional dependencies F.
2. Check BCNF:
For each FD X → Y , if X is not a superkey, BCNF is violated.

3. Decompose:
For a violating FD X → Y , decompose R into:
 o R 1= X ∪ Y

o R2=R−(Y −X )
4. Repeat:
Apply the same steps on the resulting relations until all relations satisfy BCNF.
✅ Property: Decomposition is always lossless.
⚠ Note: Dependency preservation is not guaranteed.
BCNF Decomposition – Example 2
Given relation:
R(A, B, C, D)
Functional Dependencies:

1. A→B
2. C → D

Step 1: Find Candidate Key

Compute closure:
+¿={A , B }¿
 A
+¿={C , D }¿
 C
 ¿
→ AC is a candidate key

Step 2: Check BCNF

 A → B → ❌ Violation (A not a superkey)
 C → D→ ❌ Violation (C not a superkey)

Step 3: Decompose using A → B

 R1 ( A , B)
 R2 ( A , C , D)

Step 4: Check R2 ( A , C , D)

FD in R₂: C → D
C is not a key in R₂ → ❌ Violation

Decompose R₂ using C → D :
  R3 (C , D)
 R4 (A , C)

✅ Final BCNF Decomposition:

 (A, B)
 (C, D)
 (A, C)
Example 1:
Example 2:
Example 3:
Unit- III:

Transactions: Transaction Concept, A Simple Transaction Model, Storage

Structure, Transaction Atomicity and Durability, Serializability.

A transaction in a Database Management System (DBMS) is a single, logical unit of work consisting
of one or more operations (read, write, update, delete) that transition a database from one
consistent state to another. It ensures data integrity, even during failures, by strictly following ACID
properties: Atomicity, Consistency, Isolation, and Durability.

Key Aspects of a Transaction:

 logical Unit: A set of operations treated as a single unit; either all operations succeed
(commit) or none do (rollback).

Simple Transaction Model

A transaction is a sequence of database operations executed as a single logical unit of work. It must
either complete entirely or have no effect at all.

🔹 Need for Transaction Model

In a multi-user environment:

 Many transactions execute concurrently.

 System failures may occur.

 Data inconsistency may arise.

The transaction model ensures correctness and reliability.

Key ACID Properties:

 Atomicity (All or Nothing): A transaction is an indivisible unit. Either all its operations are
executed successfully, or none are, preventing partial updates. If one part fails, the entire
transaction is rolled back.

 Consistency (Valid State): A transaction must transform the database from one valid state
to another, maintaining all predefined rules, constraints, and integrity checks.

 Isolation (Independent Execution): Concurrent transactions do not interfere with each

other. Each transaction behaves as if it is the only one operating on the data, preventing
issues like dirty reads or inconsistent data.

 Durability (Permanent Changes): Once a transaction is committed, its changes are

permanently saved in the database, surviving any subsequent system failures.

Basic Operations

 Read(X) – Reads data item X.

  Write(X) – Writes updated value of X.

 Commit – Makes changes permanent.

 Rollback (Abort) – Undoes changes.

States: A transaction moves through various states, including Active, Partially Committed,
Committed, Failed, and Aborted.

Example: A bank transfer is a classic transaction. If User A sends ₹500 to User B, the DBMS must both
debit A and credit B. If the system fails after debiting A but before crediting B, the transaction must
roll back to ensure money is not lost.

Storage structures in DBMS transactions manage data across a hierarchy—volatile cache/RAM for
active, fast-access processing and non-volatile disk/storage for permanent, reliable persistence. Key
mechanisms ensuring ACID properties include transaction logs for recovery, buffer pools for in-
memory modification, and data pages/blocks for structured disk organization, with data files and
indexes enhancing retrieval performance.

Key Storage Structures in Transactions

 Transaction Logs (Write-Ahead Logging - WAL): Essential for durability and recovery. Before
any modification is made to the actual database on disk, the change is first written to a log
file. This ensures that even if a system crashes, committed transactions can be replayed and
uncommitted ones rolled back.

 Buffer Pool/Cache (Main Memory): A dedicated area in RAM where the DBMS stores
frequently accessed data pages to reduce slow disk I/O. Transactions operate on these data
copies.

 Data Files (Disk): Permanent, non-volatile storage (HDDs/SSDs) where the actual database
files, tables, and indexes reside.

 Pages/Blocks: The smallest, fixed-size unit of data transferred between disk and memory.
Transactions lock these units to maintain isolation
Types of Storage Structures

1. Heap File Structure: Records are stored in no particular order, often allowing fast insertion.

2. Sequential/Ordered File Structure: Records are stored in sorted order based on a search key.

3. Indexed File Structure: Utilizes data structures like B-trees to quickly locate records, crucial
for high-performance transactions.

4. Hash File Structure: Uses a hash function to determine the location of records, providing
direct access.

Storage Hierarchy for Transactions

 Primary Storage: Registers, Cache, Main Memory (RAM). Extremely fast, volatile, used for
active transaction data.

 Secondary Storage: Magnetic Disks, SSDs. Non-volatile, used for storing the database and
transaction logs.

 Tertiary Storage: Magnetic Tapes. Used for long-term backups and archival

Efficient storage structures ensure that the database remains consistent (Atomicity, Consistency,
Isolation, Durability) despite concurrent access and potential system failures
Transaction atomicity ensures a database transaction is "all-or-nothing" (fully completed or not at
all), while durability guarantees that committed transaction changes are permanently stored, even
during failures. Serializability ensures concurrent transactions produce the same result as a serial
execution, maintaining data consistency.

Atomicity and Durability

 Atomicity: Prevents partial updates; if a transaction aborts, all changes are rolled back.

 Durability: Ensures that once a transaction is committed, its changes survive system crashes
or failures.

 Implementation: These are often achieved using recovery mechanisms, such as maintaining
a log of operations or employing a shadow database scheme where updates are made to a
copy, which replaces the original only upon commit

Serializability

 Definition: A schedule is considered serializable if its outcome is equivalent to some serial

execution of the same transactions.

 Conflict Serializability: A schedule is conflict serializable if it can be transformed into a serial

schedule by swapping non-conflicting operations (i.e., operations not on the same data item,
or where one is not a write).

 View Serializability: A more relaxed form of serializability that ensures the same data is read
initially, written finally, and intermediate reads match, even if the intermediate sequence
differs.

These properties are key components of ACID (Atomicity, Consistency, Isolation, Durability) in DBMS
to ensure reliability.

1 mark questions with solutions

Q1: Identify ACID Property

If a transaction completes and changes remain even after power failure, which property is ensured?

Answer: Durability

Q2: State Identification

Transaction executes fully but crashes before commit. State?

Answer: Failed → Aborted

Q3: Lost Update Example

T1:

read(A)

A = A + 100
write(A)

T2:

read(A)

A = A - 50

write(A)

If both run simultaneously, final value may be incorrect.

Problem name?

Answer: Lost Update Problem

Concurrency Anomalies :

1. Lost Update

Two transactions update same data.

One update overwrites another.

Example:
T1 adds ₹100
T2 subtracts ₹50
Final result incorrect.

2. Dirty Read

T1 updates A but does not commit.

T2 reads A.
T1 aborts.

Now T2 used invalid data.

3. Unrepeatable Read

T1 reads A.
T2 modifies A and commits.
T1 reads A again → different value.

4. Inconsistent Analysis

T1 calculates total balance.

T2 transfers money during calculation.
Total becomes inconsistent.
**PROBLEMS – Check Conflict Serializability:

Remember conflict pairs are:

R(x) – W(x)
W(x) -R(x)
W(x) – W(x)
Schedule Precedence Graph Conclusion
T1 T2 Cycle exists. So
R(X) Schedule is NOT
R(X) conflict-
T2: R(X)->T1:W(X) serializable.
W(X) T1:W(X) -> T2:W(X)
W(X)
T1 T2 Cycle not exists. So,
R(A) schedule is conflict
W(A) serializable
T2:R(A) -> T1: W(A)
R(B) T2:W(A) -> T1:W(A)
W(B) T2: R(B) -> T1: W(B)
R(A) T2: W(B) -> T1: W(B)
W(A) All edges from T2 to T1 only.
R(B) (one edge suffies to
W(B) represent)
T1 T2 T3 Cycle not exists. So,
R(X) schedule is conflict
R(Y) serializable.
R(X)
R(Y) The sequence is:
T3:R(X)->T1:W(X) T2 -> T3 -> T1
R(Z)
W(Y) T2: R(Y) -> T3: W(Y)
T2: W(Z) -> T1: R(Z)
W(Z)
T2: W(Z) -> T1: W(Z)
R(Z)
W(X
)
W(Z)