DBMS Question Bank – Detailed
Answers
UNIT – 3: SQL & RELATIONAL CONCEPTS
1. a) SQL SELECT & PROJECT:
- SELECT retrieves rows, PROJECT retrieves columns.
- SELECT Example: SELECT * FROM Students WHERE age > 20;
- PROJECT Example: SELECT name, age FROM Students;
b) Arithmetic & Logical Operations in SQL:
- Arithmetic: +, -, *, /; Example: SELECT salary + 1000 FROM Employees;
- Logical: AND, OR, NOT; Example: SELECT * FROM Employees WHERE age > 25 AND
dept = 'HR';
2. Relational Set Operations:
- UNION: Combines results, no duplicates.
- INTERSECT: Returns common rows.
- EXCEPT/MINUS: Rows in one not in other.
3. Categories of SQL Functions:
- Aggregate: SUM(), AVG(), COUNT(), etc.
- Scalar: UCASE(), NOW(), etc.
- Date: CURDATE(), DATEDIFF()
- String: CONCAT(), LENGTH()
4. Creating Table with Relationships:
- Use FOREIGN KEY for relationships.
- Types: One-to-One, One-to-Many, Many-to-Many.
5. Nested Queries & Correlation:
- Nested: Query inside query.
- Correlated: Inner uses outer values.
- Operators: IN, EXISTS, ANY, ALL, UNIQUE
6. Integrity Constraints:
- NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.
7. Grouping, Aggregation, Ordering:
- GROUP BY, Aggregation functions like COUNT(), ORDER BY for sorting.
8. Joins:
- INNER, LEFT, RIGHT, FULL JOINS combine rows from tables.
9. Views:
- Virtual table, enables logical independence, used for security.
10. Based on Schema:
- a) FOREIGN KEY(managerid) REFERENCES Emp(eid)
- b) Create tables with appropriate keys.
- c) Ensure managerid is NOT NULL
- d) INSERT INTO Emp VALUES(101, 'John Doe', 32, 15000);
- e) UPDATE Emp SET salary = salary * 1.1;
- f) DELETE FROM Dept WHERE dname = 'Toy'; → May fail if foreign key exists.
UNIT – 4: NORMALIZATION & FUNCTIONAL DEPENDENCIES
1. Anomalies:
- Insertion, Deletion, Update problems due to redundancy.
2. Functional Dependency:
- X → Y means Y depends on X.
- Types: Trivial, Non-trivial, Transitive.
3. 3NF vs BCNF:
- 3NF allows non-prime attributes on candidate key.
- BCNF: LHS must be super key.
4. 1NF, 2NF, 3NF:
- 1NF: Atomic values.
- 2NF: No partial dependencies.
- 3NF: No transitive dependencies.
5. BCNF:
- Stronger than 3NF, LHS of FDs must be super key.
6. 4NF & 5NF:
- 4NF: Removes multivalued dependencies.
- 5NF: Removes join dependencies.
7. FD Inference Rules:
- Armstrong’s axioms: Reflexive, Augmentation, Transitive, etc.
8. Lossless Join & Dependency Preservation:
- Lossless: No data lost.
- Dependency Preservation: Constraints maintained.
9. Importance of Dependency Preservation:
- Ensures original constraints are still enforceable.
10. Lossless Join Example:
- Join returns original relation, avoids spurious tuples.
UNIT – 5: TRANSACTIONS & INDEXING
1. ACID Properties:
- Atomicity, Consistency, Isolation, Durability.
2. Transaction States:
- Active → Partially Committed → Committed / Aborted.
3. Shadow-Copy:
- Maintains original and shadow copy, ensures durability.
4. B+ Tree:
- Balanced index structure, fast for range queries.
5. Conflict Serializability:
- Transactions reordered if no conflict using precedence graph.
6. View Serializability:
- Equivalent output, even if operation order differs.
7. Hash-Based Indexing:
- Hashes used for fast lookup, best for exact matches.
8. Index Types:
- Primary: On primary key.
- Clustered: Alters row order.
- Secondary: On non-primary attributes.
