Reference no: EM133882310

Assignment:

Coastal Rides Bike Rentals, founded in 1980, is a prominent bike rental company in Australia, offering a wide range of bicycles that suit all types of riders-from city bikes for urban commuters to mountain bikes for adventure seekers. Coastal Rides is known for its customer-centric approach and ensures a smooth rental process for both locals and tourists. With multiple locations along Australia's coastal cities, the company provides bikes from various categories including standard, road, mountain, and electric bikes. Each bike has a unique frame number and a distinct registration code, along with attributes like make, model, year, and color. Bikes can be rented at one location and returned at another (a service called drop-off). Coastal Rides records the odometer reading before and after every rental. Unlike other companies, Coastal Rides doesn't inspect bikes for damage upon return but does ensure bikes are returned with inflated tires.

The air pressure level (low, medium, or high) is recorded when a bike is returned. Coastal Rides tracks rental dates but not the specific times of rental and return. If a customer requests a specific bike category and it is unavailable, they may be upgraded to a higher-category bike at no extra cost. Rental rates are fixed based on the bike category, with rates for 1 day, 1 week, and 1-month rentals. Drop-off fees vary depending on the pick-up and drop-off locations and the bike category. The company maintains detailed customer records, including names, addresses, phone numbers, and a unique ID (similar to a driver's license). Employees' details include the same information, and they are categorized as bike technicians, clerks, or managers. Employees get a 30% discount on rentals under 1 week or a 15% discount for longer rentals.

Each employee is assigned to a specific location, and the company's head office is in Melbourne, where all staff are managers, including the president and two vice-presidents (operations and marketing). Coastal Rides also runs promotional events throughout the year, offering discounts of up to 50% on specific bike categories. These promotions do not combine with employee discounts and apply only to one bike category at a time (e.g., only electric bikes).

Tasks to be completed:

a) Create a Crow's Foot notation ERD to support the business operations of Coastal Rides Bike Rentals.

b) Transform your ERD into a relational model.

c) For each table in your relational model, create a dependency diagram and demonstrate that your design is in 3NF.