LAB Manual DBMS

Rational behind DBMS Lab

Database management has evolved from a specialized computer application to a central
component of a modern computing environment and as a result, knowledge about database
system has become an essential part of Information Technology. The aim of this course is to
provide an introduction to database management system, with an emphasis on foundational
concepts.
The fundamental concepts and related algorithms covered here are based on those used in
existing commercial or experimental database systems.

Objectives
At the end of the course students should
1. Have a good understanding of how DBMS architecture, particularly those concerned with
creation and maintenance of tables and database.
2. Have a good understanding of the fundamental DBMS used in computer science.
3. Be able to understand various types of queries and their execution.
4. Be able to design new database and modify existing ones for new applications and reason
about the efficiency of the result.
5. Have a fair knowledge of PL/SQL.

Software and Hardware Requirements

Software Required:
1. VB, ORACLE and/or DB2
2. VB, MSACCESS
3. VB, MS SQL SERVER 2000
4. JAVA, MYSQL
5. JAVA, ORACLE

Hardware Required:
 Processor : Pentium IV
 RAM : 1 GB
 Hard Disk : 200 GB
ASSIGNMENT NO.1
Q1. Create the following tables:

i) CM (Client_master)

Columnname Datatype Size Attributes

CNO varchar2 6 Primary key / first letter must starts
with ’c’
Name varchar2 20
address1 varchar2 30
address2 varchar2 30
City varchar2 15
State varchar2 15
Pincode Number 6
bal_due Number 10,2

ii) PM (Product_master)

Columnname Datatype Size Attributes

PNO varchar2 6 Primary key / first letter must starts
with ’p’
Description varchar2 15 Not NULL
Profit_percent Number 4,2 Not NULL
Unit_measure varchar2 10 Not NULL
Qty_on_hand Number 8 Not NULL
Reoder_lvl Number 8 Not NULL
Sell_price Number 8,2 Not NULL, Cannot be 0
Cost_price Number 8,2 Not NULL, Cannot be 0

iii) SM (Salesman_master)

Columnname Datatype Size Attributes

SNO varchar2 6
S_name varchar2 20 Not null
Address1 varchar2 30 Not null
Address2 varchar2 30
City varchar2 20
Pincode Number 8
State varchar2 20
Sal_amt Number 8,2 Not null, cannot be 0
Tgt_to_get Number 6,2 Not null, cannot be 0
Ytd_sales Number 6,2 Not null
Remarks Varchar2 60
iv) SO (Sales_order)
Columnname Datatype Size Attributes
SONO varchar2 6 Primary key / first letter must be ‘o’
CNO Varchar2 6 Foreign key reference clien_no of
client_master table
S_order_date Date Not Null
Dely_add Varchar2 25
SNO Varchar2 6 Foreign key references salesman_no
of salesman_master table
Dely_type Char 1 Delivery: part(p)/full(f)
Billed_yn Char 1
Dely_date Date Can not be lessthan s_order_date
Order_status Varchar2 10 Values (‘in process’;’ fulfilled’; back
order’; ’canceled’)

v) SOD (Sales_order_details)

Discription: used to store client’s orders with details of each product ordered.
Columnname Datatype Size Attributes
SONO Varchar2 6 Foreign key references
s_order_no of sales_order table.
PNO Varchar2 6 Foreign key references
product_no of product_master table.
Qty_order Number 8
Qty_disp Number 8
Product_rate Number 10,2

Exercise on altering the table structure.

a. Add a column called_tel of datatype Number and size=10 to the client_master table.
b. Change the size of sell_prize column in product_master to 10, 2.

Exercise on deleting the table structure along with the data.

a. Destroy the table client_master along with its data.

Exercise on renaming the table.

a. Change the name of salesman_master to sman_mast.

Exercise on adding & removing of constraints on existing table.

a. In the salesman_master table make salesman_no. as primary key and its first letter should
be started with ’S’.
b. Add a constraint names as chk_cons to the table client_master that will check that the
name should not be null.
c. Drop the foreign key product_no. from sales_order_details.
 ASSIGNMENT NO. 2
