Book summary “Database systems”: summary

Introduction

You face a constant struggle to transform raw enterprise data into reliable business intelligence. Traditional file systems trap information in isolated silos, causing structural dependence, data redundancy, and severe anomalies. You need a structured approach to manage vast data volumes while ensuring absolute integrity and security.

Proper database design and robust management principles are essential to prevent system failures, ensure data integrity, and transform raw data into valuable business intelligence.

Peter Rob provides a comprehensive guide through data modeling, relational theory, advanced query languages, and transaction management. You will learn how to build resilient systems that support critical organizational decisions.

• How do you transition from file systems to robust database management systems?
• What rules govern relational tables and structural integrity?
• How do you eliminate redundancies through normalization and transaction control?

1. Database Systems and File Processing

Transitioning from traditional file systems to database management systems eliminates structural dependence and data anomalies while securing data integrity.

Data consists of raw, unprocessed facts, whereas information is the result of processing data to reveal meaning within a specific context. Raw facts require proper formatting for storage and presentation. Information provides context for decision-making and acts as the bedrock for knowledge.

A university tracking faculty records enters raw data into a software screen such as Sedona. That input transforms into a data summary or pie chart showing that 20 percent of the faculty in the Information Systems department are adjuncts.

Data versus Information

Metadata describes the data characteristics and relationships that make data integration and administration possible. You work with end-user data and metadata every time you issue an ad hoc query and inspect a query result set.

Limitations of File Systems

Manual and computerized file systems require dedicated management programs for each file. You face lengthy development times and complex administration when maintaining separate files. Changing a single field requires reading each record and transforming the data to conform to the new structure. You must repeat that process for every record in the file.

File systems exhibit structural dependence and data dependence.

• Structural dependence forces you to rewrite application programs when a file structure changes.
• Data dependence forces programs to specify both what data to retrieve and the physical format of how the computer must work with the data.

Uncontrolled data redundancy creates islands of information. When you store the same data in multiple places, you risk data inconsistency and data anomalies.

• Update anomalies occur when you change data in one place and forget another.
• Insertion anomalies happen when you record facts about one entity but inadvertently create requirements for another.
• Deletion anomalies occur when deleting a record destroys unintended facts.

Database Management System Functions

A database management system acts as an intermediary between users and the database, providing a single integrated view while hiding internal complexity. Telecommunications companies like Sprint and AT&T manage trillions of phone calls with new data added at speeds up to 70,000 calls per second. Google responds to over 91 million searches per day using specialized database structures.

The DBMS accepts application requests and translates them into complex operations. You rely on the system to manage multiuser access, improve security, and minimize inconsistency.

A modern DBMS provides automated functions to guarantee integrity:

• data dictionary management;
• data storage management and performance tuning;
• data transformation and presentation;
• security management;
• multiuser access control;
• backup and recovery management;
• data integrity management;
• database access languages and application programming interfaces;
• database communication interfaces.

2. Data Modeling and Conceptual Design

Data models serve as communication tools and blueprints that translate business rules into structured entities, attributes, and relationships.

Data modeling is an iterative, progressive process. You start from a simple understanding of a problem domain and end with a narrative and graphical blueprint. An implementation-ready data model must contain data structure descriptions, enforceable integrity rules, and a data manipulation methodology.

A client reviewing a newly developed data model remarked that it was the first time they truly understood how all pieces of their business fit together after years of working in it.

Building Blocks and Business Rules

All data models are constructed using four basic building blocks. You work with entities, attributes, relationships, and constraints. An entity represents a distinguishable real-world or abstract object about which data is collected. Attributes are characteristics of entities corresponding to file system fields. Relationships describe associations between entities. Constraints are restrictions placed on data to ensure integrity.

A painter creates many different paintings, forming a one-to-many relationship where the painter is the one side and paintings represent the many side.

Business rules are brief, precise descriptions of organizational policies and procedures. You derive them from company documentation, managers, and end-user interviews to standardize an organization's view of data.

To translate business rules into data model components, you follow specific guidelines:

• Gain a thorough understanding of existing organizational data, usage, and time frames.
• Verify end-user perceptions to reconcile differing opinions about job components.
• Translate nouns in business rules into entities and verbs into relationships.
• Ask how many instances of B relate to one instance of A and vice versa to identify relationship types.

A training session constraint specifies that it cannot be scheduled for fewer than 10 employees or for more than 30 employees.

Evolution of Data Models

Introduced by E.F. Codd in 1970, the relational model revolutionized databases by presenting data as logical tables linked through shared attributes. The entity relationship model, introduced by Peter Chen in 1976, uses graphical diagrams to visually represent entities, attributes, and relationships. The object-oriented data model encapsulates data, relationships, and operational procedures into single structures known as objects. NoSQL databases emerged to handle Big Data challenges by supporting distributed architectures and high scalability at the expense of strict transaction consistency.

