Q1. What are relational integrity constraints? Explain with example Domain constraints.
Relational integrity constraints ensure correctness of data in a relational database. Types:
- Domain Constraints: Each attribute must hold values from a specific domain. Example: Marks must be
between 0–100.
- Entity Integrity: Primary key cannot be NULL.
- Referential Integrity: Foreign key must refer to a valid primary key.
Example (Domain): Age INT CHECK(Age>=18).
Q2. Explain 3NF and BCNF and enlist their differences.
3NF: A relation is in 3NF if it is in 2NF and every non-prime attribute depends only on candidate keys.
BCNF: A stronger form of 3NF where every functional dependency X→Y has X as a superkey.
Difference:
- 3NF allows some transitive dependencies, BCNF removes them.
- BCNF ensures stricter design, may require more decomposition.
Q3. What is Functional Dependency (FD)? Explain its use in DB design.
FD is a relationship X→Y meaning value of X uniquely determines Y.
Use: FDs are used for normalization, schema refinement, and eliminating redundancy.
Example: RollNo → StudentName, Branch.
Normalization applies FDs to achieve higher normal forms.
Q5. What is the impact of Insert, Delete & Update anomalies?
- Insert anomaly: Cannot add a record due to missing data. Example: New student without subject info.
- Delete anomaly: Deleting a record removes unintended data. Example: Remove last subject → lose
student info.
- Update anomaly: Need to update multiple rows → inconsistency.
Normalization removes these anomalies.
Q6. What is decomposition? Check lossless/lossy with example.
Decomposition: Splitting relation into smaller relations to remove redundancy.
Condition for lossless: Common attribute must be a key in at least one decomposed relation.
Example: F(FN,PN,C,D) → F1(FN,PN,C), F2(C,D).
FD C→D preserves key, hence decomposition is lossless.
Q9. Explain features of good relational database design.
- Minimal redundancy
- No anomalies
- Dependency preservation
- Lossless join
- Flexibility for queries
- Integrity constraints satisfied
Q10. Define 3NF with example.
3NF: Relation is in 3NF if for every FD X→A, either:
1. X is a superkey OR
2. A is a prime attribute.
Example: Student(RollNo, Name, BranchCode, BranchName).
FD: BranchCode→BranchName.
Not in 3NF.
Decompose into Student(RollNo, Name, BranchCode) & Branch(BranchCode, BranchName).
Q11. Check view serializability of given schedule.
A schedule is view-serializable if it is view-equivalent to a serial schedule.
Steps:
1. Check read-from relations.
2. Final write must match.
3. Blind writes checked.
Conclusion: If equivalent order exists, schedule is view-serializable.
Q12. What is recoverable schedule?
A schedule is recoverable if a transaction commits only after all transactions whose values it read have
committed.
Desirable for consistency.
Non-recoverable may cause cascading aborts.
Q13. What is conflict serializability?
Schedule is conflict-serializable if it can be transformed into serial schedule by swapping non-conflicting
operations.
Conflicts: R/W, W/R, W/W on same item.
Use precedence graph: if acyclic → conflict serializable.
Q14. R-timestamp(Q) and W-timestamp(Q).
- R-timestamp(Q): largest timestamp of transaction that read Q.
- W-timestamp(Q): largest timestamp of transaction that wrote Q.
Timestamp ordering ensures serializability by rejecting invalid reads/writes.
Q15. State and explain ACID properties & transaction states.
ACID: Atomicity, Consistency, Isolation, Durability.
Transaction States: Active → Partially committed → Committed OR Failed → Aborted.
Each transition occurs on completion, error, or commit.
Q16. Atomicity using recovery methods (Log-based recovery).
Log records all actions before applied. Types: Deferred update, Immediate update.
If failure: undo uncommitted, redo committed.
Ensures atomicity by restoring consistent state.
Q17. Locks in DBMS (Shared & Exclusive).
- Shared lock (S): multiple transactions can read.
- Exclusive lock (X): only one can write.
Example: Read balance uses S-lock, Update balance uses X-lock.
Q18. Deadlocks in DBMS.
Deadlock: Transactions wait indefinitely.
Prevention: Ordering, wait-die, wound-wait.
Detection: Wait-for graph.
Recovery: Abort transaction, rollback.
