Lesson 8

This module opened with a simple claim: regardless of a designer's chosen approach, good database design follows the same overarching strategy, proven objectives pursued through systematic methods and tools across the database life cycle. Eight lessons later, that claim has been unpacked into a working vocabulary: the subject and application approaches, the three-schema architecture, the eight-stage database life cycle, and the CASE tools that support it. This conclusion ties those eight lessons together and points back to each one.

None of this vocabulary was introduced for its own sake. Each term exists to answer a specific, recurring problem: how do you keep a design stable as new applications arrive (the subject approach); how do you let different groups work with the same data without stepping on each other (the three-schema architecture); where does a given piece of work actually belong in a larger process (the DBLC); and what can be automated versus what genuinely requires a designer's judgment (CASE tools). Revisiting the eight lessons with that framing in mind is more useful than simply re-reading them in order.

Lesson 1 introduced the database life cycle (DBLC) as the sequence of stages a database passes through, from the moment someone recognizes a need for it to the day it is eventually retired: six design stages, requirements collection, conceptual design, DBMS selection, logical design, physical design, and implementation, followed by two post-design stages, operation and maintenance and evolution. That same lesson drew a sharp line between this database life cycle and a related but separate idea with a similar name, the data life cycle, the stages an individual piece of data moves through from being recognized as valuable to eventually being discarded. The two rest on top of each other: a weak database design undermines every stage of the data life cycle built on it.

Lesson 2 moved from the life cycle's stages to the objectives that guide work within them. Three objectives belong to the designer alone: consistent information delivery, eliminating redundancy, and preserving still-valuable data during deletion. Three more, speed, data integrity enforcement, and security, are shaped by design but ultimately depend on the RDBMS, network, and hardware the design runs on. Knowing which objectives are fully yours to control is what tells you where design effort actually pays off, rather than being spent on something the schema alone can never guarantee.

Lesson 3 and Lesson 4 covered the two ways to approach database design, and together they form the module's central contrast. The subject approach organizes a database around the core, real-world subjects a business deals with, customers, orders, products, independent of any one application; it is a top-down approach, moving from identified business objects, to grouped subject areas, to designed databases, to the applications eventually built on top. The application approach instead designs the database around one specific application's screens, reports, and forms, a bottom-up approach that works well for a single, isolated tool but produces data silos the moment a second application needs overlapping data. The Stories on CD, Inc. project from Module 1 was, without naming it at the time, an example of the subject approach already in action: CUSTOMER, ORDER, CD, and DISTRIBUTOR were identified as entities because those are what the business itself deals with, not because any one report needed them structured that way.

Lesson 5 showed that a database can, and should, be described at three levels at once: the external schema (user views), where database users and application developers work; the conceptual schema (logical schema), where data modelers and designers work; and the internal schema (physical schema), where DBAs and database engineers work. Explicit mappings between adjacent levels are what deliver logical and physical data independence, a change at one level does not have to ripple through the others, which is why a Sales view, a Support view, and a Reporting view can all be built from one shared conceptual schema rather than each maintaining its own disconnected copy of the data.

Lesson 6 and Lesson 7 returned to the eight-stage life cycle introduced in Lesson 1 and walked through each stage's key activities in depth. Lesson 6 covered the six design stages, from analyzing a company's situation and defining scope in Stage 1, through conceptual modeling, DBMS selection, logical design, and physical design, to building and testing the system in Stage 6. Lesson 7 then focused on the two post-design stages this course spends comparatively little time in but that, in a real organization, last the longest: Operation, the database's ongoing day-to-day use, and Maintenance and Evolution, which covers database modification, performance tuning, backups and recovery, and eventually retirement and archiving. Both lessons stressed that the life cycle is not strictly one-directional. Maintenance and Evolution routinely sends a live database back through earlier design stages as requirements change, the same feedback loop that would carry a request as small as adding gift wrapping as a line-item option back through conceptual and logical design, rather than letting it be bolted onto the physical schema as an afterthought.

Lesson 8 closed the module with Computer-Aided Software Engineering (CASE) tools, mapped onto each of the eight DBLC stages covered by Lessons 6 and 7. Their heaviest lifting happens at conceptual design (visual entity-relationship modeling), logical design (automated normalization checks and many-to-many resolution), physical design (DBMS-specific datatype mapping and indexing), implementation (DDL script generation), and maintenance and evolution (reverse and forward engineering to manage schema drift). They play a smaller, supporting role at requirements analysis and DBMS selection, and essentially none during day-to-day operation. No CASE tool replaces the design judgment this module has spent eight lessons building: a tool can draw a clean diagram from the entities and relationships it is given, but it cannot decide, on its own, that CUSTOMER and ORDER should be separate entities in the first place.

A few terms introduced across these eight lessons are worth keeping together in one place, since several of them sound similar but mean different things:

• Database life cycle (DBLC) versus data life cycle. The DBLC describes the stages of designing and running a database system itself; the data life cycle describes what happens to one piece of data, from being recognized as valuable through eventual disposal. The DBLC is this module's main subject; the data life cycle rests on top of whatever DBLC produced the system storing that data.
• Subject approach versus application approach. The subject approach organizes a database around real-world business subjects, independent of any one application; the application approach organizes it around a specific application's needs. Top-down and bottom-up describe the same contrast from the direction each approach starts in.
• External, conceptual, and internal schema (user view, logical schema, physical schema). Three ways of describing the same database at once, aimed at three different groups, connected by explicit mappings that keep each level independent of the others.
• Logical design versus logical schema; physical design versus physical schema. The schema is the structure itself; the design is the stage of work, Stage 4 and Stage 5 of the DBLC respectively, that produces or tunes it.
• User views and DML. A user view is a saved query, created with DDL, exposing a subset of the database to a particular group; DML is the separate set of SQL commands, insert, update, delete, used to modify the data within existing tables.

Read end to end, this module's eight lessons form a single argument rather than eight separate topics. Design strategy (Lesson 2) sets out what a designer is trying to accomplish; the subject and application approaches (Lessons 3 and 4) describe two fundamentally different ways to pursue it, with the subject approach winning out for anything meant to last; the three-schema architecture (Lesson 5) explains the structural mechanism, external, conceptual, internal, that makes a subject-oriented design stable as new applications arrive; the DBLC (Lessons 1, 6, and 7) places all of that work inside a sequence of stages that runs from a recognized need through years of operation and maintenance; and CASE tools (Lesson 8) automate the mechanics of that sequence without ever replacing the judgment behind it.

This course, the first in a two-course series, has spent its time almost entirely inside the design stages of that life cycle, mostly conceptual and logical design, using Stories on CD, Inc. as a running example throughout. The second course in the series picks up where this one leaves off, with the detailed data modeling, normalization, and entity-relationship diagramming work that logical design actually involves.

A link to the Module 3 quiz belongs here. Supply the quiz page's URL (or the original legacy conclusion.php, if one exists) and it will be added.

Before moving on to the next module, click the Quiz link below to check your knowledge of database design strategy and tools.
Database Design Strategy - Quiz