Given the following narrative description for Super-Duper Shuttle Service Company:
a. Create a business process level REA model in either grammar or diagram format, for Super-Duper Shuttle Service Company's sales/collection process. Be sure to include all relevant entities, relationships, attributes, and cardinalities (minimum and maximum).
b. Convert your business process level model into a set of minimal relational database tables. Be sure to identify primary and foreign keys in the relational tables.
The Super-Duper Shuttle Service Company (SDSSC) makes money by providing transportation to customers in exchange for money. A typical day involves a shuttle van driving around the city, mostly between the airport and various locations. For example, a van may start the day by picking up three passengers from a local hotel and dropping them off at the airport. Two of these passengers are a husband and wife traveling together and the wife pays for both of them. The other person just happened to be staying at the same hotel and had also called SDSSC for a ride to the airport. This one trip to the airport thus consists of two service engagements. At the airport, six more passengers get on. Three of them are co-workers who need to go to a local manufacturing plant and whose corporation has an account with SDSSC. One passenger needs to go to Hotel A and the other two passengers need to go to Hotel B. Customers may either be individuals or corporations. SDSSC owns many vans, each of which holds up to six passengers with luggage. A service engagement usually involves just one van, but sometimes will include more than one van. For example, if a corporate customer is sponsoring a training seminar, it might call SDSSC and request several vans for one engagement to transport a large group of employees to another location. The drivers of the SDSSC vans are responsible for providing the transportation services to all customers and they process the cash receipts from individual customers (who are required to pay the driver when they enter the van). Corporate customers pay for all transportation services contracted in a given month on the 15th day of the following month - they NEVER make partial payments. Cash received from corporate customers is processed by one of SDSSC's five cashiers. All cash receipts for a particular day are deposited into one of several bank accounts owned by SDSSC; the account into which they are deposited rotates from day to day. Cash receipts cannot be recorded in the database without being associated with a valid bank account. Although bank accounts cannot be established without immediately depositing one or more cash receipts into them, CC must be able to enter the account information into the database before entering the related cash receipts. SDSSC has specifically requested to keep cashiers and drivers in separate tables rather than combining them into one employee table. SDSSC has also requested to be able to input data about vans, employees and corporate customers before any transactions involving them actually take place.
The following attributes must be accounted for in your model.
Driver ID
Dollar amount of a service engagement
Customer number
Date of most recent oil change for a van
Cash receipt amount
Driver name
Cash account number
Cashier ID
Origination of a service engagement
Customer name
Van vehicle identification number
Cashier telephone number
Date a van was acquired
Type of customer (individual/corporate)
Customer address
Number of points on a driver's license
Customer telephone number
Cash receipt number
Cash receipt date
Destination for a service engagement
Service engagement invoice number
Cashier name
Cashier address
Driver address
Driver telephone number
Cashier's fidelity bond rating
Date of a service engagement
Mileage at most recent oil change for a van
Driver's license number
Cash account balance
Type of a cash account (e.g. checking, savings)
Location of a cash account
Starting time of a service engagement
Ending time of a service engagement
Customer account receivable balance
Correct Answer:
Verified
View Answer
Unlock this answer now
Get Access to more Verified Answers free of charge
Q45: Fenzler Corporation shipped merchandise fob shipping point
Q46: List the resource, internal agent, and external
Q47: What document is often used to capture
Q48: What is the normal order of events
Q49: Which relationships in the sales/collection event usually
Q50: Using the following tables, what are the
Q51: Given the following narrative description for Cheerful
Q52: Given the following narrative description for Do-it-all
Q53: Create a "generic" REA business process level
Q55: List the similarities and differences in the
Unlock this Answer For Free Now!
View this answer and more for free by performing one of the following actions
Scan the QR code to install the App and get 2 free unlocks
Unlock quizzes for free by uploading documents