CSCI
470
SAMPLE MIDTERM EXAM PROBLEMS
Dr. Stapleton
1.
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
Jee 115002
Merry 115005
Merry 115005
Omar 118004
Omar 118004
Omar 118004
Eric 900045
Joan 453679
Note:
C à Customer
S à Store
I à Item