Database System Concepts and Architecture

Learning Outcomes:

By the end of this summary, you should be able to:

·       Understand what a Database Management System (DBMS) is and how it differs from a file system.

·       Explain the Three-Schema Architecture and the concept of data independence.

·       Identify various database languages and how they interact with a DBMS.

·       Grasp the Client/Server Architecture model in databases.

·       Be introduced to key principles of SQL, relational database design, transaction processing, concurrency control, and database storage structures.

1. Introduction to Database System

What is a Database?

A database is an organized collection of related data that can be easily accessed, managed, and updated. Think of it as a digital filing system that stores data in a structured way so that information retrieval becomes efficient.

What is a DBMS?

A Database Management System (DBMS) is software that helps in:

·       Defining data (structure and types),

·       Manipulating data (inserting, updating, deleting, retrieving),

·       Controlling access to data (security, integrity, concurrency),

·       Ensuring reliability (backup, recovery, fault tolerance).

Popular DBMSs include MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

2. Database System vs. File System

Before databases became common, data was stored in file systems. Here’s how the two compare:

Feature	File System	Database System (DBMS)
Data Redundancy	High	Minimized
Data Integrity	Poor	Enforced using constraints
Data Access	Requires custom programs	Structured query language (SQL)
Security	Basic OS-level security	Role-based and fine-grained access control
Concurrency Control	Manual and complex	Built-in and automatic
Backup & Recovery	Manual	Automated tools

Why use a DBMS?
A DBMS solves the problems of data duplication, integrity, and security, which are common in file-based systems. It provides tools for easy query processing, efficient data sharing, and reliable storage.

3. Three-Schema Architecture

One of the core architectural principles of DBMS is the Three-Schema Architecture defined by ANSI/SPARC. It separates the database into three levels:

Internal Level (Physical Schema):

·       Deals with how data is stored physically (e.g., indexes, file structures).

·       Focus: Performance and storage efficiency.

Conceptual Level (Logical Schema):

·       Describes what data is stored and the relationships among data.

·       Independent of physical implementation.

·       Example: Tables, relationships, constraints.

External Level (View Schema):

·       Defines how users see the data.

·       Different users may have different views of the same database.

This architecture enables data abstraction and independence, two very important goals in DBMS.

4. Data Independence

Data Independence refers to the ability to change the database schema without altering the application programs. It is of two types:

·       Logical Data Independence: Changes in the conceptual schema (e.g., adding new attributes or tables) do not affect external views or applications.

·       Physical Data Independence: Changes in the internal schema (e.g., changing storage format or index strategy) do not affect the conceptual schema.

This is a huge advantage over file systems, where every schema change might require modifying all applications accessing the files.

5. Database Languages

DBMSs provide several specialized languages to interact with data:

🔹 Data Definition Language (DDL):

·       Used to define database schema (CREATE, ALTER, DROP).

• Example:

           CREATE TABLE Students (
  ID INT PRIMARY KEY,
  Name VARCHAR(100),
  Age INT
);

🔹 Data Manipulation Language (DML):

·       Used to manipulate data (INSERT, UPDATE, DELETE, SELECT).

• Example:

           INSERT INTO Students VALUES (1, ‘Alice’, 20);

🔹 Data Control Language (DCL):

·       Controls access and permissions (GRANT, REVOKE).

• Example:

           GRANT SELECT ON Students TO user1;

🔹 Transaction Control Language (TCL):

·       Manages transactions (COMMIT, ROLLBACK, SAVEPOINT).

·       Ensures ACID properties (Atomicity, Consistency, Isolation, Durability).

6. Client/Server Architecture in DBMS

Modern DBMSs often use a Client/Server model, where:

·       Client: Front-end application (e.g., user interface) that requests data.

·       Server: Back-end system (e.g., Oracle DBMS) that processes queries and manages data.

This architecture allows:

·       Multiple clients to access a shared database,

·       Centralized control and security,

·       Better scalability and performance.

7. Relational Database Design Principles

The Relational Model organizes data into tables (relations). Each table has:

·       Rows (Tuples): Individual records,

·       Columns (Attributes): Fields like ID, Name, Age.

Key Concepts:

·       Primary Key: Unique identifier for records,

·       Foreign Key: Refers to a primary key in another table,

·       Normalization: Reduces data redundancy by dividing data into well-structured tables (1NF, 2NF, 3NF).

Designing efficient databases requires understanding entity relationships, functional dependencies, and normal forms.

8. Introduction to SQL

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases.

Basic SQL Queries:

• Retrieve data:

           SELECT Name FROM Students WHERE Age > 18;

• Update data:

           UPDATE Students SET Age = 21 WHERE Name = ‘Alice’;

• Delete data:

           DELETE FROM Students WHERE ID = 1;

Learning SQL is crucial for interacting with any relational DBMS.

9. Transaction Processing and Concurrency Control

What is a Transaction?

A transaction is a sequence of database operations that must be executed as a single unit. Example: Transferring money between two accounts.

To maintain data integrity, transactions must satisfy ACID properties:

·       Atomicity: All or nothing,

·       Consistency: Data remains valid,

·       Isolation: Transactions are isolated from each other,

·       Durability: Once committed, changes are permanent.

Concurrency Control

When multiple users access the database simultaneously, issues like lost updates, dirty reads, or uncommitted data can occur.

Concurrency control mechanisms like locking, timestamp ordering, and serialization ensure that concurrent transactions execute correctly.

10. Storage Structures and Access Techniques

How is Data Stored?

DBMSs use data files, index files, and log files stored on disk.

Indexing:

·       Similar to a book index—used to speed up search operations.

·       Types: Single-level indexes, multi-level indexes, B+ Trees, and Hashing.

Buffer Management:

·       Data is read from disk into main memory (buffer).

·       The buffer manager decides what data to keep in memory and when to write it back to disk.

Access Methods:

·       Sequential Access: Data read one by one,

·       Indexed Access: Fast access via index,

·       Hashed Access: Uses hash functions for near-instant lookups.

Conclusion

Understanding Database System Concepts and Architecture is fundamental for every Computer Science student. It lays the foundation for building, designing, querying, and maintaining databases efficiently.

Here’s a quick recap:

·       DBMS provides structured, secure, and efficient data management compared to file systems.

·       Three-schema architecture offers data abstraction and independence.

·       SQL is the essential language for managing relational databases.

·       Concurrency control and transactions ensure data accuracy in multi-user environments.

·       Storage structures like indexing and hashing speed up data access.

These principles are essential not only in academic settings but also in the real-world IT industry where database systems play a vital role in everything from banking and healthcare to social media and e-commerce.