Database Management Systems - Jun 2015
Computer Science Engg. (Semester 5)
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. 1 (a) Briefly discuss the advantages of using DBMS.(10 marks) 1 (b) Discuss the main characteristics of the database approach. How does it differ from the traditional file systems?(10 marks) 2 (a) List the summary of the notations of ER diagram. Include symbols used ER diagram and their meanings.(10 marks) 2 (b) Define an entity an attribute. Explain the different types of attributes that occur in an ER model with an example.(10 marks) 3 (a) Explain SELECT and PROJECT operations in relational algebra with an example.(10 marks) 3 (b) Explain different types of JOIN operations in relational algebra with example.(10 marks) 4 (a) With respect to SQL, explain with example.
i) DROP command
ii) ALTER command.(10 marks) 4 (b) Given the schema,
EMP(fname, ssn, bdate, address, sex, salary, supperssn , dno)
DEPT (dname, dnumber, mgrssn, mgrstartdate)
PROJECT (pname, pnumber, plot, dnum)
WORKS_ON (essn, pno, hours)
DEPENDENT (essn, department-name, sex)
Write th eSQL queries for the following:
i) List female employees from dno=20 earning more than 50000.
ii) List 'CSE' department details.
iii) Retrieve the firstname, lastname and salary of all employees who work in department no.50.
iv) Select the name of employee whose 1st letter is R and 3rd letter is M.
v) Retrieve the name of the manager of each department.(10 marks)
Explain the following:
5 (a) 1) Aggregate functions in SQL.(5 marks)
5 (a) 2) Insert, update and delete commands.(5 marks)
5 (b) How are triggers and assertions defined in SQL? Explain with example.(10 marks)
6 (a) What is functional dependency (FD)? Write an algorithm to find minimal cover for a set of functional dependencies. Construct minimal cover M for the set of functional dependencies which are
B → A, D →, AB → D.(10 marks) 6 (b) Explain INF, 2NF and 3NF with examples.(10 marks) 7 (a) Explain multi valued dependency and fourth normal form, with an example.(10 marks)
7 (b) i) Inclusion dependencies .(5 marks) 7 (b) ii) Domain key normal form.(5 marks) 8 (a) Briefly discuss the 2 phase locking (2PL) protocol used in concurrency control.(10 marks) 8 (b) Explain all the phase involved in ARIES algorithm with an example.(10 marks)