Sure!
Here's a brief version of all 19 questions, suitable for 10-mark answers in an exam setting:
1. Informal Design Guidelines for Relation Schemas
• Clear Semantics: Attributes should describe the entity clearly.
• Avoid Redundancy: Redundant data causes anomalies.
• Minimize Nulls: Too many nulls imply poor design.
• Avoid Spurious Tuples: Design relations to prevent incorrect join results.
2. 1NF, 2NF, 3NF with Example
• 1NF: No multivalued attributes.
Example: Student(Name, Subjects) → split into Student(Name, Subject)
• 2NF: No partial dependency (applies to composite keys).
Example: Course(CourseID, StudentID, StudentName) → split to remove StudentName if it depends only
on StudentID.
• 3NF: No transitive dependency.
Example: Employee(EmpID, DeptID, DeptName) → split if DeptName depends on DeptID.
3. SQL Constructs with Example
• (i) Nested Query:
• SELECT name FROM Employee WHERE dept_id =
• (SELECT dept_id FROM Department WHERE name = 'HR');
• (ii) Trigger:
• CREATE TRIGGER salary_check BEFORE INSERT ON Employee
• FOR EACH ROW
• WHEN ([Link] < 10000)
• BEGIN
• RAISE_APPLICATION_ERROR(-20001, 'Salary too low');
• END;
• (iii) Schema Change:
• ALTER TABLE Employee ADD COLUMN Phone VARCHAR(10);
4. Aggregate Functions in SQL
Used to perform calculations on multiple rows:
SELECT
SUM(Salary),
MAX(Salary),
MIN(Salary),
AVG(Salary)
FROM Employee;
5. Assertions in SQL (Constraint Example)
CREATE ASSERTION salary_limit
CHECK (NOT EXISTS (
SELECT * FROM Employee WHERE Salary > 200000
));
6. SQL View Example
CREATE VIEW HighEarners AS
SELECT Name, Salary FROM Employee WHERE Salary > 100000;
-- Usage
SELECT * FROM HighEarners;
7. COMPANY Database SQL Queries
i)
SELECT Name FROM EMP WHERE Dno =
(SELECT Dno FROM EMP WHERE Name = 'Ravi') AND Name != 'Ravi';
ii)
SELECT COUNT(*) FROM DEPENDENT
WHERE ESSN = (SELECT SSN FROM EMP WHERE Name = 'Ravi');
iii)
SELECT Name FROM EMP E, DEPT D
WHERE [Link] = [Link] AND Dlocation = 'DELHI'
AND NOT EXISTS (
SELECT * FROM DEPENDENT D1
WHERE [Link] = [Link] AND Sex = 'F'
);
iv)
SELECT Name FROM EMP WHERE Dno = 20 AND Salary > 50000 AND Sex = 'F';
v)
SELECT * FROM DEPT WHERE Dname = 'CSE';
8. SQL Queries on Given Schema
i)
SELECT Fname, Address FROM Employee
WHERE Dno = (SELECT Dnumber FROM Department WHERE Dname = 'Research');
ii)
SELECT DISTINCT Pnumber FROM Project
WHERE Dnum IN (
SELECT Dno FROM Employee WHERE Lname = 'Smith'
OR Pnumber IN (
SELECT Pno FROM Works_On W, Employee E
WHERE [Link] = [Link] AND [Link] = 'Smith'
);
iii)
SELECT Fname FROM Employee
WHERE SSN IN (
SELECT Essn FROM Dependent
);
iv)
SELECT
SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)
FROM Employee;
v)
SELECT Pnumber, Pname, COUNT(Essn)
FROM Project P JOIN Works_On W ON [Link] = [Link]
GROUP BY [Link], [Link];
9. Two Phase Locking Protocol
• Phase 1: Growing – Locks acquired, none released.
• Phase 2: Shrinking – Locks released, no new locks.
• Guarantees serializability by ensuring transactions follow a strict order of locking.
10. Basic Timestamping Algorithm
• Assigns a timestamp to each transaction.
• Uses timestamps to order operations to avoid conflicts.
• Ensures serializability by comparing timestamps for read/write operations.
11. Multi-version Concurrency Control
• Maintains multiple versions of data items.
• Readers access the latest committed version.
• Writers create new versions.
• Reduces locking, increases concurrency.
12. Need for Concurrency Control
• Example: Two transactions updating the same account balance can cause incorrect results.
• Ensures consistency, isolation, and correct results in a multi-user environment.
13. Transaction States Diagram
States:
• Active → Partially Committed → Committed
• Active → Failed → Aborted
• Shows how a transaction progresses or rolls back.
(Draw a diagram with arrows showing state transitions.)
14. ACID Properties
• Atomicity: All or none.
• Consistency: Maintains DB rules.
• Isolation: No interference.
• Durability: Results persist after commit.
15. Database Recovery Techniques
• Log-based Recovery: REDO and UNDO using logs.
• Checkpointing: Save consistent state periodically.
• Shadow Paging: Use copy-on-write method.
• Example: ARIES algorithm.
16. Timestamp Ordering Example
• Assign timestamps: T1(10), T2(20)
• If T2 tries to write a data item read by T1 → conflict → abort T2.
• Maintains serial order by timestamps.
17. Transaction Support in SQL
• BEGIN, COMMIT, ROLLBACK
• Example:
• BEGIN;
• UPDATE Account SET Balance = Balance - 100 WHERE ID = 1;
• UPDATE Account SET Balance = Balance + 100 WHERE ID = 2;
• COMMIT;
18. ARIES Recovery Algorithm
• Uses Write-Ahead Logging (WAL)
• Phases:
1. Analysis
2. Redo
3. Undo
• Supports fine-grained recovery with checkpoints and log sequence numbers.
19. Deadlock and Starvation
• Deadlock: Circular wait.
Solution: Timeout, wait-die, wound-wait.
• Starvation: A transaction never gets a chance.
Solution: Fair scheduling.
Let me know if you'd like a PDF or organized version for printing or submission!