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
Assignment No. 2
Database Management System
Submitted to: Submitted By:
Miss Jaspreet Kaur Shikha Kumari
Roll No. 20
SECTION: A1811
PART A
1. The following 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 following 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 five different books published by McGraw-Hill.
(d) For each publisher, find the name of employees who have borrowed more than five 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 ( select 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. π name (employee ⋈ (πempno (Loan ⋈ (π isbn(Ϭ publisher=McGrawHILL (books)))))
ii. π name, isbn(employee ⋈ πempno (loan ⋈books) π isbn(Ϭ publisher=McGrawHILL (books))
iii. π name (employee ⋈ πempno loan ⋈books (π isbn(Ϭ publisher=McGrawHILL count(isbn)>5(books) )
iv. π name (employee ⋈ πempno loan ⋈books (π isbn(Ϭpublisher=McGrawHILL count(isbn)>5(books) )
3. How will you perform modifications using relational algebra
ANS: The relational algebra is a procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation as their result. 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 operationsnamely, set intersection, natural join, division, and assignment.
The Select Operation: The select operation selects tuples that satisfy a given predicate. We use the lowercase Greek letter sigma (σ) to denote selection. The predicate appears as a subscript to σ.
The argument relation is in parentheses after the σ.
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 find tuples that are in one relation but are not in another. 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 information from any two relations. We write the Cartesian product of relations r1 and r2 as r1 r2.
The Rename Operation: Unlike relations in the database, the results of relational-algebra expressions do not have a name that we can use to refer to them. It is useful to be able to give them names; the rename operator, denoted by the lowercase Greek letter rho (ρ)
The Set-Intersection Operation: The first additional-relational algebra operation that we shall define is set intersection (∩). Suppose that we wish to find 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 result 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 following techniques: Selection and Projection
PART B
4. How join can be expressed in basic relational algebra operators? Justify with example
ANS:
JOIN is used to combine related tuples from two relations:
In its simplest form the JOIN operator is just the cross product of the two relations.
As the join becomes more complex, tuples are removed within the cross product to make the result of the join more meaningful.
JOIN allows you to evaluate a join condition between the attributes of the relations on which the join is undertaken.
Invariably the JOIN involves an equality test, and thus is often described as an equi-join. Such joins result in two attributes in the resulting relation having exactly the same value. A `natural join' will remove the duplicate attribute(s).
In most systems a natural join will require that the attributes have the same 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 two attributes with the same name by accident.
Notice that much of the data is lost when applying a join to two relations. In some cases this lost data might hold useful information. An outer join retains the information that would have been lost from the tables, replacing missing data with nulls.
There are three 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
Examples:
Assume we have two relations: PEOPLE and MENU:
PEOPLE:
|
MENU:
|
PEOPLE people.food = menu.food MENU
Name |
Age |
people.Food |
menu.Food |
Day |
Alice |
21 |
Hamburger |
Hamburger |
Tuesday |
Bill |
24 |
Pizza |
Pizza |
Monday |
Carl |
23 |
Beer |
NULL |
NULL |
Dina |
19 |
Shrimp |
NULL |
NULL |
PEOPLE people.food = menu.food MENU
Name |
Age |
people.Food |
menu.Food |
Day |
Bill |
24 |
Pizza |
Pizza |
Monday |
Alice |
21 |
Hamburger |
Hamburger |
Tuesday |
NULL |
NULL |
NULL |
Chicken |
Wednesday |
NULL |
NULL |
NULL |
Pasta |
Thursday |
NULL |
NULL |
NULL |
Tacos |
Friday |
PEOPLE people.food = menu.food MENU
Name |
Age |
people.Food |
menu.Food |
Day |
Alice |
21 |
Hamburger |
Hamburger |
Tuesday |
Bill |
24 |
Pizza |
Pizza |
Monday |
Carl |
23 |
Beer |
NULL |
NULL |
Dina |
19 |
Shrimp |
NULL |
NULL |
NULL |
NULL |
NULL |
Chicken |
Wednesday |
NULL |
NULL |
NULL |
Pasta |
Thursday |
NULL |
NULL |
NULL |
Tacos |
Friday |
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 modify customers 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 find the units by subtracting present and past reading
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> select * 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);
begin
cid := &customerno;
select 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
Present Reading: 780
Past Reading: 200
Units consumed: 580
Charge: 1305
6. Identify different aggregate functions in SQL. Give 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 select the sum of values from numeric column.
c. The SQL AVG function retrieves the average value for a numeric column.
d. The SQL MIN function selects 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 : Select AVG(Salary) FROM A
Value = 4675
COUNT : Select COUNT(*) FROM A
Value = 4
SELECT COUNT (DISTINCT Salary) FROM CUSTOMERS
Value = 3
MAX : SELECT MAX(Salary) FROM CUSTOMERS
Value = 5000
MAX : SELECT MIN(Salary) FROM CUSTOMERS
Value = 4000
SUM : SELECT SUM(Salary) FROM CUSTOMERS
Value = 18500
Earning: Approval pending. |