Business Scenarios

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 may 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:

  1. Name of problem and requirements in bullet form
  2. Chen ERD
  3. Relational ERD
  4. 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.

  1. 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 fixed menu 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.

Develop a two-screen Oracle APEX application:

Screen 1: Menu & Order Selection

  • Display the current day’s available meals with images, descriptions, and prices
  • Allow users to select meals and quantitiesMenu Selection Screen:
  • Display the day’s five available meal options
  • Include the following for each meal:
    • Meal name and brief description
    • Clear image of the plated meal
    • List of components (protein, vegetable, salad)
    • Price per meal
    • Special dietary indicators (vegetarian, gluten-free, etc.)
  • Allow customers to select quantity for each desired meal
  • Include a running order total that updates dynamically
  • Display estimated delivery time based on current order volume
  • Include a search function to filter meals by ingredient or dietary restriction
  • Display GWMeals’ delivery radius (2-mile campus radius)
  • Enable “Add to Order” functionality for selected meals
  • Show customer’s delivery address (if previously saved)
  • Order Confirmation Screen:
  • Create a clear order summary showing selected meals and quantities
  • Calculate and display subtotal, delivery fee, and total price
  • Allow customers to modify quantities or remove items
  • Collect delivery information:
    • Delivery address within the 2-mile radius
    • Customer phone number
    • Special delivery instructions
  • Display estimated delivery time
  • Include option to schedule delivery for later in the day
  • Provide PayPal payment information for the delivery person
  • Generate an order confirmation number
  • Include a “Track Order” option to monitor delivery progress
  • Provide a running total of the order cost
  • Include ability to add customer information (new customers) or select existing customer
  • Navigation to checkout/payment screen

Screen 2: Order Review & Payment

  • Order tracking number generation
  • Display order summary with selected meals and quantities
  • Show delivery address and estimated delivery time
  • Include a payment simulation interface (PayPal)
  • Confirmation mechanism after payment

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.

Develop a two-screen Oracle APEX application:

Menu Screen:

Create a responsive interface that displays the current day’s deals

Include the following for each deal:

  • Clear product image
  • Product name and description
  • Original price and discounted price
  • Discount percentage
  • Availability (quantity remaining)
  • Product category (textbooks, supplies, computers, entertainment, etc.)
  • Implement filtering options by category and price range
  • Allow users to search for specific items
  • Include a “Deal of the Day” featured section
  • Enable users to add items to their shopping cart
  • Display the cart total prominently

Order/Checkout Screen:

Create a clean order summary showing selected items

  • Calculate and display subtotal, tax, and final price
  • Allow users to modify quantities or remove items
  • Collect delivery information:
  • Physical address for shipped itemsDaily Deals Screen:
  • Create a responsive interface that displays the current day’s deals
  • Include the following for each deal:
    • Product image
    • Product name and description
    • Original price and discounted price
    • Discount percentage
    • Available quantity
    • Product category (textbooks, supplies, computers, entertainment, etc.)
  • Implement filtering by product category
  • Allow users to search for deals by keyword
  • Include a “Featured Deal” section for the most attractive discount
  • Enable users to add items to their cart
  • Display cart total and item count prominently
  • Provide subscription option for users to receive daily deal emails
  • Order Processing Screen:
  • Create a clean order summary showing selected items
  • Calculate and display subtotal, discount amount, and final price
  • Allow users to modify quantities or remove items
  • Collect customer information:
    • Name and contact number
    • Email address (required for digital items)
    • Delivery address (required for physical items)
    • Preferred delivery method (UPS, digital delivery)
  • Provide order tracking number generation
  • Include option to save order history for registered users
  • Generate order confirmation with estimated delivery date
  • Allow placing orders via telephone (staff interface)
  • Include a downloadable/printable receipt option
  • Email address for digital items (tickets, etc.)
  • Simulate payment processing with multiple payment options
  • Provide order confirmation with estimated delivery date
  • Generate a printable/downloadable receipt

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.

Application Requirements:Bus Schedule/Booking Screen:

Create a responsive interface that displays available bus trips between Washington and New York City

