CS327e

CS327e

Lab 2 Solution

Spring 2004

2.65 Consider the relationship between EMLOYEE and TRIP:

A. What type is this relationship?

Non-Identifying Connection Relationship

B. What are the maximum cardinalities?

The maximum cardinalities are 1:N. An EMPLOYEE can have many TRIPs but a TRIP can belong to only one EMPLOYEE.

C. What are the minimum cardinalities?

The minimum cardinalities are zero and zero. A TRIP is not required be assigned to an EMPLOYEE as indicated by the diamond. AN EMPLOYEE is not required to have TRIP, indicated by using the default.

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

Change the minimum cardinality at the Employee to mandatory by removing the diamond. If the Trip does not belong to an Employee, who is taking the trip?

E. Give an appropriate name for this relationship.

Takes/Taken By

2.66 Consider the relationship between TRIP and AIRLINE_RESERVATION:

A. What type is this relationship?

Non-Identifying Connection Relationship

B. What are the maximum cardinalities?

The maximum cardinalities are 1:N. A TRIP can require many AIRLINE_RESERVATIONSs but an AIRLINE_RESERVATION can belong to only one TRIP.

C. What are the minimum cardinalities?

The minimum cardinalities are zero and zero. An AIRLINE_RESERVATION does not need to be assigned to a TRIP as indicated by the diamond. A TRIP is not required to have an AIRLINE_RESERVATION indicated by using the default.

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

Change the minimum cardinality at the Trip to mandatory by removing the diamond. If the Airline Reservation does not belong to a Trip, why do we even have a reservation?

E. Give an appropriate name for this relationship.

Requires/Reserved For

2.67 Consider the relationship between TRIP and CAR_RESERVATION:

A. What type is this relationship?

Non-Identifying Connection Relationship

B. What are the maximum cardinalities?

The maximum cardinalities are 1:N. A TRIP can require many AIRLINE_RESERVATIONSs but an AIRLINE_RESERVATION can belong to only one TRIP.

C. What are the minimum cardinalities?

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

Change the minimum cardinality at the Trip to mandatory by removing the diamond. If the Airline Reservation does not belong to a Trip, why do we even have a reservation?

E. Give an appropriate name for this relationship.

Requires/Reserved For

2.68 Consider the relationship between TRIP and HOTEL_RESERVATION:

A. What type is this relationship?

Non-Identifying Connection Relationship

B. What are the maximum cardinalities?

The maximum cardinalities are 1:N. A TRIP can require many HOTEL_RESERVATIONSs but an HOTEL_RESERVATION can belong to only one TRIP.

C. What are the minimum cardinalities?

The minimum cardinalities are zero and 1. An HOTEL_RESERVATION must be assigned to a TRIP as indicated by the default. A TRIP is not required to have a HOTEL_RESERVATION, indicated by using the default.

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

No Changes

E. Give an appropriate name for this relationship.

May Require/Reserved For

2.69 Consider the relationships among AIRLINE_RESERVATION, E_TICKET, and PAPER_TICKET:

A. What type is this relationship?

Complete Categorization Relationship

B. What are the maximum cardinalities?

An Airline_Reservation must be either an E_Ticket or a Paper_Ticket but not both. Each E_ticket or Paper_Ticket can be only one Airline_Reservation. This gives a maximum cardinality of 1:1.

C. What are the minimum cardinalities?

Since all E_Tickets must be Airline_Reservations but Airline_Reservations are not required to be E_Tickets, the minimum cardinality here is 1 and zero. Since all Paper_Tickets must be Airline_Reservations but Airline_Reservations are not required to be Paper_Tickets, the minimum cardinality here is 1 and zero. Note that since this is a complete Categorization Relationship, the Generic entity Airline_Reservation must have one E_Ticket or one Paper_Ticket so the minimum cardinality between the Airline_Reservation and the category cluster is 1 and 1.

D. Is this a complete or an incomplete category cluster? Should it be a different type of category cluster?

It is a complete category cluster. I would argue that this is appropriate. I can think of no other type of ticket required by an airline.

E. Give an example discriminator for this relationship.

Ticket_Type: This would be a code carrying one of two values, E for E_Ticket and P for Paper_Ticket.

2.70 Add a new entity called EXPENSE_REPORT. Create a new relationship between EMPLOYEE and EXPENSE_REPORT and a new relationship between TRIP and EXPENSE_REPORT.

A. Justify the type of relationships you have chosen.

Both relationships are Non-Identifying Connection Relationships. Each instance of the relationship will be for an expense item for and EMPLOYYE on a TRIP. It is not an Identifying Connection Relationship because each expense item would have its own identifier. It is not a Non-Specific Relationship because it is not a many-to-many relationship. It is not a Categorization Relationship because Expense Reports are not Employees or Trips.

B. Specify cardinalities for your new relationships.

The relationship between Employee and Expense_Report is 1:N. An Employee may have zero instances of Expense_Report since they may not take Trips or they may have many, especially is they take more than one Trip. An Expense_Report instance must belong to an Employee and only one Employee.

The relationship between Trip and Expense_Report is also a 1:N. A Trip may have zero instances of Expense_Report since they are not required to have any reservations or they may have many since they can have both airline and hotel reservations. An Expense_Report instance must belong to a Trip and only one Trip.

C. Name your new relationships.

Employee and Expense_Report: Submits-Submitted By

Trip and Expense_Report: Charges-Charged To

D. Redraw the diagram in Figure 2-33 to show your new relationships.

2.71 Modify your answer in question 2.70 to make BUSINESS and PERSONAL category entities of TRIP. Assume that there can be no relationship between EXPENSE_REPORT and PERSONAL.

A. Should you use a complete or an incomplete category cluster?

Complete. Expense_Report instances should be either Business or Personnal.

B. Name and describe an attribute that could be used as a discriminator for this category cluster.

Expense_Category. The value would be P for Personnal and B for Business.

C. Redraw the diagram in your answer to question 2.70 to show your new relationships.

3.1 Examine the subscription form shown in Figure 3-28. Using the structure of this form, do the following:

A. Create a single entity model. Specify the identifier and attributes.

SUBSCRIPTION

Address

Zip

Name

City

State

Subscription Length

Payment Status

First Issue

B. Create a model with two entities, one for customer and a second for subscription. Specify identifiers, attributes, relationship name, type, and cardinalities.

C. Under what conditions do you prefer the model in A to that in B?

Model A would be the best model if a Customer were only allowed to have one subscription.

D. Under what conditions do you prefer the model in B to that in A?

Model B would be the best model if a Customer were only allowed to have one multiple subscriptions.