Customer tables and agent tables are linked through a shared agent code attribute, allowing you to determine a customer's sales representative.

You interact with relational data through specific workflows:

• Interact with data through end-user interfaces that automatically generate SQL code.
• Store all data in independent tables related by common values in common attributes.
• Execute queries using the SQL engine to retrieve data without specifying physical access paths.

Degrees of Data Abstraction

The ANSI/SPARC architecture defines degrees of data abstraction to separate user views from database implementation details. You use external, conceptual, internal, and physical models to organize system complexity.

Tiny College maintains separate external schemas for student registration and class scheduling that share common entities like class and course.

You build and manage these abstraction layers using structured practices:

• Define external models using ER diagrams to capture specific business unit data requirements and security constraints.
• Create a conceptual model to integrate all external views into a global software- and hardware-independent blueprint.
• Map the conceptual model to a chosen DBMS using the internal model.
• Define physical storage media and access methods using the physical model.

3. The Relational Database Model

The relational model uses two-dimensional tables, keys, integrity rules, and relational algebra to govern data storage and manipulation securely.

E. F. Codd introduced the relational database model in 1970. It provides a logical view of data built upon predicate logic and set theory. Predicate logic allows assertions about data to be verified as true or false. Set theory deals with sets of things and serves as the foundation for data manipulation.

A student with ID 12345678 is named Melissa Sanduski, which can be verified as true or false using predicate logic.

Tables and Keys

A relational table is perceived as a two-dimensional structure composed of rows and columns. Each row represents a single entity occurrence, and each column represents an attribute with a distinct name and domain. Tables store entity sets where the order of rows and columns is immaterial to the DBMS. Each intersection contains a single data value conforming to a specific data format such as numeric, character, date, or logical.

The STUDENT table in the TinyCollege database contains eight rows and twelve columns, where STU_GPA is numeric and STU_CLASS is character data. The attribute domain for STU_GPA values is 0-4.

Keys in a relational model are based on functional dependence, where knowing the value of one attribute uniquely determines the value of another. A determinant or key functionally determines a dependent attribute.

You encounter several key types:

• Superkeys: uniquely identify any row.
• Candidate keys: minimal superkeys based on full functional dependence.
• Primary key: the chosen candidate key.
• Composite key: a key composed of multiple attributes.

In the STUDENT table, STU_NUM functionally determines STU_LNAME because a single last name is associated with any given student number.

Integrity Rules

Entity integrity and referential integrity rules ensure that every row has a unique identity and that all foreign key references remain valid. Entity integrity requires that all primary key entries are unique and no part of a primary key contains a null. Referential integrity requires that every non-null foreign key value matches an existing primary key value in a related table.

In the CUSTOMER and AGENT tables, customer Paul F. Olowski has a null entry in his AGENT_CODE foreign key because he has no sales representative assigned yet. You can use -99 as a dummy variable value as a flag to indicate the absence of an assigned agent.

Relational Algebra Operators

Relational algebra defines theoretical operators that manipulate table contents with the property of closure, producing new relations from existing ones.

The eight operators include SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE. Minimally relational DBMSs must support SELECT, PROJECT, and JOIN. A SELECT operation on a table yields a horizontal subset of rows satisfying a condition. A PROJECT operation yields a vertical subset of selected attributes. A PRODUCT operation combining a table of six rows and a table of three rows yields 6 x 3 = 18 rows.

Many-to-many relationships cannot be implemented directly in the relational model and must be converted into two one-to-many relationships using a composite entity. While 1:M relationships are implemented by placing the primary key of the one side into the many side as a foreign key, M:N relationships require a linking table containing at least the primary keys of the connected tables.

The M:N relationship between STUDENT and CLASS is broken down by creating an ENROLL composite entity whose primary key is the combination of CLASS_CODE and STU_NUM.

4. Entity Relationship Modeling and Normalization

Advanced entity relationship modeling and database normalization eliminate redundancies, prevent anomalies, and ensure high data quality.

ERD Notations and Relationship Strength

Attributes are characteristics of entities and are classified into various types such as required, optional, simple, composite, single-valued, multivalued, and derived, each constrained by a domain. A domain is the set of possible values for an attribute.

Connectivity describes the general relationship classification, while cardinality expresses the precise minimum and maximum number of associated entity occurrences. At Tiny College, the cardinality (1,4) next to the CLASS entity indicates that each professor teaches up to four classes.

