1 Database Management

What is a database?

A collection of related data within a particular business process or problem setting, such as a university’s student and course data.

What is a DBMS?

A Database Management System: software used to define, create, use, and maintain a database.

What is a database system?

The combination of a database and the DBMS that manages it.

How is a database different from a DBMS?

The database is the stored collection of related data; the DBMS is the software that manages that data.

What are examples of applications of database technology?

Traditional business records, multimedia, biometrics, wearables, GIS, sensors, big data, and the Internet of Things (IoT).

What are the main weaknesses of the file approach?

Duplicate and inconsistent data, tight coupling between applications and file formats, difficult concurrent updates, and difficult application integration.

How does the database approach improve on the file approach?

It centrally manages redundancy and consistency, reduces coupling between applications and data formats, provides concurrency control, and supports shared data and querying.

Does the database approach eliminate all data duplication?

No. It manages duplication and keeps copies consistent rather than necessarily eliminating duplication.

Why might a database intentionally duplicate data?

To improve retrieval performance, including in distributed environments. The DBMS must coordinate and synchronize copies to maintain consistency.

What does tight coupling between applications and data formats mean?

Applications depend on specific data formats, so changing those formats may require changing application code.

What is a database schema?

The description of a database, including its data items, relationships, constraints, and storage details.

What is a database state?

The actual data stored in a database at a particular moment.

Which changes more frequently: schema or state?

State changes frequently as records are inserted, updated, or deleted. Schema changes relatively infrequently.

Where is the database schema stored?

In the catalog as metadata.

Does defining Student(number, name, address, email) describe the schema or the state?

The schema, because it defines the structure rather than the current records.

Does updating a student’s email change the schema or the state?

The state, because it changes a stored value without changing the structure.

Does inserting a new student change the schema or the state?

The state, because it adds a record to an existing structure.

Does adding a phone field to Student change the schema or the state?

The schema, because it changes the database’s structure.

Does changing a field’s data type change the schema or the state?

The schema, because the field’s definition changes.

What are the four data models described in this chapter?

Conceptual, logical, internal, and external.

What does the conceptual model describe?

A high-level, implementation-independent business description of data and relationships.

Which model is designed to be understandable to business users and independent of implementation?

The conceptual model.

What are examples of representations used for conceptual models?

Enhanced Entity-Relationship (EER) models and object-oriented models.

What does the logical model describe?

A mapping of the conceptual model to an implementation environment, such as a relational or NoSQL model.

What does the internal model describe?

Physical storage details, including where and how data is stored, storage formats, and indexes. It is highly DBMS-specific.

What does the external model describe?

User- or application-specific views containing relevant subsets of the logical model.

A sales team sees only the customer information it needs. Which model does this illustrate?

The external model, because it provides a view tailored to a specific group.

What are the three levels of the three-layer database architecture?

External, conceptual/logical, and internal.

How do the four model descriptions relate to the three-layer architecture?

The architecture has external, conceptual/logical, and internal levels; conceptual and logical are grouped in the middle architectural level.

What is physical data independence?

The ability to change physical storage without requiring changes to the logical model, external views, or applications.

A DBA adds an index without changing views or application code. What does this demonstrate?

Physical data independence, because a physical storage detail changes without affecting higher levels.

What is logical data independence?

The ability to change the conceptual/logical structure with minimal impact on applications.

The logical structure changes, but applications still work through compatible views. What does this demonstrate?

Logical data independence. External views protect applications from changes to the underlying structure.

What is the key difference between physical and logical data independence?

Physical independence concerns storage changes; logical independence concerns conceptual/logical structure changes.

What is the database catalog?

The DBMS’s metadata repository, which stores information about the database’s definitions.

What is metadata?

Data about data, such as definitions of tables, fields, views, and rules.

What kinds of information does the catalog store?

View definitions, logical and internal model definitions, integrity rules, and authorization rules.

How does the catalog differ from ordinary business data?

The catalog describes database structures and rules; business data contains the actual records, such as customers and invoices.

How does the catalog support database models?

It helps keep the model definitions consistent.

What is the main responsibility of an information architect?

Designing the conceptual model in collaboration with business users.

What is the main responsibility of a database designer?

Translating the conceptual model into logical and internal models.

What is the main responsibility of a DBA?

Implementing and monitoring the database.

What is the main responsibility of an application developer?

Building applications that interact with the database.

What is the main responsibility of a business user?

Using database applications to perform business tasks and database operations.

What should be documented about a database model?

Its assumptions and shortcomings.

What does DDL stand for, and what does it do?

Data Definition Language. It defines database structures and models, with definitions stored in the catalog.

What does DML stand for, and what does it do?

