Database Life Cycle (DBLC) Design Stages

As introduced earlier in this module, the Database Life Cycle (DBLC) 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 splits into six design stages, covered in detail in this lesson, followed by two post-design stages that pick up once the database is live.

Each stage builds directly on the one before it, which is why skipping ahead tends to cost more time than it saves: a physical design decision made before the logical design is settled usually has to be redone once a missed requirement surfaces, and a DBMS chosen before requirements are clear can turn out to be the wrong fit entirely. The order below is not arbitrary.

Design stages:

1. Requirements collection and analysis
2. Conceptual design
3. DBMS selection
4. Logical design
5. Physical design
6. Implementation

Post-design stages:

7. Operation
8. Maintenance and evolution

A diagram illustrating all eight stages, the design-stage to post-design-stage transition, and the feedback loop back from Maintenance and Evolution into earlier design stages, is in progress and will be inserted here once available.

This stage lays the foundation for everything that follows: understanding the organization's needs and defining the database's purpose before any design work begins.

Key activities:

• Analyze the company situation. Examine the organization's structure, mission, and operational components to understand how they function and interact.
• Define problems and constraints. Identify issues with the current system, inefficiencies or data duplication, for example, and constraints such as budget or hardware limitations, using input from stakeholders and end users.
• Define objectives. Establish the database's goals, such as supporting specific queries, reports, or transactions, and determine whether it will interface with other systems or share data.
• Define scope and boundaries. Set the project's scope, organization-wide or department-specific, and identify external boundaries such as existing hardware or software limitations.

For Stories on CD, Inc., this is the stage that produced the requirements covered back in Module 1: that the company needs to track customers, their orders, the CDs it sells, and the distributors that supply them.

This is the subject-approach modeling covered earlier in this module: identifying the entities, attributes, and relationships the business actually deals with, independent of any specific database product.

Key activities:

• Identify entities and attributes. Determine the real-world objects the database needs to track, customers, orders, products, and the properties that describe each one.
• Model relationships. Determine how those entities connect to one another, one-to-one, one-to-many, or many-to-many, without yet worrying about how a particular DBMS will implement them.
• Produce a conceptual model. Typically an entity-relationship (ER) diagram, a visual representation of the database structure that stays independent of any specific database product.

Before logical design can proceed very far, the organization needs to know which database platform it is actually designing for, since some logical and physical decisions depend on what the chosen DBMS supports.

Key activities:

• Evaluate candidate platforms. Compare database products against the requirements gathered in Stage 1: the features needed, expected data volume, performance requirements, and compatibility with existing infrastructure.
• Weigh cost and support. Consider licensing cost, vendor support, available expertise, and how well a candidate platform fits the organization's existing technology environment.
• Select the DBMS. Formally choose the platform that logical and physical design will target for the remainder of the project.

This is also the stage where an organization decides between a commercial platform such as Oracle Database and alternatives, a decision this course does not make for you, but one every real project has to make before logical design can get specific about datatypes, indexing options, and storage features that vary from one DBMS to the next. An organization already running Oracle elsewhere, for example, may weigh the cost of a new platform's licensing against the value of keeping database administration skills and tooling consistent across its systems, a cost and support consideration that has nothing to do with the conceptual model itself and everything to do with this specific stage.

Logical design translates the conceptual model into the structures a relational database actually uses, independent of any one DBMS's physical storage details.

Key activities:

• Map entities to tables. Translate the conceptual model's entities and attributes into tables, columns, and keys.
• Define relationships through keys. Establish the primary and foreign keys that implement the relationships identified during conceptual design.
• Normalize the design. Apply normalization rules to resolve redundancy and update anomalies, ensuring data can be retrieved quickly and reliably without inconsistency.

This is the stage that produces the logical schema discussed in the previous lesson, and it is where the ER diagram for Stories on CD, Inc. gained its specific columns, primary keys, and foreign keys.

Physical design optimizes the logical design for performance on the specific DBMS chosen in Stage 3.

Key activities:

• Choose storage structures and indexes. Select file organization, indexing strategies, and partitioning to minimize the time required for queries and updates.
• Account for the hardware and DBMS. Choose file structures and access methods compatible with the available hardware and the capabilities of the selected database platform.
• Balance performance against other constraints. Weigh retrieval speed against storage space, write performance, and maintainability, since optimizing heavily for one often costs something in another.

In the Implementation stage, the logical and physical designs become a working system, and that system is validated before it goes live.

Key activities:

• Create the database structures. Use SQL commands such as CREATE TABLE to build the database based on the logical and physical designs, including the constraints, primary keys, foreign keys, and check constraints those designs specified.
• Load initial data. Populate the database with data, often migrated from existing systems or files, and confirm it loaded correctly and completely.
• Configure access. Set up user permissions and security measures to protect the database.
• Test before going live. Verify the database supports the required queries, reports, and transactions; test performance under realistic load; and confirm backups can actually be restored, before anyone relies on the system in production.

Once implementation and testing are complete, the database moves into Operation: full deployment, supporting the organization's actual day-to-day activities.

Key activities:

• Run production work. The database now handles real business activity rather than test data.
• Support users and applications. Train end users and administrators as needed, and keep the database available and responsive for everything that depends on it.
• Monitor and back up. Watch for problems as they occur, and keep the backup and recovery plan tested during implementation actually running on schedule.

Maintenance and Evolution keeps the database functional and aligned with changing needs for as long as it remains in use, typically the longest stage of the entire life cycle.

Key activities:

• Monitor and tune. Track performance over time, address issues such as slow queries or data inconsistencies, and manage growing capacity before it becomes a problem.
• Update the schema. Modify tables, keys, and relationships as business requirements evolve, feeding back into earlier design stages rather than starting the whole life cycle over.
• Maintain backups and recovery. Keep backup and recovery procedures current, and periodically re-test them rather than assuming they still work.

A new Stories on CD request, say, tracking gift wrapping as a line-item option, would re-enter the life cycle here: back through conceptual and logical design to model the new attribute correctly, rather than being bolted onto the physical schema directly.

A short recap of the eight stages covered in this lesson:

• Requirements collection and analysis, conceptual design, DBMS selection, logical design, physical design, and implementation make up the six design stages.
• Operation and maintenance and evolution are the two post-design stages, picking up once the database is live and, for maintenance in particular, typically lasting far longer than every design stage combined.
• Each stage depends on the one before it: conceptual design assumes requirements are understood, logical design assumes a conceptual model exists, physical design assumes a DBMS has been chosen, and so on through implementation.
• The life cycle is not strictly one-directional. Maintenance and evolution routinely sends a live database back through earlier design stages as requirements change, rather than only ever moving forward.

The next lesson explores practical examples of building a conceptual model during Stage 2. Review the stage descriptions above as a reference as you work through it.