Logical Database Design

Question 1

___________ is a named, two-dimensional table of data.

Answer 1

RELATION

Question 2

Tables consists of rows (__________) and columns (_____________)

Answer 2

records and attribute or field

Question 3

Requirements for a table to qualify as a relation

Answer 3

[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

Question 4

All relations are in _______________________.

Answer 4

1st Normal Form

Question 5

_____________ (tables) correspond with entity types and with many-to-many relationship types.

Answer 5

RELATIONS

Question 6

_________________ correspond with entity instances and with many-to-many relationship instances

Answer 6

ROWS

Question 7

______________correspond with attributes

Answer 7

COLUMNS

Question 8

The word relation (in relational database) is the same as the word relationship in (E-R model)

True or False?

Answer 8

FALSE

Question 9

These are unique identifiers of relation in question

Answer 9

PRIMARY KEYS

Question 10

What are some examples of foreign keys?

Answer 10

(1) employee numbers
(2) social security numbers

Question 11

_________________ are how we can guarantee that all rows are unique

Answer 11

PRIMARY KEYS

Question 12

_____________ 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)

Answer 12

FOREIGN KEYS

Question 13

Keys can be simple but not composite

Answer 13

FALSE. Simple or Composite.

Question 14

________ usually are used as indexes to speed up the response to user queries

Answer 14

KEYS

Question 15

List three integrity constraints.

Answer 15

1. Domain constraints
2. Entity integrity
3. Action assertions

Question 16

This is an integrity constraint the comprises the allowable values for an attribute.

Answer 16

DOMAIN CONSTRAINT

Question 17

This is a integrity constraint wherein no primary key attribute may be null. All primary key fields must have data.

Answer 17

ENTITY INTEGRITY

Question 18

This integrity constraint is best described as business rules.

Answer 18

ACTION ASSERTIONS

Question 19

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

Answer 19

REFERENTIAL INTEGRITY

Question 20

An example of an integrity constraint is “Delete Rules.” This is comprised of three rules, these being:

Answer 20

1. Restrict
2. Cascade
3. Set-to-Null

Question 21

This “Delete Rule” does not allow the delete of the “parent” side if related rows exist in “dependent side.”

Answer 21

RESTRICT

Question 22

This “Delete Rule” automatically deletes “dependent” side rows that correspond with the “parent” side row to be deleted.

Answer 22

CASCADE

Question 23

This “Delete Rule” sets the foreign key in the dependent side to null if deleting from the parent side.

Answer 23

SET-TO-NULL

Question 24

Set-to-Null is preferably used in weak entities.

Answer 24

FALSE. Set-to-Null is not allowed for weak entities

Question 25

Referential integrity constraints are drawn via ____________ from dependent to parent table.

Answer 25

ARROWS

Question 26

Referential constraints are implemented with foreign key to primary key references. True or False?

Answer 26

TRUE

Question 27

[Mapping Regular Entities to Relations] How do we map simple attributes?

Answer 27

E-R attributes are mapped directly onto the relation. These are just copied.

Question 28

[Mapping Regular Entities to Relations] How do we map composite attributes?

Answer 28

Use only their simple, component attributes

Question 29

[Mapping Regular Entities to Relations] How do we map multivalued attributes?

Answer 29

Multivalued attributes become a separate relation with foreign key taken from the superior entity.

Question 30

________________ enforce domain integrity constraints

Answer 30

DOMAIN DEFINITIONS

Question 31

How do we map weak entities?

Answer 31

Weak entities become a separate relation with a foreign key taken from a superior entity

Question 32

In weak entities, primary keys are composed of:

Answer 32

- partial identifier of weak entity - primary key of identifying entity (strong entity)

Question 33

What are the three ways of mapping binary relationships?

Answer 33

One-to-many Many-to-many One-to-one

Question 34

This is a binary relationship wherein the primary key on one side becomes a foreign key on the many side.

Answer 34

ONE-TO-MANY

Question 35

This is a binary relationship which creates a new relations with the primary keys of the two entities as its primary key

Answer 35

MANY-TO-MANY

Question 36

This is a binary relationship wherein the primary key on the mandatory side becomes a foreign key on the optional side.

Answer 36

ONE-TO-ONE

Question 37

What are the two ways of mapping associative entities:

Answer 37

1. Identifier not assigned 2. Identifier assigned

Question 38

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

Answer 38

IDENTIFIER NOT ASSIGNED

Question 39

This is a way of mapping associative entities which is natural and familiar to end-users, although the default identifier may not be unique

Answer 39

IDENTIFIER ASSIGNED

