1. Your first task is to study the sample data and determine appropriate data definitions. Check that the spreadsheet data has been normalized to third normal form. Study the partial ERD on the first sheet that provides a suggested schema.
The file has various other worksheets including:
2. Using MySQL, you are required to develop a demonstration prototype system that handles vehicle servicing. Use MySQL to create a new database called ABC. Create tables according to your ERD. Follow a standard naming convention for table names and also field names. Avoid using spaces and any special characters in table and also field names. Use underscore_case or use camelCase to separate parts of a name.
3. Create relationships between tables and enforce the referential integrity as shown below (7 Marks)
• Customers can have one or more rental.
• Customers are identified by the source of business and also the status of the customer file.
• Each rental can have one vehicle and also many other charges associated with it.
• Each vehicle can be serviced one or more times.
• Each service is taken care of by a particular supplier.
• Each customer record may require one or more notes so as to keep a history of information related to the customer record.
• Notes may be assigned to a particular staff person to follow up.
• Each rental may have one or many payments and each payment is identified as to the payment method.
4. The database should include suitable validation and integrity checks as well as appropriate referential integrity checks. That is, AS A MINIMUM, your system should ensure that the following events cannot occur:
Referential Integrity Constraints:
• A rental record cannot be booked for a customer that does not exist.
• A customer cannot be deleted for which a car rental has been recorded. Similarly a customer cannot be deleted once notes have been entered for the customer record. Likewise, staff cannot be deleted once staff persons have been assigned to follow up a note.
• A rental cannot be deleted once the rental has rental details associated with it. Similarly a charge cannot be deleted once the charge item has been entered against any rental detail record.
• Only charges that have been entered into the charges table may be entered as charges for rental details.
• Vehicles cannot be deleted once vehicles have been assigned to a rental record or have been assigned to a service record in the database.
• Payment methods cannot be deleted once payment methods have been recorded against payments and rentals that have matching payment details cannot be deleted once payment records have been entered.
• A status record may not be deleted once the status has been recorded as the status of the customer file in the customer table.
5. ERD and sample data given for your reference. Add atleast 4 records in each table from given sample data. (3 Marks)
2. Part B (26 marks)
Use the ABC Car Rental database (ABC) database that you created in MySQL to design and execute SQL queries that answer the following questions.
Number your answers to each question clearly. The answer to each question must be tabulated as shown in the example below and include the SQL statement and also the output that is produced when you execute the statement in your database. The output includes the records that are listed and also the message that appears when you run the SQL statement.
SQL: SELECT lName, position
WHERE salary > 20000;
| lName | position |
| Brand | Manager |
| White | Manager |
2 rows in set (0.03 sec)
Do not use screen captures to display the SQL statement or the output.
You should right-click on the MySQL Command Prompt window; choose Mark and then press the [Enter] key to Copy and then Paste into your Word document that includes the answers to all questions.
Format and indent the clauses in your SQL statements for better readability and understanding as shown in the example above. Statements must be syntactically and semantically correct.
Format both the SQL and also the Output in Courier New
10 or 11 point.
Each question is 2 marks.
1. List the first name, last name of customers (join customer first and last name with a space in between and use the alias Customer Name for the column heading), and source of business where the source of business is ‘Private Rental’. Sort the output in ascending order by the customer last name.
2. List the vehicle make, model, registration no, service required description, service due date, and service date for all vehicles that service records where the service date has not been entered and the service due date is less than today’s date.
3. List the total amount owing for each customer grouped by customer Id and customer last name. Sort the output in descending order by the total amount owning. Note that this query does not need to take payments into account.
4. List the total payment amount grouped by payment method for payments made by Visa or MasterCard.
5. List the customer last name, mobile, and email for all customers that do not have a licence number recorded in the customers table. Sort the output in descending order by the date received.
6. List the last name, suburb, and post code for all customers that have a suburb that has the word ‘hill’ anywhere in the suburb. Sort the output ascending order by the post code.
7. List the staff first name, surname, and remarks from the notes table for all notes that have a follow up date before today’s date and where the complete field has a value of ‘false’.
8. List the vehicle make, model, registration number, and odometer in, odometer out, and calculate the total kilometres by subtracting the odometer in minus the odometer out (display the result of the calculation as Total Kms). Sort in ascending order by the registration number.
9. Count the number of customers that have a file status of ‘Closed’.
10. List the last name for all customers who have rented a vehicle but not paid in full. Calculate the amount owing. Sort the output in ascending order by the customer last name. Hint: You will have to create a number of queries to calculate (i) the amount owning, (ii) the amount paid, and (iii) the amount owing. Use the first two queries as inputs for the third query.
3. Part C (6 marks)
1. Write a page that describes your experience building the database. You can discuss any challenges / difficulties that you experienced or solutions that you found. Comment on any limitations and / or strengths of your database design. Comment on whether your database meets all the system requirements as specified in Part A Question 4. Include an acknowledgement of all students you have spoken to about the assignment. (6 marks)
4. Part D (4 marks)
2. The SQL that can be used to restore your database should also be uploaded to AIH Moodle. You can create the SQL for your database as follows:
Use the mysqldump command to create a text version of the database. Use mysqldump to create SQL file that contains a list of SQL statements which can be used to restore/recreate the original database.