DBMS Quiz
Questions: 16 · 10 minutes
1. Each author can write many books, and each book can have many authors. What is the standard relational design for this relationship?
Store one AuthorID column in Books and allow only one author per book.
Store a comma-separated list of authors in each Books row.
Duplicate each complete Books row once for every author.
Create an AuthorBook junction table containing references to Authors and Books.
2. Transaction A holds a lock needed by Transaction B, while Transaction B holds a lock needed by Transaction A. Neither can continue. What is this situation called?
Deadlock
Referential integrity
Replication lag
Denormalization
3. Which relational operation combines related rows from two tables using a matching condition?
GROUP BY
PROJECT
SELECT DISTINCT
JOIN
4. What does atomicity mean for a database transaction?
Its queries always run faster than nontransactional queries.
Its operations are treated as one unit that either completes or is rolled back.
Its data is automatically duplicated across multiple servers.
Its changes immediately become visible to every concurrent session.
5. An Orders table repeats each customer's name and address in every order row. Which redesign most directly reduces this redundancy?
Create a Customers table and reference each customer from Orders with a key.
Add separate indexes to the customer-name and address columns.
Copy the repeated customer columns into an order-history table.
Replace the customer address with a longer text data type.
6. What is a database view?
A physical backup containing every data file
A lock that limits access to selected rows
An index containing only unique values
A named query that presents data like a virtual table
7. An Enrollment table has the composite key (StudentID, CourseID) and also stores StudentName. StudentName depends only on StudentID. Which normalization issue does this illustrate?
A transitive dependency that first appears in Boyce–Codd normal form
A repeating group that can exist only in fourth normal form
A partial dependency that violates second normal form
A foreign-key cycle that violates first normal form
8. Which SQL structure correctly returns cities having at least five customers?
SELECT city, COUNT(*) FROM Customers WHERE COUNT(*) >= 5;
SELECT DISTINCT city, COUNT(*) FROM Customers ORDER BY COUNT(*) >= 5;
SELECT city FROM Customers HAVING city >= 5;
SELECT city, COUNT(*) FROM Customers GROUP BY city HAVING COUNT(*) >= 5;
9. Which statement best describes a primary key in a relational table?
It stores a reference that must point to a row in another table.
It uniquely identifies each row and cannot contain NULL values.
It automatically sorts the table's rows on disk.
It encrypts values that should not be exposed to users.
10. Two clerks may reduce the same inventory quantity at nearly the same time. Which approach most directly helps prevent one update from silently overwriting the other?
Run both sessions at the read-uncommitted isolation level.
Use a transaction with an atomic quantity update and appropriate row locking.
Remove constraints so both updates can finish independently.
Cache the original quantity in each clerk's application for longer.
11. A large Users table is frequently searched using exact email addresses. Which change would most directly improve these lookups?
Store the email address in a larger character type.
Create a trigger that records every search.
Create an index on the email column.
Move old rows into the same table under different column names.
12. Transaction A changes a balance but has not committed. Transaction B reads that changed value anyway. What concurrency anomaly has occurred?
A phantom read
A lost update
A dirty read
A cascading delete
13. An Orders row must not reference a customer that is absent from the Customers table. Which constraint is designed to enforce this rule?
A CHECK constraint on the order total
A UNIQUE constraint on the order date
A foreign key from Orders to Customers
A DEFAULT value for the customer identifier
14. Which ACID property means that committed transaction results should survive a system failure?
Durability
Consistency
Isolation
Atomicity
15. A query groups sales by region and should return only regions whose total sales exceed 10,000. Which clause should apply that condition?
WHERE SUM(amount) > 10000
HAVING SUM(amount) > 10000
ORDER BY SUM(amount) > 10000
DISTINCT SUM(amount) > 10000
16. A DBMS records changes in a write-ahead log before modified data pages are written. After a crash, how can this log support recovery?
By redoing committed changes that had not yet reached the data files
By rebuilding all indexes from user-submitted queries alone
By replacing committed data with the oldest available values
By converting every table into a read-only view