Assignment 5 - Data Modeling PDF

Preview

Summary

basic questions from assignment tp be posted here...

Description

1

BYGB/ISGB 7973: DATABASE MANAGEMENT Exercises on Data Modeling And One SQL assignment

DUE: November 8 for sections 1, 2, 3 before class November 3 for section 4 before class

Use Oracle Data Modeler to Draw logical E-R Diagrams for the following exercises from the textbook. You should follow a successive refinement approach: (a) 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. (b) 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. (c) 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. (d) Identify cardinality for each relationship. (e) For each entity, identify the attributes that describe the entity. Select the attribute(s) that will be used to enforce uniqueness.

(f) 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. (g) Identify attributes associated with the relationships. (h) Define associative entities to resolve many to many or higher order relationships. Take extra care in identifying attributes for associative entities.

2

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 we introduce a surrogate key for the associative entity which becomes the unique identifier for the associative entity. (i) 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. (j) Identify mechanism for enforcing class-subclass relationships. (k) Engineer the logical model into relational model. (l) Print the logical and relation models diagram to pdf files. data type, use screen shot to display the structure.

For structured

Submit one document that contain data model diagrams (logical model, relational model and the structured data type if any) for the following four problems from the textbook. 2 – 39a 2 – 39b 2 – 41 3 – 26 ==================

SQL question Referring to the PVFC database that you used in assignment 3 and 4, write the SQL query for the following information request: Find the customer ID and Customer name for the customer that has purchased most computer desks....