Case 5: Fall 2005
Assigned: 1 December 2005
Due: 8 December 2005
In this case, you will use Oracle Case tools to generate a database
schema from an entity relationship diagram. To begin, start with the
data model that you created for Case 2. Then,
- remove foreign keys for all entities
- remove any associate entities that you created. That is, those
entities you created to change a Many:Many relationship to 1:M.
Remember to put the M:M relationships back for the entities.
Once you have the data model in its original form, then,
- clearly define primary keys for all entities
- define format and domain (range of all attributes) for all attributes
- ensure that field names are unique
Now, you will normalize your data model.
- First normal form: Make sure there
are no repeating values, and all fields are atomic. This may require
additional fields. Remember to fully define your fields.
- Second normal form: Examine all non-primary key fields. Examine if these fields fully depend on the primary key. If not, you may need to create additional entities.
- Third normal form: Ensure that all
non-key fields are mutually independent. This may again require the
creation of new entities.
Before you use Oracle's case tools to convert your data model to a
database schema, you need to make sure that all relationships, and
fields (attributes) are correctly and fully defined. Once you are
satisfied with your data model, use Oracle Database Design Transformer
to generate a database schema. The transformer will automatically
create any additional entities, and foreign keys that are needed.
Finally, save the schema as group?_case5_schema.
A tutorial to use Oracle Database Design Transformer can be found here.