Database Management Systems (CS-502) - Important Questions

Explain the database approach and compare it with the traditional file processing system, including advantages of database systems.

Explain the detailed system architecture / structure of a DBMS, including its major layers and components.

Explain generalization, specialization and aggregation in ER diagrams with suitable examples/diagrams.

Draw an ER diagram for a library system and transform it into relational tables.

Explain primary key, foreign key and various key / integrity constraints used in a database system.

Discuss in detail the SELECT, PROJECT and UNION operators with suitable examples.

Write SQL queries to retrieve data from single and multiple tables using joins, GROUP BY, HAVING and nested queries.

Consider R(A,B,C,D,E,F) with FDs A->BC, C->E, E->F, F->AB. Find all candidate keys and determine the highest normal form of the relation R.

Explain normalization and functional dependency, and discuss First (1NF), Second (2NF), Third (3NF) and Boyce-Codd (BCNF) normal forms with suitable examples.

Discuss the major steps in query optimization, including sigma cascade operation.

Give a brief note on query optimization and its importance.

Draw the precedence graph for a given concurrent schedule and determine whether it is conflict serializable. Find the equivalent serial schedule if serializable.

How is concurrency performed? Explain concurrency control protocols such as locking and timestamping.

Explain log-based recovery and checkpoints with an example.

Explain cursor management, including nested and parameterized cursors.

Write SQL queries to demonstrate equi-join, non equi-join, self-join, outer join and use of LIKE, IN, EXISTS, ANY, ALL operators.