Database Life Cycle: Five Stages and Their Deliverables

The Database Life Cycle (DBLC) is the sequence of stages a database passes through from initial conception through active use and eventual retirement. Understanding where a given design task falls within the DBLC helps database designers make decisions appropriate to that stage and avoid premature implementation of details that belong to a later phase.

This course groups the database life cycle into five stages. Other methodologies separate planning, conceptual design, testing, deployment, or retirement into additional phases; there is no universal stage count.

1. Requirements Analysis
2. Logical Design
3. Physical Design
4. Implementation
5. Monitoring, Modification, and Maintenance

In the first stage, Requirements Analysis, you determine what data the database will need to hold and what tasks it will need to perform to meet the needs of its users. This stage produces two categories of output:

1. Business objects - the things in the business environment that need to be represented in the database, such as customers, products, orders, and employees.
2. Business rules - the restrictions on how users perceive and interact with data, such as "an order must belong to exactly one customer" or "a product price cannot be negative."

The first course in this series covered Requirements Analysis and the first phase of Logical Design through the creation of the ER diagram. This course picks up at the next phase of the Logical Design stage: normalizing the base tables you created in the ER diagram so that information can be stored and retrieved efficiently.

In the first phase of the Logical Design stage you converted business objects and their characteristics into entities^[1] and attributes^[2]. You then created an entity-relationship (ER) diagram^[3] that graphically represents as base tables the entities, their attributes, and the relationships^[4] that exist between them. The next phase is to normalize^[5] those base tables.

During design, a table schema describes the proposed attributes and constraints. During implementation, CREATE TABLE establishes that structure in the DBMS. An ERD may show conceptual entities or logical table schemas, depending on its level of detail.

A base table is a table whose data is stored independently of a query-defined view. Calling an ERD entity a base table is shorthand for its intended relational implementation, not a requirement that every entity maps to exactly one table.

Logical table schemas become base tables during implementation. Mapping depends on the business rules: many-to-many relationships need an associative table, and some attributes or entity hierarchies need additional tables.

In an ERD, the mapping from design to implementation follows three rules:

• Entities such as Customer, Order, and Product are each mapped to a base table during database design.
• Each attribute of an entity becomes a column in the corresponding base table.
• Primary keys and foreign keys defined in the ERD are enforced as constraints in the base tables.

Not all tables in a relational database originate from a single entity in the ERD. The following table compares base tables, common base-table roles, and views:

Consider this ERD fragment as an example. An Employee entity has attributes EmployeeID, Name, and HireDate. A WorksIn relationship connects Employee to Department. The Employee entity maps directly to the following base table:

CREATE TABLE Employee (
  EmployeeID INT PRIMARY KEY,
  Name       VARCHAR(100),
  HireDate   DATE
);

The Employee table is a base table derived from the Employee entity. If the WorksIn relationship is many-to-many - meaning one employee can work in multiple departments and one department can contain multiple employees - a second base table called EmployeeDepartment is created to resolve the relationship.

A database table is the core structural component for storing and organizing data in a relational database. Conceptually, a table resembles a matrix or spreadsheet in which each row represents a unique instance of an entity and each column defines a specific type of data with a predefined data type and associated constraints.

Database tables serve four core functions:

1. Data Storage - a table stores persistent data representing real-world entities such as customers, products, and transactions. This data survives application restarts and is available to any authorized user or process.
2. Data Organization - tables organize data into structured formats that simplify processing and reduce redundancy. Related data is grouped into the same table; unrelated data is separated into different tables linked by keys.
3. Querying and Manipulation - tables are designed to support efficient SQL operations including SELECT, INSERT, UPDATE, and DELETE. The structure of the table determines how efficiently these operations execute.
4. Relational Integrity - tables are linked through primary keys and foreign keys, enabling complex relationships across entities to be expressed and enforced at the database level.

Database tables are governed by constraints that ensure consistency and accuracy across all operations:

• Primary Key - uniquely identifies each record in the table.
• Foreign Key - links one table to another, enforcing referential integrity.
• NOT NULL - ensures that a column cannot store a null value.
• UNIQUE - prevents duplicate values in a specified column.
• CHECK - validates that column values meet a specified condition.

Tables form the foundation of the relational model. Each table represents a relation. Normalization analyzes dependencies to reduce avoidable redundancy and update anomalies. Set-based operations such as joins allow data from multiple tables to be combined and filtered efficiently. A database table is not merely a container for data - it is a relational construct designed to support efficient storage, logical access patterns, robust integrity constraints, and complex entity relationships.

The activities recur across DBMS platforms, but their grouping and sequence vary by methodology. Oracle, SQL Server, and PostgreSQL have different implementation details; they do not impose a single universal five-stage development process.

Some practitioners extend the DBLC to seven stages to capture the full operational lifecycle more explicitly:

1. Planning and Requirements Analysis - defining the database's purpose, identifying user and stakeholder requirements, and creating a conceptual data model.
2. Design - refining the conceptual model into a logical data model, then translating it into a physical schema for the target DBMS.
3. Implementation - creating tables, indexes, and constraints, and loading data into the database.
4. Testing - verifying that the database meets its requirements and performs as expected under realistic load conditions.
5. Deployment - releasing the database to production and making it available to users.
6. Maintenance and Evolution - performing ongoing backups, performance tuning, and schema modifications as business requirements change.
7. Retirement and Replacement - decommissioning the database when it reaches the end of its useful life and migrating to a replacement system.

Choose modeling, migration, monitoring, and backup tools appropriate to the target DBMS. Test recovery procedures as well as successful inserts and queries. In iterative development, these activities repeat as requirements evolve.

Database life cycle vs. systems development life cycle

The DBLC focuses on the database and its data. The broader systems development life cycle also covers application interfaces, services, and other system components. Their requirements, testing, and deployment activities must be coordinated.