old-sample - CIS 4301 – Information and Database Systems...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

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#: E-mail: 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 Pop-A-Pill chain of pharmacies has offered to give you a free life-time 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 entity-relationship (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

{[ snackBarMessage ]}

Ask a homework question - tutors are online