Database Management Systems: Exam Questions and Solutions
Database Exam Solutions
2025 Q&A
(a) Q: A minimum cardinality of 0 specifies ______ type of participation.
Ans: Partial participation
(b)
Q: Let E1 and E2 be two entity sets with relationships R1 (1:M) and R2 (no attributes). What is the minimum number of tables required?
Ans: 2 tables
(c)
Q: Stud has 120 tuples and Enroll has 8 tuples. What are max and min tuples in Stud NATURAL JOIN Enroll?
Ans:
- Maximum = 8
- Minimum = 0
(d)
Q: Which normal form is considered adequate for normal relational database design?
Ans: Third Normal Form (3NF)
(e)
Q: Find the candidate key for relation R(E, F, G, H, I, J, K, L, M, N) with given FDs.
Ans: E, F, H, L, N
(f)
Q: What is a view?
Ans: A virtual table derived from one or more base tables
(g)
Q: Give equivalent relational algebra for: SELECT DISTINCT a1, a2, …, am FROM R1, R2, …, Rn WHERE P;
Ans: π a1, a2, …, am (σ P (R1 × R2 × … × Rn))
(h)
Q: How is log used in transaction rollback and crash recovery?
Ans: By using log records to undo (rollback) or redo transactions
(i)
Q: What is a RAID system?
Ans: A technique that uses multiple disks to improve performance and reliability
(j)
Q: In a B+ tree with max 5 keys per node, what is the minimum number of keys in a non-root node?
Ans: ⌈5/2⌉ = 3
2024 Q&A
(a) Q: Discuss self join with suitable example.
Ans: Self join is a join where a table is joined with itself using aliases.
(b)
Q: List the problems that can arise due to bad database design.
Ans: Data redundancy, update anomaly, insertion anomaly, deletion anomaly
(c)
Q: Compare and contrast aggregation and composition with suitable examples.
Ans: Aggregation: Weak “has-a” relationship; Composition: Strong “part-of” relationship with dependency
(d)
Q: Given FDs, is BCD → H a valid FD?
Ans: Yes
(e)
Q: Distinguish between centralized and distributed databases.
Ans: Centralized: Data stored at one location; Distributed: Data stored at multiple locations
(f)
Q: Outline the significance of ‘D’ in ACID properties of a transaction.
Ans: Durability ensures that committed data is permanently stored even after system failure
(g)
Q: Define data independence. List different types.
Ans: Data independence is the ability to change schema without affecting applications. Types: Logical and Physical
(h)
Q: How many minimum number of tables required to map given ER scenario?
Ans: 3 tables
(i)
Q: List six theory-based relational algebra operations with examples.
Ans: Selection, Projection, Union, Set Difference, Cartesian Product, Rename
(j)
Q: Which protocol ensures both conflict serializability and freedom from deadlock?
Ans: Time-stamp ordering (II only)
2023 Q&A
(a) Q: List any two roles of a database administrator (DBA).
Ans: Database security management, backup and recovery
(b)
Q: Which level of data abstraction depicts how a record is stored in a database?
Ans: Physical level
(c)
Q: What is the possible number of superkeys of the relation Flight?
Ans: 12
(d)
Q: Define recoverable schedule.
Ans: A schedule where a transaction commits only after all transactions whose data it used have committed
(e)
Q: What is trivial functional dependency? Give an example.
Ans: A functional dependency where RHS is a subset of LHS. Example: A → A
(f)
Q: Find number of attributes and tuples in A × B.
Ans: Attributes = 17, Tuples = 100000
(g)
Q: All conflict serializable schedules are also view serializable. True or False.
Ans: True
(h)
Q: List any two consequences of data redundancy.
Ans: Data inconsistency, increased storage cost
(i)
Q: What are lock-based protocols?
Ans: Concurrency control techniques that use locks to control access to data
(j)
Q: Define clustering index.
Ans: An index that determines the physical order of data in a table
Key Database Concepts
1. DBMS vs File System
- File System stores data in files without relationships or control.
- DBMS manages data using structured schema and relationships.
- File System has high redundancy & low consistency; DBMS reduces redundancy using normalization.
- File System has limited security; DBMS provides authentication & authorization.
- File System does not support concurrency; DBMS supports multi-user access.
- Data independence is absent in file systems but present in DBMS.
2. Role of DBA
- Designs database schema, tables, and constraints.
- Controls user access and security permissions.
- Performs backup and recovery to prevent data loss.
- Optimizes performance using indexing and query tuning.
- Ensures data integrity and concurrency control.
3. ACID Properties
- Atomicity: Transaction is completed fully or not at all.
- Consistency: Database remains in a valid state after transaction.
- Isolation: Transactions execute independently without interference.
- Durability: Committed data is permanently stored.
4. Data Independence
- Logical Data Independence: Changes in logical schema do not affect applications.
- Physical Data Independence: Changes in storage do not affect logical schema.
5. Two-Phase Locking (2PL)
- Growing phase: locks are acquired only.
- Shrinking phase: locks are released only.
- Rule: No lock can be acquired after releasing a lock.
6. Concurrency Problems
- Lost Update: One transaction overwrites another.
- Dirty Read: Reading uncommitted data.
- Non-repeatable Read: Same query gives different results.
- Phantom Read: New rows appear during execution.
7. 3-Level Architecture
- External Level: User-specific views of data.
- Conceptual Level: Overall logical structure of database.
- Internal Level: Physical storage details.
8. Third Normal Form (3NF)
- A relation is in 3NF if it is in 2NF and has no transitive dependency.
- Rule: For FD X → A, X must be a super key or A must be a prime attribute.
9. Armstrong’s Axioms
- Reflexivity: If Y ⊆ X, then X → Y
- Augmentation: If X → Y, then XZ → YZ
- Transitivity: If X → Y and Y → Z, then X → Z