Lesson 1

Database designers, regardless of their chosen approach, share the same overarching strategy:

1. Accomplish proven design objectives.
2. Apply systematic design methods, supported by tools, through the database life cycle.

This module introduces those objectives, compares the subject and application approaches to design, illustrates the three-schema architecture, and explains both the design and post-design stages of the database life cycle. We also examine the role of CASE tools in automating and improving these processes.

After completing this module, you will be able to:

1. Describe the overall strategy of database design.
2. Differentiate between the subject approach and the application approach.
3. Define the three-schema architecture: user view, logical schema, and physical schema.
4. Identify the design stages of the database life cycle.
5. Explain the post-design stages of the database life cycle.
6. Discuss the role of CASE tools in database design.

Database designers generally take one of two approaches, and which one a design starts from has lasting consequences for how well the resulting database serves the organization.

The subject approach, sometimes called the data-driven approach, starts by identifying the data itself, the entities, attributes, and relationships that exist in the business, independent of any one application's specific requirements. A design built this way tends to be stable over time: the organization's customers, orders, and products do not change just because a new application needs to use that data differently.

The application approach, sometimes called the process-driven approach, starts instead from a specific application's processing requirements and derives the data structures needed to support it. This can be faster for a single, well-defined application, but when several applications are each designed this way independently, the organization tends to end up with fragmented, redundant data, the same customer information stored slightly differently in several systems, with no single structure anyone can rely on as authoritative.

Most modern database design favors the subject approach for exactly this reason: data modeled around what it represents, rather than around how one application happens to use it, is what later lessons in this course will have you practice. The Stories on CD, Inc. project from Module 1 is itself an example of the subject approach in action: the CUSTOMER, ORDER, CD, and DISTRIBUTOR entities were identified from the business itself, not from the requirements of any one report or screen.

A related idea is that a database can, and should, be described at three different levels at once, each serving a different purpose:

• User view: how a particular user or application sees and interacts with the data, often only a subset of the full database, shaped around that user's needs.
• Logical schema: the complete, organization-wide structure of the data, its entities, attributes, and relationships, independent of how it is physically stored.
• Physical schema: how the data is actually stored on disk, including file organization, indexing, and storage-specific performance decisions.

Separating these three levels is what allows each to change without disrupting the others: a new index can be added at the physical level without any user noticing, and a user's view can be customized without touching the underlying logical structure everyone shares. A billing clerk's view of the Stories on CD database, for example, might show only customer and order information, hiding distributor and cost details entirely, while the logical schema underneath still contains the full picture, and the physical schema decides, separately again, how that data is actually indexed and stored for fast retrieval.

The database life cycle is the sequence of stages a database passes through, from the moment someone recognizes a need for it to the day it is eventually retired. It is traditionally split into design stages and post-design stages.

The design stages include:

1. Requirements collection and analysis, gathering and documenting what the database needs to do.
2. Conceptual design, modeling the data at the subject level, independent of any specific DBMS.
3. DBMS selection, choosing the database platform the design will ultimately run on.
4. Logical design, mapping the conceptual model into the structures a relational (or other) database actually uses.
5. Physical design, deciding storage, indexing, and performance details for the chosen DBMS.
6. Implementation, building the actual database and loading it with data.

The post-design stages pick up once the database is live:

7. Operation, the database's normal day-to-day use supporting the organization's work.
8. Maintenance and evolution, ongoing monitoring, tuning, and modification as requirements change, which for a well-used database is often the longest stage of the entire life cycle.

Notice how closely this maps to the work you have already been doing. Module 1's conceptual framing of Stories on CD, Inc., identifying entities and attributes before specifying any particular database product, is stage 2. The logical schema work ahead of you in this course, assigning primary and foreign keys and normalizing the design, is stage 4. Stages 3, 5, and 6 lie beyond the scope of this course, but knowing where they fit is what lets you place this course's material correctly within the larger process a real database project would follow. Later lessons in this module walk through each of these stages individually.

