Database Management SystemUnit 27 min read
Unit 2: Database System Concepts & Architecture
Unit 2 of Database Management System: introduces the fundamental concepts of DBMS, data abstraction, data independence, the three‑schema architecture, and the layered architecture of a typical database system.
Key points
- A DBMS is a software system that manages data, providing a logical view, storage, and transaction support.
- Data abstraction separates the logical schema from the physical storage, enabling data independence.
- The three‑schema architecture (external, conceptual, internal) defines the logical separation between users, the database, and the storage.
- DBMS architecture comprises components such as storage manager, query processor, transaction manager, and buffer manager, each with distinct responsibilities.
- Understanding the interaction between these components is essential for designing efficient, reliable database systems.
1. Introduction
A Database Management System (DBMS) is a collection of programs that store, retrieve, and manipulate data while ensuring integrity, security, and concurrency.
- Data model: the abstract representation of data (e.g., relational, object‑oriented).
- Schema: the logical structure of the database (tables, relationships).
- Instance: the actual data at a point in time.
- Database state: the collection of all instances over time.
Unit 2 focuses on the conceptual and architectural aspects that allow a DBMS to provide these services efficiently and reliably.
2. Core Concepts
2.1 Data Abstraction
Data abstraction hides the complexity of data storage and representation. It is achieved through three levels:
| Level | Description | Example |
|---|---|---|
| Internal | Physical storage details (file organization, indexing). | B‑tree index on CustomerID. |
| Conceptual | Logical view of the entire database (tables, constraints). | Customer(CustomerID, Name, Address) |
| External | User‑specific views (schemas). | View for bank tellers: SELECT CustomerID, Name FROM Customer |
Abstraction enables data independence: changes at one level do not affect the others.
2.2 Data Independence
- Logical data independence: Modifying the conceptual schema (adding a column) does not require changes to external schemas or application programs.
- Physical data independence: Changing the internal schema (e.g., switching from B‑tree to hash index) does not affect the conceptual schema or applications.
Why it matters: Allows evolution of the database without costly rewrites of client applications.
2.3 Three‑Schema Architecture
- External (View) Schema – user‑specific views.
- Conceptual Schema – global logical structure.
- Internal Schema – physical storage details.
The architecture is illustrated below:
+-------------------+ +---------------------+ +---------------------+
| External Schema |<---->| Conceptual Schema |<---->| Internal Schema |
| (Views) | | (Logical Model) | | (Physical Storage) |
+-------------------+ +---------------------+ +---------------------+
Each layer communicates through schema mapping. The DBMS acts as the mediator.
3. DBMS Architecture
A typical DBMS follows a client‑server or multi‑tier architecture. The core components are:
| Component | Responsibility | Interaction |
|---|---|---|
| Storage Manager | Manages physical files, indexes, and buffer pool. | Provides data to Query Processor. |
| Query Processor | Parses, optimizes, and executes SQL queries. | Requests data from Storage Manager. |
| Transaction Manager | Ensures ACID properties, manages locks and logs. | Coordinates with Storage Manager and Buffer Manager. |
| Buffer Manager | Caches pages in memory, handles page replacement. | Works with Storage Manager. |
| Metadata Manager | Stores schema, constraints, and statistics. | Used by Query Processor and Transaction Manager. |
3.1 Client‑Server vs. Multi‑Tier
| Feature | Client‑Server | Multi‑Tier |
|---|---|---|
| Layers | 2 (Client, Server) | 3+ (Presentation, Application, Data) |
| Scalability | Limited | Better (load balancing, caching) |
| Deployment | Simple | Complex (requires middleware) |
3.2 Example: Banking Database Trace
Consider a simple banking database:
Customer(CustomerID, Name, Address)
Account(AccountNumber, CustomerID, Balance)
Loan(LoanNumber, CustomerID, Amount)
Scenario: Customer C123 deposits ₹10,000 into account A456.
| Step | Action | Component | Result |
|---|---|---|---|
| 1 | Client sends UPDATE Account SET Balance = Balance + 10000 WHERE AccountNumber='A456' |
Query Processor | Parses, optimizes |
| 2 | Query Processor requests page containing A456 |
Buffer Manager | Loads page into buffer |
| 3 | Transaction Manager acquires lock on A456 |
Transaction Manager | Lock granted |
| 4 | Buffer Manager updates page in memory | Buffer Manager | Page marked dirty |
| 5 | Transaction Manager writes log record | Transaction Manager | Log: UPDATE A456 10000 |
| 6 | Commit requested | Transaction Manager | Releases lock, writes page to disk |
| 7 | Client receives OK |
Query Processor | Transaction committed |
This trace demonstrates how the components collaborate to maintain consistency and durability.
4. Comparison: DBMS vs. Traditional File Processing
| Aspect | Traditional File System | DBMS |
|---|---|---|
| Data Redundancy | High (duplicate data across files) | Low (single source of truth) |
| Data Integrity | Manual enforcement | Automatic via constraints |
| Concurrency | Poor (file locks) | Advanced locking, MVCC |
| Security | File‑level permissions | Fine‑grained access control |
| Backup/Recovery | Manual, error‑prone | Automated, point‑in‑time recovery |
| Query Capability | Sequential scans, limited | Powerful SQL, indexes, joins |
| Maintenance | High (schema changes ripple) | Low (data independence) |
5. Advantages and Disadvantages of DBMS
| Advantages | Disadvantages |
|---|---|
| Data Integrity & Consistency | Complexity – requires learning DBMS concepts |
| Concurrent Access | Performance Overhead – transaction logs, locking |
| Security | Cost – licensing, hardware |
| Backup & Recovery | Vendor Lock‑in – proprietary features |
| Data Independence | Maintenance – need skilled DBAs |
6. Applications of DBMS
- Banking & Finance: Core banking systems, transaction processing.
- E‑commerce: Order management, inventory, recommendation engines.
- Healthcare: Electronic health records, patient scheduling.
- Telecommunications: Call detail records, billing.
- Government: Census data, land records.
7. Worked Example: Normalization and Schema Evolution
7.1 Initial Schema
Customer(CustomerID, Name, Address, Phone, Email, LoanNumber, LoanType, Amount)
All data in one table → 1NF violated? Actually each attribute atomic, but redundancy high.
7.2 1NF
Ensure atomic values: already satisfied.
7.3 2NF
Remove partial dependencies on composite key. Since primary key is CustomerID, no partial dependencies. Schema remains.
7.4 3NF
Remove transitive dependencies: LoanNumber depends on CustomerID, but LoanType and Amount depend on LoanNumber. Create separate Loan table:
Customer(CustomerID, Name, Address, Phone, Email)
Loan(LoanNumber, CustomerID, LoanType, Amount)
Now no transitive dependencies.
Result: Reduced redundancy, improved update integrity.
8. Summary
Unit 2 equips students with a solid understanding of how a DBMS is structured and how it abstracts data to provide independence and reliability. Mastery of these concepts is essential for designing, implementing, and maintaining robust database systems.
Exam tip
- Focus on definitions: Be able to define data abstraction, data independence, three‑schema architecture, and DBMS components.
- Diagram recall: Practice sketching the three‑schema diagram and the DBMS component diagram.
- Trace questions: Many past papers ask you to trace a transaction through the architecture; rehearse the steps (parse → optimize → lock → log → commit).
- Comparison tables: Be ready to compare DBMS with file processing; use a concise table.
- Normalization: Know the difference between 1NF, 2NF, 3NF and be able to transform a given schema.
Good luck!
Based on the TU BSc CSIT syllabus for Database Management System (CSC265), unit 2.
Discussion
Loading…