Lovely Professional University 2011 B.Tech Computer Science and Engineering DATABASE MANAGEMENT SYSTEM ASSIGNEMNT 2 solution - Question Paper

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

ANS:

a. select name from employee e, books b, loan l where e.empno = l.empno and l.isbn = b.isbn and b.publisher = 'McGrawHill'

b. select name from employee e join loan l on e.empno=l.empno join (select isbn from books where publisher = 'McGrawHill') x on l.isbn=x.isbn group by e.empno,name having count(*)= (select count(*) from books where publisher='McGrawHill')

c. select name from employee,loan where employee.empno=loan.empno and isbn in ( choose distinct isbn from books where publisher='McGraw-Hill') group by employee.empno,name having count(isbn) >=5
d. select name from employee,loan,books where employee.empno=loan.empno and books.isbn=loan.isbn group by employee.empno, name,books.publisher having count(loan.isbn) >=5


2. Formulate the above queries in relational algebra also.

ANS:
i. p name (employee ? (pempno (Loan ? (p isbn(? publisher='McGrawHILL' (books)))))
ii. p name, isbn(employee ? pempno (loan ?books) ÷ p isbn(? publisher='McGrawHILL' (books))
iii. p name (employee ? pempno loan ?books (p isbn(? publisher='McGrawHILL' ^count(isbn)>5(books) )
iv. p name (employee ? pempno loan ?books (p isbn(?publisher='McGrawHILL' ^count(isbn)>5(books) )

3. How will you perform changes using relational algebra

ANS: The relational algebra is a procedural query language. It consists of a set of operations that take 1 or 2 relations as input and produce a new relation as their outcome. The fundamental operations in the relational algebra are select, project, union, set difference, Cartesian product, and rename. In addition to the fundamental operations, there are several other operations—namely, set intersection, natural join, division, and assignment.

The choose Operation: The choose operation chooses tuples that satisfy a provided predicate. We use the lowercase Greek letter sigma (s) to denote selection. The predicate appears as a subscript to s.
The argument relation is in parentheses after the s.

The Project Operation: The project operation is a unary operation that returns its argument relation, with certain attributes left out. Since a relation is a set, any duplicate rows are eliminated.
Projection is denoted by the uppercase Greek letter pi (?)

The Set Difference Operation: The set-difference operation, denoted by -, allows us to obtain tuples that are in 1 relation but are not in a different. The expression r - s produces a relation containing those tuples in r but not in s.

The Cartesian-Product Operation: The Cartesian-product operation, denoted by a cross (×), allows us to combine info from any 2 relations. We write the Cartesian product of relations r1 and r2 as r1 × r2.

The Rename Operation: Unlike relations in the database, the outcomes of relational-algebra expressions do not have a name that we can use to refer to them. It is useful to be able to provide them names; the rename operator, denoted by the lowercase Greek letter rho (?)

The Set-Intersection Operation: The 1st additional-relational algebra operation that we shall describe is set intersection (n). Suppose that we wish to obtain all customers who have both a loan and an account.

The Natural-Join Operation: It is often desirable to simplify certain queries that require a Cartesian product. Usually, a query that involves a Cartesian product includes a selection operation on the outcome of the Cartesian product.

The Division Operation: The division operation, denoted by ÷, is suited to queries that include the phrase "for all."

Modification is done by the mainly subsequent techniques: Selection and Projection


PART – B

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

ANS:
JOIN Operator
JOIN is used to combine related tuples from 2 relations:
• In its simplest form the JOIN operator is just the cross product of the 2 relations.
• As the join becomes more complex, tuples are removed within the cross product to make the outcome of the join more worthwhile.
• JOIN allows you to evaluate a join condition ranging from the attributes of the relations on which the join is undertaken.
JOIN Example

Natural Join
Invariably the JOIN involves an equality test, and thus is often defined as an equi-join. Such joins outcome in 2 attributes in the resulting relation having exactly the identical value. A `natural join' will remove the duplicate attribute(s).
• In most systems a natural join will require that the attributes have the identical name to identify the attribute(s) to be used in the join. This may require a renaming mechanism.
• If you do use natural joins make sure that the relations do not have 2 attributes with the identical name by accident.

OUTER JOINs
Notice that much of the data is lost when applying a join to 2 relations. In a few cases this lost data might hold useful info. An outer join retains the info that would have been lost from the tables, replacing missing data with nulls.
There are 3 forms of the outer join, depending on which data is to be kept.
• LEFT OUTER JOIN - keep data from the left-hand table
• RIGHT OUTER JOIN - keep data from the right-hand table
• FULL OUTER JOIN - keep data from both tables


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.

ANS:

Algorithm:
2 create table customer and insert values into it
3 declare the variables
4 enter the value for customer number
5 obtain the units by subtracting current and past studying
6 if unit<20 then set change = nil
7 else if unit>=20 and unit<=100 then charge = unit * 50/100
8 else if unit<=300 and unit>=101 then charge= unit*75/100
9 else if unit<=500 and unit>=301 then charge= unit*150/100
10 else charge= unit*225/100
11 end if
12 pr int the electricity bill.
13 stop

Input table
SQL> choose * from customer;
CUST ID PRE-READ PAST –READ
101 540 350
102 350 100
103 780 200
104 650 630
105 650 640
Program:
declare
cid number;
p1 number;
p2 number;
u number;
chrge varchar(5);
start
cid := &customerno;
choose pre_read,past_read into p1,p2 from customer where custid=cid;
u:=p1-p2;
if u<20 then
chrge:='NIL';
elsif u> 20 and u<100 then
chrge:=u*50/100;
elsif u>101 and u<300 then
chrge:= u*75/100;
elsif u>301 and u<500 then
chrge:= u*150/100;
else
chrge:=u* 225/100;
endif;
dbms_output.put_line('ELECTRICITY BILL');
dbms_output.put_line('customer No:'||cid);
dbms_output.put_line('Present Reading:'||p1);
dbms_output.put_line('Past Reading:'||p2);
dbms_output.put_line('Units Consumed:'||u);
dbms_output.put_line('Charge:'||chrge);
end;

Output:
Enter the value for customer no: 103
ELECTRICITY BILL
Customer No: 103
current Reading: 780
Past Reading: 200
Units consumed: 580
Charge: 1305

6. Identify various aggregate functions in SQL. provide examples.

ANS:
a. The SQL COUNT function returns the number of rows in a table satisfying the criteria specified in the WHERE clause.
b. The SQL SUM function is used to choose the sum of values from numeric column.
c. The SQL AVG function retrieves the avg. value for a numeric column.

d. The SQL MIN function chooses the smallest number from a numeric column.
e. The SQL MAX function retrieves the maximum numeric value from a numeric column.
Table Name: A
Name Salary
Emil 5000
Chang 5000
Emily 4500
Nick 4000

AVG : choose AVG(Salary) FROM A
Value = 4675

COUNT : choose COUNT(*) FROM A
Value = 4

choose COUNT (DISTINCT Salary) FROM CUSTOMERS
Value = 3

MAX : choose MAX(Salary) FROM CUSTOMERS
Value = 5000

MAX : choose MIN(Salary) FROM CUSTOMERS
Value = 4000

SUM : choose SUM(Salary) FROM CUSTOMERS
Value = 18500

Earning:   Approval pending.