Include the following for each trip:

  • Bus number and type (30-seat or 50-seat)
  • Departure and arrival cities (Washington or New York)
  • Specific stop locations (Chinatown, GWU, Times Square, or Madison Square Garden)
  • Departure and arrival times
  • Date of travel
  • Available seats remaining
  • Ticket price
  • Trip duration

Implement filtering options by:

  • Origin and destination stops
  • Date of travel
  • Departure time range
  • Bus type preference

Allow users to search for specific trips Include a “Featured Routes” section highlighting popular time slots Enable users to select trips and add tickets to their shopping cart Display the cart total prominentlyTicket Purchase/Confirmation Screen:

Create a clean order summary showing selected trips

Calculate and display:

  • Subtotal for all tickets
  • Any applicable fees
  • Final price

Allow users to modify:

  • Number of tickets for each selected trip
  • Remove trips from order

Collect passenger information:

  • Name
  • Contact phone number
  • Email address
  • Payment details

Provide booking confirmation with:

  • Unique booking reference number
  • Trip details (route, date, time)
  • PDF ticket generation option
  • Email confirmation option

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.

Application Requirements

Inventory Management Screen:

  • Create a responsive interface that displays the current inventory status
  • Include the following for each part/machine:
    • Part/Machine ID and name
    • Category (part or finished machine)
    • Current quantity in stock
    • Minimum required inventory level
    • Status indicator (sufficient, low, critically low)
    • Manufacturer/supplier information (for parts)
    • Cost per unit
  • Implement filtering options by category, inventory status, and supplier
  • Allow users to search for specific parts or machines
  • Include a “Critical Inventory” section highlighting items below minimum threshold
  • Enable users to initiate manufacturing orders for low-stock machines
  • Enable users to place purchase orders for low-stock parts
  • Display manufacturing schedule prominently

Order Processing Screen:

Create a clean customer order interface showing available machines Include the following for each order:

  • Customer information (retailer name, contact details)
  • Machine type and quantity requested
  • Requested delivery date and time
  • Delivery window calculation (10 minutes before/after requested time)
  • Order status (pending, in production, shipped, delivered)

Calculate and display regular price, potential discount price, and current applicable price Allow staff to update order status and track progress Implement deadline tracking with visual indicators for:

  • On-track orders
  • At-risk orders approaching deadline
  • Orders that missed deadline (50% discount applied)

Generate shipping documentation with delivery requirements Provide order confirmation with estimated completion time

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.

Appliocation Requirements:

Order Entry Screen:

  • Create a user-friendly interface for customers to place delivery orders
  • Display menu items clearly organized by categories:
    • Eel Dishes (smoked, jellied, grilled, fried, baked, sushi)
    • Beverages (Fat Tire beer, hot sake)
  • Include for each menu item:
    • Item name and brief description
    • Price
    • Option to select quantity
    • Special preparation instructions field (optional)
  • Implement a running order total that updates as items are added
  • Collect customer information:
    • Name
    • Delivery address
    • Phone number
    • Email address (for order confirmation)
  • Display estimated delivery time
  • Include option to schedule delivery for later time/date
  • Allow customers to review order before submission
  • Provide confirmation message after successful order placement

Management Report Screen:

Order Entry Screen:

  • Create a user-friendly interface for customers to place delivery orders
  • Display menu items clearly organized by categories:
    • Eel Dishes (smoked, jellied, grilled, fried, baked, sushi)
    • Beverages (Fat Tire beer, hot sake)
  • Include for each menu item:
    • Item name and brief description
    • Price
    • Option to select quantity
    • Special preparation instructions field (optional)
  • Implement a running order total that updates as items are added
  • Collect customer information:
    • Name
    • Delivery address
    • Phone number
    • Email address (for order confirmation)
  • Display estimated delivery time
  • Include option to schedule delivery for later time/date
  • Allow customers to review order before submission
  • Provide confirmation message after successful order placement

Management Report Screen:

  • Create a dashboard interface for viewing sales data
  • Implement filtering options:
    • By date range (daily, weekly, monthly)
    • By menu item category (eel dishes or beverages)
    • By specific menu item
  • Display the following metrics:
    • Total sales revenue
    • Number of orders processed
    • Quantity sold for each menu item
    • Top-selling items
    • Peak ordering times
  • Present data in both tabular format and visual charts
  • Include export functionality for reports (PDF, Excel)
  • Provide option to view customer information for each order
  • Allow searching/filtering orders by customer
  • Calculate and display average order value

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.