Q1- Insert the following data into their respective tables:

i) client_master (CM)

CNO C_Name city Pincode state bal.due

C00001 Ivan Bayross Mumbai 400054 Maharashtra 15000
C00002 Mamta Madras 780001 Tamilnadu 0
Mazumdar
C00003 Chhaya Bankar Mumbai 400057 Maharashtra 5000
C00004 Ashwini Joshi Banglore 560001 Karnataka 0
C00005 Hansel Colaco Mumbai 400060 Maharashtra 2000
C00006 Deepak Sharma Mangalore 560050 Karnataka 0

ii) product_master (PM)

PNO Description Profit% Unit Qty Reorder Sell Cost price

Percent measured on hand price
P00001 T-Shirts 5 piece 200 50 350 250
P0345 Shirts 6 piece 150 50 500 350
P06734 Cotton Jeans 5 piece 100 20 600 450
P07865 Jeans 5 piece 100 20 750 500
P07868 Trousers 2 piece 150 50 850 550
P07885 Pull Overs 2.5 piece 80 30 700 450
P07965 Denim Shirts 4 piece 100 40 350 250
P07975 Lycra Tops 5 piece 70 30 300 175
P08865 Skirts 5 piece 75 30 450 300

iii) salesman_master (SM)

SNO S_name Addr Address City Pin State Salam Tgt_ Ytd Rem
ess1 1 code t to_get Sales
S00001 Aman A/14 worli Mumbai 400002 Mah 3000 100 50 Good
S00002 Omkar 65 Nariman Mumbai 400001 Mah 3000 200 100 Good
S00003 Raj P-7 Bandra Mumbai 400032 Mah 3000 200 100 Good
S00004 Ashish A/5 Juhu Mumbai 400044 Mah 3500 200 150 Good

On the basis of client_master & product_master tables answer the following Questionnaires:
a. Find out the names of all the clients.
b. Retrieve the entire contents of client_master table.
c. Retrieve the list of names, city and state of all the clients.
d. List the various products available from the product_master table.
e. List all the clients who are located in Mumbai.
f. List the names of salesmen who have a salary equal to Rs. 3000
Exercise on updating records in a table.
a. Change the city of client no. ‘C00005’ to ‘Banglore’.
b. Change the bal_due of client no. ‘C00001’ to Rs. 1000.
c. Change the cost price of Trousers to Rs. 950.00.
d. Change the city of salesmen to ‘Pune’.

iv) sales_order table (SO):

SONO CNO S_orderdate SNO Dely Bill Y/N Delay date Orderstatus
type
O19001 C00001 12-June-04 S00001 F N 20-July-02 In Process
O19002 C00002 25-June-04 S00002 P N 27-June-02 Cancelled
O46865 C00003 18-Feb-04 S00003 F Y 20-Feb-02 Fulfilled
O19003 C00001 03-Apr-04 S00001 F Y 07-Apr-02 Fulfilled
O46866 C00004 20-May-04 S00002 P N 22-May-02 Cancelled
O19008 C00005 24-May-04 S00004 F N 26-July-02 In Process

v) sales_order_details table (SOD):

SONO PNO Qty ordered Qty disp Product_rate

O19001 P00001 4 4 525
O19001 P07965 2 1 8400
O19001 P07885 2 1 5250
O19002 P00001 10 0 525
O46865 P07868 3 3 3150
O46865 P07885 3 1 5250
O46865 P00001 10 10 525
O46865 P0345 4 4 1050
O19003 P03453 2 2 1050
O19003 P06734 1 1 12000
O46866 P07965 1 0 8400
O46866 P07975 1 0 1050
O19008 P00001 10 5 525
O19008 P07975 5 3 1050

Exercise on deleting records in a table

a. Delete all salesmen from the salesman_master whose salaries are =3500.
b. Delete all products from product_master where qty_on_hand =100.
c. Delete from client_master where the column state holds the value ‘Tamilnadu’.
ASSIGNMENT NO.3
Answer the following Queries:
Using the tables created previously generate the SQL statements for the operations mentioned
below. The tables in use are as follows:
a. Client_master (CM)
b. Product_master (PM)
c. Salesman_master (SM)
d. Sales_order (SO)
e. Sales_order_details (SOD)

