INT306: Database Management Systems

Unit 1: Introduction to Database Systems, Architecture, and Data Modeling

1. Fundamental Concepts: File Processing Systems vs. Database Management Systems

A Database is an organized, logically coherent collection of interrelated data modeling a real-world enterprise (the mini-world or Universe of Discourse). A Database Management System (DBMS) is a comprehensive software suite designed to facilitate the definition, construction, manipulation, sharing, protection, and maintenance of databases across diverse user communities.

CODE
                           DATA MANAGEMENT EVOLUTION
                                       │
         ┌─────────────────────────────┴─────────────────────────────┐
    Traditional File Processing System                         Modern Database Management System
  (Application-Coupled Flat Files)                            (Centralized Abstract Data Model)
         │                                                                   │
  • Fragmented data definitions                                       • Unified system catalog (Metadata)
  • Tightly bound to operating system files                           • Program-data independence
  • Redundant storage across departments                              • Minimal controlled redundancy
  • Hard-coded consistency checks                                     • Declarative integrity constraints

A. Inherent Drawbacks of Traditional File-Processing Systems

Prior to database systems, business applications stored permanent records across flat OS files (e.g., indexed sequential access methods, CSV, COBOL record files). Each computer application maintained private files and specialized processing routines:

CODE
┌──────────────────────────────────────┬──────────────────────────────────────────────────────────────┐
│ File System Limitation               │ Detailed Architectural Failure & Enterprise Impact           │
├──────────────────────────────────────┼──────────────────────────────────────────────────────────────┤
│ **Data Redundancy and Inconsistency**│ Multiple copies of identical real-world facts are duplicated │
│                                      │ across disparate departments (e.g., student address stored   │
│                                      │ separately in Academic, Hostel, and Finance files). Updating │
│                                      │ one file while missing another causes data inconsistency.    │
├──────────────────────────────────────┼──────────────────────────────────────────────────────────────┤
│ **Difficulty in Accessing Data**     │ Conventional file environments lack generic ad-hoc query     │
│                                      │ facilities. Answering a novel managerial request requires    │
│                                      │ writing a brand-new custom C, COBOL, or Pascal program.      │
├──────────────────────────────────────┼──────────────────────────────────────────────────────────────┤
│ **Data Isolation**                   │ Data is scattered across multiple operating system files in  │
│                                      │ heterogeneous formats (binary records, delimited text, fixed │
│                                      │ width fields), making cross-file correlation complex.        │
├──────────────────────────────────────┼──────────────────────────────────────────────────────────────┤
│ **Integrity Problems**               │ Consistency constraints (e.g., `Account_Balance >= 0`,       │
│                                      │ `Course_Grade IN ('A','B','C','D','F')`) must be explicitly  │
│                                      │ hardcoded inside every application program. Adding a new rule│
│                                      │ requires modifying all application source code files.        │
├──────────────────────────────────────┼──────────────────────────────────────────────────────────────┤
│ **Atomicity Problems**               │ Hardware crashes or power failures midway through a composite│
│                                      │ operation (e.g., funds transfer: debiting Account A without  │
│                                      │ crediting Account B) leave data in an invalid partial state. │
├──────────────────────────────────────┼──────────────────────────────────────────────────────────────┤
│ **Concurrent Access Anomalies**      │ Uncoordinated concurrent updates by multiple users lead to   │
│                                      │ race conditions, lost updates, and temporary inconsistent    │
│                                      │ reads without isolation guarantees.                          │
├──────────────────────────────────────┼──────────────────────────────────────────────────────────────┤
│ **Security and Access Control**      │ Granular access control (e.g., an employee viewing their own │
│                                      │ salary but not their supervisor's) is virtually impossible to│
│                                      │ enforce using rudimentary operating system file permissions. │
└──────────────────────────────────────┴──────────────────────────────────────────────────────────────┘

B. Primary Characteristics of the Database Approach

The database philosophy introduces five fundamental paradigm shifts:

Self-Describing Nature of a Database System: The DBMS stores not only the operational data, but also a complete definition of its structure, formats, relationships, and constraints. This self-description is cataloged in the System Catalog or Data Dictionary as Metadata ("data about data").
Insulation between Programs and Data (Program-Data Independence): The internal structure of database records is abstracted away from application programs. Modifying physical storage devices, index structures, or record layouts does not require altering application source code.
Support for Multiple Views of the Data: Each class of users requires a specialized perspective (view) of the database, presenting relevant information while abstracting or masking confidential columns.
Sharing of Data and Multi-User Transaction Processing: Concurrency control mechanisms ensure that concurrent read and write operations execute without interfering with one another, maintaining consistency.
Control of Data Redundancy: Rather than maintaining duplicate copies, data is integrated into a unified relational schema, restricting redundancy strictly to foreign key linkage columns.

2. DBMS Architecture: The ANSI-SPARC Three-Schema Framework

To achieve program-data independence and support multiple customized user perspectives, the ANSI-SPARC (American National Standards Institute, Standards Planning and Requirements Committee) proposed the Three-Schema Architecture.

CODE
                              THE THREE-SCHEMA ARCHITECTURE
                                            │
         ┌──────────────────────────────────┴──────────────────────────────────┐
    External View 1                    External View 2                   External View n
 (Registrar Web Portal)              (Finance Department)               (Student Mobile App)
         │                                  │                                  │
         └──────────────────────────────────┼──────────────────────────────────┘
                                            │  (External / Conceptual Mapping)
                                            ▼
                             CONCEPTUAL / LOGICAL SCHEMA
                    (Community view: Entities, Attributes, Relationships,
                              Integrity Rules, Domain Constraints)
                                            │
                                            │  (Conceptual / Internal Mapping)
                                            ▼
                                  INTERNAL / PHYSICAL SCHEMA
                     (Physical Storage Layout: B-Tree Indexes, Hashing,
                           Clustered File Organization, Data Blocks)
                                            │
                                            ▼
                                  PHYSICAL DATABASE STORAGE
                               (Magnetic Disks, SSDs, RAID Arrays)

A. Architectural Levels and Schema Abstraction

Schema Level	Architectural Role & Scope	Typical Constructs & Implementation Details	User Group
External / View Level	Describes the subset of the database relevant to a specific user group; hides irrelevant and confidential entities.	SQL `CREATE VIEW`, parameterized queries, graphical forms, API response JSON schemas.	End-users, specialized clerks, application developers.
Conceptual / Logical Level	Global logical description of the entire enterprise database; abstracts physical storage details completely.	Relational tables (`CREATE TABLE`), entity sets, primary/foreign key constraints, check constraints, normalization (3NF/BCNF).	Database Administrator (DBA), enterprise data architects.
Internal / Physical Level	Low-level description of physical storage structures, access paths, record layouts, and indexing mechanisms.	Data block sizing, record sequencing, B+ tree indexes, hash partitions, compression, tablespace storage allocations.	Systems programmers, storage engineers, tuning DBAs.

B. Conceptual and Physical Mappings

Data independence is maintained through explicit bidirectional translation mechanisms termed Mappings:

External/Conceptual Mapping: Translates queries phrased against an external view into relational algebraic operations evaluated against the global conceptual schema, and conversely converts resulting tuples into the target user presentation format.
Conceptual/Internal Mapping: Translates relational conceptual requests into low-level operating system block read/write operations, record pointers, and index traversals across persistent secondary storage.

3. Data Independence: Logical and Physical

Data Independence is the capacity to modify the schema at one level of the database architecture without necessitating modifications to the schemas at higher levels or altering existing application software.

CODE
┌─────────────────────────────────┬─────────────────────────────────┬──────────────────────────────────┐
│ Dimension of Independence       │ Definition & Mechanism          │ Concrete Enterprise Examples     │
├─────────────────────────────────┼─────────────────────────────────┼──────────────────────────────────┤
│ **Logical Data Independence**   │ The capacity to modify the      │ • Adding a new entity (e.g.,     │
│                                 │ **conceptual schema** without   │   `Student_Scholarships`) to the │
│                                 │ having to alter existing        │   database.                      │
│                                 │ **external schemas** or rewrite │ • Adding a new column to a table │
│                                 │ application programs.           │   without affecting queries that │
│                                 │                                 │   select specific named columns. │
│                                 │ *Mechanism:* Handled via view   │ • Decomposing a table during     │
│                                 │ reconstruction and external-to- │   normalization while maintaining│
│                                 │ conceptual mappings.            │   the original view via a `JOIN`.│
├─────────────────────────────────┼─────────────────────────────────┼──────────────────────────────────┤
│ **Physical Data Independence**  │ The capacity to modify the      │ • Migrating tables from magnetic │
│                                 │ **internal/physical schema**    │   HDDs to high-throughput NVMe.  │
│                                 │ without altering the global     │ • Building a new B+ tree index   │
│                                 │ **conceptual schema** or        │   on `Customer(Email)` to speed  │
│                                 │ application programs.           │   lookup queries.                │
│                                 │                                 │ • Changing from heap file to hash│
│                                 │ *Mechanism:* Handled via        │   clustered file organization.   │
│                                 │ conceptual-to-internal mappings.│ • Reorganizing disk block layouts│
└─────────────────────────────────┴─────────────────────────────────┴──────────────────────────────────┘

Comparative Complexity:

Logical data independence is considerably more challenging to achieve than physical data independence, because application programs are heavily coupled to the logical structure of the data they consume. Modifying existing attributes or splitting relational entities often requires sophisticated view re-mappings to insulate legacy application code.

4. Database Schema vs. Database Instance

A rigorous mathematical and structural distinction exists between the structural definition of a database and its operational data contents:

$\text{Database System} = \text{Schema (Intension)} + \text{State / Instance (Extension)}$

CODE
┌──────────────────────────────────────┬──────────────────────────────────────────────────────────────┐
│ Database Schema (The Intension)      │ Database Instance / State (The Extension)                    │
├──────────────────────────────────────┼──────────────────────────────────────────────────────────────┤
│ The overall structural design and    │ The actual collection of data populated within the database  │
│ description of the database.         │ at a specific moment in time (a temporal snapshot).          │
├──────────────────────────────────────┼──────────────────────────────────────────────────────────────┤
│ Declared once during database design │ Constantly mutates through data manipulation language (DML)  │
│ and changes very infrequently.       │ operations (`INSERT`, `UPDATE`, `DELETE`).                   │
├──────────────────────────────────────┼──────────────────────────────────────────────────────────────┤
│ Specified via Data Definition        │ Queried and updated via Data Manipulation Language (DML).   │
│ Language (DDL) statements.           │                                                              │
├──────────────────────────────────────┼──────────────────────────────────────────────────────────────┤
│ Equivalent to variable declarations, │ Equivalent to the runtime variable values stored in memory   │
│ data types, and classes in a         │ at a specific point during program execution.                │
│ programming language.                │                                                              │
├──────────────────────────────────────┼──────────────────────────────────────────────────────────────┤
│ *Example:*                           │ *Example:*                                                   │
│ `STUDENT(RollNo: INT, Name: VARCHAR, │ `{ (101, 'Aditya Kumar', 'CS'),                              │
│         Branch: VARCHAR)`            │   (102, 'Priya Sharma',  'EE') }`                            │
└──────────────────────────────────────┴──────────────────────────────────────────────────────────────┘

A database state is valid if and only if it strictly satisfies all structural integrity constraints, domain constraints, key restrictions, and foreign key references declared in the database schema.

5. Architectural Components of a DBMS Engine

A modern multi-user relational DBMS is an intricate distributed software system divided into two core operational functional subsystems: the Query Processor and the Storage Manager.

CODE
                           DETAILED DBMS ENGINE ARCHITECTURE
                                          │
       ┌──────────────────────────────────┴──────────────────────────────────┐
  [Query Processor Subsystem]                                      [Storage Manager Subsystem]
       │                                                                     │
  • DDL Interpreter                                                 • Buffer Pool Manager
    (Processes schema changes & catalog)                              (LRU/Clock cache replacement)
  • DML Compiler & Lexer / Parser                                   • File & Record Manager
    (Syntax trees, semantic validation)                               (Allocates disk space, heap files)
  • Query Optimizer                                                 • Index Manager
    (Cost estimation, relational algebra)                             (B+ Tree / Hash lookups)
  • Query Execution Engine                                          • Transaction & Concurrency Controller
    (Plan operators: Index Scans, Hash Joins)                         (2PL locks, ACID coordination)
                                                                    • Recovery Manager
                                                                      (WAL logging, ARIES checkpoints)
                                          │
                                          ▼
                               [System Catalog / Metadatabase]
                               (Data Dictionary, Schema Tables)
                                          │
                                          ▼
                             [Operating System Storage System]
                                  (File system, Block I/O)

A. The Query Processor Subsystem

DDL Interpreter: Parses DDL statements (CREATE, ALTER, DROP, RENAME) and records resulting metadata definitions directly inside the system catalog tables.
DML Compiler & Parser: Translates high-level declarative SQL statements into low-level relational algebra expressions, checking syntactic grammar and resolving column names against data dictionary catalogs.
Query Optimization Engine: Evaluates alternative relational algebra execution trees. Using statistical estimates stored in the system catalog (histogram distributions, cardinalities, available indexes, block clustering), the optimizer selects the execution path with the lowest estimated disk I/O and CPU computational cost.
Execution Engine: Executes the compiled execution plan via iterative pipeline primitives (e.g., Volcano-style iterator model: open(), next(), close()), calling upon the storage manager to retrieve records.

B. The Storage Manager Subsystem

Buffer Pool Manager: Allocates and manages database cache memory in RAM. It fetches secondary storage data blocks into memory buffers and writes dirty blocks back using eviction algorithms (e.g., Least Recently Used [LRU], Clock).
File & Record Manager: Organizes physical bytes on secondary disk storage into fixed-size slotted pages, maintaining free-space maps and variable-length record formats.
Index Manager: Manages search data structures (predominantly B+ trees and extensible hash buckets) to accelerate record retrieval without scanning entire relation files.
Transaction & Concurrency Control Manager: Guarantees that concurrent transactions execute without conflict, enforcing serializability through locking protocols (Two-Phase Locking [2PL]) or Multiversion Concurrency Control (MVCC).
Recovery Manager: Maintains Write-Ahead Logging (WAL) files on stable disk storage to guarantee transaction atomicity and durability across system crashes, powering checkpointing algorithms like ARIES.

6. Classification of Database Models

Data models define the conceptual grammar for structuring data, representing relationships, and imposing integrity constraints.

CODE
┌───────────────────┬──────────────────────────────────┬──────────────────────────────────────────────┐
│ Data Model        │ Underlying Mathematical Structure│ Core Paradigm & Historic Context             │
├───────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│ **Hierarchical**  │ Inverted Directed Trees          │ Records are linked via parent-child pointer  │
│                   │ (1:N strictly; no M:N without    │ trees. Fast traversal, but rigid; deletion of│
│                   │ duplication).                    │ a parent deletes children. (IBM IMS, 1968).  │
├───────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│ **Network**       │ Directed Graphs / CODASYL Sets   │ Entities are record types; relationships are │
│                   │ (Supports M:N via member sets).  │ explicit pointer sets. High complexity; code │
│                   │                                  │ depends on physical pointers. (IDMS, 1971).  │
├───────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│ **Relational**    │ First-Order Predicate Logic &    │ Data is perceived strictly as two-dimensional│
│                   │ Set Theory (Codd's Relations).   │ tables (relations) of tuples; relationships  │
│                   │                                  │ established via values. (Oracle, PostgreSQL).│
├───────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│ **Object-Oriented** Complex Objects & Encapsulation │ Integrates OOP concepts directly into storage│
│                   │ (Object IDs, methods, class      │ (inheritance, polymorphism, complex nesting).│
│                   │ hierarchies).                    │ Eliminates impedance mismatch. (ObjectStore).│
├───────────────────┼──────────────────────────────────┼──────────────────────────────────────────────┤
│ **Semi-Structured** Hierarchical key-value/document  │ Schemaless or schema-flexible representation │
│ (NoSQL)           │ trees (JSON, BSON, XML).         │ where each record may carry distinct schema. │
│                   │                                  │ Highly scalable. (MongoDB, Cassandra).       │
└───────────────────┴───────────────────┴──────────────────────────────────────────────┘

7. Entity-Relationship (ER) Modeling: Foundations and Notations

The Entity-Relationship (ER) Model (introduced by Dr. Peter Chen in 1976) is a high-level conceptual data model that maps real-world enterprises into semantic entities, descriptive attributes, and inter-entity relationships.

CODE
                           CHEVRON / CHEN ER NOTATION STANDARDS
                                            │
         ┌──────────────────────────────────┼──────────────────────────────────┐
    ENTITY SETS                         ATTRIBUTES                        RELATIONSHIPS
         │                                  │                                  │
  • Strong Entity: [Rectangle]       • Simple: (Oval)                   • Relationship: ◇ (Diamond)
  • Weak Entity: [Double Rectangle]  • Key: (Underlined Oval)           • Identifying Rel: ◇◇ (Double Diamond)
                                     • Multivalued: ((Double Oval))
                                     • Composite: (Branching Tree)
                                     • Derived: (--Dashed Oval--)

A. Entities and Entity Sets

Entity: An independently identifiable real-world object, person, event, or concept possessing distinct physical existence (e.g., a specific student Aditya, a car MH-12-AB-1234) or conceptual existence (e.g., a university course INT306, a bank loan L-101).
Entity Set: A collection of all entities of the same type sharing identical attributes (e.g., the set of all students registered at a university: STUDENT).
Entity Type: The intensional schema definition defining the common structural properties of the entity set.

B. Detailed Taxonomy of Attributes

CODE
┌─────────────────────────────────┬─────────────────────────────────┬──────────────────────────────────┐
│ Attribute Classification        │ Formal Definition               │ Concrete Enterprise Examples     │
├─────────────────────────────────┼─────────────────────────────────┼──────────────────────────────────┤
│ **Simple (Atomic)**             │ Indivisible attributes that     │ `Age`, `Gender`, `Roll_Number`,  │
│                                 │ cannot be split into components.│ `Salary`.                        │
├─────────────────────────────────┼─────────────────────────────────┼──────────────────────────────────┤
│ **Composite**                   │ Attributes that can be divided  │ `Name` $\rightarrow$ (`First_Name`,│
│                                 │ into smaller sub-attributes     │ `Middle_Name`, `Last_Name`).     │
│                                 │ with independent meaning.       │ `Address` $\rightarrow$ (`Street`,│
│                                 │                                 │ `City`, `State`, `Pincode`).     │
├─────────────────────────────────┼─────────────────────────────────┼──────────────────────────────────┤
│ **Single-Valued**               │ An entity has at most a single  │ `Date_of_Birth`, `SSN`,          │
│                                 │ value for this attribute.       │ `Aadhaar_Number`.                │
├─────────────────────────────────┼─────────────────────────────────┼──────────────────────────────────┤
│ **Multi-Valued**                │ An entity can possess a set of  │ `Phone_Numbers`, `Email_Ids`,    │
│                                 │ multiple values for the field.  │ `Degrees_Earned`.                │
│                                 │ Depicted as **Double Ovals**.   │                                  │
├─────────────────────────────────┼─────────────────────────────────┼──────────────────────────────────┤
│ **Derived**                     │ Value computed dynamically from │ `Age` (computed from `DOB` and   │
│                                 │ stored attributes or functions. │ `CURRENT_DATE`), `Total_Amount`, │
│                                 │ Depicted as **Dashed Ovals**.   │ `Years_of_Experience`.           │
├─────────────────────────────────┼─────────────────────────────────┼──────────────────────────────────┤
│ **Key Attribute**               │ Values are unique for each      │ `Student_ID`, `Employee_Number`, │
│                                 │ entity in the entity set.       │ `Account_Number`.                │
│                                 │ Depicted as **Underlined Text**.│                                  │
└─────────────────────────────────┴─────────────────────────────────┴──────────────────────────────────┘

C. Relationships and Relationship Degree

A Relationship is an association among two or more distinct entities. The Degree of a Relationship is the count of participating entity sets:

Unary (Recursive) Relationship (Degree 1): The same entity set participates in the relationship in distinct roles.
- Example: EMPLOYEE participates in Manages (one employee acts in the role of manager, other employees act in the role of subordinates).
Binary Relationship (Degree 2): An association between entities of two distinct entity sets.
- Example: STUDENT enrolls in COURSE.
Ternary Relationship (Degree 3): An association simultaneously linking entities from three distinct entity sets.
- Example: SUPPLIER supplies PART for a specific PROJECT.

8. Structural Constraints on Relationships: Cardinality Ratios and Participation

Structural constraints define the valid configurations of relationship instances linking real-world entities.

CODE
                           STRUCTURAL CONSTRAINTS
                                      │
         ┌────────────────────────────┴────────────────────────────┐
    Cardinality Ratio (Mapping Ratio)                    Participation Constraint
         │                                                         │
  • One-to-One (1:1)                                        • Total Participation (Existence Dependency)
  • One-to-Many (1:N)                                         (Double line; every entity MUST participate)
  • Many-to-One (N:1)                                       • Partial Participation
  • Many-to-Many (M:N)                                        (Single line; some entities need not participate)

A. Cardinality Ratios for Binary Relationships

The cardinality ratio specifies the maximum number of relationship instances an entity can participate in:

One-to-One (1:1): Each entity in entity set $A$ $A$ is associated with at most one entity in entity set $B$ $B$ , and vice versa.
- Example: DEPARTMENT is managed by EMPLOYEE (Manages). (Assuming each department has one manager, and an employee can manage at most one department).
One-to-Many (1:N): An entity in $A$ $A$ is associated with any number (zero or more) of entities in $B$ $B$ , but an entity in $B$ $B$ can be associated with at most one entity in $A$ $A$ .
- Example: DEPARTMENT employs EMPLOYEE (Works_For). (A department has multiple employees; each employee belongs to exactly one department).
Many-to-Many (M:N): An entity in $A$ $A$ is associated with any number of entities in $B$ $B$ , and an entity in $B$ $B$ is associated with any number of entities in $A$ $A$ .
- Example: STUDENT enrolls in COURSE (Enrolled_In). (A student takes multiple courses; a course hosts multiple students).

B. Participation Constraints

Total Participation (Existence Dependency): Every entity in the entity set must participate in at least one relationship instance. Depicted as a Double Line linking the entity rectangle to the relationship diamond.
- Example: If every loan must be associated with at least one customer, the participation of LOAN in Borrower is total.
Partial Participation: Some entities in the entity set may not participate in the relationship. Depicted as a standard Single Line.
- Example: Not every employee manages a department; the participation of EMPLOYEE in Manages is partial.

C. Min-Max Notation (Alternate Constraint Representation)

In modern database engineering (often used in the Elmasri & Navathe convention), structural constraints are replaced by a pair of integers $(min, max)$ placed adjacent to the participating entity edge:
$0 \le min \le max \quad \text{and} \quad max \ge 1$

$min = 0$ indicates partial participation.
$min \ge 1$ indicates total participation.
$max = 1$ indicates that an entity participates in at most one relationship instance.
$max = N$ (or $*$ ) indicates that an entity can participate in unbounded relationship instances.

9. Weak Entity Sets and Identifying Relationships

A Weak Entity Set is an entity set that does not possess sufficient attributes to form a primary key on its own. Conversely, an entity set that possesses a primary key is termed a Strong Entity Set (or regular entity set).

CODE
                      WEAK ENTITY ARCHITECTURAL STRUCTURE
                                       │
     [EMPLOYEE] ═══════════ ◇◇(Has_Dependent)◇◇ ═══════════ [[DEPENDENT]]
   (Strong Entity)      (Identifying Relationship)      (Weak Entity Set)
   Key: Emp_ID                                          Partial Key: Dep_Name
                                                        (Dotted Underline)

A. Formal Characteristics of Weak Entities

Existence Dependency: A weak entity cannot exist in the database without being associated with a corresponding owner entity from a strong entity set (identifying owner).
The Identifying Relationship: The relationship linking the weak entity set to its strong owner is an Identifying Relationship, depicted graphically as a Double Diamond ( $\diamond\diamond$ ).
Double Rectangle: The weak entity set is represented graphically by a Double Rectangle.
Partial Key (Discriminator): A weak entity set possesses a partial key (or discriminator), which is the set of attributes that uniquely identifies a specific weak entity relative to its owner strong entity. Depicted with a Dashed or Dotted Underline.
Primary Key Synthesis: The primary key of a weak entity relation is formed by concatenating the primary key of its identifying strong entity with its own discriminator:
$\text{Primary Key}(\text{Weak Entity}) = \text{Primary Key}(\text{Strong Owner}) \cup \text{Partial Key}(\text{Discriminator})$

Concrete Enterprise Case Study:

Strong Entity: EMPLOYEE (Emp_ID, Emp_Name, Salary) $\rightarrow$ Primary Key: Emp_ID.
Weak Entity: DEPENDENT (Dep_Name, Birthdate, Relationship) $\rightarrow$ Discriminator: Dep_Name.
Two distinct employees can have children with the identical name "Aarav". Dep_Name alone cannot guarantee uniqueness across the enterprise. Concatenating Emp_ID + Dep_Name provides a composite primary key.

10. Extended / Enhanced ER (EER) Concepts

As database systems evolved to manage complex spatial, engineering, and multimedia domains, standard ER modeling was extended with object-oriented abstractions.

CODE
                           ENHANCED ER (EER) HIERARCHY
                                        │
         ┌──────────────────────────────┴──────────────────────────────┐
  SPECIALIZATION (Top-Down)                                   GENERALIZATION (Bottom-Up)
  Superclass ──> Subclasses                                   Subclasses ──> Superclass
         │                                                             │
  [EMPLOYEE] ──o (Disjoint/Total)                             [CAR], [TRUCK], [MOTORCYCLE]
       ├── [Salaried_Emp]                                                  ├── [VEHICLE]
       └── [Hourly_Emp]

A. Specialization and Generalization

Specialization (Top-Down Design): The process of defining a set of specialized subclasses from an existing general entity superclass based on distinguishing characteristics.
Generalization (Bottom-Up Synthesis): The process of abstracting common attributes and relationships from multiple lower-level entity sets into a higher-level generalized superclass, suppressing differences.
Attribute Inheritance: Every subclass automatically inherits all attributes and relationship participations possessed by its parent superclass, appending only specialized attributes unique to itself.

B. Constraints on Specialization and Generalization

Disjointness Constraint:
- Disjoint (d): An entity instance can belong to at most one specialized subclass (e.g., an employee is either salaried OR hourly, but not both).
- Overlapping (o): An entity instance can simultaneously belong to multiple subclasses (e.g., a person in a university can simultaneously be an employee and an active student).
Completeness Constraint:
- Total Specialization (Double Line): Every entity in the superclass must belong to at least one subclass.
- Partial Specialization (Single Line): An entity in the superclass need not belong to any subclass (e.g., a generic vehicle that is neither a car nor a truck).

C. Aggregation

Standard ER relationships cannot easily link a relationship directly to another relationship. Aggregation is an abstraction through which a relationship set and its participating entity sets are treated as a higher-level composite entity set, enabling it to participate in subsequent external relationships.

11. Relational Reduction: Algorithmic Mapping of ER Models to Relational Schemas

A critical phase of database design is the deterministic transformation of conceptual ER diagrams into formal relational schemas.

CODE
┌─────────────────────────────────┬──────────────────────────────────────────────────────────────────┐
│ Conceptual ER Construct         │ Relational Schema Transformation Algorithm                       │
├─────────────────────────────────┼──────────────────────────────────────────────────────────────────┤
│ **Strong Entity Set**           │ Create a separate table. All simple attributes become columns.   │
│                                 │ The entity's key attribute becomes the table's Primary Key (PK). │
├─────────────────────────────────┼──────────────────────────────────────────────────────────────────┤
│ **Composite Attributes**        │ Flatten into individual atomic columns (e.g., `Name` becomes     │
│                                 │ `First_Name`, `Middle_Name`, `Last_Name`). Do NOT retain the     │
│                                 │ parent composite container column.                               │
├─────────────────────────────────┼──────────────────────────────────────────────────────────────────┤
│ **Multi-Valued Attributes**     │ Create a separate table containing the attribute plus the PK of  │
│                                 │ the owning entity as a Foreign Key (FK). The composite of the FK │
│                                 │ and the multivalued attribute forms the composite PK.            │
├─────────────────────────────────┼──────────────────────────────────────────────────────────────────┤
│ **Weak Entity Set**             │ Create a separate table containing all weak entity attributes.   │
│                                 │ Include the PK of the strong owner entity as an FK. The PK of the│
│                                 │ weak table is composite: `(Owner_PK, Discriminator)`.            │
├─────────────────────────────────┼──────────────────────────────────────────────────────────────────┤
│ **Binary 1:1 Relationship**     │ Three possible strategies:                                       │
│                                 │ 1. Foreign Key Approach: Place the PK of one entity as an FK in  │
│                                 │    the other (prefer total participation side to avoid NULLs).   │
│                                 │ 2. Merged Table: If both participations are total, combine both  │
│                                 │    entities into a single unified table.                         │
│                                 │ 3. Cross-reference Table: Create an explicit join table.         │
├─────────────────────────────────┼──────────────────────────────────────────────────────────────────┤
│ **Binary 1:N Relationship**     │ Place the PK of the entity on the **"1" side** as a Foreign Key  │
│                                 │ inside the relation on the **"N" side**. Include any descriptive │
│                                 │ relationship attributes on the "N" side table.                   │
├─────────────────────────────────┼──────────────────────────────────────────────────────────────────┤
│ **Binary M:N Relationship**     │ Mandatory separate Relationship Table (Junction / Bridge table). │
│                                 │ Contains the PKs of both participating entity sets as FKs. Their │
│                                 │ combination forms the composite PK. Descriptive attributes are   │
│                                 │ included as columns in this junction relation.                   │
├─────────────────────────────────┼──────────────────────────────────────────────────────────────────┤
│ **N-ary Relationship (N > 2)**  │ Create a distinct relationship table containing the PKs of all   │
│                                 │ participating entity sets as FKs, plus descriptive attributes.   │
└─────────────────────────────────┴──────────────────────────────────────────────────────────────────┘

12. Enterprise Case Study: University Campus Database Schema Design

Conceptual Requirements Specification:

The university maintains records of students, academic departments, instructors, and courses.
Each DEPARTMENT has a unique Dept_Code, Dept_Name, and Office_Location.
Each INSTRUCTOR has an Instructor_ID, Name, Salary, and belongs to exactly one department (1:N). One instructor serves as the Head of Department (HOD) for each department (1:1).
Each STUDENT has a unique Roll_No, composite Name, DOB, and multi-valued Phone_Numbers. A student belongs to one major department.
Each COURSE has a Course_ID, Title, Credits, and is offered by one department.
Instructors teach multiple course offerings (TEACHES - 1:N). Students enroll in multiple courses (ENROLLS - M:N) with a recorded Grade.

Resulting Relational Schema Architecture:

sql
-- 1. DEPARTMENT (Strong Entity)
CREATE TABLE DEPARTMENT (
    Dept_Code VARCHAR(10) PRIMARY KEY,
    Dept_Name VARCHAR(100) NOT NULL UNIQUE,
    Office_Location VARCHAR(50),
    HOD_Instructor_ID INT UNIQUE -- 1:1 Relationship (Foreign Key)
);

-- 2. INSTRUCTOR (Strong Entity, 1:N with Department)
CREATE TABLE INSTRUCTOR (
    Instructor_ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Salary DECIMAL(10,2) CHECK (Salary > 0),
    Dept_Code VARCHAR(10) NOT NULL,
    FOREIGN KEY (Dept_Code) REFERENCES DEPARTMENT(Dept_Code)
);

-- Add deferred foreign key constraint for HOD
ALTER TABLE DEPARTMENT 
ADD CONSTRAINT fk_hod 
FOREIGN KEY (HOD_Instructor_ID) REFERENCES INSTRUCTOR(Instructor_ID);

-- 3. STUDENT (Strong Entity with flattened composite Name)
CREATE TABLE STUDENT (
    Roll_No INT PRIMARY KEY,
    First_Name VARCHAR(50) NOT NULL,
    Last_Name VARCHAR(50) NOT NULL,
    Date_of_Birth DATE NOT NULL,
    Dept_Code VARCHAR(10) NOT NULL,
    FOREIGN KEY (Dept_Code) REFERENCES DEPARTMENT(Dept_Code)
);

-- 4. STUDENT_PHONE (Relational Reduction of Multivalued Attribute)
CREATE TABLE STUDENT_PHONE (
    Roll_No INT,
    Phone_Number VARCHAR(15),
    PRIMARY KEY (Roll_No, Phone_Number),
    FOREIGN KEY (Roll_No) REFERENCES STUDENT(Roll_No) ON DELETE CASCADE
);

-- 5. COURSE (Strong Entity)
CREATE TABLE COURSE (
    Course_ID VARCHAR(10) PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Credits INT CHECK (Credits BETWEEN 1 AND 6),
    Dept_Code VARCHAR(10) NOT NULL,
    Instructor_ID INT, -- 1:N Teaches
    FOREIGN KEY (Dept_Code) REFERENCES DEPARTMENT(Dept_Code),
    FOREIGN KEY (Instructor_ID) REFERENCES INSTRUCTOR(Instructor_ID)
);

-- 6. ENROLLS (Relational Reduction of Binary M:N Relationship with descriptive Grade attribute)
CREATE TABLE ENROLLS (
    Roll_No INT,
    Course_ID VARCHAR(10),
    Semester VARCHAR(10),
    Academic_Year INT,
    Grade CHAR(2) CHECK (Grade IN ('A+', 'A', 'B', 'C', 'D', 'F', 'I')),
    PRIMARY KEY (Roll_No, Course_ID, Semester, Academic_Year),
    FOREIGN KEY (Roll_No) REFERENCES STUDENT(Roll_No) ON DELETE CASCADE,
    FOREIGN KEY (Course_ID) REFERENCES COURSE(Course_ID) ON DELETE RESTRICT
);

13. Comprehensive Viva Voce Assessment Guide

Q: Explain the primary differences between physical and logical data independence.
A: Physical Data Independence refers to the capacity to modify the internal schema (such as altering file organizations, adding B+ tree indexes, restructuring disk blocks, or migrating storage hardware) without altering the global conceptual schema or application programs. In contrast, Logical Data Independence refers to the capacity to modify the conceptual schema (such as adding new entities, expanding attributes, or decomposing existing relations during normalization) without altering existing external views or breaking existing application code. Logical data independence is significantly more difficult to achieve because user applications are tightly coupled to the logical attributes and schemas they query.
Q: Why does a multi-valued attribute in an ER diagram require a separate relational table upon mapping?
A: In the foundational Relational Model formalized by E.F. Codd, all relations must adhere to First Normal Form (1NF), which dictates that the domain of every attribute must contain only atomic (indivisible) values. If a multivalued attribute (such as Phone_Numbers) were accommodated within the base entity table, it would either require storing non-atomic delimited lists (e.g., '9876543210, 9123456789'), violating 1NF and destroying index efficiency, or defining an arbitrary number of duplicate columns (Phone_1, Phone_2), wasting space with NULLs. Hence, relational reduction requires a separate relation containing the owning entity's primary key as a foreign key along with the attribute value, with both combined as a composite primary key.
Q: Define the concepts of partial key and identifying relationship in the context of weak entity sets.
A: A weak entity set lacks a sufficient set of attributes to form a primary key independently. It depends upon an identifying strong entity set for its existence. The partial key (or discriminator) is the specific attribute of the weak entity that uniquely distinguishes individual entities within the scope of a single parent strong entity instance (e.g., Dependent_Name under a specific Employee_ID). The identifying relationship is the relationship connecting the weak entity set to its strong owner, depicted by a double diamond. A weak entity's primary key is synthesized by concatenating the primary key of the strong owner with the weak entity's partial key.
Q: What is the fundamental role of the Query Optimizer in a Relational DBMS?
A: SQL is a purely declarative language; users declare what data they require, not how to procedurally fetch it. For any non-trivial query (especially involving joins across multiple tables), there are hundreds of logically equivalent relational algebra evaluation plans. The Query Optimizer parses the query, generates alternative execution trees, applies transformation rules (e.g., pushing selections and projections down the tree), and consults database catalog statistics (table sizes, indexes, clustering, histograms) to estimate disk I/O, memory, and CPU costs. It selects the lowest-cost execution plan for the query execution engine.
Q: Differentiate between the terms Database Schema, Database State, and Metadata.
A:
- Database Schema (Intension): The overall structural design, integrity constraints, and data type specifications of the database, declared via DDL. It is static and changes rarely.
- Database State / Instance (Extension): The actual operational data populated inside the database tables at a given moment in time (a runtime snapshot). It mutates dynamically with every DML insert, update, or delete.
- Metadata: Data describing the database structure itself (schema definitions, tables, columns, constraints, storage layouts, user privileges, access statistics). Metadata is managed by the DBMS and stored in the System Catalog or Data Dictionary.

Database Management Systems

Unit 1: Introduction to database