Read the following case study: While visiting Europe during a study-abroad program last year, you noticed that
Question:
Read the following case study:
While visiting Europe during a study-abroad program last year, you noticed that many cities had racks of bicycles available for short-term rentals to enable customers to get around easily. When you returned to the United States for the summer, you discovered that your hometown is beginning a bicycle rental service similar to those in Europe. You do not have a summer job, so you immediately contact the manager of the new program, Maria Trejo, to see if you can help with the start-up. Maria interviews you on the telephone and discovers your talent for database design and implementation. She hires you on the spot to create a prototype database system that will be the basis for the new service.
On your first day of work, Maria explains to you how the business will operate: “Customers can go to any bicycle kiosk in the city and enter their credit card number, name, and address to register for rentals. After they register, they immediately should be assigned a customer identification number at the kiosk, and they can then request a bicycle at any rental location throughout the city. The kiosk will display a bicycle number and an unlocking code. The customer has 10 minutes to locate the bike on the rack and unlock it for use. It’s easy to adjust the seat and mirrors and then take off to explore our beautiful city.” She further explains, “Customers usually rent for a period of a few days. During that time, they can pick up bikes from any kiosk in the city and drop them off in different locations.”
You ask about the pricing scheme of a bicycle rental. Maria explains: “There are several tiers of charges. If someone rents a bicycle for less than 24 hours, it’s $3. If they rent time on bikes for 10 days or less, it’s $15. Anything over 10 days is $150. The prices are low to encourage people to rent bikes and not drive, because the city has a lot of traffic and pollution. During the rental period, customers can drop off and pick up bikes as many times as necessary. For example, if a vacationer is here for three days, she can pick up and drop off bikes at multiple kiosks during that period.”
Maria continues to explain how the kiosks or stations work. “Ten stations are strategically located around the city. They each have a unique number and street address. When a customer rents a bike, we need to
record who takes it, from which station, and when. We also need to record the time the bike is returned and the station where it is returned. Customers can return the bikes to any station in the city. So, you could pick up a bike near your hotel, ride it to your favorite restaurant, drop the bike off at the local kiosk near the restaurant, and eat your meal. Then you could pick up a bike and reverse the journey for the way back to the hotel. We think this rental service will be a huge hit with tourists, especially with Europeans.”
At this point, you think you know how you will design the tables in the database. Now you need to know what additional information Maria needs from the database. When you meet with her the following week, Maria says: “First of all, I’d like an easy way to record customer rental information on some sort of form. Next, the sponsors of this service are very interested in numbers. They’d like to know how many times each bicycle has been rented so they can plan for replacements as bikes become worn out. Our city planners are also interested in numbers. They want to know which stations have the most drop-offs and pick-ups around the city. Based on usage, they might need to increase capacity at some stations, decrease capacity at others, and even close stations that don’t get enough use. Personally, I’d like to know how often each customer rents a bicycle. Also, I’d like to know the average time of each bicycle ride. Our accountants need to know the total price charged to each customer. Finally, I’d like to have a report that lists each segment of all customer bicycle rentals. This could be a great report to validate our hard work.”
You explain to Maria that you can meet all her requirements by creating queries and a report, although the query that calculates the total price will be more challenging. It will require research and a preliminary query that will flow into the final query that calculates the total charges. However, you are confident that you are up to the task.
In this assignment, you first create database tables in Access and populate them with data.
• Create at least 10 customer records with fictitious data such as names, addresses, phone numbers, e-mail addresses, and credit card numbers.
• Create 10 different stations for the bicycles, as outlined in the Background section of this case.
- Create approximately 100 rental transactions with different pick-up and drop-off stations.
- Consider using Microsoft Excel to generate random data and thus reduce the amount of data you need to enter. You can use the RANDBETWEEN function in Excel to create random data.
Legal Research Analysis and Writing
ISBN: 978-1133591900
3rd edition
Authors: William H. Putman, Jennifer Albright