Database Design for Mere Mortals: 25th Anniversary Edition

Citation

Hernandez, M. J. (2020). Database Design for Mere Mortals: 25th Anniversary Edition (4th ed.). Addison-Wesley Professional.

Chapter Summary

The book has sixteen chapters in three parts plus eight appendixes and a glossary, about 640 pages in all. It deals only with the logical design of a relational database: the author separates this from physical implementation and application development, and keeps the text free of SQL, programming code and any particular database product. Part I (Chapters 1–3) introduces databases, design objectives and terminology; Part II (Chapters 4–13) teaches the design process; Part III (Chapters 14–16) deals with bad design and exceptions. Chapters 1–13 end with a summary, and nearly all with review questions, and the fourth edition adds a worked "Example" section at the end of Chapters 5–13.

Chapter 1: The Relational Database

What a database is, how a relational database stores and retrieves data, its advantages, and what a relational database management system does. The fourth edition trims older material and adds a "What's Next?" section on the author's view of the current state and future of the relational database.

Chapter 2: Design Objectives

Argues that design determines the consistency, integrity and accuracy of data. It lists the objectives of a good design (support for required and ad hoc queries, single-subject tables, integrity at field, table and relationship level, support for business rules, room to grow) and its benefits. It then compares traditional methods (requirements analysis, data modelling, normalization) with the book's own method, which keeps requirements analysis and a basic entity-relationship diagramming style but does not work through normal forms.

Chapter 3: Terminology

Introduces the vocabulary the method relies on, grouped as value-related terms (data, information, null and the problems nulls cause), structure-related terms (table, field, record, view, keys, index), relationship-related terms (types of relationship, type and degree of participation) and integrity-related terms (field specification, data integrity).

Chapter 4: Conceptual Overview

Maps the whole process before it is taught step by step: mission statement and objectives, analysis of the current database, data structures, relationships, business rules, views and a final integrity review.

Chapter 5: Starting the Process

Covers interviewing (guidelines for participants and for the interviewer, revised in this edition for online meetings and remote work) and how to write a mission statement and mission objectives that focus the design.

Chapter 6: Analyzing the Current Database

Examines paper-based and legacy databases, how data is currently collected and presented, interview techniques with users and with management (current, additional, future and overall information requirements), and the compilation of a preliminary field list and a calculated field list for review.

Chapter 7: Establishing Table Structures

Turns subjects into a preliminary and then final table list, with naming conventions, table types and descriptions; associates fields with tables; refines fields by resolving multipart and multivalued fields against an "ideal field"; and refines tables against an "ideal table", including subset tables and the handling of redundant data.

Chapter 8: Keys

Explains candidate, primary, alternate and non-key fields, how to choose a primary key, and the table-level integrity that keys provide, ending with a review of the initial table structures.

Chapter 9: Field Specifications

Treats field specifications, which the author says many developers neglect, as the source of field-level integrity. A specification has general, physical and logical elements, and the chapter shows how unique, generic and replica specifications reduce the work of documenting every field.

Chapter 10: Table Relationships

The longest chapter. It covers one-to-one, one-to-many, many-to-many and self-referencing relationships, how to identify and establish each, how to refine foreign keys, and how to set relationship characteristics: deletion rules, type of participation and degree of participation.

Chapter 11: Business Rules

Explains what business rules are and their types, divides them into field-specific and relationship-specific categories, and shows how to define them with users and management and record them on specification sheets. Validation tables are introduced as a way to enforce rules through data.

Chapter 12: Views

Describes data, aggregate and validation views and how to determine, define and document the views users need.

Chapter 13: Reviewing Data Integrity

Revisits every level of integrity, business rules and views, and assembles the final documentation for the design.

Chapters 14–16: Bad Design—What Not to Do; Bending or Breaking the Rules; In Closing

Chapter 14 warns against flat-file and spreadsheet-style designs and designs shaped by the features of a particular product. Chapter 15 names the rare cases where departing from the method may be justified, analytical databases and processing performance, and insists that every departure be documented. Chapter 16 is a brief closing note.

Appendixes

Answers to the review questions, a diagram of the full design process, collected design guidelines, blank documentation forms (field, business rule and view specifications), the diagram symbols, sample designs, a discussion of how normalization is built into the method ("On Normalization"), and recommended reading.

