A friendly, start-to-advanced guide to the foundations of Database Management Systems. Read it top to bottom the first time; use the Quick Revision section at the end before exams or interviews.

---

📑 Table of Contents

1. Introduction to DBMS
2. File System vs DBMS
3. Advantages and Disadvantages of DBMS
4. Applications of DBMS
5. Characteristics of a Database
6. Database Users and Database Administrators (DBA)
7. Database Languages: DDL, DML, DQL, DCL, TCL
8. Database Abstraction
9. Three-Schema Architecture
10. Data Independence: Logical and Physical
11. Database Instances and Schemas
12. Metadata and Data Dictionary
13. DBMS Components and Architecture
14. Types of DBMS
15. Centralized and Distributed Databases
16. Quick Revision Cheat Sheet

---

1. Introduction to DBMS

1.1 Data, Information, Database — the basics

Term	Meaning	Example
Data	Raw, unprocessed facts	101, Riya, 87
Information	Data that has been processed to be meaningful	"Riya (Roll 101) scored 87 marks"
Database	An organised collection of related data stored so it can be easily accessed, managed and updated	A university's student records
DBMS	Software that creates, stores, manages and controls access to a database	MySQL, PostgreSQL, Oracle, SQL Server, MongoDB*

* MongoDB is a NoSQL DBMS; the concepts in these notes focus on the classic foundations that apply to all of them.

1.2 What is a DBMS?

A Database Management System (DBMS) is software that sits between the users/applications and the actual data. Instead of every program reading and writing files directly, they ask the DBMS, and the DBMS takes care of storing, retrieving, securing and protecting the data.

Think of it like a librarian. You don't walk into the stacks and rearrange books yourself — you ask the librarian, who knows where everything is, who is allowed to borrow what, and how to keep the library consistent.

 Users / Applications
        │   (requests: "give me all students with marks > 80")
        ▼
 ┌───────────────┐
 │     DBMS      │  ← handles storage, queries, security, concurrency, recovery
 └───────────────┘
        │
        ▼
   Physical Database (files on disk)

1.3 What does a DBMS actually do?

• Define the structure of data (tables, types, constraints)
• Store data efficiently on disk
• Retrieve data quickly using queries
• Update data safely (insert, modify, delete)
• Protect data (security, backup, recovery)
• Allow many users to work at the same time without corrupting data

---

2. File System vs DBMS

Before DBMSs, applications stored data in plain files (text files, spreadsheets, binary files), and each application managed its own files. That works for tiny setups but breaks down quickly as data and users grow.

2.1 Problems with the traditional file system

