Lesson 4: Components, Architectures and Users¶
Inside the engine, and the people around it
About this lesson
Course: Database Management Systems
Unit 1: Introduction to Database · 5 Lecture Hours
Lesson duration: 2 lectures
Reading: Elmasri & Navathe, Ch. 1.4, 2.3, 2.5
Learning Outcomes
- Name and describe the major functional components of a DBMS.
- Distinguish the query processor from the storage manager.
- Explain the centralised, client/server, two-tier and three-tier architectures.
- Identify every category of database user and state their responsibilities.
- Classify the languages and interfaces a DBMS provides.
4.1 Components of a DBMS¶
A DBMS is not one program; it is a layered collection of cooperating modules. They fall into three groups: the query processor, the storage manager, and the disk structures.
Naive users Application Sophisticated DBA
(forms) programmers users (SQL) (DDL)
| | | |
+---v---+ +----v----+ +----v----+ +---v----+
|Applic.| |Applic. | | Query | | DDL |
|interf.| |programs | | tools | |interpr.|
+---+---+ +----+----+ +----+----+ +---+----+
| | | |
| +----v----+ | |
| |Precompi-| | |
| |ler / API| | |
| +----+----+ | |
+--------------+--------------+ |
| |
+---------v----------+ |
| DML COMPILER & | |
| QUERY PARSER | |
+---------+----------+ |
| |
+---------v----------+ |
| QUERY OPTIMISER | |
+---------+----------+ |
| |
+---------v----------+ |
| QUERY EVALUATION | |
| ENGINE | |
+=========+==========+ ============== |======= QUERY PROCESSOR
| |
+---------v-----------------+---------v-----+
| STORAGE MANAGER |
| +-------------+ +---------------------+ |
| | Authorisat. | | Transaction manager | |
| | & integrity | | (concurrency ctrl, | |
| | manager | | recovery manager) | |
| +-------------+ +---------------------+ |
| +-------------+ +---------------------+ |
| | File | | Buffer manager | |
| | manager | | | |
| +-------------+ +---------------------+ |
+--------------------+----------------------+
|
+-------------------------v--------------------------+
| D I S K S T O R A G E |
| data files | data dictionary | indexes | log |
| | (catalog) | | statistics|
+-----------------------------------------------------+
Figure 4.1 — The functional components of a DBMS.
The query processor¶
| Component | Responsibility |
|---|---|
| DDL interpreter | Parses CREATE/ALTER/DROP statements and records the definitions in the catalog. |
| DML compiler / query parser | Parses SELECT/INSERT/UPDATE/DELETE, checks syntax, and verifies names and types against the catalog. |
| Query optimiser | Chooses the cheapest execution plan among the many equivalent ones — which index to use, which join order, which join algorithm. Uses statistics kept in the catalog. |
| Query evaluation engine | Actually executes the chosen plan by calling the storage manager. |
| Embedded DML precompiler | Converts DML statements embedded in a host language (C, Java) into procedure calls to the runtime library. |
Why an optimiser exists
SQL is declarative: you say what you want, not how. Something must supply the how. For a three-table join there are already many candidate plans; the optimiser estimates the cost of each using catalog statistics (row counts, index selectivity) and picks the cheapest. A good optimiser routinely turns a query that would take minutes into one that takes milliseconds — and it is the direct beneficiary of physical data independence.
The storage manager¶
The storage manager is the interface between the low-level stored data and the queries submitted to the system.
| Component | Responsibility |
|---|---|
| Authorisation and integrity manager | Checks the user's privileges and enforces integrity constraints on every operation. |
| Transaction manager | Ensures the database remains in a consistent state despite failures and concurrency; provides COMMIT/ROLLBACK. Contains the concurrency-control manager (locking, serialisability) and the recovery manager (logging, restart recovery). |
| File manager | Manages allocation of disk space and the data structures used to represent stored information. |
| Buffer manager | Fetches disk blocks into main memory and decides which to keep. The single most important component for performance, because it determines how often a slow disk read must happen. |
Structures on disk¶
- Data files — the stored database itself.
- Data dictionary (catalog) — the metadata: schemas, constraints, users, privileges.
- Indexes — access structures for fast lookup (Unit 6).
- Statistics — row counts, value distributions, used by the optimiser.
- Log — the write-ahead record of every change, used for recovery.
4.2 Database System Architectures¶
Centralised architecture¶
Everything — DBMS, application, user interface — runs on one machine, a mainframe, with users at "dumb" terminals that only display characters.
Advantages: simple to administer; no network issues. Disadvantages: no scalability; the mainframe is a single point of failure and a bottleneck; expensive.
Terminal Terminal Terminal (display only)
\ | /
\ | /
+--v-------v-------v---+
| MAINFRAME |
| UI + Application |
| + DBMS + Data |
+----------------------+
Client/server architecture¶
Falling hardware prices made intelligent workstations possible. Function is split between a server that provides a service and clients that consume it.
Two-tier client/server
Tier 1 — Client: the user interface and the application logic run on the user's machine. The client connects to the DBMS through an API such as ODBC or JDBC and sends SQL.
Tier 2 — Server: the DBMS, holding the data and executing the queries.
+-------------+ +-------------+ +-------------+
| CLIENT 1 | | CLIENT 2 | | CLIENT 3 |
| UI + logic | | UI + logic | | UI + logic |
+------+------+ +------+------+ +------+------+
| SQL over ODBC/JDBC | |
+----------+-----------+------------+
|
+---------v----------+
| DATABASE SERVER |
| DBMS + data |
+--------------------+
Advantages: shares the load; clients can be heterogeneous; cheaper than a mainframe. Disadvantages ("fat client" problems): business logic is duplicated on every client, so a rule change means redeploying every client; every client needs a direct database connection and therefore database credentials, which is a security risk; connections do not scale to thousands of users.
Three-tier architecture
Tier 1 — Presentation (client): the browser or thin app. Displays and collects data only.
Tier 2 — Application / business-logic tier (middle tier): the web or application server. Holds all business rules, validates requests, and is the only tier that talks to the database. Also called the web server tier.
Tier 3 — Database tier: the DBMS.
+----------+ +----------+ +----------+
| BROWSER | | BROWSER | | MOBILE | TIER 1 presentation
+-----+----+ +-----+----+ +-----+----+
| HTTP / HTTPS | |
+-------+-------+-----------+
|
+--------v---------+
| APPLICATION | TIER 2 business logic
| SERVER |
| business rules, |
| session, security|
+--------+---------+
| SQL (pooled connections)
+--------v---------+
| DATABASE SERVER | TIER 3 data
+------------------+
Advantages: business logic lives in one place, so a rule change is deployed once; clients never hold database credentials, and the middle tier is a security firewall; connection pooling lets thousands of users share a few database connections; each tier scales independently.
Disadvantages: more complex to build and deploy; the extra network hop adds latency.
An n-tier architecture simply subdivides the middle tier further (web server, application server, integration layer).
Comparison¶
| Centralised | Two-tier | Three-tier | |
|---|---|---|---|
| Where is UI? | Server | Client | Client |
| Where is business logic? | Server | Client (fat) | Middle tier |
| Where is data? | Server | Server | Database tier |
| Scalability | Poor | Moderate | High |
| Security | Physical only | Weak — clients hold DB credentials | Strong — middle tier isolates the DB |
| Maintenance | Central, easy | Hard — redeploy every client | Easy — redeploy the middle tier |
| Typical example | 1970s mainframe | A desktop app with a MySQL connection | Any modern web application |
4.3 Database Users¶
Actors on the scene (they use the database daily)¶
Database Administrator (DBA). Responsible for the database system as a whole. Duties:
- Schema definition and schema evolution.
- Storage structure and access-method definition (physical design, indexes).
- Granting and revoking authorisation — deciding who may do what.
- Routine maintenance: backups, monitoring free space, upgrades.
- Performance tuning, and acquisition of software and hardware.
- Liaison with users; enforcement of standards and documentation.
Database Designers. Identify the data to be stored and choose the structures. They interview each user group, reconcile their conflicting requirements, and produce the conceptual schema and then the external schemas. Their work is Units 2 and 5 of this course.
End Users. People who access the database for querying, updating and reports. Four sub-types:
| Sub-type | Description |
|---|---|
| Casual | Occasional users, usually middle or high-level managers, who need different information each time and use a query language or a report generator. |
| Naive / parametric | The largest group. They constantly query and update using standard canned transactions through forms and menus — bank tellers, reservation clerks, admission counter staff. They need no knowledge of the database. |
| Sophisticated | Engineers, scientists, business analysts. They learn the DBMS facilities thoroughly and write their own complex queries. |
| Stand-alone | Maintain a personal database using a ready-made package with a graphical interface (e.g. a personal accounts application). |
System Analysts and Application Programmers (Software Engineers). Analysts determine the requirements of naive users and specify the canned transactions; programmers implement, test and document them.
Workers behind the scene (they build the DBMS, not the database)¶
- DBMS system designers and implementers — who write the DBMS software modules of Section 4.1.
- Tool developers — who build design tools, performance monitors, test-data generators, interfaces.
- Operators and maintenance personnel — who run and maintain the hardware and software environment.
Frequently confused
The DBA administers a database system that already exists; the database designer decides what that database should contain; the DBMS implementer writes the DBMS software product itself. An examination answer that blurs these loses marks.
4.4 Database Languages and Interfaces¶
Languages¶
| Language | Full form | Purpose | Typical statements |
|---|---|---|---|
| DDL | Data Definition Language | Define and modify the schema | CREATE, ALTER, DROP, TRUNCATE, RENAME |
| DML | Data Manipulation Language | Retrieve and change the data | SELECT, INSERT, UPDATE, DELETE |
| DCL | Data Control Language | Control access | GRANT, REVOKE |
| TCL | Transaction Control Language | Delimit transactions | COMMIT, ROLLBACK, SAVEPOINT |
Two further terms appear in Elmasri and Navathe: SDL (Storage Definition Language) for the internal schema and VDL (View Definition Language) for external schemas. In SQL all of these are folded into one language.
Procedural vs Non-procedural DML
A high-level or non-procedural (declarative) DML — such as SQL — lets you specify what data is wanted in a single statement, leaving the how to the optimiser. It is set-at-a-time: one statement processes many rows.
A low-level or procedural DML — such as embedded record-at-a-time navigation — requires you to specify how to retrieve the data, looping over records one at a time. Relational algebra is procedural in this sense; relational calculus and SQL are declarative.
Interfaces¶
- Menu-based and forms-based interfaces, for naive users.
- Graphical user interfaces, often showing the schema diagrammatically.
- Natural-language interfaces, which map English-like requests onto queries.
- Speech input and output, used in enquiry systems.
- Interfaces for parametric users, with a small set of keystrokes for very high throughput.
- Interfaces for the DBA, providing privileged commands.
- APIs / call-level interfaces — ODBC, JDBC, and language connectors such as MySQL Connector/Python. These are covered in Unit 4.
4.5 Solved Questions¶
Solved Question 1
Q. Draw and explain the component modules of a DBMS.
A. (Diagram: reproduce Figure 4.1.) The modules fall into three groups.
Query processor — the DDL interpreter records definitions in the catalog; the DML compiler parses and type-checks queries against the catalog; the query optimiser transforms the query into the cheapest equivalent execution plan using stored statistics; the query evaluation engine executes that plan.
Storage manager — the authorisation and integrity manager checks privileges and constraints; the transaction manager provides atomicity and isolation via its concurrency-control and recovery sub-managers; the file manager allocates disk space and manages the stored data structures; the buffer manager moves blocks between disk and main memory and decides what to keep in memory.
Disk structures — data files, the data dictionary (catalog), indexes, statistics, and the log.
The essential flow: a query enters the query processor, is parsed, optimised and executed; execution issues requests to the storage manager, which checks authorisation, takes locks, and asks the buffer manager for the required pages; results flow back up.
Solved Question 2
Q. Compare two-tier and three-tier client/server architectures. Why is three-tier preferred for web applications?
A. (Table from Section 4.2.3.)
In two-tier, the client holds both the presentation and the business logic and connects directly to the DBMS. In three-tier, the business logic is moved into a middle application-server tier, which alone talks to the database.
Three-tier is preferred for web applications for four reasons:
- Security. In two-tier every client holds database credentials and needs a direct route to the database server — impossible to expose safely over the internet. In three-tier the database sits behind the middle tier; a hostile browser never reaches it. The middle tier acts as a firewall.
- Maintainability. A change to a business rule is deployed once, on the application server, rather than redeployed to thousands of clients.
- Scalability. A database supports a limited number of concurrent connections. The middle tier uses connection pooling so that thousands of web users share a small pool, and additional application servers can be added behind a load balancer.
- Thin clients. Users need only a browser, so the system runs on any device with no installation.
The costs are greater architectural complexity and one extra network hop of latency.
Solved Question 3
Q. List the functions of a Database Administrator.
A.
- Schema definition — creating the original conceptual schema with DDL.
- Storage structure and access-method definition — the internal schema: file organisations, indexes, tablespaces.
- Schema and physical organisation modification — schema evolution, and tuning storage as workloads change.
- Granting authorisation for data access —
CREATE USER,CREATE ROLE,GRANT,REVOKE, so each user reaches only what they should. - Routine maintenance — periodic backups, monitoring disk space, applying patches and upgrades.
- Performance monitoring and tuning — watching slow queries, adding or dropping indexes, adjusting buffer sizes.
- Ensuring integrity, security and recovery — defining constraints, planning and testing disaster recovery.
- Acquisition of software and hardware resources as requirements grow.
- Contact with users and enforcement of standards for naming, documentation and design.
Solved Question 4
Q. Classify each of the following users and justify: (a) a bank teller entering deposits; (b) a market analyst writing complex SQL to study sales trends; (c) a person maintaining household accounts in a ready-made package; (d) a manager who occasionally asks for a one-off report; (e) a person who wrote the MySQL optimiser.
A.
| Category | Justification | |
|---|---|---|
| a | Naive / parametric end user | Repeatedly executes a small set of canned transactions through a form; needs no knowledge of the database structure. |
| b | Sophisticated end user | Familiar with the DBMS's facilities and writes complex ad-hoc queries directly, without a programmer's help. |
| c | Stand-alone user | Maintains a personal database with a ready-made package and a graphical interface. |
| d | Casual end user | Accesses the database occasionally and needs different information each time, using a query language or report generator. |
| e | Worker behind the scene — a DBMS system implementer | Builds the DBMS software product, not any particular database. |
Solved Question 5
Q. Distinguish DDL, DML, DCL and TCL, giving two statements of each and stating who typically uses it.
A.
| Purpose | Statements | Typical user | Level affected | |
|---|---|---|---|---|
| DDL | Define / change the schema | CREATE TABLE, ALTER TABLE |
DBA, designer | Conceptual (and internal, via storage clauses) |
| DML | Retrieve / change the data | SELECT, UPDATE |
End users, application programmers | Instance |
| DCL | Control access | GRANT, REVOKE |
DBA | Authorisation catalog |
| TCL | Delimit transactions | COMMIT, ROLLBACK |
Application programmers | Transaction boundaries |
A further useful distinction is within DML: SQL is a high-level, non-procedural, set-at-a-time DML — you state what you want and the optimiser decides how, and one statement affects many rows. A low-level, procedural, record-at-a-time DML would require you to loop, retrieving one record per call.
Solved Question 6
Q. What is the role of the buffer manager, and why is it critical to performance?
A. The buffer manager is the storage-manager component responsible for moving disk blocks (pages) into a region of main memory called the buffer pool, and for deciding which pages to retain and which to evict when space is needed.
It is critical because of the enormous speed gap between main memory and disk — a memory access takes tens of nanoseconds, a disk access milliseconds, a difference of roughly five orders of magnitude. The number of disk accesses therefore dominates the cost of almost every database operation.
The buffer manager's job is to make the number of physical reads as small as possible. When a query needs a page it first checks the buffer pool; a hit costs nothing extra, a miss costs a disk read. By choosing a good replacement policy (LRU, clock, or policies aware of the access pattern such as sequential scans) and by pinning pages that are in use, the buffer manager raises the hit ratio. A few percentage points of hit ratio can change overall throughput several-fold.
It also cooperates with the recovery manager: under write-ahead logging a dirty page must not be written to disk before the corresponding log record, so the buffer manager must respect the log's ordering constraints before evicting a modified page.
4.6 Exercises¶
A. Short answer
- Name the four components of the storage manager.
- What does the query optimiser do, and what information does it use?
- Define canned transaction, and say which user category uses them.
- What is the difference between a DBA and a database designer?
- Expand ODBC and JDBC, and say which architectural tier uses them.
- Give two examples each of DDL, DML, DCL and TCL statements.
- What is stored in the log file, and which component uses it?
B. Long answer
- Draw the component diagram of a DBMS and explain the function of each module.
- Explain the centralised, two-tier and three-tier architectures with diagrams. Compare them on scalability, security and maintainability.
- Describe all the categories of database users, distinguishing actors on the scene from workers behind the scene.
- Explain the function of the buffer manager and the transaction manager, and describe how they must cooperate for recovery to work.
- Classify database languages and interfaces. Explain the difference between procedural and non-procedural DML with an example of each.
C. Application and analytical
- A college wishes to put its admission system online for 5,000 simultaneous applicants. Recommend an architecture, draw it, and justify your choice on security, scalability and maintainability. State one disadvantage of your recommendation.
- For each task, name the DBMS component chiefly responsible:
a. Deciding to use the index on
Cnamerather than scanning the table. b. Refusing anINSERTwhoseSIDdoes not exist inCOURSE. c. Undoing a half-finished transfer after a power failure. d. Keeping the most frequently used pages in memory. e. Recording thatSTUDENT.RollnoisVARCHAR(8). f. Preventing two clerks from booking the same last seat. - A two-tier desktop application is being migrated to three tiers. List the specific changes required, and name three problems the migration solves.
- Which user categories exist in a railway reservation system? Name each, and give the actual person and the interface they would use.
- Explain why an optimiser is unnecessary in a procedural, record-at-a-time DML but essential in SQL.
D. Objective — True or False with reason
- The query optimiser guarantees the fastest possible execution plan.
- In three-tier architecture the browser connects directly to the database.
- The DBA writes the DBMS software.
GRANTis a DDL statement.- The buffer manager is part of the query processor.
- Naive end users must know SQL.
Answers to Section D
- False. It chooses the cheapest plan according to its cost estimates, which are based on approximate statistics. A better plan may exist; the optimiser only aims to avoid disastrous ones cheaply.
- False. The browser talks to the middle tier over HTTP; only the middle tier issues SQL to the database.
- False. The DBA administers a particular database system. DBMS software is written by system implementers — workers behind the scene.
- False.
GRANTis DCL. (Some textbooks group DCL under a broad definition of DDL; state the standard four-way classification and note the exception.) - False. It is part of the storage manager.
- False. They work through forms and menus that invoke canned transactions; they need no knowledge of SQL or of the schema.
Lesson Summary
- The query processor (DDL interpreter, DML compiler, optimiser, evaluation engine) turns declarative requests into plans; the storage manager (authorisation and integrity, transaction, file, buffer managers) executes them safely against disk.
- Centralised gave way to two-tier client/server and then to three-tier, which puts business logic in a middle tier for security, maintainability and scalability.
- Users: DBA, designers, end users (casual, naive, sophisticated, stand-alone), analysts and programmers — plus the workers behind the scene who build the DBMS itself.
- Languages: DDL, DML, DCL, TCL; SQL is a high-level, non-procedural, set-at-a-time DML.
End of Unit 1. Next: Unit 2, Lesson 1 — Conceptual Data Modelling: Entities and Entity Types.