1.

File Processing System

A File Processing System stores data directly in files (flat files). Each file is independent with no relation to
others. It was the method used before DBMS.

Issues with File Processing System

Issue Problem Impact

Data Redundancy Same data duplicated across many files Wasted storage, increased errors

Data Inconsistency Changes in one file not reflected Unreliable information

elsewhere

Limited Data Sharing Hard to share data between Reduced collaboration

departments

Data Dependence Programs depend on file structure High maintenance cost

directly

Lack of Data Integrity No constraints enforced automatically Lower data quality

Limited Security Basic access controls only Data breach risk

Concurrency Control Multiple users cause conflicts Data corruption

Scalability Issues Struggles with large datasets Poor performance

Limited Query No query language support Inefficient data retrieval

2. DBMS – Introduction & Need

DBMS = Database Management System. It is a collection of programs to store and access data in an easy and
effective manner.
Database = Collection of meaningful interrelated information.

Data → Information → Knowledge → Action

• Data: Raw facts (e.g., 10, RAM) – no meaning alone
• Information: Data with meaning (e.g., id = 10, name = RAM)
• Knowledge: Using information to understand patterns (INR 75 = 1 USD)
• Action: Predicting outcomes from knowledge

Why Use DBMS?

• Develop software in less time
• Data independence and efficient use
• Uniform data administration
 • Data integrity and security
• Concurrent access and crash recovery
• User-friendly declarative query language (SQL)

Advantages of DBMS over File Processing

• No redundant data
• Data Consistency and Integrity
• Data Concurrency
• Data Security and Privacy
• Easy access to data
• Data Recovery
• Flexible

3. Basic Terminologies
Term Definition

Database Organized collection of data that can be easily accessed, managed, and
updated

DBMS Software that enables users to create, manage, and query databases

Table Data organized in rows and columns (basic structure in relational DB)

Row / Record / Tuple A single entry in a table

Column / Field / Attribute A specific property of an entity

Primary Key Unique identifier for each record in a table – NOT NULL + UNIQUE

Foreign Key Column referencing the primary key of another table – enforces referential
integrity

Index Data structure to speed up data retrieval

Query Request for data (usually in SQL)

Normalization Process to eliminate redundancy and improve integrity

Schema Blueprint/structure defining organization of data

Transaction Sequence of operations treated as a single unit

ACID Atomicity, Consistency, Isolation, Durability – guarantees reliability

Data Dictionary Centralized repository storing metadata about the database

4. Database System Architecture

The database system has 4 types of users: Naive Users, Application Programmers, Sophisticated Users, Database
Administrators.
Three main components:

Query Processor
Component Purpose

DML Compiler Converts DML (Data Manipulation Language) statements into low-level
instructions

DDL Interpreter Processes DDL statements into metadata tables

Embedded DML Pre-compiler Converts DML in application programs to procedural calls

Query Optimizer Executes instructions generated by DML Compiler

Storage Manager
Component Purpose

Authorization Manager Role-based access control – checks user privileges

Integrity Manager Checks integrity constraints when database is modified

Transaction Manager Controls concurrent access, ensures consistency

File Manager Manages file space and data structures

Buffer Manager Manages cache memory and data transfer between disk and RAM

Disk Storage Components

• Data Files – stores the actual data
• Data Dictionary – metadata repository
• Indices – faster retrieval
• Statistical Data – statistics for all information

5. Data Independence
Data Independence = Making changes at one level without affecting other levels.

Three Levels of a Database

• Physical Level (Low level) – Disk storage
• Conceptual Level – Queries, procedures, logic
• Logical Level / View Level – User Interface

Type Definition Examples of Changes

Physical Data Changes in physical level Changing storage devices, file organization,
Independence WITHOUT affecting logical level data structures, indexes, migration
 Type Definition Examples of Changes

Logical Data Independence Changes in logical level WITHOUT Add/modify/delete attributes, merge/split
affecting physical level records

⚠️KEY POINT: Physical Data Independence is EASIER to achieve than Logical Data Independence.

6. Evolution of Data Models

Model Key Feature Advantage Disadvantage

Hierarchical Tree structure, 1:M parent- Fast traversal, changes in Complex, deleting
child parent reflect in child parent deletes child

Network Extension of hierarchical; a Faster access, more Very complex

record can have multiple relationships
parents

ER Model Graphical – Entities, Attributes, Simple, easy to convert No industry standard

Relationships

