Database Design: Integrity, Life Cycle, Views, and Schema

Posted on Mar 30, 2025 in Computers

Data Integrity and Completeness

Tell me everything you know about the integrity of the data in separate files.

The problem of data integrity is ensuring that the information in the database is correct. Values of the data stored in files must be valid and meaningful (logical data) and subject to certain restrictions and certain types of format constraints.

Examples:

  • That the stock of a particular item is never negative.
  • That if the maximum weekly work hours under a particular convention is 35 hours, in no case should that number of hours be exceeded.
  • That an employee’s entry date into the company is not earlier, for example, than their date of birth.

The solution to this would involve adding the appropriate code to the various application programs. However, sometimes it is difficult to modify these programs when adding new conditions or constraints. It is further complicated when the constraints involve different data files.

Database Life Cycle Stages

List the stages of a database life cycle:

  1. Study of feasibility, collection, and analysis of requirements.
  2. Requirements definition.
  3. Design:
    1. Conceptual Design
    2. Logical Design
    3. Physical Design
  4. Development of prototypes.
  5. Implementation and database validation.
  6. Testing and data loading.
  7. Operation and maintenance.

Partial and Global Database Views

Comment on everything you know about partial views within the overall DBMS architecture.

The main problem that arises in the design of a database (DB) is the concept of views, i.e., the various perspectives that can be held on the database. Views may be partial or global.

Partial views are divided into 3 groups:

  1. From the viewpoint of a program: The view consists of the data that this software can use.
  2. From the viewpoint of an application: The view is the combination of data and programs for that application.
  3. From the viewpoint of a user: The view is the combination of data from all applications that the user can access.

The global view can be of 3 types:

  1. From the organizational perspective: The database is independent of the technology used. This corresponds to the conceptual design stage.
  2. From the viewpoint of analysts and programmers: It depends on the technology to be used, i.e., the Database Management System (DBMS) to be employed. This corresponds to the logical design.
  3. From the viewpoint of the database administrator: How the DB is stored physically. This corresponds to the physical design.

Understanding the Canonical Schema

What is a canonical schema and why is it important?

The Canonical Schema corresponds to the viewpoint of programmers and analysts (logical design). This canonical schema gives rise to all the external schemas.

Starting from the real world, we implement the conceptual framework. Building on this, we derive the canonical schema, which leads to the physical layout and also to all the external schemas for all users.

As seen previously, each user’s external schema will result in corresponding external schemas for applications, and in turn, each of these will lead to different external schemas for the programs.

The canonical schema is implemented, corresponds to the logical design, and within this, we find relational, hierarchical, and network data models.