1. Data redundancy – the same data (e.g., a student's address) is stored in many files.
2. Data inconsistency – one copy gets updated, the others don't, so they disagree.
3. Difficulty in accessing data – every new type of query needs a brand-new program.
4. Data isolation – data is scattered across files with different formats.
5. Integrity problems – rules like "marks must be 0–100" are buried inside application code, so they're easy to miss or break.
6. Atomicity problems – if the system crashes halfway through a transfer, the data is left half-done.
7. Concurrent-access anomalies – two users editing the same file can overwrite each other's changes.
8. Security problems – hard to give fine-grained access (e.g., "can see names but not salaries").

2.2 Side-by-side comparison

Feature	File System	DBMS
Data redundancy	High	Minimal (controlled)
Data consistency	Hard to maintain	Maintained via constraints
Data sharing	Difficult, limited	Easy, multi-user
Querying	Needs custom programs	Powerful query language (SQL)
Security	Basic (OS-level file permissions)	Fine-grained (user/role/object privileges)
Backup & recovery	Manual, limited	Built-in mechanisms
Concurrency control	Not provided	Provided (locks, isolation levels)
Integrity constraints	Coded in each application	Declared once in the database
Data independence	None – structure changes break programs	Supported (see Section 10)
Relationships between data	Hard to represent	Easily represented (keys, relations)
Cost & complexity	Cheap, simple	More costly, more complex
Best for	Small, simple, single-user needs	Large, shared, critical data

💡 Takeaway: A file system stores data. A DBMS stores data and manages it — with rules, security, relationships and safety nets.

---

3. Advantages and Disadvantages of DBMS

3.1 Advantages ✅

Advantage	What it means
Reduced redundancy	Data is stored once and shared, saving space
Improved consistency	One source of truth, so no conflicting copies
Data sharing	Many users/applications access the same data concurrently
Data integrity	Constraints (primary key, foreign key, check, not null) keep data valid
Security	Authentication, authorization and access control per user/role
Backup and recovery	Logs and checkpoints help restore data after failures
Data independence	Change storage or structure without rewriting applications
Concurrency control	Multiple transactions run safely at the same time
Efficient querying	Query languages, indexes and optimizers make retrieval fast
Standards enforcement	Naming, formats and rules are enforced centrally
Faster application development	Developers reuse DBMS features instead of re-coding them

3.2 Disadvantages ❌

Disadvantage	What it means
High cost	Software licences, powerful hardware, training and skilled staff
Complexity	Designing, configuring and tuning a DBMS takes expertise
Performance overhead	General-purpose features can be slower than a purpose-built file solution for very simple tasks
Single point of failure risk	If a centralized DBMS goes down, everything depending on it is affected
Larger size	Needs more memory and disk space
Vendor dependence	Migrating from one DBMS to another can be painful
Frequent upgrades	Needs maintenance, patches and administration

🎯 When is a DBMS overkill? For a small, single-user, rarely-changing dataset, a simple file or spreadsheet may be enough.

---

4. Applications of DBMS

Almost every system that stores and retrieves data at scale uses a DBMS.

Domain	How a DBMS is used
Banking	Accounts, balances, transactions, loans, ATM records
Airlines & Railways	Reservations, schedules, seat availability, ticketing
Universities / Education	Student records, courses, enrollment, results, library
E-commerce	Products, carts, orders, customers, payments, inventory
Telecommunications	Call records, billing, subscriber details
Healthcare	Patient records, appointments, prescriptions, lab reports
Manufacturing	Inventory, supply chain, orders, production tracking
Human Resources	Employee data, payroll, attendance, performance
Social Media	Profiles, posts, friendships, messages
Finance & Stock Markets	Trades, portfolios, market data
Government	Citizen records, tax, census, licences
Sales & Retail	Customers, products, purchases, loyalty programs

---

5. Characteristics of a Database

The database approach differs from file processing in several key ways.

5.1 Core characteristics

1. Self-describing nature A database stores not only the data but also a description of its own structure (the catalog / metadata): table names, column names, data types, constraints. Programs can learn the structure by reading it.

2. Insulation between programs and data (Data Independence) Structure of data is stored in the catalog, not inside programs, so changing the structure doesn't force rewriting the programs.

3. Data abstraction Users see a simplified, conceptual view and are not bothered with how data is physically stored.

4. Support for multiple views of data Different users can see different customised subsets of the same database (e.g., a student sees their marks; a teacher sees the whole class).

5. Sharing of data and multi-user transaction processing Many users access data at the same time. The DBMS ensures concurrency control (no lost updates) and supports transactions.

6. Controlled redundancy Duplicates are minimised and, where kept deliberately (e.g., for speed), they're controlled and synchronized.

7. Data integrity Rules ensure data stays valid and correct.

8. Security Only authorised users can access or modify specific data.

9. Backup and recovery The system can recover to a consistent state after a crash.

5.2 Transactions and ACID (the heart of reliability)

A transaction is a logical unit of work (e.g., "transfer ₹500 from A to B"). DBMSs guarantee ACID:

Letter	Property	Meaning
A	Atomicity	All steps of the transaction happen, or none do
C	Consistency	The database moves from one valid state to another
I	Isolation	Concurrent transactions don't interfere with each other
D	Durability	Once committed, changes survive crashes

---

6. Database Users and Database Administrators (DBA)

6.1 Types of database users

Database Users
├── Naive / Parametric End Users
├── Casual (Sophisticated) End Users
├── Application Programmers
├── Specialized / Sophisticated Users
└── Stand-alone Users

User type	Who they are	How they interact	Example
Naive (Parametric) end users	Non-technical users who use ready-made applications	Through forms and menus; they never write queries	Bank teller, ticket booking clerk, online shopper
Casual / Sophisticated end users	Occasional users who need different information each time	Write queries in a query language	Manager running ad-hoc reports
Application programmers	Developers who write programs that talk to the database	Embed SQL in programming languages	Backend developer
Specialized users	Advanced users building non-traditional applications	Complex custom queries, specialized data models	Data scientists, CAD / GIS system builders
Stand-alone users	Maintain personal databases using ready-made packages	Menu/GUI-based software	Someone maintaining a personal contacts or finance DB

6.2 Database Administrator (DBA)

The DBA is the person (or team) with central control over the database system. Think of the DBA as the city planner and security chief of the database.

Key responsibilities:

1. Schema definition – create the original database structure.
2. Storage structure and access method definition – decide how data is stored and indexed.
3. Schema and physical organization modification – change structure as needs evolve.
4. Granting authorization – decide who can access what.
5. Routine maintenance – backups, free-space management, performance monitoring.
6. Security enforcement – prevent unauthorised access.
7. Backup and recovery planning – ensure data can be restored after failures.
8. Performance tuning – optimise queries, indexes and resources.
9. Monitoring and user support – respond to user problems, track usage.
10. Ensuring integrity – maintain data validity and consistency rules.

6.3 Other roles around a database (often mentioned alongside the DBA)

• Database designers – identify the data to be stored and choose structures to represent it (before the database is built).
• System analysts – determine the requirements of end users.
• Tool developers / Operators – build supporting tools and keep the system running.

---

7. Database Languages: DDL, DML, DQL, DCL, TCL

A DBMS provides different sub-languages for different jobs. In practice, they're all part of SQL (Structured Query Language).

SQL
├── DDL  → Define structure          (CREATE, ALTER, DROP, TRUNCATE, RENAME)
├── DML  → Manipulate data            (INSERT, UPDATE, DELETE)
├── DQL  → Query data                 (SELECT)
├── DCL  → Control access             (GRANT, REVOKE)
└── TCL  → Control transactions       (COMMIT, ROLLBACK, SAVEPOINT)

📝 Some textbooks include SELECT inside DML. Treating it separately as DQL is common and clearer, so we do that here.

7.1 DDL — Data Definition Language

Defines or changes the structure (schema) of the database. DDL commands usually auto-commit (they can't be rolled back in most systems).

Command	Purpose
CREATE	Create a database object (table, view, index…)
ALTER	Modify an existing object's structure
DROP	Delete an object completely (structure + data)
TRUNCATE	Remove all rows quickly, keep the table structure
RENAME	Rename an object

CREATE TABLE Student (
    roll_no  INT PRIMARY KEY,
    name     VARCHAR(50) NOT NULL,
    marks    INT CHECK (marks BETWEEN 0 AND 100)
);

ALTER TABLE Student ADD email VARCHAR(100);
TRUNCATE TABLE Student;
DROP TABLE Student;

7.2 DML — Data Manipulation Language

Works with the data inside tables: adding, changing, removing rows.

Command	Purpose
INSERT	Add new rows
UPDATE	Modify existing rows
DELETE	Remove rows (can use WHERE)

INSERT INTO Student (roll_no, name, marks) VALUES (101, 'Riya', 87);
UPDATE Student SET marks = 90 WHERE roll_no = 101;
DELETE FROM Student WHERE roll_no = 101;

7.3 DQL — Data Query Language

Used to retrieve data. It doesn't change the data.

SELECT name, marks FROM Student WHERE marks > 80 ORDER BY marks DESC;

7.4 DCL — Data Control Language

Controls who can do what (permissions).

Command	Purpose
GRANT	Give privileges to a user/role
REVOKE	Take privileges back

GRANT SELECT, INSERT ON Student TO teacher_user;
REVOKE INSERT ON Student FROM teacher_user;

7.5 TCL — Transaction Control Language

Manages transactions made by DML statements.

Command	Purpose
COMMIT	Permanently save the transaction's changes
ROLLBACK	Undo changes since the last commit (or savepoint)
SAVEPOINT	Set a point within a transaction to roll back to

BEGIN;
UPDATE Account SET balance = balance - 500 WHERE acc_no = 'A';
SAVEPOINT after_debit;
UPDATE Account SET balance = balance + 500 WHERE acc_no = 'B';
COMMIT;      -- or ROLLBACK TO after_debit; / ROLLBACK;

7.6 Quick comparison

Language	Works on	Key commands	Can be rolled back?
DDL	Structure	CREATE, ALTER, DROP, TRUNCATE	Usually no (auto-commit)
DML	Data	INSERT, UPDATE, DELETE	Yes (until commit)
DQL	Data (read-only)	SELECT	N/A
DCL	Permissions	GRANT, REVOKE	Usually no (auto-commit)
TCL	Transactions	COMMIT, ROLLBACK, SAVEPOINT	N/A

7.7 Advanced note: DELETE vs TRUNCATE vs DROP

	DELETE	TRUNCATE	DROP
Category	DML	DDL	DDL
Removes	Selected/all rows	All rows	Entire table (structure + data)
WHERE clause	✅ Yes	❌ No	❌ No
Rollback	✅ Possible	Usually ❌	Usually ❌
Speed	Slower (row by row, logged)	Faster	Fast

---

8. Database Abstraction

8.1 The idea

A database holds a lot of complex detail (bits on disk, file layouts, indexes). Data abstraction means hiding the unnecessary complexity and showing users only what they need. It's like driving a car: you use the steering wheel and pedals without knowing how the engine's fuel injection works.

8.2 Three levels of abstraction

┌────────────────────────────────────────────┐
│  VIEW LEVEL      (what each user sees)     │  ← highest, simplest
├────────────────────────────────────────────┤
│  LOGICAL LEVEL   (what data is stored,     │
│                   and how it's related)    │
├────────────────────────────────────────────┤
│  PHYSICAL LEVEL  (how data is stored)      │  ← lowest, most detailed
└────────────────────────────────────────────┘

Level	Also called	Describes	Who works here
Physical level	Internal level	How data is actually stored: files, blocks, indexes, record formats	DBMS developers, DBA
Logical level	Conceptual level	What data is stored and the relationships among them: tables, columns, types, constraints	DBA, database designers, programmers
View level	External level	Only the part of the database relevant to a particular user	End users, applications

8.3 Example

For a Student table:

• Physical level: a record is stored as a block of bytes with a pointer to the next record, using a B+ tree index on roll_no.
• Logical level: Student(roll_no INT, name VARCHAR(50), marks INT) with roll_no as the primary key.
• View level: A student sees only name and marks; the admin office sees all columns; the accounts team sees fee details.

💡 This three-level idea is formalised as the Three-Schema Architecture (next section).

---

9. Three-Schema Architecture

9.1 Purpose

Proposed by ANSI-SPARC, this architecture separates user applications from the physical database, achieving data independence and supporting multiple views.

9.2 The three schemas

      External Level (View Level)
 ┌──────────┐ ┌──────────┐ ┌──────────┐
 │ View 1   │ │ View 2   │ │ View n   │      ← External schemas
 └────┬─────┘ └────┬─────┘ └────┬─────┘
      └────────────┼────────────┘
                   │  External/Conceptual Mapping
                   ▼
        Conceptual Level (Logical)
        ┌──────────────────────┐
        │  Conceptual Schema   │              ← Whole database design
        └──────────┬───────────┘
                   │  Conceptual/Internal Mapping
                   ▼
          Internal Level (Physical)
        ┌──────────────────────┐
        │   Internal Schema    │              ← Storage details
        └──────────────────────┘
                   │
                   ▼
             Stored Database

Schema	Level	What it describes
External schema(s)	External / View	Each user group's view: the part of the DB they care about, hiding the rest
Conceptual schema	Conceptual / Logical	The structure of the entire database for the community of users: entities, data types, relationships, constraints. Hides physical details
Internal schema	Internal / Physical	Physical storage structure: file organisation, indexes, access paths, record placement

9.3 Mappings

The DBMS translates requests between levels using mappings:

• External ↔ Conceptual mapping: converts a user's view into the conceptual structure.
• Conceptual ↔ Internal mapping: converts the conceptual structure into physical storage structure.

When a query arrives in terms of a view, the DBMS maps it down: view → conceptual → internal → disk, and brings the result back up the same path.

9.4 Why it matters

• Supports multiple user views of the same data
• Provides data independence (Section 10)
• Gives security (users only see their view)
• Allows each level to change independently

---

10. Data Independence: Logical and Physical

10.1 Definition

Data independence is the ability to change the schema at one level without having to change the schema at the next higher level. It's the big payoff of the three-schema architecture.

10.2 Physical Data Independence

• The ability to change the internal (physical) schema without changing the conceptual schema (and hence not the external schemas or applications).
• Examples of physical changes:
  • Moving data to a different disk or storage device
  • Adding or changing an index
  • Changing file organisation (e.g., heap to sorted)
  • Changing compression or access path
• Only the conceptual/internal mapping needs updating.
• ✅ Easier to achieve and widely supported.

10.3 Logical Data Independence

• The ability to change the conceptual (logical) schema without changing the external schemas or application programs.
• Examples of logical changes:
  • Adding a new column or table
  • Removing a column not used by existing views
  • Splitting or merging tables
• Only the external/conceptual mapping needs updating.
• ⚠️ Harder to achieve, because applications depend heavily on the logical structure. Often, only certain kinds of changes (like adding new attributes) are truly transparent.

10.4 Comparison

Aspect	Physical Data Independence	Logical Data Independence
Changing	Internal schema	Conceptual schema
Should not affect	Conceptual schema (and above)	External schemas / applications
Typical change	New index, new storage device	Add/remove attribute or table
Mapping updated	Conceptual–Internal	External–Conceptual
Difficulty	Easier	Harder

10.5 Real-world analogy

Imagine a restaurant menu (external view). The kitchen can switch suppliers or cooking equipment (physical change) without changing the menu. Adding a new dish to the kitchen's recipe book (logical change) may or may not need the menu reprinted, depending on whether customers need to see it.

---

11. Database Instances and Schemas

11.1 Database Schema — the blueprint

The schema is the overall design/structure of the database: table names, columns, data types, relationships, constraints. It is defined during design and changes rarely.

• Also called the intension of the database.
• Think of it as the architectural plan of a house.

-- This is a schema (structure):
Student(roll_no INT, name VARCHAR(50), marks INT)

Types of schema (mirroring the three levels):

• Physical schema – how data is physically stored
• Logical schema – logical structure (tables, relationships)
• View / sub-schema – user-specific views

11.2 Database Instance — the snapshot

The instance is the actual data stored in the database at a particular moment in time. It changes frequently as data is inserted, updated or deleted.

• Also called the extension (or database state / snapshot) of the database.
• Think of it as the people currently living in the house.

-- This is an instance (data at this moment):
| roll_no | name  | marks |
|---------|-------|-------|
| 101     | Riya  | 87    |
| 102     | Aman  | 74    |

11.3 Schema vs Instance

Aspect	Schema	Instance
Meaning	Structure / design	Data at a moment in time
Also called	Intension, database description	Extension, database state, snapshot
Changes	Rarely	Frequently
Defined by	DDL	DML
Analogy	Blueprint of a building	People inside it right now
Stored in	Data dictionary / catalog	Database files

11.4 Related terms

• Empty state: when only the schema exists and no data has been loaded.
• Initial state: when the database is first populated with data.
• Valid state: a state that satisfies all constraints in the schema.
• Schema evolution: changing the schema over time (e.g., adding a column). The DBMS ensures existing data stays consistent.

---

12. Metadata and Data Dictionary

12.1 What is Metadata?

Metadata means "data about data." It describes the structure, properties and rules of the actual data, rather than being the data itself.

Examples of metadata:

• Table and column names
• Data types and sizes
• Primary and foreign keys
• Constraints and indexes
• User accounts and privileges
• Storage locations and file sizes
• Creation/modification dates

Think of a library: the books are the data; the catalog cards (title, author, shelf location) are the metadata.

12.2 Data Dictionary (System Catalog)

The data dictionary (also called the system catalog) is a repository inside the DBMS that stores all the metadata. The DBMS consults it constantly — for example, to check whether a table exists before running a query.

What it typically contains:

Category	Information stored
Schema info	Tables, views, columns, data types, sizes
Constraints	Primary keys, foreign keys, unique, check, not null
Indexes	Which indexes exist, on which columns
Users & security	User accounts, roles, privileges
Storage info	Files, tablespaces, allocation
Statistics	Row counts, value distributions (used by the query optimizer)
Views, procedures, triggers	Their definitions

12.3 Types of data dictionary

Type	Description
Active (integrated) data dictionary	Automatically updated by the DBMS whenever the structure changes; always consistent. Most modern DBMSs use this.
Passive (non-integrated) data dictionary	A separate tool that must be updated manually; may become out of sync.

12.4 Example

In many systems you can query the catalog just like normal tables:

-- MySQL / PostgreSQL / SQL Server style
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = 'Student';

12.5 Why it matters

• Lets the DBMS validate and optimise queries
• Supports self-describing nature of the database
• Helps the DBA manage and audit the system
• Provides documentation of the database

---

13. DBMS Components and Architecture

13.1 Components of a database system

A complete database environment has five major components:

Component	Description
Hardware	Physical devices: servers, storage disks, memory, network
Software	The DBMS itself, plus the OS, network software and application programs
Data	The actual data and its metadata
Users	End users, programmers, DBAs
Procedures	Rules and instructions for using and managing the system (backup steps, login, etc.)

13.2 Internal structure of a DBMS (functional components)

              Users / Applications
                    │ (SQL queries, application programs)
                    ▼
 ┌───────────────────────────────────────────────────────┐
 │                   QUERY PROCESSOR                     │
 │  ┌──────────────┐ ┌──────────────┐ ┌───────────────┐  │
 │  │ DDL          │ │ DML Compiler │ │ Query         │  │
 │  │ Interpreter  │ │ & Parser     │ │ Optimizer     │  │
 │  └──────────────┘ └──────────────┘ └───────────────┘  │
 │             ┌───────────────────────┐                 │
 │             │ Query Evaluation      │                 │
 │             │ Engine                │                 │
 │             └───────────────────────┘                 │
 └───────────────────────────┬───────────────────────────┘
                             ▼
 ┌───────────────────────────────────────────────────────┐
 │                   STORAGE MANAGER                     │
 │  Authorization & Integrity Manager                    │
 │  Transaction Manager                                  │
 │  File Manager                                         │
 │  Buffer Manager                                       │
 └───────────────────────────┬───────────────────────────┘
                             ▼
 ┌───────────────────────────────────────────────────────┐
 │                    DISK STORAGE                       │
 │   Data files │ Data dictionary │ Indices │ Logs       │
 └───────────────────────────────────────────────────────┘

13.2.1 Query Processor

Understands and executes user requests.

Part	Role
DDL interpreter	Interprets DDL statements and records definitions in the data dictionary
DML compiler	Translates DML/query statements into low-level instructions (an evaluation plan)
Query optimizer	Picks the most efficient execution plan among many possible ones
Query evaluation engine	Executes the low-level instructions generated by the DML compiler

13.2.2 Storage Manager

The interface between the data on disk and the query processor.

Part	Role
Authorization & integrity manager	Checks permissions and enforces integrity constraints
Transaction manager	Ensures the database stays consistent despite failures and concurrent transactions (ACID)
File manager	Manages disk space allocation and the data structures used to store information
Buffer manager	Fetches data from disk into main memory (cache) and decides what to keep in memory

13.2.3 Disk storage (data structures)

• Data files – the database itself
• Data dictionary – metadata
• Indices – structures for fast lookup
• Logs – records of transactions for recovery

13.3 DBMS Architecture (tiers)

How the DBMS is arranged relative to its users and applications.

1-Tier Architecture

• User works directly with the database on the same machine.
• No separate application layer.
• Used for local development, learning, or simple personal databases.

[ User + Application + DBMS + Database ]  (all on one machine)

2-Tier Architecture (Client–Server)

• Client (application/UI) talks directly to the database server.
• Communication via APIs such as ODBC / JDBC.
• Simple and fast, but the client holds business logic and the database is exposed more directly.

 Client (Application)  ───────►  Database Server (DBMS)

3-Tier Architecture

• Adds a middle application server layer between the client and the database.
• Presentation tier → UI (browser/app)
• Application (business logic) tier → processing rules
• Database tier → DBMS and data
• More secure, scalable and maintainable; the standard for web applications.

 Client (Browser/App) ───► Application Server ───► Database Server
   (Presentation)          (Business Logic)         (Data)

	1-Tier	2-Tier	3-Tier
Layers	1	2	3
Client talks to	DB directly (same machine)	DB server directly	Application server only
Security	Low	Medium	High
Scalability	Poor	Moderate	Good
Typical use	Local/personal	Small desktop apps	Web / enterprise apps

---

14. Types of DBMS: Hierarchical, Network, Relational, Object-Oriented

DBMSs are categorised by the data model they use to organise data.

14.1 Hierarchical DBMS

Idea: Data is organised as a tree with a single root; each child has exactly one parent (one-to-many relationships).

          Department
          /        \
    Employee     Project
       |
   Dependent

• Relationships: 1 : N only
• Access: navigational, starting from the root and following pointers
• Example: IBM IMS; also seen in file systems and XML structures

Pros	Cons
Simple, easy to understand	Cannot represent many-to-many relationships naturally
Very fast for parent-to-child access	Rigid structure; hard to restructure
Good integrity (child needs a parent)	Redundancy when a child must belong to many parents
	Deleting a parent deletes its children

14.2 Network DBMS

Idea: Data is organised as a graph; a child (member) can have multiple parents (owners), allowing many-to-many relationships.

   Student ───┐
              ├──► Enrollment ◄─── Course
   Student ───┘

• Model: records connected through sets (owner–member) using pointers
• Standard: CODASYL (Conference on Data Systems Languages)
• Example: IDMS, Integrated Data Store

Pros	Cons
Handles many-to-many relationships	Complex structure and pointer management
Flexible and efficient access paths	Hard to design and maintain
Better than hierarchical for complex relationships	Changing structure requires changing programs (low data independence)

14.3 Relational DBMS (RDBMS)

Idea: Data is organised into tables (relations) made of rows (tuples) and columns (attributes). Proposed by E. F. Codd (1970). Relationships are made using keys (primary/foreign), not physical pointers.

Student                          Course
┌─────────┬───────┬───────┐     ┌─────────┬────────────┐
│ roll_no │ name  │ c_id  │ ──► │ c_id    │ title      │
├─────────┼───────┼───────┤     ├─────────┼────────────┤
│ 101     │ Riya  │ C1    │     │ C1      │ Databases  │
│ 102     │ Aman  │ C2    │     │ C2      │ Networks   │
└─────────┴───────┴───────┘     └─────────┴────────────┘

• Language: SQL
• Strong foundation: set theory and relational algebra
• Examples: MySQL, PostgreSQL, Oracle, SQL Server, SQLite

Pros	Cons
Simple, intuitive table structure	Can be slower for very complex/hierarchical data
High data independence	Mapping object-oriented application data to tables can be awkward (impedance mismatch)
Powerful declarative querying (SQL)	Not ideal for multimedia / highly complex data types
Strong integrity, ACID support	Scaling horizontally can be harder
Most widely used and standardised

14.4 Object-Oriented DBMS (OODBMS)

Idea: Data is stored as objects, just like in object-oriented programming — with attributes (data) and methods (behaviour), supporting classes, inheritance, encapsulation and polymorphism.

Class Student
   Attributes: roll_no, name, marks
   Methods:    calculateGrade(), updateMarks()

• Use cases: CAD/CAM, multimedia, engineering, scientific and telecom applications with complex data
• Examples: ObjectDB, db4o, Versant (object-oriented systems)

Pros	Cons
Handles complex data types naturally	Less standardised than relational
No impedance mismatch with OO languages	Smaller user community and tooling
Supports inheritance and reuse	Steeper learning curve
Good for complex relationships	Query power is not as universal as SQL

14.5 Comparison Summary

Feature	Hierarchical	Network	Relational	Object-Oriented
Structure	Tree	Graph	Tables	Objects/classes
Relationships	1 : N	M : N	Via keys (any)	Via object references
Links through	Parent–child pointers	Owner–member sets	Primary/foreign keys	Object identifiers (OIDs)
Data independence	Low	Low	High	Moderate
Query language	Navigational	Navigational	SQL (declarative)	OQL / method-based
Flexibility	Low	Medium	High	High (for complex data)
Example	IBM IMS	IDMS	MySQL, Oracle	ObjectDB

---

15. Centralized and Distributed Databases

15.1 Centralized Database

Definition: All data is stored and managed at a single location (one central computer/server). Users access it remotely or locally via terminals/clients.

 User A ─┐
 User B ─┼──►  [ Central DB Server ]
 User C ─┘

Pros	Cons
Simple to design, manage and secure	Single point of failure — if it goes down, everything stops
Easy to maintain consistency	Heavy load on one server; can become a bottleneck
Lower cost for small/medium scale	Slow access for users far from the server
Easier backup and administration	Limited scalability

15.2 Distributed Database

Definition: Data is stored across multiple physical locations (sites/nodes) connected by a network, but appears to users as one logical database. A Distributed DBMS (DDBMS) manages it.

        ┌── Site 1 (Delhi) ──┐
 User ──┼── Site 2 (Mumbai) ─┼── Network ── Logical single database
        └── Site 3 (Pune) ───┘

Types

Type	Description
Homogeneous	All sites use the same DBMS software; simpler to manage
Heterogeneous	Sites may use different DBMS products, data models or OSs; needs translation/integration

Key techniques

• Fragmentation – splitting a table into pieces stored at different sites:
  • Horizontal (by rows), Vertical (by columns), Mixed/Hybrid
• Replication – keeping copies of data at multiple sites for availability and speed
  • Full, partial or no replication
• Allocation – deciding which fragments/copies live at which site

Transparency (users shouldn't notice the distribution)

• Location transparency – users don't need to know where data is
• Fragmentation transparency – users don't know data is split
• Replication transparency – users don't know copies exist
• Failure/Concurrency transparency – system hides failures and conflicts

Pros	Cons
Higher availability and reliability (no single point of failure)	Complex design, management and query processing
Faster local access (data close to users)	Higher cost (network, software, administration)
Scalable — add more sites easily	Keeping replicas consistent is hard
Reflects organisational structure (branches)	Security is harder across many sites
Parallel processing improves performance	Distributed transaction handling (e.g., two-phase commit) is complex

15.3 Centralized vs Distributed

Aspect	Centralized	Distributed
Data location	Single site	Multiple sites
Failure impact	High (single point of failure)	Lower (other sites keep working)
Performance	Can bottleneck	Better via local access and parallelism
Complexity	Low	High
Cost	Lower	Higher
Scalability	Limited	High
Consistency management	Simple	Complex
Example	A single college's local DB	A bank with branch databases across cities

---

⚡ Quick Revision Cheat Sheet

One-liners to remember

• DBMS = software to define, store, retrieve and protect shared data.
• File system problems → redundancy, inconsistency, poor security, no concurrency control.
• ACID → Atomicity, Consistency, Isolation, Durability.
• Users → Naive, Casual, Application programmers, Specialized, Stand-alone. DBA = central controller.
• Languages → DDL (structure), DML (data changes), DQL (SELECT), DCL (permissions), TCL (transactions).
• Abstraction levels → Physical (how stored) → Logical (what stored) → View (what user sees).
• Three-Schema Architecture → External, Conceptual, Internal + mappings between them.
• Physical independence = change storage without touching conceptual schema.
• Logical independence = change conceptual schema without touching views/apps (harder).
• Schema = design (rarely changes); Instance = data right now (changes often).
• Metadata = data about data; stored in the data dictionary / system catalog.
• Query Processor = DDL interpreter + DML compiler + optimizer + evaluation engine.
• Storage Manager = authorization/integrity + transaction + file + buffer managers.
• Tiers → 1-tier (local), 2-tier (client–server), 3-tier (client → app server → DB).
• DBMS types → Hierarchical (tree), Network (graph), Relational (tables), Object-oriented (objects).
• Centralized = one site; Distributed = many sites, one logical view (fragmentation + replication).

🎤 Commonly asked questions (from these topics)

1. What is a DBMS and how is it different from a file system?
2. List the advantages and disadvantages of using a DBMS.
3. Explain the three levels of data abstraction.
4. Explain the Three-Schema Architecture with a diagram.
5. Differentiate between logical and physical data independence.
6. What is the difference between a schema and an instance?
7. What is metadata? What does a data dictionary store?
8. Differentiate DDL, DML, DQL, DCL and TCL with examples.
9. What is the role of a DBA?
10. Compare hierarchical, network, relational and object-oriented DBMS.
11. Compare centralized and distributed databases.
12. Explain the components of a DBMS (query processor and storage manager).

---

A friendly, start-to-advanced guide to how databases model data, and how the relational model works: tables, keys, NULLs and integrity rules. Read it top to bottom once, then use the Quick Revision section before exams or interviews.

---

📑 Table of Contents

1. Introduction to Data Models
2. Hierarchical Data Model
3. Network Data Model
4. Relational Data Model
5. Object-Oriented Data Model
6. Entity-Based and Record-Based Models
7. Relation, Tuple, Attribute and Domain
8. Degree and Cardinality of a Relation
9. Relation Schema and Relation Instance
10. Keys: Super, Candidate, Primary, Alternate and Foreign
11. Composite and Natural Keys
12. Surrogate Keys
13. NULL Values and Their Meaning
14. Integrity Constraints
15. Entity Integrity and Referential Integrity
16. Domain and Key Constraints
17. Relational Database Properties
18. Quick Revision Cheat Sheet

---

1. Introduction to Data Models

1.1 What is a data model?

A data model is a collection of concepts and rules for describing the structure of a database: what data is stored, how pieces of data relate to each other, and what rules the data must obey.

Think of it as a language for describing data. Just as an architect's blueprint conventions let everyone read a building plan the same way, a data model lets everyone read a database design the same way.

1.2 The three ingredients of a data model

Ingredient	Meaning	Example (relational model)
Structure	How data is organised	Tables made of rows and columns
Operations	What you can do with the data	Select, project, join, insert, update, delete
Constraints	Rules the data must follow	Primary key, foreign key, not null, check

1.3 Categories of data models

Category	Level	Describes	Examples
High-level (conceptual)	Close to how users think	Entities, attributes, relationships	ER model
Representational (implementation)	Between user view and storage	Records, tables, objects	Hierarchical, Network, Relational
Low-level (physical)	Close to how computers store	Files, indexes, access paths	B+ tree, file organisation

1.4 Roadmap of the models in these notes

Data Models
├── Entity-based (conceptual)  → ER model
├── Record-based (implementation)
│     ├── Hierarchical model   (tree)
│     ├── Network model        (graph)
│     └── Relational model     (tables)
└── Object-based
      └── Object-Oriented model (objects)

---

2. Hierarchical Data Model

2.1 Idea

Data is organised as a tree. There is one root, and every other record (called a child) has exactly one parent. A parent can have many children.

            Department (root)
            /          \
       Employee       Project
          |              |
      Dependent       Task

2.2 Key points

• Relationship type: one-to-many (1 : N) only
• Basic unit: a record; related records are linked using parent-child pointers
• Access: navigational — you start at the root and follow pointers down the tree
• Example systems: IBM IMS (Information Management System)
• Real-world look-alikes: folder structure on a computer, XML/JSON-like nesting

2.3 Strengths and weaknesses

✅ Strengths	❌ Weaknesses
Simple and easy to understand	Cannot naturally model many-to-many relationships
Very fast for parent-to-child lookups	A child with two parents must be duplicated (redundancy)
Built-in integrity: a child cannot exist without its parent	Deleting a parent deletes all its children
	Rigid structure; hard to restructure; programs depend on the structure
	Querying "upwards" or across branches is awkward

---

3. Network Data Model

3.1 Idea

Data is organised as a graph. A record can have more than one parent, so many-to-many relationships can be represented directly.

 Student 1 ─┐                ┌─ Course A
            ├─► Enrollment ◄─┤
 Student 2 ─┘                └─ Course B

3.2 Key terms

Term	Meaning
Record type	A kind of record (like Student)
Set	A named one-to-many link between an owner record type and a member record type
Owner / Member	The "one" side / the "many" side of a set
Pointer / link	A physical connection between records

• Defined by the CODASYL (Conference on Data Systems Languages) committee, so it is sometimes called the CODASYL model.
• A many-to-many relationship is built using two one-to-many sets and a connecting (junction) record, like Enrollment above.
• Example systems: IDMS (Integrated Database Management System)

3.3 Strengths and weaknesses

✅ Strengths	❌ Weaknesses
Represents many-to-many relationships naturally	Complex to design and maintain (a tangled web of pointers)
Flexible access paths; efficient navigation	Programmer must know the physical structure; low data independence
Reduces redundancy compared to the hierarchical model	Structural change usually means changing programs
	No simple, declarative query language

3.4 Hierarchical vs Network at a glance

	Hierarchical	Network
Shape	Tree	Graph
Parents per child	Exactly one	One or more
Relationships	1 : N	M : N (via sets)
Complexity	Lower	Higher

---

4. Relational Data Model

4.1 Idea

Proposed by E. F. Codd in 1970. Data is organised into relations, which are simply tables made of rows and columns. Links between tables are made by matching values (keys), not by physical pointers.

Student                                   Department
┌─────────┬───────┬─────────┐            ┌─────────┬─────────────┐
│ roll_no │ name  │ dept_id │ ─────────► │ dept_id │ dept_name   │
├─────────┼───────┼─────────┤            ├─────────┼─────────────┤
│ 101     │ Riya  │ D1      │            │ D1      │ Computer Sc.│
│ 102     │ Aman  │ D2      │            │ D2      │ Electronics │
└─────────┴───────┴─────────┘            └─────────┴─────────────┘

4.2 Why it won

• Simple and intuitive: everything is a table
• Strong theory: built on set theory and relational algebra / calculus
• Declarative querying: you say what you want (SQL), not how to get it
• High data independence: physical storage can change without breaking queries
• Rich constraints: integrity is declared once and enforced by the DBMS
• Examples: MySQL, PostgreSQL, Oracle, SQL Server, SQLite

4.3 Strengths and weaknesses

✅ Strengths	❌ Weaknesses
Simple, uniform structure	Can be slow for deeply connected data (many joins)
Powerful, standard language (SQL)	Impedance mismatch with object-oriented programs
Strong integrity and ACID transactions	Awkward for complex data like multimedia or nested objects
Mature tools and wide adoption	Horizontal scaling can be harder

---

5. Object-Oriented Data Model

5.1 Idea

Data is stored as objects, just like in object-oriented programming. An object bundles attributes (data) and methods (behaviour) together.

Class: Student
  Attributes: roll_no, name, marks
  Methods:    calculateGrade(), updateMarks()

5.2 Core concepts

Concept	Meaning
Object	An instance of a class with its own state and an object identifier (OID)
Class	Template describing attributes and methods of similar objects
Encapsulation	Data and the methods that act on it are packaged together
Inheritance	A subclass reuses and extends a superclass (e.g., GradStudent inherits from Student)
Polymorphism	The same method name can behave differently for different classes
Object identity (OID)	A unique, system-generated identity that does not depend on attribute values
Complex objects	Objects can contain other objects or collections

5.3 Strengths and weaknesses

✅ Strengths	❌ Weaknesses
Handles complex data types naturally (CAD, multimedia, scientific data)	Less standardised than relational
No impedance mismatch with OO programming languages	Smaller ecosystem and fewer tools
Supports reuse through inheritance	Steeper learning curve
Direct navigation through object references	Ad-hoc querying is not as universal as SQL

📝 Many modern relational systems have borrowed ideas from this model (for example, user-defined types), blending both worlds. These are often called object-relational systems.

---

6. Entity-Based and Record-Based Models

Data models are also grouped by how they look at the data.

6.1 Entity-based (conceptual) models

• Describe data in terms of entities (real-world things), their attributes and relationships.
• Focus on meaning, close to how humans see the world.
• Used in the design phase, before choosing a particular DBMS.
• Main example: the Entity-Relationship (ER) model.

ER concept	Example
Entity	Student, Course
Attribute	name, roll_no
Relationship	Student enrolls in Course

6.2 Record-based (implementation) models

• Describe data in terms of fixed-format records of several types; each record type has a fixed number of fields.
• Closer to how the DBMS organises data.
• Examples: Hierarchical, Network and Relational models.

6.3 Side-by-side

Aspect	Entity-based	Record-based
Viewpoint	Real-world things and relationships	Records / tables of data
Level	Conceptual (high-level)	Logical / implementation
Used for	Designing the database	Implementing the database
Examples	ER model	Hierarchical, Network, Relational
Typical output	ER diagram	Table definitions / schema

6.4 How they connect

A typical workflow is to design with an entity-based model (ER diagram) and then convert it into a record-based model (relational tables) for the chosen DBMS.

Real-world requirements → ER diagram → Relational schema → Physical database
   (entity-based)                         (record-based)

---

7. Relation, Tuple, Attribute and Domain

These are the four building blocks of the relational model. Each has a formal name and an everyday name.

Formal term	Everyday term	Meaning
Relation	Table	A set of tuples with the same attributes
Tuple	Row / record	One entry in the relation
Attribute	Column / field	A named property of the relation
Domain	Allowed values	The set of legal values an attribute may take

7.1 Example

Relation: Student

roll_no	name	age	dept
101	Riya	20	CSE
102	Aman	21	ECE
103	Sara	20	CSE

• Relation: the whole Student table
• Tuple: (102, Aman, 21, ECE)
• Attribute: name, age, …
• Domain of age: for example, whole numbers from 15 to 60
• Domain of dept: for example, {CSE, ECE, ME, CE}

7.2 More about domains

• A domain is a set of atomic (indivisible) values of the same kind.
• Each attribute draws its values from one domain.
• A domain has a name, data type and format, e.g., Phone_Number: 10-digit string.
• Two attributes can share a domain (e.g., birth_date and join_date both use Date) but a domain match doesn't mean the values are meaningfully comparable.

7.3 Atomic values

In the relational model each cell holds one single value (no lists or nested tables). This is the idea behind First Normal Form (1NF).

❌ Not atomic	✅ Atomic
phones = "98xxx, 97xxx"	One phone number per row (or a separate table)

---

8. Degree and Cardinality of a Relation

Term	Meaning	How to count
Degree (arity)	Number of attributes in the relation	Count the columns
Cardinality	Number of tuples in the relation	Count the rows

8.1 Example

roll_no	name	age	dept
101	Riya	20	CSE
102	Aman	21	ECE
103	Sara	20	CSE

• Degree = 4 (roll_no, name, age, dept)
• Cardinality = 3 (three rows)

8.2 Things to remember

• Degree is a property of the schema — it stays fixed unless you alter the table.
• Cardinality is a property of the instance — it changes whenever you insert or delete rows.
• Relation types by degree: unary (1), binary (2), ternary (3), n-ary (n).
• ⚠️ In database design (ER model), "cardinality" can also mean the relationship ratio (1:1, 1:N, M:N). In this context, it means row count.

---

9. Relation Schema and Relation Instance

9.1 Relation schema — the blueprint

A relation schema gives the name of the relation and its attributes (with domains). It changes rarely.

Student (roll_no: INT, name: VARCHAR(50), age: INT, dept: VARCHAR(10))

Written in short form: R(A1, A2, …, An), and the degree of R is n.

9.2 Relation instance — the snapshot

A relation instance (or relation state) is the set of tuples in the relation at a particular moment. It changes often.

roll_no	name	age	dept
101	Riya	20	CSE
102	Aman	21	ECE

9.3 Database schema vs instance

• Relational database schema = a set of relation schemas plus integrity constraints.
• Relational database instance = the set of all relation instances at a moment in time.

9.4 Comparison

Aspect	Relation Schema	Relation Instance
Meaning	Structure (name, attributes, domains)	Actual rows at a point in time
Also called	Intension	Extension / state
Changes	Rarely	Frequently
Defined with	DDL (CREATE TABLE)	DML (INSERT, UPDATE, DELETE)
Analogy	Blueprint of a form	Filled-in forms

9.5 Valid states

Not every set of tuples is allowed. A valid state is an instance that satisfies all constraints in the schema.

---

10. Keys: Super, Candidate, Primary, Alternate and Foreign

10.1 Why keys?

In a relation, tuples are a set, so no two can be identical. Keys are the attributes that uniquely identify each tuple, and they are also how relations are linked.

We'll use this table throughout:

Student(roll_no, email, name, phone, dept_id)

roll_no	email	name	phone	dept_id
101	riya@x.com	Riya	98111	D1
102	aman@x.com	Aman	98222	D2
103	sara@x.com	Sara	98333	D1

Assume roll_no and email are each unique for every student, and name can repeat.

10.2 Super key

A set of one or more attributes that uniquely identifies a tuple. It may contain extra, unnecessary attributes.

• {roll_no} ✅
• {email} ✅
• {roll_no, name} ✅ (extra attribute name)
• {roll_no, email, name, phone} ✅
• {name} ❌ (names can repeat)

Every superset of a key is also a super key.

10.3 Candidate key

A minimal super key: unique, and if you remove any attribute it stops being unique.

• {roll_no} ✅ and {email} ✅ are candidate keys.
• {roll_no, name} ❌ — not minimal, because roll_no alone is enough.

A relation can have several candidate keys.

10.4 Primary key

The one candidate key chosen by the designer to identify tuples.

• Must be unique
• Must be NOT NULL
• Only one primary key per table (it may be made of several attributes)
• Usually stable (rarely changes) and compact

Here, we choose roll_no as the primary key.

10.5 Alternate key

Any candidate key that was not chosen as the primary key. They are still unique, just not the "official" identifier.

• Here, email is an alternate key.
• Enforced in SQL using UNIQUE (often with NOT NULL).

10.6 Foreign key

An attribute (or set of attributes) in one relation that refers to the primary key (or a candidate key) of another (or the same) relation. It creates the link between tables.

Student                         Department
┌─────────┬────────────┐       ┌─────────┬──────────────┐
│ roll_no │ dept_id(FK)│ ────► │ dept_id │ dept_name    │
│ 101     │ D1         │       │ D1 (PK) │ Computer Sc. │
│ 102     │ D2         │       │ D2 (PK) │ Electronics  │
└─────────┴────────────┘       └─────────┴──────────────┘

• The table holding the foreign key is the referencing (child) table.
• The table being pointed to is the referenced (parent) table.
• A foreign key value must either match an existing value in the parent or be NULL (if allowed).
• A foreign key may be repeated and may be NULL, unlike a primary key.
• A foreign key can refer to the same table (self-reference), e.g., manager_id in an Employee table.

10.7 All keys in SQL

CREATE TABLE Department (
    dept_id    VARCHAR(5) PRIMARY KEY,
    dept_name  VARCHAR(50) NOT NULL
);

CREATE TABLE Student (
    roll_no  INT PRIMARY KEY,                       -- primary key
    email    VARCHAR(100) UNIQUE NOT NULL,          -- alternate key
    name     VARCHAR(50) NOT NULL,
    phone    VARCHAR(15),
    dept_id  VARCHAR(5),
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)   -- foreign key
);

10.8 Comparison table

Key	Unique?	Minimal?	NULL allowed?	How many per table?
Super key	✅	❌ (not necessarily)	—	Many
Candidate key	✅	✅	Not in the chosen primary key	One or more
Primary key	✅	✅	❌ No	Exactly one
Alternate key	✅	✅	Depends on definition (usually not null)	Zero or more
Foreign key	❌ (can repeat)	—	✅ Yes (if allowed)	Zero or more

10.9 How keys relate to each other

Super keys
  └── Candidate keys (minimal super keys)
         ├── Primary key (the chosen one)
         └── Alternate keys (the rest)

Foreign key → points to a primary/candidate key of another table

10.10 A handy counting fact

If a relation with n attributes has a single-attribute candidate key, the number of super keys is 2^(n−1). For example, a relation with 4 attributes where only one attribute is a candidate key has 2³ = 8 super keys.

---

11. Composite and Natural Keys

11.1 Composite key

A key made of two or more attributes that together identify a tuple uniquely. No single attribute in it is enough on its own.

Example: in Enrollment(roll_no, course_id, grade), a student can enroll in many courses and a course has many students, but each (roll_no, course_id) pair appears once.

roll_no	course_id	grade
101	C1	A
101	C2	B
102	C1	A

CREATE TABLE Enrollment (
    roll_no   INT,
    course_id VARCHAR(5),
    grade     CHAR(2),
    PRIMARY KEY (roll_no, course_id),
    FOREIGN KEY (roll_no)   REFERENCES Student(roll_no),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

• Each part of this composite key is itself a foreign key.
• Very common in junction (bridge) tables that implement many-to-many relationships.

11.2 Natural key

A key that comes from the real-world data itself, with real business meaning.

Examples: email, passport number, ISBN of a book, vehicle registration number, national ID.

✅ Pros	❌ Cons
Meaningful to users	Real-world values can change (email, name)
No extra column needed	Might be long (slower joins, larger indexes)
	Might be reused or missing for some records
	May expose sensitive information

11.3 Composite vs Natural — not opposites

These describe different things: composite is about how many attributes form the key; natural is about where the key comes from. A key can be both, e.g., (country_code, national_id) is a composite natural key.

---

12. Surrogate Keys

12.1 Definition

A surrogate key is an artificial identifier created by the system with no business meaning. Its only job is to identify the row.

Typical forms:

• Auto-incrementing integers (1, 2, 3, …)
• Sequences
• UUID / GUID values

CREATE TABLE Customer (
    customer_id  INT AUTO_INCREMENT PRIMARY KEY,   -- surrogate key
    email        VARCHAR(100) UNIQUE NOT NULL,     -- natural key kept as unique
    name         VARCHAR(50)
);

(Syntax for auto-increment differs by DBMS: AUTO_INCREMENT, SERIAL, IDENTITY, sequences.)

12.2 Why use one?

• Stability: never changes, even if business data changes
• Compactness: a small integer is faster to index and join than long text
• Uniqueness guaranteed by the system
• Simpler foreign keys, especially compared with composite keys
• Privacy: doesn't reveal real-world information

12.3 Drawbacks

• No meaning: you can't tell what a row is from its key
• Extra column that is not part of the real data
• Duplicates are still possible unless the natural key is also protected with a UNIQUE constraint
• Values may have gaps (e.g., after rollbacks or deletions) — gaps are normal and harmless
• Random UUIDs can be larger and less index-friendly than sequential integers

✅ Good practice: use a surrogate primary key and keep a UNIQUE constraint on the natural key so the real-world rule is still enforced.

12.4 Natural vs Surrogate

Aspect	Natural Key	Surrogate Key
Source	Real-world data	Generated by the system
Business meaning	Yes	No
Stability	May change	Never changes
Size	Can be large	Usually small
Example	Email, ISBN	customer_id = 1042
Visible to users	Often	Usually hidden

---

13. NULL Values and Their Meaning

13.1 What is NULL?

NULL is a special marker meaning "no value is present." It is not zero, not an empty string, and not a space.

Value	Means
0	The number zero (a real value)
''	An empty string (a real value, just empty)
NULL	No value / unknown / not applicable

13.2 What can NULL mean? (Why it is tricky)

The same marker is used for different situations:

Meaning	Example
Unknown (value exists but isn't known)	A student's date of birth is not recorded yet
Not applicable	spouse_name for an unmarried person
Missing / withheld	A person chose not to give their phone number
Not yet assigned	A new employee with no manager assigned yet

Because one marker covers all these, you cannot tell which meaning applies just by seeing NULL.

13.3 NULL behaves differently: three-valued logic

Normal logic has TRUE and FALSE. With NULL, SQL uses three values: TRUE, FALSE, UNKNOWN.

NULL = NULL     -- UNKNOWN (not TRUE!)
NULL <> 5       -- UNKNOWN
5 + NULL        -- NULL

Truth tables

A	B	A AND B	A OR B
TRUE	UNKNOWN	UNKNOWN	TRUE
FALSE	UNKNOWN	FALSE	UNKNOWN
UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

A	NOT A
TRUE	FALSE
FALSE	TRUE
UNKNOWN	UNKNOWN

A WHERE clause keeps a row only if the condition is TRUE — rows where it is UNKNOWN are dropped.

13.4 Working with NULL in SQL

-- ❌ Wrong: never matches anything
SELECT * FROM Student WHERE phone = NULL;

-- ✅ Right
SELECT * FROM Student WHERE phone IS NULL;
SELECT * FROM Student WHERE phone IS NOT NULL;

-- Replace NULL with a default when displaying
SELECT name, COALESCE(phone, 'Not provided') FROM Student;

13.5 NULL and other operations

Situation	Behaviour
Aggregates (SUM, AVG, MIN, MAX, COUNT(column))	NULLs are ignored
COUNT(*)	Counts all rows, including those with NULLs
ORDER BY	NULLs are placed first or last depending on the DBMS
GROUP BY	All NULLs are grouped together
DISTINCT	NULLs are treated as the same for duplicate removal
Joins	NULL = NULL is not true, so NULLs don't match in a normal join condition
UNIQUE constraint	Behaviour with multiple NULLs varies by DBMS

13.6 NULL and keys

• Primary key: can never be NULL (entity integrity).
• Foreign key: may be NULL, meaning "this row isn't linked to any parent."
• Alternate keys: commonly declared NOT NULL as well.

13.7 Controlling NULLs

CREATE TABLE Employee (
    emp_id  INT PRIMARY KEY,
    name    VARCHAR(50) NOT NULL,       -- must have a value
    phone   VARCHAR(15),                -- NULL allowed
    salary  INT DEFAULT 0               -- default if none supplied
);

💡 Tip: use NOT NULL wherever a value is genuinely required, and use DEFAULT for sensible fallbacks.

---

14. Integrity Constraints

14.1 What are they?

Integrity constraints are rules that the data in a database must always satisfy. They protect the accuracy and consistency of the data, preventing invalid entries at the door.

They are like security checks at a gate: only data that follows the rules gets in.

14.2 Why they matter

• Keep the data correct and meaningful
• Enforce business rules inside the database itself (not just in application code)
• Prevent orphan records and duplicates
• Enforced automatically by the DBMS on every insert, update and delete

14.3 Types of integrity constraints

Type	Purpose	SQL tool
Domain constraint	Values must come from the attribute's domain	Data type, CHECK, NOT NULL, DEFAULT
Key constraint	Key values must be unique	PRIMARY KEY, UNIQUE
Entity integrity	Primary key can't be NULL	PRIMARY KEY
Referential integrity	Foreign keys must match existing parent keys	FOREIGN KEY ... REFERENCES
Semantic / business constraints	Application-specific rules	CHECK, triggers, assertions

14.4 When are constraints checked?

Constraints are checked on INSERT, UPDATE and DELETE. If an operation would violate a constraint, the DBMS rejects it (or applies a defined action, as described in Section 15).

---

15. Entity Integrity and Referential Integrity

15.1 Entity integrity

Rule: No attribute of the primary key may be NULL, and primary key values must be unique.

Why? Each tuple represents a real-world entity. If you can't identify it, you can't refer to it.

INSERT INTO Student (roll_no, email, name) VALUES (NULL, 'x@x.com', 'Zed');
-- ❌ Rejected: roll_no is the primary key and cannot be NULL

INSERT INTO Student (roll_no, email, name) VALUES (101, 'y@y.com', 'Yan');
-- ❌ Rejected if 101 already exists: duplicate primary key

For a composite primary key, every part must be non-NULL.

15.2 Referential integrity

Rule: A foreign key value must either match an existing primary (or candidate) key value in the referenced table, or be NULL (if allowed).

Why? It guarantees that links between tables never point to something that doesn't exist (no orphan rows / "dangling references").

INSERT INTO Student (roll_no, email, name, dept_id)
VALUES (104, 'z@z.com', 'Zoya', 'D9');
-- ❌ Rejected: no department with dept_id = 'D9'

15.3 Operations that can violate referential integrity

Operation	On which table	Possible violation
Insert	Child (referencing)	New FK value has no matching parent
Update	Child	FK changed to a non-existent parent value
Delete	Parent (referenced)	Child rows still point to the deleted parent
Update	Parent	Parent key changed while children still reference the old value
Insert into parent	—	✅ Never violates referential integrity

15.4 Referential actions (what to do on parent delete/update)

Action	Behaviour
RESTRICT / NO ACTION	Reject the operation if child rows exist (the default in most systems)
CASCADE	Apply the same change to child rows (delete children / update their FK)
SET NULL	Set the child's FK to NULL
SET DEFAULT	Set the child's FK to its default value

CREATE TABLE Student (
    roll_no  INT PRIMARY KEY,
    name     VARCHAR(50) NOT NULL,
    dept_id  VARCHAR(5),
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

(Exact support for each option varies slightly between DBMSs.)

15.5 Entity vs Referential integrity

	Entity Integrity	Referential Integrity
Applies to	Primary key	Foreign key
Rule	Not NULL and unique	Must match a parent key or be NULL
Within	A single table	Across two tables (or self-referencing)
Prevents	Unidentifiable rows	Orphan / dangling references

---

16. Domain and Key Constraints

16.1 Domain constraints

Rule: Every value of an attribute must be an atomic value from that attribute's domain.

The domain is enforced using:

Tool	What it enforces
Data type	INT, VARCHAR(50), DATE, DECIMAL(10,2) …
NOT NULL	A value must be provided
DEFAULT	A value used when none is supplied
CHECK	A condition on allowed values
Enumerations / lookup tables	A limited set of legal values

CREATE TABLE Student (
    roll_no  INT PRIMARY KEY,
    name     VARCHAR(50) NOT NULL,
    age      INT CHECK (age BETWEEN 15 AND 60),
    gender   CHAR(1) CHECK (gender IN ('M', 'F', 'O')),
    join_dt  DATE DEFAULT CURRENT_DATE
);

INSERT INTO Student (roll_no, name, age) VALUES (105, 'Neel', 150);
-- ❌ Rejected: age violates the CHECK (domain) constraint

16.2 Key constraints

Rule: The values of the attributes forming a key must be unique across all tuples of the relation; no two tuples may have the same key value.

• Applies to primary keys and candidate/alternate keys (UNIQUE).
• Follows from the fact that a relation is a set of tuples, so duplicates can't exist.
• Every relation has at least one key — in the worst case, the set of all its attributes is a super key.

INSERT INTO Student (roll_no, email, name) VALUES (110, 'riya@x.com', 'Another');
-- ❌ Rejected: email already exists (UNIQUE / alternate key violation)

16.3 Summary of how constraints protect data

Constraint	Protects against
Domain	Invalid or out-of-range values
Key	Duplicate tuples
Entity integrity	Missing identifiers
Referential integrity	Broken links between tables

---

17. Relational Database Properties

17.1 Properties of a relation (table)

These properties follow from the mathematical definition of a relation as a set of tuples.

#	Property	Meaning
1	Unique relation name	Each table has its own name within the database
2	Unique attribute names	Column names are distinct within a table
3	Atomic values	Each cell holds a single, indivisible value
4	Single domain per attribute	All values in a column are of the same kind
5	No duplicate tuples	Every row is distinct (guaranteed by a key)
6	Tuples are unordered	The order of rows has no meaning
7	Attributes are unordered	The order of columns has no meaning (they are identified by name)
8	Fixed structure	Every tuple has the same attributes as defined by the schema

⚠️ Practical note: Real SQL tables can technically allow duplicate rows when no key is defined, and ORDER BY is needed to get a specific row order. Good design (with primary keys) brings SQL tables back in line with the relational model.

17.2 Properties of a relational database

• All data is represented in tables (relations)
• Relationships are expressed through values (keys), not physical pointers
• Integrity is built in through declared constraints
• Declarative language: data is accessed with a high-level language such as SQL
• Data independence: physical storage can change without affecting how users query data
• Set-oriented operations: operations work on whole sets of tuples at a time
• Multiple views of the same data can be created
• Transactions (ACID) keep the data reliable, even with many users

17.3 Putting it together — a complete example

CREATE TABLE Department (
    dept_id    VARCHAR(5)  PRIMARY KEY,
    dept_name  VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Student (
    student_id INT AUTO_INCREMENT PRIMARY KEY,         -- surrogate key
    roll_no    VARCHAR(10) NOT NULL UNIQUE,            -- natural / alternate key
    name       VARCHAR(50) NOT NULL,                   -- domain: NOT NULL
    age        INT CHECK (age BETWEEN 15 AND 60),      -- domain: CHECK
    phone      VARCHAR(15),                            -- NULL allowed
    dept_id    VARCHAR(5),
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)   -- referential integrity
        ON DELETE SET NULL
);

Concept	Where it appears
Relation / Attribute	Student, name, age…
Degree	Student has 6 attributes
Primary key	student_id (also surrogate)
Alternate / natural key	roll_no
Foreign key	dept_id
Domain constraint	CHECK (age …), NOT NULL
Key constraint	PRIMARY KEY, UNIQUE
Entity integrity	student_id cannot be NULL
Referential integrity	dept_id must exist in Department or be NULL
NULL	phone, and dept_id after SET NULL

---

⚡ Quick Revision Cheat Sheet

Data models

• A data model = structure + operations + constraints.
• Hierarchical = tree (1 parent per child, 1 : N).
• Network = graph (many parents, M : N via sets, CODASYL).
• Relational = tables, linked by keys (Codd, 1970).
• Object-oriented = objects with attributes + methods, inheritance.
• Entity-based (ER) = conceptual design; record-based = implementation (hierarchical, network, relational).

Relational terms

• Relation = table, Tuple = row, Attribute = column, Domain = allowed values.
• Degree = number of columns; Cardinality = number of rows.
• Schema = structure (rarely changes); Instance = data at this moment.

Keys

• Super key = unique set of attributes (can have extras).
• Candidate key = minimal super key.
• Primary key = chosen candidate key (unique, not NULL, one per table).
• Alternate key = candidate keys not chosen.
• Foreign key = refers to a key in another (or the same) table; can be NULL or repeat.
• Composite key = made of 2+ attributes; Natural key = real-world data; Surrogate key = system-generated, no meaning.

NULL

• NULL = unknown / not applicable / missing — not 0 or empty string.
• Use IS NULL, never = NULL. Logic is three-valued (TRUE / FALSE / UNKNOWN).
• Aggregates ignore NULLs; COUNT(*) counts all rows.

Integrity

• Domain → valid values; Key → uniqueness; Entity → primary key not NULL; Referential → foreign key matches a parent key or is NULL.
• Referential actions: RESTRICT/NO ACTION, CASCADE, SET NULL, SET DEFAULT.

Relation properties

• Unique names, atomic values, no duplicate tuples, row and column order doesn't matter.

🎤 Commonly asked questions (from these topics)

1. What is a data model? What are its components?
2. Compare hierarchical, network, relational and object-oriented models.
3. Differentiate entity-based and record-based models.
4. Define relation, tuple, attribute and domain with an example.
5. What are degree and cardinality of a relation?
6. Differentiate relation schema and relation instance.
7. Differentiate super key, candidate key, primary key and alternate key.
8. What is a foreign key? Can it be NULL? Can it have duplicates?
9. What is a composite key? Give an example.
10. Natural key vs surrogate key — which is better and why?
11. What does NULL mean? Why is NULL = NULL not true?
12. What are integrity constraints? Name and explain each type.
13. Explain entity integrity and referential integrity with examples.
14. What operations can violate referential integrity, and how are they handled?
15. What are the properties of a relation?

---

A friendly, start-to-advanced guide to designing clean relational tables: why bad design hurts, how functional dependencies describe the data, and how normal forms (1NF to 5NF) fix the mess. Read top to bottom the first time. Use the Quick Revision section before exams or interviews.

---

📑 Table of Contents

Part A — The Problem

1. Data Redundancy and Update Anomalies

Part B — Functional Dependency Theory 2. Functional Dependencies (FDs) 3. Trivial and Non-Trivial Dependencies 4. Full and Partial Functional Dependencies 5. Transitive Dependencies 6. Attribute Closure 7. Closure of Functional Dependencies 8. Armstrong's Axioms 9. Candidate Key Identification 10. Minimal Cover / Canonical Cover

Part C — Decomposition 11. Decomposition of Relations 12. Lossless-Join Decomposition 13. Dependency Preservation

Part D — Normal Forms 14. First Normal Form (1NF) 15. Second Normal Form (2NF) 16. Third Normal Form (3NF) 17. Boyce-Codd Normal Form (BCNF) 18. Multivalued Dependencies 19. Fourth Normal Form (4NF) 20. Join Dependencies 21. Fifth Normal Form (5NF) 22. Normal Forms at a Glance

Part E — Going the Other Way 23. Denormalization and Its Trade-offs

24. Quick Revision Cheat Sheet

---

Part A — The Problem

1. Data Redundancy and Update Anomalies

1.1 What is data redundancy?

Redundancy means the same piece of information is stored more than once. A little controlled duplication can be useful, but uncontrolled duplication causes trouble.

1.2 A badly designed table

Enrollment(roll_no, student_name, course_id, course_title, instructor, grade)

roll_no	student_name	course_id	course_title	instructor	grade
101	Riya	C1	Databases	Dr. Rao	A
101	Riya	C2	Networks	Dr. Iyer	B
102	Aman	C1	Databases	Dr. Rao	A
103	Sara	C1	Databases	Dr. Rao	B

Notice how "Databases / Dr. Rao" repeats for every student in course C1, and "Riya" repeats for every course she takes.

1.3 Problems caused by redundancy

Problem	Meaning
Wasted storage	The same facts are stored again and again
Inconsistency risk	Copies can disagree (e.g., "Dr. Rao" in one row and "Dr. R. Rao" in another)
Update anomaly	Changing one fact means updating many rows
Insertion anomaly	Can't store one fact without another
Deletion anomaly	Deleting one fact accidentally destroys another

1.4 The three anomalies

🔸 Update (modification) anomaly If Dr. Rao is replaced by Dr. Mehta for Databases, you must change every row with C1. If you miss one, the data becomes inconsistent.

🔸 Insertion anomaly You want to add a new course C3 – Compilers that nobody has enrolled in yet. You can't — the primary key is (roll_no, course_id) and roll_no would have to be NULL. A fact about courses is trapped behind student data.

🔸 Deletion anomaly If Sara (103) drops out and her row is deleted, nothing is lost for C1 because others still have it. But if Aman were the only student in a course and his row is deleted, the entire course information vanishes too.

1.5 The cure

The cure is normalization: organise data so that each fact is stored in exactly one place. To do that, we first need a precise way to describe relationships between attributes: functional dependencies.

---

Part B — Functional Dependency Theory

2. Functional Dependencies (FDs)

2.1 Definition

A functional dependency X → Y (read "X determines Y") holds in a relation if whenever two tuples have the same value for X, they must have the same value for Y.

• X is called the determinant (left-hand side, LHS)
• Y is called the dependent (right-hand side, RHS)

Everyday example: A roll number determines a student's name. If two rows have roll_no 101, they must show the same name. So roll_no → student_name.

2.2 Example

roll_no	name	dept	hod
101	Riya	CSE	Dr. Rao
102	Aman	ECE	Dr. Iyer
103	Sara	CSE	Dr. Rao

• roll_no → name ✅ (each roll number has one name)
• roll_no → dept ✅
• dept → hod ✅ (each department has one head)
• name → roll_no ❌ (two students could share a name)
• dept → roll_no ❌ (CSE has 101 and 103)

2.3 Important points

• An FD is a constraint about the meaning (semantics) of the data, decided by the real-world rules, not by looking at one sample table. A sample can only disprove an FD, never prove it.
• If X is a key, X determines every attribute of the relation.
• Both sides can have multiple attributes, e.g., {roll_no, course_id} → grade.
• X → Y does not mean Y → X.
• Notation: A → BC means A → B and A → C.

2.4 Quick test for an FD in a table

To check X → Y against a table: group rows by X; if any group has more than one distinct Y, the FD is violated.

---

3. Trivial and Non-Trivial Dependencies

Type	Condition	Example
Trivial	Y ⊆ X (RHS is part of LHS)	{roll_no, name} → name
Non-trivial	Y ⊄ X (at least one RHS attribute is not in LHS)	roll_no → name
Completely non-trivial	X ∩ Y = ∅ (nothing in common)	roll_no → name

• Trivial FDs are always true and give no new information.
• Example of non-trivial but not completely non-trivial: {roll_no, name} → {name, dept} (name appears on both sides, but dept is new).
• Normal forms and design rules focus on non-trivial FDs.

---

4. Full and Partial Functional Dependencies

These matter when the determinant is composite (has more than one attribute).

4.1 Full functional dependency

X → Y is a full FD if Y depends on all of X, and removing any attribute from X breaks the dependency.

4.2 Partial functional dependency

X → Y is partial if some proper subset of X already determines Y.

4.3 Example

OrderItem(order_id, product_id, product_name, qty), key = (order_id, product_id)

FD	Type	Why
{order_id, product_id} → qty	Full	Quantity needs both order and product
{order_id, product_id} → product_name	Partial	product_id → product_name alone is enough

4.4 Remember

• A partial dependency can only occur when the key is composite.
• If the determinant is a single attribute, the FD is automatically full.
• Partial dependencies are exactly what 2NF removes.

---

5. Transitive Dependencies

5.1 Definition

A transitive dependency exists when X → Y and Y → Z (with Y not determining X, and Y not a key), so X → Z holds indirectly through Y.

 X  ───►  Y  ───►  Z
 └──────────────────►   (X → Z is transitive)

5.2 Example

Student(roll_no, name, dept, hod)

• roll_no → dept
• dept → hod
• Therefore roll_no → hod (transitively, via dept)

The head of department is really a fact about the department, not the student, so it gets repeated for every student in that department. This is a source of anomalies, and exactly what 3NF removes.

5.3 Conditions to remember

• Y must be a non-prime / non-key attribute (otherwise X → Y and Y → Z is not considered a harmful transitive dependency).
• The dependency of a non-key attribute on another non-key attribute is the classic trouble spot.

---

6. Attribute Closure

6.1 Definition

The closure of an attribute set X under a set of FDs F, written X⁺, is the set of all attributes that X functionally determines.

6.2 Algorithm (the "keep growing" method)

1. Start with result = X
2. Repeat:
     for each FD  A → B  in F:
         if A ⊆ result:  add B to result
   until result stops changing
3. X⁺ = result

6.3 Worked example

Relation R(A, B, C, D, E) with F = { A → B, BC → D, D → E }

Find A⁺:

• Start: {A}
• A → B ⇒ {A, B}
• BC → D? Need C, which we don't have. ✗
• Result: A⁺ = {A, B}

Find (AC)⁺:

• Start: {A, C}
• A → B ⇒ {A, B, C}
• BC → D ⇒ {A, B, C, D}
• D → E ⇒ {A, B, C, D, E}
• Result: (AC)⁺ = {A, B, C, D, E} → AC determines everything.

6.4 What is attribute closure used for?

Use	How
Test if X is a super key	X⁺ contains all attributes of R
Find candidate keys	Find minimal X with X⁺ = R
Check if an FD X → Y follows from F	Compute X⁺ and see whether Y ⊆ X⁺
Check equivalence of two FD sets	See if each FD in one set is implied by the other
Test dependency preservation	Used in the preservation algorithm (Section 13)

---

7. Closure of Functional Dependencies (F⁺)

7.1 Definition

F⁺ is the set of ALL functional dependencies that are logically implied by F, including trivial ones.

7.2 Example

R(A, B, C), F = { A → B, B → C }

F⁺ includes (among many others):

• Given: A → B, B → C
• Derived: A → C (transitivity), A → BC (union)
• Trivial: A → A, AB → A, ABC → AB, …
• Augmented: AC → BC, AB → BC, …

7.3 Key points

• F⁺ can be very large (it grows exponentially with the number of attributes), so we rarely compute it fully.
• Instead of computing F⁺, we use attribute closure (X⁺) to answer questions about it efficiently.
• Two FD sets F and G are equivalent if F⁺ = G⁺, meaning each can derive the other.
• F⁺ is generated by repeatedly applying Armstrong's axioms.

---

8. Armstrong's Axioms

A small set of inference rules to derive new FDs from given ones. They are sound (they never produce an invalid FD) and complete (they can produce every FD in F⁺).

8.1 The three primary axioms

Axiom	Rule	Meaning
Reflexivity	If Y ⊆ X, then X → Y	A set determines its own subsets (trivial FDs)
Augmentation	If X → Y, then XZ → YZ	Adding the same attributes to both sides keeps it true
Transitivity	If X → Y and Y → Z, then X → Z	Dependencies chain together

8.2 Derived (secondary) rules

These can be proved from the three axioms, and are handy shortcuts.

Rule	Statement	Example
Union	If X → Y and X → Z, then X → YZ	A → B, A → C ⇒ A → BC
Decomposition (projectivity)	If X → YZ, then X → Y and X → Z	A → BC ⇒ A → B, A → C
Pseudotransitivity	If X → Y and WY → Z, then WX → Z	A → B, CB → D ⇒ CA → D
Composition	If X → Y and Z → W, then XZ → YW	A → B, C → D ⇒ AC → BD

8.3 Proof sketch: Union rule

Given X → Y and X → Z:

1. Augment X → Y with X: X → XY
2. Augment X → Z with Y: XY → YZ
3. Transitivity on (1) and (2): X → YZ ✅

8.4 ⚠️ Common mistakes

• Wrong: A → B implies B → A ❌ (FDs are not symmetric)
• Wrong: AB → C implies A → C ❌ (can't drop from the LHS)
• Right: A → BC implies A → B ✅ (can split the RHS)

---

9. Candidate Key Identification

9.1 Quick reminders

• A super key is any set whose closure is the whole relation.
• A candidate key is a minimal super key.
• A relation can have multiple candidate keys.
• Prime attribute = part of at least one candidate key; non-prime attribute = part of none.

9.2 Step-by-step method

1. List all attributes of R and all FDs.
2. Attributes that never appear on the RHS of any FD must be in every candidate key (nothing else can determine them).
3. Attributes that appear only on the RHS (never on the LHS) are in no candidate key.
4. Compute the closure of the "must-have" attributes. If it covers everything → that's the (only) candidate key.
5. If not, add other attributes one by one (or in small groups) and test closures, keeping sets minimal.
6. Look for other keys: if some FD X → A has A inside a key, you can swap A with X to get another key.

9.3 Example 1 — single candidate key

R(A, B, C, D, E), F = { A → B, BC → D, D → E }

• Never on RHS: A, C → they must be in every key.
• (AC)⁺ = {A, B, C, D, E} = everything ✅
• Check minimality: A⁺ = {A, B} ✗, C⁺ = {C} ✗ → AC is minimal.
• Candidate key = {AC}; prime attributes: A, C.

9.3 Example 2 — multiple candidate keys

R(A, B, C, D), F = { A → B, B → C, C → D, D → A }

• A⁺ = {A, B, C, D} ✅ → A is a key
• B⁺ = {B, C, D, A} ✅ → B is a key
• C⁺ ✅, D⁺ ✅
• Candidate keys = {A}, {B}, {C}, {D} — every attribute is prime.

9.4 Example 3 — a classic

R(Student, Subject, Teacher), F = { {Student, Subject} → Teacher, Teacher → Subject }

• Never on RHS: Student → must be in every key.
• (Student, Subject)⁺ = all ✅
• (Student, Teacher)⁺: Teacher → Subject, so = all ✅
• Candidate keys = {Student, Subject}, {Student, Teacher}; prime attributes: Student, Subject, Teacher.

---

10. Minimal Cover / Canonical Cover

10.1 What and why

A minimal cover (also called canonical cover), written Fc, is a simplified FD set equivalent to F (same F⁺) with no redundancy. It is the starting point for 3NF synthesis and for removing needless checking work.

10.2 A set Fc is minimal if:

1. Every RHS has a single attribute.
2. No extraneous attribute on any LHS (can't remove any attribute from a LHS without changing the closure).
3. No redundant FD (can't remove any FD without changing the closure).

10.3 The three steps

Step	Action
1. Split RHS	Replace X → YZ with X → Y and X → Z
2. Remove extraneous LHS attributes	For XA → B, if B ∈ X⁺ (computed using the full set), then A is extraneous; replace with X → B
3. Remove redundant FDs	For each FD X → B, temporarily remove it; if B ∈ X⁺ using the remaining FDs, it's redundant → delete it

10.4 Worked example

F = { A → BC, B → C, A → B, AB → C, AC → D }

Step 1 — Split RHS A → B, A → C, B → C, A → B (duplicate), AB → C, AC → D → Remove the duplicate: { A → B, A → C, B → C, AB → C, AC → D }

Step 2 — Remove extraneous LHS attributes

• AB → C: is B extraneous? Check A⁺ under F: A → B, A → C ⇒ A⁺ ⊇ {A, B, C}; C ∈ A⁺ ✅ → B extraneous. Becomes A → C (already present).
• AC → D: is C extraneous? A⁺ includes C, and so with A we reach D? Check: A⁺ = {A, B, C} and AC → D fires since C ∈ A⁺ ⇒ D ∈ A⁺ ✅ → C extraneous. Becomes A → D.

Now: { A → B, A → C, B → C, A → D }

Step 3 — Remove redundant FDs

• A → C: without it, A → B and B → C give A → C ✅ → redundant, remove.
• A → B: without it, A⁺ = {A, C, D}, no B ✗ → needed.
• B → C: needed (no other way to get C from B).
• A → D: needed.

Minimal cover: Fc = { A → B, B → C, A → D }

ℹ️ A minimal cover is not always unique: different orders of removal can give different (but equivalent) covers.

---

Part C — Decomposition

11. Decomposition of Relations

11.1 What is it?

Decomposition means splitting one relation R into two or more smaller relations (R1, R2, …) so that:

• every attribute of R appears in at least one Ri, and
• the pieces are better designed (fewer anomalies).

11.2 Example

Enrollment(roll_no, student_name, course_id, course_title, instructor, grade) could be split into:

• Student(roll_no, student_name)
• Course(course_id, course_title, instructor)
• Enrollment(roll_no, course_id, grade)

11.3 A good decomposition must have

Property	Why it matters
Lossless join (mandatory)	Joining pieces gives back exactly the original data, with no lost or fake tuples
Dependency preservation (desirable)	All original FDs can still be enforced without joining tables
Reduced redundancy	Pieces are in a higher normal form

11.4 Lossy vs lossless in one line

• Lossy decomposition: the join produces extra (spurious) tuples, so information is lost (you can no longer tell which tuples were real).
• Lossless: the join returns exactly the original relation.

---

12. Lossless-Join Decomposition

12.1 Definition

A decomposition of R into R1 and R2 is lossless if R1 ⋈ R2 = R for every valid instance of R.

12.2 The test (binary decomposition)

Decomposition of R into R1 and R2 is lossless if at least one holds in F⁺:

• (R1 ∩ R2) → R1, or
• (R1 ∩ R2) → R2

In words: the common attributes must be a key (super key) of at least one of the two pieces.

12.3 ✅ Lossless example

R(A, B, C), F = { A → B }. Decompose into R1(A, B) and R2(A, C).

• R1 ∩ R2 = {A}; A → B and A⁺ ⊇ {A, B} = R1 ✅ → lossless.

12.4 ❌ Lossy example

R(Student, Course, Teacher) with no helpful FDs. Decompose into R1(Student, Course) and R2(Course, Teacher).

Original:

Student	Course	Teacher
Riya	DB	Rao
Aman	DB	Iyer

R1 = {(Riya, DB), (Aman, DB)}, R2 = {(DB, Rao), (DB, Iyer)}

R1 ⋈ R2 gives 4 rows, including fake ones like (Riya, DB, Iyer) ❌. Since Course is not a key of either piece, the decomposition is lossy.

12.5 Notes

• For more than two pieces, a tableau (chase) test is used; for exam problems you can often decompose step by step and apply the binary test each time.
• Decomposing on an FD X → Y into (X ∪ Y) and (R − Y) (with X kept in both) is always lossless, which is the foundation of the normalization algorithms.
• Lossless-join is mandatory; the other properties are desirable.

---

13. Dependency Preservation

13.1 Definition

A decomposition of R into R1, …, Rn preserves dependencies if every FD in F can be checked within a single smaller table (or derived from FDs that can).

Formally: let Fi = the FDs of F⁺ that involve only attributes of Ri. The decomposition is dependency preserving if

(F1 ∪ F2 ∪ … ∪ Fn)⁺ = F⁺

13.2 Why does it matter?

If an FD is not preserved, enforcing it requires joining tables on every insert/update, which is slow and error-prone. Otherwise, the DBMS can check each constraint cheaply inside a single table.

13.3 ✅ Preserved example

R(A, B, C), F = { A → B, B → C }. Decompose into R1(A, B) and R2(B, C).

• A → B lives in R1 ✅
• B → C lives in R2 ✅
• Also lossless (R1 ∩ R2 = B, and B → C). → Lossless AND dependency preserving.

13.4 ❌ Not preserved example

R(A, B, C), F = { AB → C, C → B }. Decompose into R1(A, C) and R2(B, C).

• C → B lives in R2 ✅
• AB → C: no table contains A, B and C together ❌ → lost
• Lossless? R1 ∩ R2 = C, and C → B means C is a key of R2 ✅ — so lossless but not dependency preserving.

13.5 Practical test (using attribute closures)

For each FD X → Y in F:

1. Set result = X.
2. Repeat: for each Ri, result = result ∪ ((result ∩ Ri)⁺ ∩ Ri) (closure under F, restricted to Ri).
3. Until no change. If Y ⊆ result, the FD is preserved.

13.6 Summary

Property	Mandatory?	Guarantees
Lossless join	✅ Yes	No information lost
Dependency preservation	⭐ Desirable	Cheap constraint checking
3NF decomposition (synthesis)	Can always achieve both
BCNF decomposition	Can always achieve lossless, but not always dependency preserving

---

Part D — Normal Forms

Normalization is the process of organising tables into progressively stricter "normal forms" to remove redundancy and anomalies. Each form builds on the one before.

Unnormalized → 1NF → 2NF → 3NF → BCNF → 4NF → 5NF
                      (more restrictions, less redundancy)

Our running example (used for 1NF → 3NF)

OrderDetails(order_id, product_id, product_name, customer_id, customer_name, customer_city, qty)

Business rules (FDs):

• {order_id, product_id} → qty
• order_id → customer_id
• customer_id → customer_name, customer_city
• product_id → product_name

Candidate key: {order_id, product_id}

---

14. First Normal Form (1NF)

14.1 Rule

A relation is in 1NF if:

1. Every attribute holds atomic (single, indivisible) values.
2. There are no repeating groups or multivalued attributes (no lists/arrays in a cell).
3. Each row is unique (there is a primary key).

14.2 ❌ Violating example

student_id	name	phones
1	Riya	98111, 98222
2	Aman	98333

phones holds multiple values in one cell.

14.3 ✅ Fix — Option A: one value per row

student_id	name	phone
1	Riya	98111
1	Riya	98222
2	Aman	98333

14.4 ✅ Fix — Option B (preferred): separate table

Student(student_id, name) StudentPhone(student_id, phone)

14.5 Also violates 1NF

• Repeating columns: phone1, phone2, phone3
• Composite (non-atomic) values: address = "12 MG Road, Pune, 411001" when you need to query the city separately

14.6 Notes

• 1NF is the minimum requirement for a relational table.
• It does not remove all redundancy (our running example is in 1NF but still full of anomalies).

---

15. Second Normal Form (2NF)

15.1 Rule

A relation is in 2NF if:

1. It is in 1NF, and
2. No non-prime attribute is partially dependent on any candidate key. (Every non-key attribute must depend on the whole key.)

15.2 When does it apply?

• Only matters when a candidate key is composite.
• If all candidate keys are single attributes, a 1NF relation is automatically in 2NF.

15.3 Checking the running example

Key = {order_id, product_id}

FD	Problem
product_id → product_name	Partial (part of key determines it) ❌
order_id → customer_id, customer_name, customer_city	Partial ❌
{order_id, product_id} → qty	Full ✅

So the table is not in 2NF.

15.4 Decomposition to 2NF

Move partially dependent attributes into tables keyed by the part of the key that determines them:

• OrderItem(order_id, product_id, qty) — key {order_id, product_id}
• Product(product_id, product_name) — key product_id
• Orders(order_id, customer_id, customer_name, customer_city) — key order_id

All three are in 2NF now. ✅ But look at Orders: customer_name and customer_city depend on customer_id, not directly on the key. That's the next problem.

---

16. Third Normal Form (3NF)

16.1 Rule

A relation is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on a candidate key.

16.2 Formal definition (very useful in exams)

For every non-trivial FD X → A in the relation, at least one must hold:

1. X is a super key, or
2. A is a prime attribute (A is part of some candidate key).

16.3 Checking Orders

Orders(order_id, customer_id, customer_name, customer_city)

• order_id → customer_id ✅ (order_id is the key)
• customer_id → customer_name, customer_city ❌ (customer_id is not a super key, and the RHS attributes are non-prime)
• So order_id → customer_name is transitive (via customer_id) → not in 3NF.

16.4 Decomposition to 3NF

• Orders(order_id, customer_id)
• Customer(customer_id, customer_name, customer_city)

16.5 Final 3NF design

Table	Key	Contents
OrderItem	(order_id, product_id)	qty
Product	product_id	product_name
Orders	order_id	customer_id (FK)
Customer	customer_id	customer_name, customer_city

Each fact now lives in one place; the anomalies from Section 1 are gone. ✅

16.6 3NF synthesis algorithm (advanced)

A standard method that always gives a lossless, dependency-preserving 3NF decomposition:

1. Find a minimal cover Fc of F.
2. Group FDs in Fc by the same LHS; for each group X → A1, A2, … create a relation (X, A1, A2, …).
3. If no relation contains a candidate key of R, add one relation made of a candidate key.
4. Remove any relation whose attributes are fully contained in another.

16.7 Why 3NF is popular

• It is always achievable with lossless join + dependency preservation.
• It removes most real-world redundancy.
• It may still keep small redundancies where a non-prime FD has a prime attribute on the RHS (the gap BCNF closes).

---

17. Boyce-Codd Normal Form (BCNF)

17.1 Rule

A relation is in BCNF if, for every non-trivial FD X → Y, X is a super key.

It is a stricter version of 3NF: BCNF removes the "or A is prime" escape clause.

17.2 BCNF vs 3NF

• Every BCNF relation is in 3NF, but not every 3NF is in BCNF.
• The two differ only when a relation has multiple overlapping composite candidate keys.

17.3 Classic example (3NF but not BCNF)

R(Student, Subject, Teacher)

Business rules:

• Each teacher teaches only one subject: Teacher → Subject
• A student learns a subject from one teacher: {Student, Subject} → Teacher

Candidate keys: {Student, Subject} and {Student, Teacher} → all three attributes are prime.

Student	Subject	Teacher
Riya	DB	Rao
Aman	DB	Rao
Sara	Networks	Iyer

• 3NF? For Teacher → Subject, Teacher is not a super key, but Subject is prime → allowed → in 3NF ✅
• BCNF? Teacher is not a super key ❌ → not in BCNF.

Problem (redundancy / anomaly): the fact "Rao teaches DB" is repeated for every student. You can't record that a new teacher teaches a subject until a student enrolls with them.

17.4 Decomposing to BCNF

For each violating FD X → Y, split R into:

• R1 = (X ∪ Y)
• R2 = (R − (Y − X)) (i.e., keep X, drop Y from the rest)

Apply to Teacher → Subject:

• R1(Teacher, Subject) — key Teacher
• R2(Student, Teacher) — key {Student, Teacher}

Both are in BCNF ✅ and the decomposition is lossless (common attribute Teacher is a key of R1).

17.5 The catch: dependency preservation

The FD {Student, Subject} → Teacher now spans two tables, so it cannot be checked without a join ❌.

	3NF	BCNF
Lossless decomposition always possible?	✅	✅
Dependency-preserving always possible?	✅	❌ Not always
Redundancy removed	Almost all	All FD-based redundancy

In practice, designers typically aim for BCNF, and settle for 3NF when BCNF would lose an important dependency.

17.6 BCNF decomposition algorithm

result = {R}
while some relation Ri in result is not in BCNF:
    pick a violating FD  X → Y  in Ri   (X is not a super key of Ri)
    replace Ri with  (X ∪ Y)  and  (Ri − (Y − X))

---

18. Multivalued Dependencies (MVDs)

18.1 The situation

Sometimes a table has two or more independent multi-valued facts about the same thing. FDs can't describe this, and BCNF isn't enough to fix the redundancy.

18.2 Example

CourseInfo(Course, Teacher, Book)

Rules: a course can have several teachers and several recommended books, and the teachers and books are independent of each other.

Course	Teacher	Book
DB	Rao	Korth
DB	Rao	Elmasri
DB	Iyer	Korth
DB	Iyer	Elmasri
OS	Mehta	Galvin

For DB, every teacher is paired with every book (cross product). To add a new teacher, you must add a row for every book — redundancy and update anomalies!

The key is all three attributes (Course, Teacher, Book), there are no non-trivial FDs, so the table is in BCNF, yet it clearly has a design problem.

18.3 Definition

An MVD X ↠ Y (read "X multidetermines Y") holds if, for each value of X, the set of Y-values is independent of the values of the remaining attributes Z = R − X − Y.

• Here: Course ↠ Teacher and Course ↠ Book.
• Whenever an MVD X ↠ Y exists with Z remaining, you get the cross-product pattern: if (x, y1, z1) and (x, y2, z2) exist, then (x, y1, z2) and (x, y2, z1) must exist.

18.4 Trivial vs non-trivial MVD

X ↠ Y is trivial if:

• Y ⊆ X, or
• X ∪ Y = R (Z is empty)

Otherwise it is non-trivial.

18.5 Important properties

Property	Statement
Replication	Every FD is also an MVD: if X → Y then X ↠ Y
Complementation	If X ↠ Y, then X ↠ R − X − Y (so MVDs come in pairs: `Course ↠ Teacher
Augmentation	If X ↠ Y and W ⊆ Z..., then WX ↠ Y (more inference rules exist)
Transitivity	If X ↠ Y and Y ↠ Z, then X ↠ (Z − Y)

18.6 FD vs MVD

	FD (X → Y)	MVD (X ↠ Y)
X determines	Exactly one Y value	A set of Y values
Generates	Equality constraint	Tuple-generating constraint
Typical cause	Dependent attributes	Independent multi-valued attributes
Fixed by	2NF, 3NF, BCNF	4NF

---

19. Fourth Normal Form (4NF)

19.1 Rule

A relation is in 4NF if it is in BCNF and, for every non-trivial MVD X ↠ Y, X is a super key.

19.2 Fixing the example

CourseInfo(Course, Teacher, Book) has non-trivial MVDs Course ↠ Teacher and Course ↠ Book, but Course is not a super key → not in 4NF.

Decompose (for the MVD X ↠ Y, split into (X ∪ Y) and (X ∪ Z)):

CourseTeacher(Course, Teacher)

Course	Teacher
DB	Rao
DB	Iyer
OS	Mehta

CourseBook(Course, Book)

Course	Book
DB	Korth
DB	Elmasri
OS	Galvin

Now adding a new teacher is one row, and teachers and books are stored independently. ✅

19.3 Notes

• Every 4NF relation is in BCNF (and so 3NF, 2NF, 1NF).
• 4NF only matters when a table contains two or more independent multivalued facts.
• A table with only FDs (no non-trivial MVDs) that is in BCNF is automatically in 4NF.
• This decomposition is lossless, since Course is the common attribute and Course ↠ Teacher holds.

---

20. Join Dependencies

20.1 Idea

A join dependency (JD) says that a relation can be split into several projections and rebuilt exactly by joining them, with no loss and no extra rows.

20.2 Definition

A relation R satisfies the join dependency *(R1, R2, …, Rn) if

R = π_R1(R) ⋈ π_R2(R) ⋈ … ⋈ π_Rn(R)

where R1 ∪ R2 ∪ … ∪ Rn = R.

• If the JD holds, then decomposing R into R1, …, Rn is lossless.
• A JD is trivial if one of the Ri is R itself.

20.3 Relationship with MVD

• An MVD is a special case of a JD with just two components: X ↠ Y is the same as the JD *(XY, XZ).
• General JDs involve three or more components and can't always be expressed using MVDs.
• This makes JDs the most general dependency discussed in these notes (FD ⊂ MVD ⊂ JD).

20.4 Example

A relation SPJ(Supplier, Part, Project) satisfies the JD *( (Supplier, Part), (Part, Project), (Supplier, Project) ) if it can be rebuilt by joining those three projections. The business rule that makes this true is in the next section.

---

21. Fifth Normal Form (5NF)

Also called Project-Join Normal Form (PJNF).

21.1 Rule

A relation is in 5NF if it is in 4NF and every non-trivial join dependency in it is implied by its candidate keys.

In simple words: the table can't be split into smaller tables without losing information, unless the split is "obvious" from the keys.

21.2 Example: Supplier–Part–Project

SPJ(Supplier, Part, Project) records which supplier supplies which part to which project.

Business rule (cyclic constraint): If supplier S supplies part P, and project J uses part P, and supplier S supplies to project J, then S supplies part P to project J.

Supplier	Part	Project
S1	P1	J2
S1	P2	J1
S2	P1	J1
S1	P1	J1

Because of the rule, SPJ equals the join of its three binary projections:

• SP(Supplier, Part)
• PJ(Part, Project)
• SJ(Supplier, Project)

SPJ has no non-trivial MVDs (so it is in 4NF) but it has a non-trivial JD that isn't implied by the candidate key (Supplier, Part, Project) → not in 5NF.

5NF design: replace SPJ with SP, PJ and SJ — redundancy is gone, and the original can be reconstructed with a three-way join. ✅

21.3 ⚠️ Important caution

• If the cyclic business rule does not hold, decomposing SPJ into those three tables would be lossy (it would create false tuples). 5NF depends on real-world rules, never on a sample.
• 5NF situations are rare in practice, and often considered more of a theoretical level of design.

---

22. Normal Forms at a Glance

22.1 The nesting

┌──────────────────────────────────────────────────┐
│ 1NF                                              │
│  ┌────────────────────────────────────────────┐  │
│  │ 2NF                                        │  │
│  │  ┌──────────────────────────────────────┐  │  │
│  │  │ 3NF                                  │  │  │
│  │  │  ┌────────────────────────────────┐  │  │  │
│  │  │  │ BCNF                           │  │  │  │
│  │  │  │  ┌──────────────────────────┐  │  │  │  │
│  │  │  │  │ 4NF  ⊃  5NF              │  │  │  │  │
│  │  │  │  └──────────────────────────┘  │  │  │  │
│  │  │  └────────────────────────────────┘  │  │  │
│  │  └──────────────────────────────────────┘  │  │
│  └────────────────────────────────────────────┘  │
└──────────────────────────────────────────────────┘
        5NF ⊂ 4NF ⊂ BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF

22.2 Comparison table

Normal form	Requirement (in addition to the previous one)	Removes	Dependency involved
1NF	Atomic values, no repeating groups	Multivalued cells, repeating groups	—
2NF	No partial dependency of non-prime attributes on a candidate key	Partial dependencies	FD
3NF	No transitive dependency of non-prime attributes (for each X → A: X super key or A prime)	Transitive dependencies	FD
BCNF	For every non-trivial X → Y, X is a super key	Remaining FD-based anomalies	FD
4NF	For every non-trivial X ↠ Y, X is a super key	Independent multivalued facts	MVD
5NF	Every non-trivial JD is implied by candidate keys	Join-dependency redundancy	JD

22.3 Decomposition guarantees

Target	Lossless join	Dependency preserving
3NF	✅ Always	✅ Always
BCNF	✅ Always	⚠️ Not always
4NF	✅ Always	⚠️ Not always

22.4 Quick way to find the highest normal form (exam method)

1. Find all candidate keys and the prime attributes.
2. Check partial dependencies (non-prime attribute depending on part of a candidate key) → if any, it's only in 1NF.
3. Check transitive dependencies: for each X → A, if X isn't a super key and A isn't prime → it's only in 2NF.
4. Check BCNF: if every determinant is a super key → BCNF; otherwise it's in 3NF.
5. Check non-trivial MVDs → 4NF; check JDs → 5NF.

---

Part E — Going the Other Way

23. Denormalization and Its Trade-offs

23.1 What is it?

Denormalization is the deliberate introduction of redundancy into a normalized design (by merging tables or adding duplicate/derived data) to improve read performance.

Normalize first to get the design right; denormalize selectively and knowingly only where performance demands it.

23.2 Why would you do it?

Normalized designs spread data across many tables, so common queries need many joins, which can be slow on large data sets, especially in read-heavy systems like reports and dashboards.

23.3 Common techniques

Technique	Description	Example
Adding redundant columns	Copy a frequently used column into another table to avoid a join	Store customer_name in Orders
Pre-computed / derived columns	Store calculated values	order_total in Orders
Merging tables	Combine tables that are always joined (e.g., 1:1 relationships)	Employee + EmployeeProfile
Summary / aggregate tables	Store pre-aggregated results	Daily sales totals
Storing repeating groups	Keep a few repeated values in columns for speed (violates 1NF)	phone1, phone2
Materialized views	Store the result of a query physically and refresh it	Monthly report view

23.4 Trade-offs

✅ Benefits	❌ Costs
Faster reads (fewer joins)	Redundant data uses more storage
Simpler, shorter queries	Slower writes (many copies to update)
Faster reporting and analytics	Risk of inconsistency if copies drift apart
Fewer tables to scan	Anomalies return (update, insert, delete)
	More complex code needed to keep copies in sync (triggers, application logic)
	Harder to maintain and change the schema

23.5 When to denormalize (and when not to)

Consider denormalizing when…	Avoid when…
Workload is read-heavy and write-light	Data changes frequently
Joins are a proven bottleneck (measure first!)	Accuracy and consistency are critical (banking, billing)
Reporting / data warehouse / analytics systems	A simpler fix like indexing or query tuning would work
Derived values are expensive to recompute	You haven't measured a real problem

23.6 Golden rules

1. Normalize first, then denormalize only for a measured performance need.
2. Document every denormalization decision.
3. Add safeguards (triggers, constraints, scheduled refreshes) to keep redundant copies consistent.
4. Try indexing and query optimization before denormalizing.

23.7 Normalization vs Denormalization

Aspect	Normalization	Denormalization
Goal	Minimize redundancy, ensure integrity	Maximize read speed
Tables	More, smaller	Fewer, larger
Joins	More	Fewer
Read speed	Slower for complex queries	Faster
Write speed	Faster	Slower
Storage	Less	More
Anomalies	Avoided	Possible
Best for	Transactional systems (OLTP)	Reporting / analytics (read-heavy)

---

⚡ Quick Revision Cheat Sheet

Why normalize?

• Redundancy → update, insertion, deletion anomalies. Normalization = one fact in one place.

FD basics

• X → Y: same X ⇒ same Y. Trivial: Y ⊆ X.
• Partial: part of a composite key determines it. Full: needs the whole key.
• Transitive: X → Y → Z with Y non-key.

Closure & rules

• X⁺ = all attributes determined by X. X is a super key iff X⁺ = R.
• Armstrong's axioms: Reflexivity, Augmentation, Transitivity (sound + complete).
• Derived: Union, Decomposition, Pseudotransitivity, Composition.
• F⁺ = all FDs implied by F.

Candidate keys

• Attributes never on RHS ⇒ in every key. Attributes only on RHS ⇒ in no key.

Minimal cover (3 steps)

1. Split RHS to single attributes
2. Remove extraneous LHS attributes
3. Remove redundant FDs

Decomposition

• Lossless (mandatory): (R1 ∩ R2) → R1 or (R1 ∩ R2) → R2.
• Dependency preserving (desirable): (F1 ∪ … ∪ Fn)⁺ = F⁺.
• 3NF ⇒ both always possible. BCNF ⇒ lossless always, preservation not always.

Normal forms

NF	One-line rule
1NF	Atomic values, no repeating groups
2NF	1NF + no partial dependencies
3NF	2NF + for every X → A: X super key or A prime
BCNF	Every non-trivial X → Y has X as super key
4NF	BCNF + no non-trivial MVD with non-super-key LHS
5NF	4NF + every JD implied by candidate keys

Other

• MVD X ↠ Y: independent multi-valued facts; JD *(R1,…,Rn): lossless n-way split.
• Denormalization: add redundancy for faster reads; costs: slower writes, risk of inconsistency.

🎤 Commonly asked questions (from these topics)

1. What are update, insertion and deletion anomalies? Give examples.
2. Define a functional dependency. What are trivial and non-trivial FDs?
3. Differentiate full, partial and transitive dependencies.
4. How do you compute the closure of an attribute set? What is it used for?
5. State Armstrong's axioms and prove the union rule.
6. How do you find all candidate keys of a relation?
7. Find the minimal cover of a given FD set.
8. What is lossless-join decomposition? How is it tested?
9. What is dependency preservation? Give an example where it fails.
10. Explain 1NF, 2NF and 3NF with examples.
11. Differentiate 3NF and BCNF. Give a relation that is in 3NF but not BCNF.
12. What is a multivalued dependency? Explain 4NF with an example.
13. What is a join dependency? Explain 5NF.
14. What is denormalization? When is it useful and what are its drawbacks?

---

A friendly, start-to-advanced guide to the core of SQL: creating tables, adding and changing data, reading it back, filtering and sorting, protecting it with constraints, and a few handy tools like CASE. Read top to bottom the first time. Use the Quick Revision section before exams or interviews.

---

📑 Table of Contents

1. Introduction to SQL
2. SQL Standards and Dialects
3. SQL Data Types
4. Creating and Selecting Databases
5. CREATE, ALTER, DROP and TRUNCATE
6. INSERT, UPDATE and DELETE
7. SELECT Statements
8. WHERE, DISTINCT and ORDER BY
9. LIMIT, OFFSET and FETCH
10. Aliases and Column Expressions
11. Comparison and Logical Operators
12. BETWEEN, IN, LIKE and IS NULL
13. AND, OR and NOT
14. SQL Constraints
15. AUTO INCREMENT / Identity Columns
16. INSERT INTO SELECT
17. CASE WHEN Expressions
18. Comments in SQL
19. Quick Revision Cheat Sheet

---

🗂️ Sample data used in these notes

Most examples use these two tables so you can follow along.

Department

dept_id	dept_name
D1	Computer Science
D2	Electronics
D3	Mechanical

Student

student_id	name	age	dept_id	marks	city	email
1	Riya	20	D1	87	Bhopal	riya@x.com
2	Aman	21	D2	74	Indore	aman@x.com
3	Sara	20	D1	91	Bhopal	sara@x.com
4	Neel	22	D3	NULL	Pune	NULL
5	Zoya	19	D2	68	Delhi	zoya@x.com

---

1. Introduction to SQL

1.1 What is SQL?

SQL (Structured Query Language) is the standard language used to talk to relational databases: to create structures, store data, ask questions about it, and control access to it.

• Pronounced "S-Q-L" or "sequel".
• Declarative: you describe what result you want, not how to compute it. The database's optimizer decides the "how".
• Works on tables (relations) made of rows and columns.

Analogy: SQL is like ordering at a restaurant. You say "one margherita pizza" (what you want). You don't tell the chef how to knead the dough.

1.2 What can you do with SQL?

Category	Purpose	Main commands
DDL (Data Definition)	Define/modify structure	CREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation)	Add/change/remove data	INSERT, UPDATE, DELETE
DQL (Data Query)	Read data	SELECT
DCL (Data Control)	Permissions	GRANT, REVOKE
TCL (Transaction Control)	Manage transactions	COMMIT, ROLLBACK, SAVEPOINT

These notes focus on DDL, DML and DQL basics plus constraints and CASE.

1.3 Basic rules of SQL syntax

• A statement usually ends with a semicolon ;.
• Keywords are case-insensitive (SELECT = select); writing keywords in UPPERCASE is a popular style for readability.
• Table and column names may be case-sensitive depending on the DBMS and operating system.
• Whitespace and line breaks don't matter; use them to keep queries readable.
• String values go in single quotes: 'Riya'. Double quotes are normally for identifiers (names), not strings.
• Dates are written as strings in a standard format, e.g., '2026-10-13'.

1.4 Execution flow (big picture)

SQL text → Parser → Optimizer (chooses a plan) → Execution engine → Result

---

2. SQL Standards and Dialects

2.1 SQL standards

SQL is standardised by ANSI (American National Standards Institute) and ISO. The standard has evolved over time:

Version	Notable additions (examples)
SQL-86 / SQL-89	First standards
SQL-92	Major release; joins syntax, CASE, CAST, many data types
SQL:1999	Triggers, recursive queries, user-defined types
SQL:2003	Window functions, identity columns (GENERATED ... AS IDENTITY), XML support
SQL:2008	FETCH FIRST ... ROWS ONLY, TRUNCATE
SQL:2011	Temporal data support
SQL:2016	JSON support
SQL:2023	Latest revision (more JSON and property graph features)

Don't memorise every detail: just remember that a core, common SQL exists and each newer version adds features.

2.2 Dialects

Each database vendor implements the standard plus its own extensions and differences. These variants are called dialects.

DBMS	Dialect / extension	Notes
MySQL / MariaDB	MySQL SQL	LIMIT, AUTO_INCREMENT, backtick-quoted names, # comments
PostgreSQL	PL/pgSQL, PG SQL	Rich types, SERIAL/identity, ILIKE, RETURNING
SQL Server	T-SQL (Transact-SQL)	TOP, IDENTITY, + for string concatenation
Oracle	PL/SQL	FETCH FIRST, DUAL table, VARCHAR2, NUMBER
SQLite	SQLite SQL	Lightweight, flexible typing, file-based

2.3 Where dialects differ (things to watch)

Feature	MySQL	PostgreSQL	SQL Server	Oracle	SQLite
Limit rows	LIMIT	LIMIT / FETCH	TOP / OFFSET FETCH	FETCH FIRST	LIMIT
Auto-number	AUTO_INCREMENT	SERIAL / identity	IDENTITY	identity / sequence	AUTOINCREMENT
String concat	CONCAT()	|| / CONCAT()	+ / CONCAT()	||	||
Switch database	USE db;	\c db (psql)	USE db;	Connect as a schema/user	Open a file
Comment (line)	-- and #	--	--	--	--

✅ Tip: write standard SQL where possible, and check your DBMS documentation for the dialect-specific parts. The examples here follow the common standard and note differences where they matter.

---

3. SQL Data Types

A data type tells the database what kind of value a column can hold, and how much space to use. Names and details vary a bit between systems.

3.1 Numeric types

Type	Description	Example
SMALLINT	Small whole number	1500
INT / INTEGER	Standard whole number (about ±2.1 billion)	20
BIGINT	Very large whole number	9000000000
DECIMAL(p, s) / NUMERIC(p, s)	Exact number with p total digits, s after the decimal point	DECIMAL(8,2) → 123456.78
FLOAT / REAL / DOUBLE	Approximate floating-point numbers	3.14159

⚠️ Use DECIMAL for money (exact). Floating types can introduce tiny rounding errors.

3.2 Character (string) types

Type	Description
CHAR(n)	Fixed-length text; padded with spaces if shorter
VARCHAR(n)	Variable-length text up to n characters
TEXT	Long text (size limits and exact name vary by DBMS)

• Use CHAR for values of a fixed size (e.g., a 2-letter state code), VARCHAR for names, emails, etc.

3.3 Date and time types

Type	Stores	Example
DATE	Year-month-day	'2026-10-13'
TIME	Time of day	'14:30:00'
TIMESTAMP / DATETIME	Date and time	'2026-10-13 14:30:00'
INTERVAL	A span of time (standard SQL, e.g., PostgreSQL, Oracle)	'3 days'

3.4 Other common types

Type	Description
BOOLEAN	TRUE / FALSE (MySQL treats it as a small integer; SQL Server uses BIT)
BLOB / BYTEA / VARBINARY	Binary data like images or files
JSON	JSON documents (supported by most modern systems)
UUID	Universally unique identifier (native in PostgreSQL)
ENUM	A fixed list of allowed text values (MySQL, PostgreSQL)

3.5 Choosing wisely

• Pick the smallest type that safely fits your data.
• Use proper types (store dates as DATE, not text) so sorting, comparing and date functions work correctly.
• Don't store numbers you don't calculate with (like phone numbers or ZIP codes) as numeric types; use VARCHAR.

---

4. Creating and Selecting Databases

A database is a container for tables and other objects.

4.1 Create a database

CREATE DATABASE college;

In MySQL you can avoid an error if it already exists:

CREATE DATABASE IF NOT EXISTS college;

(Support for IF NOT EXISTS on CREATE DATABASE varies by DBMS.)

4.2 List databases

SHOW DATABASES;          -- MySQL
\l                       -- PostgreSQL (psql command)
SELECT name FROM sys.databases;   -- SQL Server

4.3 Select (switch to) a database

USE college;             -- MySQL, SQL Server
\c college               -- PostgreSQL (psql command)

• After this, all table names you write refer to tables inside that database.
• In many systems you can also write the full name: college.Student.

4.4 Drop a database ⚠️

DROP DATABASE college;               -- deletes everything inside it!
DROP DATABASE IF EXISTS college;     -- no error if it doesn't exist

4.5 Notes

• SQLite has no CREATE DATABASE: each database is simply a file you open.
• Oracle works with schemas/users rather than a plain CREATE DATABASE for everyday work.
• Database names should avoid spaces and special characters.

---

5. CREATE, ALTER, DROP and TRUNCATE

These are DDL commands: they change the structure.

5.1 CREATE TABLE

CREATE TABLE Department (
    dept_id    VARCHAR(5)  PRIMARY KEY,
    dept_name  VARCHAR(50) NOT NULL
);

CREATE TABLE Student (
    student_id INT          PRIMARY KEY,
    name       VARCHAR(50)  NOT NULL,
    age        INT,
    dept_id    VARCHAR(5),
    marks      INT,
    city       VARCHAR(30),
    email      VARCHAR(100) UNIQUE,
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

Create a table only if it doesn't already exist:

CREATE TABLE IF NOT EXISTS Course (course_id INT PRIMARY KEY, title VARCHAR(60));

5.2 ALTER TABLE — change an existing table

Task	Common syntax
Add a column	ALTER TABLE Student ADD phone VARCHAR(15);
Drop a column	ALTER TABLE Student DROP COLUMN phone;
Rename a column	ALTER TABLE Student RENAME COLUMN city TO town;
Change a data type	MySQL: ALTER TABLE Student MODIFY age SMALLINT; · PostgreSQL: ALTER TABLE Student ALTER COLUMN age TYPE SMALLINT; · SQL Server: ALTER TABLE Student ALTER COLUMN age SMALLINT;
Rename a table	ALTER TABLE Student RENAME TO Learner; (SQL Server uses sp_rename)
Add a constraint	ALTER TABLE Student ADD CONSTRAINT chk_age CHECK (age >= 15);
Drop a constraint	ALTER TABLE Student DROP CONSTRAINT chk_age; (MySQL uses specific forms like DROP CHECK, DROP FOREIGN KEY)

⚠️ Changing a type or adding a NOT NULL constraint can fail or lose data if existing rows don't fit.

5.3 DROP TABLE — delete the table completely

DROP TABLE Student;                 -- structure AND data gone
DROP TABLE IF EXISTS Student;       -- no error if missing

• You may be blocked if other tables reference it through a foreign key (drop the child tables or constraints first).

5.4 TRUNCATE TABLE — empty the table, keep the structure

TRUNCATE TABLE Student;

• Removes all rows quickly; the table definition stays.
• No WHERE clause allowed.
• Usually resets auto-increment counters (varies by DBMS).
• In most systems it can't be rolled back easily (PostgreSQL and SQL Server allow rollback inside a transaction).

5.5 DELETE vs TRUNCATE vs DROP

	DELETE	TRUNCATE	DROP
Category	DML	DDL	DDL
Removes	Chosen/all rows	All rows	Whole table
WHERE allowed	✅	❌	❌
Table structure kept	✅	✅	❌
Speed on big tables	Slower	Fast	Fast
Rollback	Yes (in a transaction)	Usually no	Usually no

---

6. INSERT, UPDATE and DELETE

These are DML commands: they change the data.

6.1 INSERT — add rows

Specify columns (recommended):

INSERT INTO Student (student_id, name, age, dept_id, marks, city, email)
VALUES (1, 'Riya', 20, 'D1', 87, 'Bhopal', 'riya@x.com');

Insert several rows in one statement:

INSERT INTO Student (student_id, name, age, dept_id, marks, city, email)
VALUES
  (2, 'Aman', 21, 'D2', 74, 'Indore', 'aman@x.com'),
  (3, 'Sara', 20, 'D1', 91, 'Bhopal', 'sara@x.com');

Insert only some columns (others get DEFAULT or NULL):

INSERT INTO Student (student_id, name) VALUES (4, 'Neel');

Without a column list (values must match every column in order; fragile if the table changes):

INSERT INTO Student VALUES (5, 'Zoya', 19, 'D2', 68, 'Delhi', 'zoya@x.com');

✅ Always list the columns explicitly. It's clearer and safer when the table structure changes.

6.2 UPDATE — change existing rows

UPDATE Student
SET marks = 90
WHERE student_id = 1;

Update several columns:

UPDATE Student
SET city = 'Mumbai', age = age + 1
WHERE student_id = 2;

Update using an expression for many rows:

UPDATE Student SET marks = marks + 5 WHERE dept_id = 'D1';

🚨 Danger: UPDATE Student SET marks = 0; with no WHERE updates every row!

6.3 DELETE — remove rows

DELETE FROM Student WHERE student_id = 5;
DELETE FROM Student WHERE marks < 40;

🚨 DELETE FROM Student; with no WHERE deletes all rows (the table remains).

6.4 Safe-practice checklist

1. Test the condition with SELECT first: SELECT * FROM Student WHERE marks < 40;
2. Always include WHERE unless you truly mean "all rows".
3. Run risky changes inside a transaction (BEGIN; ... ROLLBACK; or COMMIT;) so you can undo mistakes.
4. Remember constraints may reject the change (e.g., deleting a department that students still reference).

---

7. SELECT Statements

SELECT reads data. It never changes the table.

7.1 Basic forms

SELECT * FROM Student;                       -- all columns, all rows
SELECT name, marks FROM Student;             -- specific columns
SELECT 'Hello';                              -- no table needed (most DBMSs; Oracle needs FROM DUAL)
SELECT 5 * 4 AS result;                      -- expressions

7.2 SELECT * — handle with care

• Great for quick exploration.
• In real applications, list the columns you need: it is faster, clearer, and won't break when the table gains new columns.

7.3 Clause order (how you write a query)

SELECT   columns
FROM     table
WHERE    row filter
ORDER BY sort order
LIMIT    number of rows;     -- (or FETCH / TOP, dialect-dependent)

7.4 Logical order (how the database thinks about it)

1. FROM       → pick the table
2. WHERE      → filter rows
3. SELECT     → choose/compute columns (aliases are created here)
4. DISTINCT   → remove duplicates
5. ORDER BY   → sort
6. LIMIT / OFFSET / FETCH → trim the result

💡 This explains why a column alias from SELECT can't be used in WHERE (WHERE runs first) but can be used in ORDER BY (it runs later).

7.5 Example

SELECT name, marks
FROM Student
WHERE dept_id = 'D1'
ORDER BY marks DESC;

Result:

name	marks
Sara	91
Riya	87

---

8. WHERE, DISTINCT and ORDER BY

8.1 WHERE — filter rows

Keeps only rows for which the condition is TRUE.

SELECT * FROM Student WHERE city = 'Bhopal';
SELECT name FROM Student WHERE marks > 80 AND age < 21;

• Rows where the condition is FALSE or UNKNOWN (NULL) are dropped.
• Text comparison is case-sensitive or not depending on the DBMS and collation.

8.2 DISTINCT — remove duplicate rows from the result

SELECT DISTINCT city FROM Student;

city
Bhopal
Indore
Pune
Delhi

With multiple columns, the combination must be unique:

SELECT DISTINCT city, dept_id FROM Student;

• DISTINCT applies to the whole row of selected columns, not just the first column.
• NULLs are treated as equal to each other for DISTINCT (one NULL row remains).
• DISTINCT can slow queries on big data because the database must compare/sort rows.

8.3 ORDER BY — sort the result

SELECT name, marks FROM Student ORDER BY marks;         -- ascending (default)
SELECT name, marks FROM Student ORDER BY marks DESC;    -- descending

Sort by multiple columns (second column breaks ties):

SELECT name, city, marks FROM Student
ORDER BY city ASC, marks DESC;

Other handy forms

ORDER BY 2 DESC;              -- by the 2nd selected column (position; avoid in production code)
ORDER BY total_marks DESC;    -- by an alias (allowed in ORDER BY)
ORDER BY LENGTH(name);        -- by an expression

Where do NULLs go?

• Behaviour differs: some systems put NULLs first in ascending order (MySQL, SQL Server), others last (PostgreSQL, Oracle).
• PostgreSQL and Oracle let you control it: ORDER BY marks DESC NULLS LAST;

⚠️ Without ORDER BY, the order of rows is not guaranteed. Never rely on "natural" order.

---

9. LIMIT, OFFSET and FETCH

These restrict how many rows come back, which is useful for "top N" and pagination.

9.1 LIMIT and OFFSET (MySQL, PostgreSQL, SQLite)

SELECT name, marks FROM Student
ORDER BY marks DESC
LIMIT 3;                    -- top 3 rows

SELECT name, marks FROM Student
ORDER BY marks DESC
LIMIT 3 OFFSET 3;           -- skip 3 rows, then take 3

(MySQL also accepts the shorthand LIMIT 3, 3 → LIMIT offset, count.)

9.2 TOP (SQL Server)

SELECT TOP 3 name, marks FROM Student ORDER BY marks DESC;

9.3 OFFSET ... FETCH (SQL standard; Oracle 12c+, PostgreSQL, SQL Server)

SELECT name, marks FROM Student
ORDER BY marks DESC
OFFSET 3 ROWS
FETCH NEXT 3 ROWS ONLY;

SELECT name, marks FROM Student
ORDER BY marks DESC
FETCH FIRST 3 ROWS ONLY;                 -- top 3

• In SQL Server, OFFSET ... FETCH requires an ORDER BY.
• FETCH FIRST n ROWS WITH TIES (where supported) also returns extra rows that tie with the last one.

9.4 Comparison

Goal	MySQL / PostgreSQL / SQLite	SQL Server	Standard / Oracle
First N rows	LIMIT N	TOP N	FETCH FIRST N ROWS ONLY
Skip M, take N	LIMIT N OFFSET M	OFFSET M ROWS FETCH NEXT N ROWS ONLY	OFFSET M ROWS FETCH NEXT N ROWS ONLY

9.5 Pagination formula

For page number p (starting at 1) with page size s:

OFFSET = (p − 1) × s, LIMIT = s

Example: page 3, 10 rows per page → LIMIT 10 OFFSET 20.

9.6 Important cautions

• Always combine with ORDER BY; otherwise the "first N rows" are arbitrary.
• Very large offsets (like OFFSET 1000000) can be slow, because the database still has to walk past all skipped rows.

---

10. Aliases and Column Expressions

10.1 Column aliases (AS)

An alias gives a column or expression a temporary, friendlier name in the result.

SELECT name AS student_name, marks AS score FROM Student;

• The word AS is optional in most systems (marks score), but writing it is clearer.
• Use quotes if the alias has spaces: AS "Student Name" (standard) or backticks in MySQL / brackets in SQL Server.
• The alias exists only for that query's output; it doesn't rename anything in the table.
• Aliases can be used in ORDER BY, but not in WHERE (see Section 7.4).

10.2 Table aliases

A short nickname for a table, especially useful when a query uses several tables or long names.

SELECT s.name, s.marks
FROM Student AS s
WHERE s.marks > 80;

(In Oracle, write the table alias without AS: FROM Student s.)

10.3 Column expressions

You can compute values in the SELECT list using operators and functions.

Arithmetic

SELECT name, marks, marks + 5 AS grace_marks,
       marks / 100.0 * 100 AS percentage
FROM Student;

Operators: + - * / % (modulo; MOD() in some systems).

String concatenation (dialect differences!)

SELECT name || ' from ' || city FROM Student;         -- standard, PostgreSQL, Oracle, SQLite
SELECT CONCAT(name, ' from ', city) FROM Student;     -- MySQL, PostgreSQL, SQL Server
SELECT name + ' from ' + city FROM Student;           -- SQL Server

Common built-in functions (names vary slightly by DBMS)

Purpose	Examples
Text	UPPER(name), LOWER(name), LENGTH(name), TRIM(name), SUBSTRING(name, 1, 3)
Numeric	ROUND(x, 2), ABS(x), CEIL(x), FLOOR(x)
Null handling	COALESCE(marks, 0) (first non-NULL value)
Type conversion	CAST(marks AS DECIMAL(5,2))
Date	CURRENT_DATE, CURRENT_TIMESTAMP

Handling NULL in expressions

SELECT name, marks + 5 AS bumped FROM Student;              -- NULL + 5 = NULL
SELECT name, COALESCE(marks, 0) + 5 AS bumped FROM Student; -- NULL treated as 0

Any arithmetic with NULL gives NULL.

---

11. Comparison and Logical Operators

11.1 Comparison operators

Operator	Meaning	Example
=	Equal to	marks = 90
<> or !=	Not equal to (<> is the standard)	city <> 'Pune'
<	Less than	age < 21
>	Greater than	marks > 80
<=	Less than or equal to	age <= 20
>=	Greater than or equal to	marks >= 75

• SQL uses a single = for comparison (not ==).
• Comparing with NULL using these operators gives UNKNOWN. Use IS NULL (Section 12).
• Comparisons also work on text (alphabetical order) and dates (chronological order).

11.2 Logical operators (overview)

Operator	Meaning
AND	All conditions must be true
OR	At least one condition must be true
NOT	Reverses a condition
BETWEEN, IN, LIKE, IS NULL, EXISTS, ANY/ALL	Special condition operators (the first four are explained in Sections 12–13)

11.3 Operator groups at a glance

Group	Operators
Arithmetic	+ - * / %
Comparison	= <> != < > <= >=
Logical	AND OR NOT
Special	BETWEEN IN LIKE IS NULL
String	|| (or CONCAT)

---

12. BETWEEN, IN, LIKE and IS NULL

12.1 BETWEEN — within a range (inclusive of both ends)

SELECT name, marks FROM Student WHERE marks BETWEEN 70 AND 90;
-- same as: marks >= 70 AND marks <= 90

• Order matters: BETWEEN low AND high (a reversed range returns nothing).
• Works with numbers, text and dates: WHERE join_date BETWEEN '2026-01-01' AND '2026-03-31'.
• Negate with NOT BETWEEN.

12.2 IN — matches any value in a list

SELECT name, city FROM Student WHERE city IN ('Bhopal', 'Pune');
-- same as: city = 'Bhopal' OR city = 'Pune'

• Negate with NOT IN.
• The list can also come from a subquery:

  SELECT name FROM Student
  WHERE dept_id IN (SELECT dept_id FROM Department WHERE dept_name LIKE 'C%');
  

⚠️ NOT IN trap: if the list (or subquery result) contains a NULL, NOT IN returns no rows, because comparisons with NULL are UNKNOWN. Make sure the list has no NULLs, or filter them out.

12.3 LIKE — pattern matching on text

Wildcard	Meaning
%	Any number of characters (including none)
_	Exactly one character

SELECT name FROM Student WHERE name LIKE 'S%';       -- starts with S
SELECT name FROM Student WHERE name LIKE '%a';       -- ends with a
SELECT name FROM Student WHERE name LIKE '%ia%';     -- contains "ia"
SELECT name FROM Student WHERE name LIKE '_a%';      -- 2nd letter is 'a'
SELECT name FROM Student WHERE name LIKE '____';     -- exactly 4 characters
SELECT name FROM Student WHERE name NOT LIKE 'A%';

Searching for a literal % or _ → use ESCAPE:

SELECT * FROM Offers WHERE title LIKE '50\%%' ESCAPE '\';   -- titles starting with "50%"

Case sensitivity:

• MySQL, SQL Server: usually case-insensitive (depends on collation)
• PostgreSQL, Oracle: LIKE is case-sensitive; PostgreSQL offers ILIKE for case-insensitive matching.

💡 Patterns starting with % (like '%ia') can't use a normal index well, so they may be slow on big tables.

12.4 IS NULL / IS NOT NULL — test for missing values

SELECT name FROM Student WHERE marks IS NULL;
SELECT name FROM Student WHERE email IS NOT NULL;

• ❌ WHERE marks = NULL never works (the result is UNKNOWN, never TRUE).
• NULL ≠ 0 and NULL ≠ ''.
• Replace NULLs in output with COALESCE(marks, 0).

12.5 Quick comparison

Operator	Tests for	Example
BETWEEN a AND b	Inside a range (inclusive)	age BETWEEN 19 AND 21
IN (…)	Matches one of several values	city IN ('Pune','Delhi')
LIKE	Text pattern	name LIKE 'R%'
IS NULL	Missing value	marks IS NULL

---

13. AND, OR and NOT

13.1 What they do

SELECT name FROM Student WHERE city = 'Bhopal' AND marks > 85;   -- both true
SELECT name FROM Student WHERE city = 'Pune'   OR  city = 'Delhi'; -- either true
SELECT name FROM Student WHERE NOT city = 'Bhopal';              -- reverses

13.2 Truth tables (with NULL → UNKNOWN)

AND

A \ B	TRUE	FALSE	UNKNOWN
TRUE	TRUE	FALSE	UNKNOWN
FALSE	FALSE	FALSE	FALSE
UNKNOWN	UNKNOWN	FALSE	UNKNOWN

OR

A \ B	TRUE	FALSE	UNKNOWN
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	UNKNOWN

NOT

A	NOT A
TRUE	FALSE
FALSE	TRUE
UNKNOWN	UNKNOWN

WHERE keeps only rows whose final result is TRUE.

13.3 Precedence (who goes first?)

1. NOT (highest)
2. AND
3. OR (lowest)

13.4 The classic mistake

-- Intended: students from Bhopal or Pune who scored above 85
SELECT name FROM Student
WHERE city = 'Bhopal' OR city = 'Pune' AND marks > 85;

Because AND binds first, this actually means: city = 'Bhopal' OR (city = 'Pune' AND marks > 85) ❌

Fix with parentheses:

SELECT name FROM Student
WHERE (city = 'Bhopal' OR city = 'Pune') AND marks > 85;   -- ✅

✅ Rule of thumb: whenever you mix AND and OR, use parentheses, even if you're sure about the precedence.

13.5 Combining with other operators

SELECT name FROM Student
WHERE age BETWEEN 19 AND 21
  AND city IN ('Bhopal', 'Delhi')
  AND NOT (marks IS NULL);

You can negate special operators directly: NOT BETWEEN, NOT IN, NOT LIKE, IS NOT NULL.

---

14. SQL Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT

14.1 What are constraints?

Constraints are rules enforced by the database on the data in a table. They keep data valid and consistent; any INSERT, UPDATE or DELETE that would break a rule is rejected.

14.2 The six constraints at a glance

Constraint	Rule	Allows NULL?	Per table
PRIMARY KEY	Uniquely identifies each row	❌ No	One (can be multi-column)
FOREIGN KEY	Value must exist in another table's key (or be NULL)	✅ Yes	Many
UNIQUE	No duplicate values	✅ Yes (rules for multiple NULLs vary by DBMS)	Many
NOT NULL	A value is required	❌ No	Per column
CHECK	Value must satisfy a condition	✅ (NULL passes unless combined with NOT NULL)	Many
DEFAULT	Value used when none is supplied	—	Per column

14.3 PRIMARY KEY

CREATE TABLE Student (
    student_id INT PRIMARY KEY,         -- column level
    name       VARCHAR(50)
);

Composite primary key (table level):

CREATE TABLE Enrollment (
    student_id INT,
    course_id  INT,
    grade      CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

• Automatically unique and NOT NULL.
• The database normally creates an index on it for fast lookup.

14.4 FOREIGN KEY

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    dept_id    VARCHAR(5),
    CONSTRAINT fk_student_dept
        FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

• The referenced column must be a primary key or unique column.
• Referential actions: RESTRICT / NO ACTION (reject; usual default), CASCADE, SET NULL, SET DEFAULT.
• Prevents orphan rows (e.g., a student pointing to a department that doesn't exist).
• Create the parent table first, drop it last.

14.5 UNIQUE

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    email      VARCHAR(100) UNIQUE,
    phone      VARCHAR(15),
    UNIQUE (phone)                     -- table-level form
);

• A table can have many UNIQUE constraints, but only one primary key.
• Can span several columns: UNIQUE (first_name, last_name) → the pair must be unique.

14.6 NOT NULL

name VARCHAR(50) NOT NULL

• A value must be provided on insert/update.
• Column-level only (it isn't written as a table-level constraint in most systems).

14.7 CHECK

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    age        INT CHECK (age BETWEEN 15 AND 60),
    marks      INT,
    gender     CHAR(1),
    CONSTRAINT chk_marks  CHECK (marks BETWEEN 0 AND 100),
    CONSTRAINT chk_gender CHECK (gender IN ('M', 'F', 'O'))
);

• Rejects a row only when the condition is FALSE; if the result is UNKNOWN (because of NULL), the row is accepted. Add NOT NULL if NULL shouldn't be allowed.
• Can compare columns of the same row: CHECK (end_date >= start_date).
• MySQL enforces CHECK from version 8.0.16; older versions parsed but ignored it.

14.8 DEFAULT

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    city       VARCHAR(30) DEFAULT 'Unknown',
    status     VARCHAR(10) DEFAULT 'active',
    joined_on  DATE DEFAULT CURRENT_DATE
);

INSERT INTO Student (student_id) VALUES (10);   -- city = 'Unknown', status = 'active'

• Used when a column is omitted in INSERT (or when you write the keyword DEFAULT).
• An explicit NULL insert stores NULL; it does not trigger the default.

14.9 Column-level vs table-level; naming constraints

CREATE TABLE Employee (
    emp_id    INT          NOT NULL,                 -- column-level
    email     VARCHAR(100) NOT NULL,
    salary    INT          DEFAULT 0 CHECK (salary >= 0),
    dept_id   VARCHAR(5),

    CONSTRAINT pk_employee PRIMARY KEY (emp_id),     -- table-level, named
    CONSTRAINT uq_emp_email UNIQUE (email),
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

• Naming constraints (CONSTRAINT name ...) makes error messages clearer and lets you drop or change them later.
• Multi-column constraints must be written at the table level.

14.10 Adding and removing constraints later

ALTER TABLE Student ADD CONSTRAINT uq_email UNIQUE (email);
ALTER TABLE Student ADD CONSTRAINT chk_age CHECK (age >= 15);
ALTER TABLE Student ALTER COLUMN name SET NOT NULL;        -- PostgreSQL / Oracle style differs slightly
ALTER TABLE Student DROP CONSTRAINT chk_age;

(Exact syntax for NOT NULL, DEFAULT and dropping constraints differs between MySQL, PostgreSQL, SQL Server and Oracle.)

---

15. AUTO INCREMENT / Identity Columns

15.1 What and why?

An auto-increment (identity) column automatically generates the next number for each new row, so you don't have to supply the ID yourself. It's most often used for primary keys (these are called surrogate keys).

15.2 Syntax in different systems

DBMS	Syntax
MySQL / MariaDB	id INT AUTO_INCREMENT PRIMARY KEY
PostgreSQL	id SERIAL PRIMARY KEY (older style) or id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
SQL Server	id INT IDENTITY(1,1) PRIMARY KEY → (start, step)
Oracle (12c+)	id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY (older versions used sequences + triggers)
SQLite	id INTEGER PRIMARY KEY (auto-assigns) or id INTEGER PRIMARY KEY AUTOINCREMENT
SQL standard	`GENERATED { ALWAYS

15.3 Example (MySQL)

CREATE TABLE Course (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    title     VARCHAR(60) NOT NULL
);

INSERT INTO Course (title) VALUES ('Databases');   -- course_id = 1
INSERT INTO Course (title) VALUES ('Networks');    -- course_id = 2

15.4 ALWAYS vs BY DEFAULT (standard identity)

Option	Behaviour
GENERATED ALWAYS AS IDENTITY	The system always generates the value; manual values are rejected (unless overridden explicitly)
GENERATED BY DEFAULT AS IDENTITY	The system generates a value unless you supply one

15.5 Customising the sequence

-- SQL Server: start at 1000, step 5
id INT IDENTITY(1000, 5) PRIMARY KEY

-- MySQL: change the next value
ALTER TABLE Course AUTO_INCREMENT = 500;

-- PostgreSQL / standard
id INT GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 10)

15.6 Getting the ID of the row you just inserted

DBMS	How
MySQL	SELECT LAST_INSERT_ID();
PostgreSQL	INSERT ... RETURNING course_id;
SQL Server	SELECT SCOPE_IDENTITY();
SQLite	SELECT last_insert_rowid();

15.7 Things to know

• Gaps are normal. Rolled-back inserts, failed inserts and deleted rows leave holes in the sequence; the counter doesn't go back.
• Deleting rows doesn't reset the counter. TRUNCATE often does (depends on the DBMS).
• Only one identity column per table in most systems.
• Don't assume IDs are consecutive or carry meaning; treat them as simple unique labels.

---

16. INSERT INTO SELECT

16.1 What is it?

It copies rows from one query's result into an existing table in a single statement. Instead of VALUES, you supply a SELECT.

16.2 Syntax

INSERT INTO target_table (col1, col2, ...)
SELECT colA, colB, ...
FROM source_table
WHERE condition;

16.3 Examples

Archive old/low-score students:

CREATE TABLE StudentArchive (
    student_id INT PRIMARY KEY,
    name       VARCHAR(50),
    marks      INT
);

INSERT INTO StudentArchive (student_id, name, marks)
SELECT student_id, name, marks
FROM Student
WHERE marks < 70;

Copy everything (same column structure):

INSERT INTO StudentBackup
SELECT * FROM Student;

Insert with computed values:

INSERT INTO StudentArchive (student_id, name, marks)
SELECT student_id, UPPER(name), COALESCE(marks, 0)
FROM Student
WHERE city = 'Bhopal';

16.4 Rules

1. The number of columns in the SELECT must match the target column list.
2. The data types must be compatible, matched by position, not by name.
3. Columns not listed get their DEFAULT or NULL.
4. Constraints on the target table still apply: duplicate keys, NOT NULL, foreign keys, and so on can make the whole statement fail.
5. The SELECT can use WHERE, ORDER BY, expressions and more.

16.5 Similar tools (for comparison)

Command	Target table	Notes
INSERT INTO ... SELECT	Must already exist	Supported almost everywhere
CREATE TABLE new AS SELECT ...	Created on the fly	MySQL, PostgreSQL, Oracle, SQLite
SELECT ... INTO new FROM ...	Created on the fly	SQL Server (and PostgreSQL's different meaning in procedural code)

CREATE TABLE TopStudents AS
SELECT * FROM Student WHERE marks >= 85;

(Such copies usually don't carry over constraints, indexes or defaults.)

---

17. CASE WHEN Expressions

17.1 What is it?

CASE is SQL's if-then-else. It returns a value based on conditions. It's an expression (it produces a value), so it can be used almost anywhere a value is allowed.

17.2 Searched CASE (most flexible)

SELECT name, marks,
  CASE
    WHEN marks >= 90 THEN 'A'
    WHEN marks >= 75 THEN 'B'
    WHEN marks >= 60 THEN 'C'
    ELSE 'D'
  END AS grade
FROM Student;

name	marks	grade
Riya	87	B
Aman	74	C
Sara	91	A
Neel	NULL	D
Zoya	68	C

Notice Neel (marks is NULL): every WHEN marks >= ... is UNKNOWN, so it falls to ELSE. Handle NULL explicitly when it matters: WHEN marks IS NULL THEN 'Absent'.

17.3 Simple CASE (compare one expression to values)

SELECT name,
  CASE dept_id
    WHEN 'D1' THEN 'Computer Science'
    WHEN 'D2' THEN 'Electronics'
    ELSE 'Other'
  END AS department
FROM Student;

• Simple CASE only tests equality. For ranges or complex conditions, use the searched form.

17.4 How it works

• Conditions are checked top to bottom; the first true WHEN wins and the rest are skipped.
• If nothing matches and there is no ELSE, the result is NULL.
• All THEN/ELSE results should be of compatible types (don't mix numbers and text).
• Every CASE must end with END.

17.5 Where you can use CASE

Clause	Example
SELECT	Create labels/categories (above)
ORDER BY	Custom sort order
UPDATE ... SET	Conditional updates
WHERE	Conditional filters
Inside functions/aggregates	Conditional counting/summing

Custom sort order

SELECT name, city FROM Student
ORDER BY CASE city
           WHEN 'Bhopal' THEN 1
           WHEN 'Indore' THEN 2
           ELSE 3
         END;

Conditional update (different raise per range)

UPDATE Student
SET marks = CASE
              WHEN marks < 40 THEN marks + 10
              WHEN marks < 60 THEN marks + 5
              ELSE marks
            END
WHERE marks IS NOT NULL;

Conditional counting

SELECT
  COUNT(CASE WHEN marks >= 75 THEN 1 END) AS high_scorers,
  COUNT(CASE WHEN marks <  75 THEN 1 END) AS others
FROM Student;

(COUNT ignores NULLs, so rows that don't match return NULL and aren't counted.)

17.6 Related shortcuts

Function	Meaning
COALESCE(a, b, c)	First non-NULL value (like a CASE on NULL)
NULLIF(a, b)	Returns NULL if a = b, else a (handy to avoid divide-by-zero)

---

18. Comments in SQL

18.1 Why comment?

Comments are notes ignored by the database. They explain why a query or table exists, document assumptions, or temporarily disable code.

18.2 Single-line comments

-- This selects only active students
SELECT * FROM Student WHERE marks > 80;  -- inline comment at the end of a line

• Starts with two hyphens --; runs to the end of the line.
• MySQL requires a space after -- (-- comment) and also supports #:

  # MySQL-only style comment
  

18.3 Multi-line (block) comments

/*
   Report: top students per city
   Author: ...
   Purpose: monthly review
*/
SELECT name, marks
FROM Student /* temporarily ignore filter */;

• Starts with /*, ends with */.
• Can sit in the middle of a statement.
• Support for nested block comments varies (PostgreSQL supports it; MySQL doesn't).

18.4 Commenting out code while debugging

SELECT name, marks
FROM Student
-- WHERE city = 'Pune'
ORDER BY marks DESC;

18.5 Comment support by DBMS

Style	MySQL	PostgreSQL	SQL Server	Oracle	SQLite
-- text	✅ (needs space after)	✅	✅	✅	✅
/* text */	✅	✅	✅	✅	✅
# text	✅	❌	❌	❌	❌

18.6 Good commenting habits

• Explain the "why", not the obvious "what".
• Keep comments up to date (a wrong comment is worse than none).
• Be careful not to leave a stray comment marker that accidentally comments out part of your query.
• Some systems use special comment forms for optimizer hints or version-specific code, so avoid odd symbols right after /*.

---

⚡ Quick Revision Cheat Sheet

Basics

• SQL is declarative; standardised by ANSI/ISO; every DBMS has its own dialect (T-SQL, PL/SQL, MySQL, PostgreSQL…).
• Categories: DDL (CREATE, ALTER, DROP, TRUNCATE) · DML (INSERT, UPDATE, DELETE) · DQL (SELECT).
• Strings use single quotes; statements end with ;.

Data types

• Numbers: INT, BIGINT, DECIMAL(p,s) (exact), FLOAT (approximate)
• Text: CHAR (fixed), VARCHAR (variable), TEXT
• Date/time: DATE, TIME, TIMESTAMP/DATETIME
• Others: BOOLEAN, JSON, BLOB, UUID, ENUM

Structure commands

• CREATE DATABASE, USE db, CREATE TABLE, ALTER TABLE (ADD / DROP / MODIFY), DROP TABLE, TRUNCATE TABLE
• DELETE = some/all rows (rollback-able) · TRUNCATE = all rows, keep table · DROP = table gone.

Data commands

• INSERT INTO t (cols) VALUES (...) · UPDATE t SET c = v WHERE ... · DELETE FROM t WHERE ...
• 🚨 No WHERE = all rows affected.

Querying

• Order written: SELECT → FROM → WHERE → ORDER BY → LIMIT
• Order executed: FROM → WHERE → SELECT → DISTINCT → ORDER BY → LIMIT
• DISTINCT removes duplicate rows · ORDER BY col [ASC|DESC] (no ORDER BY = no guaranteed order)
• Row limiting: LIMIT n OFFSET m · TOP n (SQL Server) · OFFSET m ROWS FETCH NEXT n ROWS ONLY (standard)
• Pagination: OFFSET = (page − 1) × size

Operators

• Comparison: = <> != < > <= >=
• BETWEEN a AND b (inclusive) · IN (...) · LIKE (% many, _ one) · IS [NOT] NULL
• Precedence: NOT > AND > OR; use parentheses.
• = NULL ❌ → use IS NULL ✅. NOT IN + NULL in list = no rows.

Aliases & expressions

• col AS alias, table AS t; alias usable in ORDER BY, not in WHERE.
• Concatenation: || / CONCAT() / + (SQL Server). COALESCE(x, default) handles NULL.

Constraints

• PRIMARY KEY (unique + not null, one per table) · FOREIGN KEY (references parent key) · UNIQUE · NOT NULL · CHECK (condition) · DEFAULT value.

Auto numbering

• AUTO_INCREMENT (MySQL) · SERIAL / IDENTITY (PostgreSQL) · IDENTITY(1,1) (SQL Server) · GENERATED ... AS IDENTITY (standard/Oracle) · gaps are normal.

Other

• INSERT INTO t (cols) SELECT ... copies rows into an existing table; columns match by position.
• CASE WHEN cond THEN x ... ELSE y END · first true branch wins · no ELSE → NULL.
• Comments: -- line, /* block */, # line (MySQL only).

🎤 Commonly asked questions (from these topics)

1. What is SQL? What are DDL, DML and DQL?
2. What are SQL standards and dialects? Give examples of dialect differences.
3. What is the difference between CHAR and VARCHAR? Why use DECIMAL for money?
4. Differentiate DELETE, TRUNCATE and DROP.
5. What happens if you run UPDATE or DELETE without WHERE?
6. What is the difference between WHERE and ORDER BY? What does DISTINCT do?
7. How do you get the top 5 rows, or implement pagination, in different databases?
8. Why can't a column alias be used in WHERE?
9. Explain BETWEEN, IN, LIKE and IS NULL. Why does = NULL fail?
10. What is the precedence of AND, OR and NOT? Why use parentheses?
11. Explain the six constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.
12. What is the difference between PRIMARY KEY and UNIQUE?
13. How do identity/auto-increment columns work in MySQL, PostgreSQL and SQL Server?
14. What does INSERT INTO ... SELECT do, and what rules apply?
15. Explain CASE WHEN with an example. What happens with no ELSE?
16. How do you write comments in SQL?

---