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, relationships, and diagrams. I will not be grading you on data types, indexes or whether you allow Null values for the fields in your database other than the Primary Key fields.

Your Database Name should be FishingFinal_FirstName.

Your DBDLMUSTmatchthe database you create in SQL Server! If it does not,30 points will be deducted from your score.

Data

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. Save the five queries in one query window named FinalQuery_FirstName.sql

Queries - use SQL Server to make the following queries in one query window.

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's 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.

Database Diagram

Make the database diagram of your tables and take a screenshot to include in your Word document.