SCHOOL OF ENGINEERING

(DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING)

A MINI PROJECT BASED ON

VEHICLE MANAGEMENT SYSTEM
Submitted in partial fullfilment of the requirement for the subject
DATABASE MANAGEMENT SYSTEMS(DBMS)

Submitted by:
[Link] Teja - 237Z1A1243
Shaik Sami - 237Z1A12254
[Link] - 237Z1A1256

Under the guidance of:

Mrs.S. Radhika

Academic Year:2023-2027
CERTIFICATE

This is to certify that the project entitled “Vehicle Management

System” has been carried out by Team No 8 under the guidance
of Mrs. S . Radhika ( Assistant professor) in partial fulfillment of the
requirements for the Database Management Systems course in the
Computer Science and Engineering department during the academic
year 2024–2025.

The project work is original and has not been submitted elsewhere
for any other degree or diploma.
DECLARATION

I hereby declare that the project titled “Vehicle Management

System” is an original work carried out by me under the supervision
of my faculty guide. This project has not been submitted to
any other institution or university for any degree or diploma.
I have taken due care in the acknowledgment of all sources and
references used in the preparation of this report.

Submitted by:
[Link] Teja - 237Z1A1243
Shaik Sami - 237Z1A12254
[Link] - 237Z1A1256

Date:
03 July,2025
I would like to express my sincere thanks to all those who supported
me in completing this project. I am grateful to my guide
[Link] (Assistant professor) for their invaluable advice,
guidance, and encouragement.
My thanks also go to the Head of Department, my classmates, and
my family for their
continuous moral support and motivation throughout the
project.
The Vehicle Management System is a database-driven application
designed to streamline and automate the management of vehicles within
an organization or a fleet-based business. The system provides a
centralized platform for storing, accessing, and managing data related to
vehicles, drivers, maintenance schedules, and usage history. The primary
objective is to enhance operational efficiency, reduce manual errors, and
ensure timely servicing and regulatory compliance.
This project utilizes a relational database management system (RDBMS)
such as MySQL, PostgreSQL, or Oracle to maintain structured data. Core
entities in the database include vehicles, drivers, assignments, service
records, fuel logs, and insurance details. The system supports CRUD
(Create, Read, Update, Delete) operations for all major components and
ensures data integrity through the use of foreign keys, normalization, and
constraints.
The Vehicle Management System is beneficial for transport companies,
government fleets, rental services, and any organization that requires
structured vehicle tracking. By using a DBMS as the backend, the system
ensures scalability, security, and efficient data retrieval, making vehicle
management more systematic and less error-prone.
1. Introduction
1.1 Requirements
1.2 Existing System
1.3 Proposed System
1.4 Entities
1.5 Relations

2. E-R Model

3. Relational Model

4. Logical Database Design

5. MySQL DB
6. Output Screens
7. Future Enhancements

8. Conclusion
 al
ensnheest
Grupsthetthtas
NALLA NARASIMHA REDDY Acredias
br

NBC Education Society's Group of Institutions-Integrated Campus

SCHOOL OF ENGINEERING

Project Report

Roll Student Name:S Manoj Branch &Section:CSE-

No:237Z1A05G1 C

Year &Sem:I-II Course Name:Database Management System

Project Title:Vehicle Management System

Submitted
to
[Link].

Asst Professor.
DESIGN OF ER-MODEL;

IDENTIFY THE ENTITIES:

· Vehicle
· Owner
● Driver
● ServiceRecord
● Insurance
● Registration

IDENTIFY THE ATTRIBUTES:

· Vehicle: VehicleID (PK),Model,Make,Year,Color,EngineNumber,ChassisNumber ·
Owner: OwnerID (PK),Name,Phone,Email,Address
· Driver:DriverlD (PK),Name,LicenseNumber,Phone
· ServiceRecord: ServicelD (PK),VehicleID (FK),ServiceDate,Description,Cost
· Insurance:InsuranceID (PK),VehicleID (FK),Provider,StartDate,EndDate,Premium
· Registration:RegID (PK),VehicleID (FK),OwnerID (FK),RegNumber,RegDate,ExpiryDate

