Conceptual & Logical Database Design (Week 12 Test)

Question 1

What is the purpose of a design methodology?

Answer 1

To support and facilitate the process of design using procedures, techniques, tools, and documentation aids.

Question 2

What are the three main phases of database design?

Answer 2

• Conceptual database design
• Logical database design
• Physical database design

Question 3

What is the aim of conceptual database design?

Answer 3

To build the conceptual representation of the database, including identifying important entities, relationships, and attributes.

Question 4

What is the output of conceptual database design?

Answer 4

ER Diagram

Question 5

What is the aim of logical database design?

Answer 5

To translate the conceptual representation to the logical structure of the database, which includes designing the relations.

Question 6

What is the output of logical database design?

Answer 6

Relational Database Schema

Question 7

What is the aim of physical database design?

Answer 7

To decide how the logical structure is to be physically implemented in the target DBMS.

Question 8

What does physical database design output?

Answer 8

Actual implemented database

Question 9

What is the process of constructing a model of data used in an enterprise independent of physical considerations called?

Answer 9

Conceptual database design

Question 10

What is the process of producing a description of the implementation of the database on secondary storage?

Answer 10

Physical database design

Question 11

What are the steps involved in building a conceptual data model?

Answer 11

• Identify entity types
• Identify relationship types
• Identify and associate attributes with entity or relationship types
• Determine attribute domains
• Determine candidate, primary and alternate key attributes
• Consider use of enhanced modelling concepts (optional step)
• Check model for redundancy

Question 12

What is normalisation in the context of logical database design?

Answer 12

A process to validate relations to ensure they are structured correctly.

Question 13

What is the difference between the term ‘relation’ and ‘relationship’ in the context of databases?

Answer 13

‘Relation’ refers to a table in a relational database, while ‘relationship’ refers to associations between entities in an ER model.

Question 14

What are the types of attributes that can exist in an ER diagram?

Answer 14

• Simple
• Composite
• Multi-valued

Question 15

What are weak entities in database design?

Answer 15

Entities that are existence-dependent on some other entity.

Question 16

What is a surrogate key in database design?

Answer 16

A simpler key used to replace a cumbersome composite primary key.

Question 17

How is a many-to-many binary relationship represented in a database?

Answer 17

By creating a separate relation to represent the relationship and including foreign keys from both participating entities.

Question 18

What does the term ‘mapping’ refer to in logical database design?

Answer 18

The process of converting entities and their attributes from an ER diagram to a set of relations.

Question 19

What is the primary key composed of when mapping weak entities?

Answer 19

Partial identifier of weak entity and primary key of identifying relation (strong entity).

Question 20

What is the role of foreign keys in database relations?

Answer 20

To link records in one relation to records in another, representing relationships between entities.

Question 21

What attributes are included in a relation representing a multi-valued attribute?

Answer 21

The primary key of the entity and the multi-valued attribute as a new relation.

Question 22

What is the first step in logical database design?

Answer 22

Derive relations for the local logical data model.

Question 23

Fill in the blank: The output of physical database design is the _______.

Answer 23

actual implemented database

Question 24

True or False: The output of logical database design is an ER diagram.

Answer 24

False

Question 25

What must be checked during the validation of a logical data model?

Answer 25

Relations against user transactions.

Question 26

What type of attributes should be used when mapping composite attributes?

Answer 26

Only their simple, component attributes.

Question 27

What is the process of designing physical representation in database design?

Answer 27

Translating the global logical data model for the target DBMS.

Question 28

How is a 1:1 relationship represented when there is mandatory participation on both sides?

Answer 28

Combine the entities into one relation

Question 29

In a 1:1 relationship with optional participation on both sides, how is the parent and child entity designated?

Answer 29

Arbitrarily, based on which is closer to being mandatory

Question 30

In a 1:1 relationship with mandatory participation on one side, how is the parent and child identified?

Answer 30

The entity with optional participation is the parent

Question 31

What is the approach for mapping unary (recursive) relationships in a one-to-many scenario?

Answer 31

Create a recursive foreign key in the same relation

Question 32

What is the approach for mapping unary (recursive) relationships in a many-to-many scenario?

Answer 32

Create two relations: one for the entity type and one for an associative relation

Question 33

What is the first phase of database design methodology?

Answer 33

Conceptual database design

Question 34

What is the second phase of database design methodology?

Answer 34

Logical database design

Question 35

What is the third phase of database design methodology?

Answer 35

Physical database design

Question 36

What are the three main phases of database design methodology?

Answer 36

Conceptual database design, Logical database design, Physical database design ## Footnote These phases outline the progression from initial concept to implementation.

Question 37

What is the purpose of creating a logical data model from an ER conceptual data model?

Answer 37

To structure the data in a way that can be effectively implemented in a database system ## Footnote It involves defining the relationships and attributes in detail.

Question 38

What types of attributes are mapped in database design?

Answer 38

Simple, composite, and multivalued attributes ## Footnote Each type of attribute has different implications for data storage and retrieval.

Question 39

What is a weak entity in database design?

Answer 39

An entity that cannot be uniquely identified by its own attributes alone ## Footnote Weak entities rely on a 'strong' entity for their identification.

Question 40

What are the types of binary relationships in database design?

Answer 40

1:1, 1:*, *:* ## Footnote These relationships define how entities interact with each other.

Question 41

What is a ternary relationship in database design?

Answer 41

A complex relationship involving three entities ## Footnote It is more complex than binary relationships and requires careful mapping.

Question 42

What does mapping unary (recursive) relationships involve?

Answer 42

Mapping relationships where an entity is related to itself ## Footnote This is common in hierarchical data structures.