Advanced Database Course Question Bank
True/False
1. The transition from file systems to intelligent data platforms marks a significant
evolution in database technology. [ ]
2. Data independence means changes in application code do not affect the database
schema. [ ]
3. Data independence allows changes in the database schema without affecting the
application programs. [ ]
4. Metadata refers to raw transactional data stored in tables. [ ]
5. Storage, buffering, and access paths are foundational components of core database
architecture. [ ]
6. The ACID properties stand for Atomicity, Consistency, Isolation, and Durability. [ ]
7. A dirty read occurs when a transaction reads data written by an uncommitted
transaction. [ ]
8. Lock-based protocols are a type of concurrency control strategy. [ ]
9. Deadlocks occur when two or more transactions are waiting indefinitely for each other
to release locks. [ ]
[Link]-ahead logging requires that log records be written to stable storage before the
corresponding data pages. [ ]
[Link]-phase commit is a protocol used to coordinate distributed transactions. [ ]
[Link] processing architecture is responsible for executing SQL queries efficiently. [ ]
[Link]-phase commit is used only in single-node databases. [ ]
[Link] algebra is used for logical rewriting of queries. [ ]
[Link] structures like B-trees are used to speed up data access. [ ]
[Link] algorithms include nested-loop, sort-merge, and hash joins. [ ]
[Link] estimation predicts the number of rows returned by a query operation. [ ]
[Link] always slow down data retrieval operations. [ ]
[Link] involves dividing a large table into smaller, more manageable pieces. [ ]
[Link] refers to storing the result of a query or view for faster access. [ ]
[Link] has fundamentally changed assumptions about database design. [ ]
[Link] involves maintaining multiple copies of data across different nodes. [ ]
[Link] CAP theorem states that a distributed system can simultaneously provide
Consistency, Availability, and Partition tolerance. [ ]
[Link] consistency is a weaker consistency model often used in distributed systems.
[ ]
[Link] databases include key-value, document, column-family, and graph databases.
[]
[Link] increases the size of tables without any performance benefit. [ ]
[Link] databases aim to combine ACID guarantees with horizontal scalability. [ ]
[Link] protects data both at rest and in transit. [ ]
[Link] masking is used to hide sensitive data in non-production environments. [ ]
[Link] involves tracking database access and modifications. [ ]
[Link] databases do not support any form of indexing. [ ]
[Link] and HIPAA are examples of privacy regulations that impact database
governance. [ ]
[Link] stewardship involves managing data assets to ensure their quality and security. [ ]
34.A data warehouse is a centralized repository designed for analytical processing. [ ]
[Link] stands for Extract, Transform, and Load. [ ]
[Link] modeling organizes data into facts and dimensions. [ ]
[Link] stands for Online Analytical Processing. [ ]
[Link] Data is typically characterized by volume, velocity, and variety. [ ]
[Link] processing models handle data in motion. [ ]
[Link] properties are optional in traditional relational databases. [ ]
[Link]-time semantics process data based on when the event actually occurred. [ ]
[Link] tolerance ensures that a distributed system can continue operating despite
failures. [ ]
[Link] and restore operations are critical for data protection. [ ]
[Link] is the ability of different systems to exchange and use data seamlessly.
[ ]
[Link] is only applied to data in transit, not at rest. [ ]
Multiple Choice (MCQ(
1. What does data independence allow in a database system?
A) Changing data without changing schema
B) Changing schema without affecting application programs
C) Running applications without a database
D) Storing data independently of hardware
2. Which of the following best describes metadata?
A) Unstructured data
B) Data about data
C) Encrypted data
D) Deleted data
3. Which component is foundational to core database architecture?
A) Web browser
B) Storage, buffering, and access paths
C) Word processor
D) Graphic design tools
4. Intelligent data platforms evolved from which earlier technology?
A) Cloud computing
B) File systems
C) Artificial Intelligence
D) Blockchain
5. What is the core unit of meaningful database action?
A) A query
B) A table
C) A transaction
D) A row
6. What do the ACID properties stand for?
A) Atomicity, Consistency, Isolation, Durability
B) Accuracy, Completeness, Integrity, Dependability
C) Access, Control, Identity, Data
D) Allocation, Concurrency, Indexing, Deletion
7. Which anomaly occurs when a transaction reads uncommitted data?
A) Phantom read
B) Dirty read
C) Nonrepeatable read
D) Serialization anomaly
8. What is a common lock-based protocol used for concurrency control?
A) Two-phase commit
B) Two-phase locking (2PL)
C) Write-ahead logging
D) Checkpointing
9. When do deadlocks occur?
A) When the database is turned off
B) When transactions wait indefinitely for each other
C) When a query runs too fast
D) When data is deleted
10. What principle does write-ahead logging (WAL) enforce?
A) Data is written before logs
B) Logs are written to stable storage before data pages
C) Logs are never written
D) Data is only kept in memory
11. Which protocol coordinates distributed transactions?
A) Optimistic concurrency control
B) Two-phase commit (2PC)
C) Timestamp ordering
D) B-tree indexing
12. What does isolation in ACID ensure?
A) Data is saved permanently
B) Transactions execute independently without interference
C) All transactions fail
D) Data is always encrypted
13. What is optimistic concurrency control based on?
A) The assumption that conflicts are rare
B) The assumption that conflicts are frequent
C) Locking all tables
D) Never committing data
14. What is the purpose of recovery in a database system?
A) To delete old data
B) To reconstruct a trustworthy state after an interruption
C) To speed up queries
D) To format the hard drive
15. What is the purpose of relational algebra in query processing?
A) To design user interfaces B) For logical rewriting and optimization of queries C)
To manage hardware D) To encrypt data
16. What does cost-based optimization aim to do?
A) To design user interfaces
B) For logical rewriting and optimization of queries
C) To manage hardware
D) To encrypt data
17. Which of the following is a common index structure?
A) C-tree
B) B-tree
C) D-tree
D) Z-tree
18. Which of the following is a join algorithm?
A) Hash join
B) Split join
C) Jump join
D) Break join
19. What does cardinality estimation predict?
A) The number of users
B) The number of rows returned by an operation
C) The cost of hardware
D) The size of the log file
20. What is partitioning in physical database design?
A) Combining multiple databases
B) Dividing a large table into smaller pieces
C) Deleting unused tables
D) Encrypting specific columns
21. What does materialization refer to?
A) Imagining a database
B) Storing the result of a query or view
C) Deleting indexes
D) Printing data on paper
22. What is an execution plan?
A) A project management timeline
B) The sequence of operations chosen to execute a query
C) A backup schedule
D) A disaster recovery document
23. What does the CAP theorem state about distributed systems?
A) They can guarantee Consistency, Availability, and Partition tolerance
simultaneously
B) They can guarantee at most two of Consistency, Availability, and Partition
tolerance
C) They cannot guarantee any of them
D) They only guarantee Consistency
24. Which of the following is a type of NoSQL database?
A) Relational B) Document C) Hierarchical D) NetworkA) Relational
B) Document
C) Hierarchical
D) Network
25. What is the purpose of replication?
A) To reduce storage space
B) To maintain multiple copies of data for availability and fault tolerance
C) To encrypt data
D) To slow down updates
26. What does eventual consistency mean?
A) Data is never consistent
B) Given enough time without updates, all replicas will converge to the same state
C) Data is consistent immediately
D) Only one replica is ever updated
27. Which database type is best suited for representing complex relationships like
social networks?
A) Key-value store
B) Column-family store
C) Graph database
D) Document database
28. What is a characteristic of NewSQL databases?
A) They abandon ACID properties
B) They combine ACID guarantees with horizontal scalability
C) They only run on mainframes
D) They do not support SQL
29. What is the difference between authentication and authorization?
A) They are the same
B) Authentication verifies identity; authorization determines access rights
C) Authorization verifies identity; authentication determines access rights
D) Both encrypt data
30. What technique is used to hide sensitive data in non-production environments?
A) Indexing
B) Data masking
C) Replication
D) Partitioning
31. What is the purpose of database auditing?
A) To increase storage capacity
B) To track database access and modifications for security and compliance
C) To optimize queries
D) To format disks
32. What does encryption protect?
A) Only data at rest
B) Only data in transit
C) Data at rest and in transit
D) Hardware from theft
33. What is a data warehouse primarily used for?
A) Daily transaction processing
B) Analytical processing and decision support
C) Real-time gaming
D) Web hosting
34. What does ETL stand for?
A) Execute, Test, Load
B) Extract, Transform, Load
C) Enter, Transfer, Leave
D) Evaluate, Track, Log
35. In dimensional modeling, what does a fact table typically contain?
A) Descriptive attributes
B) Measurable, quantitative data
C) User passwords
D) Application code
36. How does ELT differ from ETL?
A) It doesn't extract data
B) Transformation happens after loading into the warehouse
C) It only works with text files
D) It is slower
37. What are the three V's commonly used to characterize Big Data?
A) Value, Vision, Vector
B) Volume, Velocity, Variety
C) Visual, Virtual, Valid
D) Variable, Vague, Vast
38. What does stream processing handle?
A) Data at rest
B) Continuous data in motion
C) Printed reports
D) Archived backups
39. What is a data lake?
A) A structured relational database
B) A repository for storing raw data in its native format
C) A pool of water used for cooling servers
D) A small, highly curated dataset
40. What is the primary goal of high availability operations?
A) To maximize database downtime
B) To minimize database downtime and ensure continuous access
C) To increase storage costs
D) To slow down queries
41. Why is interoperability considered a database problem?
A) Because databases are too slow
B) Because it requires seamless data exchange and semantic consistency across
different systems
C) Because databases cannot connect to networks
D) Because SQL is outdated
42. What is a 'learned database'?
A) A database that users learn to use
B) A database that uses machine learning to optimize its internal operations
C) A database containing educational materials
D) A legacy database
43. What is normalization in database design?
A) Increasing redundancy
B) Organizing data to reduce redundancy and improve integrity
C) Deleting duplicate tables
D) Encrypting database tables
Essay Questions
1. Explain the ACID properties in database systems.
Answer:
• Atomicity
o Transaction is completed fully or not executed at all.
• Consistency
o Database remains in a valid state before and after transaction.
• Isolation
o Transactions execute independently without interference.
• Durability
o Committed data remains stored even after system failure.
2. Compare File Systems and Modern Database Systems.
Answer:
File Systems Database Systems
High redundancy Reduced redundancy
Difficult data sharing Easy data sharing
Limited security Strong security
No concurrency control Supports concurrency control
Program-data dependence Data independence
3. Explain the difference between OLTP and OLAP systems.
Answer:
OLTP OLAP
Handles daily transactions Handles analytical queries
Fast insert/update operations Complex aggregations
operational data Historical data
Normalized schema Dimensional schema
Example: Banking system Example: Data warehouse
4. Explain the importance of concurrency control.
Answer:
Importance:
• Prevents data inconsistency.
• Allows multiple users at the same time.
• Maintains transaction isolation.
Common Techniques:
• (2PL)
• Timestamp Ordering
• Optimistic Concurrency Control
6. Compare SQL Databases and NoSQL Databases.
Answer:
SQL Databases NoSQL Databases
Relational structure Non-relational structure
Fixed schema Flexible schema
Strong ACID support Often eventual consistency
Vertical scalability Horizontal scalability
Uses SQL Uses different query models
Examples:
• SQL: MySQL
• NoSQL: MongoDB
7. Compare ETL and ELT.
Answer:
ETL ELT
Transform before loading Transform after loading
Suitable for traditional warehouses Suitable for cloud platforms
Requires staging area Uses warehouse processing power
Slower for big data Faster for large-scale systems
8. Explain advantages and disadvantages Indexing in Databases.
Answer:
Advantages:
• Speeds up data retrieval
• Improves query performance
Disadvantages:
• Requires additional storage
• Slows insert and update operations
9. Explain Stream Processing.
Answer:
• Processes continuous data in motion.
• Supports real-time analytics.
• Handles live events instantly.
Applications:
• Social media analytics
• IoT systems
10. Explain High Availability in Databases.
Answer:
• Minimizes system downtime.
• Improves reliability.
Techniques:
• Replication
• Failover
• Backup servers