2: DBMS Architecture & Classification

What are the main components of a DBMS?

Connection manager, security manager, DDL compiler, query processor, storage manager, DBMS utilities, and DBMS interfaces.

What does the connection manager do?

Establishes a local or network connection, verifies logon credentials, and returns a connection handle.

How may a database connection be handled?

By a separate process or a thread within a process.

What does the security manager do?

Checks whether a user has permission to perform an operation, such as reading, writing, or deleting data.

How does the connection manager differ from the security manager?

The connection manager establishes and authenticates a connection; the security manager checks permission for specific operations.

Does successfully logging in authorize a user to perform every database operation?

No. Authentication establishes identity, while authorization determines permitted operations.

A user can log in but cannot delete records. Which component checks the delete permission?

The security manager.

What does the DDL compiler process?

Definitions of database structures, not the business records themselves.

What are the main steps of DDL compilation?

Parse definitions, check syntactical correctness, translate them into an internal format, and register successful definitions in the catalog.

Where does the DDL compiler register successfully compiled definitions?

In the catalog as metadata.

Which models should ideally have DDL support according to the chapter?

External, logical, and internal models.

What does the query processor do?

Processes requests to retrieve, insert, update, or delete data.

What does the storage manager do?

Controls physical file access and the correct, efficient storage of data.

What is procedural DML?

DML that specifies how to navigate and access data, typically using record-at-a-time operations.

What is declarative DML?

DML that specifies what data or changes are wanted, leaving the execution strategy to the DBMS.

What is the key difference between procedural and declarative DML?

Procedural DML specifies how to perform an operation; declarative DML specifies the desired result.

Which processing style is associated with procedural DML?

Record-at-a-time processing.

Which processing style is associated with declarative DML?

Set-at-a-time processing.

Which database models are associated with procedural DML in this chapter?

Hierarchical and network models.

Which database model is associated with declarative SQL?

The relational model.

Do classic hierarchical and network systems use a query processor in the model described by the slides?

No. Their procedural DML specifies record navigation directly.

Why do declarative systems use a query processor?

To determine how to execute requests that specify the desired result rather than the navigation procedure.

Does SQL become procedural DML when embedded in Java?

No. The SQL statement remains declarative even if the surrounding Java code uses loops or other procedural logic.

What does the DML compiler do?

Compiles DML statements, extracts them from a host language where applicable, and coordinates their processing.

What is the central sequence of query-processing stages?

Parse → Rewrite → Optimize → Execute.

What does the query parser do?

Checks syntax and semantics and converts the query into an internal representation.

What is a query syntax check?

A check that the query follows the language’s grammar.

What is a query semantics check?

A check that the query makes sense, such as verifying that referenced tables and columns exist.

A query refers to a nonexistent column. What kind of check detects the problem?

A semantic check performed by the query parser.

A query contains incorrectly ordered SQL keywords. What kind of check detects the problem?

A syntax check performed by the query parser.

What does the query rewriter do?

Transforms the query to improve it independently of the current database state.

What does the query optimizer do?

Evaluates execution plans using database information and estimated costs, then chooses a final execution plan.

What is the key difference between the query rewriter and query optimizer?

The rewriter transforms queries independently of current database state; the optimizer selects an execution strategy using database information.

What information does the optimizer use?

Catalog information, available indexes, statistics, and statistical inference.

What costs may the query optimizer estimate?

The number of I/O operations, CPU processing cost, and execution time.

What is the output of the query optimizer?

A final execution plan.

What does the query executor do?

Runs the chosen execution plan and calls the storage manager for data access.

What is the difference between the optimizer and executor?

The optimizer chooses the execution plan; the executor runs it.

Two plans return the same result, but one scans a table and the other uses an index. Which component compares them?

The query optimizer.

Does an available index guarantee that an index-based plan is best?

No. Its usefulness depends on the query, database information, and estimated costs.

Which query-processing component directly requests data access from the storage manager?

The query executor.

What is impedance mismatch?

A difference between how a programming language represents data and how a database represents it.

How do classes, objects, and object references roughly correspond to relational structures?

Classes correspond to tables, objects to rows, and object references to foreign keys, although the mappings are not always exact.

Is impedance mismatch a network connection problem?

No. It is a data-representation mismatch between a programming language and a database.

What are the two solutions to impedance mismatch described in the chapter?

Use a host language and DBMS with comparable structures, or use middleware to map between their representations.

How can an object-oriented language and an object-oriented DBMS reduce impedance mismatch?

They use comparable object-oriented data structures, reducing the need to translate objects into relational structures.

A Java application uses objects and references, while its database uses rows and foreign keys. What problem can arise?

Impedance mismatch.

What software approach can address object-relational impedance mismatch?

Mapping middleware, such as an object-relational mapping (ORM) layer.

What are the four components of the storage manager?

Transaction manager, buffer manager, lock manager, and recovery manager.

What does the transaction manager do?