Application Requirements:

Appointment Booking Screen:

Create a responsive interface that allows customers to book hair styling appointments

Include the following for appointment booking:

  • Calendar view showing available time slots
  • Drop-down list of all stylists with option to select “Any Available Stylist”
  • Display of stylist experience level (Junior/Senior)
  • List of service types with descriptions
  • Duration and price information for each service (varies by stylist level)
  • Customer information collection (name, phone, email)
  • Special requests or notes field
  • Confirmation of appointment details before submission
  • Email notification option for appointment confirmation

Stylist Dashboard Screen:

Create a comprehensive view of stylist schedules and financial information

Include the following features:

  • Daily appointment schedule for each stylist
  • Customer details for upcoming appointments
  • Service types and duration for each appointment
  • Revenue tracking showing daily/weekly totals
  • Calculation of stylist earnings (50% of service fees)
  • Ability to mark appointments as completed
  • Option to add product sales to customer bills
  • Summary of completed services and revenue
  • Filter view by date range or stylist
  • Quick-view of available time slots for booking additional appointments
  • Daily salon revenue totals and breakdown by stylist

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.

Flight Booking Screen:

Create a responsive interface that displays available flights between Dulles and Newark

Include the following for each flight:

  • Flight number and aircraft type
  • Departure and arrival times
  • Flight duration
  • Available seats in economy and first class
  • Base fare for each class (economy and first class)
  • Baggage allowance for each class
  • Implement filtering options by date, time, and price range
  • Allow users to search for specific flights by flight number
  • Include a “Special Offer” featured section
  • Enable users to select seats and add flights to their booking cart
  • Display the cart total prominently
  • Provide option to enter frequent flyer number for points/discounts

Checkout/Passenger Information Screen:

Create a clean booking summary showing selected flights

  • Calculate and display base fare, taxes, fees, and final price
  • Allow users to modify seat selection or remove flights
  • Collect passenger information:
    • Full name as it appears on ID
    • Contact information (phone, email)
    • Frequent flyer number (optional)
    • Special requests (wheelchair assistance, dietary needs, etc.)
  • Simulate payment processing with multiple payment options
  • Provide booking confirmation with e-ticket number
  • Generate a printable/downloadable boarding pass
  • Display frequent flyer points earned from this booking

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.

Application Requirements:

Scheduling Screen:

Create a responsive interface that displays the current schedule of detailing appointments

Include the following for each appointment:

  • Customer name and contact information
  • Vehicle information (make, model, year, license plate)
  • Selected detailing package (Basic, Standard, Premium)
  • Appointment date and time
  • Estimated completion time
  • Assigned employee(s)
  • Current status (Scheduled, In Progress, Completed)

Payment/Tracking Screen:

Create a clean transaction summary showing completed jobs

Calculate and display:

  • Service fee based on detailing package
  • Employee commission amount
  • Shop revenue portion
  • Tax collected
  • Final customer payment amount

Allow managers to:

  • Update job status
  • Record customer payments
  • Process employee commissions
  • Track employee availability and schedule
  • View daily/weekly/monthly revenue reports

Generate a printable/downloadable receipt for customers Provide commission statements for employees

Implement filtering options by date range and status Allow staff to search for specific customers or vehicles Include a “Today’s Schedule” featured section Enable staff to add new appointments Display available employee slots prominently

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.

Application Requirements:

Reservation Screen:

Create a responsive interface that displays available reservation times

Include the following for reservation booking:

  • Calendar date picker for selecting reservation date
  • Time slot selection based on restaurant operating hours
  • Party size selector (1-8 people)
  • Customer contact information fields (name, phone, email)
  • Table assignment display showing table number and capacity
  • Special requests/notes text area
  • Reservation confirmation number generation
  • Option to modify or cancel existing reservations
  • Display of restaurant hours and peak times
  • Implement filtering of available times based on party size
  • Color-coded indicators for availability status
  • Reservation summary before final confirmation

Order/Payment Screen:

Create a clean ordering interface for table service

