CYCLE 3

EXPERIMENT NO:9 DATE:09/10/24

FAMILIARIZATION OF STORED PROCEDURE, FUNCTION,
CURSOR AND TRIGGERS

Stored Procedure
• A stored procedure is a set of SQL commands that can be executed repeatedly with a
single call. It is stored in the database and can be executed by calling its name.
• Syntax: CREATE PROCEDURE procedure_name (parameters) AS BEGIN SQL
commands END;
• Example: CREATE PROCEDURE get_employee_details (emp_id INT) AS BEGIN
SELECT * FROM employees WHERE emp_id = ?; END;
Function
• A function is a self-contained block of code that performs a specific task. It returns a
value and can be used in a SELECT statement.
• Syntax: CREATE FUNCTION function_name (parameters) RETURNS data_type AS
BEGIN SQL commands RETURN result; END;
• Example: CREATE FUNCTION get_employee_name (emp_id INT) RETURNS
VARCHAR(255) AS BEGIN SELECT name FROM employees WHERE emp_id = ?;
END;
Cursor
• A cursor is a temporary result set that allows you to process data row by row. It is used
to retrieve and manipulate data in a loop.
• Syntax: DECLARE cursor_name CURSOR FOR SELECT statement;
• Example: DECLARE emp_cursor CURSOR FOR SELECT * FROM employees
WHERE department = 'Sales';
Trigger
• A trigger is a set of SQL commands that are executed automatically when a specific
event occurs (e.g., insert, update, delete). It is used to maintain data integrity and
perform actions automatically.
• Syntax: CREATE TRIGGER trigger_name BEFORE/AFTER
INSERT/UPDATE/DELETE ON table_name FOR EACH ROW BEGIN SQL
commands END;
• Example: CREATE TRIGGER update_employee_salary BEFORE UPDATE ON
employees FOR EACH ROW BEGIN IF NEW.salary > 100000 THEN SET
NEW.salary = 100000; END;

89
90
RESULT:
Familiarized with Stored Procedure, Function, Cursor and Triggers

91
92
EXPERIMENT NO:10 DATE:09/10/24
LIBRARY MANAGEMENT - STORED PROCEDURE I
AIM:
To familiarize with stored procedure.

DESCRIPITION:
A procedure (often called a stored procedure) is a collection of precompiled SQL statements
stored inside the database. It is a subroutine or a subprogram in the regular computing language.
A procedure always contains a name, parameter list, and SQL statements.

Syntax:
Delimiter //
create procedure procedure_name
[ [ IN / OUT / INOUT ] parameter_name datatype ]
Begin
Declaration_section
Executable_section
End //
Delimiter;

To call the procedure:

call procedure_name(parameter(s));

QUIRES:

1.Create the following tables,

BOOK (Book_id, Title, Language_id, MRP, Publisher_id, Published_date, volume,
status) //Language_id, Publisher-id are FK (foreign key).
AUTHOR (Author_id, Name, Email, Phone number, Status)

BOOK AUTHOR (Book_id, Author_id) / many to-many relationship, both columns are
PKFK (Primary key and Foreign key).
PUBLISHER (Publisher_id, Name, Address)

93
94
MEMBER (Member_id, Name, Branch_code, Roll_Number, Phone_number, Email_id,
Date_of_ join, Status)

BOOK_ISSUE (Issue_id, Date_of_issue, Book_id, Member_id,

Expected_date_of_return, Status) //Book_id & Member_id are FKs.

BOOK RETURN (Issue_id, Actual_date_of_return, late_days, late_fee) // Issue_id is PK

and FK.

LANGUAGE (Language_id, Name) // Static table for storing permanent data.

LATE_FEF_RULE (From_days, To_days, Amount) / Composite key.

CREATE TABLE LANGUAGE (

Language_id INT PRIMARY KEY,

Name VARCHAR(50) NOT NULL

);
CREATE TABLE PUBLISHER (
Publisher_id INT PRIMARY KEY,

Name VARCHAR(100) NOT NULL,

Address VARCHAR(255)

);
CREATE TABLE BOOK (

Book_id INT PRIMARY KEY,

Title VARCHAR(255) NOT NULL,
Language_id INT,

MRP DECIMAL(10, 2),

Publisher_id INT,
Published_date DATE,
Volume INT,

Status VARCHAR(20),
FOREIGN KEY (Language_id) REFERENCES LANGUAGE(Language_id),

FOREIGN KEY (Publisher_id) REFERENCES PUBLISHER(Publisher_id)

);

CREATE TABLE AUTHOR (

95
96
 Author_id INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,

Email VARCHAR(100),
Phone_number VARCHAR(15),

Status VARCHAR(20)
);

CREATE TABLE BOOK_AUTHOR (

Book_id INT,

Author_id INT,
PRIMARY KEY (Book_id, Author_id),

FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id),

FOREIGN KEY (Author_id) REFERENCES AUTHOR(Author_id)

);
CREATE TABLE MEMBER (

Member_id INT PRIMARY KEY,

Name VARCHAR(100) NOT NULL,

Branch_code VARCHAR(20),
Roll_Number VARCHAR(20),

Phone_number VARCHAR(15),
Email_id VARCHAR(100),
Date_of_join DATE,

Status VARCHAR(20)
);

CREATE TABLE BOOK_ISSUE (

Issue_id INT PRIMARY KEY,

Date_of_issue DATE,
Book_id INT,

Member_id INT,
Expected_date_of_return DATE,

97
98
 Status VARCHAR(20),
FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id),

FOREIGN KEY (Member_id) REFERENCES MEMBER(Member_id)

);

CREATE TABLE BOOK_RETURN (

Issue_id INT PRIMARY KEY,

Actual_date_of_return DATE,
Late_days INT,

Late_fee DECIMAL(10, 2),

FOREIGN KEY (Issue_id) REFERENCES BOOK_ISSUE(Issue_id)

);
CREATE TABLE LATE_FEE_RULE (

From_days INT,
To_days INT,

Amount DECIMAL(10, 2),

PRIMARY KEY (From_days, To_days)

);
Output
Query OK, 0 rows affected

2.Enter about 5 values to all the table.

INSERT INTO LANGUAGE (Language_id, Name) VALUES
(1, 'English'),
(2, 'Spanish'),
(3, 'French'),
(4, 'German'),
(5, 'Japanese');

INSERT INTO PUBLISHER (Publisher_id, Name, Address) VALUES

99
100
(1, 'Penguin Books', '375 Hudson Street, New York, NY 10014'),
(2, 'HarperCollins', '195 Broadway, New York, NY 10007'),
(3, 'Macmillan Publishers', '120 Broadway, New York, NY 10271'),
(4, 'Oxford University Press', 'Great Clarendon St, Oxford OX2 6DP, UK'),
(5, 'Scholastic Corporation', '557 Broadway, New York, NY 10012');

