Kayak Adventures Database Design

This assignment focuses on designing a relational database for Kayak Adventures, a company that offers kayak trips. The task involves normalizing the provided data into 3NF (Third Normal Form) by breaking down a single table into multiple related tables. The solution requires identifying primary keys (PKs) and foreign keys (FKs) to establish relationships between tables, adhering to specific business rules. These rules include the many-to-many relationship between customers and trips, the one-to-many relationship between instructors and trips, and the one-to-many relationship between rivers and trips. The assignment also involves creating tables for trips, customers, instructors, and rivers, along with a junction table to handle the many-to-many relationship between customers and trips. The solution demonstrates a practical application of database normalization principles.

(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)