Snake River 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. Identify the dependencies among the fields (2 points).
2. Break the table into multiple tables, so that the database is in 3NF. You may need to add new fields (4 points).
3. Build the Crow’s Foot diagram that shows the following:
a. Tables with PKs and FKs (2 points).
b. Relationships and their connectivity and cardinality (2 points).
c. Whether each relationships is strong or weak (2 points).
TRIP_NAME DIFFICULTY MAP RIVER TRIP_DATE CUST_LNAME CUST_FNAME EXPERTISE INSTRUCTOR INSTR_EXP_YEARS
Wild Water Adventure 2 (Intermediate) wva.bmp Clearwater Falls 5/14/15 Adams Nicole 2 (Intermediate) Jackson 12
Wild Water Adventure 2 (Intermediate) wva.bmp Clearwater Falls 5/20/15 Allen James 3 (Advanced) Moore 15
Red Rock Rapids 1 (Easy) rrr.bmp Red river 5/22/15 Adams Nicole 2 (Intermediate) Simmons 13
Clearwater 3 (Difficult) c.bmp Clearwater Falls 5/25/15 Marks Amy 4 (Expert) Moore 15
Clearwater 3 (Difficult) c.bmp Clearwater Falls 5/25/15 Alexander Eric 3 (Advanced) Jackson 12
Clearwater 3 (Difficult) c.bmp Clearwater Falls 5/30/15 Allen James 3 (Advanced) Jackson 12
Red Rock Rapids 1 (Easy) rrr.bmp Red River 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 (1 point).
• The same customer can book only one trip on one day (1 point).
• Each booked trip has only one instructor. Each instructor can accompany many booked trips (1 point).
• Each river can be the base for many trips, but each trip is based only on one river (1 point).
• There are four possible levels of difficulty of the trips: 1 (Easy), 2 (Intermediate), 3 (Difficult), and 4 (Insane) (2 points).
• There are four possible levels of customer expertize: 1 (Beginner), 2 (Intermediate), 3 (Advanced), and 4 (Expert) (2 points).
The post Snake River Adventures gives appeared first on My Assignment Online.