Relationships are classified as weak (non-identifying) or strong (identifying) based on whether the primary key of the related child entity contains a primary key component of the parent entity. Implementing a many-to-many relationship in a relational database requires an associative or composite entity that bridges the parent entities.

• Decompose an M:N relationship into two 1:M relationships by creating an associative entity.
• Compose the associative entity's primary key using the primary keys of the connecting parent entities.

The extended entity relationship model introduces entity supertypes and subtypes to capture specialized attributes and relationships without generating unnecessary null values. An aviation business employs pilots who require special qualifications like flight hour restrictions and licenses, which would create unnecessary null values if stored in a general EMPLOYEE entity.

Database Normalization Stages

Unnormalized table structures create severe update, insertion, and deletion anomalies that compromise data integrity. Modifying the job classification for employee number 105 requires alterations for every occurrence in the table, while deleting the last employee on a project deletes the project information itself.

First normal form requires a tabular format with defined keys, no repeating groups, and all attributes dependent on the primary key. Second normal form requires the table to be in first normal form and free of partial dependencies.

• Eliminate the repeating groups by presenting data in a tabular format where each cell has a single value and nulls are removed.
• Identify the primary key that uniquely identifies each row.
• Identify all functional dependencies.
• Make new tables to eliminate partial dependencies by creating a table for each primary key component that acts as a determinant.
• Reassign corresponding dependent attributes from the original table to the new tables.

Higher-Level Normal Forms

Third normal form requires the table to be in second normal form and free of transitive dependencies. Denormalization introduces controlled data redundancy to reduce table joins and increase query processing speed.

• Make new tables to eliminate transitive dependencies by writing each determinant as a primary key for a new table.
• Reassign corresponding dependent attributes to the new tables and remove them from the original table.
• Analyze organizational priorities to balance design standards, processing speed, and information requirements.
• Document all design decisions, activities, and compromises in writing throughout the development process.

Creating a temporary denormalized FACHIST table from a normalized EVALDATA table to generate faculty evaluation reports efficiently. Storing invoice subtotals and taxes as derived attributes decreases real-time transaction speed slightly but significantly speeds up the generation of massive sales reports.

5. Structured Query Language Mastery

Structured Query Language provides nonprocedural commands for defining database structures, manipulating data, and executing complex multi-table queries.

Data Definition and Manipulation

You use Data Definition Language (DDL) to create database objects like tables and indexes, and Data Manipulation Language (DML) to insert, update, delete, and retrieve data. Being nonprocedural means you specify what must be done rather than how the system should execute it. You work with a basic command set vocabulary of fewer than 100 words across software such as Oracle, Microsoft SQL Server, MySQL, IBM DB2, or Microsoft Access.

Creating tables in SQL involves defining attributes, data types, and integrity constraints. Fixed-length CHAR data accepts 1 to 255 characters, while variable-length VARCHAR data accepts 1 to 2,000 characters. You enforce entity and referential integrity through specific constraints:

• Start the command with CREATE TABLE tablename followed by parentheses.
• Define each column name, its data type, and optional constraints.
• Specify table-level constraints such as PRIMARY KEY and FOREIGN KEY.
• End the command sequence with a semicolon.

Using software such as Oracle, Microsoft SQL Server, MySQL, IBM DB2, or Microsoft Access to execute SQL statements.

Data manipulation commands allow you to add, modify, delete, and retrieve rows within database tables. You can make your changes permanent or restore original values using transaction commands:

• Use INSERT INTO tablename VALUES to add a complete row of values.
• Use UPDATE tablename SET columnname = expression WHERE conditionlist to modify data.
• Use DELETE FROM tablename WHERE conditionlist to remove specific rows.
• Use COMMIT to permanently save changes or ROLLBACK to restore original values.

Inserting rows into the VENDOR table using INSERT INTO VENDOR VALUES.

Joining Tables and Subqueries

Tables are joined in a query by listing them in the FROM clause and linking them on common attributes. A join retrieves data from multiple tables simultaneously by equating primary keys and foreign keys in the WHERE clause join condition, requiring $n$ minus 1 join conditions when joining $n$ tables. You can simplify table references and enable recursive joins by using aliases.

• List all participating tables in the FROM clause.
• Define join conditions matching foreign keys to primary keys in the WHERE clause.
• Use table name prefixes or aliases when attribute names are ambiguous.

Joining the PRODUCT and VENDOR tables where PRODUCT.V_CODE equals VENDOR.V_CODE.

Subqueries allow a query to be nested inside another statement, processing data based on dynamically derived information. A subquery executes first, and its output is passed as input to the outer query. While independent subqueries run once, a correlated subquery executes once for every row processed in the outer query by referencing columns from the outer query.

Listing products with a price greater than or equal to the average product price using a nested subquery.

Procedural SQL and Triggers

