Database Management Systems (DBMS) Lab
Manual
Cover Page
DBMS Laboratory Manual
Course Code:
Course Title: Database Management Systems
Prepared By: __
Submitted To: _____
Department of Computer Science & Engineering
(Institution Name)
Academic Year: 20XX – 20XX
Index
Exp. Page
Experiment Title
No. No.
1 Defining schema for applications 3
2 Creating tables, Renaming tables, Data constraints, Data insertion into a table 5
Grouping data, aggregate functions, Oracle functions (mathematical, character
3 7
functions)
4 Sub-queries, Set operations, Joins 9
5 Applying Data Normalization, Procedures, Triggers and Cursors on databases 11
Assignment in Design and Implementation of Database systems (Hotel
6 14
Management Example)
7 Deployment of Forms, Reports, Query Processing Algorithms 16
8 Studying Large objects – CLOB, NCLOB, BLOB and BFILE 18
Experiment 1: Defining Schema for Applications
Aim:
To define a schema for an application (e.g., Hospital Management System).
1
Theory:
A schema defines the logical structure of a database, including tables, columns, data types, and
relationships.
Code (Oracle SQL):
CREATE SCHEMA AUTHORIZATION hospital_admin;
CREATE TABLE Patient (
patient_id INT PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
age INT,
gender VARCHAR2(10),
disease VARCHAR2(100)
);
CREATE TABLE Doctor (
doctor_id INT PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
specialization VARCHAR2(50)
);
CREATE TABLE Appointment (
appt_id INT PRIMARY KEY,
patient_id INT,
doctor_id INT,
appt_date DATE,
FOREIGN KEY(patient_id) REFERENCES Patient(patient_id),
FOREIGN KEY(doctor_id) REFERENCES Doctor(doctor_id)
);
Sample Output:
Schema created with 3 tables (Patient, Doctor, Appointment).
Experiment 2: Creating Tables, Renaming Tables, Data Constraints,
Data Insertion
Aim:
To create tables, rename tables, apply constraints, and insert records.
Code:
2
-- Create table
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
dept VARCHAR2(30),
salary NUMBER(10,2)
);
-- Rename table
ALTER TABLE Employee RENAME TO Staff;
-- Insert records
INSERT INTO Staff VALUES (101, 'Ravi Kumar', 'IT', 50000);
INSERT INTO Staff VALUES (102, 'Neha Sharma', 'HR', 40000);
Sample Output:
Table Employee renamed to Staff.
Two records inserted.
Experiment 3: Grouping Data, Aggregate Functions, Oracle
Functions
Aim:
To use GROUP BY, aggregate functions, and Oracle functions.
Code:
-- Grouping and aggregate functions
SELECT dept, AVG(salary) AS avg_salary, MAX(salary) AS max_salary
FROM Staff
GROUP BY dept;
-- Oracle functions
SELECT name, LENGTH(name) AS name_length, UPPER(dept) AS department
FROM Staff;
Sample Output:
Dept Avg_Salary Max_Salary
IT 50000 50000
HR 40000 40000
3
Experiment 4: Subqueries, Set Operations, Joins
Aim:
To demonstrate subqueries, set operations, and joins.
Code:
-- Subquery
SELECT name FROM Staff WHERE salary > (SELECT AVG(salary) FROM Staff);
-- Set operation (UNION)
SELECT name FROM Staff WHERE dept='IT'
UNION
SELECT name FROM Staff WHERE dept='HR';
-- Join
SELECT P.name, D.name AS doctor_name
FROM Patient P
JOIN Appointment A ON P.patient_id = A.patient_id
JOIN Doctor D ON A.doctor_id = D.doctor_id;
Sample Output:
Displays employees with salary above average, merged department names, and patient-doctor
appointments.
Experiment 5: Data Normalization, Procedures, Triggers, Cursors
Aim:
To apply normalization and implement procedures, triggers, and cursors.
Code:
-- Example procedure
CREATE OR REPLACE PROCEDURE get_salary(emp IN INT) AS
sal NUMBER;
BEGIN
SELECT salary INTO sal FROM Staff WHERE emp_id = emp;
DBMS_OUTPUT.PUT_LINE('Salary: ' || sal);
END;
-- Trigger
CREATE OR REPLACE TRIGGER salary_check
4
BEFORE INSERT OR UPDATE ON Staff
FOR EACH ROW
BEGIN
IF :NEW.salary < 10000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary too low!');
END IF;
END;
-- Cursor example
DECLARE
CURSOR c1 IS SELECT name, salary FROM Staff;
v_name Staff.name%TYPE;
v_sal Staff.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_name, v_sal;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name || ' - ' || v_sal);
END LOOP;
CLOSE c1;
END;
Sample Output:
Procedure returns salary, trigger prevents low salary insert, cursor prints all staff details.
Experiment 6: Database Design Assignment – Hotel Management
Aim:
To design and implement a Hotel Management database system.
Tables: - Hotel (hotel_id, name, location)
- Room (room_id, hotel_id, type, price)
- Guest (guest_id, name, contact)
- Booking (booking_id, guest_id, room_id, check_in, check_out)
Code:
CREATE TABLE Hotel (
hotel_id INT PRIMARY KEY,
name VARCHAR2(50),
location VARCHAR2(50)
);
5
CREATE TABLE Room (
room_id INT PRIMARY KEY,
hotel_id INT,
type VARCHAR2(20),
price NUMBER,
FOREIGN KEY(hotel_id) REFERENCES Hotel(hotel_id)
);
CREATE TABLE Guest (
guest_id INT PRIMARY KEY,
name VARCHAR2(50),
contact VARCHAR2(15)
);
CREATE TABLE Booking (
booking_id INT PRIMARY KEY,
guest_id INT,
room_id INT,
check_in DATE,
check_out DATE,
FOREIGN KEY(guest_id) REFERENCES Guest(guest_id),
FOREIGN KEY(room_id) REFERENCES Room(room_id)
);
Experiment 7: Deployment of Forms, Reports, Query Processing
Aim:
To create forms and reports for database applications.
Implementation (Oracle Forms/Reports): - Forms: Guest registration, Room booking form.
- Reports: Monthly hotel bookings, revenue report.
Query Processing Example:
SELECT hotel_id, COUNT(*) AS total_bookings
FROM Booking
GROUP BY hotel_id;
Sample Output:
Displays number of bookings per hotel.
6
Experiment 8: Large Objects – CLOB, NCLOB, BLOB, BFILE
Aim:
To study and use large object datatypes.
Code:
CREATE TABLE FileStore (
file_id INT PRIMARY KEY,
doc CLOB,
image BLOB,
ntext NCLOB,
file_path BFILE
);
Sample Output:
Table created for storing large text, images, and files.
End of Lab Manual
