Database Management System - May 2016
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.
1(a) Define RDBMS.(1 marks) 1(b) Write any one difference between traditional file processing system and database management system.(1 marks) 1(c) What do you mean by instances and schemas?(1 marks) 1(d) What is data dictionary?(1 marks) 1(e) Define candidate key.(1 marks) 1(f) Define NULL constraint.(1 marks) 1(g) Define entity set and attribute in E-R diagram.(1 marks) 1(h) Write a symbol for project operation in relational algebra.(1 marks) 1(i) Which command is used to change the name of table?(1 marks) 1(j) Give full form of SQL and DCL.(1 marks) 1(k) Write functionality of DESC command in SQL.(1 marks) 1(l) State true or false: Any relation schema that satisfies BCNF also satisfies 3NF.(1 marks) 1(m) What do you mean by concurrent execution of transaction?(1 marks) 1(n) Give name of method to check the conflict serializability.(1 marks) 2(a) Briefly explain applications of DBMS.(3 marks) 2(b) Discuss the role of database administrator (DBA).(4 marks)
Solve any one question from Q.2(c) & Q.2(d)
2(c) Define E-R diagram. Draw an E-R diagram for Library Management System. Assume relevant entities and attributes for the given system.(7 marks) 2(d) Explain Database Management System (DBMS) architecture with proper diagram.(7 marks)
Solve any three question from Q.3(a), Q.3(b), Q.3(c) & Q.3(d), Q.3(e), Q.3(f)
3(a) List the relational algebra operators. Discuss any one such algebra operator with suitable example.(3 marks)
3(b) Discuss following terms with proper example(s).
1. Referential Integrity (Foreign Key) constraint
2. Check constraint(4 marks) 3(c) Enlist SQL commands for DML.
Consider following relations and write SQL queries for given statements. Assume suitable constrains.
job(job-id, job-title, minimum-salary, maximum-salary)
employee(emp-no, emp-name, emp-salary,dept-no)
deposit(acc-no, cust-name, branch-name, amount, account-date)
borrow(loan-no, cust-name, branch-name, amount)
department (dept-no, dept-name)
1. Give name of depositors whose branch name starts from 'D'.
2. Give employee name(s) whose salary is between Rs. 20000 to 30000 and department name is Finance.
3. Delete borrower details whose amount is less than 25000.(7 marks) 3(d) Discuss aggregate functions with example(s).(3 marks) 3(e) Write a note on mapping cardinality in E-R Diagram.(4 marks) 3(f) Elaborate Normalization. Explain any three normal forms with suitable example(s).(7 marks)
Solve any three question from Q.4(a), Q.4(b), Q.4(c) & Q.4(d), Q.4(e), Q.4(f)
4(a) Define transaction. Explain various states of transaction with suitable diagram.(3 marks) 4(b) Write a note on view serializability.(4 marks) 4(c) Briefly describe immediate database modification for log based recovery.(7 marks) 4(d) Write differences between shared lock and exclusive lock.(3 marks) 4(e) Explain ACID properties of transaction.(4 marks) 4(f) Discuss various steps of query processing with proper diagram.(7 marks)
Solve any three question from Q.5(a), Q.5(b), Q.5(c) & Q.5(d), Q.5(e), Q.5(f)
5(a) Describe GRANT and REVOKE commands.(3 marks) 5(b) Define cursor. Briefly describe types of cursor in PL/SQL.(4 marks) 5(c) Write a note on stored procedure with suitable example.(7 marks) 5(d) State the differences between COMMIT and ROLLBACK Commands.(3 marks) 5(e) Write a note on two phase locking protocol.(4 marks) 5(f) Define PL/SQL. Write PL/SQL block to print sum of even numbers between 1 to 20.(7 marks)