Question 40

The domain constraint for the foreign key should not allow null value if dependent is a weak entity. True or False?

Answer 40

TRUE

Question 41

Often in 1:1 relationships, twenty directions is optimal

Answer 41

FALSE. One direction is optimal

Question 42

What are the two ways of mapping unary relationships?

Answer 42

1. One-to-many 2. Many-to-many

Question 43

How do we describe the one-to-many method of mapping unary relationships?

Answer 43

Recursive foreign key in the same relation

Question 44

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?

Answer 44

TRUE

Question 45

What are the ways of mapping ternary relationships?

Answer 45

- One relation for each entity and one for the associative entity - Associative entity has foreign keys to each entity in the relationship

Question 46

In mapping, supertype attributes (including identifier and subtype discriminator) go into ________________

Answer 46

SUPERTYPE RELATION

Question 47

Primary key of supertype relation also becomes primary key of subtype relation. True or False?

Answer 47

TRUE

Question 48

2:1 relationship is established between supertype and each subtype, with supertype as primary table True or False?

Answer 48

FALSE. 1:1 relationship

Question 49

This is primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data.

Answer 49

DATA NORMALIZATION

Question 50

It is the process of decomposing relations with anomalies to produce smaller, well-structured relations

Answer 50

DATA NORMALIZATION

Question 51

It is a relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies

Answer 51

WELL-STRUCTURED RELATIONS

Question 52

This an anomaly that results from adding new rows that forces user to create duplicate data

Answer 52

INSERTION ANOMALY

Question 53

This is an anomaly that results from deleting rows that may cause a loss of data that would be needed for other future rows

Answer 53

DELETION ANOMALY

Question 54

This is an anomaly that happens when changing data in a row forces changes to other rows because of duplication

Answer 54

MODIFICATION ANOMALY

Question 55

A table should pertain to more than one entity type. True or False?

Answer 55

False. A table should not pertain to more than one entity type

Question 56

This is the case when the value of one attribute (the determinant) determines the value of another attribute

Answer 56

FUNCTIONAL DEPENDENCY

Question 57

This is an attribute that determines the value of another attribute

Answer 57

DETERMINANT

Question 58

This is a unique identifier. One of the ________________ will become the primary key.

Answer 58

CANDIDATE KEY

Question 59

Each non-key field is functionally independent from every candidate key. True or False?

Answer 59

FALSE. Each non-key field is functionally dependent on every candidate key

Question 60

The _________________ is a state wherein there are no multivalued attributes and every attribute value is atomic.

Answer 60

FIRST NORMAL FORM

Question 61

All relations are in the first normal form. True or False?

Answer 61

TRUE

Question 62

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?

Answer 62

INSERTION

Question 63

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?

Answer 63

DELETION

Question 64

Changing the price of product ID 4 requires update in several records. What kind of anomaly is this?

Answer 64

UPDATE

Question 65

What is one reason that these anomalies exist? (See Figure 5-2b for reference)

Answer 65

Because there are multiple themes (entity types) in one relation. This results in duplication and an unnecessary dependency between the entities

Question 66

This describes a form wherein every non-key attribute is functionally dependent on the entire primary key

Answer 66

SECOND NORMAL FORM [1NF PLUS]

Question 67

Every non-key attribute must only be defined by part of the key, not the entire key. True or False?

Answer 67

FALSE. Every non-key attribute must be defined by the entire key, not by only part of the key.

Question 68

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

Answer 68

PARTIAL FUNCTIONAL DEPENDENCY

Question 69

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.

Answer 69

THIRD NORMAL FORM (3NF)

Question 70

These are called transitive, because the primary key is a determinant for another attribute which in turn is determinant for a third

Answer 70

TRANSITIVE DEPENDENCIES

Question 71

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

Answer 71

TRANSITIVE DEPENDENCY

Question 72

A table that contains one or more repeating groups.

Answer 72

Unnormalized Form (UNF)

Question 73

A relation in which the intersection of each row and column contains one and only one value.

Answer 73

First Normal Form (1NF)

Question 74

A relation that is in first normal form and every non-primary-key attribute is fully functionally dependent on the primary key.

Answer 74

Second Normal Form (2NF)

Question 75

A relation that is in first and normal form and in which no non-primary-key attribute is transitively dependent on the primary key.

Answer 75

Third Normal Form (3NF)

Question 76

This is a normal form without any multivalued dependencies

Answer 76

FOURTH NORMAL FORM

Question 77

This is a normal form without any remaining anomalies

Answer 77

FIFTH NORMAL FORM