Chapter 4: Logical Database Design and the Relational Model

Question 1

The entity relationship diagram is a logical data model (T or F)

Answer 1

False

Question 2

What is the objective of logical database design?

Answer 2

To transform the conceptual design into a logical database design that can be implemented via a chosen dbms

Question 3

The relational model is based on mathematics (T or F)

Answer 3

True

Question 4

What are the 3 components of a relational data model?

Answer 4

1. Data structure
2. Data manipulation
3. Data integrity

Question 5

A named two-dimensional table
of data

Answer 5

relation

Question 6

is an attribute or a combination of attributes that
uniquely identifies each row in a relation

Answer 6

primary key

Question 7

A primary key that consists of
more than one attribute

Answer 7

composite key

Question 8

What is used to show relationships with relations?

Answer 8

Foreign keys

Question 9

In the shorthand notation, what is used to identify foreign and primary keys?

Answer 9

PK: Solid underline
FK: Dashed underline

Question 10

Properties of Relations: What are the properties of relations?

Answer 10

1. Has a unique name
2. rows and columns are atomic
3. each row is unique
4. each column is unique
5. The sequence of columns are insignificant
6. the sequence of rows is insignificant

Question 11

a description of the overall logical structure of the database

Answer 11

Schema

Question 12

What are the two common methods for expressing a schema

Answer 12

1. short text statements
2. graphical representation

Question 13

Integrity Constraints: What are the three constraints found in relational data models?

Answer 13

1. Domain Constraint
2. Entity Integrity
3. Referential Integrity

Question 14

Integrity Constraints: is the set of values that may be assigned to an attribute

Answer 14

Domain Constraint

Question 15

Integrity Constraints: is design to ensure that every relation has a primary key and that the data values are all valid

Answer 15

Entity Integrity

Question 16

Integrity Constraints: In entity integrity, it ensures that all primary key attributes are?

Answer 16

Non-null

Question 17

Integrity Constraints: A value that may be assigned to an attribute when no other value
applies or when the applicable
value is unknown.

Answer 17

Null

Question 18

Integrity Constraints: A rule that states that either each
foreign key value must match
a primary key value in another
relation or the foreign key value
must be null.

Answer 18

referential integrity constraint

Question 19

Integrity Constraints: In referential integrity, it is allowed to have a null foreign key if?

Answer 19

The relationship is optional

Question 20

Well-Structured Relations: A relation that contains __ ___ and allows users
to __ ___ __ ___the
rows in a table without errors or
inconsistencies.

Answer 20

1. minimal redundancy
2. insert, modify, and delete

Question 21

Well-Structured Relations: Redundancies may lead to?

Answer 21

Anomalies

Question 22

Well-Structured Relations: An error or inconsistency that
may result when a user attempts
to update a table that contains
redundant data

Answer 22

anomaly

Question 23

Well-Structured Relations: What are the three anomalies?

Answer 23

1. Insertion
2. Deletion
3. Modification

Question 24

Well-Structured Relations: Restrictions in composite primary keys when creating new tables are what anomaly?

Answer 24

Insertion Anomaly

Question 25

Well-Structured Relations: When a removal of an attribute leads to a deletion of the whole instance

Answer 25

Deletion Anomaly

Question 26

Well-Structured Relations: When updating needs to be done to every instance is what type of anomaly?

Answer 26

Modification Anomaly

Question 27

Transforming EER Diagrams into Relations: aare entities that have an independent existence and generally represent real-world objects, such as persons and products. It is represented by what in a graphical representation?

Answer 27

Regular Entities. By a solid line box

Question 28

Transforming EER Diagrams into Relations: are entities that cannot exist except with an identifying relationship with an owner (regular) entity type. It is identified in a graphical representation as?

Answer 28

Weak Entities. A double line box

Question 29

Transforming EER Diagrams into Relations: are formed from many-to-many relationships between other entity types. It is identified in a graphical representation with?

Answer 29

Associative Entities. Rounded cornered box

Question 30

Transforming EER Diagrams into Relations: Associative entities are also called?

Answer 30

Gerunds

Question 31

Transforming EER Diagrams into Relations: What are the steps in creating relational data models?

Answer 31

1. Map Regular Entities 2. Map Weak Entities 3. Map Binary Entities 4. Map Associative Entities 5. Map Unary Entities 6. Map Ternary Relationships 7. Map Supertype/Subtype Relationships

Question 32

Transforming EER Diagrams into Relations: What happens to entities in step 1?

Answer 32

They become relations/tables

Question 33

Transforming EER Diagrams into Relations: What will you do if there are composite attributes from an entity?

Answer 33

Separate each attribute into its own columns

Question 34

Transforming EER Diagrams into Relations: What would you do with multivalued attributes?

Answer 34

Create a separate relation for the said attribute, and then use the primary key of the original relation and the ex- multivalued attribute as the primary keys

Question 35

Transforming EER Diagrams into Relations: Nonkey attributes are also called

Answer 35

descriptors

Question 36

Transforming EER Diagrams into Relations: exists only through an identifying relationship with another entity type called the

Answer 36

owner

Question 37

Transforming EER Diagrams into Relations: a weak entity has no complete identified and only uses a?

Answer 37

partial identifier

Question 38

Transforming EER Diagrams into Relations: What do you do when there is a weak and strong entity relationship in your E-R diagram?

Answer 38

The weak entity references the pk of the strong entity and its partial identifier as its primary keys