Supervises transactions, schedules interleaved operations, and coordinates ACID guarantees.

What is a transaction?

A sequence of read/write operations treated as one atomic unit.

What does COMMIT do?

Successfully completes a transaction and makes its changes permanent.

What does ROLLBACK do?

Undoes a transaction’s changes after failure or cancellation.

What is the difference between COMMIT and ROLLBACK?

COMMIT preserves a successfully completed transaction’s changes; ROLLBACK undoes its changes.

Which ACID property is directly illustrated by undoing a partially completed transaction?

Atomicity, because a transaction must take effect completely or not at all.

What are interleaved transaction operations?

Operations from multiple transactions mixed in a schedule while preserving correct behavior.

What does ACID stand for?

Atomicity, Consistency, Isolation, and Durability.

What is atomicity?

All operations of a transaction take effect, or none do.

What is consistency in ACID?

A transaction moves the database from one valid state to another while respecting integrity rules.

What is isolation in ACID?

Concurrent transactions should have the same effect as if executed in isolation.

What is durability?

Once a transaction succeeds, its changes persist, including through failures.

What does the buffer manager do?

Manages data cached in memory and decides what to replace when the buffer is full.

Why does a DBMS cache data in memory?

To reduce slower access to persistent storage.

What is data locality as described in this chapter?

The tendency for recently accessed data to be accessed again.

What is the 20/80 law in database buffering?

A heuristic suggesting that a large proportion of transactions access a relatively small portion of the data.

Is the 20/80 law guaranteed for every workload?

No. It is a useful heuristic, not a universal rule.

Why do data locality and the 20/80 heuristic support caching?

They suggest that keeping frequently reused data in memory can avoid many slower storage accesses.

What is a buffer replacement strategy?

A method for deciding which cached data to remove when the buffer is full.

Which component chooses what cached data to replace when memory is full?

The buffer manager.

What does the lock manager do?

Coordinates concurrent read/write access by assigning, tracking, and releasing locks.

What types of locks are discussed in this chapter?

Read locks and write locks.

What is a locking protocol?

The rules that govern how locks are acquired, used, and released.

What is a lock table?

A structure that records information about locks.

How does a locking protocol differ from a lock table?

The protocol defines locking rules; the table records lock information.

What does the recovery manager do?

Logs database operations and supports undoing aborted transactions and recovering after crashes.

Where does the recovery manager record database operations?

In a log file.

When is recovery information needed?

When a transaction aborts or a crash occurs.

A transaction modifies several records but fails before completion. Which component uses logged operations to support undo?

The recovery manager.

What is the transaction-level action that undoes a failed transaction’s changes?

ROLLBACK.

How do the transaction manager and recovery manager differ?

The transaction manager coordinates transaction execution; the recovery manager uses logs and recovery information to support undo and crash recovery.

How do the buffer manager and lock manager differ?

The buffer manager manages cached data; the lock manager controls concurrent access through locks.

What are DBMS utilities?

Tools that perform supporting tasks such as loading, reorganization, performance monitoring, user management, backup, and recovery.

What does a loading utility do?

Brings data into the database.

What does a reorganization utility do?

Rearranges stored data.

What does a performance-monitoring utility do?

Tracks database performance.

What does a user-management utility do?

Manages user accounts and privileges.

What are DBMS interfaces?

Ways for people or applications to interact with the database system.

What are examples of DBMS interfaces?

Web-based, stand-alone query language, command-line, forms-based, graphical, natural-language, administrative, and network interfaces.

What is the difference between a utility and an interface?

A utility performs a supporting task; an interface provides a way to interact with the system.

What are the major DBMS data-model categories in this chapter?

Hierarchical, network/CODASYL, relational, object-oriented, object-relational, XML, and NoSQL.

What characterizes a hierarchical DBMS?

A tree-like data model, procedural record-oriented DML, and intertwined logical and internal models.

What is an example of a hierarchical DBMS from the slides?

IBM IMS.

What characterizes a network/CODASYL DBMS?

A network data model, procedural record-oriented DML, and intertwined logical and internal models.

What is an example of a network DBMS from the slides?

CA-IDMS.

What is the main difference between hierarchical and network data models?

Hierarchical models use a tree structure; network models use a network structure.

What characterizes a relational DBMS?

A relational model, declarative set-oriented SQL, a query processor, and separation of logical and internal models.

What are examples of relational DBMSs from the slides?

MySQL, Oracle, DB2, and Microsoft SQL Server.

How do hierarchical/network and relational DBMSs differ in DML style?

Hierarchical/network systems use procedural DML; relational systems use declarative SQL.

How do hierarchical/network and relational DBMSs differ in processing style?

Hierarchical/network systems use record-at-a-time processing; relational systems use set-at-a-time processing.

How do hierarchical/network and relational DBMSs differ in model separation?

Hierarchical/network systems intertwine logical and internal models; relational systems separate them.