INSERT INTO BOOK (Book_id, Title, Language_id, MRP, Publisher_id, Published_date,

Volume, Status) VALUES
(1, 'To Kill a Mockingbird', 1, 25.00, 1, '1960-07-11', 1, 'Available'),
(2, 'One Hundred Years of Solitude', 2, 30.00, 2, '1967-05-30', 1, 'Available'),
(3, 'Le Petit Prince', 3, 20.00, 3, '1943-04-06', 1, 'Issued'),
(4, 'Die Verwandlung', 4, 15.00, 4, '1915-10-01', 1, 'Available'),
(5, 'Norwegian Wood', 5, 28.50, 5, '1987-09-04', 1, 'Reserved');

INSERT INTO AUTHOR (Author_id, Name, Email, Phone_number, Status) VALUES

(1, 'Harper Lee', 'harper.lee@example.com', '123-456-7890', 'Active'),
(2, 'Gabriel Garcia Marquez', 'gabriel.gm@example.com', '234-567-8901', 'Active'),
(3, 'Antoine de Saint-Exupery', 'antoine@example.com', '345-678-9012', 'Inactive'),
(4, 'Franz Kafka', 'franz.kafka@example.com', '456-789-0123', 'Inactive'),
(5, 'Haruki Murakami', 'haruki.m@example.com', '567-890-1234', 'Active');

INSERT INTO BOOK_AUTHOR (Book_id, Author_id) VALUES

(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5);

INSERT INTO MEMBER (Member_id, Name, Branch_code, Roll_Number, Phone_number,

Email_id, Date_of_join, Status) VALUES
(1, 'Alice Johnson', 'CS01', '123456', '789-012-3456', 'alice.j@example.com', '2022-09-01',
'Active'),

