Database Management Systems

VTU Module-wise Question Bank — Simple 10-Mark Answers

Covers all 5 modules from the curated Instagram question list (mohsin_alii_14)

MODULE 1 — Database Concepts, Architecture, ER Modeling

1. Define Database. Elaborate Component Modules of DBMS and Their

Interactions
What is a Database?
A Database is a collection of related data, organized so that it can be easily accessed, managed, and
updated. It represents some aspect of the real world (called the 'mini-world') and is built to serve the
information needs of an organization.
A DBMS (Database Management System) is the software that lets users create, store, update, and
retrieve data from a database easily and safely.

Component Modules of a DBMS

Think of a DBMS as a team of smaller modules, each doing one specific job:
Module What It Does
DDL Compiler Reads CREATE/ALTER/DROP commands and stores table structure
(metadata) in the system catalog
DML Compiler / Query Processor Reads SELECT/INSERT/UPDATE/DELETE commands and converts
them into low-level instructions
Query Optimizer Picks the FASTEST way to run a query (which index to use, which join
order)
Database Manager The 'middleman' that connects user requests to the actual stored data
Stored Data Manager Talks directly to the operating system to read/write data on the disk
Transaction Manager Makes sure multiple users using the database at once don't mess up
each other's data
System Catalog (Data Dictionary) Stores ALL metadata — table names, columns, constraints, indexes —
used by every other module

How They Interact (Simple Diagram)

[User types a query]
↓
[DML Compiler] reads the query
↓
[Query Optimizer] picks the best plan ←→ checks [System Catalog]
↓
[Database Manager] executes the plan
↓
[Stored Data Manager] fetches/writes actual data on disk
↓
[Result shown to User]
In short: the DDL Compiler builds the structure, the DML Compiler + Optimizer handle queries
efficiently, the Database Manager + Stored Data Manager do the actual reading/writing, and the
System Catalog is the shared reference book that everyone consults.

2. Three-Schema Architecture. Why Do We Need Mappings Among Schema

Levels?
The Three Levels (Simple Explanation)
The idea is simple: separate WHAT the user sees, WHAT the data actually means, and HOW it's
physically stored — into 3 layers, so changing one doesn't break the others.
Level Simple Meaning Example
External Level What a SPECIFIC USER sees (their own A clerk only sees employee
custom view) names & salaries, not SSNs
Conceptual Level The FULL logical structure of the whole All tables: EMPLOYEE,
database (entities, relationships, constraints) DEPARTMENT, PROJECT and
their relationships
Internal Level How the data is ACTUALLY stored on disk B-tree index on EmpID, fixed-
(files, indexes, pointers) length records

Diagram
[View 1] [View 2] [View 3] ← External Level (user views)
\ | /
External/Conceptual Mapping
|
[CONCEPTUAL SCHEMA] ← Whole database structure
|
Conceptual/Internal Mapping
|
[INTERNAL SCHEMA] ← Physical storage details
|
Actual Stored Data

Why We Need Mappings

A MAPPING is just a translation rule that connects one level to the next. We need them because:
• They let the DBMS convert a user's simple request (External level) into the full database
structure (Conceptual level), and then into the actual disk location (Internal level).
• They give us DATA INDEPENDENCE — if we change how data is physically stored (e.g., add a
new index), we only update the Conceptual/Internal mapping. The user's view and queries don't
need to change at all.
• Without mappings, every change in storage would force us to rewrite every application —
mappings act like a buffer/shock-absorber between layers.
3. Logical vs Physical Data Independence + Advantages of Using DBMS
Approach
Logical Data Independence
This means: we can change the CONCEPTUAL schema (add a new table, new attribute, new
relationship) WITHOUT having to change the External views or the application programs that use them.
Example: Adding a new attribute 'Email' to the EMPLOYEE table doesn't break existing programs that
don't use Email.
This is HARDER to achieve because application programs are often closely tied to the conceptual
structure.

Physical Data Independence

This means: we can change the INTERNAL schema (how data is physically stored — file organization,
indexes, storage device) WITHOUT changing the Conceptual schema or External views.
Example: Switching from a simple file to a B-tree indexed file to speed up searches — the user doesn't
notice any difference.
This is EASIER to achieve since physical storage is well separated from logical meaning.

Quick Comparison
Aspect Logical Data Independence Physical Data Independence
Change made at Conceptual level Internal level
Protects External views & application programs Conceptual schema & external views
Difficulty Harder Easier

Advantages of Using the DBMS Approach

• Controls Redundancy — avoids storing the same data multiple times
• Restricts Unauthorized Access — only authorized users can view/edit sensitive data
• Provides Persistent Storage — data stays safe even after the program that created it ends
• Allows Efficient Query Processing — built-in optimizer makes queries fast
• Provides Backup and Recovery — automatically protects data from crashes/failures
• Provides Multiple User Interfaces — different users (forms, GUIs, command line) can access
the same data
• Represents Complex Relationships — easily models relationships between many types of data
• Enforces Integrity Constraints — automatically rejects invalid data (e.g., negative salary)
• Permits Inferencing via Rules — supports deductive rules in advanced systems

4. ER Diagram for a COMPANY Database (Employee, Department, Project

as Strong; Dependent as Weak)
Entities and Their Attributes
Entity Attributes
EMPLOYEE (strong) SSN (key), Name, Address, Sex, Salary, Bdate
DEPARTMENT (strong) Dnumber (key), Dname, Mgr_SSN, Mgr_start_date
PROJECT (strong) Pnumber (key), Pname, Plocation
DEPENDENT (weak) Dependent_name (partial key), Sex, Bdate, Relationship

Relationships, Cardinality Ratios, and Participation

Relationship Between Cardinality Participation
WORKS_FOR EMPLOYEE — DEPARTMENT N:1 EMPLOYEE: Total,
DEPARTMENT:
Partial
MANAGES EMPLOYEE — DEPARTMENT 1:1 Both: Partial
CONTROLS DEPARTMENT — PROJECT 1:N PROJECT: Total,
DEPARTMENT:
Partial
WORKS_ON EMPLOYEE — PROJECT M:N EMPLOYEE: Partial,
PROJECT: Total
DEPENDENTS_O EMPLOYEE — DEPENDENT 1 : N (identifying) DEPENDENT: Total,
F EMPLOYEE: Partial

ER Diagram (Simplified Text Form)

