Case: Please use the following case to answer Q1 and Q2 in this assignment: Rapidity is a scooter rental company who offers rental scooters to its customers in Toronto. They have several scooters rental offices where scooters can be parked and found in these stations. . Each scooter is identifiable with their brand, registry number, 5digit-ID (format: AA111), date of purchase, and date of production. Each rental office is described by its geographical location (i.e., the street name, longitude, and latitude), and a unique 4-digit ID (format: 1111). To handle the operations, different departments are involved in this process, such as accounting, operation, and scooter management. In each department there could be no employees (automated) to 10 employees. If there are not any scooters in the rental office, or there are damaged scooters in the rental office, the scooter management department will replace scooters and repair the damaged ones. There are two types of users/customers (unregistered, and registered users) and only registered users can request a scooter. Registered users should provide their personal information and their payment information. There are two types of rental requests: members rental requests and one-time rental (i.e., unregistered users) requests. members can take a scooter for free, limited to 40 minutes, and for more than 40 minutes, they should pay an extra fee of 2$ per hour. For one-time rental requests (i.e., unregistered users), initially, they should pay 80$ as a deposit and their hourly rate begins from the moment of usage which is 2$ per hour. After returning the scooter, the deposit will be returned to them. o For membership request, users must buy a monthly membership for 38 dollars. They can also buy a family membership (membership for up to 4 people) for 90 dollars. No customer can use a scooter for more than 90 hours in one rental request. For each usage, the rental office keeps track of the date of request, the date the scooter is returned, scooter information, customer information (e.g., name, address, telephone number), and the rental total charged amount. c. Implement the ERD using Microsoft Access 2010/2013/2016/2019 or LibreOffice (20 marks) Microsoft Access is installed in the SCBTM computer labs. LibreOffice can be downloaded from the link below (Windows or Mac): https://www.libreoffice.org/download/download/ i. Using the ERD and data dictionary that you created previously, complete the following steps: Implement the ERD and data dictionary in Microsoft Access 2010/2013/2016/2019 or LibreOffice. The Microsoft Access Database should have all the tables, attributes, and relationships defined in your ERD and data dictionary. You should make sure that all the primary keys and foreign keys are defined (7 marks). The videos below can help you to achieve this for Microsoft Access: Table creation: http://youtu.be/hd7XpwIBOKO . Relationships creation: http://www.youtube.com/watch?v=xjhuhZ120Ho The PowerPoint for the video tutorials above is named "AccessTutorial.pptx" and included with this assignment. The videos below can help you to achieve this for LibreOffice: . 3 Case study: https://www.youtube.com/watch?v=au0KS- VOFPM&feature=youtu.be Table and relationships creation: https://youtu.be/5GxGbc0Flik ii. iii. Case study: https://www.youtube.com/watch?v=au0KS- VOFPM&feature=youtu.be Table and relationships creation: https://youtu.be/5GxGbc0Fljk iv. Create 5 records in each table of your Microsoft Access Database. Make sure to create the records for the parent tables first (the ones with the one side), otherwise you might run into referential integrity issues (5 marks). The video below can help to achieve this for Microsoft Access: http://www.youtube.com/watch?v=frsmRm7z7F0&feature=youtu.be The video below can help to achieve this for LibreOffice: Records creation: https://youtu.be/5GxGbc0Flik Create 2 queries in Microsoft Access that a manager could answer using this database. Your queries must each use of at least two tables (4 marks). You can watch the two tutorials below that show how to create queries in Microsoft Access. Query example 1: http://youtu.be/1rdikLxteGq Query example 2: http://www.youtube.com/watch?v=iRTTujmQkYw The video below can help to achieve this for LibreOffice: Query creation: https://youtu.be/t65om-TSbJU Create 2 reports by using the queries designed in step 3 (4 marks). You can watch the tutorial below for Microsoft Access that shows how to create reports based on the queries of the previous step. http://youtu.be/qx2yJoTsaqU The video below can help to achieve this for LibreOffice: Report creation: https://youtu.be/CbAHngcs Twc Rental Request rental_req_nb Department Membership Employee rent total charged rental_request_date date_scoot_returned rental_tot_amount scooter id time_limit extra fee deposit pay hourly rate department_nb department_name department office id membership_plan_nb descrip_amt type_of_plan employee ID employee name employee phone nb Employee_email_id employee_salary employee department_id request number rent total charged request date return date rental total amount scooter ID rental time limit rental extra fee rental deposit pay Irental hourly rate membership plan number description amount type of plan employee number name of employee phone number of employee email ID of employee salary of employee employee department ID int decimal date date int int decimal decimal int int int varchar int int varchar yyyy-mm-dd yyyy-mm-dd varchar,decimal999999.XXXXXX int varchar int varchar decimal int XXXXXX XXXXXX XXXXX 99999 N/A 99999 N/A N/A N/A 99999 N/A 99999 N/A 99999 N/A 99999 N/A 99999 N/A N/A 999999 N/A |N/A 999999 N/A N/A N/A 999999 N/A N/A N/A 99999 N/A N/A 9999 N/A 9999 N/A 9999 N/A 9999 N/A TY Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y N Y Y PK,FK,FK,FK User, scooter, rental office PK,FK PK,FK Customer department Table Name USER Scooters ALAR Rental Office Atribute Name USER_ID USER_NAME USER_ADDRESS Registered User Credit_card_num USER_TEL_NO cred_card_exp_date office_id scooter_id scooter_brand registry_num date_of_purch date of product office_id street_name office longitude office_latitude Rental Request rental_req_nb rent total charged rental_request date. |date_scoot_returned rental_tot_amount scooter id time_limit extra_fee deposit pay hourly rate ALIGN Contents user unique ID Username User address User phone number credit card number credit card expiry date office ID scooter ID scooter brand scooter registry number purchase date date of production office number address of the office longitude of office latitude of office request number rent total charged request date return date rental total amount scooter ID rental time limit rental extra fee rental deposit pay Irental hourly rate ukili.. Type int varchar varchar int int date int int varchar int date date int varchar decimal decimal int decimal date date int int decimal decimal int int Format XXXXXXXXXX XXXXXXXXXX yyyy-mm-dd XXXXXXXXXX XXXXXXXXXX yyyy-mm-dd yyyy-mm-dd XXXXXX yyyy-mm-dd yyyy-mm-dd Range 99999999 N/A N/A N/A 999999999 N/A 999999999 N/A N/A 99999 N/A N/A N/A N/A N/A 999999 N/A 99999 N/A N/A 999999 N/A 999999 N/A 99999 N/A 99999 N/A N/A N/A 99999 N/A 99999 N/A 99999 N/A 99999 N/A 99999 N/A N/A Required Y Y Y Y Y Y TY T Y Y Y Y Y |Y Y N N Y Y Y Y Y Y Y Y Y H PK or FK FK Referenced Table PK,FK, FK, FK Rental Office PK,FK, FK, FK User, scooter, rental office Question a! Membership Membership plan number (PK) description ameyattues Registered user Request Time limit One time request Deposit pay hourly rate User User ID (PK) name, address, telephone number Department Rental Request Request number (PK) Request date Return date Rental total charged amount Department pb (PK) name Registered User Credit card number Unregistered User Employee Emp. No (PK) name, phone n