CS 307, Assignment #4

Invent an organization, and design a database for it.

Due as specified by course web page. Relates to 2 upcoming labs.

This week, please design your own database that you will use in coming weeks for various lab assignments. This should be appropriate for some organization that takes orders and sells or distributes something. It could be a store selling fruits and vegetables, a food bank, a business selling computer parts, pottery, space ships, towels, or digital watches; it could even be a group distributing CD's of open-source software.

An alternate idea would be a group that gets together to trade stamps, coins, or hockey cards. Members would have a certain number of rare items, and agree to trade them for other items.
 
In any case, I want you keep information about some clients, or customers, who order things. Often they will order multiple quantities of several items (For example, 10 apples, 7 oranges, and 1 bottle of milk). Alternatively, you can put people in touch with each other who want to trade things, or meet to play a game.
 
Let's suppose that you have an inventory of your stock, and naturally, you can't sell what you don't have, and also you don't want to be caught short. Thus, you should not accept an order for 10 apples unless you have at least that many, and if your stock goes below some threshold, you should get some more from your supplier (or make some more.) So, for each item in your inventory, you will need to know a short description, perhaps a part number, how many you have, the threshold amount (if you go below, time to reorder) amount to reorder, and (if you are selling them) the price.
Or something of equivalent complexity.
 
Each order by a client should include the date, and several "line items" one for each different kind of thing. Each line item must identify the kind of thing and the amount (quantity) ordered.

In the lab, you will at some point need to produce a report, that is, some activity by one (or more) people. It could be an "invoice" = a group of items bought at one time, all the items ordered on one day, all services (ever) done for a client (for example visits to the dentist), or one trade of items between two people.

For this assignment:

  1. Describe in words the kind of business or non-profit organization you have in mind.
  2. Sketch out how the report (order) mentioned above should look.
  3. Identify entities, and their attributes.
  4. Draw an E-R diagram of the data you will need to store. Be sure that things can be linked together (and sensible joins are possible.)
  5. Convert this E-R diagram into a schema for a collection of tables. (The lab tasks, now scheduled as Labs 4 and 6, will be to create these tables, with appropriate constraints.) Therefore, you should specify types, constraints, keys, and referential integrity.
I promise to return this assignment, with helpful suggestions, in time for Lab 4.