Introduction to Database Management Systems

Exam Tip: If asked to distinguish between a File Management System (FMS) and a DBMS, remember that a DBMS provides automated integrity constraints, transaction management, and much more granular security than an FMS.

DBMS Architecture and Access Levels

To manage the complexity of separating how data is stored from how it is viewed, a DBMS uses a layered architecture. This architecture is divided into three distinct access levels:

1. The External Level: This is the user interface. It handles the dialogue with the users, analyzes their requests, and presents the results.
2. The Intermediate Level: This level acts as the "brain" of the system. It provides control functions such as query optimization, managing concurrent access, and ensuring data consistency.
3. The Internal Level: This level deals with the physical reality of the data. It manages how data is actually stored on physical media (like disks) and handles storage structures and indexing.

Implementation Topologies

Depending on the needs of an organization, a DBMS can be implemented in several different ways, known as topologies:

• Centralized: Both the application program and the DBMS reside on the same single machine.
• Client-Server (2-tier): The application runs on a client machine, while the DBMS is installed on a separate server machine.
• Client-Server (3-tier): This adds a middle layer. A client app runs on the user's machine, a server application runs on a separate server, and the DBMS runs on yet another server.
• Distributed: The DBMS is installed and replicated across several different server machines. This topology is highly reliable because it enables load balancing and "failover" mechanisms (if one server fails, others take over).

Languages and User Roles

Interacting with a DBMS requires specific languages and involves different types of users, each with unique responsibilities.

DBMS Languages

• Data Definition Language (DDL): Used to express the logical schema or design of the database. These statements are compiled and stored in a "data dictionary". A specialized type of DDL called DSDL (Data Storage Definition Language) is used to specify storage methods.
• Data Manipulation Language (DML): Used to perform actual tasks like insertion, updating, deletion, and searching.

User Roles

• Database Administrator (DBA): The "manager" of the system. They control access, maintain performance, and manage backup/recovery procedures.
• Programmer: The "builder." They create tables and procedures and use the database to build applications.
• End User: The "consumer." They only have access to the specific data required for their tasks.

Transaction Management and ACID Properties

One of the most critical aspects of a DBMS is Transaction Management. A transaction is a sequence of data manipulation operations performed by a program, bounded by a "start" and an "end".

To ensure that these transactions do not corrupt the database, they must adhere to the ACID properties:

• Atomicity: The transaction must be treated as a single unit—it either happens completely or not at all.
• Consistency: The transaction must take the database from one valid state to another.
• Isolation: Transactions occurring simultaneously must not interfere with each other.
• Durability: Once a transaction is committed, it remains so, even in the event of a system failure.

The Challenge of Concurrency

Without proper Concurrency Control, multiple users accessing the same data can cause errors. For example, if two processes try to update the same account balance at the exact same time without coordination, one update might overwrite the other, leading to an incorrect final balance.

Summary Table: FMS vs. DBMS