Relational Tables (rows and columns / 2D Simple, scalable, Hardware overhead

array) structured

Object-Oriented Objects with attributes and Best for complex data More complex
methods; complex data
(images, video)

⚠️KEY POINT: Relational Model uses: Relation=Table, Tuple=Row, Attribute=Column, Domain=Set of

permitted values

7. Degrees of Data Abstraction (ANSI/SPARC)

• External Model – End user's view; specific to business needs
• Conceptual Model – Database as seen by DBMS (logical structure)
• Internal Model – Implementation level; achieving logical independence
• Physical Model – Lowest level; how data is physically stored on media

8. Database Users & DBA

User Type Description Example

Naive Users No DBMS knowledge; use applications via Railway booking users, bank clerks
browser

Application Develop DBMS applications using languages Backend developers

Programmers (Java, Python)
 User Type Description Example

Sophisticated Users Have DB knowledge; use queries but no Business Analysts, Researchers
coding

Native Users Use existing database applications; no Library management users

codes/queries

Specialized Users Write complex database programs Specialized developers

Stand-alone Users Personal use databases with ready-made Personal finance apps
packages

DBA (Database Administrator) Responsibilities

• Install & Upgrade the Database
• Design and Implementation
• Database Tuning
• Migrating the Database
• User Management (create/modify/remove)
• Backup and Recovery
• Security of the Database
• Documentation

9. Entity-Relationship (ER) Model

E-R Model = High-level conceptual data model. Represents real-world problems as pictorial representations.
Three basic concepts:
• Entity Sets
• Relationship Sets
• Attributes

Entity & Entity Set

• Entity = Any real-world object (person, furniture, university)
• Entity Set = Set of entities of the same type sharing the same attributes
• Example: All faculties = 'faculty' entity set; all students = 'student' entity set

Attributes
Attribute Type Description Example

Simple Cannot be divided further (atomic salary, dept_name

values)

Composite Can be divided into subparts name → first_name, middle_name,

last_name

Single-valued Only one value per entity gender, DOB, reg_no

 Attribute Type Description Example

Multi-valued Can hold multiple values phone_no, email_id (shown as

{phone_no})

Derived Derived from existing value; shown Age (derived from DOB)
with dotted ellipse

Key Uniquely identifies an entity; faculty_id, reg_no

underlined in ER diagram

Null Value is unknown or does not exist —

10. Keys & Constraints

Key Type Definition

Superkey One or more attributes that uniquely identify a record. May have extra
attributes.

Candidate Key Minimal Superkey (no redundant attributes). Multiple possible.

Primary Key Selected candidate key. NOT NULL + UNIQUE. Only ONE per table.

Composite Primary Key Combination of multiple attributes as primary key (max 16 columns)

Foreign Key Primary key of another table used in this table. Enforces referential integrity.

Constraints Summary
Constraint Null Values Duplicate Values Notes

NOT NULL NO YES Value must be entered;

duplicates allowed

CHECK YES YES Validates condition (e.g.,

salary > 10000)

UNIQUE YES NO No duplicates; multiple NULL

allowed

PRIMARY KEY NO NO NOT NULL + UNIQUE

combined; only one per table

FOREIGN KEY YES YES References primary key of

another table

11. Mapping Cardinalities

Cardinality = Number of entities one entity can be associated with through a relationship.
Type Meaning Arrow Notation

One-to-One (1:1) Each entity in A associated with at most one in → on both sides
 Type Meaning Arrow Notation

B, and vice versa

One-to-Many (1:M) One entity in A associated with many in B; each → on A side, — on B side
in B has at most one in A

Many-to-One (M:1) Many in A associated with at most one in B — on A side, → on B side

Many-to-Many Many in A associated with many in B — on both sides

(M:M)

12. Participation Constraints

• Total Participation: EVERY entity in set E participates in at least one relationship. Shown with DOUBLE
LINE.
• Partial Participation: Only SOME entities participate. Shown with single line.

13. ER Diagram Notations

Symbol Represents

Rectangle (divided) Entity Set – name in top, attributes listed below

Diamond Relationship Set

Ellipse (solid) Simple or Composite Attribute

Ellipse (dashed) Derived Attribute

Ellipse ({} notation) Multi-valued Attribute

Underlined attribute Primary Key attribute

Double Rectangle Weak Entity Set

Double Diamond Identifying Relationship Set (for weak entity)

Double Line Total Participation

→ (directed line) One side in cardinality

— (undirected line) Many side in cardinality

