This is a case problem in Database Systems Design, Implementation, and Managemen
ID: 3790086 • Letter: T
Question
This is a case problem in Database Systems Design, Implementation, and Management 12e
Chapter 4 Cases Problem13Solution is not available in this website.
The questions are a few sentences in the end and all in the beginning part are just descriptions.
Use the following description of the operations of the RC_Charter2 Company to complete this exercise. The RC_Charter2 Company operates a fleet of aircraft under the Federal Air Regulations (FAR) Part 135 (air taxi or charter) certificate, enforced by the FAA. The aircraft are available for air taxi (charter) operations within the United States and Canada. Charter companies provide so-called “unscheduled” operations—that is, charter flights take place only after a customer reserves the use of an aircraft to fly at a customer-designated date and time to one or more customer-designated destinations, transporting passengers, cargo, or some combination of passengers and cargo. A customer can, of course, reserve many different charter flights (trips) during any time frame. However, for billing purposes, each charter trip is reserved by one and only one customer. Some of RC_Charter2’s customers do not use the company’s charter operations; instead, they purchase fuel, use maintenance services, or use other RC_Charter2 services. However, this database design will focus on the charter operations only. Each charter trip yields revenue for the RC_Charter2 Company. This revenue is generated by the charges a customer pays upon the completion of a flight. The charter flight charges are a function of aircraft model used, distance flown, waiting time, special customer requirements, and crew expenses. The distance flown charges are computed by multiplying the round-trip miles by the model’s charge per mile. Round-trip miles are based on the actual navigational path flown. The sample route traced in Figure P4.13 illustrates the procedure. Note that the number of round-trip miles is calculated to be 130 + 200 + 180 + 390 = 900.
Depending on whether a customer has RC_Charter2 credit authorization, the customer may: _ Pay the entire charter bill upon the completion of the charter flight. _ Pay a part of the charter bill and charge the remainder to the account. The charge amount may not exceed the available credit. _ Charge the entire charter bill to the account. The charge amount may not exceed the available credit.
Customers may pay all or part of the existing balance for previous charter trips. Such payments may be made at any time and are not necessarily tied to a specific charter trip. The charter mileage charge includes the expense of the pilot(s) and other crew required by FAR 135. However, if customers request additional crew not required by FAR 135, those customers are charged for the crew members on an hourly basis. The hourly crew-member charge is based on each crew member’s qualifications The database must be able to handle crew assignments. Each charter trip requires the use of an aircraft, and a crew flies each aircraft. The smaller piston engine-powered charter aircraft require a crew consisting of only a single pilot. Larger aircraft (that is, aircraft having a gross takeoff weight of 12,500 pounds or more) and jet-powered aircraft require a pilot and a copilot, while some of the larger aircraft used to transport passengers may require flight attendants as part of the crew. Some of the older aircraft require the assignment of a flight engineer, and larger cargo-carrying aircraft require the assignment of a loadmaster. In short, a crew can consist of more than one person, and not all crew members are pilots.
The charter flight’s aircraft waiting charges are computed by multiplying the hours waited by the model’s hourly waiting charge. Crew expenses are limited to meals, lodging, and ground transportation. The RC_Charter2 database must be designed to generate a monthly summary of all charter trips, expenses, and revenues derived from the charter records. Such records are based on the data that each pilot in command is required to record for each charter trip: trip date(s) and time(s), destination(s), aircraft number, pilot (and other crew) data, distance flown, fuel usage, and other data pertinent to the charter flight. Such charter data are then used to generate monthly reports that detail revenue and operating cost information for customers, aircraft, and pilots. All pilots and other crew members are RC_Charter2 Company employees; that is, the company does not use contract pilots and crew. FAR Part 135 operations are conducted under a strict set of requirements that govern the licensing and training of crew members. For example, pilots must have earned either a Commercial license or an Airline Transport Pilot (ATP) license. Both licenses require appropriate ratings. Ratings are specific competency requirements. For example:
To operate a multiengine aircraft designed for takeoffs and landings on land only, the appropriate rating is MEL, or Multiengine Landplane. When a multiengine aircraft can take off and land on water, the appropriate rating is MES, or Multiengine Seaplane.
The instrument rating is based on a demonstrated ability to conduct all flight operations with sole reference to cockpit instrumentation. The instrument rating is required to operate an aircraft under Instrument Meteorological Conditions (IMC), and all such operations are governed under FAR-specified Instrument Flight Rules (IFR). In contrast, operations conducted under “good weather” or visual flight conditions are based on the FAR Visual Flight Rules (VFR).
The type rating is required for all aircraft with a takeoff weight of more than 12,500 pounds or for aircraft that are purely jet-powered. If an aircraft uses jet engines to drive propellers, that aircraft is said to be turboprop-powered. A turboprop—that is, a turbo propeller-powered aircraft—does not require a type rating unless it meets the 12,500-pound weight limitation. Although pilot licenses and ratings are not time-limited, exercising the privilege of the license and ratings under Part 135 requires both a current medical certificate and a current Part 135 check ride. The following distinctions are important:
The medical certificate may be Class I or Class II. The Class I medical is more stringent than the Class II, and it must be renewed every six months. The Class II medical must be renewed yearly. If the Class I medical is not renewed during the six-month period, it automatically reverts to a Class II certificate. If the Class II medical is not renewed within the specified period, it automatically reverts to a Class III medical, which is not valid for commercial flight operations.
A Part 135 check ride is a practical flight examination that must be successfully completed every six months. The check ride includes all flight maneuvers and procedures specified in Part 135. No pilot crew members must also have the proper certificates in order to meet specific job requirements. For example, loadmasters need an appropriate certificate, as do flight attendants. In addition, crew members such as loadmasters and flight attendants, who may be required in operations that involve large aircraft (more than a 12,500-pound takeoff weight and passenger configurations over 19) are also required periodically to pass a written and practical exam. The RC_Charter2 Company is required to keep a complete record of all test types, dates, and results for each crew member, as well as pilot medical certificate examination dates. In addition, all flight crew members are required to submit to periodic drug testing; the results must be tracked, too. (Note that no pilot crew members are not required to take pilot-specific tests such as Part 135 check rides. Nor are pilots required to take crew tests such as loadmaster and flight attendant practical exams.) However, many crew members have licenses and/or certifications in several areas. For example, a pilot may have an ATP and a loadmaster certificate. If that pilot is assigned to be a loadmaster on a given charter flight, the loadmaster certificate is required. Similarly, a flight attendant may have earned a commercial pilot’s license. Sample data formats are shown in Table P4.13.
PART A TESTS
TEST CODE
TEST DESCRIPTION
TEST FREQUENCY
Part 135 Flight Check
months
Medical, Class
months
Medical, Class
12 months
Loadmaster Practical
12 months
Flight Attendant Practical
12 months
Drug test
Random
Operations, written exam
months
PART B RESULTS
EMPLOYEE
TEST CODE
TEST DATE
TEST RESULT
101
12-Nov-07
Pass-
103
23-Dec-07
Pass-
112
23-Dec-07
Pass-
103
11-Jan-08
Pass-
112
16-Jan-08
Pass-
101
16-Jan-08
Pass-
101
11-Feb-08
Pass-
125
15-Feb-08
Pass-
PART C LICENSES AND CERTIFICATIONS
LICENSE OR CERTIFICATE
LICENSE OR CERTIFICATE DESCRIPTION
ATP
Airline Transport Pilot
Comma
Commercial license
Med-
Medical certificate, class
Med-
Medical certificate, class
Instar
Instrument rating
MEL
Multiengine Land aircraft rating
LM
Load Master
FA
Flight Attendant
PART D LICENSES AND CERTIFICATES HELD BY EMPLOYEES
EMPLOYEE
LICENSE OR CERTIFICATE
DATE EARNED
101
Comma
12-Nov-93
101
Instar
28-Jun-94
101
MEL
9-Aug-94
103
Comma
21-Dec-95
112
FA
23-Jun-02
103
Instar
18-Jan-96
112
LM
27-Nov-05
Pilots and other crew members must receive recurrence training appropriate to their work assignments. Recurrence training is based on an FAA-approved curriculum that is job-specific. For example, pilot recurrence training includes a review of all applicable Part 135 flight rules and regulations, weather data interpretation, company flight operations requirements, and specified flight procedures. The RC_Charter2 Company is required to keep a complete record of all recurrence training for each crew member subject to the training. The RC_Charter2 Company is required to maintain a detailed record of all crew credentials and all training mandated by Part 135. The company must keep a complete record of each requirement and of all compliance data.
To conduct a charter flight, the company must have a properly maintained aircraft available. A pilot who meets all of the FAA’s licensing and currency requirements must fly the aircraft as Pilot in Command (PIC). For those aircraft that are powered by piston engines or turboprops and have a gross takeoff weight under 12,500 pounds, single-pilot operations are permitted under Part 135 as long as a properly maintained autopilot is available. However, even if FAR Part 135 permits single-pilot operations, many customers require the presence of a copilot who is capable of conducting the flight operations under Part 135.
The RC_Charter2 operations manager anticipates the lease of turbojet-powered aircraft, and those aircraft are required to have a crew consisting of a pilot and copilot. Both pilot and copilot must meet the same Part 135 licensing, ratings, and training requirements.
The company also leases larger aircraft that exceed the 12,500-pound gross takeoff weight. Those aircraft can carry the number of passengers that requires the presence of one or more flight attendants. If those aircraft carry cargo weighing over 12,500 pounds, a loadmaster must be assigned as a crew member to supervise the loading and securing of the cargo. The database must be designed to meet the anticipated additional charter crew assignment capability.
a. Given this incomplete description of operations, write all applicable business rules to establish entities, relationships, optionality’s, connectivity’s, and cardinalities. (Hint: Use the following five business rules as examples, writing the remaining business rules in the same format.)
A customer may request many charter trips.
Each charter trip is requested by only one customer.
Some customers have not (yet) requested a charter trip.
An employee may be assigned to serve as a crew member on many charter trips.
Each charter trip may have many employees assigned to it to serve as crew members.
b. Draw the fully labeled and implementable Crow’s Foot ERD based on the business rules you wrote in Part (a) of this problem. Include all entities, relationships, optionality’s, connectivity, and cardinalities.
PART A TESTS
TEST CODE
TEST DESCRIPTION
TEST FREQUENCY
Part 135 Flight Check
months
Medical, Class
months
Medical, Class
12 months
Loadmaster Practical
12 months
Flight Attendant Practical
12 months
Drug test
Random
Operations, written exam
months
FIGURE Round-trip mile determination P4.13 Intermediate Stop 200 miles Pax Pickup 130 miles Home Base 180 miles 390 miles DestinationExplanation / Answer
Page 23 of 144
PRODUCT table
ProductcodeProductdescriptionUnit of sale
Standard price per unit
P001 Apples kg 0,80 €P876 Oranges kg 0,90 €
ORDER table
OrdernumberCustomernumberProductcode
Actual price per unit
Quantity AmountO001 C001 P001 0,80 € 2,5 2,00 €O002 C001 P876 0,90 € 1 0,90 €O003 C002 P001 0,80 € 3 2,40 €O004 C001 P001 0,80 € 2 1,60 €O005 C002 P876 1,05 € 1,5 1,58 €O006 C001 P001 0,90 € 2 1,80 €O007 C003 P001 0,90 € 3 2,70 €O008 C001 P876 0,90 € 1,5 1,35 €
This still isn’t perfect, since Orders and their Details continue tobe mixed together in one table.
1
2.9. Learning a minimum about database
Every business student needs to know about and understand:
Database principles
Tables
Queries
A query is a results table
Introduction to database design
How to use a sample database
How databases are used - ERP, CRM etc.
2.10. Basic concepts
Entity
: class of thing about which data is stored
Examples: student; programme – these are
tables
of data.
Occurrence
: a single instance of an entity
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.