Rdbms LC Labguide

RDBMS Exercises for Hands on
ER/CORP/CRS/DB07/004 Education & Research Department

Infosys Technologies Limited 1 of 27
RDBMS
Exercises for Hands-on

Infosys Technologies Ltd
No. 350, Hebbal Electronics City, Hootagalli
Mysore 571186

Author(s) Anubhav Pradhan, Umesha Murthy,
Hanumesh V.J, Seema Acharya
Authorized by Dr. M.P. Ravindra
Creation Date
Version 1.00b


Background
This document contains the assignments to be completed as part of the hands on for the
subject RDBMS (Course code: DB07).

Note: All assignments in this document must be completed in the
sequence in this document in order to complete the course.

Day 1
Assignment 1

Draw an ER diagram to capture the requirements as stated below:
A Company has several business units. Each business unit has multiple projects. Employees
must be assigned to one business unit. One or more employees are assigned to a project, but
an employee may be on vacation and not assigned to any projects. One of the assigned
employees will be project manager for that project.

Assignment 2

In a hospital there are different departments. Patients are treated in these departments by
the doctors assigned to patients. Usually each patient is treated by a single doctor, but in
rare cases they will have two or three. Healthcare assistants will also attend to patients;
every department has many healthcare assistants. Each patient is required to take a variety
of drugs during different parts of the day such as morning, afternoon and night.

Assignment 3

A toy manufacturing company manufactures different types of toys. The company has several
manufacturing plants. Each plant manufactures different types of toys. A customer can place
the order for these toys. Each order may contain one or more toys. Each customer has
multiple ship-to addresses. To promote the business, the company offers different schemes
based on the order value.

Day 2
Assignment 4

Convert the ER model into a relational Schema

Assignment 5
Let us find out the Primany Key and highest normal form for this relation:
R{a,b,c,d,e,} a,e c, d e, a,eb, a,ed
Solution:
Determining primary key : From above stated functional dependencies, it is evident that a,e
determines c and d . d determines e and hence a,e determines all the non-key attributes
(b,c,d) of the relation. Thus a,e is the primary key for relation R.
Highest normal form: All the attributes are atomic in nature therefore Relation R is in 1NF.
All non-key attributes (b,c,d) are fully functionally dependent on the primary key (a,e).
Therefore the relation in is 2NF.
All the non-key attributes (b,c,d) are non-transitively depending on primary key (a,e).
Therefore the relation is in 3NF.
e is a part of the primary key, therefore its dependence on d does not violate 3NF definition.
Relation R is not in BCNF because d determines e and d, although a determinant is not a
candidate key.

Assignment 6
Find the primary key and the highest normal form of following relations
a. R{a,b,c} a,bc and ca
b. R{a,b,c,d} ab, ad and bc
c. R{a,b} ab and ba
d. R{a,b,c,d,e} a,bc, a,bd and de

Assignment 7
Convert following table to 3NF table. Please add appropriate columns in normalized tables to make 3NF compliant.

Assignment 8

Draw the E-R diagram for above normalized Relational schema.

CustomerID Customer_Name Customer_Address Product_Name Product_Brand Product_Category
Sell
Price Date_of_Transaction Qty
101 Lawrence
21, Lion Blvd, St
Peter Excel Surf
Detergent and
Powder 43 11-Feb-05 2
102 Jagan
29, Tiger Blvd,
Burbank GlucoBis Goodday
Cookies and
Confectionaries 8 22-Mar-05 12
103 Bobby
213,Parliament
Blvd, Petas Rotomax KingTime Watch and clocks 120 27-Mar-05 8
104 Ramsey
401,Gazipura
Blvd,Gulbarga SouthernStar CoolPlus Garments 63 5-Apr-05 3
105 Chang Hu
101,Marshal
Blvd,Kingston Winner Fusion Stationary 3 22-Apr-05 27

Day 3
Assignment 9
Configuring ORACLE net service on your machine
Objective: To learn how to create host string using net service to connect oracle client to the
oracle server.
Background: Although on your machine net service is pre configured but please follow these
steps to get a feel of how actually this has been configured.

Step 1:

Reach OracleNet8 Assistant via :
Start->All programs->oracle-orahome81->networkadministration->net8assistant

Step2:

You will get an interface like
Click on service Naming and click the + button to the left

Step 3:

After clicking + wizard appears

Enter Net Service Name as training (If it already exists chose any other name)

Step 4:

Click the next button
Choose TCP/IP as the protocol and click next

Step 5:

Enter Host Name given to you and dont change port number (default is 1521)

Step 6:

Click next

Choose (oracle8i) and service name is training

Step 7:

Test the connection by clicking the test button


Default it will test with the userid as scott and password as tiger,but you give your
UserID and password and test that also

Click on change login button and give your userid and password

For example userid is trng180 password is Infosys

Once again test the connection after giving username and password

Now configuration is tested

Step 8:

After that login into SQL plus by following the set of links as given below


Enter your username ex trng180 password is Infosys and host string is training

you will get SQL prompt and start working
Summary of this exercise:
You have just learnt
Net 8 is the service for communication between oracle client and server
How to configure Net service.

Assignment 10
Solving simple queries

Create two tables - EMP & DEPT

EMP
Column name Data type Description

EMPNO Number Employee number
ENAME Varchar Employee name
JOB Char Designation
MGR Number Managers Emp. number
HIREDATE Date Date of joining
SAL Number Basic Salary
COMM Number Commission
DEPTNO Number Department Number

DEPT
Column name Data type Description

DEPTNO Number Department number
DNAME Varchar Department name
LOC Varchar Location of department

Data for EMP
7369 Smith Clerk 7902 17/12/80 800 20
7499 Allen Salesman 7698 20/2/81 1600 300 30
7521 Ward Salesman 7698 22/2/81 1250 500 30
7566 Jones Manager 7839 2/4/81 2975 20
7654 Martin Salesman 7698 28/9/81 1250 1400 30
7698 Blake Manager 7839 1/5/81 2850 30
7782 Clark Manager 7839 9/6/81 2450 10
7788 Scott Analyst 7566 9/12/82 3000 20
7839 King President 17/11/81 5000 10
7844 Turner Salesman 7698 8/9/81 1500 0 30
7876 Adams Clerk 7788 12/1/83 1100 20
7900 James Clerk 7698 3/12/81 950 30
7902 Ford Analyst 7566 4/12/81 3000 20
7934 Miller Clerk 7782 23/1/82 1300 10

Data for DEPT table
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston

Assignment 11

Perform the following queries on the EMP and DEPT table:

a) List the names of analysts and salesmen.

b) List details of employees who have joined before 30 Sep 81.

c) List names of employees who are not managers.

d) List the names of employees whose employee numbers are 7369, 7521, 7839, 7934,
7788.

e) List employees not belonging to department 30, 40, or 10.

f) List employee names for those who have joined between 30 June and 31 Dec. 81.

g) List the different designations in the company.

h) List the names of employees who are not eligible for commission.

i) List the name and designation of the employee who does not report to anybody.

j) List the employees not assigned to any department.

k) List the employees who are eligible for commission.

l) List employees whose names either start or end with S.

m) List names of employees whose names have i as the second character.

n) List the number of employees working with the company.

o) List the number of designations available in the EMP table.

p) List the total salaries paid to the employees.

q) List the maximum, minimum and average salary in the company.

r) List the maximum salary paid to a salesman.


Assignment 12

Understanding a simple query

Objective: To visualize how a simple query works.

Problem Statement: Let us try to understand how the following query works:

List employees not belonging to department 30, 40, or 10

The solution is:

SELECT ename FROM emp WHERE deptno NOT IN (30, 40, 10);

Let us explore how this query is working.

Step 1: The query filters those tuples which do not belong to deptno 30, 40 or 50, because of
the operator NOT IN, from the emp table.

Step 2: Finally it displays only the employee names of the filtered tuples because we specify
only ename in the SELECT clause.

How a simple query works.
Day 4
Assignment 13
Objective: To visualize how group by and having works.
Problem Statement: Let us try to understand how the following query works:

List the total salary, maximum and minimum salary and average salary of the employees
jobwise, for department 20 and display only those rows having an average salary > 1000

The solution is:

SELECT job, avg(sal) from emp where deptno=20 group by job having avg(sal) > 1000
order by job;

Note: All columns in the column clause of your select statement that are not in
a group function must be listed in the group by clause. However a column
listed in the group by clause neednt appear in the column clause.

