Intro & 3-Schema Architecture

A Database Management System (DBMS) is a software system designed to store, retrieve, and manage data. It ensures that data is stored securely, consistently, and is easily accessible to multiple users simultaneously.

Before the invention of DBMS, applications used File-Based Systems (storing data in simple .txt or .csv files). This caused massive problems:

• Data Redundancy: The same data was duplicated across many files.
• Data Inconsistency: Updating an address in one file but forgetting to update it in another.
• Difficulty in Access: Writing complex C/C++ programs just to retrieve specific records.
• Concurrent Access Anomalies: If two people tried to write to the same file simultaneously, data was corrupted.

1. Data Abstraction

A major purpose of a database system is to provide users with an abstract view of the data. That is, the system hides certain details of how the data is stored and maintained.

This abstraction allows developers to write SQL queries without needing to know whether the data is stored on a spinning hard drive, an SSD, or spread across 10 different servers.

2. The Three-Schema Architecture

To achieve this abstraction, the ANSI/SPARC committee proposed a standard framework for DBMS architecture, divided into three levels:

1. Internal Level (Physical Level)

The lowest level of abstraction describes how the data is actually stored physically on the storage devices.

• It deals with low-level data structures, B-Trees, file allocation, index implementations, and data compression.
• Example: "Store this record using a B+ tree index occupying 4KB disk blocks."

2. Conceptual Level (Logical Level)

The next-higher level of abstraction describes what data is stored in the database, and what relationships exist among those data.

• It hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints.
• Example: "There is a Students table with an ID (Integer) and a Name (String)."

3. External Level (View Level)

The highest level of abstraction describes only part of the entire database. It is what the end-user or the application actually sees.

• Different users may need different views of the same database. A payroll application only sees salaries, while a course registration system only sees student grades.
• Example: "Create a view that only shows the names of students in the CS department, hiding their passwords and ID numbers."

3. Data Independence

The main objective of the three-schema architecture is Data Independence, which is the capacity to change the schema at one level of a database system without having to change the schema at the next higher level.

• Logical Data Independence: The ability to change the conceptual schema (adding a new column to a table) without changing the external views or application programs.
• Physical Data Independence: The ability to change the internal schema (moving from a HDD to an SSD, or changing from a Hash Index to a B+ Tree) without having to change the conceptual schema.