Include the following for meal ordering:

  • Table number association with active reservation
  • Steak size selection (large or very large)
  • Steak preparation selection (rare only)
  • Side selection options (baked potato, Caesar salad, or both)
  • Complimentary drink selection
  • Special preparation instructions text area
  • Order summary with item details
  • Calculate and display subtotal
  • Add sales tax calculation (displayed separately)
  • Apply standard 22% gratuity (displayed separately)
  • Display final total amount
  • Order confirmation with estimated preparation time
  • Generate a printable/downloadable receipt
  • Option to call server for assistance

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.

Rental Reservation Screen:

Create a responsive interface that displays available vehicles for rental

Include the following for each vehicle:

  • Clear vehicle image
  • Make, model, and year
  • Vehicle type (discount, standard, premium)
  • Hourly, daily, and weekly rates
  • Current availability status
  • Features (automatic/manual, GPS, car seat, etc.)
  • Fuel efficiency

Implement filtering options by vehicle type, availability, and price range Allow users to search for specific vehicles by make or model Include a “Featured Vehicles” section Enable users to select rental duration (hour, day, week) Allow users to select pickup and return dates/times Display the estimated total cost prominently

Rental Management Screen:

Create a clean rental summary showing reserved and active rentals

Calculate and display daily, weekly, monthly, and yearly income reports Allow staff to update rental status (reserved, active, completed) Track customer information:

  • Name and contact information
  • Driver’s license number
  • Payment information
  • Rental history

Generate income statements by selected time period Manage vehicle inventory (add/remove vehicles, update status) Track vehicle maintenance schedules Provide rental confirmation with reservation details Generate printable/downloadable rental agreements and receipts

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.

Application Requirements:

Bar Search Screen:

Create a responsive interface that displays local bars with their current information

Include the following for each bar:

  • Bar name and logo
  • Address and contact information
  • Current operating hours (open/closed status)
  • Average rating (if applicable)
  • Featured specials of the day
  • Bar category (sports bar, cocktail lounge, brewpub, etc.)
  • Implement filtering options by location, category, and current open status
  • Allow users to search for specific bars or specials
  • Include a “Featured Bars” section highlighting popular venues
  • Enable users to select a bar to view more details
  • Display user login/logout status prominently

Reservation Screen:

Create a detailed bar view showing complete information about the selected bar

Display comprehensive specials list with:

  • Drink/food name and description
  • Regular price and special price
  • Special availability (time period)
  • Allow users to make reservations by specifying:
    • Date and time
    • Number of guests
    • Reservation type (cocktails or dinner)
    • Special requests/notes
  • Show availability calendar with open slots
  • Collect user contact information
  • Provide reservation confirmation with details
  • Generate a reservation confirmation number

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.

Application Requirements:

Appointment Scheduling Screen:

Create an intuitive interface that displays the dental office’s weekly schedule

Include the following for each appointment slot:

  • Date and time (in 15-minute increments)
  • Availability status (available/booked)
  • Required staff (dentist, hygienist, assistant)
  • Procedure type (cleaning, checkup, filling)
  • Duration (30 minutes for cleaning, 15 minutes for checkup, 60 minutes for filling)

Implement filtering options by:

  • Date range
  • Procedure type
  • Specific provider (dentist, hygienist, assistant)
  • Patient name

Allow office staff to:

  • View available time slots based on staff working hours
  • Check provider availability before scheduling
  • Book new appointments by selecting procedure type and time
  • Schedule follow-up appointments (fillings) after checkups
  • Modify or cancel existing appointments
  • Display patient contact information for appointment reminders

Patient Management Screen:

Create a comprehensive patient profile interface

Include the following for each patient:

  • Personal information (name, contact details, insurance)
  • Medical history and notes
  • Appointment history with procedure details
  • Upcoming scheduled appointments

Implement financial tracking:

  • List of procedures performed with dates
  • Associated costs for each procedure
  • Bills generated and their status (pending, sent)
  • Payments received and outstanding balances
  • Payment history with dates and amounts
  • Option to generate new bills

Allow office staff to:

  • Search for patients by name or phone number
  • Update patient information
  • View patient procedure history
  • Record new payments
  • Generate billing statements
  • Track outstanding balances
  • Schedule follow-up appointments directly from this screen

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.

Application Requirements:

Loan Management Screen:

