ISM 4212 Final Exam Study Guide | Database
Design and Implementation | Questions and
Answers with Rationales
1. What is the primary objective of Conceptual Database Design?
A) To write optimized SQL queries for specific vendor platforms (e.g., PostgreSQL,
Oracle)
B) To create a high-level model of data requirements independent of any specific
DBMS software or hardware implementation
C) To define physical storage files, tablespaces, and disk allocations
D) To configure network routers and firewall rules for the database server
Answer: B
Rationale: Conceptual design focuses entirely on capturing organizational data
needs and entities using models like Entity-Relationship (ER) diagrams, without
worrying about physical or logical implementation details.
2. What distinguishes Logical Database Design from Conceptual Database
Design?
A) Logical design defines the specific DBMS data model (e.g., relational,
document) and maps entities into tables, columns, and foreign keys, remaining
independent of specific database vendor software.
B) Logical design deals strictly with physical hard drive block allocations.
C) Logical design is performed before requirements gathering.
D) Logical design focuses exclusively on writing front-end user interface code.
Answer: A
Rationale: Logical design takes the conceptual model and translates it into a
structured schema (like relational tables), specifying data types and keys without
tying it to a single proprietary engine.
,3. What is Physical Database Design?
A) The process of sketching user interviews on a physical whiteboard
B) The stage where the logical schema is translated into physical storage
structures, index strategies, partitioning, filegroups, and access paths optimized
for a specific DBMS engine
C) The process of designing physical server room cooling systems
D) Defining entity-relationship cardinalities
Answer: B
Rationale: Physical design tailors the database to the target hardware and DBMS
software, tuning performance via indexes, storage layout, and clustering.
4. What is an Entity in the Entity-Relationship (ER) model?
A) A specific SQL query execution plan
B) A discrete object or concept in the real world (e.g., Customer, Product, Order)
that has independent existence and can be uniquely identified
C) A column data type restriction
D) A database transaction log backup file
Answer: B
Rationale: Entities represent the core "things" or objects that organizations want
to track in a database.
5. What is an Entity Type versus Entity Instance?
A) An entity type is the collection of similar entities (e.g., Employee table
definition); an entity instance is a single occurrence of that type (e.g., a specific
employee record like John Doe).
B) Entity types are stored in RAM; entity instances are stored on disk.
C) There is no difference; they are synonyms.
D) An entity type is a foreign key; an instance is a primary key.
,Answer: A
Rationale: Analogous to object-oriented programming classes and objects, an
entity type is the schema definition, while an instance is the actual row data.
6. What is an Attribute in database modeling?
A) A rule restricting user login privileges
B) A characteristic or property that describes an entity (e.g., FirstName, BirthDate,
Price)
C) A link between two tables
D) A stored procedure execution error
Answer: B
Rationale: Attributes define the specific data fields captured for each entity type.
7. What is a Composite Attribute?
A) An attribute composed of multiple sub-parts that can be further subdivided
(e.g., Address consisting of Street, City, ZipCode)
B) An attribute shared across multiple tables
C) A primary key combined with a foreign key
D) An attribute that accepts null values exclusively
Answer: A
Rationale: Composite attributes group related sub-attributes into a single logical
unit.
8. What is a Derived Attribute?
A) An attribute whose value is calculated or derived dynamically from other
attributes rather than stored permanently (e.g., Age calculated from BirthDate)
B) An attribute inherited from a parent table via foreign key
C) A primary key generated automatically by an auto-increment sequence
D) An encrypted column value
, Answer: A
Rationale: Derived attributes save storage and prevent data anomalies by
computing values on the fly or via views.
9. What is Relationship Degree in ER modeling?
A) The physical temperature of the database server room
B) The number of entity types participating in a relationship (e.g., unary/degree 1,
binary/degree 2, ternary/degree 3)
C) The number of foreign keys in a table
D) The cardinality ratio of a join operation
Answer: B
Rationale: Degree refers to the count of distinct entity sets connected by a single
relationship association.
10. What is a Ternary Relationship?
A) A relationship involving exactly three distinct entity types (e.g., Supplier, Part,
and Project)
B) A relationship with three primary keys
C) A relationship allowing three levels of user access security
D) A binary relationship split into three tables
Answer: A
Rationale: Ternary relationships link three independent entity sets
simultaneously, requiring careful cardinality analysis during design.
11. What is Cardinality Ratio in ER modeling?
A) The number of columns in a table
B) The maximum and minimum number of relationship instances an entity can
participate in (e.g., 1:1, 1:N, M:N)
C) The ratio of indexed columns to total columns
Design and Implementation | Questions and
Answers with Rationales
1. What is the primary objective of Conceptual Database Design?
A) To write optimized SQL queries for specific vendor platforms (e.g., PostgreSQL,
Oracle)
B) To create a high-level model of data requirements independent of any specific
DBMS software or hardware implementation
C) To define physical storage files, tablespaces, and disk allocations
D) To configure network routers and firewall rules for the database server
Answer: B
Rationale: Conceptual design focuses entirely on capturing organizational data
needs and entities using models like Entity-Relationship (ER) diagrams, without
worrying about physical or logical implementation details.
2. What distinguishes Logical Database Design from Conceptual Database
Design?
A) Logical design defines the specific DBMS data model (e.g., relational,
document) and maps entities into tables, columns, and foreign keys, remaining
independent of specific database vendor software.
B) Logical design deals strictly with physical hard drive block allocations.
C) Logical design is performed before requirements gathering.
D) Logical design focuses exclusively on writing front-end user interface code.
Answer: A
Rationale: Logical design takes the conceptual model and translates it into a
structured schema (like relational tables), specifying data types and keys without
tying it to a single proprietary engine.
,3. What is Physical Database Design?
A) The process of sketching user interviews on a physical whiteboard
B) The stage where the logical schema is translated into physical storage
structures, index strategies, partitioning, filegroups, and access paths optimized
for a specific DBMS engine
C) The process of designing physical server room cooling systems
D) Defining entity-relationship cardinalities
Answer: B
Rationale: Physical design tailors the database to the target hardware and DBMS
software, tuning performance via indexes, storage layout, and clustering.
4. What is an Entity in the Entity-Relationship (ER) model?
A) A specific SQL query execution plan
B) A discrete object or concept in the real world (e.g., Customer, Product, Order)
that has independent existence and can be uniquely identified
C) A column data type restriction
D) A database transaction log backup file
Answer: B
Rationale: Entities represent the core "things" or objects that organizations want
to track in a database.
5. What is an Entity Type versus Entity Instance?
A) An entity type is the collection of similar entities (e.g., Employee table
definition); an entity instance is a single occurrence of that type (e.g., a specific
employee record like John Doe).
B) Entity types are stored in RAM; entity instances are stored on disk.
C) There is no difference; they are synonyms.
D) An entity type is a foreign key; an instance is a primary key.
,Answer: A
Rationale: Analogous to object-oriented programming classes and objects, an
entity type is the schema definition, while an instance is the actual row data.
6. What is an Attribute in database modeling?
A) A rule restricting user login privileges
B) A characteristic or property that describes an entity (e.g., FirstName, BirthDate,
Price)
C) A link between two tables
D) A stored procedure execution error
Answer: B
Rationale: Attributes define the specific data fields captured for each entity type.
7. What is a Composite Attribute?
A) An attribute composed of multiple sub-parts that can be further subdivided
(e.g., Address consisting of Street, City, ZipCode)
B) An attribute shared across multiple tables
C) A primary key combined with a foreign key
D) An attribute that accepts null values exclusively
Answer: A
Rationale: Composite attributes group related sub-attributes into a single logical
unit.
8. What is a Derived Attribute?
A) An attribute whose value is calculated or derived dynamically from other
attributes rather than stored permanently (e.g., Age calculated from BirthDate)
B) An attribute inherited from a parent table via foreign key
C) A primary key generated automatically by an auto-increment sequence
D) An encrypted column value
, Answer: A
Rationale: Derived attributes save storage and prevent data anomalies by
computing values on the fly or via views.
9. What is Relationship Degree in ER modeling?
A) The physical temperature of the database server room
B) The number of entity types participating in a relationship (e.g., unary/degree 1,
binary/degree 2, ternary/degree 3)
C) The number of foreign keys in a table
D) The cardinality ratio of a join operation
Answer: B
Rationale: Degree refers to the count of distinct entity sets connected by a single
relationship association.
10. What is a Ternary Relationship?
A) A relationship involving exactly three distinct entity types (e.g., Supplier, Part,
and Project)
B) A relationship with three primary keys
C) A relationship allowing three levels of user access security
D) A binary relationship split into three tables
Answer: A
Rationale: Ternary relationships link three independent entity sets
simultaneously, requiring careful cardinality analysis during design.
11. What is Cardinality Ratio in ER modeling?
A) The number of columns in a table
B) The maximum and minimum number of relationship instances an entity can
participate in (e.g., 1:1, 1:N, M:N)
C) The ratio of indexed columns to total columns