[DEPARTMENT] --(1:N CONTROLS)--> [PROJECT]
[DEPARTMENT] <--(1:1 MANAGES)-- [EMPLOYEE]
[EMPLOYEE] --(N:1 WORKS_FOR)--> [DEPARTMENT]
[EMPLOYEE] <==(M:N WORKS_ON, attribute: Hours)==> [PROJECT]
[EMPLOYEE] ===(identifying relationship)===> [DEPENDENT] (double rectangle — weak entity)
Notation Key: Single rectangle = strong entity | Double rectangle = weak entity | Diamond = relationship
| Double diamond = identifying relationship | Underlined attribute = key | Double line = total participation
| Single line = partial participation

5. Entity, Attribute, Composite Attribute, Multivalued Attribute, Participation

Role, Cardinality Ratio — Definitions with Examples
Entity
A 'thing' in the real world that we want to store data about, having its own independent existence.
Example: an EMPLOYEE, a CAR, a PRODUCT.

Attribute
A property or characteristic of an entity. Example: an EMPLOYEE has attributes Name, Age, Salary.

Composite Attribute
An attribute that can be split into smaller, meaningful sub-parts. Example: Address can be split into
Street, City, State.

Multivalued Attribute
An attribute that can have MORE THAN ONE value for a single entity, shown with a double oval.
Example: a person can have multiple {PhoneNumber} values.

Participation Role
Describes HOW an entity participates in a relationship. There are two types:
 • Total Participation: EVERY entity instance MUST participate in the relationship (shown as a
double line). Example: every EMPLOYEE must work for some department.
• Partial Participation: only SOME entity instances participate (shown as a single line). Example:
not every employee manages a department.

Cardinality Ratio
Specifies the MAXIMUM number of relationship instances an entity can participate in. The 3 common
types:
Ratio Meaning Example
1:1 One entity relates to exactly one of the other One employee manages one department
1:N One entity relates to many of the other One department has many employees
M:N Many entities relate to many of the other Many employees work on many projects

6. Categories of Data Models. Characteristics of Database

Categories of Data Models
A Data Model is a collection of concepts used to describe the structure of a database. There are 3 main
categories, based on the LEVEL of detail they represent:
Category Description Example
High-Level (Conceptual) Data Provides concepts close to how users perceive ER Model, EER Model
Models data — uses entities, attributes, relationships
Representational Provides concepts users CAN understand, but Relational Model,
(Implementation) Data Models close to how data is organized in storage Network Model,
Hierarchical Model
Low-Level (Physical) Data Describes how data is stored at the lowest level Physical storage / file
Models — records, bits, pointers structure models

Characteristics of the Database (Approach)

• Self-Describing Nature: The database stores both the DATA and the DESCRIPTION (metadata)
of that data in the system catalog.
• Insulation between Programs and Data: Changing the data's storage structure does not require
changing the application programs (this is data abstraction/independence).
• Support for Multiple Views: Different users can have their own customized 'view' of the same
database.
• Sharing of Data and Multi-user Transactions: Many users can access/update the database at
the same time, safely.

7. Responsibilities of DBA and Database Designers

Database Administrator (DBA)
The DBA is the person (or team) responsible for the OVERALL management and control of the
database system.
• Authorizing access to the database — deciding who can read/write what data
• Acquiring software and hardware resources needed for the database
 • Monitoring and coordinating database usage — keeping track of who's using the system and
how
• Setting up and managing backup and recovery procedures
• Defining storage structure and access methods for performance tuning

Database Designers
Database Designers are responsible for identifying the data to be stored and choosing appropriate
structures to represent it.
• Identifying the entities, attributes, and relationships relevant to the enterprise (conceptual/logical
design)
• Communicating with all prospective users to understand their requirements
• Designing a schema that satisfies the data and functional requirements of various user groups
• Coming up with a design that is efficient and free of anomalies (often using normalization)

8. Types of End Users with Examples

Type of End User Description Example
Casual End Users Occasionally access the database, may need A manager who checks
different information each time, use a query sales reports once a
language month
Naive / Parametric End Constantly query and update the database using A bank teller processing
Users STANDARD, pre-written queries/transactions withdrawals, a reservation
(forms-based) clerk
Sophisticated End Users Engineers, scientists, business analysts who A data analyst writing
thoroughly understand DBMS facilities to complex SQL queries for
implement their own applications reports
Standalone Users Maintain personal databases using ready-made A person using a personal
packages with easy menu/graphical interfaces finance app like an
expense tracker

9. Different Types of Attributes in ER Model

Attribute Type Description Example
Simple (Atomic) Cannot be divided further Age
Composite Can be divided into smaller parts Address → (Street, City, State)
Single-Valued Has only ONE value for an entity Age
Multi-Valued Can have MULTIPLE values for an entity {PhoneNumber}
Stored Value is directly stored in the database Date_of_Birth
Derived Value is calculated from other attributes Age (derived from
Date_of_Birth)
Key Uniquely identifies each entity instance SSN

MODULE 2 — Relational Model, Relational Algebra, ER-Mapping

1. Update Operations and Dealing with Constraint Violations — With
Examples
The 3 Update Operations
These are the operations that CHANGE the data inside a relation (not its structure):
Operation What It Does Possible Violation
INSERT Adds a new tuple (row) into a relation Domain, Key, Entity Integrity,
Referential Integrity
DELETE Removes an existing tuple from a relation Referential Integrity (if other rows
reference it)
UPDATE Changes the value of one or more attributes Domain, Key, Entity/Referential Integrity
(MODIFY) in existing tuples

INSERT — Example and Possible Violations

INSERT INTO EMPLOYEE VALUES (NULL, 'John', 5);
Violates ENTITY INTEGRITY because the primary key (SSN) cannot be NULL → REJECTED.
INSERT INTO EMPLOYEE VALUES (123, 'John', 99);
Violates REFERENTIAL INTEGRITY if Department 99 does not exist → REJECTED.

DELETE — Example and Possible Violation

DELETE FROM DEPARTMENT WHERE Dnumber = 5;
If employees still belong to Department 5, deleting it creates DANGLING REFERENCES (orphan rows)
→ violates Referential Integrity.
3 Ways to Handle This:
• RESTRICT: Reject the delete operation entirely if references exist
• CASCADE: Automatically delete all the referencing employee rows too
• SET NULL: Set the referencing foreign key (Dno) to NULL in the employee rows

UPDATE — Example and Possible Violation

UPDATE EMPLOYEE SET SSN = NULL WHERE SSN = 123;
Violates Entity Integrity → REJECTED.
UPDATE EMPLOYEE SET Dno = 99 WHERE SSN = 123;
Violates Referential Integrity if Department 99 doesn't exist → REJECTED.

2. Relational Algebra Operators: SELECT and PROJECT — With Examples