Step 1: First thing first we have to select (filter) data only for dept 20. For this we have to
put
WHERE deptno=20
Step 2: Now, we have to find average salary of the employees job wise. Therefore we have
to group our selected (filtered) data using group by clause.

GROUP BY job
Step 3: Now, we have to display only those rows having an average salary > 1000. Therefore
we have to put

HAVING avg(sal) > 1000

Note: When you are using where, group by and having together than the order
should be as follows: where, group by and having.

Step 4: For getting the final output in ascending order of job we have to put order by clause.

ORDER BY job

Note: Order by clause should be the last clause in the query otherwise you will
get the error as follows (In oracle): ORA-00933: SQL command not properly
ended.

You have learnt
Where to use where and having and there differences.
The order of group by, having and where.
The placement of order by.

Assignment 14

14. Perform the following queries against EMP and DEPT tables:

Note: Use sysdate to get the systems date.
For example : select sysdate from emp;

a) List the number of employees and average salary for employees in department 20.
b) List name, salary and PF amount of all employees. (PF is calculated as 10% of basic
salary)
c) List names of employees who are more than 2 years old in the company.
d) List the employee details in the ascending order of their basic salary.
e) List the employee name and hire date in the descending order of the hire date.
f) List employee name, salary, PF, HRA, DA and gross; order the results in the ascending
order of gross. HRA is 50% of the salary and DA is 30% of the salary.
g) List the department numbers and number of employees in each department.
h) List the department number and total salary payable in each department.
i) List the jobs and number of employees in each job. The result should be in the
descending order of the number of employees.
j) List the total salary, maximum and minimum salary and average salary of the
employees jobwise.
k) List the total salary, maximum and minimum salary and average salary of the
employees, for department 20.
l) List the average salary of the employees job wise, for department 20 and display only
those rows having an average salary > 1000


Assignment 15

The following questions pertain to a database with the following tables.
Suppliers
S# SName Status City
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens

Parts
P# PName Color Weight City
P1 Nut Red 12.0 London
P2 Bolt Green 17.0 Paris
P3 Screw Blue 17.0 Rome
P4 Screw Red 14.0 London
P5 Cam Blue 12.0 Paris
P6 Cog Red 19.0 London

Projects
J# Jname City
J1 Montago London
J2 Cat Paris
J3 Box London
J4 Montago Rome
J5 Eagles Athens

Shipment
S# P# J# Qty
S1 P1 J1 350
S1 P3 J3 120
S2 P1 J1 620
S3 P2 J3 700
S2 P2 J4 250
S4 P3 J2 125
S5 P4 J2 325

15. The significance of an SPJ record is that the specified supplier supplies the specified
part to the specified project in the specified quantity (and the combination S#-P#-J#
uniquely identifies such a record).
a) Get full details of all projects in London.
b) Get S# for suppliers who supply project J1.
c) Get all part-color/part-city combinations.
d) Get all S#/P#/J# triples such that all are co-located.
e) Get al S#, P#, J# triples such that they are not all co-located.
f) Get P# for parts supplied by a supplier in London.
g) Get all pairs of cities such that a supplier in the first city supplies to a Project in
the second city.
h) Get J# for projects supplied by at least one supplier not in the same city.
i) Get all pairs of part numbers such that some supplier supplies both the indicated
parts.
j) Get the total quantity of part P1 supplied by S1.
k) For each part supplied to a project, get the P#, J# and corresponding total
quantity.
l) Get P# of parts supplied to some project in an average quantity > 320.
m) Get project names for projects supplied by supplier S1.
n) Get colors of parts supplied by S1.
o) Get J# for projects using at least one part available from supplier S1.
p) Get supplier numbers for suppliers supplying at least one part supplied by at least
one supplier who supplies at least one red part.
q) Get supplier numbers for suppliers with a status lower than that of supplier S1.
r) Get project numbers for projects not supplied with any red part by any London
supplier.


Assignment 16

Objective: To visualize how an inner query works.

Problem Statement: Let us consider the following query:

Get supplier numbers for suppliers with a status lower than that of supplier S1

The solution is:

SELECT S# FROM Suppliers WHERE Status < (SELECT Status FROM Suppliers WHERE
S#=S1);


