Computer Science 455

Third Exercise Set

Due: Friday, Feb. 23, in class

Worth:  About 50 points

Palouse Enterprises, as small mail-order firm located in Moscow, Idaho, maintains an order-invoice system that they would like to put on Oracle. Their current system is a paper system, with files on customers (customer number, customer name, address) and a catalog(catalog number, item description, cost). Customers may make several orders (each of which has an order number, a date the order is made, and a total cost), but each order belongs to only one customer. An order contains several lines which itemize the item ordered, the amount ordered, the item cost (from the catalog) and the total cost (item cost * amount ordered).

  1. From this description, construct an entity-relationship diagram. You may need to alter this diagram by the time the other parts of the problem are completed. What you submit should be the final copy.
  2. From the E-R diagram construct a relational schema. Identify keys and foreign keys. Describe which fields are appropriate for indexes. For those fields appropriate for indexes you choose not to index, say why (assume a reasonably large catalog and customer base).
  3. From the relational schema, construct CREATE TABLE commands with appropriate key constraints.
  4. The file universal.dat in the handouts folder on Plato will (shortly) contain a "universal relation" with all the fields (you can use this to determine data types and lengths for the previous step). Write a CREATE TABLE statement for this table and use the database loader to load it with data from universal.dat.
  5. Using INSERT ... AS SELECT, copy information from the universal table into the tables you created in step 3.
  6. Create a report (an order-invoice report) from the tables thus created.

 

 

Any questions? Please let me know. Many thanks!