Geog 495: GIS Database Design

10/12/05

 

Entity-Relationship Modeling

Individual assignment

 

This is graded! Due by next Monday (10/17/05) sharp 1:30 pm

 

The Hudson Engineering Group (HEG) has contacted you to create a conceptual model whose application will meet the expected database requirements for its training program. The HEG administrator gives you the following description of the training group’s operating environment:

 

The HEG has 12 instructors and can handle up to 30 trainees per class. HEG offers five “advanced technology” courses, each of which may generate several classes. If a class has fewer than 10 trainees in it, it will be canceled. It is, therefore, possible for a course not to generate any classes. Each class is taught by one instructor. Each instructor may teach up to two classes or may be assigned to do research only. Each trainee may take up to two classes per year.

 

Given this information, do the following:

 

  1. Define all the entities and relationships.

 

  1. Identify cardinality and participation for “each” relationship

 

  1. Build ERD (use composite entity called ENROLL)

 

  1. Draw Crow’s foot model with Visio (learn how to use Visio on Thursday: we meet at Sherman Lab) based on attributes and PK for each table.

 

INSTRUCTOR (INSTR_NUM, INSTR_SPECIALTY)

 

TRAINEE (TRN_NUM, TRN_LNAME, TRN_FNAME, TRN_INITIAL, TRN_E_MAIL)

 

CLASS (CLASS_CODE, CRS_CODE, INSTR_NUM, CLASS_SECTION, CLASS_TIME)

 

COURSE (CRS_CODE, CRS_TITLE, CRS_DESCRIPTION, CRS_CREDITS)

 

ENROLL (CLASS_CODE, TRN_NUM, ENROLL_DATE, ENROLL_GRADE)

 

 

Handwritten works can be accepted as long as it’s readable for question numbers 1-3. No handwritten work for question 4 will be accepted (it’s testing your ability to use Visio not artistic skill!).

 

Friday: no lecture, instead I will be around at Sherman lab during the class hour to provide assistance for you to complete the assignment.