Database Problem

Note: Use mysql,vi for scripts, code in System Operative Linux

A car rental company wants to know the status of their fleet at all times. The company has several offices spread throughout PR. Each office is identified by a unique code and is characterized by the city in which it is located and its complete address (street, number, zip code, etc.) and telephone. In each office there is a set of cars available, of which the car liscense plate is known, the group to which it belongs: A, B, C, D, E, F or G (depends on the type and size of the vehicle), the brand, the model, the number of doors, the number of seats, the trunk capacity and the minimum age required for rental. To keep track of the status of each vehicle, the company maintains a record of all the rents that has been done, indicating for each one of them the driver's name, his ID, his address, a contact telephone and credit card number on which to make the corresponding charges. In addition to this customer information, for each rental the duration (in days), the type of insurance contracted and the total price are stored.

a. Create the ERD(Entity-Relationship-Diagraml) b. Create the DB using the server and you must insert at least 5 tuples in each table c. Create two triggers: the first one is the one that you decide and the second one must be a trigger to avoid renting the same car more than once d. Create a procedure with at least one cursor to grant a free rent to a prominent customer, a prominent customer will be one who rents a vehicle 3 or more times a month

Note part b was generated by Visual Paradigm generation database please help for part b,c,d create the script

a)

b)

CREATE TABLE Office (office_ID varchar(50) NOT NULL, office_address varchar(125) NOT NULL, office_telephone varchar(55) NOT NULL, City varchar(55) NOT NULL, State varchar(55) NOT NULL, ZIP_Code varchar(55) NOT NULL, PRIMARY KEY (office_ID)); CREATE TABLE `Rent Register` (register_rentnum varchar(50) NOT NULL, customer_ID varchar(50) NOT NULL, start_date date NOT NULL, end_date date NOT NULL, `total days_rent` date NOT NULL, insurance varchar(55) NOT NULL, total_price varchar(200) NOT NULL, `CarsCar License Plate` varchar(50) NOT NULL, PRIMARY KEY (register_rentnum)); CREATE TABLE Cars (`Car License Plate` varchar(50) NOT NULL, Model varchar(50) NOT NULL, Brand varchar(50) NOT NULL, num_doors int(10) NOT NULL, num_seats int(10) NOT NULL, trunk_capacity int(10) NOT NULL, min_agerent int(10) NOT NULL, office_ID varchar(50) NOT NULL, `Car GroupGroup` varchar(1) NOT NULL, PRIMARY KEY (`Car License Plate`)); CREATE TABLE `Car Group` (`Group` varchar(1) NOT NULL, car_type varchar(55) NOT NULL, car_size varchar(55) NOT NULL, PRIMARY KEY (`Group`)); CREATE TABLE Customer (customer_ID varchar(55) NOT NULL, customer_address varchar(55) NOT NULL, customer_phonenumber varchar(55) NOT NULL, creditcard_num varchar(55) NOT NULL, register_rentnum varchar(50) NOT NULL, PRIMARY KEY (customer_ID)); ALTER TABLE Cars ADD CONSTRAINT FKCars400130 FOREIGN KEY (office_ID) REFERENCES Office (office_ID); ALTER TABLE Customer ADD CONSTRAINT FKCustomer608769 FOREIGN KEY (register_rentnum) REFERENCES `Rent Register` (register_rentnum); ALTER TABLE Cars ADD CONSTRAINT FKCars520079 FOREIGN KEY (`Car GroupGroup`) REFERENCES `Car Group` (`Group`); ALTER TABLE `Rent Register` ADD CONSTRAINT `FKRent Regis484378` FOREIGN KEY (`CarsCar License Plate`) REFERENCES Cars (`Car License Plate`);

Office Car License Plate varchar(50) varchat50) varchart50) integer(10) integer(10) E tnunk capacity integer(10) El min agerent integer(10) varcharr50) Car GroupGroup varcha1) office ID varchar(50) office_address varchan 125) office_telephone varchar(55 varchar(55) varchar(55) varchan(55) E num doors numseats State - ZIP Code office ID Customer Rent Register Car Grou varchar(50) varcharf50) date date date varchan 55) varchan 200) register_rentnum varchar(55) varchar(55) customer phonenumber varchar(55) varchar(55) Group varchar(1) E car type varchan55) E car size varchan55) customer ID custorner ID start cdate customer address end date a ragister rentnum a total days_rent total_price CarsCar License Platie varchar 50) Office Car License Plate varchar(50) varchat50) varchart50) integer(10) integer(10) E tnunk capacity integer(10) El min agerent integer(10) varcharr50) Car GroupGroup varcha1) office ID varchar(50) office_address varchan 125) office_telephone varchar(55 varchar(55) varchar(55) varchan(55) E num doors numseats State - ZIP Code office ID Customer Rent Register Car Grou varchar(50) varcharf50) date date date varchan 55) varchan 200) register_rentnum varchar(55) varchar(55) customer phonenumber varchar(55) varchar(55) Group varchar(1) E car type varchan55) E car size varchan55) customer ID custorner ID start cdate customer address end date a ragister rentnum a total days_rent total_price CarsCar License Platie varchar 50)