101
102
(2, 'Bob Smith', 'ME02', '234567', '890-123-4567', 'bob.smith@example.com', '2023-01-15',
'Active'),
(3, 'Charlie Brown', 'EE03', '345678', '901-234-5678', 'charlie.b@example.com', '2021-03-10',
'Suspended'),
(4, 'Diana Prince', 'CE04', '456789', '012-345-6789', 'diana.p@example.com', '2020-07-23',
'Active'),
(5, 'Edward Nigma', 'MA05', '567890', '123-456-7890', 'edward.n@example.com', '2022-10-
30', 'Inactive');

INSERT INTO BOOK_ISSUE (Issue_id, Date_of_issue, Book_id, Member_id,

Expected_date_of_return, Status) VALUES
(1, '2023-10-01', 1, 1, '2023-10-15', 'Issued'),
(2, '2023-09-20', 2, 2, '2023-10-04', 'Returned'),
(3, '2023-09-25', 3, 3, '2023-10-09', 'Overdue'),
(4, '2023-10-05', 4, 4, '2023-10-19', 'Issued'),
(5, '2023-10-08', 5, 5, '2023-10-22', 'Issued');

INSERT INTO BOOK_RETURN (Issue_id, Actual_date_of_return, Late_days, Late_fee)

VALUES
(2, '2023-10-05', 1, 0.50),
(3, '2023-10-12', 3, 1.50),
(4, '2023-10-19', 0, 0.00),
(5, '2023-10-22', 0, 0.00),
(1, '2023-10-16', 1, 0.50);

INSERT INTO LATE_FEE_RULE (From_days, To_days, Amount) VALUES

(1, 3, 0.50),
(4, 7, 1.00),
(8, 14, 2.00),
(15, 21, 3.00),
(22, 30, 5.00);
Output
Query OK, 5 rows affected

103
104
3. Book return should insert an entry into the Book_Return table and also update the
status in Book_Issue table as "Returned". Create a database TRANSACTION to do this
operation (stored procedure).
Create database TRANSACTION;
Use TRANSACTION;
Delimiter //
Create procedure BookReturn (IN p1 int, IN p2 date, IN p3 int, IN p4 int)
begin
update BOOK_ISSUE set Status = 'Returned' where Issue_id = p1;
insert into BOOK_RETURN values (p1, p2, p3, p4);
end //
Delimiter ;
Output
Query OK, 0 rows affected

BOOK_ISSUE
+----------+---------------+---------+-----------+---------------------
----+----------+
| Issue_id | Date_of_issue | Book_id | Member_id |
Expected_date_of_return | Status |
+----------+---------------+---------+-----------+---------------------
----+----------+
| 1 | 2023-10-01 | 1 | 1 | 2023-10-15 |
Issued |
| 2 | 2023-09-20 | 2 | 2 | 2023-10-04 |
Returned |
| 3 | 2023-09-25 | 3 | 3 | 2023-10-09 |
Overdue |
| 4 | 2023-10-05 | 4 | 4 | 2023-10-19 |
Issued |
| 5 | 2023-10-08 | 5 | 5 | 2023-10-22 |
Issued |
+----------+---------------+---------+-----------+---------------------
----+----------+

BOOK_RETURN
+----------+---------------------+-----------+----------+
| Issue_id |Actual_date_of_return| Late_days | Late_fee |
+----------+---------------------+-----------+----------+
| 1 | 2023-10-16 | 1 | 0.50 |
| 2 | 2023-10-05 | 1 | 0.50 |
| 3 | 2023-10-12 | 3 | 1.50 |
| 4 | 2023-10-19 | 0 | 0.00 |
+----------+---------------------+-----------+----------+

105
106
4.Call the procedure
call BookReturn (5, '2024-10-1', 0, 0);
Output
BOOK_ISSUE
+----------+---------------+---------+-----------+---------------------
----+----------+
| Issue_id | Date_of_issue | Book_id | Member_id |
Expected_date_of_return | Status |
+----------+---------------+---------+-----------+---------------------
----+----------+
| 1 | 2023-10-01 | 1 | 1 | 2023-10-15 |
Issued |
| 2 | 2023-09-20 | 2 | 2 | 2023-10-04 |
Returned |
| 3 | 2023-09-25 | 3 | 3 | 2023-10-09 |
Overdue |
| 4 | 2023-10-05 | 4 | 4 | 2023-10-19 |
Issued |
| 5 | 2023-10-08 | 5 | 5 | 2023-10-22 |
Returned |
+----------+---------------+---------+-----------+---------------------
----+----------+

BOOK_RETURN
+----------+---------------------+-----------+----------+
| Issue_id |Actual_date_of_return| Late_days | Late_fee |
+----------+---------------------+-----------+----------+
| 1 | 2023-10-16 | 1 | 0.50 |
| 2 | 2023-10-05 | 1 | 0.50 |
| 3 | 2023-10-12 | 3 | 1.50 |
| 4 | 2023-10-19 | 0 | 0.00 |
| 5 | 2023-10-22 | 0 | 0.00 |
+----------+---------------------+-----------+----------+

RESULT:
Familiarized with stored procedures.

107
108
EXPERIMENT NO:11 DATE:09/10/24
LIBRARY MANAGEMENT - VIEWS
AIM:
To familiarize with creating views.

DESCRIPITION:
A view is a database object that has no values. Its contents are based on the base table. It
contains rows and columns similar to the real table. The view is a virtual table created by a
query by joining one or more tables.

Syntax:
Create [OR replace] view view_name as
Select columns
From tables
[Where conditions];

QUIRES:
1. Create a database view "Available_Books", which will list out books that are currently
available in the library.
CREATE VIEW Available_Books AS
SELECT Title
FROM BOOK
WHERE Book_id NOT IN (
SELECT Book.Book_id
FROM BOOK
JOIN BOOK_ISSUE ON BOOK.Book_id = BOOK_ISSUE.Book_id
WHERE BOOK_ISSUE.Status = 'Issued'
);

Output
Query OK, 0 rows affected

109
select * from Available_Books
+-------------------------------+
| Title |
+-------------------------------+
| One Hundred Years of Solitude |
| Le Petit Prince |
| Norwegian Wood |
+-------------------------------+

RESULT:
Familiarized with creating views.

111
112
EXPERIMENT NO:12 DATE:09/10/24
LIBRARY MANAGEMENT - STORED PROCEDURE II
AIM:
To familiarize with stored procedure.

QUIRES:
1. Create a database procedure to add, update and delete a book to the Library database
(use parameters).
DELIMITER //
CREATE PROCEDURE Modify (
IN s1 INT,
IN s2 VARCHAR(30),
IN s3 INT,
IN s4 INT,
IN s5 INT,
IN s6 DATE,
IN s7 INT,
IN s8 VARCHAR(20),
IN s9 INT
)
BEGIN
IF s9 = 1 THEN
INSERT INTO BOOK (Book_id, Title, Language_id, MRP, Publisher_id,
Published_date, Volume, Status)
VALUES (s1, s2, s3, s4, s5, s6, s7, s8);
ELSEIF s9 = 2 THEN
UPDATE BOOK
SET MRP = s4, Volume = s7, Status = s8
WHERE Book_id = s1;
ELSEIF s9 = 3 THEN
DELETE FROM BOOK

113
114
 WHERE Book_id = s1;
END IF;
END //
DELIMITER ;
Output
Query OK, 0 rows affected
+---------+-----------------------------+-------------+-------+--------
------+----------------+--------+-----------+
| Book_id | Title | Language_id | MRP | Publisher_id
| Published_date | Volume | Status |
+---------+-----------------------------+-------------+-------+--------
------+----------------+--------+-----------+
| 1 | To Kill a Mockingbird | 1 | 25.00 | 1
| 1960-07-11 | 1 | Available |
| 2 | One Hundred Years of Solitude | 2 | 30.00 | 2
| 1967-05-30 | 1 | Available |
| 3 | Le Petit Prince | 3 | 20.00 | 3
| 1943-04-06 | 1 | Issued |
| 4 | Die Verwandlung | 4 | 15.00 | 4
| 1915-10-01 | 1 | Available |
| 5 | Norwegian Wood | 5 | 28.50 | 5
| 1987-09-04 | 1 | Reserved |
+---------+-----------------------------+-------------+-------+--------
------+----------------+--------+-----------+
Execution
call Modify (6,’Kite Runner’ ,1,100,2,’2020-09-04’,1,’Available’,1);
+---------+-----------------------------+-------------+-------+--------
------+----------------+--------+-----------+
| Book_id | Title | Language_id | MRP | Publisher_id
| Published_date | Volume | Status |
+---------+-----------------------------+-------------+-------+--------
------+----------------+--------+-----------+
| 1 | To Kill a Mockingbird | 1 | 25.00 | 1
| 1960-07-11 | 1 | Available |
| 2 | One Hundred Years of Solitude | 2 | 30.00 | 2
| 1967-05-30 | 1 | Available |
| 3 | Le Petit Prince | 3 | 20.00 | 3
| 1943-04-06 | 1 | Issued |
| 4 | Die Verwandlung | 4 | 15.00 | 4
| 1915-10-01 | 1 | Available |
| 5 | Norwegian Wood | 5 | 28.50 | 5
| 1987-09-04 | 1 | Reserved |
| 6 | Kite Runner | 1 | 100.00| 2
| 2020-09-04 | 1 | Available |
+---------+-----------------------------+-------------+-------+--------
------+----------------+--------+-----------+

RESULT:
Familiarized with stored procedures.

115
116
EXPERIMENT NO:13 DATE:09/10/24
LIBRARY MANAGEMENT - CURSOR
AIM:
To familiarize with use of cursor.

DESCRIPITION:
A cursor is a temporary memory or temporary work station. It allows row-by-row processing
of the result set. A cursor is used for the result set and returned from a query.

QUIRES:
1 Use cursors and create a procedure to print Books Issue Register (page wise - 20 rows
in a page).
DELIMITER //
CREATE PROCEDURE books_issue_register()
BEGIN
DECLARE v_issueid INTEGER;
DECLARE v_doi DATE;
DECLARE v_exp_dor DATE;
DECLARE v_memberid INTEGER;
DECLARE flag INTEGER DEFAULT 0;

DECLARE cur CURSOR FOR

SELECT Issue_id, Date_of_issue, Expected_date_of_return, Member_id
FROM BOOK_ISSUE;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET flag = 1;
OPEN cur;
get_list: LOOP
FETCH cur INTO v_issueid, v_doi, v_exp_dor, v_memberid;
IF flag = 1 THEN
LEAVE get_list;
END IF;

117
118
 SELECT CONCAT(v_issueid, ':', v_doi, ':', v_exp_dor, ':', v_memberid) AS
register;
END LOOP get_list;
CLOSE cur;
END //
DELIMITER ;
Output
Query OK, 0 rows affected
Execution
CALL books_issue_register();
+---------------------------+
| register |
+---------------------------+
| 5:2023-10-08:2023-10-22:5 |
+---------------------------+

RESULT:
Familiarized with cursor.

119
120
EXPERIMENT NO:14 DATE:16/10/24
LIBRARY MANAGEMENT - TRIGGER
AIM:
To familiarize with use of trigger.

DESCRIPITION:
A trigger is a stored procedure in database which automatically involves whenever a special
event occurs in the database design.

QUIRES:
1 Create a history table (you may use the same structure without any keys) for the
MEMBER table and copy the original values of the row being updated to the history table
using a TRIGGER.
CREATE TABLE History (
Member_id INT,
Name VARCHAR(30),
Branch_code VARCHAR(30),
Roll_no INT,
Ph_no VARCHAR(30),
Email VARCHAR(20),
Date_of_join DATE,
Status VARCHAR(10)
);

Delimiter //
Create Trigger update_history
before update on MEMBER
for each row
begin
insert into History select * from MEMBER where Member_id = new.Member_id;
end //
Delimiter ;

121
122
Output
Query OK, 0 rows affected
MEMBER
+-----------+------------------+-----------+---------+--------------+--
------------------------+--------------+----------+
| Member_id | Name | Branch | Roll_No | Phone_Number |
Email | Date_of_join | Status |
+-----------+------------------+-----------+---------+--------------+--
------------------------+--------------+----------+
| 1 | Alice Johnson | CS01 | 123456 | 789-012-3456 |
alice.j@example.com | 2022-09-01 | Active |
| 2 | Bob Smith | ME02 | 234567 | 890-123-4567 |
bob.smith@example.com | 2023-01-15 | Active |
| 3 | Charlie Brown | EE03 | 345678 | 901-234-5678 |
charlie.b@example.com | 2021-03-10 |Suspended |
| 4 | Diana Prince | CE04 | 456789 | 012-345-6789 |
diana.p@example.com | 2020-07-23 | Active |
| 5 | Edward Nigma | MA05 | 567890 | 123-456-7890 |
edward.n@example.com | 2022-10-30 | Inactive |
+-----------+------------------+-----------+---------+--------------+--
------------------------+--------------+----------+

Execution
UPDATE Member SET Status = "Inactive" WHERE Member_id = 1;
+-----------+------------------+-----------+---------+--------------+--
------------------------+--------------+----------+
| Member_id | Name | Branch | Roll_No | Phone_Number |
Email | Date_of_join | Status |
+-----------+------------------+-----------+---------+--------------+--
------------------------+--------------+----------+
| 1 | Alice Johnson | CS01 | 123456 | 789-012-3456 |
alice.j@example.com | 2022-09-01 | Active |
+-----------+------------------+-----------+---------+--------------+--
------------------------+--------------+----------+

RESULT:
Familiarized with the use of trigger.

123
124
EXPERIMENT NO:15 DATE:16/10/24
TRIGGER II
AIM:
To familiarize with use of trigger.

DESCRIPITION:
A trigger is a stored procedure in database which automatically involves whenever a special
event occurs in the database design.

QUIRES:
1.Create tables customer, customer_detail, mini_statement, micro_statement,
deleted_customer.
CREATE TABLE customer (
acc_no INT PRIMARY KEY,
cust_name VARCHAR(100),
avail_balance DECIMAL(10, 2)
);
CREATE TABLE customer_detail (
detail_id INT AUTO_INCREMENT PRIMARY KEY,
acc_no INT,
cust_name VARCHAR(100),
FOREIGN KEY (acc_no) REFERENCES customer(acc_no)
);
CREATE TABLE mini_statement (
statement_id INT AUTO_INCREMENT PRIMARY KEY,
acc_no INT,
avail_balance DECIMAL(10, 2),
statement_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (acc_no) REFERENCES customer(acc_no)
);
CREATE TABLE micro_statement (
micro_statement_id INT AUTO_INCREMENT PRIMARY KEY,

125
126
 acc_no INT,
avail_balance DECIMAL(10, 2),
statement_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (acc_no) REFERENCES customer(acc_no)
);
CREATE TABLE deleted_customer (
deleted_id INT AUTO_INCREMENT PRIMARY KEY,
acc_no INT,
cust_name VARCHAR(100),
deletion_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
2. Describe the tables
describe customer;
describe customer_detail;
describe mini_statement;
describe micro_statement;
describe deleted_customer;
3. Insert five rows in customer table
insert into customer values (100, 'Manu', 12000);
insert into customer values (101, 'Remya', 2500);
insert into customer values (102, 'Anu', 13500);
insert into customer values (103, 'Sonu', 10000);
insert into customer values (104, 'Achu', 12500);
4. Create a trigger to insert account number and customer name into customer_detail
table after an insert occurred in customers table.
DELIMITER //
CREATE TRIGGER after_customer_insert
AFTER INSERT ON customer
FOR EACH ROW
BEGIN
INSERT INTO customer_detail (acc_no, cust_name)
VALUES (NEW.acc_no, NEW.cust_name);

127
128
 END;
//
DELIMITER ;
Output
Query OK, 0 rows affected
Customer
+--------+-----------+---------------+
| acc_no | cust_name | avail_balance |
+--------+-----------+---------------+
| 100 | Manu | 12000.00 |
| 101 | Remya | 2500.00 |
| 102 | Anu | 13500.00 |
| 103 | Sonu | 10000.00 |
| 104 | Achu | 12500.00 |
+--------+-----------+---------------+

Execution
INSERT INTO customer (acc_no, cust_name, avail_balance) VALUES (105, 'John Doe',
5000);
Customer_details
+-----------+--------+-----------+
| detail_id | acc_no | cust_name |
+-----------+--------+-----------+
| 1 | 105 | John Doe |
+-----------+--------+-----------+

5. Create a trigger on customers table such that whenever an update occurs in customers
table insert (old) values into a mini-statement record (including account number and
available balance as parameters).
DELIMITER //
CREATE TRIGGER update_customer
BEFORE UPDATE ON customer
FOR EACH ROW
BEGIN
INSERT INTO mini_statement (acc_no, avail_balance)
VALUES (OLD.acc_no, OLD.avail_balance);
END;
//
DELIMITER ;

129
130
Execution:
update customer set avail_balance = 15000 where acc_no = 101;
select * from customers;
select * from mini_statement;
mini_statment
+--------------+--------+---------------+---------------------+
| statement_id | acc_no | avail_balance | statement_date |
+--------------+--------+---------------+---------------------+
| 1 | 101 | 2500.00 | 2024-11-05 11:06:04 |
+--------------+--------+---------------+---------------------+

6. Create a trigger on customer table to insert (old) values of account number and
available balance into micro statement record after an update has occurred.
DELIMITER //
CREATE TRIGGER insert_new_customer
AFTER INSERT ON customer
FOR EACH ROW
BEGIN
INSERT INTO micro_statement (acc_no, avail_balance)
VALUES (NEW.acc_no, NEW.avail_balance);
END;
//
DELIMITER ;
Execution
INSERT INTO customer (acc_no, cust_name, avail_balance) VALUES (106 'Doe', 1000);

Micro_statement
+-------------------+--------+---------------+---------------------+
| micro_statement_id| acc_no | avail_balance | statement_date |
+-------------------+--------+---------------+---------------------+
| 1 | 106 | 1000.00 | 2024-11-05 11:11:13 |
+-------------------+--------+---------------+---------------------+

131
132
7. Create a trigger to insert account number and customer name into deleted_customers
table after a deletion occurred in customer’s table.
DELIMITER //
CREATE TRIGGER after_delete_store
AFTER DELETE ON customer
FOR EACH ROW
BEGIN
INSERT INTO deleted_customer (acc_no, cust_name)
VALUES (OLD.acc_no, OLD.cust_name);
END //
DELIMITER ;
Execution
DELETE FROM customer WHERE acc_no = 102;
Deleted_customer
+------------+--------+------------+---------------------+
| deleted_id | acc_no | cust_name | deletion_date |
+------------+--------+------------+---------------------+
| 1 | 106 | Jane Smith | 2024-11-05 11:20:00 |
+------------+--------+------------+---------------------+

RESULT:
Familiarized with the use of trigger.

133
134
EXPERIMENT NO:16 DATE:21/10/24
STORED PROCEDURE III
AIM:
To familiarize with use of stored procedure.

QUIRES:
1. Create a database for employee information system for an organization.
Each employee in the organization can be described with unique employee ID, first name,
last name, email ID, phone number, hire date, salary, etc. Each employee occupies a job
in the organization hierarchy, and each employee belongs to a division or department of
the organization. Each division or department is headed by a manager. Employee is
residing in various cities of the cities, which belongs to a state and has a unique postal
code. Derive relational schema from ER-model and express following query in SQL.

a) Create a database employees

CREATE DATABASE employees;
Output
Query OK, 0 rows affected

b) Create table city

