Question

For the below questions, use the following schema definition.
customer(custid,lname,fname,street,city,state,zip,dob) device(devid,custid,type) device_onoff(ts,devid,pwr) device_chnl(ts,devid,chnl) schedule(start_ts,end_ts,chnl,showid)
This is a schema for a tv network. Customers own devices (of a certain type; phone is a device, cable-box is a device, etc.), and they can watch channels on those devices. Whenever a device comes online, we get a device onoff event, indicating device power state. Whenever a device channel changes, we get a device chnl event. There is also a schedule object, which tells us start and end times of shows on channels. Pick the best answer that fits the question. Not all of the answers may be correct. If none of the answers fit, write your own answer. There are at most 2 questions where writing your own answer may be appropriate.

12. (5 points) Create a table device show(ts,devid,chnl,showid), which looks up the currently playing show on chnl for each device.

(a) create table device show as with dshow as ( select ts,devid,chnl,null showid from device chnl union all select start ts,null,chnl,showid from schedule), grps as ( select a.*, sum(case when showid is not null then 1 else 0 end) over (partition by chnl order by ts rows between unbounded preceding and current row) grp from dshow a ), mx as ( select ts,devid,chnl, max(showid) over (partition by chnl,grp) showid from grps) select ts,devid,chnl,showid from mx where devid is not null

(b) create table device show as with dshow as ( select ts,devid,chnl,null showid from device chnl union all select start ts,null,chnl,showid from schedule), grps as ( select a.*, sum(case when showid is not null then 1 else 0 end) over (partition by chnl order by ts rows between unbounded preceding and current row) grp from dshow a ), mx as ( select ts,devid,chnl, max(showid) over (partition by chnl,grp) showid from grps) select ts,devid,chnl,showid from mx where devid is not null union all select a.ts, b.devid, a.chnl, a.showid from grps a inner join grps b on a.devid is null and b.devid is not null and a.chnl=b.chnl and a.grp=b.grp+1

(c) create table device show as select distinct a.ts,a.devid,a.chnl,b.showid from device chnl a inner join schedule b on a.chnl=b.chnl and a.ts between b.start ts and b.end ts

(d) create table device show as select b.start ts,a.devid,a.chnl,b.showid from device chnl a inner join schedule b on a.chnl=b.chnl and a.ts between b.start ts and b.end ts

(e) Other:

Answer 1

Working on it, need some time to complete. Please cope with that. Thanks for your understanding