Database Design Stages Notes

Database Design Stages

Why Abstraction Levels

  • It is easier to start database design with more abstract levels and then work towards more details.

  • Three Levels:

    • Conceptual model

    • Logical model

    • Physical model

Different Definitions

  • Different people have different definitions of what goes into each model.

  • Following textbook descriptions.

  • Feature comparison across models:

    • Entity Names: Yes for Conceptual, Logical, and Physical

    • Entity Relationships: Yes for Conceptual, Logical, and Physical

    • Attributes: Yes for Conceptual, Logical, and Physical

    • Primary Keys: Yes for Logical and Physical

    • Foreign Keys: Yes for Logical and Physical

    • Table Names: Yes for Logical and Physical

    • Column Names: Yes for Physical

    • Column Data Types: Yes for Physical

Conceptual Level

  • The highest level of abstraction.

  • Focuses on the high-level organization of data without concern for how the data will be stored or retrieved.

  • Defines entities (things about which information is stored) and relationships (associations between entities) in a general way.

  • Aims to be understandable by non-technical stakeholders.

  • Tools often used for conceptual modeling include Entity-Relationship Diagrams (ERDs).

  • Attributes can be left out at this level, but some people include them without types.

  • This model should still be valid even when using MongoDB.

Conceptual Model Example

  • Patient Meets with Doctor Belongs to Department Undergoes Procedure

Logical Model

  • A more detailed exploration that includes specifics about the structure of the data without being tied to a particular database management system (DBMS).

  • Expands on the conceptual model to include attributes (data we record about entities), primary keys (unique identifiers), and foreign keys (identifiers that establish relationships).

  • Resolve many-to-many relationships using bridging tables.

  • Still somewhat abstract, as it doesn't get into specifics about how the data will be stored in the database.

  • We usually do not worry about the types of attributes at this stage.

Example of a Logical Model

  • Appointment

    • patient_id (PK,FK)

    • doctor_id (PK,FK)

    • date (PK)

  • Doctor

    • doctor_id (PK)

    • name

    • credentials

    • department_id (FK)

  • Department

    • department_id (PK)

    • name

    • location

  • Patient

    • patient_id (PK)

    • name

    • dob

  • Undergoes

    • patient_id (PK,FK)

    • procedure_id (PK,FK)

    • date (PK)

  • Procedure

    • procedure_id (PK)

    • name

    • price

Physical Model

  • The most detailed level of database design, specifying exactly how data will be stored in the database.

  • Includes all tables, columns, data types, constraints, indexes, and relationships defined in the logical model, but tailored to the specifics of the chosen DBMS.

  • This is where performance considerations, storage details, and access methods are defined.

  • Some database systems include the ability to store data in different drives (SSD vs Mechanical Hard drives); this would be decided here.

MySQL Workbench Limitations

  • Workbench is Physical Modeling

  • N:M relations are always resolved as a bridge table automatically

  • You cannot create relations in Workbench unless the tables have primary keys

MySQL Symbols

  • Yellow key: attribute is a primary key.

  • Red key: attribute is a foreign key and part of the primary key.

  • Blue diamond: mandatory column (not null)

  • Open blue diamond: optional column

  • Red diamond: mandatory foreign key (not null)

  • Open red diamond: optional foreign key

Relationship Participation

  • A patient can have 0 or more appointments

  • An appointment must have 1 doctor

  • The | and O in the line end indicates the minimum number. The relationship says that a patient could have many appointments (including zero) but each appointment is for a single patient.