CREATE TABLE city (
city_name VARCHAR(20),
postal_code INT PRIMARY KEY NOT NULL,
state VARCHAR(20)
);
Output
Query OK, 0 rows affected

c) Create table employees

CREATE TABLE employee (
emp_id INT NOT NULL PRIMARY KEY,
first_name VARCHAR(20),
last_name VARCHAR(20),

135
136
 email_id VARCHAR(20),
phone_no VARCHAR(20),
hire_date DATE,
salary INT(,
postal_code INT,
FOREIGN KEY (postal_code) REFERENCES city(postal_code)
);
Output
Query OK, 0 rows affected

d) Create table department

CREATE TABLE department (
manager_id INT,
dept_name VARCHAR(20) PRIMARY KEY NOT NULL,
FOREIGN KEY (manager_id) REFERENCES employee(emp_id)
);
Output
Query OK, 0 rows affected

e) Insert values in the table city

INSERT INTO city (city_name, postal_code, state) VALUES
('Thrikkedilthanam', 686523, 'Kerala'),
('Changanassery', 686535, 'Kerala'),
('Pala', 686536, 'Kerala'),
('Kadaplamattam', 686545, 'Kerala'),
('Erode', 686589, 'Tamil Nadu');
Output
Query OK, 5 rows affected

137
138
f) Insert values in the table employee
INSERT INTO employee (emp_id, first_name, last_name, email_id, phone_no,
hire_date, salary, postal_code) VALUES
(1001, 'Anandan', 'NK', 'anandan@gmail.com', 9841423142, '2001-08-10', 10000,
686535),
(1002, 'Abhijith', 'K', 'abhijith@gmail.com', 903456781, '2010-05-30', 15000,
686523),
(1003, 'Amal', 'Ashek', 'amal@gmail.com', 915456151, '1996-01-12', 9000, 686523);
Output
Query OK, 3 rows affected