Data Manipulation Language. It retrieves, inserts, deletes, and modifies data.

What is the difference between DDL and DML?

DDL defines database structures; DML works with the data stored in those structures.

Does SQL provide DDL, DML, or both?

Both. SQL provides structure-definition and data-manipulation capabilities for relational databases.

According to this chapter, is SELECT classified as DDL or DML?

DML, because retrieving data is classified as data manipulation.

Is defining a table or a view a DDL or DML task?

DDL, because it defines a database structure.

Is inserting, updating, or deleting a record a DDL or DML task?

DML, because it changes data rather than defining its structure.

What does SELECT * FROM Customer WHERE name = 'Bart'; return?

All columns from every row in Customer whose name equals 'Bart'.

What does the asterisk (*) mean in SELECT *?

Return all columns.

Does WHERE name = 'Bart' necessarily identify exactly one customer?

No. The query returns every matching row, so multiple customers named Bart may be returned.

What is structured data?

Data that follows a formal logical model with identifiable fields, such as student number, name, address, and email.

What is semi-structured data?

Data with some recognizable structure that may be irregular or change frequently, such as résumés with varying sections or social-media webpages.

What is unstructured data?

Data that the system does not organize into finer-grained, meaningfully interpretable fields, such as a document containing biographies.

Does unstructured data mean meaningless data?

No. It describes how the system organizes and interprets the content, not whether the content has meaning.

What is a syntactical integrity rule?

A rule about representation, format, or data type.

What is a semantical integrity rule?

A rule about meaning or business correctness.

“Customer ID must be an integer” is what kind of integrity rule?

Syntactical, because it specifies a data type.

“Customer ID must be unique” is what kind of integrity rule?

Semantical, because it specifies correctness rather than representation.

“A customer with pending invoices cannot be deleted” is what kind of integrity rule?

Semantical, because it enforces a business requirement.

Where are integrity rules defined, stored, and enforced?

They are defined as part of the conceptual/logical model, stored in the catalog, and enforced by the DBMS.

What is a transaction?

A sequence of read/write operations treated as one atomic unit: all operations take effect, or none do.

What is concurrency control?

The coordination of simultaneous transactions to prevent inconsistencies.

What is a lost update?

A problem in which one transaction overwrites another transaction’s update, often because both read the same old value.

What does ACID stand for?

Atomicity, Consistency, Isolation, and Durability.

What is atomicity?

All operations in a transaction succeed, or none take effect.

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 is declared successful, its changes persist, including through failures.

What is the difference between atomicity and durability?

Atomicity prevents partial transactions; durability preserves the changes of successful transactions.

A transfer deducts money from account A but fails before crediting account B. Which ACID property requires undoing the partial transfer?

Atomicity, because the transfer must happen completely or not at all.

A confirmed address update must survive a server crash. Which ACID property applies?

Durability, because successful changes must persist through failures.

A transaction must preserve the database’s integrity rules. Which ACID property applies?

Consistency, because the database must move from one valid state to another.

Concurrent transactions must behave as though they ran separately. Which ACID property applies?

Isolation.

An account has $100. T1 and T2 both read $100. T1 adds $20 and writes $120; T2 adds $50 and writes $150. What should the final balance be?

$170, because both additions should be preserved: $100 + $20 + $50.

T1 writes $120 after adding $20 to $100, but T2 then writes $150 using the original $100. What balance is stored, and what went wrong?

$150 is stored. A lost update occurred because T2 overwrote T1’s $20 addition using an outdated balance.

What is a full backup?

A copy of all data within the backup’s scope.

What is an incremental backup?

A copy of changes made since the previous backup.

What is recovery?

Restoring data after loss or damage.

How do backup and recovery differ?

Backup creates copies of data; recovery uses available recovery resources to restore data after loss or damage.

What mechanisms support database security?

User accounts, authentication, and authorization rules controlling actions such as reading and writing.

What is the difference between authentication and authorization?

Authentication verifies who a user is; authorization determines what that user is allowed to do.

“Analysts can read records but cannot modify them” illustrates what concept?

Authorization or access control.

What are the three database performance KPIs in this chapter?

Response time, throughput, and space utilization.

What is response time?

The time from issuing a request to its successful completion. Lower response time is generally desirable.

What is throughput?

The number of transactions processed per unit of time. Higher throughput is generally desirable.

What is space utilization?

The storage used for raw data and metadata. Efficient use is generally desirable.

“A request completed in 0.4 seconds” measures which KPI?

Response time.

“The DBMS processed 900 transactions per second” measures which KPI?

Throughput.

“The database uses 80 GB for records and metadata” measures which KPI?

Space utilization.

What can database performance utilities optimize?

Storage, indexes, queries, and buffer management.