Unit 1: Introduction to Database Systems

A Database Management System (DBMS) is software that stores, retrieves, and manages data in a structured, efficient, and secure way. It was invented to solve the 7 deadly problems of file-based data storage.

Imagine a hospital where patient records are kept in paper files in individual doctor's cabins (file system). Each doctor has their own copies. Patient X visits 3 doctors — now there are 3 copies of their allergy information. One doctor updates it, the others don't know. A DBMS is like a centralized digital system (AIIMS HIS) — one source of truth, accessible by all authorized doctors, always consistent.

IRCTC handled 25 crore+ bookings last year. Imagine managing this with flat files — every concurrent booking, every waitlist update, every cancellation. File-based systems would collapse in minutes. IRCTC uses Oracle RDBMS to handle 25 million+ daily queries with ACID guarantee: your ticket is confirmed OR your money is refunded — never lost in between.

A three-tier architecture separates an application into three independent layers, each responsible for a specific function. This separation allows teams to work independently and technology to be swapped without rewriting everything.

Three-Tier Architecture
┌─────────────────────────────────────────────────────────────┐
│ TIER 1 — PRESENTATION LAYER (What you see)                   │
│ Swiggy app (React Native / Flutter)                          │
│ You tap "Order Butter Chicken" → sends HTTP request          │
├─────────────────────────────────────────────────────────────┤
│ TIER 2 — APPLICATION/BUSINESS LOGIC LAYER (Brain)            │
│ Swiggy backend (Node.js / Java Spring Boot)                  │
│ Validates: Is restaurant open? Is item available?            │
│ Calculates: Price + GST + delivery charge = ₹389            │
│ Sends SQL query to database                                  │
├─────────────────────────────────────────────────────────────┤
│ TIER 3 — DATABASE LAYER (Memory)                             │
│ PostgreSQL / MySQL cluster                                   │
│ INSERT INTO orders (customer_id, restaurant_id, total, ...)  │
│ UPDATE restaurant SET current_orders = current_orders + 1    │
│ Returns confirmation → Tier 2 → Tier 1 → "Order Placed! 🎉" │
└─────────────────────────────────────────────────────────────┘

Why separate? Flipkart can redesign their mobile app (Tier 1) without touching the database. SBI can switch from Oracle to PostgreSQL (Tier 3) without rewriting their customer-facing website. TCS can assign frontend, backend, and DBA teams independently. This is how all modern Indian IT projects are structured.

The ANSI/SPARC three-schema architecture defines three levels of data abstraction, ensuring that changes at one level don't affect the others.

Three-Schema Architecture
┌───────────────────────────────────────────────────────────┐
│  EXTERNAL SCHEMA (View Level) — What each USER sees        │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐                │
│  │ View 1:  │  │ View 2:  │  │ View 3:  │                │
│  │ Student  │  │ Accounts │  │ HR       │                │
│  │ Portal   │  │ Dept     │  │ Manager  │                │
│  │ sees:    │  │ sees:    │  │ sees:    │                │
│  │ name,    │  │ fees,    │  │ name,    │                │
│  │ grades,  │  │ payments,│  │ salary,  │                │
│  │ attendance│ │ dues     │  │ dept     │                │
│  └──────────┘  └──────────┘  └──────────┘                │
│          ↕ Logical Data Independence                       │
├───────────────────────────────────────────────────────────┤
│  CONCEPTUAL SCHEMA (Logical Level) — Full logical design   │
│  Tables: students, courses, enrollments, fees,             │
│  departments, employees, salaries, attendance              │
│  Relationships, constraints, data types — no storage info  │
│          ↕ Physical Data Independence                      │
├───────────────────────────────────────────────────────────┤
│  INTERNAL SCHEMA (Physical Level) — How data is stored     │
│  Index structures (B-tree, hash), storage allocation,      │
│  data compression, file organization, buffer management    │
│  Specific to disk — hidden from users and programmers      │
└───────────────────────────────────────────────────────────┘

"Three-tier architecture and three-schema architecture are the same." No. Three-tier is about APPLICATION architecture (presentation/logic/data layers). Three-schema is about DATA ABSTRACTION within the DBMS itself (external/conceptual/internal views of data). They work together but solve different problems.

Schema = the structure/design of the database (table definitions, columns, types, constraints). Rarely changes. Instance = the actual data in the database at a particular moment. Changes constantly.

Schema is like a class definition in Java: class Student { String name; int rollNo; }. Instance is like an object: new Student("Rahul", 101). The class defines structure; the object holds current data. Schema is designed once; instances change every second (new students, updated grades).

SQL
-- SCHEMA (Blueprint — defined once)
CREATE TABLE students (
    student_id   NUMBER(10)   PRIMARY KEY,
    student_name VARCHAR2(100) NOT NULL,
    dept_code    CHAR(3),
    cgpa         NUMBER(3,2)  CHECK (cgpa BETWEEN 0 AND 10)
);

-- INSTANCE (Data — changes constantly)
-- At 10:00 AM:
-- | 101 | Rahul Sharma | CSE | 8.50 |
-- | 102 | Priya Patel  | ECE | 9.10 |
-- At 10:05 AM (after new admission):
-- | 101 | Rahul Sharma | CSE | 8.50 |
-- | 102 | Priya Patel  | ECE | 9.10 |
-- | 103 | Amit Kumar   | CSE | 7.80 |  ← new row

The ER model is a high-level conceptual data model used to design the database structure BEFORE writing any SQL. It uses entities (things), attributes (properties), and relationships (connections) to visually represent data requirements. Think of it as the architect's floor plan before construction begins.

Before constructing a house, you create a blueprint (ER diagram). Before coding a database, you design the ER diagram. Skipping this step is like building a house without a plan — walls end up in wrong places, rooms don't connect, and plumbing is a nightmare.

A weak entity cannot be uniquely identified by its own attributes alone — it depends on a "strong" (owner) entity. It has a partial key (discriminator) that, combined with the owner's primary key, creates a unique identifier.

Dependent is a weak entity of Employee. "Priya" (dependent name) isn't unique globally — many employees might have a dependent named "Priya." But (employee_id=101, dependent_name="Priya") IS unique. The dependent can't exist without the employee.

Weak entity: double rectangle. Identifying relationship: double diamond. Partial key: dashed underline.

Proposed by E.F. Codd (IBM) in 1970, the relational model represents data as relations (tables). Each relation has tuples (rows) and attributes (columns). All modern SQL databases implement this model.

1. Each cell contains exactly one value (atomic — 1NF)
2. Each column has a distinct name
3. Values in a column are from the same domain
4. Order of rows does not matter
5. Order of columns does not matter
6. No two rows are identical (unique tuples)