g) Insert values in the table department

INSERT INTO department (manager_id, dept_name) VALUES
(1001, 'Mathematics'),
(1001, 'Physics'),
(1002, 'CS');
Output
Query OK, 3 rows affected

h) Create a procedure to count the number of employees with the following conditions:
i) salary < 10000
ii) salary > 10000
iii) salary = 10000
delimiter //

create procedure test (inout no_employees int, in sal int)

begin
case
when (sal > 10000) then
(select count(emp_id) no_employees from employee
where salary > 10000);
when (sal < 10000) then
(select count(emp_id) no_employees from employee

139
140
 where salary < 10000);
else
(select count(emp_id) no_employees from employee
where salary = 10000);
end case;
end //

delimiter ;
Output
Query OK, 0 rows affected
Execution
call test (@counts, 10000) ;
+--------------+
| no_employees |
+--------------+
| 1 |
+--------------+

i) Write a procedure which will accept employee no and calculate the bonus as per the
following instructions:
Salary Bonus

<= 15000 20% of salary

15001-40000 15% of salary

40001-80000 10% of salary

> 80000 5% of salary

delimiter //
Create procedure Bonus_Calculate (in emp_id int, inout bonus int)
begin
declare sal int;
SELECT salary INTO sal FROM employee WHERE emp_id = emp_id LIMIT 1;
case
when (sal <= 15000) then

141
142
 (select (0.2 * sal) into bonus);
when (sal > 15000 and sal <= 40000) then
(select (0.15 * sal) into bonus);
when (sal > 40000 and sal <= 80000) then
(select (0.1 * sal) into bonus);
when (sal > 80000) then
(select (0.05 * sal) into bonus);
end case;
select bonus from employee;
end //
delimiter ;
Output
Query OK, 0 rows affected
Execution
call Bonus_Calculate (1001, @bonus);
+-------+
| bonus |
+-------+
| 2000 |
| 2000 |
| 2000 |
+-------+

RESULT:
Familiarized with the use of stored procedures.