SELECT Operation (σ)
Picks out a SUBSET OF ROWS (tuples) from a relation that satisfy a given condition. Think of it as
filtering rows HORIZONTALLY.
Syntax: σ<condition>(Relation)
Example: EMPLOYEE(SSN, Name, Salary, Dno)
σ(Salary > 30000)(EMPLOYEE)
This returns only the employee rows where Salary is greater than 30000.
We can also combine multiple conditions using AND/OR:
σ(Dno=4 AND Salary>30000)(EMPLOYEE)
PROJECT Operation (π)
Picks out a SUBSET OF COLUMNS (attributes) from a relation, discarding the rest. Think of it as
filtering columns VERTICALLY.
Syntax: π<attribute list>(Relation)
Example:
π(Name, Salary)(EMPLOYEE)
This returns only the Name and Salary columns for every employee.
Important: PROJECT automatically removes any DUPLICATE rows that result, since a relation is
mathematically a SET.

Combining SELECT and PROJECT

Find the names of employees who earn more than 30000:
π(Name)( σ(Salary > 30000)(EMPLOYEE) )
Here, SELECT first filters the rows, then PROJECT picks only the Name column from the result.

3. Characteristics of Relations (How They Differ from Ordinary Tables/Files)

• 1. Tuples are NOT Ordered: A relation is a SET of tuples, so there is no fixed 'first' or 'last' row
— unlike files, where physical record order can matter.
• 2. Attributes within a Tuple are NOT Ordered (by position): What matters is the attribute NAME,
not its column position — a tuple is like a function mapping attribute names to values.
• 3. All Attribute Values are Atomic: Every value in a relation must be a single, indivisible value
(1NF rule) — unlike some file formats that allow nested or repeating groups.
• 4. No Duplicate Tuples Allowed: Since a relation is a mathematical SET, no two tuples can be
exactly identical — files can have duplicate records.
• 5. A Relation has a Clear Meaning (Interpretation): Each relation schema can be read as a
statement/predicate about the real world, and each tuple is a fact matching that statement —
ordinary files don't carry this formal meaning.

4. Unary Relational Operations — With Examples

'Unary' means the operation works on only ONE relation at a time. The three main unary operations are
SELECT, PROJECT, and RENAME.

SELECT (σ) — filters rows

σ(Dno=5)(EMPLOYEE) -- gives all employees in department 5

PROJECT (π) — filters columns

π(Name, Dno)(EMPLOYEE) -- gives only Name and Dno columns

RENAME (ρ) — renames relation/attributes

Used to give a NEW NAME to a relation or its attributes — useful for clarity, or when the same relation
needs to be used twice in one expression.
Syntax: ρ NewName(NewAttr1, NewAttr2, ...)(Relation)
ρ EMP(ID, EName, Sal, DeptNo)(EMPLOYEE)
This renames the EMPLOYEE relation to EMP, and its columns to ID, EName, Sal, DeptNo.
Why RENAME Matters
If we want to compare an employee with their own supervisor (using the SAME relation twice), we must
RENAME one of the copies — otherwise SQL/relational algebra won't know which 'EMPLOYEE' we
mean in each part of the expression.

5. What is an Integrity Constraint? Importance of Referential Integrity

Constraint
Integrity Constraint — Definition
An Integrity Constraint is a RULE that the data in a database MUST always follow, to make sure the
data stays accurate and meaningful. The DBMS automatically checks these rules whenever data is
inserted, updated, or deleted.

Common Types of Integrity Constraints

Constraint Meaning
Domain Constraint Each attribute value must come from its defined data type/range
Key Constraint No two tuples can have the same primary key value
Entity Integrity Primary key value can NEVER be NULL
Referential Integrity A foreign key value must match an existing primary key value in another table,
or be NULL

Referential Integrity Constraint — Detailed

Specifically, this constraint is defined BETWEEN TWO relations and is used to keep the relationships
between tables consistent. It says: a foreign key in the REFERENCING table must either match a
primary key value that EXISTS in the REFERENCED table, or be NULL.
Example: [Link] (foreign key) must match an existing [Link] (primary
key).

Why It's Important

• Prevents 'dangling' or 'orphan' references — i.e., a record pointing to something that doesn't
exist.
• Keeps relationships between tables LOGICALLY CONSISTENT (e.g., you can't have an
employee assigned to a department that was deleted).
• Helps maintain DATA ACCURACY across the whole database, not just within one table.

6. E-R to Relational Mapping Algorithm — With Example for Each Step

Step 1: Map Regular (Strong) Entity Types
Create a table for each strong entity, with all its simple attributes; choose one key attribute as the
Primary Key.
EMPLOYEE(SSN, Name, Bdate, Address) -- PK: SSN

Step 2: Map Weak Entity Types

Create a table including the weak entity's own attributes PLUS the primary key of its OWNER entity (as
foreign key). The Primary Key becomes (Owner's PK + weak entity's partial key).
 DEPENDENT(Essn[FK], Dependent_name, Sex, Bdate) -- PK: (Essn, Dependent_name)

Step 3: Map Binary 1:1 Relationship Types

Pick ONE of the two tables (preferably the one with total participation) and add the OTHER table's
primary key as a foreign key.
DEPARTMENT(Dnumber, Dname, Mgr_ssn[FK]) -- Mgr_ssn references [Link]

Step 4: Map Binary 1:N Relationship Types

Add the primary key of the entity on the '1' side as a foreign key in the table on the 'N' side.
EMPLOYEE(SSN, Name, Dno[FK]) -- Dno references [Link]

Step 5: Map Binary M:N Relationship Types

Create a BRAND NEW table containing the primary keys of BOTH entities (as foreign keys) — their
combination becomes the new table's primary key. Add any relationship attributes too.
WORKS_ON(Essn[FK], Pno[FK], Hours) -- PK: (Essn, Pno)

Step 6: Map Multivalued Attributes

Create a NEW table with the multivalued attribute plus the primary key of its owning entity (as foreign
key).
DEPT_LOCATIONS(Dnumber[FK], Dlocation) -- PK: (Dnumber, Dlocation)

Step 7: Map N-ary Relationship Types (3 or more entities)

Create a new table with the primary keys of ALL participating entities as foreign keys, plus relationship
attributes.

7. Generalization and Specialization — With Examples

Specialization
The process of defining a set of SUB-CLASSES (more specific entity types) of an entity type — going
from GENERAL to SPECIFIC. It's a TOP-DOWN process.
Example: VEHICLE entity is specialized into CAR and TRUCK (CAR has extra attribute 'No_of_doors',
TRUCK has extra attribute 'Tonnage').

