Deck 5: Database Design 1: Normalization

Question 1

What is a potential problem, if any, if you design a database in which the last name of a consultant functionally determines the consultant's address?

A) There is no problem with this design.
B) There could be two consultants living at the same address.
C) There could be more than one consultant with the same last name.
D) The address will automatically functionally determine the consultant's last name.

Answer 1

C

Question 2

What do you call a data inconsistency that results from data redundancy or the use of inappropriate nulls?

A) update anomaly
B) insertion anomaly
C) deletion anomaly
D) relational anomaly

Answer 2

A

Question 3

If B is functionally dependent on A, what can you also say?

A) A functionally determines B
B) A functionally determines another attribute in the table
C) B functionally determines A
D) B does not determine any other attribute

Answer 3

A

Question 4

Which of the following can be defined as a column (or collection of columns) A such that all other columns are functionally dependent on A and no subcollection of the columns in A has this property?

A) functional key
B) composite key
C) primary key
D) declared key

Question 5

The normalization process is a series of steps that will automatically restructure your database to resolve design flaws.

Question 6

Which term's definition also defines a candidate key?

A) functional key
B) repeating group
C) primary key
D) nonkey column

Question 7

Converting to third normal form always avoids the problems related to dependencies.

Question 8

Tables without repeating groups are said to be in first normal form.

Question 9

A table is in fourth normal form (4NF) when it is in third normal form and there are no multivalued dependencies.

Question 10

Third normal form is sometimes referred to as the Boyce-Codd normal form.

Question 11

Any row that determines another row is called a determinant.

Question 12

A table that is in first normal form may contain problems that will require you to restructure it.

Question 13

Tables that are in second normal form can still contain redundancy problems.

Question 14

A column is a nonkey column when it is part of the primary key.

Question 15

If the primary key of a table contains only a single column, the table is automatically in third normal form.

Question 16

An interrelation constraint is a condition that involves two or more relations.

Question 17

To correct anomalies in a database, tables must be converted into various types of normal forms.

Question 18

Functional dependencies can always be determined by looking at sample data.

Question 19

When converting a non-first normal form table to first normal form, the foreign key will usually include the original foreign key concatenated with the key to the repeating group.

Question 20

If column B is functionally dependent on column A, you can also say that column B functionally determines column A.

Question 21

If there is more than one possible choice for the primary key, and one of the possibilities is chosen to be the primary key. What are the others called?

A) canceled keys
B) alternate keys
C) nonkey attributes
D) contributory keys

Question 22

What is the difference between a third normal form and fourth normal form table?

A) A 4NF table has no alternate keys.
B) A 4NF table has no foreign keys.
C) A 4NF table has no multivalued dependencies.
D) A 4NF table has no primary dependencies.

Question 23

Which normal form has an additional condition that the only determinants the table contains are candidate keys?

A) First
B) Second
C) Third
D) Fourth

Question 24

What is each key that meets the criteria for a primary key called?

A) alternate key
B) candidate key
C) functional key
D) normal key

Question 25

What is a table that contains a repeating group called?

A) normalized relation
B) unnormalized relation
C) nominal relation
D) non-nominal relation

Question 26

The ____________________ process enables you to identify the existence of potential problems in the design of a database.

Question 27

With the table Orders (OrderNum, OrderDate, (ItemNum, NumOrdered) ) where OrderNum is the primary key, how would you convert the table to first normal form?

A) by creating a second table with just the columns ItemNum and NumOrdered
B) by changing the primary key to OrderDate
C) by making ItemNum a foreign key
D) by making the primary key a combination of OrderNum and ItemNum

Question 28

Which of the following is a column or collection of columns on which all columns in the table are functionally dependent?

A) index key
B) candidate key
C) major key
D) special key

Question 29

Second normal form can be defined as a table that is in first normal form but that contains no ____.

A) partial dependencies
B) alternate keys
C) nonkey columns
D) interrelation constraints

Question 30

If you convert a collection of tables to an equivalent third normal form collection of tables, what problems do you remove?

A) those arising from multivalued dependencies
B) those arising from functional dependencies
C) those arising from inappropriate null values
D) those arising from incomplete deletions

Question 31

What type of diagram uses arrows to indicate all the functional dependencies present in a table?

A) interrelation diagram
B) functional diagram
C) dependency diagram
D) index diagram

Question 32

When the ____________________ of a table is a single column, the table is automatically in second normal form.

Question 33

What is an alternate key?

A) foreign key
B) primary key
C) column that could be a primary key but was not chosen
D) row that could be a primary key but was not chosen

Question 34

A column B is ____________________ on another column A if each value for A in the database is associated with exactly one value of B.

Question 35

A column is a nonkey column if it is not a part of the ____________________.

Question 36

Which of the following is a characteristic of a table that is in first normal form?

A) It doesn't contain repeating groups.
B) It has at least two foreign keys.
C) It doesn't have a primary key
D) There are no alternate keys.

Question 37

When is a 1NF table automatically a 2NF table?

A) when two or more columns make up the primary key
B) when there are no interrelation constraints
C) when there are only nonkey columns
D) when the primary key contains only a single column

Question 38

A(n) ________________ key is a candidate key not chosen ad the primary key.

Question 39

What is a nonkey column?

A) A column that is not part of the primary key.
B) A column that has a null value.
C) A column that is part of a repeating group.
D) A column that contains a numeric value.

Question 40

Which of the following contains a repeating group where the primary key is OrderNum?

A) Orders (OrderNum, OrderDate, (ItemNum, NumOrdered) )
B) Orders (OrderNum, OrderDate, ItemNum, NumOrdered )
C) Orders (OrderNum, OrderDate)
D) Orders (OrderNum, ItemNum, NumOrdered )

Question 41

Describe the normalization process.

Question 42

Describe the procedure for converting a table to the third normal form.

Question 43

When you normalize a table by breaking down the relations into progressively finer levels of detail, it is called?_____________________.

Question 44

A table that contains a ________________ that is not a candidate key indicates the table is not in third normal form.

Question 45

A table is in fourth normal form when it is in third normal form and there are no ________________ dependencies.

Question 46

Any column or collection of columns that determines another column is called a(n) ___________________.

Question 47

What is the difference between a table in first normal form and one in second normal form?

Question 48

Discuss what normal forms are and list the most common normal forms.

Question 49

An interrelation ____________________ is a condition that involves two or more relations.

Question 50

Discuss how candidate keys, primary key, and alternate keys are related.