143
OUTPUT:
d) db.db_mca.find()
{"_id":{"$oid":"6253bca768e7049d77715897"},"name":"Joel","rollno":"34","DOB":"13- 06-
2000"}
{"_id":{"$oid":"6253bca768e7049d77715898"},"name":"Athul","rollno":"20","DOB":"11-
05-2001"}
{"_id":{"$oid":"6253bca768e7049d77715899"},"name":"sasi","rollno":"13","DOB":"12- 09-
2000"}
{"_id":{"$oid":"6253bca768e7049d7771589a"},"name":"dude","rollno":"29","DOB":"15-
07-2001"}
{"_id":{"$oid":"6253bca768e7049d7771589b"},"name":"rony","rollno":"30","DOB":"18-
04-2001"}
{"_id":{"$oid":"6253bca768e7049d7771589c"},"name":"Amal","rollno":"9","DOB":"17-
06-2001"}
{"_id":{"$oid":"6253bca768e7049d7771589d"},"name":"robin","rollno":"77","DOB":"13-
08-1955"}
{"_id":{"$oid":"6253bca768e7049d7771589e"},"name":"niv","rollno":"65","DOB":"21- 09-
1978"}
{"_id":{"$oid":"6253bca768e7049d7771589f"},"name":"diya","rollno":"70","DOB":"04- 03-
1947"}
{"_id":{"$oid":"6253bca768e7049d777158a0"},"name":"lone","rollno":"01","DOB":"05-
04-1985"}
e) db.db_mca.find().limit(5)
{"_id":{"$oid":"6253bca768e7049d77715897"},"name":"Joel","rollno":"34","DOB":"13- 06-
2000"}
{"_id":{"$oid":"6253bca768e7049d77715898"},"name":"Athul","rollno":"20","DOB":"11-
05-2001"}
{"_id":{"$oid":"6253bca768e7049d77715899"},"name":"sasi","rollno":"13","DOB":"12- 09-
2000"}
{"_id":{"$oid":"6253bca768e7049d7771589a"},"name":"dude","rollno":"29","DOB":"15-
07-2001"}
{"_id":{"$oid":"6253bca768e7049d7771589b"},"name":"rony","rollno":"30","DOB":"18-
04-2001"}
f) db.db_mca.find().skip(2)
{"_id":{"$oid":"6253bca768e7049d77715899"},"name":"sasi","rollno":"13","DOB":"12- 09-
2000"}
{"_id":{"$oid":"6253bca768e7049d7771589a"},"name":"dude","rollno":"29","DOB":"15-
07-2001"}
{"_id":{"$oid":"6253bca768e7049d7771589b"},"name":"rony","rollno":"30","DOB":"18-
04-2001"}
{"_id":{"$oid":"6253bca768e7049d7771589c"},"name":"Amal","rollno":"9","DOB":"17-
06-2001"}
{"_id":{"$oid":"6253bca768e7049d7771589d"},"name":"robin","rollno":"77","DOB":"13-
08-1955"}
{"_id":{"$oid":"6253bca768e7049d7771589e"},"name":"niv","rollno":"65","DOB":"21- 09-
1978"}
{"_id":{"$oid":"6253bca768e7049d7771589f"},"name":"diya","rollno":"70","DOB":"04- 03-
1947"}

144
 CYCLE 4
EXPERIMENT NO:17 DATE:04/11/24
MONGO DB - DATABASE 1
AIM:
Build sample collections/documents to perform query operation.

QUIRES:
a) Create a database (Eg: MyCev)
use MyCev

b) Create a collection (Eg: db_mca)

db.createCollection('db_mca')

c) Create a collection (Eg: db_cs)

db.createCollection('db_cs')

d) Insert 10 data to the collection

db.db_mca.insertMany ([
{ name: 'Joel', rollno: '34', DOB:"13-06-2000" },
{ name: 'Athul', rollno: '20', DOB:"11-05-2001" },
{ name: 'sasi', rollno: '13', DOB: "12-09-2000" },
{ name: 'dude', rollno: '29', DOB: "15-07-2001" },
{ name: 'rony', rollno: '30', DOB: "18-04-2001" },
{ name: 'Amal', rollno: '9', DOB: "17-06-2001" },
{ name: 'robin', rollno: '77', DOB:"13-08-1955" },
{ name: 'niv', rollno:'65', DOB:"21-09-1978" },
{ name: 'diya', rollno: '70', DOB:"04-03-1947" },
{ name: 'lone', rollno: '01', DOB:"05-04-1985" }
])
e) List the first 5 data from the collection (limit)
db.db_mca.find().limit(5)

145
{"_id" : ObjectId("6253bca768e7049d777158a0"), "name" : "lone", "rollno" : "01", "DOB" :
"05-04-1985"}
{"_id" : ObjectId("6253bca768e7049d7771589c"), "name" : "Amal", "rollno" : "9", "DOB" :
"17-06-2001"}
{"_id" : ObjectId("6253bca768e7049d77715898"), "name" : "Athul", "rollno" : "20", "DOB"
: "11-05-2001"}
{"_id" : ObjectId("6253bca768e7049d77715897"), "name" : "Joel", "rollno" : "34", "DOB" :
"13-06-2000"}
{"_id" : ObjectId("6253bca768e7049d7771589f"), "name" : "diya", "rollno" : "70", "DOB" :
"04-03-1947"}
{"_id" : ObjectId("6253bca768e7049d7771589a"), "name" : "dude", "rollno" : "29", "DOB" :
"15-07-2001"}
{"_id" : ObjectId("6253bca768e7049d777158a0"), "name" : "lone", "rollno" : "01", "DOB" :
"05-04-1985"}
{"_id" : ObjectId("6253bca768e7049d7771589e"), "name" : "niv", "rollno" : "65", "DOB" :
"21-09-1978"}
{"_id" : ObjectId("6253bca768e7049d7771589d"), "name" : "robin", "rollno" : "77", "DOB" :
"13-08-1955"}
{"_id" : ObjectId("6253bca768e7049d7771589b"), "name" : "rony", "rollno" : "30", "DOB" :
"18-04-2001"}
{"_id" : ObjectId("6253bca768e7049d77715899"), "name" : "sasi", "rollno" : "13", "DOB" :
"12-09-2000"}

h) db.db_mca.find()
{"_id" : ObjectId("6253bca768e7049d77715897"), "name" : "Joel", "rollno" : "34", "DOB" :
"13-06-2000"}
{"_id" : ObjectId("6253bca768e7049d77715899"), "name" : "sasi", "rollno" : "13", "DOB" :
"12-09-2000"}
{"_id" : ObjectId("6253bca768e7049d7771589a"), "name" : "dude", "rollno" : "29", "DOB" :
"15-07-2001"}
{"_id" : ObjectId("6253bca768e7049d7771589b"), "name" : "rony", "rollno" : "30", "DOB" :
"18-04-2001"}
{"_id" : ObjectId("6253bca768e7049d7771589c"), "name" : "Amal", "rollno" : "9", "DOB" :
"17-06-2001"}
{"_id" : ObjectId("6253bca768e7049d7771589d"), "name" : "robin", "rollno" : "77", "DOB" :
"13-08-1955"}
{"_id" : ObjectId("6253bca768e7049d7771589e"), "name" : "niv", "rollno" : "65", "DOB" :
"21-09-1978"}

146
f) List the entire data except first 2 data (skip)
db.db_mca.find().skip(2)

g) Sort the data by choosing any field in the collection

db.db_mca.find().sort({ name: 1 })

h) Delete data from the collection

db.db_mca.deleteOne({ name: 'Athul' })

i) Drop the collection (db_cs)

db.db_cs.drop()

j) Drop Database
db.dropDatabase()

147
{"_id" : ObjectId("6253bca768e7049d7771589f"), "name" : "diya", "rollno" : "70", "DOB" :
"04-03-1947"}
{"_id" : ObjectId("6253bca768e7049d777158a0"), "name" : "lone", "rollno" : "01", "DOB" :
"05-04-1985"}

148
RESULT:
Sample collection and documents are built and query operations are successfully executed.

149
OUTPUT:
d) { "_id" : ObjectId("62552df480b49718c39a5de4"), "student_name": "adarsh",
"student_rollno": 1, "mark": [ { "dbms": 70 }, { "mpmc": 70 }, { "cn": 70 }, { "ss": 70 }, {
"dm": 69 }, { "moss": 71 } ] }
{ "_id" : ObjectId("62552df480b49718c39a5de5"), "student_name": "amal", "student_rollno":
11, "mark": [ { "dbms": 50 }, { "mpmc": 60 }, { "cn": 60 }, { "ss": 100 }, { "dm": 69 }, {
"moss": 70 } ] }
{ "_id" : ObjectId("62552df480b49718c39a5de6"), "student_name": "joel", "student_rollno":
34, "mark": [ { "dbms": 80 }, { "mpmc": 80 }, { "cn": 80 }, { "ss": 90 }, { "dm": 69 }, { "moss":
100 } ] }
{ "_id" : ObjectId("62552df480b49718c39a5de7"), "student_name": "athul", "student_rollno":
20, "mark": [ { "dbms": 84 }, { "mpmc": 50 }, { "cn": 60 }, { "ss": 96 }, { "dm": 69 }, { "moss":
65 } ] }
{ "_id" : ObjectId("62552df480b49718c39a5de8"), "student_name": "dude", "student_rollno":
7, "mark": [ { "dbms": 74 }, { "mpmc": 60 }, { "cn": 70 }, { "ss": 86 }, { "dm": 69 }, { "moss":
55 } ] }
{ "_id" : ObjectId("62552df480b49718c39a5de9"), "student_name": "pranav",
"student_rollno": 60, "mark": [ { "dbms": 78 }, { "mpmc": 67 }, { "cn": 50 }, { "ss": 76 }, {
"dm": 69 }, { "moss": 67 } ] }
{ "_id" : ObjectId("62552df480b49718c39a5dea"), "student_name": "noel", "student_rollno":
15, "mark": [ { "dbms": 88 }, { "mpmc": 87 }, { "cn": 80 }, { "ss": 86 }, { "dm": 69 }, { "moss":
87 } ] }
{ "_id" : ObjectId("62552df480b49718c39a5deb"), "student_name": "nivin", "student_rollno":
40, "mark": [ { "dbms": 58 }, { "mpmc": 57 }, { "cn": 55 }, { "ss": 56 }, { "dm": 69 }, { "moss":
57 } ] }
{ "_id" : ObjectId("62552df480b49718c39a5dec"), "student_name": "thomas",
"student_rollno": 23, "mark": [ { "dbms": 98 }, { "mpmc": 97 }, { "cn": 95 }, { "ss": 96 }, {
"dm": 69 }, { "moss": 97 } ] }
{ "_id" : ObjectId("62552df480b49718c39a5ded"), "student_name": "athulp",
"student_rollno": 21, "mark": [ { "dbms": 88 }, { "mpmc": 87 }, { "cn": 85 }, { "ss": 86 }, {
"dm": 69 }, { "moss": 87 } ] }

150
EXPERIMENT NO:18 DATE:04/11/24
MONGO DB - DATABASE 2
AIM:
Design Databases using MongoDB and perform CRUD operations.

QUIRES:
a) Create a database Myclass.
use Myclass

b) Create a collection named "db_students" Should contain this fields: (student_name,

student_rollno, mark[subject, mark]) Nb: Mark should be stored as array
db.createCollection('db_students')

c) Insert details of 10 students in a class

db.Myclass.insertMany([
{"_id" : ObjectId("62552df480b49718c39a5de4"), "student_name": "adarsh",
"student_rollno": 1, "mark": [ { "dbms": 70 }, { "mpmc": 70 }, { "cn": 70 }, { "ss": 70
}, { "dm": 69 }, { "moss": 71 } ] },
{ "_id" : ObjectId("62552df480b49718c39a5de5"), "student_name": "amal",
"student_rollno": 11, "mark": [ { "dbms": 50 }, { "mpmc": 60 }, { "cn": 60 }, { "ss":
100 }, { "dm": 69 }, { "moss": 70 } ] },
{ "_id" : ObjectId("62552df480b49718c39a5de6"), "student_name": "joel",
"student_rollno": 34, "mark": [ { "dbms": 80 }, { "mpmc": 80 }, { "cn": 80 }, { "ss":
90 }, { "dm": 69 }, { "moss": 100 } ] },
{ "_id" : ObjectId("62552df480b49718c39a5de7"), "student_name": "athul",
"student_rollno": 20, "mark": [ { "dbms": 84 }, { "mpmc": 50 }, { "cn": 60 }, { "ss":
96 }, { "dm": 69 }, { "moss": 65 } ] },
{ "_id" : ObjectId("62552df480b49718c39a5de8"), "student_name": "dude",
"student_rollno": 7, "mark": [ { "dbms": 74 }, { "mpmc": 60 }, { "cn": 70 }, { "ss": 86
}, { "dm": 69 }, { "moss": 55 } ] },
{ "_id" : ObjectId("62552df480b49718c39a5de9"), "student_name": "pranav",
"student_rollno": 60, "mark": [ { "dbms": 78 }, { "mpmc": 67 }, { "cn": 50 }, { "ss":
76 }, { "dm": 69 }, { "moss": 67 } ] },
{ "_id" : ObjectId("62552df480b49718c39a5dea"), "student_name": "noel",
"student_rollno": 15, "mark": [ { "dbms": 88 }, { "mpmc": 87 }, { "cn": 80 }, { "ss":
86 }, { "dm": 69 }, { "moss": 87 } ] },

151
152
 { "_id" : ObjectId("62552df480b49718c39a5deb"), "student_name": "nivin",
"student_rollno": 40, "mark": [ { "dbms": 58 }, { "mpmc": 57 }, { "cn": 55 }, { "ss":
56 }, { "dm": 69 }, { "moss": 57 } ] },
{ "_id" : ObjectId("62552df480b49718c39a5dec"), "student_name": "thomas",
"student_rollno": 23, "mark": [ { "dbms": 98 }, { "mpmc": 97 }, { "cn": 95 }, { "ss":
96 }, { "dm": 69 }, { "moss": 97 } ] },
{ "_id" : ObjectId("62552df480b49718c39a5ded"), "student_name": "athulp",
"student_rollno": 21, "mark": [ { "dbms": 88 }, { "mpmc": 87 }, { "cn": 85 }, { "ss":
86 }, { "dm": 69 }, { "moss": 87 } ] }
])

d) List the entire students in the class

db.Myclass.find()

e) Update mark of any one student in the collection "db_students"

db.db_students.updateOne({ student_name: "amal" },{ $set: { mark: ["en", 50] } })

f) Delete the data of first student in the collection

db.coll.deleteOne({name:"amal"})

RESULT:
Designed Databases using MongoDB and perform CRUD operations.

153
OUTPUT:
a) db
Employee
b) show collections
db_employee