Question 39

Transforming EER Diagrams into Relations: A serial number or other systemassigned primary key for a relation.

Answer 39

Surrogate primary key

Question 40

Transforming EER Diagrams into Relations: What are the instances when you use a surrogate key?

Answer 40

1. When there is a composite primary key 2. When the natural primary key is inefficient 3. When it cannot be unique in the future

Question 41

Transforming EER Diagrams into Relations: For binary 1:M, what do you do?

Answer 41

Create two relations, wherein the foreign key is placed on the "many" relation

Question 42

Transforming EER Diagrams into Relations: Are foreign keys required to be the same names at the primary key it is referencing?

Answer 42

No. As long as it has the same domain.

Question 43

Transforming EER Diagrams into Relations: In binary m:n, how do you do it?

Answer 43

Create 3 relations, each having their own unique attributes while the third one uses a composite key of both the original relations. (Associative Entity)

Question 44

Transforming EER Diagrams into Relations: In binary 1:1, what do you do?

Answer 44

Create 2 relations, the optional relation has the foreign key reference

Question 45

Transforming EER Diagrams into Relations: When identifiers are not assigned, associative entities use what primary keys?

Answer 45

A composite primary key of the two related relations

Question 46

Transforming EER Diagrams into Relations: When an identifier is assigned to an associative entity, how would the relation look like?

Answer 46

1. The primary key will be the assigned one 2. The primary keys of the other two relations are referenced using foreign keys

Question 47

Transforming EER Diagrams into Relations: In unary 1:M relationships, how would you make it?

Answer 47

1. Create a relation 2. Add a foreign key that references the primary key of the same relation

Question 48

Transforming EER Diagrams into Relations: A foreign key in a relation that references the primary key values of the same relation

Answer 48

recursive foreign key

Question 49

Transforming EER Diagrams into Relations: A unary M:N relationship is made how?

Answer 49

1. Create two relations (one is an associative entity) 2. A composite key that references the PK of the main relation

Question 50

Transforming EER Diagrams into Relations: In a ternary relationship what is the default primary key?

Answer 50

The primary keys of the three relation and partial identifier of the associative entity

Question 51

Normalization: When should you use normalization?

Answer 51

1. During logical database design 2. When reverse-engineering older systems

Question 52

decomposing relations with anomalies

Answer 52

Normalization

Question 53

Normalization: Normalization makes no assumptions about how data will be used in displays (T or F)

Answer 53

True

Question 54

Normalization: is based on normal forms or functional dependencies defines data usage (T or F)

Answer 54

False. Business Rules dapat

Question 55

Normalization: A state of a relation that requires that certain rules regarding relationships between attributes (or functional dependencies) are satisfied

Answer 55

Normal form

Question 56

Steps in Normalization: What is the rules for first normal form?

Answer 56

multivalued attributes have been removed

Question 57

Steps in Normalization: Can intersections in the table be null I the first normal form?

Answer 57

Yes

Question 58

Steps in Normalization: rule for 2nd normal form?

Answer 58

Remove partial functional dependencies

Question 59

Steps in Normalization: rules for 3rd normal form

Answer 59

Transitive dependencies are removed

Question 60

Steps in Normalization: Any remaining anomalies that result from functional dependencies have been removed

Answer 60

Boyce Codd normal form

Question 61

Steps in Normalization: Any multivalued dependencies have been removed

Answer 61

foruth normal form

Question 62

Steps in Normalization: Any remaining anomalies have been removed

Answer 62

Fifth normal form

Question 63

Normalization:A constraint between two attributes in which the value of one attribute is determined by the value of another attribute.

Answer 63

Functional dependency

Question 64

Normalization:The attribute on the left side of the arrow in a functional dependency

Answer 64

Determinant

Question 65

Normalization:An attribute, or combination of attributes, that uniquely identifies a row in a relation

Answer 65

Candidate key

Question 66

Normalization:What are the two properties of Candidate Keys?

Answer 66

1. Unique 2. Nonredundant

Question 67

Normalization:Are all candidate keys determinants? What about the other way around?

Answer 67

All candidate keys are determinants but not all determinants are candidate keys

Question 68

Normalization: What is the preliminary step in normalization?

Answer 68

Get a user view

Question 69

exists when a nonkey attribute is functionally dependent on part (but not all) of the primary key.

Answer 69

partial functional dependency

Question 70

Normalization:A functional dependency between the primary key and one or more nonkey attributes that are dependent on the primary key via another nonkey attribute.

Answer 70

transitive dependency

Question 71

Normalization: How to convert to 3rd normal form?

Answer 71

1. Create a new relation 2. Transfer all related attributes to the non pk 3. Reference PK of new relation to the old relation

Question 72

Merging Relations: Is also called?

Answer 72

View integration

Question 73

Merging Relations: Two (or more) attributes that have different names but the same meaning

Answer 73

synonym

Question 74

Merging Relations:An alternative name used for an attribute.

Answer 74

alias

Question 75

Merging Relations:An attribute that may have more than one meaning

Answer 75

homonym

Question 76

A primary key whose value is unique across all relations.

Answer 76

Enterprise Key

Question 77

Enterprise Key:This criterion makes a primary key more like what in object-oriented databases is called

Answer 77

object identifier

Question 78

Enterprise Key: If a enterprise key is already available, what would happen to the "old pks"

Answer 78

they would become non key attributes