CS-502 - Database Management Systems

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.

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 Employee_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 when the following SQL Command: | Emp_Manager | | Employee_ID | Manager_ID | | 2 | 3 | | 4 | 5 | | 6 | 7 | | 8 | 2 | | 10 | 4 | | 12 | 6 | | 14 | 8 | | 16 | 2 | Delete From Emp_Manager Where Employee_ID = '2'

Consider three relations R(a, b), S(b, c) and T(c, d) with following tuple values: R(a, b) = {(0, 1), (4, 5), (8, 9)} S(b, c) = {(1, 2), (5, 2), (5, 6), (5, 10), (13, 10)} T(c, d) = {(2, 3), (6, 7), (10, 11), (10, 3)} Find the result of the following, where $\bowtie$ denotes natural join

i) R $\bowtie$ S

ii) (R $\bowtie$ S) $\bowtie$ T

Consider the following Employee relation with E_ID as the primary key | Employee | | E-ID | Ename | dno | | E1 | T | 101 | | E2 | T | 101 | | E3 | R | 102 | | E4 | M | NULL | | E5 | Y | 104 | Discuss the issues that may arise with the interleaved execution of the transactions T1 and T2.

Consider the following Employee Relation | Employee | | E_ID | E_NAME | Salary | Manager_ID | Dept_ID | | 1 | A | 8000 | NULL | 101 | | 2 | R | 5000 | 1 | 101 | | 3 | T | 2000 | 1 | 101 | | 4 | M | 2000 | 2 | 102 | | 5 | N | 4000 | 2 | 102 | | 6 | S | 2000 | 1 | 103 | | 7 | P | 4500 | 1 | 103 | | 8 | H | 3000 | 1 | 104 | Write SQL Queries for the following:

i) Fetch the E_ID and Name of employees who are getting salary higher than the salary of employee 'P'

ii) Fetch the D_ID of employees who are getting salary higher than the salary of employee 'N'

iii) Fetch the E_ID and Name of employees who are getting salary higher than anyone in the Dept_ID '102'

iv) Fetch the E_ID and Name of employees who are getting salary higher than all employees in the Dept_ID '103'

Discuss the three major steps in query optimization.

Discuss the various exception handling mechanisms in database platforms.

Identify the candidate key and highest normal form of the relation R (E, F, G, H). Following functional dependencies exists for the relation R E$\to$F F$\to$G H$\to$G

Consider a relation R(ABCD) with the following set of functional dependencies A$\to$B B$\to$C C$\to$D D$\to$A The relation R has been decomposed into three sub relations as R1(AB), R2(BC), and R3(CD). Determine the given decomposition satisfies the dependency preservation property or not?

Consider the transaction T as following begin 1. Read (A) 2. Read (B) 3. T = A - 10% of A 4. B = B + t 5. Write (A) 6. Write (B) commit Discuss the transaction states active, partially commit, commit, failure and terminated possible with reference to the above example.

Discuss the issues that may arise with the interleaved execution of the transactions T1 and T2.

Draw the precedence graph for the given concurrent schedule and determine if it is conflict serializable or not?

Illustrate the concept of two-phase locking protocol and its variants with an example.

Write short note on (any two) a) Oracle Application Express

Specialization vs Generalization

Temporal vs Multimedia Databases

Triggers