c) db.db_employee.find().pretty()
{
"_id" : ObjectId("625570d8fc9237d1c6693766"),
"emp_name" : "sarath",
"designation" : "sales",
"salary" : 15000
}
{
"_id" : ObjectId("625570d8fc9237d1c6693767"),
"emp_name" : "shyam",
"designation" : "manager",
"salary" : 50000
}
{
"_id" : ObjectId("625570d8fc9237d1c6693768"),
"emp_name" : "abraham",
"designation" : "superwiser",
"salary" : 35000
}
{
"_id" : ObjectId("625570d8fc9237d1c6693769"),
"emp_name" : "muhammed",
"designation" : "sales",
"salary" : 15000
}
{

154
EXPERIMENT NO:19 DATE:04/11/24
MONGO DB - DATABASE 3
AIM:
To design database for employee using MongoDB.

QUIRES:
(emp_name: "Sharath", designation: "sales", salary: 15000)
(emp_name: "Shyam", designation: "manager", salary: 50000)
(emp_name: "Abraham", designation: "superwiser", salary: 35000)
(emp_name: "Muhammed", designation: "sales", salary: 15000)
(emp_name: "Rohith", designation: "sales", salary: 20000)
(emp_name: "Nirmal", designation: "driver", salary: 20000)
(emp_name: "Samuel", designation: "superwiser", salary: 35000)
(emp_name: "Johns", designation: "sales", salary: 15000)

a) Create a Database Employee

