1 / 12

Agenda

Agenda. News! (1 min) Homework 1 (10 min) Hand back SQ 2 (1 min) Intro to Biological Sequences. Homework 1. There were lots of good ideas: User-defined Types Vector(float, float) User-defined functions CloseEnough(t1, t2, epsilon) InRegion(x,y, xmin, xmax, ymin, ymax) Views.

nickan
Download Presentation

Agenda

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Agenda • News! (1 min) • Homework 1 (10 min) • Hand back SQ 2 (1 min) • Intro to Biological Sequences

  2. Homework 1 • There were lots of good ideas: • User-defined Types • Vector(float, float) • User-defined functions • CloseEnough(t1, t2, epsilon) • InRegion(x,y, xmin, xmax, ymin, ymax) • Views CREATE VIEW readings AS ( SELECT D.station, O.salinity FROM deployment D, CTD O WHERE D.sensor = O.sensor )

  3. r station name, x, y name, x, y r.name, r.x, r.y, t.name, t.x, t.y ‘red26’ ‘tansy’ ‘red26’ t name, x, y ‘tansy’ Homework 1 • Q1: SELECT distance(r.x, r.y, t.x, t.y) FROM station r, station t WHERE r.name = ‘red26’ AND t.name = ‘tansy’

  4. station(id, name, x, y) sensor(id, serial) deployment(id, stationid, sensorid, date) CTD(id, did, salinity, temp) Homework 1 station(id, name, x, y) sensor(id, station, snum, model) CTD(sensor, time, depth, salinity, temp)

  5. Homework 1: Observations • Surrogate Key Addiction! SELECT S.name, I.serial, O.salinity FROM station S, sensor I, deployment D, CTD O WHERE D.id = O.did AND D.sensorid = I.id AND D.stationid = S.id station(id, name, x, y) sensor(id, serial) deployment(id, stationid, sensorid, …) CTD(id, did, salinity, temp)

  6. Homework 1: Observations • Compare station(name, x, y) SELECT station, serial, salinity FROM CTD sensor(serial) deployment(station, serial, date) CTD(station, serial, date, time, salinity, temp)

  7. S1.time = S2.time Homework 1: Joins on Time S2 S1 time 

  8. | S1.time - S2.time | <  Homework 1: Joins on Time S2 S1 time 

  9.      Homework 1: Joins on Time | S1.time - S2.time | <  S2 S1 time 

  10. Homework 1: Joins on Time S1.time/b*b = S2.time/b*b S2 S1 time 

  11. SELECT MIN(next.time) FROM tansy_obs next WHERE next.depth = t.depth AND next.time > t.time Homework 1: T, T+1 At station tansy, compute the change in velocity from time t to time t+1 for each depth and each time t. • Q5: • How do you get the “next” time, t+1? SELECT t.depth, t.time, t1.u – t.u as delta_u, t1.v – t.v as delta_v FROM tansy_obs t, tansy_obs t1 WHERE t.depth = t1.depth AND t1.time = (?????) tansy_obs(depth, time, u, v)

  12. Homework 1 • Grading: • Design/Schema: 20pts • Each Query: 4pts • up to 5 bonus pts

More Related