D426 Database Design Phases

The choice of table structure and the creation of indexes are key activities in which phase of database design?

Conceptual Design

Logical Design

Physical Design 

Indexation

Physical Design

Discovery is a step in which phase?

Conceptual design

Logical design

Database design

Physical design

Conceptual design

Discovery is the first step of the conceptual design phase.

In practice, database design is not always sequential. Often an early step is revisited after a later step is completed. T/F

True.

Which design phase creates an ER (Entity-Relationship) model?

Conceptual design

Which design phase?: Develops an entity-relationship model, capturing data requirements while ignoring implementation details.

Conceptual design

Conceptual design considers implementation issues related to a specific database system. T/F?

False. Conceptual design ignores implementation details for any database system.

An entity-relationship model is developed for all database design projects. T/F?

An entity-relationship model is developed in the conceptual design phase. Conceptual design is sometimes omitted for simple databases with just a few users and tables.

Conceptual design is sometimes omitted for simple databases with just a few users and tables. T/F?

True.

Which design phase?: Converts the entity-relationship model into tables, columns, and keys for a particular database system.

Logical design

Which design phase?: Adds indexes and specifies how tables are organized on storage media.

Physical design

This step belongs to which design phase? Which step number?: Discover entities, relationships, and attributes

Conceptual design, step 1.

A hospital system identifies that it needs to track patients, doctors, and appointments. Which phase is this?

Conceptual design

This step belongs to which design phase? Which step number?: Determine cardinality

Conceptual design, step 2.

This step belongs to which design phase? Which step number?: Distinguish strong and weak entities

Conceptual design, step 3.

This step belongs to which design phase? Which step number?: Create supertype and subtype entities

Conceptual design, step 4.

This step belongs to which design phase? Which step number?: Implement entities

Logical design, step 5.

This step belongs to which design phase? Which step number?: Implement relationships

Logical design, step 6.

This step belongs to which design phase? Which step number?: Implement attributes

Logical design, step 7.

This step belongs to which design phase? Which step number?: Apply normal form

Logical design, step 8.

A system decides that one doctor can have many appointments, but each appointment belongs to one doctor. What phase is this? What is being determined?

Conceptual design (cardinality step)

A designer draws a diagram showing Customers linked to Orders with relationships but no tables yet. Which phase is this?

Conceptual design

A system identifies that “Employee” is a weak entity because it depends on “Department.” Which phase and step is this?

Conceptual design, step 3: Distinguish strong and weak entities

A database design separates “Person” into “Student” and “Instructor.” Which phase and step is being applied?

Conceptual design, step 4: Create supertype and subtype entities

An ER diagram with Students and Courses is converted into Student(StudentID, Name) and Course(CourseID, Title). Which phase is this?

Logical design. Students and Courses are entities that are being turned into tables. Step 5, implement entities.

A many-to-many relationship between Students and Courses becomes a table called Enrollment(StudentID, CourseID). Which phase is this?

Logical design.

👉 You are converting a relationship into a table. Step 6, implement relationships.

A system adds StudentID as a foreign key inside an Enrollment table. Which phase is this?

Logical design

A table has repeated groups like multiple phone numbers in one column, and it is split into separate tables. What phase is this and what is happening?

Logical design (normalization)

Normalization is exactly where you:

• remove repeating groups

• eliminate non-atomic values (like lists inside a column)

• structure data into proper relational form

A database removes duplicate customer address data by splitting into Customer and Address tables. Which phase is this?

Logical design

Normalization is exactly where you:

• remove repeating groups

• eliminate non-atomic values (like lists inside a column)

• structure data into proper relational form

A designer ensures every column contains atomic values (no lists like “Red, Blue, Green”). Which phase is this?

Logical design. This is talking about normalization (1NF) which is part of logical design.

Normalization is where you:

• remove repeating groups

• eliminate non-atomic values (like lists inside a column)

• structure data into proper relational form

A database adds an index on the CustomerName column to speed up search queries. Which phase is this?

Physical design

A table is stored using a B-tree index to improve sorting and lookup speed. Which phase is this?

Physical design

A large Orders table is split into partitions by year (2024, 2025, 2026). Which phase is this?

Physical design

A database administrator chooses to store frequently accessed tables on SSD instead of HDD. Which phase is this?

Physical design

A system creates clustered indexes to physically order rows by primary key. Which phase is this?

Physical design

A query is slow, so an index is added to improve performance without changing table structure. Which phase is this?

Physical design