IDFNTIFY THF RELATTONSHIPS:

· Vehicle is owned by Owner

● Vehicle is driven by Driver
· Vehicle has multiple ServiceRecords
● Vehicle has Insurance
· Vehicle has Registration linked to Owner
 FIN
AL

premiu dervice id
nsuranceid provider vehicle
m id
insurance
service record
has
ehicleid has service date ER-
DIAGRAM

enddae cost
start date
ownerid description
address
name
make
year divenb
name owner vehicl driver driverid
e )
engne number
ownedb

vehicle id
chasssnumhb phone
phone emai license number
l

has reg number

done by,
vehicle id
regid
reg date
owner id registration
expiry date
CURD OPERATIONS:
mysql>CREATE TABLE Driver(
-> DriverID INT PRIMARY KEY,
-> Name VARCHAR(50),
-> LicenseNumber VARCHAR(20)UNIQUE NOT NULL,

Phone VARCHAR(15)
->

>)
Query Ok,o rows affected(0.02 sec)

mysql>
mysql>--Describe Table:Driver

Query Ok,0 rows affected(0.00 sec)

mysql>DESC Driver;

Field Type Nul Ke

y Defaul Extra
2 t

DriverID int NO PR NUL

I
Name varchar(50) YES
LicenseNu varchar(20) NO
mber varchar(15)
Phone YES
L-一一

------
-- -
rows in set(0.00 sec)

TABLE Owner(
mysql>CREATE
-> OwnerID INT PRIMARY KEY,
-> Phone VARCHAR(15)UNIQUE,
-> Email VARCHAR(50),
-> Address TEXT NOT NULL
->)
Query ok,o rows affected(0.04 sec)

mysql>
mysql>--Describe Table:Owner
Query Ok,0 rows affected(0.00 sec)

mysql>DESC Owner; +-—-

+---------+--- OwnerI int

Field |Type
 D| varchar(
50)
Name varchar( 一十—-
Phone 15)
varchar( Null I Key |Default |Extra
Email 50) text
Addres
s NO PRI NI

+----- YES N|

5 rows in set(0.01 sec) YES UNI

YES
NO NUL
mysql>--Create Table:Insurance
Query OK,0 rows affected(0.00 sec)

mysql>CREATE TABLE Insurance(

>

- InsuranceID INT PRIMARY KEY,

>-

>
VehicleID INT,
-> Provider VARCHAR(50),
->
StartDate DATE,
一>

->
EndDate DATE,

- Premium DECIMAL(10,2),
>) FOREIGN KEY(VehicleID)REFERENCES Vehicle(VehicleID)
Query
Ok,o rows affected(0.02 sec)

mysql>
mysql>--Describe Table:Insurance
Query Ok,0 rows affected(0.00 sec)

mysql>DESC Insurance;

+------- -
十
——- 一

Type Nul Re Defaul Extr

Field l y a
t

InsuranceID int NO PR NULL

VehicleID int YES
I NULL
Provider varchar(50) YES M NULL
U
StartDate date YES L NULL
EndDate date YES NULL
Premium decimal(10,2) YES NULL

6 rows in set(0.00 sec)

mysql>--Create Table:ServiceRecord
Query Ok,o rows affected(0.00 sec)

mysql>CREATE
TABLE ServiceRecord(
->

ServiceID INT PRIMARY KEY,

->

->
VehicleID INT,
ServiceDate DATE,
Description TEXT,
-> Cost DECIMAL(10,2),
->) FOREIGN KEY(VehicleID)REFERENCES Vehicle(VehicleID)
Query ok,0 rows affected(0.03 sec)
mysql>
mysql>--Describe
Table:ServiceRecord
Query OK,0 rows
affected(0.00 sec)

mysql>DESC ServiceRecord;

十一———- 一十————

Field Type Nul Ke Defaul Extr

l y t a

