programming
beginner
10 sample questions
Database Design MCQ Practice Test
Structuring a database to efficiently store, retrieve, and manage data.
Q1. What is the primary purpose of a surrogate key in a database?
-
A. To uniquely identify each row in a table ✓
-
B. To store redundant data in a table
-
C. To enforce data normalization in a database
-
D. To improve query performance in a database
Explanation: A surrogate key, also known as a primary key, is a unique identifier assigned to each row in a table. Its primary purpose is to uniquely identify each row and allow for efficient data retrieval and manipulation.
Q2. What is the primary purpose of a 'bridge table' in a many-to-many relationship in database design?
-
A. To reduce data redundancy by storing duplicate data in multiple tables
-
B. To increase data consistency by enforcing referential integrity across tables
-
C. To establish a relationship between two tables with multiple connections ✓
-
D. To improve data normalization by minimizing data dependencies
Explanation: A bridge table, also known as a join table, is used to resolve many-to-many relationships between two tables by creating a new table that links the two tables together.
Q3. In a database design, what is the primary purpose of a “bridge table”?
-
A. To reduce data redundancy by storing duplicate data in multiple tables
-
B. To improve query performance by joining related tables
-
C. To establish a many-to-many relationship between two tables ✓
-
D. To enforce data consistency across multiple tables
Explanation: A bridge table is a specialized table used to resolve many-to-many relationships in a database. It acts as a bridge between two tables, allowing multiple records to be associated with each other.
Q4. A database designer is implementing a many-to-many relationship between the "Orders" and "Products" tables. The "Orders" table has a primary key of "OrderID". The "Products" table has a primary key of "ProductID". Which of the following is the correct approach to implement this relationship in the database schema?
-
A. Introduce a new junction table with foreign keys referencing the "OrderID" and "ProductID" columns in the "Orders" and "Products" tables, respectively. ✓
-
B. Create a self-referential relationship on the "Products" table by adding a new column "OrderID" with a foreign key constraint referencing the "Orders" table.
-
C. Denormalize the database by storing the "ProductID" column in the "Orders" table and removing the many-to-many relationship.
-
D. Use a bridge table with foreign keys referencing the "ProductID" column in the "Products" table and the "OrderID" column in the "Orders" table.
Explanation: In a many-to-many relationship, a junction table (also known as a bridge table or linking table) is required to resolve the relationship. The junction table should have foreign keys referencing the primary keys of the "Orders" and "Products" tables.
Q5. In a database design, what is the primary purpose of a many-to-many relationship between the "Orders" table and the "Products" table, if there is a separate "Order_Items" table that acts as a bridge table?
-
A. To establish a one-to-one relationship between orders and products
-
B. To reduce data redundancy and improve data integrity ✓
-
C. To create a hierarchical structure between orders and products
-
D. To enable the use of a single table for both orders and products
Explanation: A many-to-many relationship between the "Orders" table and the "Products" table would result in data redundancy and potential data inconsistencies. The "Order_Items" table acts as a bridge table to resolve this issue by establishing a separate relationship between orders and products, thereby reducing data redundancy and improving data integrity.
Q6. What is the primary function of a candidate key in a database?
-
A. A field that uniquely identifies a record in a table ✓
-
B. A field that is used to join two tables
-
C. A field that is used to index a table
-
D. A field that is used to enforce data integrity
Explanation: A candidate key is a field or set of fields that uniquely identifies a record in a table. It is used to enforce data integrity and is often used as the primary key in a table.
Q7. A database designer wants to store information about customers, including their name, address, and phone number. Which of the following database normalization techniques would be most appropriate for this scenario?
-
A. First Normal Form (1NF) ✓
-
B. Second Normal Form (2NF)
-
C. Third Normal Form (3NF)
-
D. Boyce-Codd Normal Form (BCNF)
Explanation: The First Normal Form (1NF) is a database normalization technique that ensures each table cell contains a single value. In this scenario, storing information about customers, including their name, address, and phone number, would be suitable for 1NF because each customer would have multiple values for each attribute, and 1NF would ensure that each value is stored in a separate cell, avoiding data redundancy and improving data integrity.
Q8. Which of the following database design principles is used to prevent data redundancy and improve data integrity?
-
A. Denormalization
-
B. Data normalization ✓
-
C. Data denugmentation
-
D. Data standardization
Explanation: Data normalization is a database design technique that aims to minimize data redundancy and improve data integrity by organizing data in a way that eliminates data duplication and dependency.
Q9. In a database design, what is the primary benefit of using a denormalized schema for an e-commerce application with a large number of related tables, such as orders, customers, and products?
-
A. Improved data consistency and integrity
-
B. Enhanced scalability and performance ✓
-
C. Simplified data modeling and querying
-
D. Reduced data redundancy and storage requirements
Explanation: Denormalization can improve query performance by reducing the number of joins required to retrieve data, but it can also lead to data inconsistencies if not implemented properly. In an e-commerce application with a large number of related tables, denormalization can enhance scalability and performance by reducing the complexity of queries and improving data retrieval times.
Q10. In a database design that employs a normal form higher than 2NF, which of the following is a characteristic of the relationship between a many-to-many association and its resolving tables?
-
A. Each resolving table contains a composite primary key consisting of foreign keys from both tables in the association.
-
B. Each resolving table contains a foreign key referencing the primary key of one table in the association, and a unique identifier as the primary key.
-
C. Each resolving table contains a foreign key referencing the primary key of one table in the association, and a foreign key referencing the primary key of the other table in the association. ✓
-
D. Each resolving table contains a composite primary key consisting of foreign keys from one table in the association, and a unique identifier as the primary key.
Explanation: In a database design that employs a normal form higher than 2NF, resolving a many-to-many association typically involves creating a bridge table with foreign keys referencing the primary keys of both tables in the association, thereby establishing a relationship between them.
That was just a sample. Sign up to unlock the full question bank with timed tests and certificates.
Sign Up Free