Generalization
The REVERSE process — we look at several entity types that share common attributes, and define a
single GENERAL super-class that includes them all. It's a BOTTOM-UP process.
Example: CAR and TRUCK both share common attributes (Vehicle_id, Price) → generalized into a
single super-class VEHICLE.

Diagram
[VEHICLE] (superclass)
/ \
[CAR] [TRUCK] (subclasses)
Specialization: Start with VEHICLE → split into CAR, TRUCK
Generalization: Start with CAR, TRUCK → combine into VEHICLE

Key Difference
Aspect Specialization Generalization
Direction Top-down (general → specific) Bottom-up (specific → general)
Starts with One superclass Multiple subclasses
Result Multiple subclasses created One superclass created

8. Equijoin and Natural Join — With Suitable Examples Using Relational

Algebra
Equijoin
A JOIN where the joining condition uses ONLY the equality (=) operator. The result keeps BOTH
joining columns (even though they have the same values), so there's some duplication.
Syntax: R ⋈ <condition> S
EMPLOYEE ⋈ ([Link] = [Link]) DEPARTMENT
Result includes both Dno AND Dnumber columns, even though their values always match.

Natural Join
A special case of equijoin where: the join attributes have the SAME NAME in both tables, and the
DUPLICATE column is automatically removed from the result.
Syntax: R * S
EMPLOYEE * DEPARTMENT
(Assuming both relations share a column literally named 'Dnumber'.) The result keeps only ONE copy
of Dnumber.

Quick Comparison
Aspect Equijoin Natural Join
Join Condition Equality, column names can differ Equality, column names must be the
SAME
Duplicate Columns Kept in result Automatically removed

9. Any Two Operations That Change the State of a Relation — With

Examples
(This refers to the basic update operations covered in Q1 — here are two explained in more depth)

Operation 1: INSERT
Adds a NEW tuple to a relation, changing its STATE by increasing the number of tuples.
INSERT INTO EMPLOYEE (SSN, Name, Salary, Dno) VALUES ('999', 'Anita', 45000, 4);
Before: EMPLOYEE has 10 rows. After: EMPLOYEE has 11 rows — a new fact has been added to the
database.

Operation 2: DELETE
Removes an existing tuple from a relation, changing its state by decreasing the number of tuples.
DELETE FROM EMPLOYEE WHERE SSN = '999';
Before: EMPLOYEE has 11 rows. After: EMPLOYEE has 10 rows — Anita's record has been removed.
(UPDATE is the third such operation — it changes existing values without changing the row count.)
10. Aggregation Functions and Grouping in Relational Algebra — With
Examples
Aggregate Functions
These COMPUTE a single summary value from a collection of values in a column. Common ones:
COUNT, SUM, AVG, MAX, MIN.
Symbol used in relational algebra: ℑ (script F)

Syntax
<grouping attributes> ℑ <function list>(Relation)

Example 1 — No Grouping (single summary value)

ℑ MAX(Salary)(EMPLOYEE)
Returns the single highest salary among ALL employees.

Example 2 — With Grouping

Dno ℑ COUNT(SSN), AVG(Salary) (EMPLOYEE)
This GROUPS employees by their department number (Dno), then for EACH group computes: the
COUNT of employees, and the AVERAGE salary.
Result Example:
Dno COUNT(SSN) AVG(Salary)
4 5 38500
5 8 42100
1 3 55000
In plain terms: Grouping organizes rows into 'buckets' based on a column's value, and the aggregate
function then computes one summary result per bucket.

MODULE 3 — Normalization, Functional Dependencies, SQL DML

1 & 6. Informal Design Guidelines for Relational Schema Design

Guideline 1: Design a Schema with Clear, Easy-to-Explain Meaning
Each table should represent ONE entity or ONE relationship type. Don't mix unrelated facts into a
single table — it should be easy to explain what each row means in plain language.

Guideline 2: Reduce Redundant Information

Avoid storing the same fact in multiple places. If a piece of information is repeated across many rows,
any update needs to happen in ALL those rows — risky and wasteful. Normalization helps fix this.
Example (Bad): Storing DeptName next to every single employee row → if the department is renamed,
every employee row must be updated.

Guideline 3: Reduce NULL Values in Tables

Try to avoid attributes that are frequently empty/NULL for many rows. NULLs waste storage, are
confusing (does NULL mean 'unknown' or 'not applicable'?), and can cause errors in calculations like
SUM or AVG.
Guideline 4: Avoid Generating Spurious (Fake) Tuples
Design tables so that when you JOIN them back together (using primary key/foreign key), you get back
EXACTLY the original correct data — no extra, incorrect rows should appear. This is called the
LOSSLESS JOIN property.
Bad Example: Splitting a table incorrectly (without keeping a proper key relationship) can cause a join
to create FALSE combinations of data that never actually existed.

2. Define 1NF, 2NF, and 3NF with Examples

First Normal Form (1NF)
A table is in 1NF if every column holds only ATOMIC (single, indivisible) values — no lists, no repeating
groups inside one cell.
Bad Example: EMP(SSN, Name, {Pnumber, Hours}) — the Pnumber/Hours is a repeating group inside
one row.
1NF Fix: One row per (employee, project) pair → EMP_PROJ(SSN, Name, Pnumber, Hours).

Second Normal Form (2NF)

A table is in 2NF if it is already in 1NF, AND every non-key column depends on the ENTIRE primary
key (not just part of it) — no PARTIAL DEPENDENCY.
Example: EMP_PROJ(SSN, Pnumber, Hours, Ename, Pname), PK = {SSN, Pnumber}
Problem: Ename depends ONLY on SSN (not the full key) → partial dependency, violates 2NF.
2NF Fix — split into:
• EMP_PROJ1(SSN, Pnumber, Hours)
• EMP1(SSN, Ename)

Third Normal Form (3NF)

A table is in 3NF if it is already in 2NF, AND no non-key column depends on ANOTHER non-key
column — no TRANSITIVE DEPENDENCY.
Example: EMP_DEPT(SSN, Ename, Dnumber, Dname), PK = {SSN}
Problem: SSN → Dnumber → Dname (Dname depends on Dnumber, which depends on SSN — a
chain) → transitive dependency, violates 3NF.
3NF Fix — split into:
• EMP1(SSN, Ename, Dnumber)
• DEPT1(Dnumber, Dname)

3. Syntax for INSERT, UPDATE, and DELETE Statements in SQL — With

Examples
INSERT Statement
Syntax: INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);
INSERT INTO EMPLOYEE (SSN, Name, Salary) VALUES ('101', 'Ravi', 35000);

