This free quiz gives you 50 DBMS MCQs with answers and explanations, covering database anomalies, normalization types (1NF, 2NF, 3NF, BCNF, 4NF), and functional dependencies. Built for computer science students, software engineers, and professional database developers preparing for certified exams.
Understanding relational database design is highly essential for building efficient, high-performance database structures. This comprehensive practice set helps you clear core concepts regarding schema design, structural rules, and integrity constraints effectively.
- ✔ Data Relational Structures
- ✔ Modification Anomalies
- ✔ Functional Dependencies
- ✔ Candidate & Primary Keys
- ✔ 1NF & 2NF Rules
- ✔ Transitive Dependencies & 3NF
- ✔ Boyce-Codd Normal Form (BCNF)
- ✔ Multi-valued Dependencies & 4NF
Database Basics, Anomalies & Keys (Q1-10)
DATABASE ANOMALIES & KEYSThis introductory section focuses on the foundational building blocks of the relational model, exploring structural tables, constraints, and data errors.
1. A relation is considered a _____?
View Answer
Answer: C) Two-dimensional table. In relational database management systems, a relation is formally structured as a two-dimensional grid composed of rows (tuples) and columns (attributes).
2. Which of the following is a group of one or more attributes that uniquely identifies a row?
View Answer
Answer: A) Key. A key is an attribute or a combination of attributes that uniquely distinguishes each individual record inside a relational table.
3. For some relations, changing the data can have undesirable consequences, called _____?
View Answer
Answer: B) Modification anomalies. Modification anomalies refer to unexpected errors or structural gaps that pop up when you try to insert, delete, or update data inside poorly constructed tables.
4. When the values in one attribute must exist in another attribute, it is called _____?
View Answer
Answer: C) Referential integrity constraints. Referential integrity rules make sure a foreign key value always links back to a matching primary key value in the referenced table, keeping the records synchronized.
5. The different classes of relations and the techniques for preventing anomalies are called _____?
View Answer
Answer: A) Normal forms. Normal forms represent a set of progressive structural templates designed to streamline database relations and eliminate anomalies step by step.
6. Two or more attributes or attribute collections that can be a key are called _____?
View Answer
Answer: C) Candidate Keys. Candidate keys are the minimal group of fields capable of acting as a unique row identifier, from which one is chosen as the official primary key.
7. In general, a row in a relation should have all of the data about?
View Answer
Answer: B) One instance of the relation's theme. To keep schemas clean and readable, each row inside a normalized table should only capture details about a single specific entity or transaction instance.
8. A relation is not allowed to have _____?
View Answer
Answer: C) Two columns with the same names. Relational integrity rules demand that every single column inside a distinct table has a completely unique name to avoid field ambiguity.
9. What type of anomaly happens when deleting a row inadvertently destroys independent unrelated data?
View Answer
Answer: B) Deletion Anomaly. A deletion anomaly occurs when removing a specific record unintentionally wipes out completely different, useful information that can't be stored elsewhere.
10. An entry block that cannot be created because it requires facts that are currently unavailable is known as a(n):
View Answer
Answer: A) Insertion Anomaly. An insertion anomaly holds you back from logging a new fact because it requires a completely separate piece of missing background data to validate the record.
Functional Dependencies & Relationships (Q11-20)
FUNCTIONAL DEPENDENCIESThis section explores how attributes interact with one another and the functional rules that determine schemas.
11. Which of the following is TRUE from functional dependency shown as (A, B) → (C, D)?
View Answer
Answer: C) A and B together determine D. This notation indicates that the composite set of attributes (A, B) acts together as the core determinant to fully map the values of C and D.
12. A functional dependency is a relationship between or among?
View Answer
Answer: D) Attributes. Functional dependencies express targeted semantic rules showing how the values of specific attributes (columns) relate to one another within a table.
13. If attribute A determines attribute B and B determines A, the values of the attributes have this relationship _____?
View Answer
Answer: A) One-to-one. Mutual dependency means that each value in column A maps directly to exactly one value in column B, and vice versa.
14. If attribute A determines B but B does not determine A, the relationship among their data values is _____?
View Answer
Answer: C) Many-to-one relationship. While a specific value in A uniquely pins down a single value in B, multiple distinct rows with different A values can still map back to the same shared B value.
15. Which is TRUE from functional dependency shown as A → (X, Y)?
View Answer
Answer: E) All of the above. Given that attribute A is the main determinant, it independently drives the mapping for both attribute X and attribute Y simultaneously.
16. If an attribute can determine the value of another attribute, the first attribute is called a(n):
View Answer
Answer: B) Determinant. Any attribute placed on the left-hand side of a functional dependency statement is the determinant, as it sets or limits the value of the right side.
17. An axiom that allows you to state that if X → Y and Y → Z, then X → Z is called:
View Answer
Answer: C) Transitivity rule. Armstrong's Transitivity rule outlines how functional properties chain together through an intermediary attribute group.
18. If X → Y is true, then XW → YW is true by virtue of which rule?
View Answer
Answer: A) Augmentation. The augmentation rule proves that padding both sides of an existing functional dependency with a shared attribute set preserves its validity.
19. A functional dependency where the right-hand side attributes are a subset of the left-hand side attributes is called:
View Answer
Answer: B) Trivial dependency. A dependency is trivial when the dependent attributes are already wrapped inside the initial determinant group, requiring no extra verification logic.
20. Which of the following describes a scenario where an attribute's value is uniquely determined by a non-key field?
View Answer
Answer: C) Transitive Dependency. Transitive dependency happens when a non-key column maps its value directly from another non-key column instead of relying solely on the primary key.
First, Second, and Third Normal Forms (Q21-30)
1NF, 2NF, & 3NF SETSThis section dives into the practical constraints required to systematically move schemas through 1NF, 2NF, and 3NF architectures.
21. In _____ normal form, any multi-valued attributes have been removed.
View Answer
Answer: A) First. First Normal Form (1NF) demands that every table cell holds single, atomic values, banning any repeating structural groups or arrays.
22. A partial functional dependency (FD) means that _____?
View Answer
Answer: D) Not all of the attributes on the left-hand side of the FD are necessary. A partial dependency occurs when a non-prime attribute derives its value from only a small portion of a composite primary key layout.
23. Which of the following is a requirement of 3NF?
View Answer
Answer: D) Must contain no transitive dependencies. Third Normal Form (3NF) requires a table to be in 2NF with absolutely zero transitive dependencies present among its non-prime attributes.
24. Which of the following should not be placed in a relational table?
View Answer
Answer: B) Repeating group. To respect 1NF criteria, multi-valued items or repeating structures must be split out into distinct rows or individual tables.
25. A 3NF violation means _____?
View Answer
Answer: D) An attribute in the table belongs in another entity. When a 3NF violation happens, a non-prime attribute depends on another non-prime field, meaning it conceptually belongs in a different entity table altogether.
26. To convert a table from 1NF to 2NF, you must remove which of the following?
View Answer
Answer: B) Partial dependencies. Moving a table into 2NF requires removing partial dependencies, ensuring every non-key column depends fully on the complete primary key.
27. If a table has a single-attribute primary key and is already in 1NF, it automatically satisfies:
View Answer
Answer: A) 2NF. Partial dependencies can only occur with composite keys. If the primary key is a single attribute, partial dependencies are impossible, satisfying 2NF instantly.
28. Which normal form deals directly with eliminating hidden functional dependencies between non-key fields?
View Answer
Answer: C) 3NF. 3NF breaks up transitive pathways where a non-prime column relies on an intermediate non-prime field instead of the primary key.
29. An attribute that is a member of at least one candidate key is termed as a:
View Answer
Answer: C) Prime attribute. Any field that forms part of a candidate key is called a prime attribute, which shields it from certain normalization restrictions.
30. Decomposing tables to achieve higher normal forms should always protect against losing data relationships, a concept called:
View Answer
Answer: B) Lossless-join decomposition. Lossless-join decomposition guarantees that when a table is split up, it can be reassembled via standard JOIN queries without losing or generating bad data.
BCNF and Fourth Normal Form Concepts (Q31-40)
BCNF & 4NF SPECIFICATIONSThis section explores advanced refinement patterns, diving into Boyce-Codd Normal Form and multi-valued anomalies.
31. What is the highest normal form a relation is in if every determinant is a candidate key?
View Answer
Answer: D) BCNF. Boyce-Codd Normal Form (BCNF) enforces a strict rule: every single determinant column must be a candidate key.
32. A relation is in this form if it is in BCNF and has no multi-value dependencies?
View Answer
Answer: D) Fourth. Fourth Normal Form (4NF) requires the relation to be in BCNF while removing all independent multi-valued dependencies.
33. One solution to the multi-value dependency constraint problem is to _____?
View Answer
Answer: B) Split relation in two relations, each with a single theme. To fix 4NF issues, you split the table into two separate tables so each multi-valued fact is isolated cleanly.
34. A relation is in fourth normal form if it is in BCNF and it has no _____?
View Answer
Answer: C) Multi-value dependencies. 4NF targets multi-valued dependencies, which occur when a single key maps to multiple independent lists of values in the same row.
35. The anomalies addressed by moving from BCNF to 4NF generally deal with _____?
View Answer
Answer: C) Excessive updates and redundancy of data for each entity. Missing 4NF criteria leads to repetitive data combinations across rows, causing high redundancy and excessive updates.
36. In BCNF, every _____ in a table is a candidate key.
View Answer
Answer: A) Determinant. BCNF leaves no room for non-key attributes to determine other elements; every functional determinant must be a candidate key.
37. BCNF is considered a stricter enhancement of which standard normal form?
View Answer
Answer: C) 3NF. BCNF is a stronger version of 3NF that handles cases where candidate keys overlap and a prime attribute depends on a non-prime attribute.
38. Can a relation be in 3NF but fail to satisfy BCNF requirements?
View Answer
Answer: B) Yes, if a prime attribute depends on a non-candidate key determinant. A table can be in 3NF yet violate BCNF if an overlapping candidate key element is determined by a non-key column.
39. A multi-valued dependency is represented symbolically using which indicator?
View Answer
Answer: C) X ↠ Y. The double-headed arrow symbol explicitly denotes a multi-valued dependency constraint within relational systems.
40. If a table holds independent facts about an employee's multiple Skills and multiple Languages, it likely violates:
View Answer
Answer: D) 4NF. Mixing independent multi-valued properties (like skills and languages) in one table creates cross-product rows, violating 4NF.
Advanced Schema Refinement & Indexing (Q41-50)
ADVANCED ENHANCEMENTSThis final section covers advanced performance tuning, logical structures, and normalization trade-offs.
41. An index _____?
View Answer
Answer: D) Both a and b. Indexes speed up queries and are automatically generated for primary keys, though they consume extra disk storage.
42. Fifth Normal Form (5NF) is primarily concerned with resolving which structural dependency issue?
View Answer
Answer: B) Join dependencies. 5NF ensures a table can be split and rebuilt without introducing fake rows, handling complex cases where fields are closely linked.
43. What is the process of intentionally reducing normalization levels to improve query speed called?
View Answer
Answer: B) Denormalization. Denormalization introduces controlled redundancy into production tables to reduce heavy JOIN operations and speed up queries.
44. A table with zero duplicate records where every attribute value is atomic is guaranteed to be in:
View Answer
Answer: A) 1NF. Clean atomic attributes combined with unique rows satisfy the foundational baseline of 1NF.
45. When decomposing a relation, preserving functional dependencies is important because it allows you to verify dependencies without using:
View Answer
Answer: B) SQL Join operations. Dependency preservation lets the system check integrity constraints within individual tables without running expensive cross-table JOINs.
46. Which type of database key consists of more than one column to guarantee uniqueness?
View Answer
Answer: C) Composite Key. A composite key combines two or more columns to create a unique identifier for a row when no single column can do it alone.
47. An automatically generated numeric value used as a primary key when no natural key is available is called a:
View Answer
Answer: B) Surrogate Key. A surrogate key is an artificial, system-assigned identifier (like an auto-incrementing ID) that carries no business meaning but ensures row uniqueness.
48. Which normal form is violated if a non-key column determines another non-key column in a 2NF table?
View Answer
Answer: B) 3NF. A dependency between two non-key columns creates a transitive dependency, which violates 3NF rules.
49. If a relation contains a single candidate key which is also the primary key, then 3NF and _____ are identical.
View Answer
Answer: C) BCNF. When there are no overlapping candidate keys, the criteria for 3NF and BCNF realign perfectly, making them functionally identical.
50. The ultimate objective of the normalization process is to eliminate:
View Answer
Answer: C) Data redundancy and anomalies. Normalization structures your data logically to eliminate redundant storage patterns and prevent data update errors.
💬 How many did you get right? Let us know your score in the comments, and tell us which topic you would like covered next.
Frequently Asked Questions
What is the primary purpose of database normalization?▾
The main goal of database normalization is to eliminate data redundancy and reduce modification anomalies (insertion, update, and deletion anomalies) to ensure data integrity.
What is the difference between 3NF and BCNF?▾
A table is in 3NF if there are no transitive dependencies. Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF where every single determinant in a functional dependency must be a candidate key.
How does a partial dependency violate 2NF?▾
A partial dependency happens when a non-prime attribute depends on only a part of a composite primary key instead of the entire key. 2NF strictly prohibits this layout.
What triggers a 4NF violation?▾
A 4NF violation occurs when a table has two or more independent multi-valued dependencies, causing unnecessary duplicate rows for independent multi-valued facts.
