1 / 12

Analyzing Biological Sequences and Data with SQL Queries

This homework assignment focuses on exploring biological sequences through user-defined types and functions in SQL. Students will apply SQL queries to extract meaningful insights from biological data, including sensor readings and station observations. Key tasks involve calculating distances between data points, handling surrogate keys, and performing time-based joins to analyze changes in velocity over time. Emphasis is placed on design, schema development, and accuracy of queries, with a total of 20 points possible, plus bonus opportunities for exceptional work.

nickan
Télécharger la présentation

Analyzing Biological Sequences and Data with SQL Queries

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