UPDATE Statement
Syntax: UPDATE table_name SET col1=val1, col2=val2 WHERE condition;
UPDATE EMPLOYEE SET Salary = Salary + 5000 WHERE SSN = '101';
(If you omit the WHERE clause, EVERY row in the table gets updated!)

DELETE Statement
Syntax: DELETE FROM table_name WHERE condition;
DELETE FROM EMPLOYEE WHERE Salary < 20000;
(Again, omitting WHERE deletes ALL rows in the table.)

4 & 8. Insertion, Deletion, and Modification Anomalies — Why They're Bad,

With Examples
What Causes Anomalies?
Anomalies happen when a table is NOT properly normalized — usually because it mixes facts about
TWO DIFFERENT entities into one table, causing repeated/redundant data.
Example unnormalized table: EMP_DEPT(SSN, Ename, Dnumber, Dname, Dlocation)

1. Insertion Anomaly
We CANNOT insert a fact (like a new department) WITHOUT also having an unrelated fact (an
employee) at the same time.
Example: To add a NEW department 'Finance' that has no employees yet, we'd need to insert a row
with a NULL employee — awkward and not always allowed.

2. Deletion Anomaly
Deleting one fact ACCIDENTALLY deletes another, completely unrelated fact.
Example: If the LAST employee in the 'Finance' department is deleted, we lose ALL information about
the Finance department itself (its name, location) — even though we only meant to remove the
employee.

3. Modification (Update) Anomaly

Updating ONE fact requires changing MULTIPLE rows — if we miss even one row, the data becomes
inconsistent.
Example: If the Finance department moves to a new location, we must update the Dlocation value in
EVERY row of every employee in Finance. If we forget even one row, we now have TWO different
locations listed for the same department — contradictory data!

Why These Are Considered Bad

• They waste storage space (redundant data)
• They risk data inconsistency (different rows showing different 'truths')
• They make the database harder to maintain and trust
Solution: NORMALIZATION — splitting the table into smaller, well-structured tables (as shown in Q2)
removes these anomalies.

5. Datatypes in SQL and Substring Pattern Matching — With Examples

(i) Datatypes in SQL
Category Datatype Example Use
Numeric INT, SMALLINT, DECIMAL(p,s), FLOAT Age INT, Salary DECIMAL(10,2)
Character/String CHAR(n), VARCHAR(n) Gender CHAR(1), Name
VARCHAR(30)
Date/Time DATE, TIME, TIMESTAMP Bdate DATE
Boolean BOOLEAN IsActive BOOLEAN
CREATE TABLE EMPLOYEE (
SSN CHAR(9), Name VARCHAR(30), Salary DECIMAL(10,2), Bdate DATE
);

(ii) Substring Pattern Matching in SQL

SQL provides the LIKE operator to search for a specific PATTERN within a string column, using two
special wildcard symbols:
Symbol Meaning
% (percent) Matches ANY sequence of zero or more characters
_ (underscore) Matches EXACTLY ONE character
Example 1: Find all employees whose name starts with 'J':
SELECT * FROM EMPLOYEE WHERE Name LIKE 'J%';
Example 2: Find all employees whose name contains 'an' anywhere:
SELECT * FROM EMPLOYEE WHERE Name LIKE '%an%';
Example 3: Find names that are exactly 4 letters long and start with 'A':
SELECT * FROM EMPLOYEE WHERE Name LIKE 'A___'; -- 3 underscores = 3 more letters

7 & 9. Functional Dependency, Armstrong's Inference Rules (with Proof),

and Types of JDBC Drivers
Functional Dependency (FD) — Definition
A Functional Dependency X → Y means: for any two rows in a table, if they have the SAME value of X,
they MUST also have the SAME value of Y. In simple words, X determines Y.
Example: SSN → Ename (knowing the SSN tells you exactly which name it belongs to).

Armstrong's Inference Rules (with Brief Proof Sketch)

Rule Statement Why It's True (Proof Idea)
Reflexivity If Y ⊆ X, then X → Y Trivially true — if Y is part of X, then knowing X
automatically tells you Y
Augmentation If X → Y, then XZ → YZ Adding the same extra attribute Z to both sides
doesn't break the original dependency
Transitivity If X → Y and Y → Z, then X → Z If X fixes Y, and Y fixes Z, then X indirectly fixes
Z too (chain rule)
From these 3 basic rules, we can DERIVE more useful rules:
• Union Rule: If X→Y and X→Z, then X→YZ (combine the dependencies)
• Decomposition Rule: If X→YZ, then X→Y and X→Z (split the dependency)
• Pseudotransitivity: If X→Y and WY→Z, then WX→Z

Types of JDBC Drivers

JDBC (Java Database Connectivity) drivers let a Java program talk to a database. There are 4 types:
Type Name How It Works
Type 1 JDBC-ODBC Bridge Driver Converts JDBC calls into ODBC calls — needs
ODBC installed, now mostly obsolete
Type 2 Native-API Driver (Partly Java Converts JDBC calls into native database-specific
Driver) calls using a client-side library
Type 3 Network Protocol Driver (Pure Java) Sends JDBC calls to a middleware server, which
then talks to the database
Type 4 Thin Driver / Native Protocol Driver Directly converts JDBC calls into the database's
(Pure Java) own network protocol — fastest, most commonly
used today

MODULE 4 — Transactions, SQL Views/Queries, Concurrency Issues

1. Correlated Nested Queries in SQL — With Suitable Example

What is a Correlated Nested Query?
A Correlated Nested Query is a subquery that uses a value from the OUTER query — meaning the
subquery CANNOT be run independently; it must be re-evaluated for EVERY row of the outer query.
This is different from a normal (non-correlated) subquery, which can run on its own once and gives one
fixed result used by the outer query.

Example
Find all employees who earn more than the AVERAGE salary of their OWN department:
SELECT Name, Salary, Dno
FROM EMPLOYEE E1
WHERE Salary > (
SELECT AVG(Salary)
FROM EMPLOYEE E2
WHERE [Link] = [Link] -- this links back to the OUTER query!
);
Why this is correlated: The inner query's condition ([Link] = [Link]) depends on E1, which comes
from the OUTER query. So for EVERY employee (E1) checked by the outer query, the inner query RE-
RUNS to compute the average salary of THAT employee's specific department.
In simple words: the inner query keeps 'looking back' at the outer row currently being processed.

2 & 6. ACID Properties and Demonstrating Transaction States

