NYU Tandon School of Engineering Computer Science and Engineering CS6083, Spring 2020
Problem 1 A
April 5, 2020
Midterm Sample Solution
Copyright By PowCoder代写 加微信 powcoder
1 Output the cid and cnames of any senders of packages sent to the company “Dinky Donuts”.
2. Output the name of any messenger under age 30 who took more than one hour to make a delivery (time from pickup to delivery).
3. Output the average delivery time (pickup to delivery) for each messenger who has made more than 10 deliveries.
4. 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. )
Problem 1 B
Π!”#,!%&'( “𝜎!).!%&'(+,”%-. ,0%123$(𝜌!4𝐶𝑜𝑚𝑝𝑎𝑛𝑦) ⋈!4.!”#+#.3(%#(5 (𝜌#𝐷𝑒𝑙𝑖𝑣𝑒𝑟𝑦) ⋈#.5(!(“6(5+!).!”# (𝜌!)𝐶𝑜𝑚𝑝𝑎𝑛𝑦)67
Π’%&'( ⋈ 𝐷𝑒𝑙𝑖𝑣𝑒𝑟𝑦)7
SELECT DISTINCT cid, cname
FROM DELIVERY D, COMPANY C1, COMPANY C2
WHERE D.sender = C1.cid AND D.receiver = C2.cid AND C2.cname = “Dinky Donuts”
SELECT DISTINCT mid, mname
FROM MESSAGE M, JOIN DELIVERY D
WHERE M.age < 30 AND delivery - pickup > 60min
SELECT mid, avg(delivery – pickup) FROM DELIVERY D
GROUP BY mid
HAVING count(*) > 10
SELECT mid, mname
FROM MESSAGE JOIN DELIVERY
GROUP BY mid
HAVING max(delivery – pickup) < 60 min
Problem 1 C
Π'"#,&"𝜎!?4:$'"#𝒢!01%2(∗)&3!, &67(#(<"6(5.=>“!-1>)&3&𝐷𝑒𝑙𝑖𝑣𝑒𝑟𝑦67
{𝑟𝑒𝑠|∃𝑐1 ∈ 𝐶𝑜𝑚𝑝𝑎𝑛𝑦 “𝑟𝑒𝑠[𝑐𝑖𝑑] = 𝑐1[𝑐𝑖𝑑] ∧ 𝑟𝑒𝑠[𝑐𝑛𝑎𝑚𝑒] = 𝑐2[𝑐𝑛𝑎𝑚𝑒] ∧ ∃𝑑 ∈ 𝐷𝑒𝑙𝑖𝑣𝑒𝑟𝑦$𝑐1[𝑐𝑖𝑑] = 𝑑[𝑠𝑒𝑛𝑑𝑒𝑟] ∧ ∃𝑐2
∈ 𝐶𝑜𝑚𝑝𝑎𝑛𝑦(𝑑[𝑟𝑒𝑐𝑒𝑖𝑣𝑒𝑟] = 𝑠2[𝑐𝑖𝑑] ∧ 𝑠2[𝑐𝑛𝑎𝑚𝑒] =D 𝐷𝑖𝑛𝑘𝑦𝐷𝑜𝑛𝑢𝑡𝑠′)67}
{𝑟𝑒𝑠|∃𝑚 ∈ 𝑀𝑒𝑠𝑠𝑒𝑛𝑔𝑒𝑟$𝑟𝑒𝑠[𝑚𝑛𝑎𝑚𝑒] = 𝑚[𝑚𝑛𝑎𝑚𝑒]⋀𝑚[𝑎𝑔𝑒] < 30⋀∃𝑑
∈ 𝐷𝑒𝑙𝑖𝑣𝑒𝑟𝑦(𝑑[𝑚𝑖𝑑] = 𝑚[𝑚𝑖𝑑]⋀𝑑[𝑑𝑒𝑙𝑖𝑣𝑒𝑟𝑦] − 𝑑[𝑝𝑖𝑐𝑘𝑢𝑝] > 1h𝑜𝑢𝑟)6}
Problem 2A
Discussion:
One issue is how to model refills, and make sure that only a certain number of refills are given. If seems difficult to do this in the design itself via integrity constraint, and thus we assume that will be checked in program log, say using trigger, when a customer gets another refill.
We also decided not to separate model medications as an entity. One could argue that having a separate entity medication might be a good idea and would avoid redundancy and save space
Medication(mid, mname, strength, amount) Problem 2B
DOCTOR(did, dname, dphone)
PATIENT(ssn, name, gender, city, dob)
PRESCRIPTION(pid, did, ssn, mname, amount, strength, refills, pdate, expiredate)
did refs DOCTOR, ssn refs PATIENT BRANCH(bid, baddress, bphone)
REFILL(pid, refillnum, refilldate, cost, bid) Problem 2C
pid refs PRESCRIPTION, bid refs BRANCH
SELECT did, dname, count(*)
FROM DOCTOR D JOIN PRESCRIPTION P GROUP BY did, dname
WITH SALES_BY_BRANCH_2002 as (
SELECT bid, mname, sum(cost) as total FROM PRESCRIPTION JOIN REFILL
WHERE refilldate during 2002
GROUP BY bid, mname),
MAX_SALES_PRE_BRANCH as (
SELECT bid, max(total) as max FROM SALES_BY_BRANCH_2002 GROUP BY bid
SELECT bid, mname, total
FROM SALES_BY_BRANCH_2002 SB, MAX_SALES_PRE_BRANCH M WHERE SB.bid = M.bid and Sb.total = M.max
WITH PRESS_DATE as (
SELECT pid, max(refilldate) as MR, count(*) as NR FROM PRESCRIPTION P JOIN REFILL R
GROUP BY pid)
SELECT ssn, sname
FROM PATIENT JOIN PRESCRIPTION JOIN PRESS_DATE
WHERE YEAR(MR) <= 2018 AND NR < refills AND now() <= expiredate
W(did, cid, eid) (did refs D, cid refs C, eid ref E) B(bid, cid) (cid refs C)
A(aid, bid, cid) ((bid, cid) refs B)
Z(did, aid, bid, cid, zval) (did refs D, (aid, bid, cid) refs A)
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com