Month Year Issue

June 1998 Issue: 3

Journal of Conceptual Modeling
www.inconcept.com/jcm

Analysis Problem
by Dr. Gordon C. Everest

Introduction

Here is a problem which I gave my students on the mid-term exam in my Advanced Database Design class this spring, at the Carlson School of Management, University of Minnesota. I thought it might be a good one to put in the JCM. You have a couple of months to submit your solutions, directly to me at geverest@csom.umn.edu. The deadline for submissions is July 20th.

A retailing organization, which sells products from a catalogue, has a policy of substituting a similar product of greater value when they are out of stock on an ordered product. It is considered a significant aspect of their competitive advantage since the substitute product is always one which sells at a higher price. They make such an offer to emphasize that they are rarely out of stock and the customers can generally depend on getting what they order. Therefore, when not available, the customer gets rewarded with a close substitute of greater value, like a premium brand or a package of larger quantity. At the same time the retailer benefits. First, since all orders are prepaid, this policy avoids having to process a refund. It also enables them to clear out old, over-stocked, or discontinued items. The organization always determines unilaterally what the substitutes will be for any given product - the customer does not choose. They keep track of at most three (3) substitutes at any given point in time, and every product has a substitute. Model as much of the semantics of this domain as possible using an ORM diagram including all relevant constraints. Focus on the Product and Substitute relationships, and anything else directly relevant, thus excluding such entities as Customer, Order, and inventory quantity. Be sure to write down any assumptions you make or explain anything which might be confusing.

WARNING: I don't have a complete solution to this one... yet!

Dr. Gordon C. Everest is Associate Professor of MIS and DBMS in the Carlson School of Management at the University of Minnesota. He published numerous articles and a textbook entitled, "Database Management: Objectives, System Functions, and Administration," from McGraw-Hill, 1986. He was a member of the CODASYL Systems Committee from 1969 to 1980 and the ANSI Object-Oriented Data Base Task Group, 1989-1992. He lectures and consults widely on Information Systems, the next generation of DBMS, OODBMS, conceptual data modeling, computer support tools (CASE) for data modeling, data warehousing, and the legal aspects of computing. He has served as an expert legal witness for several law firms. His real passion is Object Role Modeling and telling people what is wrong with ER modeling, which is a major focus of his Advanced Database Design class at the University of Minnesota.

Contact Information:

Dr. Gordon C. Everest
Carlson School of Management
University of Minnesota
321 - 19 Avenue South, room 3-365
Minneapolis, MN 55455 USA
Voice Phone/Mail: 612-624-0854
School FAX: 612-626-1316
Home Office/Fax: 612-646-3615
geverest@csom.umn.edu