ServiceID int NO P NU
RT
VehicleID int YES M NULL
U
L
ServiceDat date YES NULL
e
Description text YES NULL
Cost decimal(10,2) YES NULL
5 rows in set(0.00 sec)
mysql>CREATE TABLE Vehicle(
VehicleID INT PRIMARY KEY,
-> Model VARCHAR(30),
-2 Make VARCHAR(30),
-> Year INT CHECK(Year >=1990),
-> Color VARCHAR(20),
->
EngineNumber VARCHAR(30)UNIQUE NOT NULL,
-
> ChassisNumber VARCHAR(30)UNIQUE NOT NULL
->):
Query Ok,0 rows affected(0.02 sec)

mysql>
mysql>--Describe Table:Vehicle
Query ok,θ rows affected(0.00 sec)

mysql>DESC Vehicle;

Field Type Nul Ke Defau Extr

l y lt a

VehicleID int NO PR N ]

Model varchar( YES I H

30)
Make varchar( YES
Year 30) YES
Color int YES
EngineNu varchar( NO
mber 20) NO U
ChassisNu varchar( NI
mber 30)
varchar( U
30) NI

7 rows in set(0.00 sec)

mysql>CREATE TABLE Registration(

> RegID INT PRIMARY KEY,
VehicleID INT,
OwnerID INT,
RegNumber VARCHAR(20)UNIQUE,
RegDate DATE,
ExpiryDate DATE,
FOREIGN KEY(VehicleID)REFERENCES Vehicle(VehicleID),
FOREIGN KEY(OwnerID)REFERENCES Owner(OwnerID)
Query->) ok,o rows affected(0.06 sec)

mysql>
mysql>--Describe Table:Registration

Query Ok,0 rows affected(0.00 sec)

mysql>DESC Registration;

十一

Type Nul Ke Defaul Extr

Field l y a
t

RegID int NO P NULL

