Project's status: finished ✔️
This project's main purpose is to put into practice SQL structures such as triggers and views, by taking a fictitious car dealearship case whose relational database schemas that must be followed are sketched with the designing tool Dia. To spice things up, all the SQL statements are created and run in the MySQL Command-Line and the resulting database is exported as a SQL file with HeidiSQL.
1. Dia
2. HeidiSQL
3. MySQL
-
The table names are in plural and lowercase.
E.g: users.
-
The column names are fully human-readable, in order to make that possible the names are in singular and use camelCase naming practice followed by an underscore and then the first three letters of the table's name where each column belongs to.
E.g: userName_use. This is a column that belongs to a table called "users".
-
The primary key column names use as default name "ID" followed by an underscore and then the first three letters of the table's name where each primary key column belongs to.
E.g.: ID_use. This is a primary key column that belongs to a table called "users".
The 1st schema shows the tables that are in the database and how they are related to each other through one-to-many relationships.
The 2nd schema show the tables thar are not related with each other in the database, these have as main function to backup all the data from the related tables.
The 01_backup_trigger_cars is a trigger that makes a copy of the record from the cars table in the cars_backup table before it is deleted.
DELIMITER //
CREATE TRIGGER 01_backup_trigger_cars BEFORE DELETE ON cars FOR EACH ROW
BEGIN
INSERT INTO cars_backup VALUES(
old.id_car, old.model_car, old.manufacturer_car, old.price_car, old.stock_car
);
END//
DELIMITER ;The 02_backup_trigger_orders is a trigger that makes a copy of the record from the orders table in the orders_backup table before it is deleted.
DELIMITER //
CREATE TRIGGER 02_backup_trigger_orders BEFORE DELETE ON orders FOR EACH ROW
BEGIN
INSERT INTO orders_backup VALUES(
old.id_ord, old.id_cli, old.id_car, old.quantity_ord, old.date_ord
);
END//
DELIMITER ;The 03_backup_trigger_clients is a trigger that makes a copy of the record from the clients table in the clients_backup table before it is deleted.
DELIMITER //
CREATE TRIGGER 03_backup_trigger_clients BEFORE DELETE ON clients FOR EACH ROW
BEGIN
INSERT INTO clients_backup VALUES(
old.id_cli, old.id_sel, old.name_cli, old.city_cli, old.payment_cli
);
END//
DELIMITER ;The 04_backup_trigger_sellers is a trigger that makes a copy of the record from the sellers table in the sellers_backup table before it is deleted.
DELIMITER //
CREATE TRIGGER 04_backup_trigger_sellers BEFORE DELETE ON sellers FOR EACH ROW
BEGIN
INSERT INTO sellers_backup VALUES(
old.id_sel, old.id_gro, old.name_sel, old.surname_sel, old.charge_sel, old.salary_sel, old.commissionForSales_sel, old.manager_sel, old.hiringDate_sel
);
END//
DELIMITER ;The 05_backup_trigger_groups is a trigger that makes a copy of the record from the groups table in the groups_backup table before it is deleted.
DELIMITER //
CREATE TRIGGER 05_backup_trigger_groups BEFORE DELETE ON groups FOR EACH ROW
BEGIN
INSERT INTO groups_backup VALUES(
old.id_gro, old.name_gro, old.country_gro
);
END//
DELIMITER ;The stop_order_making_trigger is a trigger that prevents making an order if the car model is out of stock, at the same time it shows an error message.
DELIMITER //
CREATE TRIGGER stop_order_making_trigger BEFORE INSERT ON orders FOR EACH ROW
BEGIN
SET @stock = (SELECT stock_car FROM cars WHERE id_car = new.id_car);
IF @stock = 0 THEN signal sqlstate '45000' SET message_text = 'You can\'t order this car because its stock is empty, please try other model';
END IF;
END//
DELIMITER ;The update_stock_trigger is a trigger that updates the quantity available in stock of a car model once an order is made.
DELIMITER //
CREATE TRIGGER update_stock_trigger BEFORE INSERT ON orders FOR EACH ROW
BEGIN
UPDATE cars SET cars.stock_car = cars.stock_car - new.quantity_ord WHERE cars.id_car = new.id_car;
END//
DELIMITER ;The bills_view shows the basic bill information using the data stored in the cars, clients and orders tables
DROP TABLE IF EXISTS `bills_view`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `bills_view` AS select `o`.`id_ord` AS `Order's ID`,`o`.`quantity_ord` AS `Units`,
`o`.`date_ord` AS `Date of Purchase`,`k`.`name_cli` AS `Client's Full Name`,`c`.`model_car` AS `Car's Model`,`c`.`manufacturer_car` AS `Car's Manufacturer`,
`c`.`price_car` AS `Car's Price`,`k`.`payment_cli` AS `Client's Payment` from ((`orders` `o` join `cars` `c` on((`o`.`id_car` = `c`.`id_car`))) join `clients` `k` on((`o`.`id_cli` = `k`.`id_cli`)));
Copyright © 2021 David Lozada