AIH – Higher Education ISY103 – Database for Business Teaching Period Semester 1, 2016 – Weighting 20% Individual Project: Database Case Study Aim: To give you practical experience in with database modelling, normalization and writing SQL statements. Background Information ABC Car Rentals will be starting operations in 2017. They require a database system to cater for the rentals of their vehicles. When a person rings up or comes into the office of ABC to book a vehicle for the first time they are asked to provide their name, address, and telephone number. The vehicle date out, time out, odometer reading, and fuel out is entered into the system when a vehicle is rented by a customer. When the vehicle is returned then the date in, time in, odometer reading, and fuel in is entered. The number of days is calculated and the customer is billed for the car rental plus additional charges such as cleaning, petrol, a fuel refilling fee, damage to hire car, toll fees, smoking fee, and administration charge. Once the rental is complete and details about the rental have been entered then the customer is invoiced for the hire of the vehicle. Payments are recorded for each rental record. Payments are expected to be in full. However, the system allows multiple payments to be recorded against each rental record. The database system also needs to keep a record of vehicle servicing which includes the last service date, a service description, and the next service due date. Assumptions Some charges have a fixed cost. However, the charge amount may be adjusted up or down when charges are entered against a rental record. Total charges are calculated by the system by multiplying the charge amount (price) by the quantity. System Requirements The system is a prototype system and as such is not a full production version. You will be required to enter a representative sample data into your tables in order to test the design and operation of your database. You are required to import the sample data provided into your tables and you are required to enter at least two new records of your own in some of the tables. Project Specification 1. Part A (20 marks) You are provided an Excel file that contains a partial ERD, suggested table definition, and also some sample data. See ABCRentalsData.xlsx Use the Excel workbook file ABCRentalsData.xlsx to perform the following tasks. 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: • Claims • SourceBusiness • Status • Rentals • RentalDetails • Charges • PaymentMethods • Part • Vehicles • Service • Suppliers • Notes • Staff 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. (10 marks) 3. Create relationships between tables and enforce the referential integrity as shown below (7 Marks) Relationships: • 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. For example: Question 1: SQL: SELECT lName, position FROM Staff WHERE salary > 20000; Output: +-------+----------+ | 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. The syntax is as follows: $ mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql] WHERE: [uname] Your database username [pass] The password for your database [dbname] The name of your database [backupfile.sql] The filename for your database backup You do not need to log on to MySQL. For example from the DOS command prompt, the syntax is: e:\xampp\mysql\bin>mysqldump -u root MyDB > e:\MyDB_bak.sql This will create the SQL file that can be used to restore your database.   5. Submission a. When to submit Refer to the Course Description for due date, extensions and late policy. b. What to submit Part A (1) Include in your documentation the ERD that you created using Word, Visio, or other software. Copy and paste your diagram into your Word document as illustrated by the following example: Orders Database ERD: (2) Title your ERD ABC Rental ERD.   (2) Use the DESCRIBE command to describe the structure of each and every one of your tables in your WC database. Do not use screen captures. The following is an example of how each table should be documented: Describe Branch; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | branchNo | char(4) | NO | PRI | NULL | | | street | varchar(16) | YES | | NULL | | | city | varchar(10) | YES | | NULL | | | postcode | char(8) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+   (3) Show the first five records in each of your tables as shown by the example below: SELECT * FROM Branch; +----------+--------------+----------+----------+ | branchNo | street | city | postcode | +----------+--------------+----------+----------+ | B002 | 56 Clover Dr | London | NW10 6EU | | B003 | 163 Main St | Glasgow | G11 9QX | | B004 | 32 Manse Rd | Bristol | BS99 1NZ | | B005 | 22 Deer Rd | London | SW1 4EH | | B007 | 16 Argyll St | Aberdeen | AB2 3SU | +----------+--------------+----------+----------+ (4) Also print the records that you have added to the tables that were specified. (5) Use Courier New 8 or 9 point to ensure that the columns fit between the margins. Columns should not wrap if this can be avoided.   Part B (1) The answer to each question must be clearly numbered and tabulated as shown in the example below. Question 1: SQL: SELECT lName, position FROM Staff WHERE salary > 20000; Output: +-------+----------+ | lName | position | +-------+----------+ | Brand | Manager | | White | Manager | +-------+----------+ 2 rows in set (0.03 sec) • Add a heading immediately above the table to identify the question number. • Include both 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. • Do not use screen captures to display the SQL statement or the output. Copy the text of your SQL statement and also the output into the table. • Edit the keywords of your SQL statement (if necessary) to appear in uppercase as shown by the example above. Field names and table names should be entered in upper or lowercase as they appear in the database schema. • 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 or smaller in order to avoid wrapping. • Marks will be deducted where students fail to follow the requirements given above. Part C (1) Include your personal report as per instructions given.   Administrative Details PLAGIARISM AND COLLUSION Any submitted work that contains plagiarism or collusion will be reported to the appropriate authorities according to the University policy and will receive an automatic zero '0' mark for the assignment. ISY103 Database for Business Student Name: _______________________________ Student ID#: ___________ I acknowledge that this project is my own work, that there is no collusion with other students, past or present, and that any work used from another source has been correctly cited and referenced (using the Harvard style) as per the University handbook. Signed: _________________________________ Date: ____________________ Marking Guide for Assignment 2 PART MARKS A: ER Diagram (entities, attributes, relationships, keys) ______ / 10 Schema matches ERD Database Design (appropriate data types, field size), Data Integrity (keys), Constraints (FKs) _______ / 7 Sufficient data / + additional records shown _______ / 3 B: SQL & Output Q1 /2 Q2 /2 Q3 /2 Q4 /2 Q5 /2 Q6 /2 Q7 /2 Q8 /2 Q9 /2 Q10 /2 ______ / 20 C: Personal Report _______ / 6 D: Report Layout & Presentation _______ / 4 Late Penalties 10% each day late Total: _______ / 50 Comments: End of the document