Back to Subject

Unit 3: Database Normalisation - Practice Quiz

CAP570 — Advanced Database Techniques 60 Questions
0 Correct 0 Wrong 60 Left
0/60

1 What does the functional dependency mean?

Functional Dependencies and Anomalies Easy
A. Each value of determines one value of
B. The values of and must always be identical
C. Each value of determines one value of
D. Both and must be primary keys

2 In the dependency , which attribute is the determinant?

Functional Dependencies and Anomalies Easy
A.
B. Neither attribute
C. Both attributes
D.

3 Which anomaly occurs when the same fact must be changed in several rows?

Functional Dependencies and Anomalies Easy
A. Insertion anomaly
B. Update anomaly
C. Selection anomaly
D. Deletion anomaly

4 Which anomaly may prevent adding a new department until at least one employee belongs to it?

Functional Dependencies and Anomalies Easy
A. Update anomaly
B. Deletion anomaly
C. Sorting anomaly
D. Insertion anomaly

5 Which anomaly occurs when deleting a row unintentionally removes the only stored details about another entity?

Functional Dependencies and Anomalies Easy
A. Indexing anomaly
B. Deletion anomaly
C. Update anomaly
D. Insertion anomaly

6 What is a trivial functional dependency?

Functional Dependencies and Anomalies Easy
A. A dependency whose left side contains only candidate-key attributes
B. A dependency that exists only when every table value is unique
C. A dependency whose right side is a subset of its left side
D. A dependency whose right side contains no attributes from the table

7 What is the main cause of insertion, update, and deletion anomalies in poorly designed tables?

Functional Dependencies and Anomalies Easy
A. Strong password policies
B. A large number of users
C. Frequent database backups
D. Excessive data redundancy

8 What is the main purpose of database normalization?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Easy
A. Replace all primary keys
B. Store every attribute in one table
C. Increase duplicated data
D. Reduce redundancy and anomalies

9 Which requirement must a table satisfy to be in First Normal Form (1NF)?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Easy
A. Every table has exactly two columns
B. Every attribute is a foreign key
C. Every non-key attribute determines the primary key through another attribute
D. Every field contains an atomic value

10 Which value would violate 1NF if stored in a single PhoneNumber field?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Easy
A. 555-0101, 555-0102
B. 555-0101
C. 555-0103
D. 555-0104

11 A table must already be in which normal form before it can satisfy Second Normal Form (2NF)?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Easy
A. 3NF
B. BCNF
C. 1NF
D. Fourth Normal Form

12 Second Normal Form (2NF) primarily removes which type of dependency?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Easy
A. Multivalued dependency
B. Trivial dependency
C. A dependency in which every determinant must be a superkey without exception
D. Partial dependency

13 When can a partial dependency occur?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Easy
A. When every field contains NULL
B. When a table has no attributes
C. When the database has only one table
D. When a candidate key is composite

14 Third Normal Form (3NF) primarily removes which type of dependency involving non-key attributes?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Easy
A. Partial dependency
B. Reflexive dependency
C. Trivial dependency
D. Transitive dependency

15 Suppose and . Which dependency is transitive?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Easy
A.
B.
C.
D.

16 Which statement describes Boyce-Codd Normal Form (BCNF)?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Easy
A. Every relation must have exactly one candidate key
B. Every non-key attribute may determine other non-key attributes if the table is in 1NF
C. Every determinant is a candidate key
D. Every attribute must contain a numeric value

17 How does BCNF generally compare with 3NF?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Easy
A. BCNF is identical to 2NF
B. BCNF allows repeating groups that are prohibited by all earlier normal forms
C. BCNF is stricter than 3NF
D. BCNF is weaker than 1NF

18 What is denormalization?

Denormalization Easy
A. Removing every index from a database
B. Splitting every relation until each table contains only one column
C. Converting all text values into numbers
D. Intentionally adding redundancy to improve performance

19 What is a common reason for denormalizing a database?

Denormalization Easy
A. To ensure that each query scans every table and reconstructs all related records
B. To remove every candidate key
C. To reduce the number of joins
D. To guarantee elimination of redundancy

20 What is a common disadvantage of denormalization?

Denormalization Easy
A. Greater risk of inconsistent data
B. Guaranteed reduction in storage use
C. Automatic removal of duplicate data
D. Complete prevention of update anomalies

21 In the relation Enrollment(StudentID, CourseID, StudentName, CourseName, Grade), assume each student has one name and each course has one name. Which functional dependency is valid?

Functional Dependencies and Anomalies Medium
A. StudentName -> StudentID
B. Grade -> CourseID
C. CourseID -> Grade
D. StudentID -> StudentName

22 A relation stores EmployeeID, EmployeeName, ProjectID, and ProjectName. The key is (EmployeeID, ProjectID), and EmployeeID -> EmployeeName. Which anomaly is most directly caused by this dependency?

Functional Dependencies and Anomalies Medium
A. Deletion anomaly
B. Selection anomaly
C. Update anomaly
D. Insertion anomaly

