Relational Constructs   «Prev  Next»

Lesson 2 Database Life Cycle
Objective Describe where we are in the database life cycle.

The Database Life Cycle consists of Five Stages

As mentioned in other areas of this site, the (DBLC) database life cycle consists of five stages:
  1. Requirements Analysis
  2. Logical Design
  3. Physical Design
  4. Implementation
  5. Monitoring, Modification, and Maintenance

In the first stage of the DBLC, Requirements Analysis, you determine what data your database will need to hold and what tasks it will need to perform to meet the needs of your users. You identify
  1. business objects: (things in the business environment that need to be represented in the database) and
  2. business rules: (restrictions on how users perceive and use data).

DBLC consisting of 1. Requirements Analysis, 2. Logical Design, 3. Physical Design, 4. Implementation, 5. Modification, Maintenance
DBLC consisting of 1. Requirements Analysis, 2. Logical Design, 3. Physical Design, 4. Implementation, 5. Modification, Maintenance


First phase of the Logical Design Stage

In the first phase of the Logical Design stage you convert the business objects and their characteristics into entities[1] and attributes [2]. You then create 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 first course in this series covered Requirements Analysis and the Logical Design stage through the creation of the ER diagram. The next phase in the Logical Design stage of the DBLC is to normalize[5] the base tables you created in the ER diagram, so you can store and retrieve information in the database efficiently.

Uses of the term Database Table

The term table takes on a dual meaning when we talk about database design. On the one hand, the elements comprising an ER diagram are referred to as base tables. These are represented as
  1. entities and
  2. attributes, which represent the business objects
to be included in the database, and are the sort of tables during the process of relational database design. They come into play during the Logical Design phase of the (DBLC) database lifye cycle. We also use the term table to refer to the relational constructs comprising the implementation of the database. These tables will be described in greater detail later in this module.

What are Base Tables

In the context of entity-relationship diagrams (ERDs) within the field of data modeling, the term "base tables" typically refers to the fundamental tables that represent the core entities in the database design. These base tables are derived from the entities identified in the ERD and form the backbone of the relational database schema. An entity-relationship diagram is a visual representation of the data model, showcasing entities, their attributes, and the relationships between them. When this conceptual model is translated into a relational database design, the entities become base tables. Key characteristics and considerations regarding base tables might include:
  1. Representation of Entities: Each base table typically represents a single entity from the ERD. For instance, in a database for a university system, you might have entities like "Student," "Course," and "Instructor," each of which would be represented as a base table in the relational database.
  2. Attributes as Columns: The attributes of an entity in the ERD translate into the columns of the corresponding base table. These columns hold the data for the various attributes defined in the ER model.
  3. Primary Keys: Each base table generally includes a primary key, which is a unique identifier for the records in the table. This primary key is often an attribute or a combination of attributes that are unique to each entity instance.
  4. Relationships as Foreign Keys: Relationships between entities in an ERD are represented in base tables through the use of foreign keys. These are columns in a table that link to the primary key of another table, thereby establishing a relationship between the two tables.
  5. Normalization Considerations: The process of defining base tables often involves normalization, which is the process of structurally optimizing the database to reduce redundancy and improve data integrity. Base tables are typically designed to be in at least the first normal form (1NF).
  6. Physical Implementation: While the ERD provides a conceptual model, the base tables represent the physical implementation of this model in a specific database management system. The design and characteristics of these tables can be influenced by the capabilities and constraints of the database system being used.

In summary, base tables in the context of entity-relationship diagrams are the direct translation of entities into tables within a relational database. They embody the structure, attributes, and relationships defined in the ERD and are central to the implementation and operation of the database system. The careful design of these base tables is crucial for ensuring the effectiveness, efficiency, and integrity of the database.


Purpose and Function of a Database Table

In the realm of relational database design, a database table serves a fundamental and multifaceted role. It is the primary structure where data is stored and organized in a database. Imagine a table as a matrix composed of rows and columns or a spreadsheet. Each row, known as a record, represents a single, distinct entry or item. Each column, on the other hand, corresponds to a specific attribute or field, defining the data type and nature of information that can be stored in that column. The purpose of a database table extends beyond mere storage. It is designed to efficiently manage and retrieve data. Tables allow for the organization of data in a way that supports both simplicity and complexity in querying, updating, and managing data. They form the backbone of a relational database, where the key concept is the relation, which is a set of tuples sharing the same attributes. This relational model enables the establishment of relationships between tables through the use of foreign keys, enhancing the database's capability to represent complex real-world relationships among data entities.
Moreover, database tables are governed by a set of rules and constraints to ensure data integrity and accuracy. These include primary keys, which uniquely identify each record, and foreign keys, which establish a link between tables. Constraints like ‘NOT NULL’, ‘UNIQUE’, and ‘CHECK’ ensure that the data adheres to specified rules, thus maintaining the quality and reliability of the data stored. In summary, the purpose and function of a database table in the context of relational database design are to systematically store data in an organized, efficient, and structurally sound manner. This organization not only facilitates easy data retrieval and manipulation but also ensures the integrity and accuracy of the data through various constraints and relational mechanisms.

DBLC is specific to the DBMS

The database life cycle is a set of stages that a database goes through, from initial planning and design to retirement and replacement. While the stages of the database life cycle are generally the same across different database management systems (DBMS), the specific tools, methods, and processes used in each stage may differ depending on the DBMS.
The stages of the database life cycle include:
  1. Planning and Requirements Analysis: This stage involves defining the purpose of the database, identifying the requirements of the users and stakeholders, and creating a conceptual data model.
  2. Design: In this stage, the conceptual data model is refined into a logical data model, which is used to create the physical schema of the database.
  3. Implementation: The physical schema is implemented using the DBMS, including creating tables, indexes, and constraints, and loading data into the database.
  4. Testing: The database is tested to ensure that it meets the requirements and performs as expected.
  5. Deployment: The database is deployed to production and made available to users.
  6. Maintenance and Evolution: The database is maintained over time, including performing backups, tuning performance, and making changes to the schema as needed.
  7. Retirement and Replacement: Eventually, the database may reach the end of its useful life and need to be retired and replaced with a new database.
While the stages of the database life cycle are generally the same across different DBMS, the specific tools, methods, and processes used in each stage may differ depending on the DBMS. For example, some DBMS may have different options for creating the physical schema or for tuning performance. Additionally, some DBMS may offer additional tools or features for managing the database over its life cycle, such as automated backup and recovery or schema migration tools.

The next lesson introduces tables, describing their role in relational theory.
[1]Entity: A single stand-alone unit or a business object about which data are stored in a database; usually synonymous with a database table.
[2]Attribute: A characteristic of an entity; data that identifies or describes an entity.
Usually represented as a column in a table, attributes store data values.
[3]entity-relationship (ER) diagram: A diagram used during the design phase of database development to illustrate the organization of and relationships between data during database design.
[4]relationship: If the same attribute occurs in more than one table, a relationship exists between those two tables.
[5]normalize: To break up large tables into smaller, more efficient tables without losing any information.

SEMrush Software