A trigger is a block of procedural SQL code automatically invoked by the DBMS upon a specified data manipulation event. Triggers execute as statement-level or row-level operations either before or after INSERT, UPDATE, or DELETE statements. You rely on them to enforce business constraints, maintain data integrity, and update dependent table attributes.

• Define trigger timing using BEFORE or AFTER.
• Specify the triggering event using INSERT, UPDATE, or DELETE.
• Use FOR EACH ROW keywords for row-level triggers.
• Reference changed attribute values using the :NEW modifier and original values using the :OLD modifier.

Creating the TRG_PRODUCT_REORDER row-level trigger to update reorder flags when stock levels fall below minimum limits.

Procedural Language SQL extends standard SQL by adding programming constructs like variables, loops, and conditional execution. You group statements into blocks, functions, triggers, and stored procedures executed directly at the database server to encapsulate business logic.

• Enclose commands inside BEGIN and END clauses.
• Declare variables, data types, and initial values in the DECLARE section.
• Execute WHILE loops using the syntax WHILE condition LOOP ... END LOOP.

6. Transaction Management and Concurrency Control

Transaction management and concurrency control protocols guarantee database consistency and isolation in multi-user environments.

A database transaction is a logical unit of work that must be entirely completed or entirely aborted to maintain consistency, adhering to ACID properties. Transactions reflect real-world events and consist of database requests like SELECT, INSERT, and UPDATE. To guarantee integrity, they must satisfy Atomicity, Consistency, Isolation, and Durability, alongside serializability for concurrent operations.

In the Ch10_SaleCo database, selling a product requires creating an invoice, updating product inventory, updating account transactions, and adjusting the customer balance.

ACID Properties and Transaction Logs

The transaction log keeps track of all database-modifying transactions and provides the necessary data to recover the database after a failure. The DBMS automatically updates the transaction log alongside transactions, recording operation types, affected objects, before and after values, and pointers to maintain transactional correctness.

Concurrency Control and Locking

Simultaneous execution of transactions in a multiuser environment can introduce lost updates, uncommitted data, and inconsistent retrievals without proper control. Without a scheduler enforcing concurrency control, concurrent transactions can overwrite each other's updates, access uncommitted data that later gets rolled back, or read aggregate data mid-update.

In the PRODUCT table, two concurrent transactions attempting to adjust quantity on hand result in a lost update where a 100-unit addition is overwritten by a subtraction.

The scheduler uses locking mechanisms to establish execution orders for conflicting operations, ensuring serializability and data isolation. The scheduler interleaves database operations based on concurrency control algorithms. Pessimistic locking assumes conflicts are likely and uses a lock manager to assign exclusive or shared access.

Two-phase locking defines a growing phase and a shrinking phase to guarantee transaction serializability. In the growing phase, a transaction acquires all required locks without releasing any. Once its locked point is reached, it enters the shrinking phase where it releases locks and cannot obtain new ones.

Database Recovery Management

Database recovery restores an inconsistent database to a consistent state using transaction logs, checkpoints, and deferred-write or write-through techniques. Critical events like hardware failures, human errors, or natural disasters can compromise integrity. Recovery protocols use write-ahead logging, database buffers, checkpoints, and undo/redo operations.

When you restore database consistency, you apply these recovery steps:

• Identify the last checkpoint in the transaction log.
• For a transaction started and committed before the last checkpoint, take no additional action.
• For a transaction committed after the last checkpoint, redo the transaction using after values in ascending order.
• For any transaction rolled back or left active after the last checkpoint, handle changes based on whether deferred-write or write-through is used.

10 Key Ideas

1. Transitioning from traditional file systems to database management systems eliminates structural dependence and data anomalies while securing data integrity.
2. Data models serve as communication tools and blueprints that translate business rules into structured entities, attributes, and relationships.
3. The relational model uses two-dimensional tables, keys, integrity rules, and relational algebra to govern data storage and manipulation securely.
4. Advanced entity relationship modeling and database normalization eliminate redundancies, prevent anomalies, and ensure high data quality.
5. Structured Query Language provides nonprocedural commands for defining database structures, manipulating data, and executing complex multi-table queries.
6. Transaction management and concurrency control protocols guarantee database consistency and isolation in multi-user environments.
7. Proper database design and robust management principles are essential to prevent system failures, ensure data integrity, and transform raw data into valuable business intelligence.
8. Relational algebra defines theoretical operators that manipulate table contents with the property of closure, producing new relations from existing ones.
9. Database normalization stages—including first, second, and third normal forms—progressively eliminate partial and transitive dependencies to safeguard data.
10. The transaction log keeps track of all database-modifying transactions and provides the necessary data to recover the database after a failure.