As a frequent traveler, Basil Wright recognizes the need for economical off-airport private parking. When an opportunity presented itself, Basil Wright and his brother Sage Wright, purchased several acres of land near the International Airport. The brothers have since converted a portion of their newly acquired land into a parking facility and provide a shuttle service for travelers. While Sage Wright is responsible for shuttling travelers to and from the airport, Basil Wright manages the business's daily office activities. Wright Brothers Airport Shuttle Service (WBAS) offers valet parking, providing 200 covered valet spaces and 250 uncovered valet parking spaces. Covered parking is $13.00 per day, and uncovered parking is $10.50 per day. When a customer arrives, he turns his car over to a parking attendant employee. The customer then goes into the office to check in his vehicle with another employee - a parking assistant. He provides the necessary information and requests covered or uncovered parking. When a vehicle is checked in the attendant looks at a list of available parking spaces to determine where a car is to be parked. The vehicle is assigned a parking location. The customer receives a claim ticket, and then boards an airport shuttle. A claim tag is hung from the vehicle's rearview mirror. When the valet parking customer returns, he calls the shuttle service, provides his claim ticket number, and then catches the next available shuttle to the parking facility. When he arrives at the parking facility, the parking attendant locates the customer record by ticket number. The customer then pays his parking fees, picks up his car and leaves. WBAS also provides parking for pilots who keep their private aircrafts at the airport. Basil Wright wants a database for the parking and shuttle service. You are Mr. Wright's good friend, so you volunteer to work on the database for him. Information Specifications A customer might be a regular commercial traveler or a private aircraft traveler When a new customer checks in, he gives his name, email, phone, flight number, expected return date, return flight number (if he has one). The customer will be assigned an ID by WBAS. Repeat customers will give their customer ID and flight information (i.e. flight number, expected return date, return flight number). If s/he cannot produce or remember the ID, WBAS should be able to search for the customer by email or phone number. Private aircraft customers do not have to provide flight information, instead, WBAS keep track of their aircraft registration number and pilot's licence number. As long as a vehicle is in WBAS parking facility, an active record is kept. An active record will contain at least the customer number, claim tag number, date brought in - should default to the current date, parking location, parking attendant ID, expected return date. Once the customer claims the vehicle, WBAS a claim record is made that contains the customer number, vehicle registration number, date the vehicle was brought in, location where it was parked, and the date it was claimed. When a customer returns to claim his/her vehicle, a receipt is prepared indicating the date and the parking cost. Mr. Wright wants the parking attendant completes a parking reservation form online. When he does this, he should be able to select the rate code and then have the rate and description fields automatically filled in. At the end of each day, Mr. Wright prepares two reports, a Daily Check-in Report and a Tentative Check-Out Report. The Daily Check-in Report lists each vehicle that was checked in that day. The Tentative Check-Out Report identifies vehicles scheduled for checkout the next day. For any selected day, My Wright prints a Tentative Check-Out Report. This report identifies the vehicles tentatively scheduled for pick up the next day. Mr. Wright wants this report sorted by rate code. He also wants to know how many vehicles are scheduled for pick up. Each Month, Mr. Wright prepares an Activity Report. The Activity report shows each vehicle that was checked in, the date it was checked in, the name and contact information of the customer, the make, model and tag of the vehicle, the initials of the attendant who did they check in, the initials of the parking attendant, the actual checkout date, and the charge. Mr. Wright also needs answers to the following questions for any given time period: What is the average length of stay for vehicles? How many cars use the covered and uncovered spaces? How many cars were checked in today? How many cars checked out today? Some Current Sample documents: Claim ticket and tag: Wright Brothers Airport Parking Date: 4/10/2020 Time: 7:13 a.m. Location Lot2 C35 Vehicle BES1234 Please show this ticket to reclaim your vehicle Wright Brothers Airport Parking Date: 4/10/2020 Time: 7:13 a.m. Location Lot2 C35 Vehicle BES1234 Keep this in the vehicle WRIGHT BROTHERS' AIRPORT SHUTTLE SERVICE Parking Reservation Form Claim No: Customer Last Name: Customer First Name Flight Number: Email: Phone: Check-in Date: Tentative Return Date: Check-out Date: Return Flight Number: Registration Parking Location: Rate Code: Rate Description: Rate: Current Charges: Comments Daily Check-In Report WRIGHT BROTHERS' AIRPORT SHUTTLE SERVICE Daily Check-In Report Current Date Customer Covered/ Uncovered Parking Location Ticket Registration Car Number Number Make Car Model Tentative Check-Out-Date Covered C1 C3 Brooke Bennett 144 Larry Lansing 146 B7987 D4756 Mitsubishi Montero Lexus ES250 1/5/2020 1/7/20207 Category Subtotal Uncovered U5 U7 David Farmer Samantha Yu 145 148 IMOK4 IM47 Ford Mazda Expo ES250 1/4/2020 1/30/2020 Category Subtotal Total Vehicles Checked in Deliverables: A copy of the final design using Visio, drawio, or any other ERD documenting tool. A documented copy of your script with all the necessary stored procedures, triggers, queries, including DDL statements. A copy/self contained dump of the database Documentation of any business rules assumptions that you make Test your design You should have a minimum of 10 records in each of your tables. Make up the data. Include some drop-offs that have not yet been picked up. At least we know that the some customers have dropped off and picked up their cars: Design Considerations: You may treat your instructor as your client in order to get clarification of the database requirements. In addition you may also make pertinent assumptions. Use they most appropriate size and type for your fields. Also use constraints and validation rules where appropriate. The samples shown above are just suggestions. You should design and build your database based on the current and possible future needs of your client