3/23/2020 Submit Midterm Exam | Gradescope
https://www.gradescope.com/courses/101776/assignments/405204/submissions/new
Q2 In which way do you prefer to do this exam? 0 Points
I prefer to do this exam online, by filling in the boxes and/or uploading individual files for each problem.
Copyright By PowCoder代写 加微信 powcoder
I prefer to do the entire exam in handwriting. I will upload all my answers as scans or photos here. Preferably in one or a few files. Upload your scanned handwriting here, and graders will ONLY check the files uploaded here.
I prefer to do the entire exam in handwriting, but am unable to upload a copy here. I will send my solution via email by 7pm.
Please select file(s) Select file(s) Submit Answer *Unsaved Changes
Q3 Problem 1 18 Points
Consider a database for a bicycle messenger company, which makes deliveries between different companies. Each delivery involves a package that is picked up at some pickup time in one company and then delivered at the delivery time at some other company. Each delivery is performed by one messenger identified by a messenger ID. Each company has a company ID, a company name, a contact person name, a contact phone number, and an address. Here is the relational schema for your database:
COMPANY(cid, cname, contactname, phone, address) MESSENGER(mid, mname, age)
DELIVERY(did, sender, pickup, receiver, delivery, mid)
Primary keys are shown in bold. In DELIVERY, pickup and delivery are timestamps (time plus date), and sender and receiver are foreign keys referencing COMPANY.cid. Also, DELIVERY.mid is a foreign key referencing MESSENGER.mid
Q3.1 Problem 1 (a) 8 Points
file chosen
seliF esoohC
3/23/2020 Submit Midterm Exam | Gradescope
https://www.gradescope.com/courses/101776/assignments/405204/submissions/new
Write SQL queries for the following 4 problems.
(i) Output the cid and cnames of any senders of packages sent to the company “Dinky Donuts”.
(ii) Output the name of any messenger under age 30 who took more than one hour to make a delivery (time from pickup to delivery).
(iii) Output the average delivery time (pickup to delivery) for each messenger who has made more than 10 deliveries.
(iv) Output the name of any messenger whose maximum delivery time ever was under 60 minutes. (That is, he has never taken more than 60 minutes for any delivery. )
You can upload file(s) here for Problem 1(a) if you prefer to upload screenshot / scan of handwriting instead of text box.
Enter your answer here
Enter your answer here
Enter your answer here
Enter your answer here
Please select file(s) Submit Answer
Q3.2 Problem 1 (b) 6 Points
Select file(s)
file chosen
seliF esoohC
3/23/2020 Submit Midterm Exam | Gradescope
https://www.gradescope.com/courses/101776/assignments/405204/submissions/new
Write statements in Relational Algebra for queries (i), (ii), and (iii). Use basic RA is possible, and extended RA otherwise.
(i) Output the cid and cnames of any senders of packages sent to the company “Dinky Donuts”.
(ii) Output the name of any messenger under age 30 who took more than one hour to make a delivery (time from pickup to delivery).
(iii) Output the average delivery time (pickup to delivery) for each messenger who has made more than 10 deliveries.
You can upload file(s) here for Problem 1(b) if you prefer to upload screenshot / scan of handwriting instead of text box.
Please select file(s) Select file(s) Submit Answer
Q3.3 Problem 1 (c) 4 Points
Write statements in (Domain or Tuple) Relational Calculus for queries (i) and (ii).
(i) Output the cid and cnames of any senders of packages sent to the company “Dinky Donuts”.
Enter your answer here
Enter your answer here
Enter your answer here
Enter your answer here
file chosen
seliF esoohC
3/23/2020 Submit Midterm Exam | Gradescope
https://www.gradescope.com/courses/101776/assignments/405204/submissions/new
(ii) Output the name of any messenger under age 30 who took more than one hour to make a delivery (time from pickup to delivery).
You can upload file(s) here for Problem 1(c) if you prefer to upload screenshot / scan of handwriting instead of text box.
Please select file(s) Select file(s) Submit Answer
Q4 Problem 2 19 Points
In this problem, you have to design a database schema for a chain of pharmacies (like CVS) that has to keep track of prescriptions, doctors, and patients. Doctors have a name and a phone number at which they can be reached (and maybe an ID number if you choose to). Patients have names, SSNs, a gender, a city, and a date of birth. Prescriptions are written by a doctor for a particular patient, and they specify the name, strength, and amount of the medication and the number of refills. The prescriptions are then forwarded to the pharmacy chain. There are many branches of this pharmacy chain, and customers could choose any one branch to pick up a refill. Your design should be able to track in which branch the customer has picked up the refill. The pharmacy should also keep track of how many refills the patient has already gotten on the prescription, and how much he paid for each refill and on which date. Make sure that all the problems in part (c) can be solved in your design!
For simplicity, we ignore issues such as health insurances and copays etc.
Q4.1 Problem 2 (a) 9 Points
Design and draw an appropriate database schema in the ER model. Define reasonable entities and relationships. Decide which of the relationships are one-to-one, one-to-many, etc. and which attributes are keys. Also, justify your
Enter your answer here
file chosen
seliF esoohC
3/23/2020 Submit Midterm Exam | Gradescope
https://www.gradescope.com/courses/101776/assignments/405204/submissions/new
design in a few sentences, with focus on any non-obvious decisions or assumptions you are making.
Type your answer in the box and/or upload an image for this question
Please select file(s) Select file(s) Submit Answer
Q4.2 Problem 2 (b) 4 Points
Now define an appropriate table schema in the relational model. You can either just list the tables and their attributes, or show SQL statements for creating them. Again, justify any non-obvious decisions and assumptions. Show primary and foreign keys.
Type your answer in the box and/or upload an image for this question
Please select file(s) Select file(s) Submit Answer
Q4.3 Problem 2 (c) 6 Points
Write SQL queries for the following questions:
(i) List the names of all doctors and the number of prescriptions each has written.
Enter your answer here
Enter your answer here
file chosen
seliF esoohC
3/23/2020 Submit Midterm Exam | Gradescope
https://www.gradescope.com/courses/101776/assignments/405204/submissions/new
(ii) For each pharmacy branch, list the prescription medication with the highest total sales (in dollars) in the year 2002.
(iii) List all patients who have not picked up any refill since 2018 for a prescription that still has refills left.
You can upload file(s) here for Problem 2(c) if you prefer to upload screenshot / scan of handwriting instead of text box.
Please select file(s) Select file(s) Submit Answer
Q5 Problem 3 7 Points
Consider the E-R diagram below. Convert it into a relational schema. Show primary keys and foreign key constrains. Note that zval is an attribute of relationship Z, and W is a ternary relationship.
Enter your answer here
Enter your answer here
file chosen
Enter your answer here
seliF esoohC
3/23/2020 Submit Midterm Exam | Gradescope
https://www.gradescope.com/courses/101776/assignments/405204/submissions/new 9/10
Type your answer in the box and/or upload an image for this question
Please select file(s) Select file(s) Submit Answer
Submit Assignment
Enter your answer here
3/23/2020 Submit Midterm Exam | Gradescope
https://www.gradescope.com/courses/101776/assignments/405204/submissions/new 10/10
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com