ACID Properties
Property Meaning
Atomicity A transaction is all-or-nothing — either ALL its steps complete, or NONE do
Consistency A transaction must take the database from one valid (consistent) state to another
Isolation Each transaction behaves as if it's the ONLY one running, even with others
happening at the same time
Durability Once a transaction is committed, its changes are PERMANENT, even if the system
crashes right after
Example — Bank Transfer:
BEGIN TRANSACTION;
UPDATE ACCOUNT SET balance = balance - 500 WHERE acc_id = 'A';
UPDATE ACCOUNT SET balance = balance + 500 WHERE acc_id = 'B';
COMMIT;
Atomicity ensures that if the system crashes after the FIRST update but before the SECOND, the first
update is also UNDONE — so money doesn't vanish.

Transaction States (with Diagram)

State Meaning
Active Transaction is currently executing
Partially Committed Last operation finished, but not yet confirmed as permanently saved
Committed Transaction succeeded — changes are now permanent
Failed Something went wrong — transaction cannot continue normally
Aborted Transaction was rolled back — database restored to its state BEFORE the
transaction started
Terminated The transaction has fully exited the system (after commit or abort)
Diagram:
[Active] → (finishes) → [Partially Committed] → (confirmed) → [Committed] → [Terminated]
↓ (something fails) ↓ (something fails)
[Failed] → (rollback) → [Aborted] → [Terminated]

3. Views in SQL — With Examples

What is a View?
A View is a VIRTUAL table — it doesn't store data itself, but is generated on-the-fly from a SELECT
query on one or more real (base) tables.

Creating a View
CREATE VIEW HighEarners AS
SELECT Name, Salary, Dno
FROM EMPLOYEE
WHERE Salary > 50000;
Now we can query this view JUST LIKE a regular table:
SELECT * FROM HighEarners;

Why Use Views?

• Simplicity: Hides complex joins/conditions behind a simple, reusable name
• Security: Shows only specific rows/columns to certain users, hiding sensitive data
• Logical Data Independence: Underlying table structure can change without affecting how the
view is used
Dropping a view: DROP VIEW HighEarners;
4. GROUP BY and HAVING Clauses in SQL — With Examples
GROUP BY
Used to GROUP rows that have the same value in one or more columns, usually so we can apply an
aggregate function (COUNT, SUM, AVG) to each group SEPARATELY.
SELECT Dno, COUNT(*) AS NumEmployees
FROM EMPLOYEE
GROUP BY Dno;
This gives the number of employees in EACH department.

HAVING
Used to FILTER the GROUPS created by GROUP BY — similar to WHERE, but HAVING works
AFTER grouping and CAN use aggregate functions.
SELECT Dno, COUNT(*) AS NumEmployees
FROM EMPLOYEE
GROUP BY Dno
HAVING COUNT(*) > 5;
This only shows departments that have MORE THAN 5 employees.

Quick Rule of Thumb

Clause Filters Can Use Aggregate
Functions?
WHERE Individual rows, BEFORE grouping No
HAVING Groups, AFTER grouping Yes

5. Problems That May Occur with Concurrent Transactions

1. Lost Update Problem
Two transactions read the SAME value and update it — the second WRITE overwrites (loses) the first
one's update.
Example: Balance X=100. T1 reads X(100), plans to add 50. T2 reads X(100) too, plans to add 30. T1
writes X=150. T2 writes X=130 — T1's update is LOST.

2. Dirty Read (Temporary Update) Problem

A transaction reads a value that was changed by ANOTHER transaction that HASN'T committed yet —
and that other transaction later rolls back, making the read value invalid/never-real.
Example: T1 updates X to 200 (not committed). T2 reads X=200. T1 then FAILS and rolls back to
X=100. But T2 already used the wrong value 200!

3. Unrepeatable Read (Incorrect Summary) Problem

A transaction reads the SAME data item TWICE, and gets DIFFERENT values, because another
transaction modified it in between.
Example: T1 reads Account_A = 50 for a report. Meanwhile, T2 updates Account_A to 100 and
commits. T1 reads Account_A again and now gets 100 — inconsistent within its own transaction.
8. System Log in Database Transactions; Cursor and Its Properties in
Embedded SQL
System Log
The System Log (or Transaction Log) is a special file that keeps a record of ALL changes made to the
database, used by the Recovery Manager to UNDO or REDO transactions after a crash.
Typical entries recorded in the log:
Log Entry Meaning
[start_transaction, T] Transaction T has started
[write_item, T, X, old_value, Transaction T changed item X from old_value to new_value
new_value]
[read_item, T, X] Transaction T read item X
[commit, T] Transaction T has successfully committed
[abort, T] Transaction T was aborted/rolled back
Why it's needed: If the system crashes, the recovery manager reads the log to know exactly what each
transaction did — so it can UNDO uncommitted changes and REDO committed ones, restoring the
database to a consistent state.

Cursor in Embedded SQL

A Cursor is a pointer that lets a program process the rows of a SQL query result ONE AT A TIME —
needed because programming languages (like C, Java) work with one record at a time, while SQL
naturally returns a whole SET of rows.
Properties / Steps of Using a Cursor:
• DECLARE — define the cursor and link it to a SELECT query
• OPEN — run the query and position the cursor just before the first row
• FETCH — retrieve the next row into program variables, and move the cursor forward
• CLOSE — release the cursor and its resources
DECLARE emp_cursor CURSOR FOR
SELECT Name, Salary FROM EMPLOYEE WHERE Dno = 5;
OPEN emp_cursor;
FETCH emp_cursor INTO :name_var, :salary_var;
CLOSE emp_cursor;

9. Stored Procedure Language in SQL — With an Example

What is a Stored Procedure?
A Stored Procedure is a set of SQL statements (with logic like IF/LOOP) that is SAVED inside the
database itself, given a name, and can be CALLED/executed whenever needed — instead of rewriting
the same SQL every time.

Why Use Stored Procedures?

• Reusability — write the logic once, call it many times
• Performance — runs directly on the database server, reducing back-and-forth network calls
• Security — users can be given permission to RUN a procedure without seeing/accessing the
underlying tables directly
Example
CREATE PROCEDURE GiveRaise (IN emp_ssn CHAR(9), IN raise_amt DECIMAL(10,2))
BEGIN
UPDATE EMPLOYEE
SET Salary = Salary + raise_amt
WHERE SSN = emp_ssn;
END;
Calling the procedure:
CALL GiveRaise('123456789', 5000);
This single CALL statement runs the entire UPDATE logic stored inside the procedure — much simpler
than writing the full UPDATE statement every single time.

10. Transition Diagram of a Transaction — With Neat Diagram

(Same concept as Q2's transaction states — here's the focused diagram explanation)

The Diagram
[BEGIN_TRANSACTION]
↓
[ACTIVE] ← (read/write operations happen repeatedly here)
/ \
(end_transaction) (some operation fails)
↓ ↓
[PARTIALLY COMMITTED] [FAILED]
↓ ↓
[COMMITTED] [ABORTED]
↓ ↓
[TERMINATED] ←———————————┘

