DBMS Ext
DBMS Set 1
DBMS Set 1
a) In the process of computerization of roadways travels , draw an ER diagram to
design a database which consists database for buses, passengers, tickets,
reservations and cancellation details.
b) create a table bus with the following attributes (bus no, source, destination,
weekday);
c) insert 5 rows and rename bus to buses
d) display source which starts with ‘M’
e)select all bus numbers which run in between Monday to wednesday
f) write about cursors and explain it with a syntax for creating it.
a) ER Diagram for Roadways Travels Database
Entities and Attributes:
Bus: Bus_No , Source , Destination , Weekday
Passenger: Passenger_ID , Name , Age , Gender
Ticket: Ticket_No , Ticket_Price , Age , Gender , Source , Arrival_Time ,
Dept_Time
Reservation: Reservation_ID , Date , Time
Cancellation: Cancellation_ID , Date , Time
Relationships:
A Bus has multiple Tickets.
A Passenger can have multiple Tickets.
A Ticket is associated with one Reservation and can optionally have a
Cancellation.
ER Diagram:
b) Create Table bus
CREATE TABLE bus (
bus_no INT PRIMARY KEY,
source VARCHAR(100),
destination VARCHAR(100),
weekday VARCHAR(20)
);
c) Insert 5 Rows and Rename Table to buses
Insert 5 Rows:
INSERT INTO bus (bus_no, source, destination, weekday) VALUES
(1, 'Mumbai', 'Pune', 'Monday'),
(2, 'Mumbai', 'Goa', 'Tuesday'),
(3, 'Delhi', 'Agra', 'Wednesday'),
(4, 'Chennai', 'Bangalore', 'Thursday'),
(5, 'Mumbai', 'Delhi', 'Friday');
Rename Table:
ALTER TABLE bus RENAME TO buses;
d) Display Source which Starts with ‘M’
SELECT source FROM buses WHERE source LIKE 'M%';
e) Select All Bus Numbers Which Run Between Monday to
Wednesday
SELECT bus_no FROM buses WHERE weekday IN ('Monday', 'Tuesday',
'Wednesday');
f) Cursors in SQL
Cursors are database objects used to retrieve, manipulate, and navigate through a
result set row by row. They are particularly useful when you need to process each row
individually.
Types of Cursors:
1. Implicit Cursor: Automatically created by SQL when a single row is fetched.
2. Explicit Cursor: Defined and controlled by the user for processing multiple rows.
Syntax for Creating an Explicit Cursor:
1. Declare Cursor: Define the cursor and its SQL query.
2. Open Cursor: Allocate resources for the cursor and execute the query.
3. Fetch Cursor: Retrieve rows from the cursor one at a time.
4. Close Cursor: Release resources associated with the cursor.
Example:
DECLARE
CURSOR bus_cursor IS
SELECT bus_no, source, destination FROM buses WHERE weekday IN
('Monday', 'Tuesday', 'Wednesday');
bus_record buses%ROWTYPE; -- Variable to hold each row fetched from
the cursor
BEGIN
OPEN bus_cursor; -- Open the cursor
LOOP
FETCH bus_cursor INTO bus_record; -- Fetch each row into
bus_record
EXIT WHEN bus_cursor%NOTFOUND; -- Exit loop if no more rows are
found
-- Process each row (example: display bus_no)
DBMS_OUTPUT.PUT_LINE('Bus No: ' || bus_record.bus_no || ',
Source: ' || bus_record.source || ', Destination: ' ||
bus_record.destination);
END LOOP;
CLOSE bus_cursor; -- Close the cursor
END;
Explanation:
DECLARE: Declares the cursor bus_cursor with a SQL query to select bus details
for specific weekdays.
bus_record: A variable of type buses%ROWTYPE to hold each fetched row.
BEGIN...END: Block to execute cursor operations.
OPEN bus_cursor: Opens the cursor and executes the query.
LOOP...FETCH...EXIT: Loop to fetch rows from the cursor into bus_record and
process them. The loop exits when no more rows are found.
CLOSE bus_cursor: Closes the cursor to release resources.
DBMS Set 2
DBMS Set 2
a) In the process of computerization in colleges , draw an ER diagram to design a
database which consists database for student, staff, departments, and
administration details.
b) create table emp_salary with the following attributes (empcode, dob,dept,
salary)
c) insert 5 rows and rename empsalary to emp_info;
d) find the sum of salaries, least and highest salaries that an employee draws.
e) List all the salaries in ascending order.
f)create view and write the syntax of view
a) ER Diagram for College Database
The ER diagram for a college database would include entities such as Student, Staff,
Department, and Administration, along with their relationships.
Entities and Attributes:
Student: Student_ID , Name , DOB , Dept_ID
Staff: Staff_ID , Name , DOB , Dept_ID
Department: Dept_ID , Dept_Name , Head_ID (Staff_ID as foreign key)
Administration: Admin_ID , Name , Role
Relationships:
A Student belongs to a Department.
A Staff works in a Department.
Administration manages Departments and other entities.
ER Diagram:
b) Create Table emp_salary
CREATE TABLE emp_salary (
empcode INT PRIMARY KEY,
dob DATE,
dept VARCHAR(50),
salary DECIMAL(10, 2)
);
c) Insert Rows and Rename Table to emp_info
Insert 5 Rows:
INSERT INTO emp_salary (empcode, dob, dept, salary) VALUES
(1, '1985-01-23', 'HR', 50000),
(2, '1990-07-15', 'Finance', 60000),
(3, '1988-03-10', 'IT', 55000),
(4, '1986-11-05', 'Marketing', 48000),
(5, '1992-02-28', 'HR', 52000);
Rename Table:
ALTER TABLE emp_salary RENAME TO emp_info;
d) Find the Sum of Salaries, Least, and Highest Salaries
Sum of Salaries:
SELECT SUM(salary) AS Total_Salaries FROM emp_info;
Least Salary:
SELECT MIN(salary) AS Least_Salary FROM emp_info;
Highest Salary:
SELECT MAX(salary) AS Highest_Salary FROM emp_info;
e) List All Salaries in Ascending Order
SELECT salary FROM emp_info ORDER BY salary ASC;
f) Create View and Syntax
Create View:
CREATE VIEW emp_summary AS
SELECT empcode, dept, salary
FROM emp_info;
Syntax Explanation:
CREATE VIEW is used to create a view.
emp_summary is the name of the view.
The SELECT statement defines the query for the view, selecting empcode , dept ,
and salary from the emp_info table.
DBMS Set 3
DBMS Set 3
a) In the process of computerization of roadways travels , draw an ER diagram to
design a database which consists database for buses, passengers, tickets,
reservations and cancellation details.
b) Create table passenger with the following attributes(pnr_no, ticket_no,name,
age, gender, category like AC or non AC)
c) insert 5 rows and rename passenger to passenger_info
d) display unique pnr numbers of all passengers.
e) list all the passengers whose age is greater than equal to 15years
f) Explain normalization and different forms of it
a) ER Diagram for Roadways Travels Database
The ER diagram for a roadways travel database would include entities such as Buses,
Passengers, Tickets, Reservations, and Cancellations, along with their relationships.
Entities and Attributes:
Bus: Bus_ID , Bus_Number , Type (AC or Non-AC), Capacity
Passenger: Passenger_ID , Name , Age , Gender
Ticket: Ticket_No , Bus_ID , Passenger_ID , Reservation_ID , Cancellation_ID
Reservation: Reservation_ID , Date , Time
Cancellation: Cancellation_ID , Date , Time
Relationships:
A Bus has multiple Tickets.
A Passenger can have multiple Tickets.
A Ticket is associated with one Reservation and can optionally have a
Cancellation.
ER Diagram:
b) Create Table passenger
CREATE TABLE passenger (
pnr_no INT PRIMARY KEY,
ticket_no INT,
name VARCHAR(100),
age INT,
gender CHAR(1),
category VARCHAR(10)
);
c) Insert 5 Rows and Rename Table to passenger_info
Insert 5 Rows:
INSERT INTO passenger (pnr_no, ticket_no, name, age, gender, category)
VALUES
(1, 1001, 'John Doe', 25, 'M', 'AC'),
(2, 1002, 'Jane Smith', 30, 'F', 'Non-AC'),
(3, 1003, 'Sam Brown', 17, 'M', 'AC'),
(4, 1004, 'Nancy White', 45, 'F', 'Non-AC'),
(5, 1005, 'Mike Green', 12, 'M', 'AC');
Rename Table:
ALTER TABLE passenger RENAME TO passenger_info;
d) Display Unique PNR Numbers of All Passengers
SELECT DISTINCT pnr_no FROM passenger_info;
e) List All Passengers Whose Age is Greater Than or Equal
to 15 Years
SELECT * FROM passenger_info WHERE age >= 15;
f) Explain Normalization and Different Forms of It
Normalization is the process of organizing data in a database to reduce redundancy
and improve data integrity. The main goals are to:
1. Eliminate redundant data.
2. Ensure data dependencies make sense.
Forms of Normalization:
1. First Normal Form (1NF):
Ensure that the table has a primary key.
Eliminate repeating groups (each column contains atomic values, no multiple
values in a single column).
Example:
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
subjects VARCHAR(100) -- Violates 1NF if it stores multiple
subjects like "Math, Science"
);
1NF Example:
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE student_subjects (
student_id INT,
subject VARCHAR(100),
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
2. Second Normal Form (2NF):
Ensure that the table is in 1NF.
Remove partial dependencies (all non-key attributes should depend on the
whole primary key).
Example:
CREATE TABLE orders (
order_id INT,
product_id INT,
product_name VARCHAR(100), -- Partial dependency on product_id
quantity INT,
PRIMARY KEY (order_id, product_id)
);
2NF Example:
CREATE TABLE orders (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100)
);
3. Third Normal Form (3NF):
Ensure that the table is in 2NF.
Remove transitive dependencies (non-key attributes should depend only on
the primary key).
Example:
CREATE TABLE employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
dept_id INT,
dept_name VARCHAR(100) -- Transitive dependency on dept_id
);
3NF Example:
CREATE TABLE employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
dept_id INT
);
CREATE TABLE department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100)
);
DBMS Set 4
DBMS Set 4
a) In the process of computerization in colleges , draw an ER diagram to design a
database which consists database for student, staff, departments, and
administration details.
b) create the following tables: Student (roll_no, marks, category, district, state)
and Student_rank(roll_no, marks, rank)
c)insert 5 rows into each table
d) list all those students who have come from ‘Telangana’ state and secured a
rank above 100
e)select all the fields from students where state is ‘AP’ or ‘UP’.
f) write a procedure and show the syntax to create it
a) ER Diagram for College Database
Entities and Attributes:
Student: Student_ID , Name , DOB , Dept_ID
Staff: Staff_ID , Name , DOB , Dept_ID
Department: Dept_ID , Dept_Name , Head_ID (Staff_ID as foreign key)
Administration: Admin_ID , Name , Role
Relationships:
A Department has multiple Students.
A Department has multiple Staff members.
A Staff member can be the head of a Department.
An Administration oversees multiple Departments.
ER Diagram:
b) Create Tables
CREATE TABLE Student (
roll_no INT PRIMARY KEY,
marks INT,
category VARCHAR(50),
district VARCHAR(50),
state VARCHAR(50)
);
CREATE TABLE Student_rank (
roll_no INT PRIMARY KEY,
marks INT,
rank INT,
FOREIGN KEY (roll_no) REFERENCES Student(roll_no)
);
c) Insert 5 Rows into Each Table
INSERT INTO Student (roll_no, marks, category, district, state) VALUES
(1, 85, 'General', 'Hyderabad', 'Telangana'),
(2, 92, 'OBC', 'Warangal', 'Telangana'),
(3, 78, 'SC', 'Vijayawada', 'AP'),
(4, 88, 'General', 'Lucknow', 'UP'),
(5, 80, 'ST', 'Guntur', 'AP');
INSERT INTO Student_rank (roll_no, marks, rank) VALUES
(1, 85, 120),
(2, 92, 50),
(3, 78, 200),
(4, 88, 90),
(5, 80, 150);
d) List All Those Students Who Have Come from
‘Telangana’ State and Secured a Rank Above 100
SELECT s.roll_no, [Link], [Link], [Link], [Link], [Link]
FROM Student s
JOIN Student_rank sr ON s.roll_no = sr.roll_no
WHERE [Link] = 'Telangana' AND [Link] > 100;
e) Select All the Fields from Students Where State is ‘AP’ or
‘UP’
SELECT * FROM Student
WHERE state IN ('AP', 'UP');
f) Write a Procedure and Show the Syntax to Create It
Procedure Example:
Let's create a procedure to update the marks of a student based on their roll number.
Procedure Syntax:
DELIMITER //
CREATE PROCEDURE UpdateStudentMarks (
IN student_roll_no INT,
IN new_marks INT
)
BEGIN
UPDATE Student
SET marks = new_marks
WHERE roll_no = student_roll_no;
END //
DELIMITER ;
Explanation:
DELIMITER // : Changes the delimiter to allow the procedure definition to contain
semicolons.
CREATE PROCEDURE UpdateStudentMarks : Defines a new procedure named
UpdateStudentMarks .
IN student_roll_no INT, IN new_marks INT : Defines two input parameters,
student_roll_no and new_marks .
BEGIN ... END : Block containing the SQL statements to execute.
UPDATE Student SET marks = new_marks WHERE roll_no = student_roll_no; :
Updates the marks column in the Student table for the given roll_no .
DELIMITER ; : Resets the delimiter back to the default semicolon.
To call the procedure:
CALL UpdateStudentMarks(1, 95);
DBMS Set 5
DBMS Set 5
a) In the process of computerization of roadways travels , draw an ER diagram to
design a database which consists database for buses, passengers, tickets,
reservations and cancellation details.
b) Create table ticket with the following attributes(ticket_no, ticket_price
,age,gender,source, arrival_time, dept_time)
c) insert 5 rows and rename ticket to ticket_info
d) display all the names of female passengers.
e) list all the tickets that are priced higher than the average
f) write and create a syntax for triggers with an example.
a) ER Diagram for Roadways Travels Database
Entities and Attributes:
Bus: Bus_ID , Bus_Number , Type (AC or Non-AC), Capacity
Passenger: Passenger_ID , Name , Age , Gender
Ticket: Ticket_No , Ticket_Price , Age , Gender , Source , Arrival_Time ,
Dept_Time
Reservation: Reservation_ID , Date , Time
Cancellation: Cancellation_ID , Date , Time
Relationships:
A Bus has multiple Tickets.
A Passenger can have multiple Tickets.
A Ticket is associated with one Reservation and can optionally have a
Cancellation.
ER Diagram:
b) Create Table ticket
CREATE TABLE ticket (
ticket_no INT PRIMARY KEY,
ticket_price DECIMAL(10, 2),
age INT,
gender CHAR(1),
source VARCHAR(100),
arrival_time TIME,
dept_time TIME
);
c) Insert 5 Rows and Rename Table to ticket_info
Insert 5 Rows:
INSERT INTO ticket (ticket_no, ticket_price, age, gender, source,
arrival_time, dept_time) VALUES
(1, 150.00, 25, 'M', 'CityA', '10:00', '12:00'),
(2, 200.00, 30, 'F', 'CityB', '11:00', '13:00'),
(3, 175.00, 20, 'F', 'CityC', '12:00', '14:00'),
(4, 180.00, 45, 'M', 'CityD', '13:00', '15:00'),
(5, 160.00, 35, 'F', 'CityE', '14:00', '16:00');
Rename Table:
ALTER TABLE ticket RENAME TO ticket_info;
d) Display All the Names of Female Passengers
Create and Populate Passenger Table:
First, create the passenger table and populate it to relate passengers to tickets.
CREATE TABLE passenger (
passenger_id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
gender CHAR(1),
ticket_no INT,
FOREIGN KEY (ticket_no) REFERENCES ticket_info(ticket_no)
);
INSERT INTO passenger (passenger_id, name, age, gender, ticket_no)
VALUES
(1, 'Alice', 30, 'F', 2),
(2, 'Bella', 20, 'F', 3),
(3, 'Charlie', 45, 'M', 4),
(4, 'Diana', 35, 'F', 5),
(5, 'Edward', 25, 'M', 1);
Display Names:
SELECT name FROM passenger WHERE gender = 'F';
e) List All the Tickets That Are Priced Higher Than the
Average
Calculate Average Price and List Higher Priced Tickets:
SELECT * FROM ticket_info
WHERE ticket_price > (SELECT AVG(ticket_price) FROM ticket_info);
f) Create and Explain Syntax for Triggers
Syntax for Creating Triggers:
Triggers are used to automatically perform actions based on certain events in the
database. Here's a basic example of creating an AFTER INSERT trigger.
Example Trigger:
Let's create a trigger that logs any new ticket inserted into a log table called
ticket_log .
Create Log Table:
CREATE TABLE ticket_log (
log_id INT AUTO_INCREMENT PRIMARY KEY,
ticket_no INT,
action VARCHAR(50),
log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Create Trigger:
DELIMITER //
CREATE TRIGGER after_ticket_insert
AFTER INSERT ON ticket_info
FOR EACH ROW
BEGIN
INSERT INTO ticket_log (ticket_no, action) VALUES (NEW.ticket_no,
'INSERTED');
END;
//
DELIMITER ;
Explanation:
DELIMITER // : Changes the delimiter so we can define the trigger without
interfering with semicolons in the trigger body.
CREATE TRIGGER after_ticket_insert AFTER INSERT ON ticket_info FOR EACH
ROW : Defines a trigger named after_ticket_insert that activates after an insert
operation on the ticket_info table.
BEGIN ... END; : Defines the trigger body. In this case, it inserts a log entry into the
ticket_log table with the ticket_no and action 'INSERTED'.
DELIMITER ; : Resets the delimiter back to the default semicolon.
DBMS Set 1
a) In the process of computerization of roadways travels , draw an ER diagram to
design a database which consists database for buses, passengers, tickets,
reservations and cancellation details.
b) create a table bus with the following attributes (bus no, source, destination,
weekday);
c) insert 5 rows and rename bus to buses
d) display source which starts with ‘M’
e)select all bus numbers which run in between Monday to wednesday
f) write about cursors and explain it with a syntax for creating it.
a) ER Diagram for Roadways Travels Database
Entities and Attributes:
Bus: Bus_No , Source , Destination , Weekday
Passenger: Passenger_ID , Name , Age , Gender
Ticket: Ticket_No , Ticket_Price , Age , Gender , Source , Arrival_Time ,
Dept_Time
Reservation: Reservation_ID , Date , Time
Cancellation: Cancellation_ID , Date , Time
Relationships:
A Bus has multiple Tickets.
A Passenger can have multiple Tickets.
A Ticket is associated with one Reservation and can optionally have a
Cancellation.
ER Diagram:
b) Create Table bus
CREATE TABLE bus (
bus_no INT PRIMARY KEY,
source VARCHAR(100),
destination VARCHAR(100),
weekday VARCHAR(20)
);
c) Insert 5 Rows and Rename Table to buses
Insert 5 Rows:
INSERT INTO bus (bus_no, source, destination, weekday) VALUES
(1, 'Mumbai', 'Pune', 'Monday'),
(2, 'Mumbai', 'Goa', 'Tuesday'),
(3, 'Delhi', 'Agra', 'Wednesday'),
(4, 'Chennai', 'Bangalore', 'Thursday'),
(5, 'Mumbai', 'Delhi', 'Friday');
Rename Table:
ALTER TABLE bus RENAME TO buses;
d) Display Source which Starts with ‘M’
SELECT source FROM buses WHERE source LIKE 'M%';
e) Select All Bus Numbers Which Run Between Monday to
Wednesday
SELECT bus_no FROM buses WHERE weekday IN ('Monday', 'Tuesday',
'Wednesday');
f) Cursors in SQL
Cursors are database objects used to retrieve, manipulate, and navigate through a
result set row by row. They are particularly useful when you need to process each row
individually.
Types of Cursors:
1. Implicit Cursor: Automatically created by SQL when a single row is fetched.
2. Explicit Cursor: Defined and controlled by the user for processing multiple rows.
Syntax for Creating an Explicit Cursor:
1. Declare Cursor: Define the cursor and its SQL query.
2. Open Cursor: Allocate resources for the cursor and execute the query.
3. Fetch Cursor: Retrieve rows from the cursor one at a time.
4. Close Cursor: Release resources associated with the cursor.
Example:
DECLARE
CURSOR bus_cursor IS
SELECT bus_no, source, destination FROM buses WHERE weekday IN
('Monday', 'Tuesday', 'Wednesday');
bus_record buses%ROWTYPE; -- Variable to hold each row fetched from
the cursor
BEGIN
OPEN bus_cursor; -- Open the cursor
LOOP
FETCH bus_cursor INTO bus_record; -- Fetch each row into
bus_record
EXIT WHEN bus_cursor%NOTFOUND; -- Exit loop if no more rows are
found
-- Process each row (example: display bus_no)
DBMS_OUTPUT.PUT_LINE('Bus No: ' || bus_record.bus_no || ',
Source: ' || bus_record.source || ', Destination: ' ||
bus_record.destination);
END LOOP;
CLOSE bus_cursor; -- Close the cursor
END;
Explanation:
DECLARE: Declares the cursor bus_cursor with a SQL query to select bus details
for specific weekdays.
bus_record: A variable of type buses%ROWTYPE to hold each fetched row.
BEGIN...END: Block to execute cursor operations.
OPEN bus_cursor: Opens the cursor and executes the query.
LOOP...FETCH...EXIT: Loop to fetch rows from the cursor into bus_record and
process them. The loop exits when no more rows are found.
CLOSE bus_cursor: Closes the cursor to release resources.