ASSIGNMENT

OF
DBMS

Submitted to: Submitted By:

Mr. Ramandeep Anish Sethi
Btech Hons MBA (CSE A)

RA1702C069
Q-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 -

Ans- A)

a) Select Name from

Employee, books b, loanl

Where c.empno = l.empno

And l.asbn= b.isbn and

b.publishers = MCGRAWHILL

b) Select Distinct empname

from employe e

where not exists

(select.isbn

From books

Where publishers = ‘MCGRAWHILL’)

Except

(select.isbn

From Loanl

Where l.empno=e.empno)

c) Select pulisher, name

from( Select.publisher,name,isbn,cnt)

from employee e,book, loanl

and l.isbn=b.isbn

group by (publisher, name) and

publisher MCGRAWHILL

d) Select Publisher , name

From ( select publisher , name, cont,isbn)

From employee e , books b, loan l

where e.empno = l.empno

and l.isbn=b.isbn

groupby (Publisher, name)as

emplist (Publisher ,name, cont, books)

where cont book 15.

Q2 -Formulate the above queries in relational algebra also ?

Ans
a. Πperson-name (σcompany-name = “MCGRAWHILL” (borrowed))
employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, Borrower-name)
b. Πperson-name, city (employee _
(σcompany-name = “Publisher” (Borrowed)))

c. Πperson-name, street, city

(σ(company-name = “Publisher” ∧ MCGRAWHILL > 5)
works _ employee)

d. Πperson-name (employee _ works _ company)

e.
Πcompany-name (company ÷
(Πcity (σcompany-name=“MCGRAWHILL” (company))))

Q-3 How will you perform modifications using relational algebra ?

Ans- Modification is done by following techniques

Selection
Projection
 It's important because it will teach you what the idea is behind grouping attributes together to
entities and use them to define the meaning of data and above all: creating new entities from them
by using projections.

Normalization and De-normalization

Relational models are in a given normal form. This normal form is a way to describe how much
redundant information is stored in the data set defined by the relational model. To transform a
model from a lower normal form to a higher normal form is called normalization. The other way
around is called de-normalization. Normalization is considered a good thing because it solves various
problems which arise with redundant information in a de-normalized model, if you perform data
manipulation operations.
Relations in relational algebra are seen as sets of tuples, so we can use basic set operations.

Projection
Example: The table E (for EMPLOYEE)

nr name salary

1 John 100

5 Sarah 300

7 Tom 100

SQL Result Relational algebra

select salary salary PROJECTsalary(E)

from E
100
 300

nr Salary

1 100
select nr, salary PROJECTnr, salary(E)
from E 5 300

7 100

Note that there are no duplicate rows in the result.

Selection :
The same table E (for EMPLOYEE) as above.

SQL Result Relational algebra

nr name salary
select *
from E 1 John 100 SELECTsalary < 200(E)
where salary < 200
7 Tom 100

select * nr name salary

from E SELECTsalary < 200 and nr >= 7(E)
where salary < 200 7 Tom 100
and nr >= 7

Note that the select operation in relational algebra has nothing to do with the SQL keyword select.
Selection in relational algebra returns those tuples in a relation that fulfil a condition, while the SQL
keyword selectmeans "here comes an SQL statement".

Relational algebra expressions :

SQL Result Relational algebra

name Salary PROJECTname, salary (SELECTsalary < 200(E))

select name, salary or, step by step, using an intermediate result
from E John 100
where salary < 200 Temp <- SELECTsalary < 200(E) 
Tom 100
Result <- PROJECTname, salary(Temp)

PROJECTNamn ( SELECTMedlemsnummer < 3 ( Medlem ) )

 PART – B

Q-4 How join can be expressed in basic relational algebra operators? Justify with
example ?
Ans- Join ( called "inner join")

The cartesian product example above combined each employee with each department. If we only
keep those lines where the dept attribute for the employee is equal to the dnr (the department
number) of the department, we get a nice list of the employees, and the department that each
employee works for:

SQL Result Relational algebra

enr ename dept dnr dname

SELECTdept = dnr (E X D)
select * 1 Bill A A Marketing
from E, D or, using the equivalent join operation
where dept = dnr 2 Sarah C C Legal
E JOINdept = dnr D
3 John A A Marketing

 If we assume that these relational algebra expressions are executed, inside a relational DBMS
which uses relational algebra operations as its lower-level internal operations, different
relational algebra expressions can take very different time (and memory) to execute.

Natural join

A normal inner join, but using the join condition that columns with the same names should be
equal. Duplicate columns are removed. The Natural Join

The natural join is a binary operation that is written as R |×| S where R and S are relations. The result
of the cartesion product is the set of all combinations of tupels in R and S that are equal on their
common attribute names. More formally:

R |×| S = { t ∪ s : t ∈ R, s ∈ S, fun(t ∪ s) }

where fun(r) is a predicate that is true for a binary relation r iff r is a functional binary relation. This
operation can be regarded as a generalization of the previously defined cartesian product since it is
the special case where Rand S have no common attributes.

