CASE 20:

CREATING A DATABASE MANAGEMENT SYSTEM

MOUNTAIN BIKE RENTALS

Vail Resort in Vail, Colorado, is internationally known as one of the best places in North America for mountain biking. Since 1973, Slopeside Bike Rentals has been a tradition in the area. At Slopeside Bike Rentals customers will find the largest selection ofbikes, parts, accessories, books, maps, clothing, shocks, helmets, eyewear, shoes, car racks, and touring gear in the area with everything you need for on and Off the road. Its state-of-the-art demo and rental program has everything from premium dual suspension to kids' bikes and trailers.

You have been employed for the past three sunnners by Slopeside Bike Rentals. Recently, there has been a surge in business and the owners need a more accurate way to luanage the rental busi-ness. You have decided to create a database to help the owners keep track ofthe bike rentals, who the customers are, antount paid, and any danrage to the bikes when they are rented. Currently Slopeside Bike Rentals owns 13 mountain bikes in its fleet ofrentals. The bikes vary' in type, size, and parts. When customers rent bikes, they are required to leave their driver's license nunlber and to give you a home address, phone ntnnber, and credit card number.

You llave designed the entity classes and primary keys for the database as the following:

Entity

Bike

Customer

Rental

Primary Key

Bike_1D

CustomerJD

Rental_lD

You have also identified the following business rules:

1. Rentals can have many customers assigned but have at least one.

2. A bike must be assigned to one and only one rental type.

3. A customer can rent one or more bikes at one tinie.

4. A bike can be assigned to only one customer but need not be assigned to any customer.

Yourjob is to be completed in the lbllowing phases:

2. 3.

4.

5.

Develop and describe the entity-relationship diagram.

Use normalization to assure the correctness of the tables (relations).

Create the database using a personal DBMS package (preferably Microsoft Access).

Slopeside Bike Rentals Iras the fbllowing fix structures for its 13 bike rentals:

Use the DBMS package to create the basic report in Figure GP.3.

Description

Specialized Rockhopper

Specialized Rockhopper

Trek Fuel 70

"Trek Fuel 80

Trek Fuel 80

Trek Fuel 90

Marin Downhill FRS

Marin Downhill FRS

Marin Downhill FRS

Specialized Stumpjumper FSR

Specialized Stumpjumper FSR

Specialized Stumpjumper FSR

Specialized Stumpjumper Hardtail

Cost per Hour

$12

12

12

15

15

16

16

16

16 18

18

18

20

Management Information Systems

308

Group Projects

Figure GP. 3

Slopeside Bike Rental Report

Slopeside Bike Rental Report

Descrip'ion Specialized ockhopper

Oate

Sm:th

Suvruna-y Rock hcvpel decod records'

Fuel

detail

Laperre

Ttek 801.1

Trek 90

7'1'2005

Dunn

Son-unary tor Trek Fuel 90 (2 deloil record')