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. Do the normalization process b. Create the ERM(Entity-Relationship-Model) c. Create the DB using the server and you must insert at least 5 tuples in each table d. 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 e. 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: This is my Normalization process and EMR plese check they are correct I am missing part c,d and e.

a)

Not Normalize

office_ID

office_address

office_telephone

Car License Plate

Group A

Group B

Group C

Group D

Group E

Group F

Group G

car_type

Model

car_size

Brand

num_doors

num_seats

trunk_capacity

min_agerent

register_rentnum

customer_ID

customer_address

customer_phonenumber

creditcard_num

rent duration (start_date & end_date)

total days_rent

insurance

total_price

First Normalization

Group 1:

office_ID

office_address

office_telephone

Group 2:

office_ID

Car License Plate

Group A

Group B

Group C

Group D

Group E

Group F

Group G

car_type

Model

car_size

Brand

num_doors

num_seats

trunk_capacity

min_agerent

register_rentnum

customer_ID

customer_address

customer_phonenumber

creditcard_num

rent duration (start_date & end_date)

total days_rent

insurance

total_price

Second Normalization

Group 2:

office_ID

Car License Plate

Model

Brand

num_doors

num_seats

trunk_capacity

min_agerent

Group 3:

Car License Plate

car_type

car_size

Group A

Group B

Group C

Group D

Group E

Group F

Group G

register_rentnum

customer_ID

customer_address

customer_phonenumber

creditcard_num

rent duration (start_date & end_date)

total days_rent

insurance

total_price

Third Normalization

Group 3:

Car License Plate

car_type

car_size

Group A

Group B

Group C

Group D

Group E

Group F

Group G

Group 4:

car_type

register_rentnum

customer_ID

rent duration (start_date & end_date)

total days_rent

insurance

total_price

Group 5:

register_rentnum

customer_ID

customer_address

customer_phonenumber

creditcard_num

EMR:

office_ID office_address office_telephone A City A State ZIP_Code varchar(50) varchar(50) varchar56) varchant 56) varchar(56) varchar(55) i Car License Platevarchar(50) 8 Model varchar(50) Brand varchar(50) A num_doors Integer(10) A num_seats integer(10) trunk_capacity integer(10) min_agerent integer(10) ni office_ID varchan 50) .- od Cars Car Groups Cars Car License Platevarchar(50) Car Groupscar_type varchar(55) Customer Y customer_ID varchar(55) A customer_address varchar(55) customer_phonenumber varchar(55) creditcard_num varchar(55) in register_rentrum varchar(50) Rent Register varchar(50) varchar50) date date register_rentnum A customer_ID start_date end date total days_rent insurance total_price Acar_type date varchar(55) varchar(200) varchar(55) car_type car_size GroupA Group B Group C Group D Group E GroupF Group G Car License Plate Car Groups varchar(55) varchar(55) varchar(55) varchar(55) varchar(55) varchar(55) varchar(55) varchar(255) varchar(55) varchar(55) office_ID office_address office_telephone A City A State ZIP_Code varchar(50) varchar(50) varchar56) varchant 56) varchar(56) varchar(55) i Car License Platevarchar(50) 8 Model varchar(50) Brand varchar(50) A num_doors Integer(10) A num_seats integer(10) trunk_capacity integer(10) min_agerent integer(10) ni office_ID varchan 50) .- od Cars Car Groups Cars Car License Platevarchar(50) Car Groupscar_type varchar(55) Customer Y customer_ID varchar(55) A customer_address varchar(55) customer_phonenumber varchar(55) creditcard_num varchar(55) in register_rentrum varchar(50) Rent Register varchar(50) varchar50) date date register_rentnum A customer_ID start_date end date total days_rent insurance total_price Acar_type date varchar(55) varchar(200) varchar(55) car_type car_size GroupA Group B Group C Group D Group E GroupF Group G Car License Plate Car Groups varchar(55) varchar(55) varchar(55) varchar(55) varchar(55) varchar(55) varchar(55) varchar(255) varchar(55) varchar(55)