Explanation of Each Transition

• BEGIN_TRANSACTION → ACTIVE: the transaction starts executing
• ACTIVE → PARTIALLY COMMITTED: triggered by the end_transaction operation, after the
LAST statement runs
• PARTIALLY COMMITTED → COMMITTED: the system confirms the transaction can be
permanently recorded
• ACTIVE/PARTIALLY COMMITTED → FAILED: triggered if any operation fails, or the system
detects an error
• FAILED → ABORTED: the transaction is ROLLED BACK, undoing any changes already made
• COMMITTED/ABORTED → TERMINATED: the transaction has fully exited the system

MODULE 5 — Locking, Deadlock Prevention, NoSQL Systems

1. Two-Phase Locking Protocol (2PL) and How It Guarantees Serializability
What is 2PL?
A transaction follows the Two-Phase Locking protocol if ALL its LOCKING operations happen BEFORE
its FIRST UNLOCKING operation. Every transaction is split into 2 clear phases:
• Growing Phase: The transaction can ONLY acquire (get) new locks — it cannot release any
lock yet
• Shrinking Phase: The transaction can ONLY release locks — it cannot acquire any new lock
anymore

How It Guarantees Serializability

Because every transaction must finish ALL its locking BEFORE releasing ANY lock, two transactions
cannot 'interleave' their conflicting operations in an inconsistent way. This forces the schedule to
behave AS IF the transactions ran one after another (serially), even though they were technically
running at overlapping times.
In simple words: 2PL prevents a transaction from grabbing a NEW lock once it has started giving up old
ones — this strict ordering rule is mathematically proven to always produce a CONFLICT
SERIALIZABLE schedule.

Simple Example
T1: Lock-X(A), Read(A), Write(A), Lock-X(B), Read(B), Write(B), Unlock(A), Unlock(B)
T2: Lock-S(A), Read(A), Unlock(A)
If T2 wants to lock A while T1 still holds it, T2 must WAIT until T1 enters its shrinking phase and
releases the lock. This forced waiting makes the final result EQUIVALENT to running T1 completely,
then T2 — a serial order.

Variants of 2PL
Variant Rule
Basic 2PL Locks can be released any time during the shrinking phase
Strict 2PL Write locks are held until COMMIT/ABORT (prevents cascading rollbacks)
Rigorous 2PL BOTH read and write locks held until COMMIT/ABORT (strictest, simplest to
manage)

2 & 9. Wait-Die and Wound-Wait Protocols for Deadlock Prevention

Why Deadlock Prevention is Needed
A DEADLOCK happens when two (or more) transactions are stuck waiting for each other FOREVER —
each one holds a lock the other needs. Deadlock Prevention protocols stop this from EVER happening,
by using transaction TIMESTAMPS (which transaction started first) to decide who waits and who gets
aborted.

Wait-Die Protocol (Non-preemptive)

Rule: If transaction Ti requests a lock held by Tj:
• If Ti is OLDER than Tj → Ti is allowed to WAIT
• If Ti is YOUNGER than Tj → Ti is ABORTED ('dies') and restarted later
Simple memory trick: 'Old waits, Young dies'
Wound-Wait Protocol (Preemptive)
Rule: If transaction Ti requests a lock held by Tj:
• If Ti is OLDER than Tj → Ti FORCES Tj to abort ('wounds' it) and takes the lock
• If Ti is YOUNGER than Tj → Ti WAITS for Tj to finish
Simple memory trick: 'Old wounds, Young waits'

Quick Comparison
Protocol If Requester is OLDER If Requester is YOUNGER
Wait-Die Waits Aborted (dies)
Wound-Wait Forces other to abort (wounds) Waits
Both methods guarantee NO deadlock can ever form, because the 'direction' of waiting always depends
consistently on age — preventing any circular waiting pattern.

3 & 9. What is NoSQL? CAP Theorem, 4 Major Categories, and Graph

Database
What is NoSQL?
NoSQL ('Not Only SQL') databases are a category of databases that do NOT follow the traditional
relational (table-based) model. They are designed to handle LARGE volumes of unstructured/semi-
structured data with HIGH scalability and flexibility.

CAP Theorem
Says that a distributed database CANNOT guarantee all 3 of the following AT THE SAME TIME — only
2 out of 3:
Property Meaning
Consistency (C) Every read gets the most up-to-date data
Availability (A) Every request gets SOME response (system never goes down)
Partition Tolerance (P) System keeps working even if network communication breaks between servers
Since network partitions WILL happen eventually, real systems must choose between Consistency and
Availability when one occurs.

4 Major Categories of NoSQL Systems

Category Description Example
Key-Value Store Simplest type — stores data as simple key-value pairs Redis, DynamoDB
Document Store Stores data as flexible 'documents' (like JSON) MongoDB, CouchDB
Column-Family Store Stores data in columns (good for analytics on huge Cassandra, HBase
datasets)
Graph Database Stores data as nodes and edges, optimized for Neo4j
relationships

Graph Database
A Graph Database stores data as NODES (entities) and EDGES (relationships between them), making
it extremely fast for queries involving CONNECTED data — like social networks, recommendation
systems, fraud detection.
Example: In Neo4j, a query to find 'friends of friends' is much faster than doing the equivalent multi-
table JOIN in a relational database.

4. Multiple Granularity Locking — How It's Implemented Using Intension

Locks
What is Multiple Granularity Locking?
Instead of locking ONLY at one fixed level (like a single row), Multiple Granularity Locking allows
locking at DIFFERENT SIZES/LEVELS — the WHOLE database, a TABLE, a PAGE, or a single
RECORD — arranged like a TREE (hierarchy).
Granularity Hierarchy (biggest to smallest):
Database → Table → Page → Record

Why We Need Intension Locks

If a transaction wants to lock a SMALL item (like one record), the system needs an EFFICIENT way to
check if that conflicts with a lock someone else holds on a BIGGER item (like the whole table) ABOVE
it. Intension Locks solve this by 'announcing' an intention to lock something below, BEFORE actually
locking it.

The 3 Intension Lock Modes

Lock Mode Meaning
IS (Intention Shared) I intend to place SHARED locks somewhere below this node
IX (Intention Exclusive) I intend to place EXCLUSIVE locks somewhere below this node
SIX (Shared + Intention Exclusive) This node itself is locked SHARED, AND I intend to exclusively lock
something below it

How It Works (Rule)

