Logical Database Design Flashcards
___________ is a named, two-dimensional table of data.
RELATION
Tables consists of rows (__________) and columns (_____________)
records and attribute or field
Requirements for a table to qualify as a relation
[1] It must have a unique name
[2] Every attribute value must be atomic
[3] Every row must be unique
[4] Attributes in tables must have unique names
[5] The order of the columns must be relevant
[6] The order of the rows must be irrelevant
All relations are in _______________________.
1st Normal Form
_____________ (tables) correspond with entity types and with many-to-many relationship types.
RELATIONS
_________________ correspond with entity instances and with many-to-many relationship instances
ROWS
______________correspond with attributes
COLUMNS
The word relation (in relational database) is the same as the word relationship in (E-R model)
True or False?
FALSE
These are unique identifiers of relation in question
PRIMARY KEYS
What are some examples of foreign keys?
(1) employee numbers
(2) social security numbers
_________________ are how we can guarantee that all rows are unique
PRIMARY KEYS
_____________ are identifiers that enable a dependent relation on (the many side of a relationship) to refer to its parent relation (on the one side of the relationship)
FOREIGN KEYS
Keys can be simple but not composite
FALSE. Simple or Composite.
________ usually are used as indexes to speed up the response to user queries
KEYS
List three integrity constraints.
- Domain constraints
- Entity integrity
- Action assertions
This is an integrity constraint the comprises the allowable values for an attribute.
DOMAIN CONSTRAINT
This is a integrity constraint wherein no primary key attribute may be null. All primary key fields must have data.
ENTITY INTEGRITY
This integrity constraint is best described as business rules.
ACTION ASSERTIONS
This is an integrity constraint that states that any foreign key value (on the relation of the many side) must match a primary key value in the relations of the one side
REFERENTIAL INTEGRITY
An example of an integrity constraint is “Delete Rules.” This is comprised of three rules, these being:
- Restrict
- Cascade
- Set-to-Null
This “Delete Rule” does not allow the delete of the “parent” side if related rows exist in “dependent side.”
RESTRICT
This “Delete Rule” automatically deletes “dependent” side rows that correspond with the “parent” side row to be deleted.
CASCADE
This “Delete Rule” sets the foreign key in the dependent side to null if deleting from the parent side.
SET-TO-NULL
Set-to-Null is preferably used in weak entities.
FALSE. Set-to-Null is not allowed for weak entities
Referential integrity constraints are drawn via ____________ from dependent to parent table.
ARROWS
Referential constraints are implemented with foreign key to primary key references.
True or False?
TRUE
[Mapping Regular Entities to Relations]
How do we map simple attributes?
E-R attributes are mapped directly onto the relation. These are just copied.
[Mapping Regular Entities to Relations]
How do we map composite attributes?
Use only their simple, component attributes
[Mapping Regular Entities to Relations]
How do we map multivalued attributes?
Multivalued attributes become a separate relation with foreign key taken from the superior entity.
________________ enforce domain integrity constraints
DOMAIN DEFINITIONS
How do we map weak entities?
Weak entities become a separate relation with a foreign key taken from a superior entity
In weak entities, primary keys are composed of:
- partial identifier of weak entity
- primary key of identifying entity (strong entity)
What are the three ways of mapping binary relationships?
One-to-many
Many-to-many
One-to-one
This is a binary relationship wherein the primary key on one side becomes a foreign key on the many side.
ONE-TO-MANY
This is a binary relationship which creates a new relations with the primary keys of the two entities as its primary key
MANY-TO-MANY
This is a binary relationship wherein the primary key on the mandatory side becomes a foreign key on the optional side.
ONE-TO-ONE
What are the two ways of mapping associative entities:
- Identifier not assigned
- Identifier assigned
This is a way of mapping associative entities wherein the default primary key for the association relation is composed of the primary keys of the two entities
IDENTIFIER NOT ASSIGNED
This is a way of mapping associative entities which is natural and familiar to end-users, although the default identifier may not be unique
IDENTIFIER ASSIGNED
The domain constraint for the foreign key should not allow null value if dependent is a weak entity.
True or False?
TRUE
Often in 1:1 relationships, twenty directions is optimal
FALSE. One direction is optimal
What are the two ways of mapping unary relationships?
- One-to-many
- Many-to-many
How do we describe the one-to-many method of mapping unary relationships?
Recursive foreign key in the same relation
There are two relations for the many-to-many method of mapping unary relationships
- One for the entity type
- One for the associative relation in which the primary key has two attributes, both taken from the primary key of the entity
True or False?
TRUE
What are the ways of mapping ternary relationships?
- One relation for each entity and one for the associative entity
- Associative entity has foreign keys to each entity in the relationship
In mapping, supertype attributes (including identifier and subtype discriminator) go into ________________
SUPERTYPE RELATION
Primary key of supertype relation also becomes primary key of subtype relation.
True or False?
TRUE
2:1 relationship is established between supertype and each subtype, with supertype as primary table
True or False?
FALSE. 1:1 relationship
This is primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data.
DATA NORMALIZATION
It is the process of decomposing relations with anomalies to produce smaller, well-structured relations
DATA NORMALIZATION
It is a relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies
WELL-STRUCTURED RELATIONS
This an anomaly that results from adding new rows that forces user to create duplicate data
INSERTION ANOMALY
This is an anomaly that results from deleting rows that may cause a loss of data that would be needed for other future rows
DELETION ANOMALY
This is an anomaly that happens when changing data in a row forces changes to other rows because of duplication
MODIFICATION ANOMALY
A table should pertain to more than one entity type.
True or False?
False. A table should not pertain to more than one entity type
This is the case when the value of one attribute (the determinant) determines the value of another attribute
FUNCTIONAL DEPENDENCY
This is an attribute that determines the value of another attribute
DETERMINANT
This is a unique identifier. One of the ________________ will become the primary key.
CANDIDATE KEY
Each non-key field is functionally independent from every candidate key.
True or False?
FALSE. Each non-key field is functionally dependent on every candidate key
The _________________ is a state wherein there are no multivalued attributes and every attribute value is atomic.
FIRST NORMAL FORM
All relations are in the first normal form.
True or False?
TRUE
If a new product is ordered for order 1007 of existing customer, customer data must be re-entered, causing duplication.
What kind of anomaly is this?
INSERTION
If we delete the Dining Table from Order 1006, we lose information concerning this item’s finish and price.
What kind of anomaly is this?
DELETION
Changing the price of product ID 4 requires update in several records.
What kind of anomaly is this?
UPDATE
What is one reason that these anomalies exist?
(See Figure 5-2b for reference)
Because there are multiple themes (entity types) in one relation. This results in duplication and an unnecessary dependency between the entities
This describes a form wherein every non-key attribute is functionally dependent on the entire primary key
SECOND NORMAL FORM [1NF PLUS]
Every non-key attribute must only be defined by part of the key, not the entire key.
True or False?
FALSE. Every non-key attribute must be defined by the entire key, not by only part of the key.
This is a functional dependency in which one or more non-key attributes are functionally dependent on part (but not all) of the primary key
PARTIAL FUNCTIONAL DEPENDENCY
No transitive dependencies. Non-key determinant with transitive dependencies go into new table; non-key determinant becomes primary key in the new table and stays as a foreign key in the old table.
THIRD NORMAL FORM (3NF)
These are called transitive, because the primary key is a determinant for another attribute which in turn is determinant for a third
TRANSITIVE DEPENDENCIES
A functional dependency between the primary key and one or more non-key attributes that are dependent on the primary key via another non-key attribute
TRANSITIVE DEPENDENCY
A table that contains one or more repeating groups.
Unnormalized Form (UNF)
A relation in which the intersection of each row and column contains one and only one value.
First Normal Form (1NF)
A relation that is in first normal form and every non-primary-key attribute is fully functionally dependent on the primary key.
Second Normal Form (2NF)
A relation that is in first and normal form and in which no non-primary-key attribute is transitively dependent on the primary key.
Third Normal Form (3NF)
This is a normal form without any multivalued dependencies
FOURTH NORMAL FORM
This is a normal form without any remaining anomalies
FIFTH NORMAL FORM
