The University of Texas

CS 327e

Fall 2003

Lab 2 Solution

Use the STUDENT ACTIVITY IDEF1X diagram in Figure 2-32 to answer the following questions:

2.57Consider the relationship between STUDENT and EQUIPMENT:

A. What type is this relationship?

Non-Identifying Connection Relationship

B. What are the maximum cardinalities?

The maximum cardinalities are 1:N. A STUDENT can have many EQUIPMENTs but an EQUIMENT can belong to only one STUDENT.

C. What are the minimum cardinalities?

The minimum cardinalities are zero on both sides. An EQUIPMENT does not require a STUDENT as indicated by the diamond. A STUDENT is not required to have an EQUIPMENT indicated by using the default.

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

No changes. Since Students are no required to take course they would not be required to be assigned equipment. It is also possible that a course does not have students so the equipment may not be assigned to a student.

E. Give an appropriate name for this relationship.

Assigned/Assigned To

2.58Consider the relationship between EQUIPMENT and COURSE:

A. What type is this relationship?

Non-Identifying Connection Relationship

B. What are the maximum cardinalities?

The maximum cardinalities are 1:N. A COURSE can have many EQUIPMENTs but an EQUIMENT can belong to only one COURSE.

C. What are the minimum cardinalities?

The minimum cardinalities are zero AND 1. An EQUIPMENT must be assigned to a COURSE (default cardinality). A COURSE is not required to have EQUIPMENT 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.

Uses/Used by

2.59In Figure 2-32, COURSE describes a course such as Beginning Kayaking or Intermediate Scuba; CLASS describes a particular offering of a course, such as the class Beginning Kayaking offered on January 7, 2003. For the relationship between COURSE and CLASS:

A. What type is this relationship? Is this the correct type for this relationship?

Identifying Connection Relationships since the key of the class would include the key of the course (course name). YES, the correct type of relationship is shown.

B. What are the maximum cardinalities?

The maximum cardinalities are 1:N. A COURSE can have many CLASSes but a CLASS can belong to only one COURSE.

C. What are the minimum cardinalities?

The minimum cardinalities are zero AND 1. A CLASS must be assigned to a COURSE (default cardinality). A COURSE is not required to have CLASSes 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.

Offers/Offered By

2.60Consider the relationship between CLASS_FEE and CLASS:

A. What type is this relationship?

Non-Identifying Connection Relationship

B. What are the maximum cardinalities?

The maximum cardinalities are 1:N. A CLASS can have many CLASS_FEEs but a CLASS_FEE can belong to only one CLASS.

C. What are the minimum cardinalities?

The minimum cardinalities are zero AND 1. A CLASS_FEE must be assigned to a CLASS (default cardinality). A CLASS is not required to have CLASS_FEE indicated by using the default.

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

No changes. Students are not required to have classes so a class may not have a fee. If a student pays a fee it must be assigned to a class.

E. Give an appropriate name for this relationship.

Pays/Charges

2.61Consider the relationship between CLASS_FEE and STUDENT:

A. What type is this relationship?

Non-Identifying Connection Relationship

B. What are the maximum cardinalities?

The maximum cardinalities are 1:N. A STUDENT can pay many CLASS_FEEs but a CLASS_FEE can belong to only one CLASS.

C. What are the minimum cardinalities?

The minimum cardinalities are zero and zero. A CLASS_FEE is not required to be assigned to a CLASS as indicated by the diamond. A COURSE is not required to have a CLASS_FEE indicated by using the default.

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

Change the minimum cardinality to STUDENT to the default (required) by removing the diamond. If a CLASS_FEE does not require a STUDENT, who pays the fee?.

E. Give an appropriate name for this relationship.

Pays/Paid By

2.62 Consider the relationship between STUDENT and CLUB:

A. What type is this relationship?

Non-specific Relationship

B. Describe a possible new entity that would change this relationship into two non-identifying connection relationships. Specify appropriate cardinalities for your new relationships.

MEMBERSHIP: This entity would include attributes such as Member_Id, Date_joined, and Dues_Paid. The cardinalities would be 1:M between CLUB and MEMBERSHIP (Clubs can have zero to many Memberships but a Membership must be for a single Club). 1:N between STUDENT and MEMBERSHIP (Students can have zero to many Memberships but each Membership must belong to exactly one Student.

C. Do you think the design in your answer to question B is a better design than the relationship shown in this figure?

YES, there are attributes that do not belong to either the CLUB or the STUDENT (Member_Id, Date_Joined, and Dues_Paid. This design allows you to show a cleared design.

2.63 Add a new entity called INSTRUCTOR. Create a new relationship between INSTRUCTOR and COURSE and a new relationship between INSTRUCTOR and EQUIPMENT.

A. Justify the type of relationships you have chosen.

Non-Identifying Connection Relationship. Both relationships would be 1:N. They are non-identifying because Instructor, Equipment, and Course have their own identifiers.

Note that these relationships would probably make the relationship between EQUIPMENT and COURSE unnecessary.

B. Specify cardinalities for your new relationships.

Both relationships would be 1:N. An Instructor could teach from zero to many Courses but a Course must have one Instructor. An instructor may have from zero to many Equipments (zero if they do not teach a course, many if they teach at least one course) but Equipment must be assigned to only one Instructor since the Equipment must be assigned to one Course and the Course must have one Instructor.

C. Name your new relationships.

Between INSTRUCTOR and EQUIPMENT: Uses-Used By

Between INSTRUCTOR and COURSE: Teaches-Taught By

D. Redraw the diagram in Figure 2-32 to show your new relationships. Place all cardinalities from your answers to questions 2.57 to 2.62 on this diagram.

2.64 Modify your answer in question 2.63 to make INSTRUCTOR and CLUB_OFFICER category entities of STUDENT.

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

Incomplete Category Cluster. Students are not required to members of a Club so there will be Students that are not Club_Officers. We must assume that not all Students are Instructors to there will be Students that are neither Instructors nor Club_Officers.

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

Student_Status. This would be a code to indicate that the Student is an Officer (value O) or Instructor (value I). If the Student is neither the value would be null.

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

3.38Examine the list of stock quotations in Figure 3-31. Using the structure and example data items in this list, do the following:

A. Create a single entity data model for this list. Specify the identifier and attributes.

B. Modify your answer to A to include the entities COMPANY and INDEX. Specify the identifier and attributes of the entities and the type and cardinalities for relationships. Explain which cardinalities can be inferred from Figure 3-31 and which need to be checked out with users.

Each of these relationships is a One-to-1. A Quote is for a single Index or Company and an Index or Company has one quote. They are Identifying relationships because the key to all three tables is the same, the Symbol. This is a unique situation because a Stockquote will belong to either an Index or a Company but not both. All of these can be implied by the data shown.

C. The list in Figure 3-31 is for a quotation on a particular day at a particular time of day. Suppose that the list were changed to show closing daily prices for each of these stocks and that it includes a new column: QuoteDate. Modify your model in B appropriately.

The only things that would change are the identifier and the maximum cardinality for the weak entity StockQuote. The identifier would now need to include the QuoteDate and the cardinality would be 1:N rather than 1:1.

D. Change your model in C to include the tracking of a portfolio. Assume the portfolio has an owner name, phone, email address, and a list of stocks held. The list includes the identity of the stock and the number of shares held. Specify all additional entities, their identifiers and attributes, and the type and cardinality of all relationships.

E. Change your answer to question D to keep track of portfolio stock purchases and sales in a portfolio. Specify entities, their identifiers and attributes, and the type and cardinality of all relationships.