Database Design Process

Jul 17, 2024

Database Design Process Overview

Requirements Collection and Analysis

  • Interview or ask users about their requirements.
  • Document data and functional requirements.
    • Data requirements: concise documentation.
    • Functional requirements: user-defined operations (data retrieval and update).

Conceptual Schema Creation

  • Use high-level/conceptual data model.
  • Definition:
    • Conceptual schema: brief description of data requirements, entity types, relationships, and constraints.
    • Easier to understand and communicate with non-technical users.
  • Functional requirements specify basic operations.

Logical Design

  • Implementation of the database using commercial DBMS (e.g., MS SQL Server, Oracle).
  • Transform conceptual schema to implementation data model.
  • Result: Database schema in the implementation data model of DBMS.

Physical Design

  • Specify internal storage structures, indexes, access paths, and files.
  • Design and implement application programs as transactions.

Weak and Strong Entity Types

  • Weak Entity Types: No key attributes. Identified by relating to another entity (Identifying Entity Type/Owner).
    • Example: Dependent.
  • Strong Entity Types: Have key attributes.

ER Diagram Symbols

  • Entity: Rectangle (Strong entity).
  • Weak Entity: Double rectangle.
  • Attribute: Oval.
  • Key Attribute: Oval with underline.
  • Multi-valued Attribute: Double oval.
  • Composite Attribute: Can be divided into further attributes.
  • Derived Attribute: Dashed oval (value derived from another attribute).
  • Identifying Relationship: Double diamond (relationship with weak entity and owner).
  • Relationship: Single diamond (relationship between strong entities).

Example Database: Company

Requirements Collection for Company Database

  1. Organized into departments with unique names, numbers, and managers.
  2. Departments control projects (with unique names, numbers, and locations).
  3. Store employees' details (name, unique SSN, gender, salary, projects, and dependents).
  4. Employees' dependents (name, gender, relationship, birth date).

Initial Conceptual Design

Entity Types

  1. Department
  • Attributes: Department name (key), Department number (key), locations (multi-valued), manager, manager start date.
  1. Project
  • Attributes: Project name (key), project number (key), location, controlling department.
  1. Employee
  • Attributes: Employee name (first, middle, last), SSN (key), gender, salary, birth date, department, projects (hours worked).
  1. Dependent (Weak Entity)
  • Attributes: Dependent name (partial key), gender, birth date, relationship to employee.

In the next video: Learn about relationships and ER diagrams in detail.