Computer-Aided Software Engineering (CASE) tools support and automate parts of this process: drawing and validating ER diagrams, generating physical schema from a logical design, checking a design against normalization rules, and documenting the result. The diagramming tools mentioned earlier in this course, such as Oracle Data Modeler, dbdiagram.io, and DrawSQL, are themselves examples of CASE tools used during the conceptual and logical design stages; more comprehensive CASE suites can carry a design further, generating physical schema and even application code directly from the logical model.

None of these tools replace the thinking this course teaches. A CASE tool can draw a clean ER diagram from the entities and relationships you give it, and can even flag an unnormalized table, but it cannot decide, on its own, that CUSTOMER and ORDER should be separate entities in the first place; that judgment is squarely the subject-approach design work covered above.

It is worth distinguishing the database life cycle, above, from a related but separate concept with a very similar name: the data life cycle. Where the database life cycle describes the stages of designing and running the database system itself, the data life cycle describes what happens to an individual piece of data, from the moment it is recognized as valuable to the moment it is finally discarded.

All software, including databases, has a life cycle of usefulness bounded by cost-effectiveness. At some point, legacy systems become too costly or impractical to maintain, and organizations must replace or retire them. The data life cycle reflects a related but narrower pattern: the transition of a specific piece of data from immediate use to archival storage and eventual disposal.

1. Need it: recognition that a data element has value. Tracking a credit card transaction's charge-back fee amount, for example, helps a retailer assess profitability, return on investment, and budgeting needs.
2. Plan it: define business rules, frequency, size, and security requirements. At this stage, questions are asked about how the data will be captured, stored, and validated.
3. Collect it: implement the plan and begin capturing real data. Initial testing may reveal gaps or the need for additional data elements.
4. Store it: decide on appropriate storage methods, from lightweight formats such as XML for real-time capture, to enterprise data warehouses for reporting and aggregation. Reliability and performance are key considerations.
5. Combine it: integrate the data with other elements for reporting and decision-making. Well-defined data becomes part of the organization's broader knowledge base and is reused across departments.
6. Act on it: data drives decisions, from operational changes, such as offering cash discounts, to strategic moves, such as launching a debit card program. At this stage, data must have an auditable pedigree to support accountability.
7. Archive it: once data loses immediate relevance, it is transferred to archives, supporting long-term analysis and disaster recovery while reducing the load on production systems.
8. Remove it: eventually, obsolete data is deleted or becomes inaccessible as its storage media ages out of use. Not all data retains value indefinitely, though historians and analysts may later extract insight from archived fragments.

A well-designed database, built according to the database life cycle described above, is what makes each of these data life cycle stages tractable in the first place. A sound logical schema makes it clear which data needs planning and validation at the "plan it" stage; good physical design keeps "store it" performant as volume grows; and a design that respects referential integrity is what keeps "combine it" from producing contradictory reports. The two life cycles describe different things, but a weak database design undermines every stage of the data life cycle built on top of it.

A short recap of the terrain this lesson has covered, and where the rest of this course sits within it:

• Good database design follows an overall strategy: proven objectives, pursued through systematic methods and tools across the database life cycle.
• The subject approach models data around what it represents; the application approach models it around a single application's needs. This course, and most modern design practice, favors the subject approach.
• The three-schema architecture, user view, logical schema, and physical schema, lets each level change independently of the others.
• The database life cycle has six design stages, requirements collection, conceptual design, DBMS selection, logical design, physical design, and implementation, followed by two post-design stages, operation and ongoing maintenance.
• CASE tools automate parts of this process but do not replace the design judgment this course teaches.
• The data life cycle is a related but distinct idea: the stages one piece of data moves through, from being recognized as valuable to eventually being discarded, resting on top of whatever database life cycle produced the system storing it.

Everything from here through the rest of this course sits inside the design stages of the database life cycle, mostly conceptual and logical design. Keeping that larger map in view is what will make each individual lesson's exercises feel like part of something, rather than isolated skills.

The next lesson explores the objectives of database design strategy.