Create a responsive interface that displays all active loans

  • Include the following for each loan:
    • Borrower’s full name
    • Loan status (current, past due)
    • Original loan amount
    • Current outstanding balance
    • Monthly interest rate
    • Payment term details (amount and frequency)
    • Date of next payment due
    • List of ID documents held as collateral
  • Implement filtering options by loan status and date range
  • Allow users to search for specific borrowers
  • Include a “Loans Due Today” featured section
  • Enable users to add new loans or record payments
  • Display the total loan portfolio value prominently
  • Include a “Simulation Date” field to adjust the current date for testing

Payment/Borrower Detail Screen:

Create a detailed view of an individual loan showing complete history

  • Calculate and display loan details:
    • Original loan amount
    • Total interest accrued to date
    • Total payments made to date
    • Current outstanding balance
  • Allow users to record new payments
  • Display complete borrower information:
    • Full name and contact details (address, phone, email)
    • List of ID documents currently held
    • Payment history with dates and amounts
  • Track loan performance metrics (days past due, if applicable)
  • Provide payment schedule showing upcoming payments
  • Generate a printable/downloadable loan statement

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.

Application Requirements:

Order Screen:

Create a responsive interface that displays the available dog bone products

Include the following for each bone product:

  • Clear product image
  • Bone size (1 inch to 6 inches)
  • Preparation type (raw frozen or precooked frozen)
  • Price per package (6 bones per package)
  • Package quantity in stock
  • Option to select one-time delivery or subscription
  • Subscription options (packages per month)
  • Enable customers to add items to their cart
  • Display the cart total prominently
  • Include filtering options by bone size and preparation type
  • Allow customers to search for specific products

Checkout/Delivery Screen:

Create a clean order summary showing selected items

  • Calculate and display subtotal, tax, and final price
  • Allow customers to modify quantities or remove items
  • Collect customer information:
    • Name and phone number
    • Delivery address
    • Preferred delivery time/date
  • Provide options for one-time delivery or subscription setup
  • For subscriptions, allow selection of:
    • Number of packages per month
    • Preferred delivery days
    • Subscription duration
  • Process payment with multiple payment options
  • Generate order confirmation with estimated delivery date
  • Display delivery person assigned to their order

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.

Application Requirements:

Order Entry Screen:

Create a responsive interface that allows staff to take customer orders

  • Include the following fields for each order:
    • Customer information (name, email, phone number)
    • Stone size selection (with available sizes from inventory)
    • Inscription text with character counter (varying by stone size)
    • Order type (regular or rush)
    • Automatically calculated cost based on size and order type
    • Automatically generated promised delivery time
    • Order date and time (auto-filled)
  • Display real-time inventory levels for each stone size
  • Show warning indicators when inventory drops below 2-day supply
  • Include a preview of the inscription layout
  • Enable staff to submit and save the order
  • Generate a printable order receipt with pickup information

Order Management Screen:

Create a comprehensive view of all orders and inventory status

  • Include filterable/sortable order list with:
    • Order ID and date
    • Customer information
    • Stone size and inscription summary
    • Order type (regular/rush)
    • Status (pending, ready, completed)
    • Promised delivery date/time
    • Pickup date/time (when applicable)
    • Payment status and amount
  • Allow staff to update order status when completed
  • Record pickup date/time and payment when customer retrieves order
  • Display inventory dashboard showing:
    • Current stock levels by stone size
    • Highlighted warnings for low inventory (below 2-day supply)
    • Ability to record new inventory when received
    • Basic reporting on sales trends and popular sizes
  • Enable creating purchase orders for new blank stones

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 how far the group must travel to reach the engagement. 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.

Application Requirements:

GWTones Booking Application

Booking Inquiry Screen:

Create a responsive interface that allows customers to search for available GWTones performances

Include the following search criteria:

  • Date and time range for the event
  • Event location (on-campus or off-campus with distance specification)
  • Preferred group size selection (3 singers with guitar, 6 singers with guitar and drummer, or 12 singers a cappella)
  • Event duration in hours

Display search results showing:

  • Available time slots based on member availability
  • Group size options that can be accommodated
  • Base hourly rate for each group size
  • Transportation fee calculation (for off-campus events)
  • Total estimated cost (hourly rate × duration + transportation fee if applicable)

