Question

Complete the following tasks. In each exercise, represent your answer only in DBDL. Do not use diagrams a this point. Submit either a text file or a Word document with your work. Make sure you follow chapter 6's DBDL notation

(****** Just to clarify: this is the first question on Page 220. It says to 'produce the following reports', but what you are asked to do is use DBDL notation to create 5 tables: Guide, Trip, Customer, Reservation and TripGuides
Make sure to list the FK where they belong ******)

1. Design a database to produce the following reports. Do not use any surrogate keys in your design.
   • For each guide, list the guide number, guide last name, guide first name, address, city, state, postal code, telephone number, and date hired.
   • For each trip, list the trip ID number, the trip name, the location from which the trip starts, the state in which the trip originates, the trip distance, the maximum group size, the type of trip (hiking, biking, or paddling), the season in which the trip occurs, and the guide number, first name, and last name of each guide. A guide may lead many trips and a trip may be led by many differen't guides.
   • For each customer, list the customer number, customer last name, customer first name, address, city, state, postal code, and telephone number.
   • For each reservation, list the trip ID number, the trip date, the number of persons included in the reservation, the price of the trip per person, the total of any additional fees per person, and the customer number, first name, and last name of the customer who made the reservation.

Answer 1

Schema design in DBDL :

Guide [GuideNumber(Primary Key), GuideLastName, GuideFirstName, Address, City , State, PostalCode, TelephoneNumber, DateHired]

Trip [TripIdNumber(Primary Key), TripName, TripStartLocation, TripStartState, TripDistance, MaxGroupSize, TypeOfTrip, SeasonOfTrip, GuideNumber(Foreign Key)]

Customer[ CustomerNumber(Primary Key), CustomerLastName, CustomerFirstName, Address, City, State, PostalCode, TelephoneNumber]

Reservation[TripIdNumber(Primary Key), TripDate, NumOfPeopleInReservation, TripPricePerPerson, AdditionalFeesPerPerson, CustomerNumber(Foreign Key)]

Queries to fetch required data from the above tables :

1) select GuideNumber,GuideLastName,GuideFirstName,Address, City , State, PostalCode, TelephoneNumber, DateHired from Guides where GuideNumber = "123"
2) select TripIdNumber, TripName, TripStartLocation, TripStartState, TripDistance, MaxGroupSize, TypeOfTrip, SeasonOfTrip, GuideNumber,GuideLastName,GuideFirstName from Trip inner join Guide on(Trip.GuideNumber=Guide.GuideNumber)
where TripIdNumber = "123"
3) select CustomerNumber, CustomerLastName, CustomerFirstName, Address, City, State, PostalCode, TelephoneNumber from Customer where CustomerNumber = "123"
4) select TripIdNumber, TripDate, NumOfPeopleInReservation, TripPricePerPerson, AdditionalFeesPerPerson, CustomerNumber , CustomerFirstName, CustomerLastName
from Reservation inner join Customer on (Reservation.CustomerNumber = Customer.CustomerNumber) where TripIdNumber = "123"