use Employee

b) Create a collection "db_employee"

db.createCollection('db_employee')

155
 "_id" : ObjectId("625570d8fc9237d1c669376a"),
"emp_name" : "rohith",
"designation" : "sales",
"salary" : 20000
}
{
"_id" : ObjectId("625570d8fc9237d1c669376b"),
"emp_name" : "nirmal",
"designation" : "driver",
"salary" : 20000
}
{
"_id" : ObjectId("625570d8fc9237d1c669376c"),
"emp_name" : "samuel",
"designation" : "superwiser",
"salary" : 35000
}
{
"_id" : ObjectId("625570d8fc9237d1c669376d"),
"emp_name" : "johns",
"designation" : "sales",
"salary" : 15000
}

(d) { "_id" : ObjectId("625570d8fc9237d1c6693768"), "emp_name": "abraham",

"designation": "superwiser", "salary" : 35000}
{ "_id" : ObjectId("625570d8fc9237d1c669376c"), "emp_name": "samuel", "designation":
"superwiser", "salary" : 35000}

(e) { "_id" : ObjectId("625570d8fc9237d1c6693766"), "emp_name": "sarath", "designation":

"sales", "salary": 15000}

156
c) Insert the above employee details to the collection called "db_employee"

db.db_employee.insert([{emp_name:'sarath', designation:
'sales', salary:15000},

{emp_name:'shyam', designation: 'manager', salary:50000},

{emp_name:'abraham', designation:'superwiser', salary: 35000},

{emp_name: 'muhammed', designation: 'sales', salary:15000},

{emp_name:'rohith', designation:'sales', salary:20000},

{emp_name:'nirmal', designation:'driver', salary:20000},

{emp_name:'samuel', designation: 'superwiser',salary:35000},

{emp_name:'johns', designation:'sales', salary:15000}])

157
{ "_id" : ObjectId("625570d8fc9237d1c6693769"), "emp_name": "muhammed",
"designation": "sales", "salary" : 15000}
{ "_id" : ObjectId("625570d8fc9237d1c669376a"), "emp_name": "rohith", "designation":
"sales", "salary": 20000}
{ "_id" : ObjectId("625570d8fc9237d1c669376d"), "emp_name": "johns", "designation":
"sales", "salary" : 15000}

(f) db.db_employee.find().pretty()
{
"_id" : ObjectId("625570d8fc9237d1c6693766"),
"emp_name": "abhijith",
"designation": "sales",
"salary" : 15000
}
{
"_id" : ObjectId("625570d8fc9237d1c6693767"),
"emp_name": "shyam",
"designation": "manager",
"salary" : 50000
}
{
"_id" : ObjectId("625570d8fc9237d1c6693768"),
"emp_name": "abraham",
"designation": "superwiser",
"salary" : 35000
}
{
"_id" : ObjectId("625570d8fc9237d1c6693769"),
"emp_name": "muhammed",
"designation": "sales",
"salary" : 15000
}

158
d) List the details of employee having 'salary > 15000' AND designation = "supervisor"

db.db_employee.find({salary: {$gt:15000},designation:
{$eq:'supervisor'}})

e) List the details of employee who working in 'sales' department

db.db_employee.find({designation: {$eq:'sales'}})

f) Update the emp_name "Sarath" to Abhijith

db.db_employee.update({emp_name:'sarath'}, {$set:
{emp_name:'abhijith'}})

g) Find the total sum of salary of employees under the sales department

db.db_employee.aggregate([{$match:{designation:
{$eq:'sales'}}},{$group:{_id:"$designation", total:
{$sum:"$salary"}}}])

159
{
"_id" : ObjectId("625570d8fc9237d1c669376a"),
"emp_name" : "rohith",
"designation" : "sales",
"salary" : 20000
}
{
"_id" : ObjectId("625570d8fc9237d1c669376b"),
"emp_name" : "nirmal",
"designation" : "driver",
"salary" : 20000
}
{
"_id" : ObjectId("625570d8fc9237d1c669376c"),
"emp_name" : "samuel",
"designation" : "superwiser",
"salary" : 35000
}
{
"_id" : ObjectId("625570d8fc9237d1c669376d"),
"emp_name" : "johns",
"designation" : "sales",
"salary" : 15000
}

(g) {"_id": "sales", "total": 65000}

160
RESULT:
Designed database for employee using MongoDB.

161
MICROPROJECT
REPORT

162
ER DIAGRAM:

163
 Airline Reservation System
ABSTRACT:
The purpose of this project is to design and implement a relational database for an airline
reservation system. The system is expected to efficiently manage the seat booking, ticket
preferences, flight timings and layovers, luggage information, airport details, and details of
the passengers. The system will be able to maintain integrity and performance through the
use of normalisation and indexing methods. The system will have a strong base while also
providing flexibility to its structure. The project aims to ensure fast data retrieval from a
large data pool. Furthermore, the database schema is designed to ensure efficient retrieval,
storage and manipulation of data.

SCREEN LAYOUTS:

Procedures:

164
RELATIONAL SCHEMA:

165
166
167
Trigger:

168