Perform the following computation on table data.

a. List the names of all clients having ‘a’ as second letter in their names.
b. List the clients who stay in the city whose first letter is ‘M’.
c. List all clients who stay in ‘Bangalore’ or ‘Mangalore’.
d. List all clients whose bal_due is greater than value 10000.
e. List all information from sales_order for orders placed in the month of June.
f. List the order information for the client no. ‘C00001’ and ‘C00002’.
g. List products whose selling price is greater than 500 and less than or equal to 750.
h. List products whose selling price is more than 500. Calculate a new selling price as, original
selling price *.15. Rename the new column in the output of the above query as new_price.
i. List the names, city and state of clients who are NOT in the state of ‘Maharashtra’.
j. Count the total no. of orders.
k. Calculate the average price of all products.
l. Determine the maximum and minimum product prices. Rename the output as max_price
and min_price respectively.
m. Count the number of products having price less than or equal to 500.
n. List all the products whose qnty_on_hand is less than reorder_lvl.
ASSIGNMENT NO.4
Exercise on sub-queries.
a. Find the product_no and description of non- moving products i.e. products not being sold.
b. Find the customer name, address1, address2, city and pincode for the client who has
placed order no “019001”.
c. Find the client names that have placed order before the month of May’02.
d. List if the product ‘Lycra Top’ has been ordered by any client and print the client_no.,
name to whom it was sold.
e. List the names of clients who have placed orders worth Rs. 10000 or more.

Exercise on Having and Group By Clauses:

a. Print the description and total quantity sold for each product.
b. Find the value of each product sold.
c. Calculate the average quantity sold for each client that has a maximum order value of
15000.00.
d. Find out the total of all the billed orders for the month of June.

ASSIGNMENT NO.5
Exercise on Joins and Correlation:
a. Find out the products which have been sold to ‘Ivan Bayross’.
b. Find out the products and their quantities that have to be delivered in the current month.
c. List the product no. and the description of constantly sold (i.e. rapidly moving) products.
d. Find the names of clients who have purchased ‘Trousers’.
e. List the products and orders from customers who have ordered less than 5 units of ‘Pull
Overs’.
f. Find the product and their quantities for the orders placed by ‘Ivan Bayross’ and ‘Mamta
Mazumdar’.
g. Find the product and their quantities for the orders placed by client no. ‘C00001’ and
‘C00002’.

ASSIGNMENT NO. 6
Write appropriate SQL statements for the following:
a. Create a simple index idx_prod on product cost price from the product_master table.
b. Create a sequence inv_seq with the following parameters, increment by 3, cycle, cache 4
and which will generate the numbers from 1 to 9999 in ascending order.
c. Create a view on order no., orderdate, orderstatus of sales_order table and productno.,
productrate and qtyordered of sales_order_details.
ASSINGNMENT NO. 7
Exercise on DCL statements:
a. Give the user Ivan permission only to view records in the tables sales_order and
sales_order_details along with option to further grant permission on these tables to other
users.
b. Give the user Ivan all data manipulation privileges on the table client_master without an
option to further grant permission on client_master table to other users.
c. Take back all privileges given to the user Ivan on the table client_master.
Exercise on TCL statements:
a. A factory maintains records of stocks_on_hand and material requirements in item_master
table and item_requisite table respectively.
Table Name: item_master
Column Name Data Type Size Default Attributes
item_id Number 4 Primary key, identify number for the item
Description Varchar2 20 Item description
Bal_stock Number 3 Balance stock for an item

Data for item_master table:

Item_id Description Bal_stock
102 Aluminium sheets 120
105 Steel sheets 96
151 A14 Iron rods 50
209 A14 Hexagonal nut 334
254 A14 steel bolts 250
303 Tar Washers 500

Table Name: item_requisite

Column Name Data type Size Default Attributes
item_id Number 4 Identity number for the item requested
dept_code Number 2 Code for department placing the requisite
Quantity Number 3 Quantity of items requested

