Deck 5: Database Design: Normalization

Question 1

Which of the following is not a problem with duplicated data?

A) increased storage
B) decreased performance
C) inconsistencies in the data
D) decreased time in running queries

Answer 1

D

Question 2

Which of the following is an example of an insertion anomaly?

A) A new student cannot be assigned to a professor because the student does not have a StudentID.
B) A department name must be changed manually in several records.
C) When professors retire, their records are removed from the database, which also loses information about the students they advised.
D) A student can take several classes, and classes can enroll many students.

Answer 2

A

Question 3

A(n) _____ results if you cannot add data to the database due to absence of other data.

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

Answer 3

D

Question 4

What do you call a table that contains multiple field entries in a single column?

A) unnormalized relation
B) composite key
C) Boyce-Codd normal form
D) lookup table

Question 5

A(n) _____ is a piece of data that cannot be meaningfully divided.

A) attribute
B) composite key
C) atomic value
D) tuple

Question 6

How do you free data from the problems associated with insertion, update, and deletion anomalies?

A) Apply algorithms to identify the anomalies.
B) Remove the functional dependencies from the data.
C) Normalize the data by converting a single list into multiple tables.
D) Identify atomic values in the data.

Question 7

_____ refers to how attributes (fields) depend on or relate to other attributes in a relation (table).

A) Data anomaly
B) Object dependence
C) Functional dependence
D) Functional relationships

Question 8

_____ follows a series of steps to break columns into smaller tables to identify entities and facts about each entity.

A) Functional dependence
B) Online analytical processing
C) Structural modification
D) Normalization

Question 9

_____ describes the relationship of attributes in an entity.

A) Transitive dependency
B) Relation anomaly
C) Boyce-Codd normal form
D) Functional dependence

Question 10

What is a major goal of normalization?

A) minimizing redundant data
B) clarifying field names
C) increasing functional independence
D) selecting suitable primary keys

Question 11

A table is in first normal form when it does not contain repeating groups, each column contains atomic values, and there are no duplicate records.

Question 12

Which of the following statements describes a table in first normal form?

A) It contains no repeating groups.
B) It has at least two foreign keys.
C) It doesn't have a primary key
D) It has no candidate keys.

Question 13

What is the problem with designing a database in which the last name of an employee functionally determines the employee's address?

A) This design has no problems.
B) Two employees could be living at the same address.
C) More than one employee could have the same last name.
D) The address will automatically functionally determine the employee's last name.

Question 14

In the following Projects table, which field is functionally dependent on the ClientID field? Projects (ProjectID, ProjectType, ProjectName, ProjectManager, Budget, ClientID)

A) ProjectID
B) ProjectManager
C) Budget
D) None of the fields in the Projects table is functionally dependent on ClientID.

Question 15

An Employee field with an employee's full name contains an atomic value.

Question 16

Data stored in a single list often creates redundant data when _____.

A) the list is not sorted
B) the list contains multiple subjects or topics
C) the list is used for looking up data
D) the list contains atomic values

Question 17

The normalization process is a series of steps built into most relational database management software products that will automatically restructure your database to resolve all design flaws.

Question 18

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

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

Question 19

Functional dependence refers to how tables are related to each other in a database.

Question 20

The primary key can be a composite key, which is a combination of two or more fields.

Question 21

Which of the following tables should be converted to second normal form?

A) Employees (EmpID, EmpFirst, EmpLast, Department)
B) Visits (VisitID, VisitType, VisitDate, PatientID, LengthInMinutes)
C) Players (PlayerID, PlayerFirst, PlayerLast, CoachID, CoachFirst, CoachLast, Team)
D) Bookings (BookingID, CheckInDate, CheckOutDate, RoomID, CustomerID)

Question 22

A table is in second normal form when it is in first normal form and _____.

A) each nonkey column is dependent on the entire primary key field
B) it contains candidate keys
C) it allows no data modification anomalies
D) it contains at least one foreign key field

Question 23

What do you call a table that contains a repeating group?

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

Question 24

When the primary key field consists of a combination of two or more fields, it is called a _____.

A) combo key
B) nonkey field
C) candidate key
D) composite key

Question 25

What type of query should you create to populate new EmployeeFirst and EmployeeLast fields with atomic values from an EmployeeName field?

A) select query
B) update query
C) concatenation query
D) atomic query

Question 26

A table with no repeating groups in any attribute and atomic values in each column is, at a minimum, in what normal form?

A) third normal form
B) first normal form
C) second normal form
D) The table is unnormalized.

Question 27

Which normal form has an additional condition that excludes transitive dependencies?

A) 1NF
B) 2NF
C) 3NF
D) 4NF

Question 28

Which of the following pairs has the clearest field name and atomic value?

A) DistanceInMiles, 20
B) Length, 10 feet
C) Time, 1.5
D) Amount, 12 per package

Question 29

What is an algorithm?

A) a piece of data that cannot be meaningfully divided
B) a defined set of steps to solve a problem
C) a characteristic or property of an entity
D) a problem generated when entering, updating, and deleting data

Question 30

Which of the following expressions combines the atomic values from two fields in a LastName, FirstName format in an expression in an Access query?

A) [LastName] > ", " < [FirstName]
B) "LastName" & "FirstName"
C) [FirstName] & " " & [LastName]
D) [LastName] & ", " & [FirstName]

Question 31

Which of the following tables should be converted to third normal form?

A) Clients (ClientID, ClientFirst, ClientLast, ProjectID, ProjectType)
B) Departments (DeptID, DeptName)
C) Products (ProductID, CategoryID, ProductName, UnitPrice)
D) Classes (ClassID, ClassName, ClassDate, StartTime, EndTime, ClassPrice)

Question 32

Third normal form is also often referred to as _____.

A) ultimate normal form
B) Boyce-Codd normal form
C) SQL normal form
D) Edgar-Hopper normal form

Question 33

Which type of table is an excellent candidate for improvement to second normal form?

A) table with duplicate records
B) lookup table
C) table with a multifield primary key field
D) table with an AutoNumber primary key field

Question 34

A(n) _____ occurs if a nonkey attribute determines another nonkey attribute.

A) insertion anomaly
B) unnormalized relation
C) composite key
D) transitive dependency

Question 35

The nonkey fields of a table should be functionally dependent on _____.

A) the foreign key field
B) the primary key field
C) at least one field in a related table
D) an unnormalized relation

Question 36

What do you call each key that meets the criteria for a primary key?

A) candidate key
B) potential key
C) lookup key
D) functional key

Question 37

What helps separate atomic values from a single column if the values follow a pattern?

A) conversion anomaly
B) Codd's 12 rules
C) algorithm
D) transitive dependency

Question 38

Which Access function returns the position of the space character in a field?

A) InStr
B) Space
C) Len
D) Null

Question 39

Which of the following is a benefit of having atomic values in each column of a table?

A) The data is easier to sort, find, and filter.
B) Atomic values prevent deletion anomalies.
C) The data is functionally independent.
D) The data can be organized into repeating groups.

Question 40

What is a nonkey column?

A) a column that contains a null value
B) a column that is not part of the foreign key
C) a column that is not part of the primary key
D) a column that does not contain an atomic value

Question 41

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)

Question 42

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

Question 43

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

Question 44

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

A) 4NF
B) 3NF
C) 2NF
D) 1NF

Question 45

Describe the purpose of the normalization process.

Question 46

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

Question 47

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

Question 48

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

Question 49

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

Question 50

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