VehicleID int RI
YES NULL
OwnerID int M NULL
YES U
RegNumb varchar(20 YES L NULL
er M
date YES NULL
RegDate U
date YES L NULL
ExpiryDat
e U
N
T

6 rows in set(0.00 sec)

NSERT:
mysql>--INSERT INTO Owner
Query Ok,0 rows affected(0.00 sec)

mysql>INSERT
INTO >(1,'Manoj Owner VALUES
->(2,'Dhanush Shivagalla','9876543210','[Link]','Hyderabad'),
>(3,'Raju
Kumar','9765432190','[Link]','Chennai'),
Mude','9654321980','[Link]','Bangalore'),
Query Ok,5 Sharma','9543219870','sunny@[Link]','Mumbai'),
rows Yada,9432198760,[Link],Dethi)
affected(0.01 sec)
Records:5 Duplicates:0 Warnings:0
mysql>

mysgl>--View Owner Table

Query Ok,0 rows affected(0.00 sec)

mysql>SELECT *FROM Owner;

L=

OwnerID |Name Phone Email Address

Manoj Shivagalla 9876543 [Link] Hyderab

1
210 dhanush@[Link] ad
2 Dhanush Kumar m
9765432 Chennai
3 Raju Mude 190 raju@[Link]
Bangalor
4 Sunny Sharma 96543219 sunny@[Link] e
80
5 Harsha Yada
9543219 [Link] Mumbai
870 Delhi
9432198
760

5 rows in set(0.00 sec)

mysql>--INSERT INTO Vehicle

Query Ok,o rows affected(0.00 sec)

mysql>INSERT INTO Vehicle VALUES

>(101,'i20','Hyundai',2020,'White','EN101','CH101'),
>(102,'Swift','Maruti',2019,'Red','EN102','CH102'),
>(103,'City','Honda',2021,'Black','EN103','CH103'),
>(104,'Creta','Hyundai',2022,'Blue','EN104','CH104'),
>(105,'Fortuner','Toyota',2023,'Silver','EN105','CH105');
Query Ok,5 rows affected(0.01 sec)
Records:5 Duplicates:0 Warnings:0
mysgls

mysql>--View Vehicle Table

Query Ok,0 rows affected(0.00 sec)
mysql>SELECT *FROM Vehicle;

Vehicle Model Make Ye Color EngineNu ChassisNu

ID ar mber mber

1 i20 Hyund 20 Whit EN101 CH101

0 ai 20 e
1
10 Swift Maruti 20 Red EN102 CH102
2 19
1 City Honda 20 Blac EN103 CH103
0 21 k
3
1 Creta Hyund 202 Blue EN104 CH104
0 ai 2
4
1 Fortuner Toyota 20 Silve EN105 CH105
0 23 r
5

5 rows in set (0.00 sec)

mysql>--INSERT INTO Driver
Query Ok,0 rows affected(0.00 sec)

mysql>INSERT Driver VALUES

INTO
>(201,'Chandu Sammeta','DL201','9123456780'),
->(202,'Suman Yara','DL202','9234567810'),
->(203,'Manoj Reddy','DL203','9345678120')
->(204,'Santosh Reddy','DL204', '9456781230'),
->(205,'Bhanu Prasad','DL205','9567812340');
Query Ok,5 rows affected(0.00 sec)
Records:5 Duplicates:0 Marnings:0

mysql>
mysql>--View Driver Table

Query Ok,0 rows affected(0.00 sec)

mysql>SELECT *FROM Driver;

十———— — —

DriverI Name LicenseNum Phone

D ber

20 Chandu DL201 91234567

1 Sammeta 80
20 Suman Yara DL202 92345678
2 10
20 Manoj Reddy DL203 93456781
3 20
20 Santosh DL204 94567812
4 Reddy 30
20 Bhanu DL205 95678123
5 Prasad 40

5 rows in set(0.00 sec)

mysql>INSERT INTO Registration VALUES Query Ok,5 rows
>(501,101,1,'TS09AB1234','2024-01- affected(0.00 sec) Records:5
05',
>(502,102,2,'TN10CD5678','2023- Duplicates:0 Warnings:0
06-10',
>(503,103,3,'KA03EF9101','2024-03- mysql>
tion e
15', - mysql>--View Re gistra Tabl
>(504,104,4,'MHO4GH2345','2024-04-
20', Query 0k,0 rows affected(0.00 sec)
>(505,105,5,'DL05IJ6789','2024-05-10',
 '2027-03-15'),
'2027-01-05'), '2027-04-20'),
'2026-06-10'), '2027-05-10');
mysql>SELECT *FROM Registration;

+
-
-------+ OwnerI RegNum RegDate ExpiryDa
VehicleI D ber te
RegI D
D
———-

50 10 1 TS09AB1 2024-01- 2027-01-

1 1 234 05 05
50 10 2 TN1OCD 2023-06- 2026-06-
2 2 5678 10 10
50 10 3 KA03EF9 2024-03- 2027-03-
3 3 101 15 15
504 10 4 MHO4GH 2024-04- 2027-04-
4 2345 20 20
505 10 5 DL05IJ67 2024-05- 2027-05-
5 89 10 10
5 rows in set (0.00 sec)
mysql>--INSERT INTO ServiceRecord
Query OK,0 rows affected(0.00 sec)
mysql>INSERT INTO ServiceRecord VALUES
>(301,101,'2024-01-
10','Oil >(302,102,'2024-02- Change',1200.00),
15','Tire Replacement',4000.00),
>(303,103,'2024-03- Service',6000.00),
20','Full ->(304,104,'2024-04-
05','AC Repair',2500.00),
>(305,105,'2024-05-01','Brake Check',1800.00);
Query Ok,5 rows affected(0.00 sec)

Records:5 Duplicates:0 Warnings:0

mysql>
mysql>--View ServiceRecord Table

Query OK,o rows affected(0.00 sec)

mysql>SELECT *FROM ServiceRecord;

ServiceI VehicleI ServiceDat Description Cost

D D e

30 10 2024-01- 0i1 Change 1200.0

1 1 10 0
30 10 2024-02- Tire 4000.0
2 2 15 Replacement 0
30 10 2024-03- Full Service 6000.0
3 3 20 0
30 10 2024-04-05 AC Repair 2500.0
4 4 0
30 10 2024-05- Brake Check 1800.0
5 5 01 0

5 rows in set(0.00 sec)

mysql>--INSERT INTO Insurance

Query ok,o rows affected(0.00 sec)

mysql>INSERT INTO Insurance VALUES

>(401,101,'HDFC Ergo','2024-01-01','2025-01-01',8000.00),
>(402,102,'ICICI Lombard','2023-06-01','2024-06-01',7500.00),
>(403,103,'Bajaj Allianz','2024-03-10','2025-03-10',8200.00),
>(404,104,'Reliance General','2024-04-15','2025-04-15',8800.00),
->(405,105,'Tata AIG','2024-05-05','2025-05-05',9100.00);
Query Ok,5 rows affected(0.00 sec)
Records:5 Duplicates:0 Warnings:0
mysql>
mysql>--View Insurance Table

Query ok,0 rows affected(0.00 sec)

mysql>SELECT *FROM Insurance;

Insurance VehicleI Proyider StartDat EndDate Premi

ID D e um

4 10 HDFC ErqC 2024-01- 2025-01- 8000.

0 1 01 01 00
1

4 10 ICICI Lombard 2023-06- 2024-06- 7500.

0 2 01 01 00
2
4 10 Bajaj Allianz 2024-03- 2025-03- 8200.
0 3 10 10 00
3
4 10 Reliance 2024-04- 2025-04- 8800.
0 4 General 15 15 00
4
4 10 Tata AIG 2024-05- 2025-05- 9100.
0 5 05 05 00
5

5 rows in set (0.00 sec)

UPDATE:
mysql>--Update Owner's city
Query Ok,0 rows affected(0.00 sec)
ss
m ysql>UPDAT E Owner S E Addr e 'Pune'WHERE OwnerID =1;
Qu ery O k, 1 row affecte (004 sec
Rows matched:1 Changed:1 Warnings:0

mysql>

nysgl>--View Owner table after updat

Query 0k,0 rows affected(0.00 sec)
mysql>SELECT *FROM Owner;
+---——- -——— 一
十
Name Phone Email Address
OwnerI
D

Manoj Shivagalla 9876543 [Link] Pune

Dhanush Kumar 210 [Link] Chennai
m
3 Raiu Mude 9765432 Bangal
190 raju@[Link] ore
4 Sunny Sharma 96543219 Mumb
sunny@[Link]
5 Harsha Yada 80 ai
[Link]
十
一一
95432198 Delhi
70
9432198
760

5 rows in set(0.00 sec)

RENAME:
>- Con
q
Re na e c ol u mn'Ph e!to'
yus l ctNumber'in Owner table
e ryOK rows af fec t ed 00 se

mysql>A TAB O eRENAM L UMNContactNum er T OPh one

LTER E CO ; -Reverting back if already
ERROR 1054 42S : Unknown colum n'ContactNu ber'in' own er'
m ysql> LTE R TABLE Owne r AMEC
renamed A UMN Phone TO ContactNumber;
Qu e ry O k,o rows affect 03 s
Records:0 Duplicates:0 Warnings:0
m >
mySsgqll >--View Owner table to confirm renamed column(structure only works in MySQL CLI)
Query Ok,0 rows affected(0.00 sec)
mysql>DESC Owner;

Field Type Nu K Defaul

ll ey t Extra

OwnerID int NO P NULL

Name varchar( YE NULL
50) S RI
ContactNumb varchar( NULL
er 15) YE
varchar(
 Email 50) text S U NULL
N
Address YE I NULL
S
NO

5 rows in set(0.00 sec)

DELFTF;
mysql>--[Link] Operation
Query OK,0 rows affected(0.00 sec)

mysql>--Delete a driver with DriverID =205

Query Ok,0 rows affected(0.00 sec)

mysql>DELETE FROM Driver WHERE DriverID =205;

Query Ok,1 row affected(0.00 sec)

mysql>
mysql>--View Driver table after deletion
Query Ok,0 rows affected(0.00 sec)

mysql>SELECT *FROM Driver;

S e

DriverI Name LicenseNum Phone

D ber

20 Chandu DL201 91234567

1 Sammeta 80
20 Suman Yara DL202 92345678
2 10
20 Manoj Reddy DL203 93456781
3 20
20 Santosh DL204 94567812
4 Reddy 30

4 rows in set(0.00 sec

CONSTRAINTS:
mysql>CREATE TABLE Employee(
一 > EmpID INT PRIMARY KEY,
-> Name VARCHAR(50)NOT NULL,
-> Age INT CHECK(Age >=18 AND Age <=60),
-> Email VARCHAR(100)UNIQUE,
-> Department VARCHAR(30)DEFAULT'General',

-> Salary DECIMAL(10,2)CHECK(Salary >=10000)

>);
query Ok,θ rows affected(0.03 sec)
mysql>desc Employee;

Field Type Nul Ke Defaul Extr

l y a
t

EmpID int NO PR NULI

Name varchar(50) NO I
NU]
Age int YES NULL
Email varchar(100) YES U Gene
Departmen varchar(30) YES NI ral
t NUL
decimal(10,2 YE L
Salary ) S
f

6 rows in set(0.00 sec)

mysql>INSERT INTO Employee VALUES

>(1,'Manoj Reddy',28,'[Link]','IT',50000),
->(2,'Santosh Reddy',35,'[Link]@[Link]','HR',45000),
→(3,'Shiva Mohith',40,'[Link]',DEFAULT,60000);

Query OK,3 rows affected(0.00 sec)

Records:3 Duplicates:0 Warnings:0

mysql>
mysql>SELECT *FROM Employee;

EmpID |Name Ag Email Departm Salary

e ent

1 Manoj 2 [Link]@emai IT 5000

Reddy 8 [Link] 0.00
2 [Link]@ema HR 4500
Santosh 3 [Link] 0.00
3 Reddy 5 [Link]@email General 60000
Shiva .com .00
Mohith 4
0

3 rows in set(0.00 sec)

CHECK
CONSTRAINT
INTO EmploYee VALUES
mysql>INSERT
->(6,'Too Young',15,'[Link]','Support',20000);
ERROR 3819(HY000):Check constraint 'employee_chk_1'is
violated

PRIMARY KEY CONSTRAINT:

 ye
N ERT INT O Emplo eV A LUE S
1 Anot her Guy',30, 'another@[Link]','Finance',30000);
ERROR 1062(23000):Duplicate entry '1'for key'[Link]'
DEFAULT:

mysql>INSERT INTO Employee(EmpID,Name,Age,Email,Department,Salary)VALUES

->(8,'Null Department',30,'nulldept@[Link]',NULL,30000);
Query Ok,1 row affected(0.00
NOT
sec)
NULL:
mysql>INSERT INTO Employee VALUES
->(4,NULL,29,'[Link]','Marketing',35000);
ERROR 1048(23000):Column 'Name!cannot be null JNIQUE:

INTO Employee VALUES

Email',32,'[Link]','Sales',40000);
mysql>INSERT
->(5,'Duplicate entry'[Link]'for key'[Link]'

CHECK PCONSTRAINT:WITH
A
lo
UPDATE:
al
m TE ary =9 00 WHE RE 1;
s l U D S 0 Em I D
E R 3819(EHm 0 e):CShEeTck con st ra int 'emplo y ee c k 2 s violated.
_ _
FINAL
STATE:

mysql>SELECT *FROM Employee;

Em Name Age Email Departm Salary

pID ent

Manoj Reddy 28 [Link]@[Link] IT 5009

0.0G
Santosh Reddy 35 HR 4500
[Link]@[Link] 0.00
3 Shiya Mohith m 40 General 6000
[Link] 0.60
8 Null 30 nulldept@[Link] NULL 3000
Department
0.00

4 rows in set(0.00 sec)

AGGREGATION FUNCTION:
COUNT:
 Employee;
+----------------+
mysql>SELECT COUNT(*)AS TotalEmployees FROM
I TotalEmployees |
+ ---十

4 |
SUM;

+-----------------+
SUM(Salary)AS TotalSalaryPaid FROM Employee;
mysql>SELECT
I TotalSalaryPaid
+-------- |
---+

185000.00|

AVG:

+----------- ---+
eSalary FROM Employee;
mysql>SELECT
l AverageSalaryAVG(Salary)AS
| Averag
+---------------+
+---------------+

1 46250.000000 |

MIN:
mysql> SELECT MIN(Salary)AS LowestSalary FROM Employee;
--
-- -+
LowestSalar
+---

30000.00

MAX:
 MAX(Salary)AS HighestSalary FROM Employee;
mysql>SELECT
l HighestSalary I

60000.00|
1 row in set(0.00 sec)
GROUP BY:
mySgl>SELECT
一 >
Department,
一 COUNT(*)AS NumEmployees
>

一 SUM(Salary)AS TotalSalary,
>

一 > AVG(Salary)AS AvgSalary

->FROM Employee
->GROUP BY Department;
一 一 十

Department |NumEmployees |TotalSalary |AvgSalary

IT
1 50000.00 50000.000000
HR 1
General
45000.00 45000.000000
1 60000.00
60000.000000
NULL 1 30000.00
30000.000000
4 rows in

HAVING BY: set(0.01 sec)

mysql>SELECT
-> Department,
-> COUNT(*)AS NumEmployees,
-> sum(Salary)AS TotalSalary,
-> AVG(Salary)AS AvgSalary
->FROM Employee
->GROUP BY Department
->HAVING AVG(Salary)>45000;

Departmen |NumEmployees| AvgSalary

t TotalSalary

IT 1 50000.00 |50000.000000
General 1 60000.00 60000.0000
 00

2 rows in set(0.00 sec)

VIEWS:
CREATE VIEW:
mysql>CREATE VIEW View_EmployeeSummary AS
->SELECT
->
EmpID,
-> Name
->
Department,
-> Salary
->FROM Employee
Query Ok,o rows affected(0.01 sec)
mysgl>
mysql>--View the data in the created view
query OK,0 rows affected(0.00 sec)

mysql>SELECT *FROM View_EmployeeSummary;

EmpID| Name Department Galarv

ddy
1 an j Re I T 50000
2 Sa tosh Reddy HR .00
3 Shiva Mohith General
45000
.00
8 |Null Department NULL 60000
.00
30000
.00

4 rows in set(0.00 sec)

UPDATE VIEW:
mysql>UPDATE View_EmployeeSummary
->SET Salary =48000
->WHERE EmpID =2;
Query Ok,1 row affected(0.00 sec)
Rows matched:1Changed:1 Warnings:0

mysql
nysgl>--View
> again to confirm update
Query Ok,0 rows affected(0.00 sec)

mysql>SELECT *FROM View_EmployeeSummary;

+-------+--
EmpID |Name Department |Salary

1 |Manoj Reddy L
4 rows in set(0.00 sec)
2 |Santosh
Reddy 3 |Shiva DROP VIEW:
Mohith

8 |Null Department
 NULL
IT 50000.00
HR
48000.00
General 60000.00
30000.0o

>DROP VIEW
mysql
View_EmployeeSummary; Query Ok,0

TRIGGERS:
mysql>CREATE TABLE AuditLog(

一 >

LogID INT AUTO_INCREMENT PRIMARY KEY,

->
ActionType VARCHAR(20),
->
-> Description TEXT,
ActionTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
->);
Query Ok,o rows affected(0.03 sec)
mysgl>DESC AuditLog;

Field Type Nu K Default Extra

ll ey

LogID int N P NULL auto_increment

ActionT varchar(
O RI
NULL
ype 20) text YE
Descript S NULL
ion timestamr DEFAULT_GEN
ActionT YE CURRENT_TIME ERATED
ime S STAMP
YE BEFO
S RE
4 rows in (0.00 sec)
set

mysql>CREATE TRIGGER before_employee_insert

NSERT:
->BEFORE INSERT ON Employee
->FOR
>END EACH ROW

-> VALUES('BEFORE INSERT',CONCAT('Trying to insert employee:',[Link]));

-

ter_employee_insert
->AFTER INSERT ON Employee
mysql>CREATE TRIGGER af AuditLog(ActionType,Description)
->FOR EACH ROW
->BEGIN INSERT',CONCAT('Inserted employee:',[Link]));
INSERT INTO
-> VALUES('AFTER

BEFORE UPDATE:
mysql>CREATE TRIGGER before_employee_update

->BEFORE UPDATE ON Employee

->FOR EACH ROW
->BEGIN
INSERT INTO AuditLog(ActionType,Description)
>END; V A UE BE RE
。 UPDATE',CONCAT('Changing salary for',[Link],'from
',O L [Link] t o' E Salary));
-

AFTER JPDATF;
 T RIG ER a fte
C REAT
mysql> E G _update
->A FTER UP D A TE ON Emplo
->FOR EACH ROW
->
-

一 >
VALUESC'AFTER UPDATE',CONCAT('Updated employee:',[Link]));

BEFORE DELETE:

T RIGG R b ore
C REATE
mysql> E delete
->B EFORE DELETE O N E plo
->FOR EACH ROW
- BEGIN
>END INSERT INTO AuditLog(ActionType,Description)
> VALUES C'BEFORE DELETE',CONCAT('Deleting employee:',[Link]));
-

DELETE:
mysql> C REATE TRIGGE R a f t e employee_delete
->A FTER DELETE ON Emplo e
->FOR EACH ROW
-> EGIN
>
一 INSERT INTO AuditLog(ActionType,Description)
VALUESC'AFTER DELETE',CONCAT('Deleted employee:',[Link]));
-

->END;
>$$
Query OR,0 rows affected(0.01 sec)
INSERT:
ge,Email,Department,Salary)
mysql>INSERT 29,'[Link]','Development',52888);
Query ok,1 rowINTO Employee(EmpID,Name,A
affected(0.01 sec)

SAMPLE UPDATE:

Employee
mysql>UPDATE
->SET
>W
Salary
Em
=58000
D

Que y HOE E,1 row a

pI

SAMPLF DELETE:

oyee
mysql>DELETE FROM Empl
D
>WHE EmpI =1 ;
Que y O 1 row affecte (0.00
TRIGGER LOGS:
 mysql>SELECT *FROM AuditLog ORDER BY ActionTime DESC;

-------+ ---------------+ ---

Description ActionTime
LogI ActionType
D

十 ----- 一 一
十

Trying to insert employee:Pardhu Seelam

BEFORE 2025-05-
INSERT Inserted employee:Pardhu Seelam [Link]
3 2025-05-
AFTER Changing salary for Pardhu Seelam from 52000.00 to [Link]
4 INSERT 58000.06 2025-05-
[Link]
BEFORE
3 UPDATE Updated employee:Pardhu Seelam 2025-05-
AFTER [Link]
UPDATE Deleting employee:Pardhu Seelam
BEFORE 2025-05-
DELETE Deleted employee:Pardhu Seelam [Link]
AFTER
DELETE 2025-05-
[Link]

6 rows in set(0.00 sec)

CURSOR;
CURSOR PROCEDURE:

个
PROCEDURE ShowEmployeeDetails()

个
个 BEGIN
个
mysql>CREATE
DECLARE done INT DEFAULT FALSE;

DECLARE V_EmpID INT;

个
DECLARE V_Mame VARCHAR(50);

个
个 DECLARE V_Salary DECINAL(10,2);

DECLARE emP_CurSOT CURSOR FOR

个 SELECT EmPID,Name,Salary FROM Employee;

个 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =TRUE;

个
OPEN emp_cursor;

CALLTHE PROCEDURE:
mysql>CALL ShowEmployeeDetails();
|EmployeeDetails
+

+---------------------

|Employee ID:1 |Name:Manoj Reddy |Salary:50000.00

---+
+--------------------
1 row in set(0.00 sec)

+---------------------

|EmployeeDetails
+----------------------- ----------------------

|Employee ID:2 |Name:Santosh Reddy |Salary:48000.00

+ ------------ --———————- ‫ ﮮﮮ‬-

1 row in set(0.01 sec)

+---------------------

I EmployeeDetails
—-——-
+----------------------

|Employee ID:3 |Name:Shiva Mohith |Salary:60000.00

+-------------------- 一 — — — — — —

1 row in set(0.01 sec)

------------------ -
EmployeeDe--ails
-------------- ---- ——— 一

Employee ID:8 |Name:Null Department |Salary:30 000.00 |

+-------------- --—— -—

1 row in set(0.01 sec)

 Ok,0 rows affected(0.02 sec)
Query