14. Extended ER – Specialization, Generalization & Aggregation

Specialization (Top-Down)
Dividing an entity set into sub-groups based on distinguishing characteristics.
• Example: Person → Employee, Student
• Employee → Faculty, Secretary
• Student → Undergraduate, Postgraduate
 • ISA Relationship shown as hollow arrow pointing from subclass to superclass
• Overlapping Specialization: entity may belong to multiple sub-sets (two arrows)
• Disjoint Specialization: entity belongs to at most one sub-set (one arrow)

Generalization (Bottom-Up)
Synthesizing multiple entity sets into a higher-level entity set based on common features.
• Example: Faculty + Secretary → common attributes → Employee (generalization)
• Higher-level = Superclass; Lower-level = Subclass

Attribute Inheritance
• Lower-level entity sets INHERIT attributes of higher-level entity sets
• Example: Student inherits ID, name, address from Person; also has its own 'fees' attribute
• Single Inheritance: entity is subclass in only one ISA relationship
• Multiple Inheritance: entity is subclass in more than one ISA (forms a lattice)

Aggregation
Used when we need to model relationships among relationships. Treats a relationship set as a higher-level
entity.
• Example: project_guide (Faculty–Student–Project) needs a review report entity → use aggregation
━━━ UNIT 2: RELATIONAL DATABASE ━━━

1. Converting ER Diagram to Relational Tables

ER Scenario Tables Required Rule

Strong Entity with only Simple 1 table Table attributes = entity attributes; PK = key
Attributes attribute

Strong Entity with Composite 1 table Use simple sub-attributes (NOT composite
Attributes attribute itself)

Strong Entity with Multi-valued 2 tables Table 1: simple attributes + PK; Table 2: PK +
Attributes multi-valued attribute

Relationship Set 1 table Attributes = PKs of participating entities +

descriptive attributes

Binary 1:1 Total Participation 1 table Merge both entities into one table (ARB)

Binary 1:1 Partial Participation 2 tables Add FK in one entity table

Binary 1:M 2 tables Add FK of 1-side entity into M-side entity table

Binary M:M 3 tables Separate table for relationship + both entity tables

Weak Entity Set 2 tables Weak entity table includes FK from strong entity

2. Pitfalls in Relational Database Design

Bad design leads to: Repetition of Information and Inability to represent certain information.

Example – Bad Schema

Lending-schema = (branch_name, branch_city, assets, customer_name, loan_number, amount)
• Redundancy: branch data repeated for every loan
• Null values: can't store branch info if no loans exist

Decomposition
Split bad schemas into smaller, better schemas. Rule: R = R1 ∪ R2 (all attributes must appear)
Lossless-join: r = ΠR1(r) ⋈ ΠR2(r)

⚠️KEY POINT: Decomposition must be lossless-join to avoid information loss!

3. Relational Algebra
Relational Algebra is a PROCEDURAL query language. It provides the theoretical foundation for SQL.

Operation Symbo Notation Description

l

Select σ σp(r) Retrieves rows satisfying predicate p from

relation r

Project ∏ ∏A1,A2(r) Retrieves specific columns from r; duplicates

auto-removed

Union ∪ r∪s All tuples in r OR s (or both); same schema

required

Intersection ∩ r∩s Tuples in BOTH r AND s

Set Difference − r−s Tuples in r but NOT in s

Cartesian Product × r×s Every tuple of r paired with every tuple of s

Join ⋈ r⋈s Combines Select + Cartesian Product; tuples

satisfying condition

Rename ρ ρx(E) Renames relation E to x

Assignment ← x←E Assigns expression E to temporary variable x

Key Rules for Union / Intersection / Set Difference

• Relations must have the SAME number of attributes
• Attribute domains (data types) must be compatible
• Duplicate records are eliminated automatically

Select Operation Examples

• σJOB = "MANAGER"(EMP) → All employees with job = MANAGER
• σJOB = "MANAGER" ∧ DEPTNO = 20(EMP) → MANAGER in dept 20

Project Operation Examples

• ∏ENAME, JOB(EMP) → Only ENAME and JOB columns from EMP

Cartesian Product
If Table A has 3 rows and Table B has 3 rows → A × B produces 3 × 3 = 9 rows

4. Tuple Relational Calculus (TRC)

TRC is a NON-PROCEDURAL query language. Describes WHAT to retrieve, not HOW.
Basic Form
{ t | P(t) } → Set of all tuples t such that predicate P(t) is true
• t[A] = value of tuple t on attribute A
• t ∈ r = tuple t is in relation r

