CS327e

Lab 2 Solution

Fall 2004

2.72 Consider the relationship between FISH and OBSERVATION:

A. What type is this relationship?

Non-Identifying Connection Relationship

B. What are the maximum cardinalities?

The maximum cardinalities are 1:N. A FISH can require many OBSERVATIONs but an OBSERVATION can belong to only one FISH.

C. What are the minimum cardinalities?

The minimum cardinalities are zero and zero. An OBSERVATION may not be for a FISH indicated by the diamond. A FISH is not required to have an OBSERVATION, indicated by using the default.

D. Describe any cardinality changes that you think should be made. Justify your changes.

Change the minimum cardinality to the default of one (remove the diamond). Based upon Figure 2-4 of the text, no observation is recorded unless some type of fish is counted.

E. Give an appropriate name for this relationship.

Found During/Observed For

2.73 Consider the relationship between RIVER and OBSERVATION:

A. What type is this relationship?

Non-Identifying Connection Relationship

B. What are the maximum cardinalities?

The maximum cardinalities are 1:N. A RIVER can require many OBSERVATIONs but an OBSERVATION can belong to only one RIVER.

C. What are the minimum cardinalities?

The minimum cardinalities are one and one. An OBSERVATION must be for a RIVER indicated by using the default. A RIVER is required to have at least one OBSERVATION, indicated by using the P for positive number.

D. Describe any cardinality changes that you think should be made. Justify your changes.

No Changes

E. Give an appropriate name for this relationship.

Location of/Recorded at

2.74 Add a new entity called RIVER_SITE as a child entity in an identifying connection relationship to RIVER. Remove the relationship between RIVER and OBSERVATION and place a new relationship between OBSERVATION and RIVER_SITE.

A. Define and justify cardinalities between RIVER and RIVER_SITE.

This should be a 1:N relationship. Each River could have several Observation_Sites and an Observation_Site must be located on a River since the relationship is an identifying connection relationship.

B. Define and justify cardinalities between RIVER_SITE and OBSERVATION.

This relationship is also a 1:N relationship. Many Observations can be made at a site and all observations must be made at one site.

C. Name your new relationships.

River and Observation_Site: Has/Location Of

Observation and Observation_Site: Records/Made at

D. Redraw Figure 2-34 given your new design.

2.75 Add a new entity called OBSERVER to your answer to question 2.74. Create a relationship between OBSERVATION and OBSERVER. Also, add a category cluster to OBSERVER with two categories: PROFESSIONAL and VOLUNTEER.

A. What type of relationship is appropriate? Justify your answer.

The relationship between OSERVER and OBSERVATION is a non-identifying connection relationship. It is not an identifying connection relationship because OBSERVATION would have is own independent identifier. It is not a non-specific relationship because it is not a many-to-many relationship. It is not a categorization relationship because Observations are not a type of Observer.

B. Define and justify cardinalities between OBSERVER and OBSERVATION. Name this relationship.

Makes-Made By; Cardinalities are 1:N. An Observation must be made by someone (an Observer) and each Observation is made by a single Observer (Bruce or Zelda). An Observer and make many Observations or it may be possible that a new Observer made make zero observations.

C. What type of category cluster is appropriate?

Complete: This does assume that all observers will either be Professional or Volunteers.

D. Redraw your answer to question 2.74 given your new design.

2.76 Create a new entity called CLASS. Construct a relationship between VOLUNTEER and CLASS.

A. What type of relationship is appropriate? Justify your answer.

It is a non-specific relationship because it is a many-to-many relationship. A Volunteer may take many classes and a Class may be taken by many different Volunteers. It is not a non-identifying connection relationship because it is not a 1:N relationship. It is not an identifying connection relationship because Class would have is own independent identifier. It is not a categorization relationship because Classes are not a type of Volunteer.

B. Redraw your answer to question 2.75 given your new relationship.

C. Create another entity called CLASS_OFFERING, which represents a particular offering of a particular class. Add and remove relationships as appropriate. For each new relationship, give the type, give the cardinalities, and name the relationship.

The relationship between Class and Class_Offering would be an identifying connection relationship with a one to many cardinality. Each Class may have many offerings but any offering is of a single class. The relationship would be named Has-Offered For.

The original relationship between Volunteer and Class is replaced by a relationship between Volunteer and Class_Offering. A Volunteer would attend an offering of a class, not all offerings of a class. The relationship would still be a non-specific relationship because it is a many-to-many. The relationship would be name Attends-Attended By.

D. Redraw your answer to question 2.75 given your new design.

3.36.1 Consider the traffic citation shown in Figure 3-29. The rounded corners on this form provide graphical hints about the boundaries of entities represented here.

A. Create a data model with five entities. Use the data items on the form to specify identifiers and attributes for those entities.

B. Specify relationships among the entities. Name the relationship and give its type and cardinalities. Indicate which cardinalities can be inferred from data on the form and which need to be checked out with systems users.

Relationships, Types, and Cardinalities are show on the E-R Diagram. From the form you can infer that a Driver register only one Vehicle. This is because of the single Registered Owner in the Vehicle area. You can also imply that a Ticket can have several Violations because of the multiple lines and the wording Violations (plural). You can imply that only one Officer can give the Ticket because there is room for only one Officer Signature.