This assessment is a 2-students group activity and students are required to work with their respective group members. No individual submission will be accepted. Only one of the group members should submit assignment files with both members’ names and IDs written on the front cover.
You will not receive any marks for this assignment if your group member reports against you for nonparticipation or non-cooperation. You must nominate someone as your group leader to coordinate the assignment submission.
Read the description of Favourite Bike Rentals (FBR) and complete the tasks specified at the end.
Favourite Bike Rentals is a small family owner business located in Rural NSW. Tourists regularly visit the area to admire the Countryside and Cycle around the area. The main business of Favourite Bike Rentals is for -acquire bikes that customer (mainly tourists) can hire -maintain bikes to ensure their good working condition, and -to sell bikes that are no longer suitable for hiring.
You have been appointed by FBR to design and implement a database that helps them in managing and tracking their operations and entities’ data efficiently, with the following business description provided. Some details that are necessary for your tasks but not provided should be listed as assumptions.
Every bike has a bike record and is identified by a unique number. For each bike the model, manufacturer, and type (e.g., Mountain or Road) is recorded along with the size (e.g., infant, child, teenager, adult, etc.). After a bike reaches the end of its lifespan (typically three years but depends on its usage), it is sold off to a dealer. The price it is sold for and the date are also recorded in the bike’s record.
FBR have several dealers who buy bikes on a regular basis. FBR maintain a list of dealers and their contact information. A dealer may or may not purchase a bike depending on its condition. If a bike is in poor condition then it is not sold and just scrapped.
Each bike is associated with a class size code which is used to determine standard rates for the period of hire (half day or full day). Usual class sizes are: infant, Child_young, Child_old, Teenager, Standard_ Adult and Large_ Adult. For each class size a unique code is assigned along with the half day and full day rates.
After a bike has been returned, it is checked to ensure that it is still in good condition. If a fault is noticed it is recorded in the Bike’s maintenance log with its description, repair action taken and the date of repairs. For example, a recurring problem is that tyres worn out and are to be replaced. Over a bike’s lifespan, multiple repairs may be necessary but some bikes may never need maintenance.
Kent Institute Australia Pty. Ltd. Assessment Brief ABN 49 003 577 302 CRICOS Code: 00161E RTO Code: 90458 Version 1: 22nd December, 2016 TEQSA Provider Number: PRV12051
Customers make requests to hire bikes by either telephoning FBR or by simply walking into the shop. If a customer agrees to hire a bike (s) then their name, address and contact details are recorded. A customer can rent one or more bikes at a given time.
For each bike that is rented a rental record is created. This contain the rental date, the time the bike was taken out, the time it was due back and the actual time it was returned. In addition, the amount of rent paid is recorded which is determined through the class size code. Each rental record is associated with only one customer. A bike can be rented out any number of times or may never be rented.
Complete the following tasks: 1. Create a complete ERD to support the business needs as described above 2. Convert the ERD in at least 3NF and draw a dependency diagram 3. Create a database for FBR using MS ACCESS (Create tables only for bikes, rentals and customers with primary and foreign keys defined and other their other data) 4. Create a form to input data for bikes. 5. Input some significant data (8 bikes, 5 customers and 4 rentals) 6. Create SELECT, JOIN and NUMERIC Function queries (one of each type; you can use any tables and any fields) 7. Create a report using any one of the above queries