These business scenarios are used for the Data Modeling Colloquium and for the final exam.
Each student is assigned a unique business scenario. The scenario is based on the idea that Greenwich World University, concerned about rising tuition costs, is starting a number of small businesses to raise money that will be used to reduce tuition. The University has turned to this class to help develop data models for each of the new businesses.
For the Data Modeling Colloquium, each student develops a data model using ERDPlus, creates the database, and builds a few APEX screens to access and update database tables. The APEX part of the Data Modeling Colloquium is simply to demonstrate that the student can construct a simple APEX application; the focus at this time is on the data model.
For the final exam, the data model is refined using feedback from the Colloquium, and a finished APEX application is constructed. This application is expected to be suitable for end users to use, with features such as images of items to purchase and easy-to-understand messages when errors are detected. The application should show a logo for the business. Note that you are building an application that does only some of the functions that your data model supports; for your demonstration of the application to be realistic, you will need to enter data into the database that would have come from other applications.
Your final exam presentation is to be a Powerpoint presentation and a demonstration of your APEX application. The presentation is to have only 4 slides:
- Name of problem and requirements in bullet form
- Chen ERD
- Relational ERD
- Link to APEX application
For your Chen ERD, to demonstrate that you understand natural keys, you are not to show any generated keys. For each entity type, you are to identify the natural key, and show it as underlined in the ERD. If you have a good reason to use any generated keys, you can edit the ERD after making your chart for your PowerPoint presentation. Note that every generated key must be justified.
In addition, in your Chen ERD, do not use associative entity types that are provided by ERDPlus. Instead, be careful to show all relationships as diamonds on your Chen ERD, including many-many relationships.
Note: Some sources of free images are Unsplash and AI image generators. You can generate a logo for free at logo.com, as long as you’re careful not to buy anything they offer along with the free logo.
- GWMeals: A meal delivery service. GWM will deliver ready-to- eat meals within a 2-mile radius of the campus. This is intended for busy students and others who don’t have time to cook, or who want to have friends over for dinner. Each day there will be five different meals available, each consisting of a protein, a vegetable and a salad. A customer can order only the fixed meals; no substitutions of dishes are allowed. An order consists of a number of meals, each of which can be chosen from any of the five menus available. Customers can order online or over the phone. Payments are by PayPal, collected by the delivery person using a smartphone.
Your application will be used by customers to order and pay for their meals, and will be convenient for an end user to employ for the entire order process. Images of the meals should be shown. It will allow for the order of any number of currently offered meals, payment will be simulated, and the order will be submitted to the store.
2. GWDeals: Design a database to support a discount shopping service. GWD provides discounts on items that students like to purchase. These could be special deals on textbooks, school supplies, computers, or even tickets to popular entertainment in town. Each day emails of the day’s deals are sent to subscribers, and subscribers can order online, or they can order by telephone. Their orders are delivered by a delivery service such as UPS, or online for items such as tickets.
Your application is to be used by shoppers to order deals. The user should be able to view available deals, including images of each deal. The user views the deals, selects them and orders them from the store using the application. Payment can be simulated.
3. GWWheels: Design a database for scheduling a bus company that operates between Washington and New York City. The buses travel between the two cities, making two stops in each city. In Washington, it picks up and discharges passengers in Chinatown and at GWU. In New York it picks up and discharges passengers at Times Square and Madison Square Garden. Your database is to support ticket sales and scheduling and management of the fleet of buses. Seats are not reserved. There are two kinds of buses, some with 30 seats and some with 50 seats.
Your application is designed for passenger ticket sales. A passenger should be able to use the app to purchase tickets for any scheduled trip. They should be able to browse available trips, choose one, then purchase a ticket if seats are available. They should also be able to refund an advance ticket purchase. Payment can be simulated.
4. GWSeals: Design a database that manages the inventory of a seal manufacturer–that is, machines that seal envelopes or apply security seals to envelopes. They have inventories of parts that they use in the machines that they manufacture as well as finished machines to be shipped out. Your database will be used for ordering parts for manufacturing and for scheduling manufacturing to keep the inventory up to required levels. The company’s customers, who are themselves retailers, will place orders that will be entered into your database that will be used to schedule production. The customer gives an order time, and we promise to deliver within ten minutes before or after that time. If we fail to meet that deadline the price is reduced by half.
Your application is for customer orders. A customer can browse the machines that are available, choose one and purchase it, and schedule delivery. Your application should be attractive and easy to use, and show images of the machines. Payments can be simulated.
5. GWEELS: Design a database to manage the operation of an eel bar, that serves eels in six different ways: smoked, jellied, grilled, fried, baked and as eel sushi. They also sell beer and sake. Only American eels, native to the nearby Potomac River, are served. The bar provides only delivery service; it does not have any tables or chairs. Orders are placed over the Web, and the food and drinks are delivered. You are to provide a method for a patron to enter an order for any of the six types of eels and beer (one type only, Fat Tire) and sake (one type only, served hot). A table can order multiple dishes and drinks. Management is to be able to produce reports of total sales and number of each item ordered by the week and month.
Your application will be used by customers to order. Customers can order, pay for the order, and arrange delivery using your app. Payment can be simulated. The app is to show images of the menu items.
6. GWHair: Design a database for a hairstyling salon. Your database will include work schedules for stylists and will be used to support appointments. A customer may request any stylist or a specific one. There are several different kinds of appointments that take different lengths of time to complete, and have different prices. There are two different price schedules for hair stylists; the more experienced stylists charge higher prices. Your database will also track customer charges and be able to track daily revenue of the salon and daily earnings for each stylist, who are paid 50% of the fees that they take in.
Your application will be used by customers to schedule appointments. The customer selects the services needed and the provider, and chooses a time. The application confirms the appointment. The application should display images of the services performed.
7. GWAir: Design a database for an airline. The airline flies between Dulles and Newark. It offers economy and first-class fares. The airline offers a frequent flyer program that your database is also to support. Your database will be used for ticketing and payroll for the airline.
Your application will be used by customers to make reservations. They can view the schedule, select a flight, and make a reservation if there are seats available. The application is also to give credits for miles to customers who belong to the frequent flier program, which they can join through the application. They can also use miles to reduce the fare they pay, even getting free fares, using a value of one cent per frequent flyer mile. If they do use miles, of course their mileage balance is reduced.
8. GWCare: Design a database for an auto detailing shop. The shop offers three different levels of detailing, at three different prices. Each different level takes a different time to complete. Customers bring their cars to the GW garage to be detailed. Employees are paid on a commission basis; they each receive a portion of what customers are charged for a detail job. Your database supports scheduling of cars and employees, payment of employees and tracking total income of the service. Employees work on a part-time basis; each employee provides a schedule to the business showing when they are available to work.
Your application is for customer orders. A customer can order detailing for any cars the customer owns. The application must support customer registration, and registration for all the customer’s cars. The customer chooses which services, and when they are available, and makes and confirms the reservation. Your app should display images portraying the services that are offered.
9. GWRare: Design a database for a steakhouse. The database is to support both reservations and ordering. The reservation system knows the hours the restaurant is open, how many tables are available, the capacity of each, and reservations that have been made. A client can make reservations for lunch or dinner for a party of one to eight. The tables are for two to eight diners. Customers use a tablet to order at the table. When an order is taken, the order can be only for a fixed menu from the database. The restaurant serves rare steak, in large and very large sizes, and the diner can choose baked potato or Caesar salad, or both, as an accompaniment. Drinks are served at no charge. The application calculates the check at the end of the meal, adding sales tax and a standard tip of 22% for the waiter.
Your application will display images of menu items, and allow a customer to place an order and pay for it. Payment is at the time of order for all meals. The app adds in the tip and tax and collects using a simulated payment. The customer can also order drinks through your application, which are delivered at no charge. Of course, the dinner has to be quite expensive, to cover the cost of drinks!
10. GWCars: Design a database for a car rental agency. The agency offers three types of cars, discount, standard and premium, each with a different rate. Rentals are by the hour, day, or week. Customers can make reservations. Your application will support the car inventory and scheduling of rental reservations and tracking of rentals. Your application will also keep track of income and produce income statements for the day, week, month and year.
Your application will be used by customers to reserve and rent cars. The customer needs to register through your application; having done so, they can browse the available cars and when they are available. Of course, images of the cars are shown. When the customer makes a reservation, the application confirms the reservation.
11. GWBars: Design a database to keep track of local bars. Bar operators should be able to post their specials, and users should be able to log in and search bars for their open hours as well as featured specials. Users should be able to make reservations for cocktails or dinner.
12. GWDentist: Design a database for a dentist. The dentist has hours, and has three procedures: fillings, cleaning and checkup. The filling procedure requires an assistant and a dentist. Cleaning requires only a hygienist. Checkup requires only a dentist. Patients may schedule a cleaning or a cleaning and checkup. As a result of a checkup, the dentist may schedule a filling procedure. Cleanings take a half hour; a checkup takes 15 minutes. A filling takes one hour. Your database is to support the scheduling of dentists, hygienists and assistants. Each dentist, hygienist and assistant designate hours when they will work. Your database will track patients, including procedures performed, bills sent and payments received.
Your application will be used by patients to schedule appointments. They indicate the services they want, and then they are told which providers are available at which times, and they can make and confirm an appointment. Of course, patients must register using your application.
12. GWLoans: Design a system for payday loans. The company makes loans to individuals and holds their ID until the load is paid off, including all credit cards, driver’s license, student ID, all of it. Your database is to track the original value of each loan, borrower name and complete contact information. The original value of the loan and all payments are to be tracked, along with the total outstanding value of the loan. Each loan will have a monthly interest rate that is set at the time the loan is made. Interest rates per month will vary between 1% (ha! almost never) up to 30%. Each loan is to have terms established when it is made, that a certain amount will be paid at some time interval. We need to be able to see how much of our loan portfolio is performing well and how much is past due. And we need to track the total amount due on a daily basis. So that we can simulate today’s date, you will have to be able to input today’s date so that we can simulate time passing.
Your application will be used by the poor and unfortunate customers of this company. They can find out the current balance on any loans that they owe, the interest rate on each, and make payments on any of them. Payments can be simulated. Your application should show some images to make the experience more palatable for the sad borrowers.
13. GWBones: There are many dogs living in homes near GWU, and it’s hard to find good bones for them in local stores. So GWBones will allow customers to order dog bones for their dogs. There are four different sizes, from one inch to 6 inches in length. You can order them raw and frozen, or precooked, also frozen. They are in packages of six. You can order some for delivery, or you can subscribe for a certain number of packages per month. The store buys the bones prepackaged from a supplier. They need to keep an inventory so that they can meet their delivery requirements. They need to pay two people to run the store. Delivery people get paid 10% of the value of what they deliver. The system needs to pay all the employees in the store and the delivery people.
Your application will be for customer orders. Customers can order the bones in the size desired, and frozen or not frozen. The application will check what’s in inventory, and finalize the order if inventory is available, and then change the inventory on hand to reflect the order. Payment can be simulated. Your application is to set up delivery.
14. GWStones: Students who attend GWU like to take away souvenirs. So do parents and even campus visitors. This business gives them a piece of granite that has any inscription they like carved into it. The carving is done by a small custom laser inscriber, driven by a computer, in the GWStones store. People can walk right in and order a GWStone; they come in various sizes. Typically, they are picked up the day after they are ordered, but for an extra 20% the order can be rushed and done right away. You are to design a database for tracking the entire GWStones operation. Take the orders, including size of the stone, the inscription (different size stones can have inscriptions of different lengths), cost, regular or rush, promised delivery time. Then indicate the date and time the customer picks up the order and the amount paid. You need to inventory blank stones in different sizes and order more blanks when your stock drops below a 2-day supply for any size.
Your application is for customer orders. Customers can browse images of stones and select the size of interest. They can enter their inscription and fit it to the stone they’ve chosen–if the stone isn’t big enough, they can upsize. And they can order, and pay with the order, and arrange delivery.
15. GWTones: At the end of a long day, nothing is nicer than relaxing to pleasant music from a small choir; that choir is GWTones. The choir is available to sing at company and other events, on and off campus. They are popular at fraternity parties on campus. A booking can be for just three singers and a guitar player, six singers, a guitar player and drummer, or 12 singers, who sing a Capella. For bookings off campus, a transportation charge is added, depending on the size of the group booked and the distance from campus. You will need to determine how this charge is assessed. The booking price is by the hour and is different for the three different types of groups. There are 24 members of the group who sing, three guitar players and two drummers. Each of them indicates their hours of availability, and when a booking is made the person making the booking can automatically find which sizes of groups are available at what times, and what the cost will be. Then the customer can book the group online.
Your application will be used by customers to order performances. They can browse images of the different compositions of groups, check for availability, schedule and pay in advance. Your application will allow a customer to purchase and will also update other tables to show the booking and revenue.
16. GWDogs: Students staying in apartments or dormitories may miss the companionship of their dogs back at home. While it’s not possible to completely replace a childhood companion, this service provides rental dogs that will stay with a student for as long as one week. The service has only corgis, that are Cardigan corgis and Pembroke corgis, that are medium-sized dogs. Students can reserve a dog in advance, for a period up to one week. The cost is $100 per day, or $500 per week. A 20% deposit is charged to reserve a dog. That deposit is 50% refundable up to 2 days before the reservation, at which time it is no longer refundable. The service provides dog food along with the rental and keeps track of the amount of dog food it has in stock. While a dog is being rented, every two days a representative of GWDogs pays a visit to the place where the dog is staying to be sure everything is OK. These visits must be scheduled for employees. A monthly report showing revenue and profit is produced, including salaries, dog food purchases as expenses, and dog rental as income.
Your application is used to reserve a dog and make the (simulated) deposit for the reservation, and to pick up the dog. The application checks for availability, and shows the customer an image of the dog. The customer can reserve the dog, and get the reservation confirmed on the app. The app is used again when the customer picks up the dog.
17. GWCompost: A composting service has been started for students who do not want to throw food scraps into trash. The service calls at apartments or dormitory rooms and empties a plastic bucket of compost. The number of buckets emptied is tracked for each subscriber. The cost to subscribe is $10 per month, plus $2 for each bucket of scraps that is picked up. The food scraps are taken to a plant in Arlington, which charges a fee of $10 per ton to take the scraps. Monthly bills are sent to each subscriber. Employees are paid 50 cents for every bucket picked up. A monthly profit and loss report is produced that shows employee compensation, dumping fees, other costs, and subscriber revenue.
Your application is to support customer ordering and service. Customers can subscribe to the service, and they can pay through your app (simulated). They can also request a bucket pickup through your application. Your app should show cheerful images to encourage customers to order, not a photo of half-rotted compost or a bucket of slops being dumped out.
Previous 1-3 Extra GWGroceries:
A small grocery store is opening, and needs a number of data models for various functions. The store will sell a variety of food items such as fruits, vegetables, dairy products meats and beverages. The store has a warehouse in the back of the store.
18. Inventory Management: Design a data model to track the inventory of everything the store sells, including their names, quantities in stock, and prices. The application should track quantities of items in the store and in the warehouse.
Write an application to conduct an inventory of all items that are in storage and on the shelves. This application is to be used by the person taking the inventory. They enter an SKU number, and an image of the item is shown. They enter their count and move to the next item. They can use this app in the warehouse and the store. When the inventory has been taken, the report produces a report of the total inventory, showing the items in the store and the warehouse, and the total value of each.
19. Customer Management: The grocery store wants to keep track of its customers and their purchases. Customers should be identified by their names, contact information, and loyalty program IDs if applicable. Design a data model to store customer information and their purchase history, including the items purchased, quantities, and dates of purchase.
Your application will be used by the manager of the customer relations department, who will use built-in queries to find customers to receive promotional emails with offers from the store. Your application will send emails to these customers.
20. Supplier Management: To maintain a steady supply of goods, the grocery store needs to manage its relationships with suppliers. Each supplier provides different types of products, and the store needs to keep track of supplier names, contact information, and the products they supply.
Design a data model to represent the relationship between suppliers and the products they supply to the store. You will write applications to order from suppliers and to maintain the supplier table.
21. Sales and Transactions: The grocery store needs a system to process sales transactions and generate receipts for customers. Each transaction consists of multiple items purchased by a customer. Design a data model to capture sales transactions, including the items sold, quantities, prices, and payment methods.
Your application will be used by the cashier to produce the check the customer receives at checkout. The cashier will enter SKU numbers or choose from an image of the item, then will enter the quantity. Your application will used a stored price to compute the cost, and then will add sales tax and produce the customer’s final receipt. Payment will be simulated.