Renaming tables and columns

Example: The table E (for EMPLOYEE)

nr name dept

1 Bill A
2 Sarah C

3 John A

Example: The table D (for DEPARTMENT)

nr name

A Marketing

B Sales

C Legal

We want to join these tables, but:

 Several columns in the result will have the same name (nr and name).
 How do we express the join condition, when there are two columns called nr?

Solutions:

 Rename the attributes, using the rename operator.

 Keep the names, and prefix them with the table name, as is done in SQL. (This is somewhat
unorthodox.)

SQL Result Relational algebra

enr ename dept dnr dname

select *
from E as E(enr, 1 Bill A A Marketing
(RENAME(enr, ename, dept)(E)) JOINdept =
ename, dept), dnr (RENAME(dnr, dname)(D))
D as D(dnr, dname) 2 Sarah C C Legal
where dept = dnr
3 John A A Marketing

nr name dept nr name

select * 1 Bill A A Marketing

from E, D E JOINdept = D.nr D
where dept = D.nr 2 Sarah C C Legal

3 John A A Marketing
You can use another variant of the renaming operator to change the name of a table, for example to
change the name of E to R. This is necessary when joining a table with itself (see below).

RENAMER(E)

A third variant lets you rename both the table and the columns:

RENAMER(enr, ename, dept)(E)

Q-4 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 customer’s total bill. ?

Ans
Private Sub getMyRows(inSchema As String, InTable As String)
Dim RS As Object
Dim TableSQL As String
Dim DataType As String
Dim DataLength As String
Dim DataPrecision As String
Dim DataScale As String
Dim ColCount As Integer
Dim WS As Worksheet
create a sheet with the current table as name
Worksheets.Add().Name = InTable
Set RS = CreateObject("ADODB.recordset")
TableSQL = "Select * from " & inSchema & "." & InTable
grab the data
RS.Open TableSQL, conn, adOpenStatic
For ColCount = 0 To RS.Fields.Count - 1
set column headings to match table
ActiveSheet.Cells(1, ColCount + 1).Value = RS.Fields(ColCount).Name
Next

copy table data to sheet

With Worksheets(InTable).Range("A2")
CopyFromRecordset RS
End With
RS.Close

End Sub

Q-5 Identify different scalar and aggregate functions in SQL. Give examples ?
Ans
SQL Scalar Numeric Functions

The Scalar Numeric Function operates on numeric values (i.e. INTEGER, SMALLINT, DECIMAL, FLOAT,
DOUBLE and NUMERIC data types). The PointBase database supports the following standard Numeric
Functions:

 Multiplication
 Division
 Addition
 Subtraction

The numeric functions are evaluated in the following order. Numeric Functions within parentheses
are evaluated from the innermost set of parentheses, following the same rules of precedence:

1. Multiplication (*) and division (/) from left to right

2. Addition (+) and subtraction (-) from left to right

Examples

2 + 3 * 4 / 2 = 8

2 + (3 * 4) / 2 = 8

2 + 3 / 2 = 3.5

SQL Aggregate Functions

SQL Aggregate Functions operate on complete sets of data and return a single result. PointBase
supports five Aggregate Functions: AVG, COUNT, MAX, MIN, and SUM.

AVG

The AVG Function returns the average value for the column when applied to a column containing
numeric data. The following is the syntax for the AVG Function.

AVG (column_name)

Example
SELECT AVG(commission_rate) FROM sales_rep_tbl

COUNT

The COUNT Function returns the number of rows in a specified result set. The following syntax is one
form of the COUNT Function:

COUNT(*)
Example
SELECT COUNT(*) FROM sales_rep_tbl

The second form of the COUNT Function returns the number of rows in a result set where the
specified column has a distinct, non-NULL value. The following syntax is the second form of the
COUNT Function.

COUNT(DISTINCT column_name)

MAX

The MAX Function returns the data item with the highest value for a column when applied to a
column containing numeric data. If you apply the MAX Function to a CHARACTER value, it returns the
last value in the sorted values for that column. The following syntax is for the MAX Function.

MAX(column_name)

Example
SELECT MAX(commission_rate) FROM sales_rep_tbl

MIN

The MIN Function returns the data item with the lowest value for a column when applied to a column
containing numeric data. If you apply the MIN Function to a CHARACTER value, it returns the first
value in the sorted values for that column. The following syntax is for the MIN Function.

MIN(column_name)

Example
SELECT MIN(commission_rate) FROM sales_rep_tbl

SUM

The SUM Function returns the sum of all values in the specified column. The result of the SUM
Function has the same precision as the column on which it is operating. The following syntax is for
the SUM Function.

SUM(column_name)

Example
SELECT SUM(ytd_sales) FROM sales_rep_tbl

Aggregate functions
Example: The table E (for EMPLOYEE)

nr name salary dept

1 John 100 A
5 Sarah 300 C

7 Tom 100 A

12 Anne null C

SQL Result Relational algebra

sum
select sum(salary) Fsum(salary)(E)
from E 500

 Duplicates are not eliminated.

 Null values are ignored.