Gyanrush
Start freeOpen Gyanrush
Start freeOpen Gyanrush
  1. LPU MCA Semester 1
  2. CAP570
  3. Unit 1

CAP570 · Unit 1: Basics of database

Conceptual, logical and physical database design

10 practice questions · in the MTE and ETE syllabus

Practise this topicPractise this topic

Key points

  • Conceptual design is DBMS independent
  • Logical design produces tables and keys
  • Physical design chooses indexes and file organisation
  • Order is requirements, conceptual, logical, physical

Database design moves from abstract to concrete in phases.

  1. Requirements analysis collects what users need.
  2. Conceptual design builds a DBMS-independent model, usually an ER diagram, of entities, attributes and relationships.
  3. Logical design maps that model to the structure of a chosen data model, for a relational system tables with primary and foreign keys, then refines them by normalisation.
  4. Physical design chooses file organisation, indexes, partitioning and storage parameters for performance on a particular DBMS.

Each phase hides detail from the one above it, so a physical change such as adding an index leaves the tables and the ER model untouched. The conceptual model can be reused across products such as Oracle and PostgreSQL, whereas the physical design is product-specific.

Exam trap: primary keys belong to logical design, indexes to physical design.

Try two questions

Question 1 · Tier 3, Apply

A designer converts an ER diagram into tables and chooses primary keys. Which phase is this?

  1. Conceptual design
  2. Physical design
  3. Logical design
  4. Requirements analysis
Show the answer

C. Mapping an ER model to relations and keys is logical design.

Why not A: That phase produces the ER diagram itself.

Why not B: That phase chooses storage and indexes.

Why not D: That phase collects needs before any modelling.

Question 2 · Tier 4, Analyse

A team changes a table's file organisation and adds an index but leaves the tables and keys alone. Which design levels were affected?

  1. Only physical design
  2. Conceptual and logical design
  3. Logical design only
  4. All three levels equally
Show the answer

A. File organisation and indexes are physical design elements, so higher levels are untouched.

Why not B: Neither the ER model nor the tables changed.

Why not C: The tables and keys stayed the same.

Why not D: Only storage-related choices changed.