Database Management Systems (CS-502) - Important Questions

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

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

Explain data models, schemas and instances, and what is data independence?

Describe the ER data model, explaining entities, attributes, and relationships with examples.

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

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

Discuss the mechanism of attribute and relationship inheritance and its usefulness.

Discuss the role of DDL and DML, the buffer manager, and the data model in representing real-world information in a database.

Illustrate the relational table schema for entities E1 and E2 with relationships R1 and R2 when converting the given ER diagram to relational tables.

Draw an ER diagram that captures the following information. "A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company."

Write short notes on given database topics including Specialization vs Generalization.

A woman artist designs and sells jewellery through mail orders or at craft shows. Currently she has a mailing list in a word processing file that she uses for sending catalogs. She also keeps both supplier and customer names and addresses in word processing files and prepares bills using a spreadsheet and keeps her income and expenses on another spreadsheet. What would be the advantages for her to design and use a DBMS for all her records.

Explain relational data model concepts: relations, attributes, tuples, domains, and keys.

Write SQL queries to :

Write relational algebra expressions to find employees working in a given department.

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

How does Tuple-oriented relational calculus differ from domain-oriented relational calculus?

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

Write SQL queries on Employee relation involving GROUP BY average salary, filtering by Dept_ID and Manager_ID, second-highest salary, and salary comparison with specific employees.

Design a possible schema for a doctor's office. The doctors want immediate access to patient medical information. The records clerk needs to be sure all insurance companies are billed as per patient details.

Consider the relation Emp_Manager with Employee_ID as the primary key and Manager_ID as the foreign key referencing Employee_ID with on-delete cascade.

Determine all the tuples that will be deleted with the following SQL Command

$Delete\ From\ Emp\_Manager$ $Where\ Employee\_ID = '2'$

Compute the result of natural join(s) on given relations R, S, T.

Consider the following Employee relation with E_ID as the primary key

Discuss the issues that may arise with the following SQL operations in the above relation

Explain normalization and functional dependency, and discuss first, second, and third normal forms.

Normalize a given relation $R$ from its functional dependencies.

Explain query optimization and its importance, including heuristic and cost-based optimization.

Find all candidate keys and determine the highest normal form of a relation $R$ from its functional dependencies.

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

Explain the concept of query optimization / give a brief note on query optimization.

Explain the importance of Null values in Relational Model.

Explain the role of functional dependencies in normalization with examples.

Explain Boyce-Codd normal form (BCNF) with an example.

Explain insertion, deletion, and update anomalies in an unnormalized relation.

Discuss various cost components for query execution.

Compute the minimal (canonical) cover for the given functional dependencies.

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

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

Explain transaction processing and serializability, and differentiate between conflict serializability and view serializability.

Explain log-based recovery and checkpoints with an example.

Explain Timestamping protocols for concurrency control.

What is 2PL protocol? Compare 2PL with Strict 2PL protocol.

What is Transaction? Discuss about transaction recovery techniques.

Write short notes on distributed databases, triggers and join operations.

Given $R(X), W(X), R(Y), W(Y)$ by T1, give an example of a concurrent transaction T2 that could interfere without concurrency control.

Explain the concept of recoverability in database management system. Show any recoverable schedule consisting of two transactions.

Show the precedence graph for a given concurrent schedule and find the equivalent serial schedule.

Given a schedule using 2PL, convert it to strict and rigorous 2PL.

Explain Oracle/MySQL architecture and main memory structures.

Write SQL queries to demonstrate:

Explain cursor management, including nested and parameterized cursors.

Write short notes on Oracle exception handling mechanism and branching and looping constructs in ANSI SQL.

Write short notes on any two of: Oracle exception handling, functions of DBA, SQL aggregate functions, distributed databases.

Consider the following Employee Relation

Write SQL Queries for the following:

Discuss the exception handling mechanisms in database platforms.