CINS 370

SAMPLE MIDTERM EXAM PROBLEMS

Dr. Stapleton

                                                                                                                       

1.  EER Diagram Building Problem:

Design an EER Diagram for a graphical document editor that supports grouping, which is a concept used in a variety of graphical editors.  Assume that a document is composed of several sheets.  Each sheet contains drawing objects, including text, geometrical objects, and groups.  A group is simply a set of drawing objects, possibly including other groups.  A group must include at least two drawing objects.  A drawing object can be a direct member of at most one group.  Geometrical objects include circles, ellipses, rectangles and squares.  Identify all the potential entities for the EER Diagram.  Be sure to name relationships and use the (min,max) notation on your diagram.  Also, be sure to include relevant attributes and underline primary keys in the EER Diagram.   Make any assumptions you need to make and state them clearly.

 

2. Map the EER diagram you designed above to a relational schema.  Be sure to indicate primary key, foreign key relationships by drawing arrows from the foreign key back to the primary key in the appropriate relation. 

 

3. Using the four relations given below for a database that stores information on doctors and their patients for a local hospital, give the following four queries in the relational algebra and SQL.  You may assume that patients only stay in a single room for each time they come to the hospital  (even though this is not how most hospitals work).

 

doctor(dname, dno, dphone)

patient(pname, pnum, pstreet, pcity, pinsurance, page , pphone, psex)

treats(dnum, pno, condition, treatment_name, treatment_date)

assigned (roomno, pnumber, startdate, length_of_stay)

 

where in relation 'assigned' month, day and year are referring to the start date for assignment of the patient to that room.

 

a) Give the average age of male patients treated for the condition 'lung cancer'.

 

b) Print the patient's  name, phone number, doctor name and  treatment date if they have been treated  for the condition 'hepatitis'.

 

c) Give the name of the patient and their doctor’s names for all patients that have been treated by more than one doctor in the past.

 

d) Give the name of the patient and their doctor’s names for all patients that are currently being treated by more than one doctor.

 

4. Below is a report that your customer wants automated.  They need to to design a database so that they can extract this report.  Give the ER diagram for the schema you would build to generate this report and then map the ER diagram to that  relational schema.

C-Name                 C-Num                   C-Address             S-Name       S-Address        I-Num    Descr      Price

 

Steve                      115003                  Chico, CA             Target         Chico, CA        18000    watch     $10

Jee                          115002                  Paradise, Ca         Target         Chico, CA        90002    pen         $0.30

Merry                     115005                  Chico, CA             Food4Less  Redding, CA   85002    Milk        $2.25

Merry                     115005                  Chico, CA             Food4Less  Redding, CA   67543    Eggs        $0.99

Omar                      118004                  Redding, CA         Sears            Chico, CA       86002    TV          $223.59

Omar                      118004                  Redding, CA         Sears            Chico, CA       79003    Washer  $299.95

Omar                      118004                  Redding, CA         Sears            Chico, CA       75154    Dryer      $199.95

Eric                         900045                  Orland, CA            Sears            Chico, CA       86002    TV          $223.59

Joan                       453679                  Redding, CA         Food4Less    Redding,CA  67543    Eggs        $0.99

 

Note:

C à Customer

S à Store

I à Item