Unformatted text preview: CIS 4301 – Information and Database Systems I
Department of Computer and Information Science and Engineering
Midterm Please read all instructions (including these) carefully.
• You have 60 min. to co mplete the exam.
• There are 4 problems on the exam with a varying number of points for each problem and
subproblem for a total of 60 points. You should look thr ough the entire exa m before getting
started, in order to plan your strategy.
• The exam is closed book and closed notes, but you may refer to your sheet of prepared notes.
• Please make sure your solutions are neat and clearly marked . You may use the backs of the
exam pages for s cratch work. Please do not use any add itional scratch paper.
• Simplicity and clarity of solutions will count. You may get as few as 0 points for a problem if
your solution is far more co mplicated than necessary, or if we cannot understand your
solution. Please Complete the Following:
Name:
Student ID#:
Email: For Scoring Use Only:
Maximum
Problem 1 16 Problem 2 12 Problem 3 17 Problem 4 15 Total Received 60 1 Problem 1 (16 points) – Database Modeling
The PopAPill chain of pharmacies has offered to give you a free lifetime supply of
medicine if you design its database. Given the rising cost of healthcare and your excellent
training in database design, you agree. As a basis for your database design, you are
provided the following set of informal design facts:
1. Patients are identified by an ss#, and for each, their name, address, age, and gender
must be recorded.
2. Doctors are identified by ss#; for each, the name, specialty, and years of experience
must be recorded.
3. Each patient has one primary physician. Each doctor can have multiple patients.
4. Each pharmaceutical company is identified by name and has a phone number.
5. For each drug, the trade name must be recorded. Each drug is sold by a
pharmaceutical company, and the trade name identifies the drug from among the
products of that company.
6. Each pharmacy has a name, address, and phone number.
7. Each pharmacy sells several drugs and has a price for each. A drug could be sold at
several pharmacies and the price could vary from pharmacy to pharmacy.
8. Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for
several patients, and a patient could obtain prescriptions from several doctors. Each
prescription has a date and quantity associated with it.
Draw an E/R diagram that captures the preceding information. Your schema should
include all the constraints that are specified in the design facts. Explicitly state any
important assumptions you make. 2 Problem 2 (12 points) – E/R to Relational Model Mapping
Consider the following entityrelationship (E/R) diagram, which specifies insurance
information for employees and their dependents.
SS# Nam e
Dnam e Age Employees
Dependents Purchaser C o st P_ID Beneficiary Policies Using the algorithms described in class and/or in the textbook, translate the E/R diagram
to a relational schema. Do not make any assumptions about the real world except those
encoded in the E/R diagram. Be sure to specify (underline) keys for all relations and
identify foreign key relationships. Also, you will lose points if your solution is more
complicated than necessary. List your set of relations here: 3 Problem 3 (17 points) – Database Normalization
For the following four subproblems, assume R is a relation with schema R(A,B,C,D)
a) (2 points) What is the meaning of {} → A b) (4 points) Let FD be a set of dependencies that hold for R: {C → D, C → A, B → C}.
What are the keys of R? c) (6 points) What is a BCNF decomposition for R? d) (5 points) Give a sample instance of R that shows that the following rule for MVDs is
not valid: If A →→ BC, then A → B 4 Problem 4 (15 points) – Relational Algebra
Your are given the following PC vendor database, which was also used during lectures:
Product (manufacturer,model,type)
PC (model,speed,ram,hd,cd,price)
Laptop (model,speed,ram,hd,screen,price)
The Product relation gives the manufacturer, model number and type (PC or Laptop).
We assume that model numbers are unique over all manufacturers and product types.
The PC relation gives for each model number that is a PC the speed of the processor in
megahertz, the amount of RAM, the size of the harddisk, the speed of the CD reader, and
the price. The Laptop relation is similar, except that the screen size is recorded instead
of the CD reader speed.
a. (5 points) Construct a relational algebra expression that represents the FD {model →
speed} in relation PC. Write the following queries using the relational algebra query language. You may use
either one of the notations discussed in class.
b. (5 points) Find the name of those manufacturers that sell both laptops and PC. c. (5 points) Find the average speed of laptops costing over $2,000 and which are made
by manufacturer ‘Zeta’. 5 ...
View
Full Document
 Fall '08
 Schneider
 Relational model, pharmaceutical company, relation, E/R Diagram

Click to edit the document details