Database Concepts Quiz

Which of the following is true about BCNF and 3NF?

• A. BCNF and 3NF are identical in terms of their requirements.

• B. Every 3NF relation is also in BCNF, but not every BCNF relation is in 3NF.

• C. Every BCNF relation is also in 3NF, but not every 3NF relation is in BCNF.

• D. BCNF focuses on functional dependencies, while 3NF focuses on foreign key relationships.

What is the primary difference between a clustered and a non-clustered index?

• A. A clustered index organizes data physically on the disk, while a non-clustered index does not.

• B. A non-clustered index stores the entire table data, while a clustered index only stores the indexed columns.

• C. A non-clustered index organizes data physically, while a clustered index only indexes columns.

• D. Both indexes organize data physically but clustered indexes can have multiple instances.

Which of the following is NOT an ACID property of a transaction?

• A. Atomicity

• B. Consistency

• C. Integrity

• D. Durability

A deadlock occurs in a database system when:

• A. A transaction is forced to rollback due to a timeout.

• B. A transaction is waiting indefinitely for a lock to be released.

• C. All the transactions are waiting for a signal from the CPU scheduler.

• D. Two or more transactions are waiting for each other to release resources.

Which of the following constraints ensures that a column cannot have NULL values, but does not imply uniqueness?

• A. UNIQUE

• B. PRIMARY KEY

• C. NOT NULL

• D. CHECK

What type of lock allows multiple transactions to read but prevents any write operations?

• A. Exclusive Lock

• B. Shared Lock

• C. Read Lock

• D. Deadlock

Which of the following queries will correctly filter groups based on aggregate functions?

• A. SELECT department_id, COUNT(*) FROM employees HAVING COUNT(*) > 5;

• B. SELECT department_id, COUNT(*) FROM employees WHERE COUNT(*) > 5;

• C. SELECT department_id, COUNT(*) FROM employees GROUP BY COUNT(*) > 5;

• D. SELECT department_id, COUNT(*) FROM employees WHERE department_id HAVING COUNT(*) > 5;

Which of the following queries correctly uses a subquery to return all employees earning more than the average salary?

• A. SELECT * FROM employees WHERE salary > (SELECT salary FROM employees);

• B. SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

• C. SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

• D. SELECT * FROM employees WHERE salary > (SELECT MIN(salary) FROM employees);

What does the following SQL statement do? CREATE VIEW employee_view AS SELECT name, department_id FROM employees WHERE salary > 50000;

• A. Creates a new table named employee_view with employees earning more than 50000.

• B. Creates a view employee_view that shows the names and department IDs of employees earning more than 50000.

• C. Creates a view that automatically updates employee salaries to 50000.

• D. Deletes records from the employees table for those with salaries less than 50000.

What is the difference between TRUNCATE and DELETE in SQL?

• A. TRUNCATE removes rows one by one and logs each removal, while DELETE removes all rows at once without logging.

• B. DELETE can remove specific rows based on a condition, while TRUNCATE removes all rows without conditions and is faster.

• C. TRUNCATE can remove rows based on conditions, but DELETE cannot.

• D. TRUNCATE cannot be rolled back, but DELETE can.