23 Consider OrderLine(OrderID, ProductID, ProductName, Quantity), where (OrderID, ProductID) is the key and ProductID -> ProductName. What problem exists in this relation?

Functional Dependencies and Anomalies Medium
A. A multivalued dependency
B. A transitive dependency
C. A partial dependency
D. A join dependency

24 A university relation contains (StudentID, CourseID, InstructorID) with the dependencies StudentID, CourseID -> InstructorID and InstructorID -> CourseID. Which attribute set is also a candidate key?

Functional Dependencies and Anomalies Medium
A. (InstructorID)
B. (StudentID, InstructorID)
C. (StudentID, CourseID, InstructorID)
D. (CourseID, InstructorID)

25 If a relation has the dependency chain A -> B and B -> C, which dependency follows by transitivity?

Functional Dependencies and Anomalies Medium
A. A -> B -> C
B. B -> A
C. C -> B
D. A -> C

26 A table records SupplierID, SupplierName, PartID, and PartPrice. A supplier can supply many parts, and a part can be supplied by many suppliers. Which issue is most likely if all data remains in one table?

Functional Dependencies and Anomalies Medium
A. Automatic prevention of deletion
B. Loss of all candidate keys
C. Violation of atomicity only
D. Repeated supplier and part facts

27 Which design satisfies First Normal Form (1NF)?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Medium
A. A table with one atomic value per cell
B. A table storing comma-separated phone numbers
C. A table with nested records in one attribute
D. A table containing repeating column groups

28 A relation is in 1NF with composite key (A, B) and dependency A -> C. What additional condition is required for the relation to be in 2NF?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Medium
A. B must determine A
B. C must depend on both A and B
C. A must depend on C
D. C must be a candidate key

29 Consider Employee(EmployeeID, DepartmentID, DepartmentName) with EmployeeID -> DepartmentID and DepartmentID -> DepartmentName. Which normal form is violated by the dependency structure?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Medium
A. 2NF
B. 1NF
C. BCNF only
D. 3NF

30 A relation has candidate key K and functional dependency K -> A, where A is a non-key attribute. Which statement is correct?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Medium
A. This dependency proves a partial dependency
B. This dependency is allowed in 3NF
C. This dependency violates 1NF
D. This dependency violates BCNF

31 For a relation to satisfy BCNF, every nontrivial functional dependency must have what property?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Medium
A. Y contains one attribute
B. X contains one attribute
C. X is a superkey
D. Y is a superkey

32 A relation has candidate keys (A, B) and (B, C). A non-key attribute D depends on B alone. Which normal form is definitely violated?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Medium
A. 3NF
B. None of these
C. 2NF
D. 1NF

33 A relation R(A, B, C) has dependencies A -> B and B -> C, with A as the only candidate key. Which decomposition best removes the transitive dependency?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Medium
A. R1(A, B, C) and R2(C)
B. R1(A) and R2(B, C)
C. R1(A, C) and R2(A, B, C)
D. R1(A, B) and R2(B, C)

34 A relation R(Student, Course, Instructor) has dependencies Student, Course -> Instructor and Instructor -> Course. Which dependency causes a BCNF violation?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Medium
A. Student -> Course
B. Instructor -> Course
C. Course -> Instructor
D. Student, Course -> Instructor

35 Which statement best describes a lossless-join decomposition?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Medium
A. The decomposition removes every foreign key
B. The decomposition prevents all future redundancy
C. Joining the decomposed tables reconstructs the original relation
D. The decomposition guarantees fewer total rows

36 Why can a 3NF decomposition be preferred over a BCNF decomposition in some designs?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Medium
A. 3NF can preserve dependencies more reliably
B. 3NF permits repeating groups
C. 3NF eliminates all foreign keys
D. 3NF always uses fewer relations

37 A reporting system frequently joins five normalized tables to display a product summary. Which denormalization strategy could reduce query cost?

Denormalization Medium
A. Replace all keys with free text
B. Duplicate every table without changes
C. Remove constraints from every table
D. Store selected summary attributes together

38 What is the primary trade-off when storing CustomerName in both Customer and Order tables?

Denormalization Medium
A. More atomic values but less indexing
B. Faster reads but greater update risk
C. Stronger normalization but more joins
D. Slower reads but fewer candidate keys

39 A database stores a cached OrderTotal in the Orders table even though it can be calculated from OrderLine rows. What risk does this introduce?

Denormalization Medium
A. The order can no longer have a primary key
B. The cached value may become inconsistent
C. The database cannot perform joins
D. The line items become non-atomic

40 Which workload most strongly justifies selective denormalization?

Denormalization Medium
A. Systems requiring strict minimal redundancy
B. Tables whose attributes are not yet defined
C. Write-heavy transactions with frequent changes
D. Read-heavy queries with stable duplicated data

41 For relation , let . Which set contains all and only the candidate keys of ?

Functional Dependencies and Anomalies Hard
A.
B.
C.
D.

42 Given , which functional dependency is implied by ?

Functional Dependencies and Anomalies Hard
A.
B.
C.
D.