Step 1: The inner query is executed first which selects the status of supplier s1 from suppliers
table.

Step 2: Now the query looks like this:

SELECT S# FROM Suppliers WHERE Status < 5

(Considering the status of S1 is 5).

Step 3: Above query is again executed as a simple query i.e. tuples are selected for those
suppliers whose status is less than 5 and finally there s# is being displayed.

Inner query executes first and once only.
Day 5
Assignment 17
Objective: To visualize how correlated query works.

Background: Let us consider the following query:

List drivers who have delivered shipments to every city

The solution is:

SELECT a.driver_name FROM truck a WHERE NOT EXISTS (
SELECT * FROM city b WHERE NOT EXISTS(
SELECT * FROM shipment c where c.destination=b.city_name and a.truck# =
c.truck#));


Step 1: The outermost query is executed first and for the first driver_name in truck relation
NOT EXISTS condition is being verified.

Step 2: Against the first driver_name all the cities are selected and again one by one for each
city shipment destination is being checked with the city_name in the innermost query.

NOTE: This is a correlated query because the innermost query is referring to a column in the
next outer query.

Step 3: If the condition c.destination=b.city_name become false for any of the tuple than
NOT EXISTS returns false and hence that city and henceforth that driver is not being
considered in the final output.

Step 4: Step 1 to step3 will be repeated for all the turck drivers in the truck relation.

Working of NOT EXISTS.
A sample correlated query.


Assignment 18
18. Now solve the following set of queries according to given schema:

Write the SQL commands to create a database schema for the following relational schema:

CUSTOMER (CUST_ID, CUST_NAME, ANNUAL_REVENUE, CUST_TYPE)
CUST_ID must be between 100 and 10,000
ANNUAL_REVENUE defaults to $20,000
CUST_TYPE must be manufacturer, wholesaler, or retailer

Data for CUSTOMER table

CUST_ID CUST_NAME ANNUAL_REVENUE CUST_TYPE
100 Revathi 1000000 manufacturer
101 Richa 1800000 wholesaler
102 Rishi 1000000 retailer
103 Rijesh 4000000 wholesaler
104 Kalyan 4800000 Wholesaler
311 Karthik 5500000 retailer

SHIPMENT (SHIPMENT_#, CUST_ID, WEIGHT, TRUCK_#, DESTINATION, SHIP_DATE)
Foreign Key: CUST_ID REFERENCES CUSTOMER, on deletion cascade
Foreign Key: TRUCK_# REFERENCES TRUCK, on deletion set to null
Foreign Key: DESTINATION REFERENCES CITY, on deletion set to null
WEIGHT must be under 1000 and defaults to 10

Data for SHIPMENT table

SHIPMENT_# CUST_ID WEIGHT TRUCK_# DESTINATION SHIP_DATE
100 100 500 100 London Null
101 101 100 102 Paris Null
102 101 300 103 London Null
103 101 10 102 Panama City 12-Dec-03
104 101 20 101 Los Angeles Null
105 102 200 102 Rome Null
106 100 50 101 Sioux City 18-Sep-03
107 104 500 100 Manhattan Null
108 103 50 103 San Francisco Null
109 104 25 101 San Francisco Null
110 102 200 103 London 11-Oct-98
111 103 100 101 London 09-Sep-99
112 104 500 100 London 18-Jun-88
113 104 200 100 London 11-Oct-98
114 104 50 103 Manhattan 29-May-03
115 100 75 103 Los Angeles 17-Sep-02
116 101 55 102 Baltimore 01ul-02
117 103 45 101 Paris Null
118 103 45 100 Rome Null
119 103 45 102 Los Angeles Null
120 104 45 102 London Null
121 100 150 102 Sioux City Null
122 101 500 102 Manhattan Null
123 102 250 102 San Francisco 31-Jul-02
124 311 0.5 102 Denver Null
125 311 100 102 St. Louis Null

TRUCK (TRUCK_#, DRIVER_NAME)

Data for TRUCK table

TRUCK_# DRIVER_NAME
100 Jensen
101 Sasi
102 Hrithik
103 Jake Stinson

CITY (CITY_NAME, POPULATION)

Data for CITY table

CITY_NAME POPULATION
London 100000000
Paris 120000000
Rome 200000000
Panama City 1230000000
San Francisco 20000000
Sioux City 5000000000
Manhattan 10000000
Los Angeles 7000
Baltimore 2000
Denver 1000
St. Louis 5000

Perform the following queries:

a) What are the names of customers who have sent packages (shipments) to Sioux City?
b) To what destinations have companies with revenue less than $1 million sent packages?
c) What are the names and populations of cities that have received shipments weighing
over 100 pounds?
d) Who are the customers having over $5 million in annual revenue who have sent
shipments weighing less than 1 pound?
e) Who are the customers having over $5 million in annual revenue who have sent
shipments weighing less than 1 pound or have sent a shipment to San Francisco?
f) Who are the drivers who have delivered shipments for customers with annual revenue
over $20 million to cities with populations over 1 million?
g) List the cities that have received shipments from customers having over $15 million in
annual revenue.
h) List the names of drivers who have delivered shipments weighing over 100 pounds.
i) List the name and annual revenue of customers who have sent shipments weighing
over 100 pounds.
j) List the name and annual revenue of customers whose shipments have been delivered
by truck driver Jensen.
k) List customers who had shipments delivered by every truck. ( use NOT EXISTS)
l) List cities that have received shipments from every customer. ( use NOT EXISTS)
m) List drivers who have delivered shipments to every city. (use NOT EXISTS)
n) Customers who are manufacturers or have sent a package to St. Louis.
o) Cities of population over 1 million which have received a 100-pound package
From customer 311.
p) Trucks driven by Jake Stinson which have never delivered a shipment to Denver.
q) Customers with annual revenue over $10 million which have sent packages under
1 pound to cities with population less than 10,000.
r) Create views for each of the following:
a. Customers with annual revenue under $1 million.
b. Customers with annual revenue between $1 million and $5 million.
c. Customers with annual revenue over $5 million.