Key Concepts

Chapters 1–3: Foundations

• Logical design: deciding tables, fields, keys, relationships and integrity before any implementation; the only phase the book covers.
• Types and degree of participation: whether a record must take part in a relationship, and how many related records it may have.

Chapters 4–13: The Design Process

• Mission statement and mission objectives: short statements of the database's purpose and the tasks it must support, used to guide the rest of the design.
• Multipart and multivalued fields: fields holding several items of data or several values, which the method resolves into separate fields or tables.
• Primary, candidate and alternate keys: the fields that uniquely identify records and the basis of table-level integrity.
• Field specification: the documented general, physical and logical properties of a field, which secure field-level integrity.
• Deletion rule: what happens to related records when a record in a relationship is deleted.
• Business rule and validation table: a constraint arising from how the organisation works, sometimes enforced by a table of allowed values.
• Levels of integrity: table, field and relationship integrity plus business rules, checked together at the end of the process.

Chapters 14–15: Other Issues

• Flat-file and spreadsheet design: common layouts that the book treats as sources of redundant and inconsistent data.
• Documented rule-bending: departing from the method only for reasons such as analysis or performance, and recording why.

Critical Analysis

Strengths

• One complete method from interview to documentation. Part II follows a single process in order, from mission statement through a final integrity review, with recurring forms collected in the appendixes. The author asks readers to read the book in sequence for that reason.
• No prerequisites and no product dependence. The introduction states that no background in database design is needed, and the text avoids SQL, programming languages and features of particular database systems, so the method applies whichever system is used later.
• Strong on requirements gathering. Chapters 5 and 6 spend more than eighty pages on interviews, mission objectives and analysis of existing paper and legacy systems, a stage that many database textbooks treat briefly.
• Built for self-study. Review questions with answers in Appendix A, a process diagram, a list of design guidelines and new worked examples at the end of Chapters 5–13 support a reader working alone.

Limitations

• Normalization is kept out of the main text. The method builds the effect of normalization into its steps instead of teaching normal forms, which appear only briefly in Chapter 2 and in an appendix. Students in a course that examines functional dependencies and normal forms will need another text for that material.
• Logical design only. The introduction excludes physical implementation and application development, so there is no SQL, no query processing and no treatment of transactions or storage.
• Deliberately simplified examples. The author warns that some examples are simplified, incomplete or intentionally wrong in order to illustrate a technique; readers should focus on the technique, as the book itself advises.

Where It Fits in the Course

Within the databases course this is the most approachable starting point and the only book devoted entirely to design. Coronel and Morris's Database Systems: Design, Implementation, and Management is a broad introductory textbook that also covers SQL and data management in organisations; Hernandez can be read before it or alongside its data-modelling chapters for a slower, more practical walk through the design process. Silberschatz, Korth and Sudarshan's Database System Concepts and Elmasri and Navathe's Fundamentals of Database Systems are fuller computer-science textbooks that teach normal forms, query processing and transactions formally; Hernandez gives intuition and practice for the design part of those courses but does not replace them.

Ramakrishnan and Gehrke's Database Management Systems and Gray and Reuter's Transaction Processing deal with how database systems work internally, a subject Hernandez deliberately leaves aside. Read them later, once relational design and SQL are familiar.

How to Use This Book

The introduction addresses readers new to database work, people who have used database programs and now need to build their own databases, and programmers who have created databases without a method. No mathematics, programming or SQL is assumed.

• Core: Chapters 2–3 for the objectives and vocabulary, Chapter 4 for the map of the process, then Chapters 7–10 (tables, keys, field specifications, relationships), which carry most of the design substance.
• Requirements work: Chapters 5–6 matter most for readers designing a real system for an organisation; students in a theory-oriented course can skim them.
• Integrity and rules: Chapters 11–13 complete the method; read Chapter 11 carefully if the course stresses constraints.
• Optional: Chapter 1 for readers who already know what a relational database is; Chapters 14–16 are short and quick to read.

Practise by taking a small design of your own, such as a club, a shop or a course-registration system, through every step and filling in the forms in Appendix D. Answer each chapter's review questions before checking Appendix A. Then read Appendix G alongside the normalization chapter of a course textbook to see how the method's steps correspond to the normal forms, and implement the finished design in any relational system to learn the SQL that this book leaves out.