Conceptual & Logical Database Design (Week 12 Test) Flashcards
What is the purpose of a design methodology?
To support and facilitate the process of design using procedures, techniques, tools, and documentation aids.
What are the three main phases of database design?
- Conceptual database design
- Logical database design
- Physical database design
What is the aim of conceptual database design?
To build the conceptual representation of the database, including identifying important entities, relationships, and attributes.
What is the output of conceptual database design?
ER Diagram
What is the aim of logical database design?
To translate the conceptual representation to the logical structure of the database, which includes designing the relations.
What is the output of logical database design?
Relational Database Schema
What is the aim of physical database design?
To decide how the logical structure is to be physically implemented in the target DBMS.
What does physical database design output?
Actual implemented database
What is the process of constructing a model of data used in an enterprise independent of physical considerations called?
Conceptual database design
What is the process of producing a description of the implementation of the database on secondary storage?
Physical database design
What are the steps involved in building a conceptual data model?
- 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
What is normalisation in the context of logical database design?
A process to validate relations to ensure they are structured correctly.
What is the difference between the term ‘relation’ and ‘relationship’ in the context of databases?
‘Relation’ refers to a table in a relational database, while ‘relationship’ refers to associations between entities in an ER model.
What are the types of attributes that can exist in an ER diagram?
- Simple
- Composite
- Multi-valued
What are weak entities in database design?
Entities that are existence-dependent on some other entity.
What is a surrogate key in database design?
A simpler key used to replace a cumbersome composite primary key.
How is a many-to-many binary relationship represented in a database?
By creating a separate relation to represent the relationship and including foreign keys from both participating entities.
What does the term ‘mapping’ refer to in logical database design?
The process of converting entities and their attributes from an ER diagram to a set of relations.
What is the primary key composed of when mapping weak entities?
Partial identifier of weak entity and primary key of identifying relation (strong entity).
What is the role of foreign keys in database relations?
To link records in one relation to records in another, representing relationships between entities.
What attributes are included in a relation representing a multi-valued attribute?
The primary key of the entity and the multi-valued attribute as a new relation.
What is the first step in logical database design?
Derive relations for the local logical data model.
Fill in the blank: The output of physical database design is the _______.
actual implemented database
True or False: The output of logical database design is an ER diagram.
False
What must be checked during the validation of a logical data model?
Relations against user transactions.
What type of attributes should be used when mapping composite attributes?
Only their simple, component attributes.
What is the process of designing physical representation in database design?
Translating the global logical data model for the target DBMS.
How is a 1:1 relationship represented when there is mandatory participation on both sides?
Combine the entities into one relation
In a 1:1 relationship with optional participation on both sides, how is the parent and child entity designated?
Arbitrarily, based on which is closer to being mandatory
In a 1:1 relationship with mandatory participation on one side, how is the parent and child identified?
The entity with optional participation is the parent
What is the approach for mapping unary (recursive) relationships in a one-to-many scenario?
Create a recursive foreign key in the same relation
What is the approach for mapping unary (recursive) relationships in a many-to-many scenario?
Create two relations: one for the entity type and one for an associative relation
What is the first phase of database design methodology?
Conceptual database design
What is the second phase of database design methodology?
Logical database design
What is the third phase of database design methodology?
Physical database design
What are the three main phases of database design methodology?
Conceptual database design, Logical database design, Physical database design
These phases outline the progression from initial concept to implementation.
What is the purpose of creating a logical data model from an ER conceptual data model?
To structure the data in a way that can be effectively implemented in a database system
It involves defining the relationships and attributes in detail.
What types of attributes are mapped in database design?
Simple, composite, and multivalued attributes
Each type of attribute has different implications for data storage and retrieval.
What is a weak entity in database design?
An entity that cannot be uniquely identified by its own attributes alone
Weak entities rely on a ‘strong’ entity for their identification.
What are the types of binary relationships in database design?
1:1, 1:*, :
These relationships define how entities interact with each other.
What is a ternary relationship in database design?
A complex relationship involving three entities
It is more complex than binary relationships and requires careful mapping.
What does mapping unary (recursive) relationships involve?
Mapping relationships where an entity is related to itself
This is common in hierarchical data structures.
