DBMS – Transaction Processing and ACID
Transaction, Multi Processing, Concurrency Control
• Concurrency DB should allow Multiple Users to access DB at same time
• Concurrency Control DB should prevent 2 Different users from EDITING the
SAME INFORMATION at the SAME TIME
• OLTP – Online Transaction Processing !
• Applications that Allow CONCURRENCY are called OLTP Applications
ACID - Atomicity, Consistency, Isolation, Durability
Property Description
A - Atomicity Entire Transaction takes place AT ONCE or NOTHING HAPPENS ( No half-done transaction)
- Commit / Rollback
C – Consistency Database must be consistent BEFORE & AFTER TRANSACTION
I -- Isolation Multiple Transactions occur independently without INFERENCE
D – Durability • Guarantee that changes from a Committed transaction IS NOT LOST
• Committed transactions must be persisted on DURABLE media / storage (Disk), { Non
Volatile Memory } ensuring COMMITTED transactions are SAFE even if System/DB
crashes
(!! Exception WHEN Disk GETS Destroyed ))
DBMS – Normalization
Normalization in Database Design
Process in database design to
Reduce redundancy
Ensure data integrity by dividing larger tables into smaller ones
Defining relationships among them.
We discuss the First Normal Form (1NF), Second Normal Form (2NF), and
Third Normal Form (3NF) with examples. And possible 4NF and 5NF
DBMS – Normalization – 1NF solves Atomicity in Column values
Issue
1. First Normal Form (1NF) : A table is in 1NF if:
• All columns contain atomic (indivisible) values.
• Each column contains values of a single type.
• Each row is unique, identified by a primary key.
Violations
1. Phone No. contain
multiple values
Solved
1. Phone No column
has single values
DBMS – Normalization – 2NF - solves ‘Partial Dependency Issue’
2. A table is in 2NF IF :
• It is adhering to 1NF
• Non-Key Columns ARE Fully DEPENDENT on the entire primary key (no partial dependency).
Violations
1. DepartmentName depends ONLY on DepartmentID
& NOT FULLY on (EmployeeID+DepartmentId)
2. Case of partial dependency.
Notes
• PK Primary Key: (EmployeeID, DepartmentID). CK -Composite Key When PK has more than 1 Col
Solution
Split data into 2 tables
DBMS – Normalization – 3NF (Boyce Codd Normal Form)
Solves ‘Transitive Dependency Issue‘
A table is in BCNF (stricter form of 3NF) if:
• It is already in 2NF.
• There is no transitive dependency (non-key columns do not depend on other non-key columns).
• Every determinant( set of attributes) should be a CANDIDATE KEY ( see next slide)
Violation:
DepartmentLocation
depends on
DepartmentID WHICH IS
NOT A PK column.
(transitive dependency).
Solution:
Remove Transitive
Dependency by;
Splitting into 2 Tables;
Keep the Location as
part of the Dept table
DBMS – Normalization – BCNF(Advanced form of 3NF)
Candidate Key
• Set of columns in a table that can uniquely identify each row in that table.
• A table can have multiple candidate keys, but only one of them is chosen as the
primary key.
All candidate keys must satisfy the following properties:
• Uniqueness: No two rows can have the same value for the candidate key.
• Minimality: The key must contain the minimum number of columns necessary to
ensure uniqueness. No subset of a candidate key can still uniquely identify a row.
DBMS – Normalization – 4NF solves Multivalued Dependency Issue’
A table is said to be in 4NF if: What is a Multi-Valued Dependency?
It is in 3NF. A multi-valued dependency occurs when one
It has no multi-valued dependencies. attribute in a table determines multiple values of
another attribute independently of other attributes
Issue - Redundancy
1. Student can take Multiple Courses
2. Student also has multiple hobbies
So, there ARE INDEPENDENT RELATIONSHIPS in
this table between StuCourse & StuHobby
Solution:
• Separate the independent
relationships into different
tables to eliminate multi-
valued dependencies.
DBMS – Normalization – 5NF solves Joined Dependency Issue
Table A Table B Table C
Supplier-Parts Supplier-Project Parts-Project
• Only by using Join the table A, B, and C
we can get the results as shown below .
• Omitting even 1 of the tables will give Normalization vs. Performance:
incrorrect results • Higher normal forms (4NF and 5NF) can lead to
more tables and joins, which may reduce query
Results performance.
• Often, databases are denormalized for
performance in practical applications.
When to Use 4NF and 5NF:
• Use 4NF when dealing with multi-valued
attributes or many-to-many relationships.
• Use 5NF when complex join dependencies lead to
redundancy.