The Scenario

Salmon River Outfitters is a small business located in upstate N.Y. that organizes day long fishing trips. Currently, they use an excel workbook in Excel to keep tract of reservations. Their business has grown so large that they decided a database would be a better fit for their business.

A little bit of fishing terminology/information:

1. Guides are the folks who take the Customers on fishing trips.

2. There are three types of fishing trips available, Lake, River, and Stream.

3. Fishing trips are available in the Spring, Summer and Fall.

Below are the fields from the Excel Spreadsheet in an unnormalized table named Reservation.

Reservation(ReservationID, CustomerName, CustomerEMail, CustomerCellPhone, TripName, TripCity, TripType, TripSeason, TripDate, TripPrice, NumCustomers, RentalFees, GuideName, GuideAddress, GuideCellPhone)

Your job is to normalize the Reservation table into a group of tables that are in 3NF based on the business rules listed below. There are no additional business rules so please use just the 5 rules below. Do NOT add any additional fields other than Primary keys.

After consulting with the owner/office workers and reviewing reports needed, the following business rules have been determined.

1. A customer can go on more than one trip and a trip can have more than one customer on it.

2. A customer can go on only one trip per day.

3. A Guide can guide many trips, and a trip can have more than one guide on it.

4. Each trip has a price attached to it and it is the same price for all customers.

5. If customers need to rent any fishing equipment, then they will have to pay additional rental Fees.

Tables You will use the following naming convention for the obvious table names Reservation, Customer, Trip, and Guide. If you add any composite table(s) due to a M:N relationship(s), then you should be sure to use a naming convention that best describes the data in that table.

Use DBDL to design the tables necessary to normalize the reservation table above. Make sure all table are in 3NF and the correct relationships exist between the tables

Based on your DBDL, use the SQL Server to create the database, tables, and relationships.

Your DBDL MUST match the database you create in SQL Server!

Each table should have no less than 5 and no more than 10 records. The data for the tables will be fictitious and it is up to you what names, addresses, etc. that you enter

After the tables are created and data is entered into the tables, you will create the five queries listed below

1. How many Fishing trips are schedule for each season?

2. List the name of each trip that has a trip type of river or lake and a price of $75.00.

3. List the name of each trip, the customer name, and the Guide who is leading the trip.

4. How many reservations have a trip price that is between $75.00 and $130.00?

5. For each customer that has more than one reservation, list customers first and last name, the trip name, and the trip type.