Question 1

Which of the following tables is most likely a lookup table?
A) Students (StudentID, StudentFirst, StudentLast)
B) Specialties (Specialty)
C) Vehicles (VehicleID, VehicleType, VehicleMake, VehicleModel)
D) Orders (OrderID, OrderDate)

Accepted Answer

A lookup table typically contains a list of distinct values that are used as foreign keys in other tables. The Specialties table fits this definition, as it likely contains a list of specialties that could be assigned to employees or projects in other tables. The other tables listed do not fit this definition as cleanly. Students and Orders may have relationships with other tables, but neither contains a distinct set of values that are used repeatedly as foreign keys. Vehicles is similar, but depending on the context, may include duplicated values (e.g. multiple vehicles could have the same make and model) which would make it less suitable as a lookup table.

Question 2

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

Accepted Answer

The answer of What is the difference between a table...

Question 3

Define third normal form and give an example of a transitive dependency.

Accepted Answer

The answer of Define third normal form and give an...

Question 4

Relational databases are often implemented on tables in what normal form?
A) 4NF
B) 3NF
C) 2NF
D) 1NF

Accepted Answer

Relational databases are often implemented in the third normal form (3NF) to minimize data redundancy and anomalies. This means that each table in the database should only contain data values that are directly related to the primary key of the table and that there should be no repeating groups or partial dependencies. 1NF, 2NF, and 4NF are also normal forms that can be used depending on the specific requirements of the database design.

Question 5

Describe the purpose of the normalization process.

Accepted Answer

The answer of Describe the purpose of the normalization process....

Question 6

A(n) _____ helps constrain the values in a single field to a specific list, which eliminates update anomalies.
A) candidate list
B) attribute
C) lookup table
D) functional dependency

Accepted Answer

A lookup table is used to constrain the values in a single field to a specific list, which eliminates update anomalies. This means that the field can only contain values from the list in the lookup table, preventing any invalid or inconsistent data from being entered.

Question 7

What is the foreign key in the following table? Classes (ClassID, ClassName, ClassDate, StartTime, EndTime, ClassPrice, InstructorNum)
A) ClassID
B) ClassName
C) InstructorNum
D) ClassPrice

Accepted Answer

The foreign key in this table is InstructorNum, as it refers to the primary key in another table (presumably a table of instructors). The other columns are not referencing another table and therefore cannot be foreign keys.

Question 8

Discuss how a primary key, candidate key, and composite key are related.

Accepted Answer

The answer of Discuss how a primary key, candidate key,...

Question 9

Which normal form has additional conditions that further remove data redundancies and eliminate data modification anomalies beyond the Boyce-Codd normal form?
A) 1NF
B) 2NF
C) 3NF
D) 4NF

Accepted Answer

The choice is 4NF, which is a higher normal form than the Boyce-Codd normal form (BCNF). It further eliminates data redundancies and data modification anomalies by requiring that no non-key attribute is functionally dependent on any set of attributes other than a candidate key. This means that a table in 4NF must have multiple candidate keys, and non-key attributes can only be dependent on a subset of those keys. This additional condition ensures that the table is normalized to the highest level possible and reduces the risk of data inconsistencies and errors.

Question 10

Discuss the overall process of normalization as it relates to anomalies and list the most common normal forms.

Accepted Answer

The answer of Discuss the overall process of normalization as...

Quiz 5: Database Design: Normalization

Which of the following tables is most likely a lookup table?

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

Define third normal form and give an example of a transitive dependency.

Relational databases are often implemented on tables in what normal form?

Describe the purpose of the normalization process.

A(n) _____ helps constrain the values in a single field to a specific list, which eliminates update anomalies.

What is the foreign key in the following table? Classes (ClassID, ClassName, ClassDate, StartTime, EndTime, ClassPrice, InstructorNum)

Discuss how a primary key, candidate key, and composite key are related.

Which normal form has additional conditions that further remove data redundancies and eliminate data modification anomalies beyond the Boyce-Codd normal form?

Discuss the overall process of normalization as it relates to anomalies and list the most common normal forms.