Lecture 01: Why Databases?

1. Motivation: Why Not Just Use Plain Files?

Before DBMS, systems stored persistent records in flat OS files (e.g., CSV, text, or binary files) managed by application code. This file-processing approach suffers from severe fundamental shortcomings:

1. Data Redundancy and Inconsistency:
   • Different programs create their own copies of files, leading to duplicated data across departments.
   • When updates occur in one file but not another, data becomes contradictory.
2. Difficulty in Accessing Data:
   • Answering an ad-hoc query (e.g., "Find all students with GPA > 8.5 in CSE") requires writing a new dedicated C or Python script.
3. Data Isolation:
   • Data is scattered in various files with different formats, making joins or cross-referencing extraordinarily tedious.
4. Integrity Problems:
   • Constraints (e.g., balance >= 0 or credits in (3, 4)) are hard-coded in application logic. When constraints change, every application modifying the files must be audited and rewritten.
5. Atomicity Problems:
   • In a banking transfer from Account A to Account B, if the machine crashes after debiting A but before crediting B, money vanishes. File systems lack transaction boundaries.
6. Concurrent-Access Anomalies:
   • Multiple programs updating the same file simultaneously cause lost updates and corrupted files without fine-grained locking.
7. Security Problems:
   • File systems enforce permissions only at the OS file level; they cannot selectively restrict access to specific rows or columns.

2. The Three-Schema Architecture (ANSI/SPARC)

To decouple user applications from the physical storage details, the ANSI/SPARC architecture defines three distinct levels of abstraction:

+-------------------------------------------------------------+
|    External Level (Views / Subschemas for Users & Apps)     |
|   [Student Portal View]      [Registrar Transcript View]    |
+-------------------------------------------------------------+
                              |
                    Logical Data Independence
                              |
+-------------------------------------------------------------+
|    Conceptual Level (Logical Schema: Entities & Relations)  |
|            student, course, instructor, takes               |
+-------------------------------------------------------------+
                              |
                   Physical Data Independence
                              |
+-------------------------------------------------------------+
|    Internal Level (Physical Storage, Indexes, B-Trees)      |
|           Disk layout, page blocks, secondary indexes       |
+-------------------------------------------------------------+

The Three Levels Explained

1. External Level (View Schema):
   • Describes the part of the database relevant to a specific user group.
   • Hides irrelevant or sensitive data (e.g., a student view cannot see instructors' salaries).
2. Conceptual Level (Logical Schema):
   • Describes what data is stored in the database and the relationships among data items for the entire organization.
   • Independent of storage hardware or specific file structures.
3. Internal Level (Physical Schema):
   • Describes how data is actually stored physically on storage media (record layouts, B+ tree indexes, hashing, block sizes, compression).

3. Data Independence

Data independence is the capacity to change the schema at one level without having to alter the schema at the next higher level.

Physical Data Independence

• The capacity to modify the internal physical schema without requiring changes to the conceptual (logical) schema.
• Example: Creating a new B-Tree index on student(dept_name) or moving tables across SSDs does not require rewriting application SQL queries or altering table schemas.

Logical Data Independence

• The capacity to modify the conceptual schema without altering existing external schemas or application programs.
• Example: Adding a new attribute student(phone_number) or adding a new table extracurricular does not break applications that only query student(id, name).
• Note: Logical data independence is significantly harder to achieve than physical data independence because application logic is inherently tied to conceptual entities.

4. Schema vs. Instance

5. Review Exercises & Conceptual Questions