Quantifiers Used
• ∃ (Existential) = 'there exists' – at least one tuple satisfies the condition
• ∀ (Universal) = 'for all' – all tuples satisfy the condition

TRC Examples
• Find salary > 2000: { t | t ∈ emp ∧ t[sal] > 2000 }
• Find empno where sal > 2000: { t | ∃ s ∈ emp (t[empno] = s[empno] ∧ s[sal] > 2000) }
• Find names with dept in CHICAGO: { t | ∃ s ∈ emp (t[ename] = s[ename] ∧ ∃ u ∈ dept (u[dname] =
s[dname] ∧ u[building] = "CHICAGO")) }

Free vs Bound Variables

• Free Variable: appears in formula but NOT quantified (∃ or ∀)
• Bound Variable: quantified by ∃ or ∀

Safety of TRC Expressions

An expression is SAFE if every component of t appears in one of the relations, tuples, or constants in P.
{ t | t[A] = 5 ∨ true } is NOT safe (infinite set of tuples outside any relation).

5. Domain Relational Calculus (DRC)

DRC uses domain variables (taking values from attribute domains), NOT entire tuples. Closely related to TRC.
DRC serves as theoretical basis for QBE (Query By Example) language.

Basic Form
{ <x1, x2, ..., xn> | P(x1, x2, ..., xn) } → domain variables and predicate P

Atoms in DRC
• <x1, x2, ..., xn> ∈ r → tuple (x1..xn) is in relation r
• x θ y → comparison between domain variables (θ is <, ≤, =, >, ≥, ≠)
• x θ c → domain variable compared to constant c

DRC Examples
• Loans > $1200: { l, b, a | <l, b, a> ∈ loan ∧ a > 1200 }
 • Customers with loan > $1200: { c | ∃l, b, a (<c, l> ∈ borrower ∧ <l, b, a> ∈ loan ∧ a > 1200) }

Safety in DRC
An expression { <x1,...,xn> | P(x1,...,xn) } is safe if:
• All values in output appear in dom(P)
• ∃x(P1(x)) is true only if there is x in dom(P1) making P1(x) true
• ∀x(P1(x)) is true only if P1(x) is true for all x from dom(P1)

6. Calculus vs Algebra – Quick Comparison

Aspect Relational Algebra Tuple Relational Calculus Domain Relational
Calculus

Type Procedural Non-procedural Non-procedural

Describes HOW to retrieve data WHAT to retrieve WHAT to retrieve

Variables Relations (tables) Tuples (rows) Domain values (attribute

values)

Basis for SQL — QBE (Query By Example)

Operators σ, ∏, ∪, ∩, −, ×, ⋈ ∃, ∀, ∧, ∨, ¬ ∃, ∀, ∧, ∨, ¬
━━━ QUICK REVISION – EXAM TIPS ━━━

Must-Know Formulas & Facts

• Superkey → Candidate Key (minimal superkey) → Primary Key (chosen candidate)
• Primary Key = NOT NULL + UNIQUE constraint combined
• Foreign Key allows NULL and DUPLICATE values
• Total participation = double line in ER diagram
• Derived attribute = dotted ellipse
• Multi-valued attribute = double ellipse or { } in ER
• Weak entity set = double rectangle
• Identifying relationship = double diamond
• RA = Procedural | TRC = Non-procedural | DRC = Non-procedural
• TRC is basis for SQL; DRC is basis for QBE
• Cartesian product of m rows × n rows = m×n rows
• Union, Intersection, Set Difference require COMPATIBLE schemas

Common MCQ Answer Hints

Question Pattern Answer

Which model organizes data in tree structure? Hierarchical Model

Which model allows multiple parents? Network Model

Which model uses tables (rows & columns)? Relational Model

Which model is for conceptual design? ER Model

Procedural query language? Relational Algebra

Non-procedural query language? Tuple / Domain Relational Calculus

Primary key does NOT allow? NULL values and DUPLICATE values

UNIQUE constraint ALLOWS? NULL values (multiple), but NO duplicates

Foreign key referencing? Primary key of another table

Double line in ER means? Total Participation

ISA relationship means? Specialization (top-down design)

Bottom-up design is? Generalization

Aggregation solves? Relationships among relationships

Lossless-join in decomposition? r = ΠR1(r) ⋈ ΠR2(r)

Bad schema issue? Repetition and inability to represent information