Question : ( Car Insurance Management Sytem SQL Codes )
Consider the following schemas for “car_insurance” database relations, where the primary keys are underlined.
Person (driver id, name, address) here driver id is primary key .
Car(license, model, year) here license is primary key .
Accident(report number, date, location) here report number is primary key .
Owns(driver-id, license) here driver-id, license are primary key .
Participatd (driver-id, car, report-number, damage amount) here driver-id, car, report-number are primary key .
Write down the SQL expressions for the following queries:
i. Add a new accident to the database (assume any values for required attributes).
ii. Delete the Toyota belonging to “Simanto”. ( please feed the database in this way so that this query result don’t show total_owners is 0 . Feed the database in this way so that it show total_owners: 2 )
iii. Find the total number of people who owned cars that were involved in accidents in 2012. ( feed the database in this way that this query result should be 3 )
iv. Update the damage amount for the car with license number “DHAKA2000” in the accident with report number “AR2197” to 50000/-
Answer :
Database Creation:
CREATE DATABASE car_insurance;
Table Creation:
USE car_insurance;
CREATE TABLE Person (
driver_id INT PRIMARY KEY,
name VARCHAR(100),
address VARCHAR(100)
);
CREATE TABLE Car (
license VARCHAR(10) PRIMARY KEY,
model VARCHAR(100),
year INT
);
CREATE TABLE Accident (
report_number VARCHAR(10) PRIMARY KEY,
date DATE,
location VARCHAR(100)
);
CREATE TABLE Owns (
driver_id INT,
license VARCHAR(10),
PRIMARY KEY (driver_id, license),
FOREIGN KEY (driver_id) REFERENCES Person(driver_id),
FOREIGN KEY (license) REFERENCES Car(license)
);
CREATE TABLE Participatd (
driver_id INT,
car VARCHAR(10),
report_number VARCHAR(10),
damage_amount DECIMAL(10, 2),
PRIMARY KEY (driver_id, car, report_number),
FOREIGN KEY (driver_id) REFERENCES Person(driver_id),
FOREIGN KEY (car) REFERENCES Car(license),
FOREIGN KEY (report_number) REFERENCES Accident(report_number)
);
Data Insertion:
INSERT INTO Person (driver_id, name, address)
VALUES
(1, ‘Simanto’, ‘123 Main St’),
(2, ‘John Doe’, ‘456 Elm St’),
(3, ‘Jane Smith’, ‘789 Oak St’);
INSERT INTO Car (license, model, year)
VALUES
(‘DHAKA1000’, ‘Toyota’, 2010),
(‘DHAKA2000’, ‘Toyota’, 2012),
(‘DHAKA3000’, ‘Honda’, 2015);
INSERT INTO Accident (report_number, date, location)
VALUES
(‘AR2197’, ‘2012-05-15’, ‘City A’),
(‘AR2198’, ‘2012-07-10’, ‘City B’),
(‘AR2199’, ‘2012-09-20’, ‘City C’);
INSERT INTO Owns (driver_id, license)
VALUES
(1, ‘DHAKA1000’),
(2, ‘DHAKA2000’),
(3, ‘DHAKA3000’);
INSERT INTO Participatd (driver_id, car, report_number, damage_amount)
VALUES
(1, ‘DHAKA1000’, ‘AR2198’, 1000.00),
(2, ‘DHAKA2000’, ‘AR2197’, 2000.00),
(3, ‘DHAKA3000’, ‘AR2199’, 3000.00);
Queries: i. Add a new accident to the database:
INSERT INTO Accident (report_number, date, location)
VALUES (‘AR1234’, ‘2023-06-11’, ‘City X’);
ii. Delete the Toyota belonging to “Simanto”:
— Delete records from Participatd table
DELETE FROM Participatd
WHERE car = ‘DHAKA2000’;
— Delete records from Owns table
DELETE FROM Owns
WHERE license = ‘DHAKA2000’;
— Delete record from Car table
DELETE FROM Car
WHERE license = ‘DHAKA2000’;
iii. Find the total number of people who owned cars that were involved in accidents in 2012:
SELECT COUNT(DISTINCT p.driver_id) AS total_owners
FROM Person p
JOIN Owns o ON p.driver_id = o.driver_id
JOIN Participatd pa ON o.driver_id = pa.driver_id
JOIN Accident a ON pa.report_number = a.report_number
WHERE YEAR(a.date) = 2012;
iv. Update the damage amount for the car with license number “DHAKA2000” in the accident with report number “AR2197” to 50000/-:
UPDATE Participatd
SET damage_amount = 50000.00
WHERE car = ‘DHAKA2000’ AND report_number = ‘AR2197’;
################################
Getting the data back after performing the query number (ii)
— Insert records into Car table
INSERT INTO Car (license, model, year)
VALUES (‘DHAKA2000’, ‘Toyota’, 2012);
— Insert records into Owns table
INSERT INTO Owns (driver_id, license)
VALUES (2, ‘DHAKA2000’);
— Insert records into Participatd table
INSERT INTO Participatd (driver_id, car, report_number, damage_amount)
VALUES (2, ‘DHAKA2000’, ‘AR2197’, 2000.00);
If you want more help about any topics of the CSE department feel free to contact us at any time .
Contact Information :
Email : aricholimited@gmail.com
Whatsapp : 008801914201270
Skype : palashmalakar
Mobile: +8801940333000 , +8801841501434 , +8801841201271 , +8801841201270 and +8801914-201270
Website : https://www.aricho.net and https://www.arichoit.com
Address : 29 , Shapla Clinic Building ( 2nd floor ) , Sher-A-Bangla Road , Moylapota Mor , Khulna, Khulna, Bangladesh