Database Design and ER Flashcards
T or F. In a total generalisation, every occurrence of the parent entity is always a occurrence of one of the child entities.
True.
T or F. Logical design and conceptual design should be carried out simultaneously (at the same time) when designing a database.
False.
T or F. Natural join is equivalent to where clause join of TableA.Attribute=TableB.Attribute
False.
T or F. The SQL statement DELETE FROM A removes table A from the database schema.
False.
T or F. If the cardinality of an attribute is equal to (0,1), the cardinality can be omitted in the Entity Relationship Diagram.
False.
T or F. If the cardinality of an attribute is equal to (1,1), the cardinality can be omitted in the Entity-Relationship diagram.
True.
T or F. During logical design, every redundancy in the input ER schema must be removed before the translation to a relational schema.
False.
T or F. An attribute with a cardinality of [0,1] cannot be used as part of an identifier.
True.
T or F. Not all entities must have explicit cardinalities for each of their relationships.
False.
What is database design?
Database design is the process of defining the structure, characteristics, and contents of a database according to specified requirements.
Name 6 stages of DB design.
Feasibility study
Collection & analysis of requirements
Design
Implementation
Validation & Testing
Operation
What are the three phases of the DB design process?
Conceptual design
Logical design
Physical design
Define entity-relationship model.
The Entity Relationship Model is a conceptual data model which provides a set of constructs capable of describing the data requirements of an application in a way that is easy to understand and independent of the criteria for the management and organisation of data on the system.
What do the following represent in the ER model?
(a) Rectangle
(b) Diamond
(c) line with circle on one end
(d) ______
( )
______
(a) Entity
(b) Relationship
(c) Attribute
(d) Composite attribute
What do the following represent in the ER model?
(a) Attribute with circle coloured in black within entity.
(b) Attribute with circle coloured in black to a relationship.
(c) Arrow with black top
(a) Internal identifier (primary key)
(b) External identifier (foreign key, can also be primary key that uses attribute from other entity)
(c) Generalisation
What are entities?
Entities represent classes of objects that have properties in common and an autonomous existence.
What are relationships?
Relationships represent logical links between two or more entities.
What is a recursive and ternary relationship?
Recursive relationship with itself
Ternary relationship involves three entities.
What are attributes?
Attributes describe the elementary properties of entities or relationships.
What are composite attributes?
Attributes with closely connected meanings or uses.
What cardinality can be omitted in the ER diagram?
(1,1)
T or F. An idenitfier can have cardinality (0, 1)
False.
T or F. An entity can have more than one identifier in ER diagram.
True.
T or F. Each entity must have at least one (internal or external) identifier.
True
T or F. An external identifier can not involve an entity that is in its turn identified externally.
False.
T or F. Cycles of external identifiers are allowed.
False.
What is a generalisation?
E (parent) is a general version of specific (child) entities E1,E2,…,En.
Distinguish between total and partial generalisation. How are they distinguished on ER diagram?
A generalisation is total if every occurrence of the parent entity is also an occurrence of one of the child entities, otherwise it is partial.
A fully coloured in arrow vs partial which only has top coloured in.
Further classify partial generalisation into exclusive and overlapping. Formally and informally.
Exclusive: If every occurence of the parent entity is an occurence of at most one of the child entities.
Overlapping otherwise.
E.g. In an exclusive generalisation all the possible child entities are accounted for.
Downsides of ER schema. Name three.
Names of concepts may be insufficient to convey their meaning.
It may be difficult to represent all attributes of every entity and relationship.
Some data properties and constraints are difficult to be represented.
Name 5 steps of conceptual design.
Standardise sentence structure.
Avoid complex phrases.
Identify synonyms and homonyms, and standardise terms.
Make cross-references explicit.
Construct a glossary of terms.
Name four properties of a good conceptual schema.
Correctness
Completeness
Readability
Minimality
Name the two steps of logical design.
Restructuring of the ER schema
Translation into the logical model
Name four steps of the restructuring step.
Analysis of redundancies
Removing generalisations
Partitioning/merging of entities and relationships
Selection of primary identifiers.
What is a redundancy?
Corresponds to a piece of information that can be derived from other data.
Give one advantage or disadvantage of redundancies.
D: Redundancies can help reduce data access costs.
A: Redundancies can increase storage requirements and require additional operations to maintain data consistency.
T or F. Some generalisations can be left in during logical design.
False.
How may generalisations be removed from data?
By merging the child entities with the parent entity and introducing a new attribute to represent type.
T or F. A primary identifier is better to be an external one rather than an internal one.
False.
When making an associative table for a one to many relationship which entity is used as a primary key for the new table?
One part of one to many is used as key. Both should be present.
