Database Management System

Indexing is a technique to speed up retrieval of records. An index is a separate structure with entries (search key, pointer to record), like the index of a book, so a record is located without scanning the whole file.

Index sequential file organization

Records are stored in a file sorted on the search key (sequential), and an index is maintained on that key.

• The index is sparse (one entry per block, holding the first key of that block) or dense (one entry per record).
• To find a record: search the index for the largest key $\le$ the target key, follow its pointer to the data block, then scan sequentially within the block.
• For large files a multilevel index is used. New records go to an overflow area and are linked to the main block, which is merged during periodic reorganization.

Index:  10 -> block1 | 40 -> block2 | 70 -> block3
Data:   [10,20,30] [40,50,60] [70,80,90]

Advantages

• Supports both sequential processing (in key order) and fast direct (random) access.
• Faster than pure sequential search; range queries are efficient.
• A sparse index needs little space.

Disadvantages

• Extra space for the index and overflow area.
• Insertion and deletion are slow; performance degrades as overflow grows, so periodic reorganization is needed.
• Index must be updated on every change to the file.

Hash index organizes index entries using a hash function $h(K)$ that maps a search key $K$ to the address of a bucket (a block or chain of blocks) holding the matching index entries or records.

Working

• Insert: compute $h(K)$, store the entry in that bucket.
• Search: compute $h(K)$, go straight to the bucket and look for $K$ there. Typically one disk access.
• Example: $h(K) = K \bmod 5$ puts keys 12 and 27 in bucket 2.

Bucket overflow (when a bucket is full) is handled by overflow chaining or open addressing. A poor hash function or too few buckets causes skew and more overflow.

Types: static hashing (fixed number of buckets) and dynamic hashing, such as extendible hashing, in which the bucket directory grows and shrinks.

Advantages: very fast equality searches, $O(1)$ on average. Disadvantages: unsuitable for range queries or ordered access, since neighbouring keys are scattered; overflow and rehashing reduce performance.

Query optimization is the process of choosing the most efficient execution strategy for a query from the many possible strategies that give the same result. The aim is to minimise cost (disk I/O, CPU time, memory and, in distributed systems, communication) so the answer is returned quickly.

Steps in query processing

1. Parsing and translation: the SQL query is checked for syntax, and the relations and attributes named are verified against the data dictionary (semantic check). It is then translated into an internal form, usually a relational algebra expression or a query tree. Example: SELECT name FROM emp WHERE sal > 5000 becomes $\pi_{name}(\sigma_{sal>5000}(emp))$.

2. Optimization: the optimizer generates equivalent expressions and alternative execution plans.

   • Heuristic rules: perform selection and projection as early as possible, replace cartesian product followed by selection with a join, apply the most restrictive selections first.
   • Cost-based choice: using statistics (number of tuples, block count, index availability, distinct values), it estimates the cost of every plan and selects the cheapest one. This gives the query execution plan.

3. Evaluation (execution): the query execution engine runs the chosen plan, accessing the data through file scans or indexes, performs the operations and returns the result to the user.

SQL query -> Parser/Translator -> Relational algebra
          -> Optimizer (uses statistics) -> Execution plan
          -> Evaluation engine (uses data) -> Output

Query processing is the series of activities a DBMS performs to turn a high-level query (SQL) into low-level operations on the stored data and return the result to the user.

Steps

1. Parsing and translation

   • The scanner breaks the query into tokens; the parser checks syntax.
   • The validator checks that relations and attributes exist in the data dictionary.
   • The query is translated into relational algebra, e.g. SELECT name FROM emp WHERE sal > 5000 becomes $\pi_{name}(\sigma_{sal>5000}(emp))$, represented as a query tree.

2. Optimization

   • The optimizer rewrites the expression into equivalent ones (selections and projections performed early, joins ordered well) and picks access methods (index or full scan).
   • It estimates the cost of each plan using catalog statistics and selects the cheapest one, called the execution plan.

3. Evaluation / execution

   • The query execution engine carries out the plan, reading data from disk through the storage manager and applying the operators.
   • The final result is returned to the user.

Query -> Parser & translator -> Relational algebra -> Optimizer -> Execution plan -> Evaluation engine -> Result
                                                         ^                              ^
                                                    statistics                       database

Data abstraction hides the complex details of how data is stored and gives users only the view they need. A DBMS provides three levels (ANSI/SPARC architecture).

1. Physical (internal) level. The lowest level. It describes how data is physically stored on disk: file organisation, indexes, blocks, record formats, compression. Only the DBA and system programmers deal with it.

2. Logical (conceptual) level. Describes what data is stored and the relationships among them for the whole database: tables, attributes, data types, keys and constraints. The DBA and database designers work at this level. Example: STUDENT(ID, Name, Age, Dept) with ID as the primary key.

3. View (external) level. The highest level; it describes only the part of the database relevant to a user or group. There can be many views of one database, which also provides security. Example: a library clerk sees only (ID, Name), while an accounts officer sees (ID, Name, Fee).

Example. The STUDENT table is stored as blocks in a file with a B-tree index on ID (physical); it is defined as a relation with attributes and constraints (logical); and a view CREATE VIEW names AS SELECT ID, Name FROM STUDENT is given to the clerk (view).

Data independence. It is the ability to change the schema at one level without changing the schema at the next higher level. The mappings between levels provide it.

• Physical data independence: changing storage structures or adding an index (physical level) does not require changing the logical schema or application programs.
• Logical data independence: changing the logical schema (e.g. adding an attribute) does not require changing the external views or applications. It is harder to achieve.

Hence the three-level architecture is what makes data independence possible.

Two-tier architecture. A client-server architecture in which the client application communicates directly with the database server.

+-----------------------+        SQL / ODBC / JDBC        +------------------+
| Client tier           | ------------------------------> | Database tier    |
| (UI + business logic) | <------------------------------ | (DBMS server)    |
+-----------------------+            results             +------------------+

• Client holds the user interface and application logic; server stores and manages data.
• Advantages: simple, fast development, low overhead.
• Disadvantages: business logic is on each client (hard to maintain/update), poor scalability with many users, weaker security because clients access the database directly.
• Example: a desktop application connecting directly to MySQL.

Three-tier architecture. An application server (middle tier) is placed between the client and the database.

