Database Management System - Dec 2015

Computer Engineering (Semester 3)

TOTAL MARKS: 100
TOTAL TIME: 3 HOURS (1) Question 1 is compulsory.
(2) Attempt any four from the remaining questions.
(3) Assume data wherever required.
(4) Figures to the right indicate full marks.

Short Questions

1(a) Define DBMS and list out purpose of DBMS.(1 marks) 1(b) Explain generalization and specialization in ER diagram with suitable example.(1 marks) 1(c) Draw symbols for following in E-R diagram: Weak Entity set, Derived attribute(1 marks) 1(d) List out the Mapping Cardinalities in ER diagram.(1 marks) 1(e) Define Transaction(1 marks) 1(f) Who is a DBA?(1 marks) 1(g) _______ can be used to retrieve data from multiple tables.
A. Embedded SQL.
B. Dynamic SQL.
C. Joins.
D. Views.(1 marks) 1(h) SQL belongs to the category of _______ .
A. 2GL.
B. 3GL.
C. 4GL.
D. 5GL.(1 marks) 1(i) _______ is data about data.
A. Data type.
B. Data item.
C. Meta data.
D. Information.(1 marks) 1(j) To modify the students table and to add a primary key on the student_id Column, Which statement must be used to accomplishes this task? Note: The table is currently empty.
A. Alter table students add primary key student_id;
B. Alter table students add constraint primary key (student_id);
C. Alter table students add constraint stud_id_pk primary key student_id;
D. Alter table students add constraint stud_id_pk primary key (student_id);(1 marks) 1(k) Which clause should be used to exclude group results?
A. WHERE
B. HAVING.
C. RESTRICT.
D. GROUP BY.(1 marks) 1(l) New fields can be added to the created table by using _______ command.
A. ALTER.
B. SELECT.
C. CREATE.
D. UPDATE(1 marks) 1(m) For which action the TO_DATE function can be used?
A. To convert any date literal to a date.
B. To convert any numeric literal to a date.
C. To convert any character literal to a date.
D. To convert any date to a character literal(1 marks) 1(n) The _______ knows the details of the data storage.
A. decision support system analyst.
B. database administrator.
C. database manager.
D. transaction manager.(1 marks) 2(a) Explain the dirty read problem(3 marks) 2(b) During its execution, a transaction passes through several states, until it finally commits or aborts. List all possible sequences of states through which a transaction may pass. Explain why each state transition may occur.(4 marks)

Solved any question from 2(c) & 2(d)

2(c) Solve the queries for the following database using Relational Algebra branch (branch-name, branch-city, assets)
branch (branch-name, branch-city, assets)
customer (customer-name, customer-street, customer-only)
account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)
1)Find all loans of over $1200 \ltbr\gt 2)Find the loan number for each loan of an amount greater than $1200
3)Find the names of all customers who have a loan, an account, or both, from the bank
4)Find the names of all customers who have a loan and an account at bank.
5)Find the names of all customers who have a loan at the Perryridge branch.
6)Find the names of all customers who have a loan at the Perryridge branch but do not have an account at any branch of the bank.
7)Find the names of all customers who have a loan & an account at the Perryridge branch.(7 marks) 2(d) Draw an ER diagram for describing the activities of a departmental store(7 marks)

Solved any question from Q.3 & Q.4

3(a) Signify the concept of Aggregation in ER Diagram with example.(3 marks) 3(b) Explain following terms with suitable example.
(1) Primary Key (2) Candidate Key (3) Foreign Key (4) Check Constraint(4 marks) 3(c) We have following relations:
EMP (empno, ename, jobtitle, managerno, hiredate, sal, comm, deptno)
DEPT(deptno, dname, loc)
Answer the following queries in SQL.
i) Find the Employees working in the department 10, 20, 30 only
ii) Find Employees whose names start with letter A or letter a.
iii) Find Employees along with their department name.
iv)Insert data in EMP table.
v) Find the Employees who are working in Smith's department
vi) Update Department name of Department No=10
vii)Display employees who are getting maximum salary in each department(7 marks) 4(a) Given relation R with attributes A,B, C,D,E,F and set of FDs as A-> BC, E-> CF, B->E and CD-> EF. Find out closure {A,B} ⁺ of the set of attributes.(3 marks) 4(b) A college keeps details about a student and the various modules the student studied. These details comprise regno - registration number , n - student name , a - student address ,tno - tutor number , tna - tutor name ,dc - diploma code , dn - diploma name , mc - module code , mn - module name , res - module exam result Where details (regno, n, a, tno, tna, dc, dn,(mc,mn,res))
dc -> dn
tno -> tna
mc, mn -> res
n -> a
mc -> mn
Reduce the relation DETAILS to third normal form.(4 marks) 4(c) Draw an ER diagram for a car insurance company that has a set of customers each of whom owns one or more cars. Each car has associated with it 0 to any number of recorded accidents.(7 marks)

Solved any question from Q.5 & Q.6

5(a) Given R= (A, B, C, G, H, I). The following set F of functional dependencies holds
A --> B
A --> C
CG --> H
CG --> I
B --> H
Compute AG ⁺ . Is AG a candidate key?(3 marks) 5(b) How is DBMS better than File Management System?(4 marks) 5(c) Explain two phase locking protocol in detail.(7 marks) 6(a) Compute the closure of R (A, B, C, D, E) with the following set of functional dependencies
A --> BC
CD --> E
B --> D
E --> A
List the candidate keys of R.(3 marks) 6(b) Explain ACID properties of transactions(4 marks) 6(c) Explain Lock-Based Protocols(7 marks)

Solved any question from Q.7 & Q.8

7(a) Explain deadlock with example.(3 marks) 7(b) Prove the statement "Every relation which is in BCNF is in 3NF but the converse is not true"(4 marks) 7(c) Enlist and explain the basic steps in Query Processing(7 marks) 8(a) Assuming worst case memory availability and the following given statistics for the relations customer and depositor
Number of records of
customer: 10,000   depositor: 5000
Number of blocks of
customer: 400   depositor: 100
Estimate the cost
i) with depositor as outer relation
ii) with customer as the outer relation(4 marks) 8(b) Write a PL/SQL cursor to display the names and branch of all students from the STUDENT relation.(4 marks) 8(c) What is cryptography? Explain the difference between symmetric & asymmetric key cryptography.(6 marks)