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: registered users' rental requests and onetime rental (i.e., unregistered users) requests. Registered users 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.
• 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.

Q1: Data Management: Entity Relationship Diagram & MS Access

a, Draw the ERD of the database that could be used to support the operations of the business described in the case (20 marks).

The ERD should identify:

• Primary keys and Foreign keys;
• Entities and at least three Attributes for each entity;
• Relationships and Cardinalities (e.g., one-to-one, one-to-many).

b, Create the data dictionary for your database (10 marks). The template is given with this

(datadictionaryTemplate.xls)

c, Implement the ERD using Microsoft Access 2010/2013/2016/2019 or LibreOffice (20 marks)

Table Name Atribute Name Contents Type Format Range Required PK or FK | FK Referenced table CUSTOMER cust_id Customer account number AutoNumber 999999999) PK cust_Iname Customer last name Short Text(25) XXXXXXXXXX cust_fname Customer first name Short Text(25 cust_Ibilldate Customer last billing date Date/Time Yyyy-mm-dd cust_balance Customer account balance Currency 9999,95 cust_address Customer mailing address Short Text(50 XXXXXXXXXX Y cust_email Customer email address Short Text(50 XXXXXXXXXX cust_status Customer service status Short Text(8) xxxxxx 'active' or "inactive'or 'deleted cust_surveydate Customer survey submission date Date/Time YYyy-mm-dd chan_num Favorite channel Number(Byte) 99 FK CHANNEL CHANNEL chan_num Channel Number Number(Byte) 99 PK chantype_id Channel Type ID Number(Byte) 99 TY FK CHANNELTYPE CHANNELTYPE chantype_id Channel Type ID Number(Byte 991 PK chantype_name |Channel Type Name Short Text(50 XXXXXXXXXX PACKAGE pkg_ic Package ID Number(Byte) 99 PK pkg_name Package name Short Text(25 XXXXXXXXXX pkg_cost Package cost Currency 9999,99 SUBSCRIPTION cust_id Customer account number AutoNumber 399999999 PK, FI CUSTOMER pkg_id Package ID Number(Byte 99 PK, FK PACKAGE PACKAGE CHANN chan_num Channel number Number(Byte 99 PK, FK CHANNEL pkg_id Package IC Number(Byte 99 PK, FK PACKAGE PROGRAM pgm_id Program ID Number(Byte 99 PK pgm_name Program Name Short Text(25) XXXXXXXXXXX pgm_desc Program Description Short Text(50) XXXXXXXXXX Ty pgm_lentgh Program time length in minutes Number(Integer 999 Y chantype_id Channel Type ID Number(Byte 99 FK CHANNELTYPE rtg_cod Rating Code Number(Byte) 99 FK RATING supp_id Supplier ID Number(Byte) 99 FK SUPPLIER RATING rtg_code Rating Code Short Text(2) xx PK rtg_desc Rating Description Short Text(50) (XXXXXxxxx SUPPLIER supp_id Supplier ID AutoNumber 999999999 PK supp_name Supplier Name Short Text(50 XXXXXXXXXx supp_address Supplier mailing address Short Text(50 XXXXXXXXXX supp_contact Supplier Contact Name Short Text(50 XXXXXXXXXX supp_phone Supplier Phone Number Short Text(10) 1999999999 SCHEDULE chan_num Channel Number Number(Byte) 99 PK, FK CHANNEL sch_date Scheduled program date Date/Time Yyyy-mm-dd hh:mm PK pgm_ic Program ID Number(Byte) 99 FK PROGRAM AGENT agent_ic User ID AutoNumber 399999999 Ty PK agent_name Username Short Text(50 XXXXXXXXXX Y ACCOUNT MOD mod_i Modification ID Number(Integer 9999 PK mod_date Modification date Date/Time YYYy-mm-dd mod_type Modification type Short Text(6) XXXXXX "NEW' or 'ADD' or 'SUBSTR' or "DELETE' mod_notes Account modification notes Short Text(50) XXXXXXXXXx agent_id User ID Number(Integer) 9999 USER cust_id FK Customer account number Number(Long Integer) 999999999 FK CUSTOMER pkg_ic Package ID Number(Byte 99 Y FK PACKAGE