+----------------+     +-----------------------+     +------------------+
| Presentation   | --> | Application tier      | --> | Data tier        |
| tier (browser, |     | (business logic, web  |     | (DBMS / database)|
| GUI)           | <-- | server, validation)   | <-- |                  |
+----------------+     +-----------------------+     +------------------+

• Presentation tier: user interface only.
• Application (business) tier: processes requests, applies business rules, talks to the database.
• Data tier: the DBMS that stores data.
• Advantages: better security (clients never touch the database), scalability, easier maintenance because logic is centralised, tiers can be changed independently, load balancing.
• Disadvantages: more complex and costly to design and deploy.
• Example: web applications (browser, web/app server, database).

Comparison: two-tier has direct client-database communication and suits small systems; three-tier adds a middle layer and suits large, web-based, multi-user systems.

Entities and attributes

• Patient (<u>PatientID</u>, Name, Age, Gender, Address, Phone)
• Doctor (<u>DoctorID</u>, Name, Specialization, Phone)
• Appointment (<u>AppointmentID</u>, Date, Time, Purpose)
• Room (<u>RoomNo</u>, Type, Capacity, Charge)

Relationships and cardinalities

• Patient books Appointment: one patient can have many appointments; each appointment belongs to one patient (1 : N).
• Doctor attends Appointment: one doctor handles many appointments; each appointment has one doctor (1 : N).
• Patient is admitted to Room: a room can hold many patients over time (and a ward room several at once); a patient is in one room at a time (N : 1). With admission date and discharge date as relationship attributes.

ER diagram (text form)

 [Patient]--1-----<books>-----N--[Appointment]--N-----<attends>-----1--[Doctor]
     |
     N
   <admitted to>   (AdmitDate, DischargeDate)
     |
     1
  [Room]

Rectangles are entities, diamonds are relationships, ovals are attributes (key attributes underlined). Appointment can be treated as the associative entity linking Patient and Doctor, giving the effective many-to-many relationship Patient-Doctor resolved through Appointment (M : N overall). A Doctor may also be assigned to a Room (e.g. consulting room) as an optional 1 : N link.

Mapping to tables: Patient(PatientID, ...), Doctor(DoctorID, ...), Room(RoomNo, ...), Appointment(AppointmentID, PatientID FK, DoctorID FK, Date, Time), Admission(PatientID FK, RoomNo FK, AdmitDate, DischargeDate).

Notation: $\pi$ = projection, $\sigma$ = selection, $\bowtie$ = natural join, $-$ = set difference, $\mathcal{G}$ = aggregate.

(i) Names of students enrolled in "Database Systems"

$\pi_{Name}\big(Student \bowtie Enroll \bowtie \sigma_{CName='Database\ Systems'}(Course)\big)$

(ii) Students (SID, Name) not enrolled in any course

$\pi_{SID,Name}(Student) - \pi_{SID,Name}(Student \bowtie Enroll)$

Equivalently, $\pi_{SID,Name}\big(Student \bowtie (\pi_{SID}(Student) - \pi_{SID}(Enroll))\big)$.

(iii) Courses with more than 3 credits

$\pi_{CID,CName}\big(\sigma_{Credits > 3}(Course)\big)$

(iv) Total number of students in each department

$_{Dept}\mathcal{G}_{COUNT(SID)}(Student)$

which returns, for each Dept, the count of SID values.

View. A view is a virtual table defined by a stored SQL query on one or more base tables. It does not store data itself (except materialised views); the data is produced from the underlying tables whenever the view is queried.

Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

DROP VIEW view_name;

Example

CREATE VIEW CS_Students AS
SELECT SID, Name FROM Student WHERE Dept = 'CS';

SELECT * FROM CS_Students;

Importance of views

1. Security: users see only permitted rows/columns (e.g. hide salary), and privileges can be granted on the view instead of the table.
2. Simplicity: complex joins and queries are stored once and reused as a simple table.
3. Logical data independence: applications use the view, so changes in base-table structure need only a modified view definition.
4. Customised presentation: different users get different views of the same data.
5. Consistency: a single definition of a calculation or join used by everyone.
6. Reduced redundancy: no duplicate copy of data is stored.

Simple views (single table, no aggregation) can also be updated via INSERT/UPDATE/DELETE, which pass through to the base table.

JOIN. A JOIN combines rows from two or more tables based on a related column (usually primary key and foreign key), so that related data can be retrieved in one query.

Sample tables: Student(SID, Name, DeptID) and Dept(DeptID, DName).

1. INNER JOIN - returns only rows with matching values in both tables.

SELECT s.Name, d.DName
FROM Student s INNER JOIN Dept d ON s.DeptID = d.DeptID;

2. LEFT (OUTER) JOIN - all rows of the left table plus matching rows of the right; unmatched right columns are NULL.

SELECT s.Name, d.DName
FROM Student s LEFT JOIN Dept d ON s.DeptID = d.DeptID;

3. RIGHT (OUTER) JOIN - all rows of the right table plus matching left rows.

SELECT s.Name, d.DName
FROM Student s RIGHT JOIN Dept d ON s.DeptID = d.DeptID;

4. FULL (OUTER) JOIN - all rows from both tables; NULL where there is no match.

SELECT s.Name, d.DName
FROM Student s FULL OUTER JOIN Dept d ON s.DeptID = d.DeptID;

5. CROSS JOIN - Cartesian product: every row of one table with every row of the other ($m \times n$ rows).

SELECT s.Name, d.DName FROM Student s CROSS JOIN Dept d;

6. NATURAL JOIN - joins automatically on columns with the same name.

SELECT * FROM Student NATURAL JOIN Dept;

7. SELF JOIN - a table joined with itself using aliases, e.g. employees and their managers.

SELECT e.Name, m.Name AS Manager
FROM Employee e JOIN Employee m ON e.MgrID = m.EmpID;

(i) Create table Employees

