Lovely Professional University 2011 B.Tech Computer Science and Engineering Database management system-assignment 2 - Question Paper

Assignment – II


PART – A

1. The subsequent tables form part of a database held in a relational DBMS:

Employee (empno, name, office, age)
Books (isbn, title, authors, publisher)
Loan (empno, isbn, date)

Write the subsequent queries in SQL

(a) Find the name of all employees who have borrowed a book published by McGraw-Hill.
(b) Find the name of all employees who have borrowed all book published by McGraw-Hill.
(c) Find the names of employees who have borrowed more than 5 various books published by McGraw-Hill.
(d) For every publisher, obtain the name of employees who have borrowed more than 5 books of that publisher

2. Formulate the above queries in relational algebra also.

3. How will you perform changes using relational algebra

PART – B

4. How join can be expressed in basic relational algebra operators? Justify with example

5. Write a PL/SQL code for generating the electricity bills of customers. Make use of functions and cursors. Apply triggers also to automatically update the database to replace customer's total bill.

6. Identify various aggregate functions in SQL. provide examples

Earning:   Approval pending.