Assignment 3
The objective of this assignment is to give you some practice in implementing a database from a data model.
Due: 08 Apr 2003
You will need:
- SnarkDreams Data Models (solution to A2)
- SnarkDreams User Needs
Based on the data models given above, generate a data strucutre diagram representing all of the relations (tables) to be implemented in the database. See Figures 6-9 and 6-10 in the textbook for examples. Remember to underline primary keys, italicize foreign keys, and include the relationship lines complete with crows feet. Also list any referential integrity constrains.
Then implement the database in Oracle. Construct all the tables you diagramed above. Be sure to constrain the primary and foreign keys. Make intelligent choices for data types; specifically, make sure that they are long enough to contain the data.
Send me your data structure diagram in a cross-platform readable format (as described for the data models of Assignment 2). Email me a .dmp of your database implmentation (as you did in Assignment 1).
Let me know if you have any questions.
Questions & Answers
- What about the constraint on an order being >= 3/4kg?
- This can be enforced with a function that determines the total weight of all items ordered in a particular Order. TotalWeight does not need to be recorded in the database since the user never uses it other than in this one check. For this assignment, you do not need to implement this contraint (though you certainly can if you want to). However, be aware that it would be required in real application.
- Do we need to put any data in the tables?
- No. Like Assignment 1, you need only design the empty tables. You do not need to enter any data. (Though doing so might be a good way to test your implementation.)
- How do I diagram/implement ORDER-PROD?
- ORDER-PROD is basically an order line-item. As an M:N relationship, it needs its own relation/table in both the data structure diagram and in the Oracle implementation. It is just a normal table with three attributes, two of which are foreign keys.
- How to I implement a composite primary key?
- To create a composite primary key, go to Constraints. In the top half of the window, create a new constraint of type Primary. (Remember to give is a recognizable name, rather than just the system assigned name.) Then, in the bottom half of the window (Constraint Definition) include the two or more fields you want to be included in this composite key. Click Apply.
- How do I create a foreign key?
- Make sure you have a column defined in your database for the foreign key. Then, in Constraints, you can constrain this column to actually hold only foreign keys. In the top half of the window, create a constraint of type Foreign and include the Referenced Schema and Referenced Table. In the bottom half of the window, select the column to you are constraining and the Referenced Column.
Grading
This assignment is worth 15 points.
- 5 points for the data structure diagram
--1 point for clarity and appearance
--1 point for correct syntax
--3 points for correct details.
- 10 points for the implementation
--2 points if I can import/open it without difficulty
--5 points that all tables and their stated attributes are implemented
--2 points for constraining primary and foreign keys as such.
--1 point for not including your own attributes, tables, or constraints that the user didn't ask for