Database Management Systems (AL-502) - Important Questions

What is ER Diagram / ER-model? Explain its components and notations/symbols for entities, attributes and relationships, with an ER diagram illustration e.g., for Employee Project Management System.

Explain the various data models of DBMS in detail.

Describe the architecture of DBMS and explain each component.

What is schema? Explain overall database structure with suitable example.

Define database languages in DBMS and describe DDL, DML, DCL and TCL with commands and examples.

Explain keys in the relational model 014 super key, candidate key, primary key, alternate key and foreign key 014 with suitable examples.

Explain integrity constraints over relations and their types (domain, entity, referential integrity) with suitable examples.

Explain Fundamental operations in relational algebra with examples.

Consider relations Employee(Eid, Ename, Dept, Salary) and Project(Pid, Pname, Eid). Write relational algebra expressions using select, project, join, division and outer union for: (i) names of employees with salary > 40000, (ii) natural join of Employee and Project, (iii) employees working on all projects.

Given a relation with functional dependencies, find candidate keys / identify normal form and decompose/normalize the relation up to 3NF. e.g., R(A,B,C,D,E) with A->B, B->C, A->D, D->E.

State BCNF. Explain with example how BCNF differs from 3NF.

What is a locking protocol? Explain shared locks, exclusive locks and strict two-phase locking protocol for concurrency control.

What is serializability and how to test serializability of a schedule, including conflict serializable and view serializable schedules with examples?

Discuss recovery from transaction failures. Explain log-based recovery techniques and the role of checkpoints in recovery.

What is a cursor? Explain nested and parameterized cursors with examples.

Write a short note on stored procedures in PL/SQL with syntax and example. Discuss its advantages.