CREATE TABLE Employees (
    EmpID    INT PRIMARY KEY,
    Name     VARCHAR(50) NOT NULL,
    DeptID   INT,
    Salary   DECIMAL(10,2) CHECK (Salary > 0),
    JoinDate DATE NOT NULL,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

(ii) Name and Salary of employees of the 'IT' department

SELECT e.Name, e.Salary
FROM Employees e
JOIN Departments d ON e.DeptID = d.DeptID
WHERE d.DeptName = 'IT';

(iii) Increase IT salaries by 10%

UPDATE Employees
SET Salary = Salary * 1.10
WHERE DeptID = (SELECT DeptID FROM Departments WHERE DeptName = 'IT');

(iv) Delete projects with budget less than 40,000

DELETE FROM Projects
WHERE Budget < 40000;

Integrity constraints are rules enforced by the DBMS on the data in a database so that it remains accurate, consistent and valid. They prevent invalid data from being inserted, updated or deleted.

Types

1. Domain constraints: each attribute value must belong to its domain (data type, range, format). Enforced by data types, CHECK, NOT NULL, DEFAULT. Example: Age INT CHECK (Age BETWEEN 0 AND 120).
2. Key constraints: a key (primary key / candidate key) uniquely identifies each tuple; UNIQUE ensures no duplicate values.
3. Entity integrity: the primary key of a relation cannot be NULL (and must be unique), so every row is identifiable.
4. Referential integrity: a foreign key value must either be NULL or match an existing primary key value in the referenced table.
5. Not-null and check constraints: a column must have a value; a condition must hold.
6. Semantic/business constraints and assertions/triggers: general rules such as "balance cannot go below zero".

Example

CREATE TABLE Dept (
    DeptID INT PRIMARY KEY,
    DName  VARCHAR(30) UNIQUE NOT NULL
);
CREATE TABLE Student (
    SID    INT PRIMARY KEY,
    Name   VARCHAR(50) NOT NULL,
    Age    INT CHECK (Age >= 16),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Dept(DeptID)
        ON DELETE SET NULL ON UPDATE CASCADE
);

Referential actions on delete/update of the parent row: CASCADE, SET NULL, SET DEFAULT, RESTRICT/NO ACTION.

Importance: ensures data accuracy and consistency, prevents orphan records and duplicates, and reduces the need for validation in application code.

Domain integrity constraint. Every value of an attribute must come from the attribute's defined domain (data type, allowed range or set of values, format). It is enforced with data types, CHECK, NOT NULL and DEFAULT. Example: Gender CHAR(1) CHECK (Gender IN ('M','F','O')); a value 'X' or Age = -5 is rejected.

Entity integrity constraint. The primary key of a relation cannot be NULL and must be unique, because the key identifies each tuple (entity). Example: in Student(SID, Name) with SID as primary key, the row (NULL, 'Ram') or a second row with SID = 1 is not allowed.

Referential integrity constraint. A foreign key in a referencing table must either be NULL or match the primary key value of a tuple in the referenced table. This keeps relationships between tables consistent. Example:

CREATE TABLE Dept (DeptID INT PRIMARY KEY, DName VARCHAR(30));
CREATE TABLE Student (
    SID INT PRIMARY KEY,
    Name VARCHAR(50),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Dept(DeptID)
);

Inserting a student with DeptID = 9 when no such department exists is rejected. Deleting a department that students reference is blocked, or handled by ON DELETE CASCADE or SET NULL.

Denormalization is the deliberate process of introducing controlled redundancy into a normalised database, by combining tables or adding duplicate/derived columns, in order to improve read (query) performance. It is the reverse of normalisation.

Example. Normalised: Orders(OrderID, CustID) and Customer(CustID, CName). Every report on orders needs a join. Denormalised: Orders(OrderID, CustID, CName) stores the customer name in each order, so no join is needed.

Why it is used

• Joins across many tables are expensive; fewer joins give faster queries.
• Reduces complexity of queries and the number of table accesses.
• Precomputed/derived values (totals, counts) avoid repeated calculation.
• Improves performance of reporting and read-heavy systems.

When it is used

• Read-heavy workloads such as data warehouses, OLAP and reporting systems, where updates are rare.
• Frequently executed queries that need data from many tables.
• When performance requirements cannot be met by indexing or tuning alone.
• When calculated values are accessed far more often than they change.

Techniques: adding redundant columns, storing derived columns, merging tables (one-to-one or one-to-many), pre-joined tables, summary tables and materialised views.

Disadvantages

• Data redundancy and more storage.
• Risk of update, insertion and deletion anomalies and inconsistent data.
• Updates become slower and more complex (several copies must be changed).
• More code needed to keep copies consistent.

Denormalisation should therefore be applied only after normalising and only where measured performance justifies it.

Conflict serializability. A schedule is the interleaved order of operations of concurrent transactions. Two operations conflict if they belong to different transactions, access the same data item and at least one is a write (read-write, write-read, write-write). A schedule is conflict serializable if it can be transformed into a serial schedule by swapping adjacent non-conflicting operations; that is, it is conflict equivalent to some serial schedule.

Test using a precedence graph.

1. Create a node for each transaction.
2. Add an edge $T_i \to T_j$ if an operation of $T_i$ conflicts with and precedes an operation of $T_j$.
3. The schedule is conflict serializable iff the graph has no cycle. A topological order of the graph gives an equivalent serial schedule.

Example: $R_1(A), W_1(A), R_2(A), W_2(A)$ gives only the edge $T_1 \to T_2$; no cycle, so it is equivalent to $T_1, T_2$. The schedule $R_1(A), W_2(A), W_1(A)$ gives $T_1 \to T_2$ and $T_2 \to T_1$, a cycle, so it is not conflict serializable.

Lock-based protocol: Two-Phase Locking (2PL). Locks are of two modes: shared (S, for reading) and exclusive (X, for writing). A transaction must obtain the appropriate lock before accessing an item. 2PL requires each transaction to work in two phases:

1. Growing phase: the transaction may acquire locks but may not release any.
2. Shrinking phase: once it releases a lock, it may release locks but cannot acquire new ones.

The point where it holds all its locks is the lock point. 2PL guarantees conflict serializability (the serial order follows the order of lock points).

Variants: Strict 2PL holds all exclusive locks until commit/abort (avoids cascading rollbacks); Rigorous 2PL holds all locks until commit.

Drawbacks: it can cause deadlock (handled by detection, prevention or timeouts) and reduces concurrency.

Query optimization is the process by which the DBMS chooses the most efficient execution plan (the one with least cost in I/O, CPU and memory) for a query from the many possible equivalent plans.

Need

• A single SQL query can be executed in many ways whose costs differ enormously (e.g. joining large tables in the wrong order).
• Reduces response time and resource usage; improves throughput for many users.
• Users write declarative SQL and should not need to decide the access strategy.

Steps

1. Parsing and translation: the SQL query is checked for syntax and semantics (tables, columns, privileges) and converted into an internal form, a relational algebra expression / query tree.
2. Optimization:
   • Logical (heuristic) optimization: apply equivalence rules, e.g. perform selections early, perform projections early, replace Cartesian product plus selection by joins, reorder joins so smallest results come first.
   • Physical (cost-based) optimization: generate alternative plans (choice of algorithms such as nested-loop, merge or hash join; use of indexes or full scans) and estimate each plan's cost using statistics from the catalog (table size, number of distinct values, index availability, selectivity).
   • Select the plan with the lowest estimated cost.
3. Code generation / execution plan: the chosen plan is turned into an executable access plan.
4. Execution: the query-execution engine runs the plan and returns the result.

Example. For $\pi_{Name}(\sigma_{Dept='CS'}(Student \bowtie Enroll))$, pushing the selection down, $\pi_{Name}(\sigma_{Dept='CS'}(Student) \bowtie Enroll)$, joins far fewer tuples.

Checkpoint. A checkpoint is a point in time at which the DBMS writes all modified buffers (dirty pages) and log records to disk and records a <checkpoint> entry in the log. During recovery, the system need only examine transactions active at or after the last checkpoint, not the whole log, so recovery is faster.

Transaction rollback. Rollback is undoing all changes made by a transaction that fails or is aborted, restoring the database to its state before the transaction began (atomicity). It is done using the log's old values: the log is scanned backwards and the old value of each updated item is written back.

Log-based recovery. The log is a sequential file of records such as <T, start>, <T, X, V_old, V_new> (an update), <T, commit> and <T, abort>. It is written before the database is changed (write-ahead logging).

Algorithm (deferred / immediate modification, with checkpoint)

1. Scan the log backwards to find the most recent <checkpoint L> record, where L is the list of active transactions.
2. Build two lists: redo-list (transactions with both <T, start> and <T, commit>) and undo-list (transactions with <T, start> but no commit or abort).
3. Undo phase: scan the log backwards from the end; for every update record of a transaction in the undo-list, restore the old value $V_{old}$ and write a compensation record; when <T, start> is reached write <T, abort>.
4. Redo phase: scan the log forwards from the checkpoint; for every update record of a transaction in the redo-list, set the item to the new value $V_{new}$.
5. Recovery completes when all transactions in the lists are handled.

Outcome: committed transactions are durable (redo) and uncommitted ones leave no effects (undo). In deferred modification only redo is needed, because the database is updated only after commit.

Advantages of ORM (Object-Relational Mapping) databases/tools

1. Higher productivity: developers work with objects and classes instead of writing SQL for each operation.
2. Less boilerplate: CRUD code is generated automatically.
3. Database independence: the same code can run on different DBMSs by changing configuration.
4. Maintainability: the data model is described once in code; schema changes are easier to manage.
5. Object-oriented design: bridges the mismatch between object models and relational tables (inheritance, associations).
6. Security: parameterised queries reduce SQL-injection risk.
7. Caching and lazy loading improve performance; transaction handling is built in.

(Examples: Hibernate, Entity Framework, Django ORM.)

NoSQL vs relational databases

Relational databases suit applications needing integrity and complex queries; NoSQL suits large-scale, rapidly changing, distributed data.

A failure is an event that stops a transaction or the DBMS from working normally. The DBMS must recover the database to a consistent state. Failures are classified as:

1. Transaction failure
   • Logical error: the transaction cannot continue due to bad input, overflow, divide by zero or an internal condition.
   • System error: the DBMS must terminate an active transaction, e.g. because of deadlock.
2. System crash: a hardware or software failure (power loss, OS crash, DBMS bug) that loses the contents of volatile memory (main memory) but not non-volatile storage. This is the "fail-stop" assumption.
3. Disk failure: a head crash or media failure destroys data on disk, requiring recovery from backups/archives and logs.
4. Other failures: communication/network failure in distributed systems, and natural disasters (fire, flood) needing off-site backup.

Recovery uses logs, checkpoints, backups, shadow paging and transaction rollback/redo to maintain atomicity and durability.

Database schema. The schema is the overall design or description of the database: its tables, attributes, data types, keys, relationships and constraints. It is specified during database design and changes rarely. It is also called the intension of the database. Schemas exist at the physical, logical and view levels (the logical schema is usually meant). Example: STUDENT(SID, Name, Age, Dept).

Database instance. The instance is the actual collection of data stored in the database at a particular moment (a snapshot). It changes frequently as data is inserted, updated and deleted. It is the extension of the database. Example: the rows (1, Ram, 20, CS) and (2, Sita, 21, IT) currently in STUDENT.

Difference: the schema is the structure (like a variable's declaration or type); the instance is the content at a point in time (like its current value). Every instance must conform to the schema.

Generalization. A bottom-up design process in which two or more lower-level entity sets sharing common attributes are combined into a higher-level (super-class) entity set. It expresses an "is-a" relationship. Example: Car and Truck (with common attributes RegNo, Model) are generalised to Vehicle. The lower-level entities inherit the attributes of the higher-level one. The reverse (top-down) process is specialization.

Aggregation. An abstraction in which a relationship set between entities is treated as a higher-level entity so it can take part in another relationship. It expresses a "has-a / part-of" relationship and removes the limitation that ER relationships are only between entities. Example: Employee works-on Project is aggregated, and then a Manager entity "manages" that works-on relationship.

Comparison

Database. A database is an organised collection of related data stored so that it can be easily accessed, managed and updated.

DBMS (Database Management System). A DBMS is software that defines, creates, stores, retrieves, updates and controls access to a database, e.g. MySQL, Oracle, SQL Server.

Advantages of DBMS over a file management system

1. Reduced data redundancy: data is stored once and shared; files often duplicate data across applications.
2. Data consistency: because duplicates are minimised, all users see the same, up-to-date values.
3. Data independence: programs are insulated from changes in storage structure; in file systems, formats are embedded in application code.
4. Data sharing: multiple users and applications can access data concurrently.
5. Data integrity: integrity constraints (keys, domains, foreign keys) are enforced centrally.
6. Security: authentication, authorisation, and privileges per user/view; file systems offer only basic OS permissions.
7. Backup and recovery: automatic logging, checkpoints and recovery from failures.
8. Concurrency control: locking and transactions (ACID) avoid lost updates.
9. Efficient querying: SQL and indexing allow ad-hoc queries without writing new programs.
10. Standards enforcement and lower application development cost.

Disadvantages (for balance): higher cost of software and hardware, complexity, and the need for trained staff.

Entities and attributes

• Customer (<u>CustID</u>, Name, Phone, Address)
• Restaurant (<u>RestID</u>, Name, Location, Phone)
• Menu_Item (<u>ItemID</u>, Name, Price, Category)
• Order (<u>OrderID</u>, Date, Time, TotalAmount, Status)
• Payment (<u>PaymentID</u>, Amount, Date, Mode)
• Person (generalised): (<u>PersonID</u>, Name, Phone, Email) with specializations
  • Customer (adds DeliveryAddress)
  • Delivery_Staff (adds VehicleNo, Availability)
  • Admin (adds Role)

Generalization/specialization. Customer, Delivery_Staff and Admin are specialised from the super-class Person (total, disjoint "is-a" hierarchy) and inherit PersonID, Name, Phone and Email. Similarly Payment can be specialised into Card_Payment (CardNo) and Cash_Payment (ChangeGiven) or Online_Wallet.

Relationships and cardinalities

• Customer places Order (1 : N).
• Order contains Menu_Item (M : N, attribute Quantity).
• Restaurant offers Menu_Item (1 : N).
• Restaurant receives Order (1 : N).
• Delivery_Staff delivers Order (1 : N).
• Order has Payment (1 : 1).

ER diagram (text form)

            [Person]
               |  (is-a, disjoint, total)
     +---------+-----------+
 [Customer] [Delivery_Staff] [Admin]

[Customer]--1--<places>--N--[Order]--M--<contains>(Qty)--N--[Menu_Item]--N--<offers>--1--[Restaurant]
                              |  N                                                        |
                       <delivered by>                                          <receives>--+--> [Order]
                              |  1
                       [Delivery_Staff]
[Order]--1--<paid by>--1--[Payment] --(is-a)--> [Card_Payment] / [Cash_Payment]

Underlined attributes are primary keys; the triangle (is-a) symbol denotes generalization.

Set operations combine the results of two or more SELECT queries into one result, treating each result as a set of rows. The queries must be union-compatible: the same number of columns, with compatible data types in corresponding positions. The operations are UNION, INTERSECT and EXCEPT/MINUS (set difference). In relational algebra these are $\cup$, $\cap$ and $-$.

Sample tables: Student_CS(ID, Name) and Student_IT(ID, Name).

UNION ($\cup$). Returns all rows that appear in either query, with duplicates removed. UNION ALL keeps duplicates.

SELECT ID, Name FROM Student_CS
UNION
SELECT ID, Name FROM Student_IT;

If CS = {1 Ram, 2 Sita} and IT = {2 Sita, 3 Hari}, the result is {1 Ram, 2 Sita, 3 Hari}.

INTERSECTION ($\cap$). Returns only the rows common to both queries.

SELECT ID, Name FROM Student_CS
INTERSECT
SELECT ID, Name FROM Student_IT;

Using the same data, the result is {2 Sita}.

EXCEPT / MINUS ($-$). Rows in the first query but not in the second; here {1 Ram}.

Note: MySQL lacks INTERSECT in older versions and can emulate it with INNER JOIN or IN.

(i) Courses in which 'Sujan' is enrolled

SELECT c.course_name
FROM Students s
JOIN Enrollments e ON s.student_id = e.student_id
JOIN Courses c ON e.course_id = c.course_id
WHERE s.name = 'Sujan';

(ii) Number of students enrolled in each course

SELECT c.course_name, COUNT(e.student_id) AS num_students
FROM Courses c
LEFT JOIN Enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.course_name;

(iii) Average number of credits taken by students (average credits of the courses students are enrolled in, per student)

SELECT AVG(total_credits) AS avg_credits
FROM (
    SELECT e.student_id, SUM(c.credits) AS total_credits
    FROM Enrollments e
    JOIN Courses c ON e.course_id = c.course_id
    GROUP BY e.student_id
) t;

(If the average over all enrollments is wanted: SELECT AVG(c.credits) FROM Enrollments e JOIN Courses c ON e.course_id = c.course_id;.)

(iv) Course names starting with 'J'

SELECT course_name FROM Courses WHERE course_name LIKE 'J%';

View. A view is a virtual table defined by a stored query over one or more base tables. It presents data without storing it separately.

CREATE VIEW emp_public AS
SELECT EmpID, Name, DeptID FROM Employees;

Applications of views

1. Security and access control: hide sensitive columns (salary, password) or rows; grant users access to the view rather than the base table.
2. Simplifying complex queries: a multi-table join or aggregation is saved as a view, and users query it like a table.
3. Logical data independence: if base tables are restructured, the view definition is updated and applications continue unchanged.
4. Customised user views: different departments see only the data relevant to them.
5. Reporting and summaries: views that aggregate data (totals per department) give ready-made reports.
6. Data integrity and consistency: a view with WITH CHECK OPTION ensures that inserts/updates through it satisfy the view's condition.
7. Backward compatibility: a view can mimic an old table structure after schema changes.
8. Materialised views store computed results to speed up data-warehouse queries.

Limitations: views with joins, aggregates or DISTINCT are generally not updatable, and complex views can be slow.

Functional dependencies (Name is the primary key)

• $Name \to Address, Phone, Salary, Post$ (key).
• $Address \to Phone$ (KTM always has 11111, BKT always 22222).
• $Post \to Salary$ (Engineer 20000, Overseer 15000, Officer 10000).

Because non-key attributes Phone and Salary depend on another non-key attribute (Address and Post respectively), these are transitive dependencies: $Name \to Address \to Phone$ and $Name \to Post \to Salary$. The relation is in 2NF (the key is a single attribute) but not in 3NF.

Conversion to 3NF (remove the transitive dependencies)

R1 (Name, Address, Post)

R2 (Address, Phone)

R3 (Post, Salary)

Each non-key attribute now depends only on the key of its relation (Name, Address or Post), so all three are in 3NF, and the join of R1, R2 and R3 reproduces the original table (lossless).

Anomalies in the original (2NF) relation

• Insertion anomaly: a new post, e.g. "Manager" with salary 30000, or a new address with its phone, cannot be recorded until some employee exists for it (Name, the key, would be NULL).
• Update anomaly: the phone of KTM (11111) is stored in three rows (A, C, E). Changing it in one row only creates inconsistency; likewise changing the Engineer salary requires updating A, B and C.
• Deletion anomaly: deleting employee D removes the only record of the post "Overseer" with salary 15000; deleting E removes "Officer" and 10000.

After decomposition to 3NF, the Address-Phone and Post-Salary facts are stored once, and these anomalies no longer occur.

Constraints are rules defined on a database that every stored value must satisfy. They are enforced automatically by the DBMS on every insert, update and delete.

Why we need them

• To keep data accurate, valid and consistent (e.g. age cannot be negative).
• To prevent duplicate or missing key values.
• To keep relationships between tables meaningful (no orphan records).
• To enforce business rules centrally, instead of in every application.

Types of integrity constraints

(a) Domain constraint: every value of an attribute must come from its defined domain (data type, range, format). Example: age INT CHECK (age >= 18 AND age <= 60).

(b) Entity integrity constraint: the primary key of a relation can never be NULL, and must be unique. This guarantees each tuple is identifiable. Example: student(roll_no PRIMARY KEY, name); a row with roll_no = NULL is rejected.

(c) Key (uniqueness) constraint: no two tuples may have the same value for a candidate key. Example: email VARCHAR(50) UNIQUE.

(d) Referential integrity constraint: a foreign key value in the child table must either be NULL or match an existing primary key value in the parent table. Example:

CREATE TABLE department(dept_id INT PRIMARY KEY, dname VARCHAR(30));
CREATE TABLE employee(
  eid INT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  dept_id INT REFERENCES department(dept_id)
);

Inserting an employee with dept_id = 99 fails if department 99 does not exist. Deleting a department that is referenced is rejected, or cascaded (ON DELETE CASCADE).

(e) Not-null constraint: the attribute must always have a value. Example: name VARCHAR(30) NOT NULL.

(f) Check / semantic constraint: a general condition on values, e.g. salary > 0.

Authentication vs authorization

Cryptography

Encryption converts readable plaintext into unreadable ciphertext using a key; decryption converts it back.

Private key (symmetric) cryptography

• The same secret key $K$ is used by sender and receiver.
• Encryption: $C = E_K(P)$. Decryption: $P = D_K(C)$.
• The sender encrypts with $K$, sends the ciphertext, and the receiver decrypts with the same $K$.
• Fast, but the key must be shared securely. Examples: DES, AES.

Public key (asymmetric) cryptography

• Each user has a key pair: a public key (known to all) and a private key (kept secret).
• For confidentiality, the sender encrypts with the receiver's public key: $C = E_{pub}(P)$. Only the receiver can decrypt with their private key: $P = D_{priv}(C)$.
• For a digital signature, the sender encrypts with their own private key and anyone can verify with the public key.
• No prior secret sharing is needed, but it is slower. Example: RSA.

Causes of failure

• Transaction failure: logical errors (overflow, bad input, division by zero) or system errors such as deadlock, causing the transaction to abort.
• System crash: power failure, hardware or operating-system fault that loses the contents of main memory (volatile storage).
• Disk failure: head crash or bad sectors destroying data on disk.
• Other causes: software bugs, fire/flood/theft, human error, network failure in distributed systems.

Log-based recovery

The DBMS keeps a log on stable storage: a sequence of records of all updates. Records are:

• <Ti, start>
• <Ti, X, V_old, V_new> (transaction Ti changed X from old to new value)
• <Ti, commit> / <Ti, abort>

The log record must be written before the database item is changed (write-ahead logging).

Deferred update: database is modified only after the transaction commits; after a crash, redo committed transactions, ignore uncommitted ones. Immediate update: database may be modified before commit; after a crash, undo uncommitted transactions (using old values) and redo committed ones (using new values).

Example (A = 1000, B = 2000, T1 transfers Rs 50 from A to B):

<T1, start>
<T1, A, 1000, 950>
<T1, B, 2000, 2050>
<T1, commit>

• If the crash happens after <T1, commit>: T1 is redone, so A = 950, B = 2050.
• If the crash happens before <T1, commit> (say after the A update): T1 is undone, so A is restored to 1000 and B stays 2000.

Checkpoints limit the log that must be scanned during recovery.

Concurrency control is the set of mechanisms by which a DBMS manages simultaneous execution of transactions so that they do not interfere with each other, keeping the database consistent and the schedules serializable. It prevents problems such as lost update, dirty read and unrepeatable read.

Two-phase locking (2PL)

Locks are of two kinds: shared (S, for reading) and exclusive (X, for writing). Several transactions may hold S on an item; X is held by only one. In 2PL every transaction acquires and releases locks in two phases:

1. Growing phase: the transaction may obtain locks but may not release any.
2. Shrinking phase: once it releases its first lock, it may release locks but cannot obtain any new one.

The lock point (the moment the last lock is obtained) divides the phases.

Locks held
   ^        /\
   |       /  \
   |      /    \
   +-----/------\----> time
     growing  shrinking

How it controls concurrency: because no transaction can lock after it has unlocked, conflicting operations of different transactions are ordered by their lock points. Hence every schedule allowed by 2PL is conflict-serializable, with no lost updates or inconsistent reads.

Variants: strict 2PL holds all exclusive locks until commit/abort (avoids cascading rollbacks); rigorous 2PL holds all locks until commit.

Limitations: deadlock is possible (T1 waits for T2 and T2 for T1), so deadlock detection/prevention is needed; basic 2PL may also cause cascading rollback.

Object Relational Model (ORM) combines the relational model with object-oriented concepts. An object-relational DBMS (ORDBMS) keeps tables but extends them with features of object technology.

Main features

• User-defined types (UDT) and complex data types (arrays, nested tables, multisets) so a column can hold structured values.
• Inheritance: a type or table can be derived from another and inherit its attributes and methods.
• Methods (behaviour) attached to types, and object identity through references.
• Large objects such as images, audio and video (BLOB/CLOB).
• Still queried with SQL (extended, e.g. SQL:1999).

Example

CREATE TYPE address_t AS (street VARCHAR(30), city VARCHAR(20));
CREATE TABLE person(id INT, name VARCHAR(30), addr address_t);

Advantages: reuses existing relational technology and SQL while supporting complex data and reuse. Disadvantage: more complex than the pure relational model. Examples: PostgreSQL, Oracle.

Data abstraction is hiding the complex details of how data is stored and maintained, and giving users only a simplified view. It makes the system usable by people who do not know the internal structures. A DBMS provides three levels:

1. Physical (internal) level: the lowest level; describes how data is actually stored (files, blocks, index structures, record formats).
2. Logical (conceptual) level: describes what data is stored and the relationships among it (tables, attributes, constraints). Used by the database administrator.
3. View (external) level: the highest level; describes only the part of the database relevant to a particular user or application. Many views may exist for one database, hiding the rest and adding security.

View 1   View 2   View 3      (external)
      \     |     /
      Logical schema          (conceptual)
            |
      Physical schema         (internal)

Data independence follows from abstraction: physical data independence (changing storage does not change the logical schema) and logical data independence (changing the logical schema need not change the views/applications).

Data: raw, unprocessed facts and figures with no meaning by themselves, e.g. 45, "Ram", 78.

Information: data that has been processed and organized so it is meaningful and useful for decisions, e.g. "Ram scored 78 marks, the highest in the class".

DBMS (Database Management System): software that lets users define, create, store, manipulate, retrieve and control a collection of related data (a database), while providing security, integrity and concurrent access. Examples: MySQL, Oracle, SQL Server.

Advantages of DBMS

• Reduced data redundancy and inconsistency.
• Data sharing among many users and applications.
• Data integrity enforced through constraints.
• Security through authorization and access control.
• Data independence (physical and logical).
• Concurrent access with transaction control.
• Backup and recovery after failure.
• Standardised, easy querying (SQL).

Disadvantages of DBMS

• High cost of software, hardware and trained staff.
• Complexity of design and administration.
• Large size; needs more memory and disk space.
• Performance overhead for simple applications compared with file systems.
• A failure of the central system can affect all users.
• Conversion from an existing file system is costly.

Data model: a collection of concepts and tools for describing the structure of a database: its data, relationships, semantics and constraints. Examples: the entity-relationship model, relational model, object-oriented model, hierarchical and network models.

Components of the ER model

(a) Entity: a real-world thing that can be distinguished from others. An entity set is a collection of similar entities. A strong entity has its own key; a weak entity depends on another entity (double rectangle). Example: STUDENT, COURSE.

(b) Attribute: a property of an entity (ellipse). Types: simple/composite (name = first + last), single-valued/multi-valued (phone numbers), derived (age from date of birth, dashed ellipse), and key attribute (underlined).

(c) Relationship: an association among entities (diamond). Degree: unary, binary, ternary. Example: STUDENT enrolls in COURSE.

(d) Cardinality / mapping constraints: one-to-one, one-to-many, many-to-one, many-to-many. Participation may be total (double line) or partial.

Example

[STUDENT]--(enrolls)--[COURSE]
 roll_no, name           code, title      cardinality M : N

Here roll_no is the key of STUDENT and code the key of COURSE; enrolls is a many-to-many relationship that may carry an attribute such as grade.

Relations: employee(eid, name, position, age), project(pid, name, duration, budget), works(eid, pid, location, salary).

(i) Managers above 30 years

$\sigma_{position='Manager' \wedge age>30}(employee)$

(ii) Budget of projects running for less than 45 days

$\pi_{budget}(\sigma_{duration<45}(project))$

(iii) Update the project name to 'Business Website' where pid is P098

Relational algebra has no update operator, so it uses the assignment/rename form: delete the old tuple and insert the modified one.

$project \leftarrow (project - \sigma_{pid='P098'}(project)) \cup \{(P098,\ 'Business Website',\ d,\ b)\}$

where $d$ and $b$ are the existing duration and budget of that tuple. Equivalent generalized projection form:

$project \leftarrow \pi_{pid,\ 'Business\ Website',\ duration,\ budget}(\sigma_{pid='P098'}(project)) \cup \sigma_{pid\neq 'P098'}(project)$

(SQL: UPDATE project SET name='Business Website' WHERE pid='P098';)

(iv) Locations of projects where all Software Developers work

Let $S = \pi_{eid}(\sigma_{position='Software\ Developer'}(employee))$. Using division, the locations $l$ such that every Software Developer has a works tuple at $l$:

$\pi_{eid,\ location}(works) \div S$

Here the division of $\pi_{eid,location}(works)$ by $S$ gives exactly the locations where all the Software Developers work. (If only locations where Software Developers work are needed, use $\pi_{location}(works \bowtie S)$.)

Join is a relational operation that combines tuples from two relations into one, based on a condition on related attributes. It is the cartesian product followed by a selection: $R \bowtie_{cond} S = \sigma_{cond}(R \times S)$. Example: employee.dept_id = department.dept_id.

Inner join vs outer join

Types of outer join

• Left outer join ($R ⟕ S$): all tuples of R plus matching ones from S.
• Right outer join ($R ⟖ S$): all tuples of S plus matching ones from R.
• Full outer join ($R ⟗ S$): all tuples of both.

Example: employee(Ram, d1), (Sita, d9); department(d1, Sales), (d2, HR).

• Inner join: (Ram, Sales).
• Left outer join: (Ram, Sales), (Sita, NULL).
• Full outer join: (Ram, Sales), (Sita, NULL), (NULL, HR).

SELECT * FROM employee e INNER JOIN department d ON e.dept_id = d.dept_id;
SELECT * FROM employee e LEFT OUTER JOIN department d ON e.dept_id = d.dept_id;

Problems if the database is not normalized

• Redundancy: the same data is stored many times, wasting space.
• Insertion anomaly: some facts cannot be stored without unrelated data (cannot add a new course until a student enrolls).
• Deletion anomaly: deleting a row can lose other facts (deleting the last student of a course loses the course).
• Update anomaly: a change must be made in many rows, and missing one causes inconsistency.

2NF vs 3NF

Example of 2NF: ENROLL(student_id, course_id, student_name, grade) with key (student_id, course_id). student_id → student_name is a partial dependency. Decompose into STUDENT(student_id, student_name) and ENROLL(student_id, course_id, grade).

Example of 3NF: EMP(emp_id, name, dept_id, dept_name) with key emp_id. emp_id → dept_id → dept_name is transitive. Decompose into EMP(emp_id, name, dept_id) and DEPT(dept_id, dept_name).

Database constraints are rules defined on tables that restrict the data which may be stored, so that the database stays accurate and consistent. The DBMS checks them automatically on insert, update and delete. Examples: PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY.

Integrity constraint

Integrity means correctness and consistency of data. Main integrity constraints:

• Domain constraint: values must belong to the attribute's domain, e.g. CHECK (marks BETWEEN 0 AND 100).
• Entity integrity: the primary key must be unique and never NULL, so each tuple is identifiable.
• Key constraint: candidate key values (UNIQUE) must not repeat.

Example: in student(roll_no PRIMARY KEY, name NOT NULL, marks CHECK(marks <= 100)), inserting a null roll_no or marks of 120 is rejected.

Referential constraint

A foreign key in one relation must either be NULL or match a primary key value in the referenced relation. This prevents dangling references.

CREATE TABLE department(dept_id INT PRIMARY KEY, dname VARCHAR(30));
CREATE TABLE employee(
  eid INT PRIMARY KEY,
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES department(dept_id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

• Inserting an employee with a non-existing dept_id is rejected.
• Deleting a referenced department is rejected, or cascades (deletes employees), or sets NULL, depending on the option chosen.

Authentication vs authorization

Views

A view is a virtual table defined by a query on one or more base tables; it stores the query, not the data.

Creating a view

CREATE VIEW emp_public AS
SELECT eid, name, dept_id FROM employee WHERE dept_id = 10;

Using a view

SELECT * FROM emp_public WHERE name LIKE 'R%';
GRANT SELECT ON emp_public TO clerk;
DROP VIEW emp_public;

Uses

• Security: users see only permitted rows/columns (e.g. salary is hidden).
• Simplicity: hides complex joins behind a simple name.
• Logical data independence: applications keep working when base tables change.
• Different users can have different customised views of the same data.

Backup and recovery system

Backup is the process of making copies of the database (and its logs) on separate storage, such as tape, another disk or a remote site, so that data can be restored after loss. Types: full, incremental/differential, and online/offline backup.

Recovery is the process of restoring the database to the latest consistent state after a failure (transaction failure, system crash, disk failure). The recovery system uses the backup together with the log to bring the database back to a correct state, and ensures atomicity and durability.

Log-based recovery

A log is a file on stable storage recording every update. Log records:

• <Ti, start>
• <Ti, X, old, new>
• <Ti, commit> or <Ti, abort>

Write-ahead rule: the log record is written to stable storage before the change is made in the database.

• Deferred database modification: updates are written to the database only after commit. On a crash, committed transactions are redone from the log; uncommitted ones are simply ignored.
• Immediate database modification: updates may be written before commit. On a crash, uncommitted transactions are undone (restoring old values) and committed ones are redone (new values).

Example: log <T1,start> <T1,A,500,400> <T1,commit> <T2,start> <T2,B,300,350> then crash. T1 has commit so redo it (A = 400). T2 has no commit so undo it (B = 300).

Checkpoints periodically force the log and modified buffers to disk, so only transactions active after the last checkpoint need to be examined during recovery.

Transaction schedule: the chronological order in which the operations (read, write, commit, abort) of a set of concurrently executing transactions are performed. Operations of each transaction keep their own relative order within the schedule.

Serial schedule

• Transactions run one after another with no interleaving; one transaction finishes completely before the next starts.
• With $n$ transactions there are $n!$ possible serial schedules.
• Always consistent and correct, but poor resource utilisation and throughput, long waiting time.

Example: T1 (read A, write A) completely, then T2 (read A, write A).

Non-serial schedule

• Operations of different transactions are interleaved.
• Better concurrency and throughput, but may leave the database inconsistent (lost update, dirty read) if not controlled.
• A non-serial schedule is acceptable only if it is serializable, i.e. equivalent to some serial schedule.

Serial:      T1: R(A) W(A)           T2: R(A) W(A)
Non-serial:  T1: R(A)      W(A)
             T2:      R(A)      W(A)

The non-serial schedule above gives a lost update, so it is not serializable; the concurrency control protocol (locking, timestamps) must reject it.

Distributed database: a collection of logically related data that is physically spread over several computers (sites) connected by a network. Each site has its own local DBMS and can run local queries, and together the sites appear to the user as a single database managed by a distributed DBMS. Data may be fragmented and replicated across sites.

Advantages over centralized database

• Local autonomy and faster local access: data is stored near where it is used, so access is quicker.
• Reliability and availability: failure of one site does not stop the whole system; replicated data remains available.
• Reduced communication cost for local data.
• Scalability: new sites can be added easily.
• Parallel processing: queries run on several sites simultaneously, improving performance.
• Matches the structure of distributed organisations (branches).

Disadvantages

• Complex design and management (fragmentation, allocation, replication).
• More complex query processing and optimization, and transaction/concurrency control (two-phase commit, distributed deadlock).
• Higher security risk through network communication.
• Consistency of replicated copies is hard to maintain.
• Higher software and hardware cost and more development effort.

Query By Example (QBE) is a graphical, table-based query language for relational databases, developed by IBM (Zloof). The user fills in skeleton tables with example values instead of writing a command syntax, so it is easy for non-programmers.

How it works

• The system displays an empty table skeleton with the relation's attributes as column headings.
• The user enters conditions or example elements in the columns.
• P. means print/display; a variable (underscore, e.g. _x) is an example element used to link conditions; conditions can use >, <, =.

Example: list names of employees in department 10 with salary above 5000, for emp(name, dept, salary):

This corresponds to SELECT name FROM emp WHERE dept = 10 AND salary > 5000;

Insert uses I., delete D., update U.. Joins are expressed by using the same variable in two tables. Microsoft Access's query design grid is a QBE-style interface.

ACID properties are the four properties a DBMS guarantees for every transaction to keep the database reliable.

• Atomicity: a transaction is "all or nothing". Either all its operations are done or none are. If it fails midway, its partial effects are rolled back (handled by the recovery manager).
• Consistency: a transaction takes the database from one consistent state to another, preserving all integrity constraints. Example: after transferring money, the total of A + B is unchanged.
• Isolation: concurrent transactions do not interfere; each behaves as if it were running alone, and intermediate results are hidden from others (handled by the concurrency control manager).
• Durability: once a transaction commits, its changes are permanent and survive any later system failure (ensured by logs and recovery).

Example: transfer of Rs 100 from A to B: read A; A = A - 100; write A; read B; B = B + 100; write B. Atomicity means both writes happen or neither; durability means the result stays after commit.