CS-502 - Database Management Systems

Discuss the role of following in representing information about the real world in a database? [7 Marks]

i) DDL and DML

ii) The buffer manager

iii) The data model

Describe the structure of a DBMS. If your operating system is upgraded to support some new functions on OS files (e.g., the ability to force some sequence of bytes to disk), which layer(s) of the DBMS would you have to rewrite to take advantage of these new functions? [7 Marks]

Consider the following ER diagram with the relations E1(a, b) and E2(c, d). R1 and R2 denote relationships between E1 and E2. Illustrate the table schema of the entities E1, E2 while converting ER Diagram to Relational tables. [7 Marks] alt=''

Draw an ER diagram that captures the following information. [7 Marks] "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) is 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."

Consider the following Employee relation with E_ID as the primary key. [7 Marks] Discuss the anomalies that may arise during Insert, Delete and Updating a record in the above relation.

Consider the following Employee Relation. [7 Marks]

i) Fetch the Dept_ID whose average salary is higher than 4000.

ii) Fetch the Dept_ID whose average salary of male employees is higher than 2000.

iii) Fetch the Name of all employees whose Manager_ID is '1'.

iv) Fetch the Name of all employees of Dept_ID as '103' and their Manager_ID is '1'.

v) Fetch the name, salary, and Dept_ID of employee getting second highest salary.

vi) Fetch the E_ID and Name of employees who are getting salary higher than the salary of employee 'F'.

vii) Fetch the D_ID of employees who are getting salary higher than the salary of employee 'E'.

Discuss the major steps in query optimization? What do you understand by sigma cascade operation? [7 Marks]

Discuss various cost components for query execution. [7 Marks]

Identify the candidate keys and highest normal form of the relation R (ABCDEF). The functional dependencies of the relation R. [7 Marks] $A \to BC$ $C \to E$ $E \to F$ $F \to AB$

Consider a relation R(WXYZ) with the following set of functional dependencies. [7 Marks] $X \to W$ $WZ \to XY$ $Y \to WXZ$ Compute the minimal (canonical) cover of the above relation R.

Consider the following actions taken by transaction T1 on database objects X and Y [7 Marks] R(X), W(X), R(Y), W(Y) Give an example of another transaction T2 that, if run concurrently to transaction T without some form of concurrency control, could interfere with T1.

Consider a schedule S using two phase locking mechanism for concurrency control. Convert the given schedule to strict and rigorous two-phase locking protocol. [7 Marks] Lock Shared(A) Read(A) Lock Exclusive(B) Read(B) B=B+A Write (A) Write (B) Unlock (A) Unlock (B) Commit

Consider the following transactions with two data items A and B. Show the precedence graph for the given concurrent schedule and find equivalent serial schedule. [7 Marks]

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

Oracle exception handling mechanism

Functions of DBA

SQL Aggregate Functions

Distributed databases