s) Use these views to answer the following queries:
a. Which drivers have taken shipments to Los Angeles for customers with revenue
over $5 million?
b. What are the populations of cities which have received shipments from
customers with revenue between $1 million and $5 million?
c. Which drivers have taken shipments to cities for customers with revenue under
$1 million, and what are the populations of those cities?
Day 6
Assignment 19
Objective: To learn about the exclusive lock.

Background: The different locking mechanisms have been explained to you in the class. For
practice, we will use EMP table (which you have used in Assignments for Day3).

Note: In Oracle the default locking is row exclusive lock for
Update/Delete/Insert

Step 1: Open two instances of SQLPLUS on your machine and login with your Oracle ID in both
the instances.

Step 2: Go to first instance.
Write the following query:

update emp set sal=9000 where empno=7369;

Step 3: Go to the second instance.


Note: The second instance is hanged. The reason is the first
transaction has issued the update statement for the empno 7369 and
it acquires the exclusive(X) lock. The second transaction is also trying
to update the same but because of the X lock acquired by the first
transaction it has to wait.

Step 4: Go to the first instance and issue COMMIT/ROLLBACK.

Step 5: Go to the second instance. You get the SQL prompt.

Note: As soon as you Commit/Rollback the transaction in second
instance the X lock is released.

Step 6: Issue COMMIT/ROLLBACK in the second instance as well.

Update/Delete/Insert DML statement acquire a row exclusive lock
The X lock is released only at the end of transaction which is marked by
Commit/Rollback.
Assignment 20

Objective: To visualize how deadlock occurs.

Step 1: Open two instances of SQLPLUS (If you have closed the previous ones) on your
machine and login with your Oracle ID in both the instances.







Note: The first instance is hanged. What is the reason? Do you
remember the last hands on?



Note: This situation is deadlock. Oracle is smart enough and detect
the same and it rollbacks the statement of Step 4. Check the Oracle
message:
ORA-00060: deadlock detected while waiting for resource

Step 6: Issue COMMIT/ROLLBACK in both the instances.

How to visualize the deadlock.
Oracle intelligently determines the deadlock situation

Rdbms LC Labguide

Uploaded by

Copyright:

Available Formats

Rdbms LC Labguide

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Rdbms LC Labguide

Uploaded by

Copyright:

Available Formats

RDBMS Exercises for Hands on

ER/CORP/CRS/DB07/004 Education & Research Department

You might also like