Include clear visual indicators for:

  • Availability status (available, limited availability, unavailable)
  • On-campus vs. off-campus pricing differences
  • Transportation fee breakdown based on distance and group size

Allow customers to:

  • Sort results by time, cost, or group size
  • View different date options using a calendar interface
  • Get detailed information about each performance package

Booking Confirmation Screen:

Create a clean booking form showing selected performance details

Display comprehensive booking information:

  • Selected date and time
  • Event duration
  • Group size and composition (number of singers, guitarists, drummers)
  • Event location details
  • Base hourly rate
  • Transportation fee (if applicable)
  • Total booking cost

Include a section for customer information:

  • Customer name and contact details
  • Organization/event name
  • Special requests or song preferences
  • Setup requirements

Provide a summary of terms and conditions:

  • Cancellation policy
  • Payment requirements
  • Setup time expectations

Enable booking submission with:

  • Payment method selection
  • Confirmation email generation
  • Receipt/invoice download option
  • Booking reference number

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.

Application Requirements:

Dog Reservation Screen:

Create a responsive interface that displays available dogs for rental

Include the following for each dog:

  • Clear dog image
  • Dog name and corgi type (Cardigan or Pembroke)
  • Dog age and brief description/temperament
  • Current availability status (available/reserved)
  • Earliest available date if currently reserved
  • Special notes (if any)

Implement filtering options:

  • By corgi type (Cardigan/Pembroke)
  • By availability date range
  • By special characteristics (good with children, etc.)

Allow users to search for specific dogs by name Include a calendar view showing available rental periods Enable users to select rental duration (1-7 days) Calculate and display rental cost and required deposit Display terms of deposit refund policy prominently

Checkout/Confirmation Screen:

Create a clean reservation summary showing selected dog and dates

Calculate and display:

  • Daily rate ($100)
  • Total rental cost
  • Deposit amount (20%)
  • Refund deadlines and amounts

Collect customer information:

  • Student ID and contact details
  • Rental location address
  • Emergency contact
  • Preferred visit times for GWDogs representatives

Schedule mandatory check-in visits (every 2 days) Process deposit payment with multiple payment options Generate a confirmation email with reservation details Allow cancellation with appropriate refund calculations Display a countdown to reservation date

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.

Subscriber Management Screen:

Create a responsive interface that displays current subscribers and their pickup details

Include the following for each subscriber:

  • Full name and contact information
  • Apartment/dormitory address and room number
  • Subscription start date
  • Current month’s bucket count
  • Current month’s charges to date
  • Payment status (paid/unpaid)

Implement filtering options by location and payment status Allow staff to search for specific subscribers Include a “Today’s Pickups” featured section showing scheduled collections Enable staff to record new bucket pickups for subscribers Display monthly revenue totals prominently

Reporting Screen:

Create a clean financial summary showing monthly business performance

Calculate and display:

  • Total subscriber revenue (subscription fees + bucket fees)
  • Total employee compensation (based on bucket pickups)
  • Dumping fees (based on weight of scraps collected)
  • Other operational costs
  • Net profit/loss

Allow staff to view data by month or date range Generate the following reports:

  • Monthly profit and loss statement
  • Employee compensation report
  • Subscriber billing summary
  • Collection route optimization

Provide export options for reports in PDF and spreadsheet formats Include visual charts showing key performance metrics over time

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.

Application Requirements:

Inventory Dashboard Screen:

Create a responsive interface that displays the current inventory status

Include the following for each product:

  • Product name and description
  • Product category (fruits, vegetables, dairy, meats, beverages)
  • Current price and cost
  • Quantity available in store
  • Quantity available in warehouse
  • Total inventory value
  • Low stock indicator (highlighted when below threshold)

Implement filtering options by:

  • Category
  • Location (store/warehouse)
  • Stock level (all/low stock)

Inventory Action Screen:

Create a clean interface for managing selected inventory items

Allow users to:

  • Transfer stock between store and warehouse
  • Update quantities (add new stock or adjust for spoilage/damage)
  • Modify product details (price, description, category)
  • Set minimum threshold levels for automatic reordering

Include validation to prevent negative quantities Provide confirmation for all inventory actions Generate inventory movement history for selected item Display alerts for items approaching expiration dates Include reporting options (printable inventory reports by category/location)

