(U6CP)

BUSINESS CASE A healthy lifestyle advocacy group is aiming to build a bike sharing system within a medium-size metropolitan area. The group has signed a contract with a company to provide the bike sharing technology and hardware (bike stations, bikes, network equipment, a server, and bike sharing software that automates the process of checking out and returning bicycles, and billing customers). The docking station provides a limited number of bike parking spots and is characterized by its location, bike capacity, and by real-time number of bikes parked. Docking stations have computers running on them, credit card reader, and a networking module which allows it to be connected to the Internet. The fleet of bikes has also been purchased.

FUNCTIONING OF THE SYSTEM There are two ways a user can get access to the system: 1) Using her annual subscription ($70) - 2) Buying day pass ($5) Every ride under 30 minutes is free. User is charged $1 dollar for a ride between 30 and 1 hour, 3 dollars for rides between 1 hour and 2 hours and a flat rate of 7 dollars for any ride over 2 hours. The users who havent returned a bike within 10 hours are billed with a $50 fine.

REGISTRATION PROCESS A user can register online or purchase a day pass at the station.

Directions

Submit the following information in a Word document:

a) Create a table(s) to describe current and historical rides;

b) Write SQL statements that would perform the following tasks:

* Compute the average ride duration for a given user;

* Compute the number of bikes checked out at the given bike station;

* Retrieve the list of all currently active users.