Designing Database for Kayak Adventures

The assignment requires breaking the provided table into multiple tables to create a database for Kayak Adventures, following business rules such as many-to-many relationships between customers, trips, instructors, and rivers, with primary keys and foreign keys defining these relationships.

(2 points)
Kayak Adventures gives its customers a unique opportunity to book a kayak trip with an instructor at the
location of his/her choice. Use the table with the sample data below and the following business rules to
build a database for the company.
1. Break the table into multiple tables, so that the database is in 3NF. You may need to add new
fields.
2. Identify the tables with PKs and FKs.
TRIP_NAME DIFFICULTY MAP RIVER TRIP_DATE CUST_LNAM
E
CUST_FNAME EXPERTISE INSTRUCTOR INSTR_EXP_YEARS
Wild Water
Adventure
2
(Intermediate)
wva.bm
p
Ohio 5/14/15 Adams Nicole 2 (Intermediate) Jackson 12
Wild Water
Adventure
2
(Intermediate)
wva.bm
p
Ohio 5/20/15 Allen James 3 (Advanced) Moore 15
Red Rock
Rapids
1 (Easy) rrr.bmp Allegheny 5/22/15 Adams Nicole 2 (Intermediate) Simmons 13
Clearwater 3 (Difficult) c.bmp Ohio 5/25/15 Marks Amy 4 (Expert) Moore 15
Clearwater 3 (Difficult) c.bmp Ohio 5/25/15 Alexander Eric 3 (Advanced) Jackson 12
Clearwater 3 (Difficult) c.bmp Ohio 5/30/15 Allen James 3 (Advanced) Jackson 12
Red Rock
Rapids
1 (Easy) rrr.bmp Allegheny 6/1/16 Dobbins Brenda 1 (Beginner) Simmons 13
Business Rules:
 The company sells multiple trips. Each customer can book many trips, and each trip can be
booked by many customers.
 The same customer can book only one trip on one day.
 Each booked trip has only one instructor. Each instructor can accompany many booked trips.
 Each river can be the base for many trips, but each trip is based only on one river.
 There are four possible levels of difficulty of the trips: 1 (Easy), 2 (Intermediate), 3 (Difficult), and
4 (Insane).
 There are four possible levels of customer expertize: 1 (Beginner), 2 (Intermediate), 3
(Advanced), and 4 (Expert)