B CompE Bachelor of Computer Engineering Database Management Systems 2 — Questions and Answers
Question 1: Which indexing structure is most commonly used in relational databases for primary keys?
- Hash Index
- B-Tree Index (Correct answer)
- Bitmap Index
- Full-Text Index
Correct answer: B-Tree Index
B-Tree indexes are the default in most relational databases because they support both equality and range queries efficiently.
Question 2: What is a deadlock in a database system?
- A query that takes too long to execute
- Two transactions wait indefinitely for each other to release locks (Correct answer)
- A corrupted index that blocks queries
- A failed backup that prevents writes
Correct answer: Two transactions wait indefinitely for each other to release locks
A deadlock occurs when two or more transactions each hold a lock the other needs, creating a circular wait that prevents progress.
Question 3: Which relational algebra operation combines tuples from two relations where a condition is met?
- Union
- Projection
- Selection
- Join (Correct answer)
Correct answer: Join
The Join operation in relational algebra combines rows from two relations based on a matching condition between their attributes.
Question 4: What is the purpose of a database view?
- Store data physically faster than a table
- Provide a virtual table based on the result of a stored query (Correct answer)
- Replace indexes for faster reads
- Cache query results on disk
Correct answer: Provide a virtual table based on the result of a stored query
A database view is a stored query that appears as a virtual table, simplifying complex queries and restricting data access.
Question 5: Which SQL aggregate function returns the total number of rows in a result set?
- SUM()
- MAX()
- COUNT() (Correct answer)
- AVG()
Correct answer: COUNT()
COUNT() returns the number of rows that match the specified criteria, counting non-NULL values when a column is specified.
Question 6: What is a stored procedure in a relational database?
- A method for compressing table data
- A precompiled set of SQL statements stored in the database (Correct answer)
- An automatic backup routine
- A type of constraint on a column
Correct answer: A precompiled set of SQL statements stored in the database
A stored procedure is a precompiled group of SQL statements saved in the database that can be executed by name, improving performance and reusability.
B CompE Bachelor of Computer Engineering Database Management Systems 2
Which indexing structure is most commonly used in relational databases for primary keys?