SnarkDreams Database Data Diagram and Prototype Implementation

Data Diagram

SD Data Diagram

Here, as allowed at the bottom of SD Models, CostsSubtotal was left out of the final design. Also, instead of a subclass table for US Customer, I went with a possibly-null field in Customer. Thus, this is not the only possible solution.


Implementation

Implementation file: ztomasze.dmp

You can import this file using imp. The username for the imported file is "ztomasze". You should import it into an empty user schema to avoid problems. If you don't want to delete your own database implementation, create a new user for yourself and import there.

Finally, while you can implement them in Oracle, you can not use the words "DATE" or "ORDER" as a table or field name in SQL. They are reserved words. Thus, this implementation had to be changed so you could use it for Assignment 4. It now looks like this:

CUSTOMER(Customer_ID, Firstname, Lastname, Address, Country, Telephone, Email, State).

SDORDER(SDNumber, Customer_ID, Order_Date, Total_Price, Kagi_Percent, Cost, Profit, Comments).

KAGI_NO(Kagi_No, SDNumber).

ORDER_PROD(SDNumber, Name, Quantity).

PRODUCT(Name, Description, Price, Weight).

The implementation also has some data in it.


Optional Reading

I realized while implementing this (as users often realize unstated needs when they go to use a design) ORDER.Cost and ORDER.Profit should also allow nulls. Otherwise, an order must be recieved, processed, and shipped before these numbers are known and the whole order can be entered into the database.

Also, you should keep in mind that there are a number of constraints that have not yet been implemented. For instance, if a Kagi percentage is entered, there needs to be a Kagi order number recorded, and vice versa. The "3/4kg order weight" business rule has not been implemented. Total_Cost, Kagi_Percent, Cost, and Profit all need to add up correctly. (In a purer design, some of these could have been left out. But, as always, in the end you have to implement what your user requests.)

I point these things out so you realize that design is an interative undertaking--you nearly always have to go back and update your design, even during later phases of implementation. Also, we are not producing a complete system in these assignments. There is much more work to be done to produce a fully-functional, robust system! But, with the basic skills you've learned in this course, you should be able to figure out the rest of the details.

Of course, none of these things will affect the grading of what you've already turned in.

Return to ICS 321 Main Page