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. Each value of determines one value of
C. The values of and must always be identical
D. Both and must be primary keys

2 In the dependency , which attribute is the determinant?

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

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

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

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

Functional Dependencies and Anomalies Easy
A. Sorting anomaly
B. Update anomaly
C. Insertion anomaly
D. Deletion 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. Insertion anomaly
C. Update anomaly
D. Deletion anomaly

6 What is a trivial functional dependency?

Functional Dependencies and Anomalies Easy
A. A dependency whose right side contains no attributes from the table
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 left side contains only candidate-key attributes

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

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

8 What is the main purpose of database normalization?

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Easy
A. Increase duplicated data
B. Store every attribute in one table
C. Replace all primary keys
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 field contains an atomic value
B. Every table has exactly two columns
C. Every non-key attribute determines the primary key through another attribute
D. Every attribute is a foreign key

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

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

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. Partial dependency
B. Multivalued dependency
C. A dependency in which every determinant must be a superkey without exception
D. Trivial dependency

13 When can a partial dependency occur?

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

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. Transitive dependency
B. Partial dependency
C. Reflexive dependency
D. Trivial 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 non-key attribute may determine other non-key attributes if the table is in 1NF
B. Every attribute must contain a numeric value
C. Every relation must have exactly one candidate key
D. Every determinant is a candidate key

17 How does BCNF generally compare with 3NF?

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

18 What is denormalization?

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

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. Complete prevention of update anomalies
C. Automatic removal of duplicate data
D. Guaranteed reduction in storage use

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. StudentID -> StudentName
D. CourseID -> Grade

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. Insertion anomaly
C. Selection anomaly
D. Update 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 transitive dependency
B. A partial dependency
C. A join dependency
D. A multivalued 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. (StudentID, CourseID, InstructorID)
B. (CourseID, InstructorID)
C. (StudentID, InstructorID)
D. (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. C -> B
B. A -> B -> C
C. A -> C
D. B -> A

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. Loss of all candidate keys
B. Violation of atomicity only
C. Automatic prevention of deletion
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 storing comma-separated phone numbers
B. A table with one atomic value per cell
C. A table containing repeating column groups
D. A table with nested records in one attribute

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. C must depend on both A and B
B. C must be a candidate key
C. B must determine A
D. A must depend on C

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. 3NF
C. 1NF
D. BCNF only

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 is allowed in 3NF
B. This dependency violates 1NF
C. This dependency violates BCNF
D. This dependency proves a partial dependency

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. X contains one attribute
B. X is a superkey
C. Y is a superkey
D. Y contains one attribute

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. 2NF
B. None of these
C. 1NF
D. 3NF

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) and R2(B, C)
B. R1(A, C) and R2(A, B, C)
C. R1(A) and R2(B, C)
D. R1(A, B, C) and R2(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 -> Instructor
B. Course -> Instructor
C. Student -> Course
D. Instructor -> Course

35 Which statement best describes a lossless-join decomposition?

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

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 eliminates all foreign keys
C. 3NF always uses fewer relations
D. 3NF permits repeating groups

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

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

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

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

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 cached value may become inconsistent
B. The order can no longer have a primary key
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. Read-heavy queries with stable duplicated data
D. Write-heavy transactions with frequent changes

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. Third Normal Form
B. First Normal Form only
C. Boyce-Codd Normal Form
D. Second Normal Form

45 For with , which classification is correct?

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

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 lossless but not dependency preserving
B. The decomposition is lossy and not dependency preserving
C. The decomposition is lossy and dependency preserving
D. The decomposition is lossless and 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. and is in
B. and is in
C. or 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. An insertion anomaly
B. A deletion anomaly
C. An update anomaly
D. A join 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 violates 1NF unless the value is stored in two relations
B. It satisfies 1NF if POINT is atomic in the defined domain
C. It violates 1NF because the value has two conceptual components
D. It satisfies 1NF only if both coordinates are candidate keys

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 proven because the current instance satisfies it
B. is disproven because duplicate values are absent
C. holds in this instance but is not thereby proven semantically
D. becomes a key constraint for all future instances

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. First Normal Form
C. Boyce-Codd Normal Form
D. Second 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. Second Normal Form
C. First Normal Form only
D. Third 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. Cache the total indefinitely and invalidate it after reporting
C. Recompute the total asynchronously during low-traffic periods
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. Storage is scarce, and redundant attributes are expected to reduce the database size
B. Developers prefer fewer tables, although no workload measurements or maintenance rules exist
C. The schema contains update anomalies, and duplicating more attributes makes updates easier to write
D. Measured read latency is dominated by repeated joins, and controlled redundancy meets the consistency budget

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

Normalization Concepts and Forms (1NF, 2NF, 3NF, BCNF) Hard
A. Lossless but not dependency preserving
B. Lossy but dependency preserving
C. Lossless and dependency preserving
D. Lossy and not 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 relation automatically satisfies the stricter determinant rule of BCNF
B. A 3NF decomposition can preserve all dependencies when no lossless BCNF decomposition does
C. Every 3NF decomposition removes more redundancy than every BCNF decomposition
D. A 3NF decomposition is always lossless, whereas a BCNF decomposition is always lossy

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. Every nontrivial dependency in the closure
B. Only the dependencies explicitly listed in the original specification
C. Only the dependencies appearing in one selected minimal cover
D. Only dependencies whose right side is a nonprime attribute

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

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

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

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