Data for item_requisite table:

Item_id dept_code Bal_stock Exercise:
02 10 50 a. Update the bal_stock in the item_master table for
102 20 50 each requiosite in the item_requisite table. Based on the
105 10 50 item_id, decrease the bal_stock in the item_master table
105 20 25
by the quantity in the item_requisite table. The update
151 10 30
operation is completed only when the corresponding
151 20 30
209 10 300 record in the item_requisite table is deleted.
303 20 400
ASSINGNMENT NO. 8
On the basis of table created in previous exercise. Write a PL/SQL block of code based on
conditions:

a. If at any given point, the value in bal_stock becomes negative, the entire update operation
for all item_id should rollback.
b. If at any given point, the value in bal_stock becomes negative, the update operation for
that particular item_id should rollback.
c. Create user defined Exceptions. Write a program, if the requested qty attempted is more
than the current balance qty held for a item, a user defined exception is raised displaying
an appropriate error message otherwise perform the appropriate task.

ASSINGNMENT NO. 9
Exercise on PL/SQL procedures and functions.

a. Create a procedure that accepts a client_no. and checks if the client_no. exists in the table
client_master. If the client_no. exists, display a message “Valid Client” and if the client_no.
does not exist than display an appropriate error message.
b. Drop the above created procedure.
c. Create a function getMaxClientNo that returns MAX value for client_no. in the table
client_master. Using this function write an insert statement to add a new record in
client_master table.
d. Create a package named mypackage that will hold the function and procedure created
above. Write down the package specification and package body for the package
mypackage.
ASSINGNMENT NO. 10
Exercise on Triggers:

a. Write and update trigger on client_master table. The system should keep track of the
records that are being updated. The old values of the updated record should be added in
the audit_trail table.
b. Write a trigger on delete of client_master table to keep the details of deleted client in
old_client_master table.
c. Write a trigger on update of client_master table and check if client_no is changing then it
should stop operation and give error message.
d. Write a trigger that limits the DML actions to the client_master table to weekdays from
8.30am to 6.30pm. If a user tries to insert/update/delete a row in the client_master table,
a warning message will be prompted.
e. Disable the trigger created in above exercise.
f. Delete the trigger created in above exercise.

Table name: audit_trail

Column name Datatype Size Default Attributes

Client_no. Varchar2 6
Name Varchar2 20
Bal_due Number 10,2
Operation Varchar2 8
Userid Varchar2 20
Odate Date
Exercise on ER Diagram
Following are the requirements of the Company for building a ERP.

 The company is organized into DEPARTMENTs. Each department has a name, number and an
employee who manages the department. We keep track of the start date of the department
manager.
 Each department controls a number of PROJECTs. Each project has a name, number and is
located at a single location.
 We store each EMPLOYEE’s social security number, address, salary, sex, and birthdate. Each
employee works for one department but may work on several projects. We keep track of the
number of hours per week that an employee currently works on each project. We also keep
track of the direct supervisor of each employee.
 Each employee may have a number of DEPENDENTs. For each dependent, we keep track of
their name, sex, birthdate, and relationship to employee.
 More than one relationship type can exist with the same participating entity types. For
example, MANAGES and WORKS_FOR are distinct relationships between EMPLOYEE and
DEPARTMENT, but with different meanings and different relationship instances.

Design the ER diagram for this problem statement and convert into relational model.
Mini Project
Mini project on designing and implementing one data base management system.

Contents of PROJECT REPORT

 Project Title
 Certificate
 Acknowledgement
 System Overview
 Current system
 Objectives of the proposed system
 Advantages of the Proposed system (over current)
 E.R. Diagram
 Entities
 Relationships
 Mapping Constraints
 Database Schema/Table Definition
 Table Name
 Field Name
 Data type
 Field size
 Constraint (e.g. auto generated, primary key, foreign key)
 Validation (e.g. not null, default value)
 Implementation using Java or .NET
 Output
 Future Enhancements of the system
 Bibliography

Note: From the 3rd lab reporting will be started for project.