A transaction must lock the ROOT (top) of the tree FIRST in the right intension mode, and move
DOWNWARD — it can only lock a child node if it already holds the PARENT locked in a compatible
mode.
Example: To lock a single RECORD in EXCLUSIVE mode, the transaction must first get IX locks on the
DATABASE and the TABLE containing that record, THEN finally get the EXCLUSIVE lock on the
record itself.
This way, another transaction wanting to lock the WHOLE TABLE can quickly see the IX lock and know
'something below is locked' — without having to check every single record individually.

5. MongoDB CRUD Operations: Insert, Delete, Read — With Formats

What is CRUD?
CRUD stands for Create, Read, Update, Delete — the 4 basic operations every database needs to
support.

(i) Insert
Format: [Link]({ field: value, ... });
[Link]({ name: "Alice", dept: "IT", salary: 50000 });
Format (multiple): [Link]([{...}, {...}]);
[Link]([{name:"Bob",dept:"HR"}, {name:"Eve",dept:"IT"}]);

(ii) Delete
Format: [Link]({ condition });
[Link]({ name: "Bob" });
Format (multiple): [Link]({ condition });
[Link]({ dept: "HR" });

(iii) Read (Find)

Format: [Link]({ condition });
[Link]({ dept: "IT" });
[Link]({ salary: { $gt: 40000 } }); // salary greater than 40000
Format (single document only): [Link]({ condition });
[Link]({ name: "Alice" });

6. Neo4j Data Model — Brief Discussion

What is Neo4j?
Neo4j is a popular GRAPH DATABASE — it stores data as NODES (representing entities) and
RELATIONSHIPS/EDGES (representing connections between them), instead of rows and tables.

Key Components of the Neo4j Data Model

Component Description
Node Represents an ENTITY (like a Person, Product, City). Can have LABELS
(categories) and PROPERTIES (key-value attributes)
Relationship Connects two nodes, has a TYPE (e.g., 'FRIENDS_WITH'), and can also have
properties (like 'since: 2020')
Label A way to group/categorize nodes (e.g., :Person, :Movie)
Property Key-value pairs that store actual data on nodes or relationships (e.g., name:
'John')

Example
Imagine two People nodes, Alice and Bob, connected by a relationship FRIENDS_WITH:
(Alice:Person {name:'Alice', age:28}) -[:FRIENDS_WITH {since:2020}]-> (Bob:Person
{name:'Bob', age:30})
This is read using Neo4j's query language Cypher:
MATCH (a:Person)-[:FRIENDS_WITH]->(b:Person) WHERE [Link]='Alice' RETURN [Link];
Why Graph Models Help: Finding connections (like 'friends of friends' or shortest path between two
nodes) is MUCH faster in Neo4j than doing the equivalent multiple JOINs in a relational database,
because relationships are stored directly, not computed at query time.
7. Define Schedule — Illustrate with an Example. What are Document-
Based NoSQL Systems?
What is a Schedule?
A Schedule is the ORDER in which the operations (read/write) of multiple transactions are EXECUTED,
when they run CONCURRENTLY (at the same time, interleaved).

Example Schedule
Suppose T1 and T2 run concurrently. A possible SCHEDULE (interleaving) of their operations might
be:
Schedule S: T1:Read(A), T2:Read(A), T1:Write(A), T2:Write(A), T1:Read(B),
T2:Read(B)
This schedule shows the operations from BOTH transactions mixed together in a specific time order —
this is what 'a schedule' means.
A SERIAL schedule, by contrast, would run T1 COMPLETELY first, then T2 completely — no
interleaving at all:
Serial Schedule: T1:Read(A), T1:Write(A), T1:Read(B), T2:Read(A), T2:Write(A),
T2:Read(B)
Concurrency control aims to make sure even an INTERLEAVED schedule produces the SAME correct
result as SOME serial schedule (this is called 'serializability').

Document-Based NoSQL Systems

A Document-Based NoSQL system stores data as DOCUMENTS (usually in JSON or BSON format),
where each document can have a DIFFERENT structure — unlike rigid relational tables.
Example (MongoDB document):
{ "_id": 1, "name": "John", "skills": ["SQL", "Python"], "address": {"city":
"Bangalore"} }
Key features: flexible schema (no fixed columns required), supports NESTED data (objects/arrays
inside one document), and is great for applications where data structure varies between records.
MongoDB and CouchDB are popular examples.

8. Why Concurrency Control is Needed — Demonstrate with an Example

Why It's Needed
When MULTIPLE transactions run AT THE SAME TIME on a SHARED database, without proper
control, they can interfere with each other and corrupt the data — even though each transaction is
individually correct.

Example: The Lost Update Problem

Suppose Account X has a balance of 100.
Time T1 T2 Value of X
t1 Read X (100) 100

t2 Read X (100) 100

t3 X = X + 50 → 150 100 (not written yet)

t4 Write X (150) 150

t5 X = X + 30 → 130 150
t6 Write X (130) 130

T1 added 50 to X, but T2's later write of 130 OVERWRITES T1's update completely. The final value
(130) is WRONG — it should have reflected BOTH additions (100+50+30=180), but T1's +50 update is
permanently LOST.
This is exactly why Concurrency Control mechanisms (like Locking, covered in earlier questions) are
essential — they prevent two transactions from reading/writing the same item in an unsafe, overlapping
way.

10. Binary Locks — With Lock and Unlock Operations and Algorithm
What is a Binary Lock?
A Binary Lock can be in only ONE of TWO states: 1 (locked) or 0 (unlocked). If a transaction wants to
access an item that is already locked, it must WAIT until it becomes unlocked.
Limitation: It's too simple — it doesn't separate READ access (which could be SHARED by multiple
transactions) from WRITE access (which must be EXCLUSIVE) — even two transactions that only want
to READ the same item must wait for each other unnecessarily.

Lock and Unlock Algorithm

lock_item(X):
while (LOCK(X) = 1):
wait // keep waiting if it's already locked
LOCK(X) = 1 // lock granted, set it to locked

unlock_item(X):
LOCK(X) = 0 // release the lock

Simple Walkthrough Example

• T1 wants to access item X. LOCK(X) is currently 0 (unlocked) → T1 sets LOCK(X)=1 and
proceeds.
• T2 also wants to access X. LOCK(X) is now 1 → T2 must WAIT.
• T1 finishes its work on X and calls unlock_item(X) → LOCK(X) becomes 0.
• T2 can now proceed — it sets LOCK(X)=1 and accesses the item.
Note: This is why MOST real systems use SHARED/EXCLUSIVE locks instead of plain binary locks —
they allow multiple readers at once, improving concurrency while still protecting writes.

— End of Answer Bank —