BYGB/ISGB 7973: DATABASE MANAGEMENT
Exercises on Data Modeling
And
One SQL assignment
DUE:
November 14
Use Oracle Data Modeler to Draw logical E-R Diagrams for the following exercises from the textbook. You should follow a successive refinement approach:
- Identify entities and relationships between them. If necessary, rewrite business rule so that each rule can be implemented either as a relationship or as a constrain that can be checked at data entry or update.
- Resolve ternary or higher order relationship by introducing associative entities. In Data Modeler, we can draw only binary relationship and unary relationships. So if you are dealing with a ternary (or higher) relationship (e.g., a sales event brings a customer, a product, and a sales person together) you would introduce an “associative entity” and then define binary relationships between the original entities and the newly defined associative entity.
- Although Data Modeler resolves many-to-many binary relationships when building relational model, I would suggest that you get into habit of resolving them in the logical model.
- Identify cardinality for each relationship.
- For each entity, identify the attributes that describe the entity. Select the attribute(s) that will be used to enforce uniqueness.
- Identify attributes that will used to enforce referential integrity rules. In Oracle data modeler, a one to many relationship causes the primary key of entity on one side to become an attribute of the entity on many side. Of course, we can rename the inherited attribute.
- Identify attributes associated with the relationships.
- Define associative entities to resolve many to many or higher order relationships. Take extra care in identifying attributes for associative entities. In many cases, the associative entity will “inherit the attribute” of the entities involved in the original many-to-many relationship (or higher order relationships.) The combination of the inherited attributes can be used to enforce the uniqueness condition for associative entity. However, we often a surrogate key for the associative entity which then becomes the unique identifier for the entity.
- If you have enough information from the description, identify the datatype for each attribute. For composite attributes like name and address, define a new structured data type. If not leave it as unknown.
- Identify mechanism for enforcing class-subclass relationships.
- Engineer the logical model into relational model.
- Print the logical and relation models diagram to pdf files. For structured data type, use screen shot to display the structure.
Submit one document that contains data model diagrams (logical model, relational model and the structured data type if any) for each of the following four problems from the textbook. Specify any additional assumption
2 – 39a
2 – 39b
2 – 41
3 – 26