Allow users to search for specific items Include a “Critical Inventory” section highlighting items below minimum threshold Enable users to select items for restocking or transfer Display inventory metrics prominently (total items, value, low stock count)

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.

Application Requirements:

Customer Profile Screen:

Create a responsive interface that displays customer information and details

  • Include the following customer data:
    • Full name (first and last name)
    • Contact information (phone number, email address)
    • Physical address (for deliveries and mailings)
    • Loyalty program ID (if enrolled)
    • Account creation date
    • Loyalty points balance (if applicable)
  • Allow staff to add new customers to the system
  • Enable search functionality to find existing customers by name, phone, or loyalty ID
  • Include a “Loyalty Status” section showing current tier/benefits
  • Provide option to edit customer information
  • Display summary statistics (total spent, visit frequency)
  • Allow staff to enroll customers in the loyalty program
  • Include a prominent button to view purchase history

Purchase History Screen:

Create a detailed view of customer purchase history with transactions listed chronologically

  • Include the following for each transaction:
    • Transaction date and time
    • Receipt/transaction number
    • Total amount spent
    • Payment method used
    • Loyalty points earned (if applicable)
  • Display itemized list for each transaction, showing:
    • Product name and category
    • Quantity purchased
    • Unit price and total item price
    • Any discounts applied
  • Implement filtering options by date range and product category
  • Allow staff to search for specific purchased items
  • Calculate and display spending trends over time
  • Enable printing/emailing of receipts
  • Provide option to create targeted promotions based on purchase history
  • Include a direct link back to the customer profile screen

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.

Application Requirements:

Supplier Directory Screen:

Create a responsive interface that displays all current suppliers

Include the following for each supplier:

  • Supplier name and logo (if available)
  • Contact person name
  • Contact information (phone, email, website)
  • Product categories supplied (produce, dairy, meat, etc.)
  • Status (active, inactive, on hold)
  • Rating (based on delivery reliability and product quality)
  • Last order date
  • Next scheduled delivery date

Implement filtering options by:

  • Product category
  • Supplier status
  • Rating range

Allow users to search for specific suppliers by name Include a “Preferred Suppliers” section for most reliable vendors Enable users to select a supplier to view detailed information Display total count of active suppliers prominently Include a “Add New Supplier” button

Supplier Details/Order Screen:

Create a detailed view of the selected supplier with complete contact information

Display a product catalog showing:

  • Product name and description
  • Unit price and minimum order quantity
  • Current warehouse inventory level
  • Lead time for delivery
  • Order history with this supplier

Allow store manager to:

  • Create new product orders
  • Set delivery date and special instructions
  • Update supplier information
  • Adjust supplier rating based on performance
  • View order history with delivery status
  • Flag quality issues from previous orders

Calculate and display:

  • Current order total
  • Historical spending with this supplier
  • Percentage of inventory sourced from this supplier

Generate printable order forms and supplier performance reports

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.

Application Requirements:

Checkout Screen:

Create a responsive interface that processes customer transactions

Include the following for each transaction:

  • Product barcode scanner/manual entry field
  • Real-time displayed list of scanned items
  • Product name, price, and quantity for each item
  • Running subtotal calculation
  • Quantity adjustment controls for each scanned item
  • Delete item option
  • Discount application field (if applicable)
  • Tax calculation display
  • Final total calculation
  • Payment method selection (cash, credit card, debit card, mobile payment)
  • Cash denomination calculator (for cash payments)
  • Change due calculator (for cash payments)
  • Receipt printing option
  • Email receipt option
  • Customer loyalty program integration

Transaction History Screen:

Create a searchable transaction history interface

Include the following features:

  • Transaction date/time filter
  • Transaction ID search
  • Cashier/employee filter
  • Payment method filter
  • Transaction amount range filter
  • Detailed view of each transaction showing:
    • Complete itemized list of products
    • Quantities and individual prices
    • Applied discounts
    • Tax amount
    • Final total
    • Payment method used
  • Option to reprint/email receipt
  • Return/refund processing capability
  • Daily/weekly/monthly sales summary reports
  • Best-selling items statistics
  • Sales by category visualization
  • Export transaction data option (CSV, PDF)