43 Consider . Which set is a minimal cover equivalent to ?

Functional Dependencies and Anomalies Hard
A.
B.
C.
D.

44 Relation satisfies . Assuming atomic attributes, what is the highest normal form of ?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Hard
A. Second Normal Form
B. Third Normal Form
C. Boyce-Codd Normal Form
D. First Normal Form only

45 For with , which classification is correct?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Hard
A. It is in 2NF but not in 3NF
B. It is in 1NF but not in 2NF
C. It is in 3NF but not in BCNF
D. It is in BCNF but not in 4NF

46 For with , a BCNF decomposition uses the violating dependency and produces and . Which statement is correct?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Hard
A. The decomposition is lossy and dependency preserving
B. The decomposition is lossy and not dependency preserving
C. The decomposition is lossless and dependency preserving
D. The decomposition is lossless but not dependency preserving

47 A binary decomposition of into and is lossless with respect to exactly when which condition holds?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Hard
A. or is in
B. and is in
C. and is in
D. or is in

48 For with minimal cover , which decomposition is produced directly by the standard dependency-preserving 3NF synthesis algorithm after removing no subsumed schemas?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Hard
A.
B.
C.
D.

49 An enrollment table has key and dependencies , , and . If deleting the final enrollment for a course also removes the only stored facts about its instructor, which anomaly occurs?

Functional Dependencies and Anomalies Hard
A. A deletion anomaly
B. An insertion anomaly
C. A join anomaly
D. An update anomaly

50 A column stores geographic coordinates as a single value such as POINT(12.4, 18.7), and the DBMS treats the entire value as indivisible for relational operations. What is the most accurate conclusion about 1NF?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Hard
A. It satisfies 1NF only if both coordinates are candidate keys
B. It violates 1NF unless the value is stored in two relations
C. It satisfies 1NF if POINT is atomic in the defined domain
D. It violates 1NF because the value has two conceptual components

51 A current instance of happens to contain no two tuples with the same value. Which conclusion about the schema-level dependency is valid?

Functional Dependencies and Anomalies Hard
A. is disproven because duplicate values are absent
B. is proven because the current instance satisfies it
C. becomes a key constraint for all future instances
D. holds in this instance but is not thereby proven semantically

52 Let satisfy . Assuming 1NF, what is the highest normal form of ?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Hard
A. Third Normal Form
B. Second Normal Form
C. First Normal Form
D. Boyce-Codd Normal Form

53 For with candidate key and dependencies and , what is the highest normal form, assuming no other nontrivial dependencies and atomic values?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Hard
A. Boyce-Codd Normal Form
B. Third Normal Form
C. First Normal Form only
D. Second Normal Form

54 A system stores a denormalized monthly sales total alongside normalized transaction rows. Which design most directly prevents committed transactions from leaving the total inconsistent with its source rows?

Denormalization Hard
A. Allow applications to update the total in separate transactions
B. Recompute the total asynchronously during low-traffic periods
C. Cache the total indefinitely and invalidate it after reporting
D. Update the rows and total atomically in one transaction

55 Which situation provides the strongest justification for deliberately denormalizing a BCNF design?

Denormalization Hard
A. Developers prefer fewer tables, although no workload measurements or maintenance rules exist
B. Measured read latency is dominated by repeated joins, and controlled redundancy meets the consistency budget
C. The schema contains update anomalies, and duplicating more attributes makes updates easier to write
D. Storage is scarce, and redundant attributes are expected to reduce the database size

56 For with , consider the decomposition and . Which property combination is correct?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Hard
A. Lossy but dependency preserving
B. Lossless but not dependency preserving
C. Lossy and not dependency preserving
D. Lossless and dependency preserving

57 Which statement best explains why a designer might select a 3NF decomposition instead of a BCNF decomposition?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Hard
A. Every 3NF decomposition removes more redundancy than every BCNF decomposition
B. Every 3NF relation automatically satisfies the stricter determinant rule of BCNF
C. A 3NF decomposition is always lossless, whereas a BCNF decomposition is always lossy
D. A 3NF decomposition can preserve all dependencies when no lossless BCNF decomposition does

58 When testing whether a relation is in BCNF, which set of dependencies must be considered?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Hard
A. Only the dependencies appearing in one selected minimal cover
B. Every nontrivial dependency in the closure
C. Only dependencies whose right side is a nonprime attribute
D. Only the dependencies explicitly listed in the original specification

59 A line-item relation stores , where . What is the principal normalization risk of storing LineTotal?

Denormalization Hard
A. It prevents Quantity and UnitPrice from remaining atomic values
B. It makes the composite identifier cease to be a candidate key
C. It creates redundant derived data that can become inconsistent after updates
D. It guarantees a lossy join whenever order data is decomposed

60 Let satisfy . What is the highest normal form of ?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Hard
A. First Normal Form only
B. Second Normal Form but not Third Normal Form
C. Boyce-Codd Normal Form
D. Third Normal Form but not Boyce-Codd Normal Form
Previous MCQ
Unit 2
Next MCQ
Unit 4