Consider the following relational design of a Car Rental Service (the primary key of the relations have been underlined):

Car (car_id, brand, rate, color, year)

Driver (driver_id, driver_name, address, city, phone)

Rental (rental_id, car_id, driver_id, rentaldate, rentedhours)
The relation Car stores the id of the car, brand of the car, per hour rate of the car and the color/year of the car. The relation Driver stores the id, name and address information of each driver registered with the Car rental service. The relation Rental stores the information when a driver rents a car. It stores the id of the car, id of the driver, the date when car is rented and the number of hours the car is rented.

Assume that there are 1,000 different cars, 1,000 drivers, and 1,000 rental records. The maximum rate is 1000 dollars and the minimum rent is 100 dollars.
Part 1

Create three new tables in your MySQL database for this scenario.

https://internalservices.fit.edu/itresources/ (Links to an external site.)

Populate them with junk data from https://www.mockaroo.com/ (Links to an external site.)

Note: To limit the range of numbers, use a number data type and select a range. For primary keys, always use the row number type. For primary keys that are also a foreign key, use the number type within the set range (Rental will need this).

Screenshot your Rental table (click browse)

Part 2

Consider the following two queries on the given relations:

Query 1:

SELECT driver.driver_id, address
FROM car, rental, driver
WHERE car.car_id = rental.car_id
AND driver.driver_id = rental.driver_id
AND city =’Melbourne’ AND brand = ‘Toyota’;
Query 2:

SELECT driver.driver_name, rate
FROM car, rental, driver
WHERE driver.driver_id = rental.driver_id
AND car.car_id = rental.car_id
AND ((brand = ‘Honda’ AND city = ‘Palm Bay’)
OR (brand = ‘Lexus’ AND city = ‘Palm Bay’));
Describe in plain English what each query does.

Update your fake data to include these cities and brands.
Part 3

Now let’s implement these queries in php file. Begin by downloading a sample php file here:
car.phpView in a new window

Upload it to your U drive under the “public_html” folder. To do this, you can access your U drive from cloud.fit.edu remotely or use the windows folders on the lab computers.

Open the car.php in Notepad++.

You will need to change the database information in the php file at the top to match your own at: https://internalservices.fit.edu/itresources/ (Links to an external site.)

Using the SQL provided earlier, define and run both query 1 and query 2 in your php file. You should only need to update the SQL itself and the output formatting.

To view your file, type the URL into a browser window like so:

my.fit.edu/~username/car.php

Replace username with your TRACKS username.
Take a screenshot of your php file output.

Part 4

Let’s perform a speed test analysis. Check out the following file and add its contents to the top of your car.php file

timers.phpView in a new window

Next, call the timer functions in the places you create and run SQL statements. For example:

$sql = “SELECT * FROM car”;
StartTimer ($sql);
$result = mysqli_query($conn, $sql);
StopTimer();
Screenshot the results for both queries.

Now let’s optimize our database. In MySQL, click on each table and under “Operations”, select “Optimize”.

Next, select Structure for your Driver table.
Create an Index on driver_name by selecting “index” on the options to the right next to the keys.
Do the same thing for “city” and “brand”.

Rerun your time analysis and compare results. Include screenshots of both.

Submit all your screenshots, discussion, and analysis in one word doc or pdf file.