For Project 1 Deliverable 3 you will be

For Project 1, Deliverable 3, you will be stepping back from your designs and studying mine. You can edit this file and include your answers here. 120 points total TEAM ID: TEAM Members: 1) I am giving you relations in the form of partially-specified CREATE TABLE statements with attribute types that are consistent with some SQL interpreters (though not others). For these table definitions on the following pages, add header comments for each definition that is not currently commented, explaining as best you can what functionality the table is intended to support. (10 points – look for reasonable comments, which do not have to be of greate depth. Grade liberally on this deliverable. ) Questions 2 and 3 follow the table definitions. Question 2 is worth 100 points All parts of questions 1 and 2 are required. Question 3 is for reflection only (at this point). If you spot bugs in the key, or have questions, please post to the Blackboard Discussion Board, or email Doug and Yi Song on Blackboard.

/* Observer associates computer network identifiers (e. g. , IP addresses) with campus dormitories. Dorm. Name can be NULL, thus allowing easy recording of off-campus (or otherwise non-dorm) network identifiers of virtual visitors to dorm web-based electricity and water usage summaries. */ CREATE TABLE Observer ( Network. ID CHAR(20), Dorm. Name VARCHAR(35), PRIMARY KEY (Network. ID) ); /* A record of visits (from observers) to Dorms (often assembled, on demand) Webpage, which displays statistics on electricity and water usage */ CREATE TABLE Dorm. Web. Page ( DWPage. ID INTEGER, Create. Date DATE NOT NULL, /* NOT NULL indicates field cannot be NULL in any record */ Create. Time TIME NOT NULL, Observer. Network. ID CHAR(20) NOT NULL, /* Corresponds to a Network. ID in Observer */ Dorm. Name VARCHAR(35) NOT NULL, /* Corresponds to a Dorm. Name in Dorm */ PRIMARY KEY (DWPage. ID) );

/* */ CREATE TABLE Dorm ( Dorm. Name VARCHAR(35), Max. Occupancy SMALLINT, PRIMARY KEY (Dorm. Name) ); /* A table of time-stamped outdoor temperatures (required) and light conditions (optional) */ CREATE TABLE Ambient. Values ( Ambient. Readings. Date DATE, Ambient. Readings. Time TIME, Ambient. Temp TINYINT NOT NULL, Ambient. Light CHAR(2), PRIMARY KEY (Ambient. Readings. Date, Ambient. Readings. Time) );

/* If you wanted to allow Dorms to move into High. Res status, perhaps you would make Start. Date part of the PK */ CREATE TABLE High. Res. Dorm ( Dorm. Name VARCHAR(35), /* Corresponds to a Dorm. Name in Dorm */ Start. Date DATE, /* Date at which it became a High. Res. Dorm */ PRIMARY KEY (Dorm. Name) ); /* A Low. Res dorm is assumed to have a unique (NOT NULL) electricity sensor, but the definition allows water sensors to be shared across dorms (not unique) and none at all (allowed NULL) */ CREATE TABLE Low. Res. Dorm ( Dorm. Name VARCHAR(35), /* Corresponds to a Dorm. Name in Dorm */ Start. Date DATE, /* Date at which it became a Low. Res. Dorm */ LRElec. Sensor. ID INTEGER NOT NULL, UNIQUE(LRElec. Sensor. ID), /* UNIQUE indicates a key; typically implies NOT NULL */ LRElec. Sensor. On. Line. Date DATE, LRWater. Sensor. ID INTEGER, PRIMARY KEY (Dorm. Name) );

/For example, Floor. Num = 3 and Dorm. Name = Mc. Gill is 3 rd floor of Mc. Gill */ CREATE TABLE Floor ( Dorm. Name VARCHAR(35), /* Corresponds to a Dorm. Name in High. Res. Dorm */ Floor. Num TINYINT, Max. Occupancy SMALLINT, PRIMARY KEY (Dorm. Name, Floor. Num) ); /* Similar meanings as those of Dorm. Web. Page */ CREATE TABLE Floor. Web. Page ( Dorm. Name VARCHAR(35), /* Corresponds to Dorm. Name in Floor */ Floor. Num TINYINT, /* Corresponds to Floor. Num in Floor */ FWPage. ID INTEGER, Create. Date DATE NOT NULL, Create. Time TIME NOT NULL, Observer. Network. ID CHAR(20) NOT NULL, /* Corresponds to Network. ID in Observer */ PRIMARY KEY (FWPage. ID) );

/* Definition allows multiple sensors per floor (thus, Dorm. Name, Floor not required UNIQUE) */ CREATE TABLE HRElec. Sensor ( Dorm. Name VARCHAR(35) NOT NULL, /* Corresponds to Dorm. Name in Floor */ Floor. Num TINYINT NOT NULL, /* Corresponds to Floor. Num in Floor */ HRElec. Sensor. ID INTEGER, HRElec. Sensor. On. Line. Date DATE, PRIMARY KEY (HRElec. Sensor. ID) ); /If you bother to record a reading, the value should be NOT NULL (perhaps coupled special value(e. g. , -999) indicating not read because not functional sensor */ CREATE TABLE HREReading ( HRElec. Sensor. ID INTEGER, /* Corresponds to HRElec. Sensor. ID in HRElec. Sensor */ HREReading. Date DATE, HREReading. Time TIME, HREValue INTEGER NOT NULL, PRIMARY KEY (HRElec. Sensor. ID, HREReading. Date, HREReading. Time), );

/* Comment here */ CREATE TABLE HRWSensor ( Dorm. Name VARCHAR(35) NOT NULL, /* Corresponds to Dorm. Name in Floor */ Floor. Num TINYINT NOT NULL, /* Corresponds to Floor. Num in Floor */ HRWater. Sensor. ID INTEGER, HRWater. Sensor. On. Line. Date DATE, PRIMARY KEY (HRWater. Sensor. ID) ); /* Comment here */ CREATE TABLE HRWReading ( HRWater. Sensor. ID INTEGER, /* Corresponds to HRWater. Sensor. ID in HRWater. Sensor */ HRWReading. Date DATE, HRWReading. Time TIME, HRWValue INTEGER NOT NULL, PRIMARY KEY (HRWater. Sensor. ID, HRWReading. Date, HRWReading. Time) );

/* Comment here */ CREATE TABLE LREReading ( Dorm. Name VARCHAR(35), /* Corresponds to Dorm. Name in Low. Res. Dorm */ LREReading. Date DATE, LREReading. Time TIME, LREValue INTEGER NOT NULL, PRIMARY KEY (Dorm. Name, LREReading. Date, LREReading. Time) ); /* Comment here */ CREATE TABLE LRWReading ( Dorm. Name VARCHAR(35), /* Corresponds to Dorm. Name in Low. Res. Dorm */ LRWReading. Date DATE, LRWReading. Time TIME, LRWValue INTEGER NOT NULL, PRIMARY KEY (Dorm. Name, LRWReading. Date, LRWReading. Time) );

2) Write SQL queries specified below in a manner that is consistent with the table definitions that are appended. For these answers, do NOT use OUTER, INNER, NATURAL or the JOIN keyword constructs generally (not because they are bad, but simply to practice you on some basics, and at the time of the assignment, not all students will have studied that material). Here and in other answers, Row table variables (e. g. , S, R) are often optional (but not always, as when you join a table with itself) If and when used, selected row variables may differ from those used above, of course. In cases where row variables are optional, it is still necessary to disambiguate the table origins of same named attributes (if not with a row variable name, then with the table names, such as HRWSensor. HRWater. Sensor. ID = HRWReading. HRWater. Sensor. ID. (-5 points from the question 2 point total for one or more failures, across all problems, to disambiguatesame-named attributes ) No answer can receive more than the number of points listed for the problem; no answer can receive less than zero.

a) Write a SQL query that lists all of the individual water readings recorded for each floor of Mc. Gill, a high res dorm, on Jan 28, 2012 (HRWReading. Date = ‘ 2012 -01 -28’). Each row of the output should list dorm name (all Mc. Gill), floor number, the date (yes, the dates in each row should be the same), the time of reading, and the reading value. (20 points) SELECT S. Dorm. Name, S. Floor. Num, R. HRWReading. Date, R. HRWReading. Time, R. HRWValue FROM HRWSensor S, HRWReading R WHERE S. Dorm. Name = ‘Mc. Gill’ AND R. HRWReading. Date = ‘ 2012 -01 -28’ AND S. HRWater. Sensor. ID = R. HRWater. Sensor. ID Start with a total of 18 points (and adhjust as follows): Because the HRWSensor table lists Dorm. Name and Floor. Num, other tables like Floor and High. Res. Dorm are not needed. -2 points for each extra table. Check that each of the two RA selection conditions are given (i. e. , S. Dorm. Name = ‘Mc. Gill’ AND R. HRWReading. Date = ‘ 2012 -01 -28’) and -3 points for each, if missing, and the join condition is given S. HRWater. Sensor. ID = R. HRWater. Sensor. ID (-5 points if missing)). There should be no other conditions, unless there are extra tables (in which case, you would have already been penalized – no additional points off unless an answer with extra tables does NOT appropriately join them with other tables, then use discretion based on how bad the failure to join is (but at least -1 per failure to join an extra table). -1 for each missing attribute in the SELECT clause, and -1 for any attribute that is listed which is not given above If the query is nicely indented, as above, and there are no “typos” (e. g. , misspelled table names), and otherwise easy to read, then add 2 points (for max of 20) Poorly formatted example SELECT S. Dorm. Name, S. Floor. Num, R. HRWReading. Date, R. HRWReading. Time, R. HRWValue FROM HRWSensor S, HRWReading R WHERE S. Dorm. Name = ‘Mc. Gill’ AND R. HRWReading. Date = ‘ 2012 -01 -28’ AND S. HRWater. Sensor. ID = R. HRWater. Sensor. ID)

b) Write a SQL query that lists all of the individual water readings recorded for each floor of each high res dorm on Jan 28, 2012 (HRWReading. Date = ‘ 2012 -01 -28’). Each row of the output should list dorm name, floor number, the date (yes, the dates in each row should be the same), the time of reading, and the reading value. (15 points) SELECT S. Dorm. Name, S. Floor. Num, R. HRWReading. Date, R. HRWReading. Time, R. HRWValue FROM HRWSensor S, HRWReading R WHERE R. HRWReading. Date = ‘ 2012 -01 -28’ AND S. HRWater. Sensor. ID = R. HRWater. Sensor. ID Much like (a), but missing one of the selection conditions. Start at 14/15 and use same point decrements for (a) and 1 point increment (rather than 2) for “style” c) Much like (b), but rather than listing all the readings for a given day, list the AVERAGE water reading values for each day, together with the date, the dorm name, and the floor number. (15 points) SELECT R. HRWReading. Date, S. Dorm. Name, S. Floor. Num, AVG(R. HRWValue) AS Average. Value FROM HRWSensor S, HRWReading R WHERE S. HRWater. Sensor. ID = R. HRWater. Sensor. ID GROUP BY R. HRWReading. Date, S. Dorm. Name, S. Floor. Num Start at 14/15 and use same decrementsand increments as (b), and additionally -2 for each missing attribute in the GROUP BY clause (or -6 if there is no GROUP BY clause at all), and -3 for no of AVG aggregate operator (or -2 for improperly formatted operator)

d) Just like (c), but list only those daily averages that are computed over more than 5 values. (15 points) SELECT R. HRWReading. Date, S. Dorm. Name, S. Floor. Num, AVG(R. HRWValue) AS Average. Value FROM HRWSensor S, HRWReading R WHERE S. HRWater. Sensor. ID = R. HRWater. Sensor. ID GROUP BY R. HRWReading. Date, S. Dorm. Name, S. Floor. Num HAVING COUNT(*) > 5 Start at 14/15 and use same decrements and increments as (c), and additionally -4 for no HAVING clause (including an incorrect placement of teh HAVING condition in another clause, like WHERE)

e) Write an SQL query that lists, for each dorm (low or high res), the AVERAGE ambient (outside) temperature and the AVERAGE electricity readings on each day in which the MINIMUM ambient temperature exceeds 70 degrees. Thus, the output should list dorm name, average electrical reading (listed as Ave. Elec), and average ambient temperature (as Ave. Temp), and the reading date. (20 points) The following returns results for High Res Dorms only SELECT S. Dorm. Name, AVG(R. HREValue) AS Ave. Elec, Qual. Days. Ave. Temp, R. HREReading. Date FROMHRElec. Sensor S, HREReading R, (SELECT AV. Ambient. Readings. Date, AVG(AV. Ambient. Temp) AS Ave. Temp FROM Ambient. Values AV GROUP BY AV. Ambient. Readings. Date HAVING MIN(AV. Ambient. Temp) > 70) AS Qual. Days WHERE Qual. Days. Ambient. Readings. Date = R. HREReading. Date AND R. HRElec. Sensor. ID = S. HRElec. Sensor. ID GROUP BY S. Dorm. Name, R. HREReading. Date, Qual. Days. Ave. Temp One can picture the join in the OUTER query as FOR each HREElec. Sensor, Si, FOR each HREReading, Rij (from sensor Si) FOR each Qual Day with suitable ambient temp (MIN > 70), QDATk, on same date as Rijs join Si + Rj + QDATk Rij 1 Si Because there is only one Qual. Day. Ave. Temp per Reading. Date some might have left Qual. Days. Ave. Temp from GROUP BY (-1 if so), but shouldn’t if its to be used in SELECT clause Rij 2 Rij 3 QDATk Rij 4 Rij 5 Give max of 14 points for “one half” of complete answer, using either high res or low res dorm as the basis. Adapt grading guidelines from previous problems (e. g. , extra tables, etc) to this one.

e) Write an SQL query that lists, for each dorm (low or high res), the AVERAGE ambient (outside) temperature and the AVERAGE electricity readings on each day in which the MINIMUM ambient temperature exceeds 70 degrees. Thus, the output should list dorm name, average electrical reading (listed as Ave. Elec), and average ambient temperature (as Ave. Temp), and the reading date. (20 points) This would work for Low Res dorms only if it were just a matter of changing all ‘H’ to ‘L’, BUT ITS NOT! SELECT S. Dorm. Name, AVG(R. LREValue) AS Ave. Elec, Qual. Days. Ave. Temp, R. LREReading. Date FROMLRElec. Sensor S, LREReading R, (SELECT AV. Ambient. Readings. Date, AVG(AV. Ambient. Temp) AS Ave. Temp FROM Ambient. Values AV GROUP BY AV. Ambient. Readings. Date HAVING MIN(AV. Ambient. Temp) > 70) AS Qual. Days WHERE Qual. Days. Ambient. Readings. Date = R. LREReading. Date AND R. LRElec. Sensor. ID = S. LRElec. Sensor. ID GROUP BY S. Dorm. Name, R. LREReading. Date, Qual. Days. Ave. Temp CREATE TABLE Low. Res. Dorm ( Dorm. Name VARCHAR(35), /* Corresponds to a Dorm. Name in Dorm */ Start. Date DATE, /* Date at which it became a Low. Res. Dorm */ LRElec. Sensor. ID INTEGER NOT NULL, UNIQUE(LRElec. Sensor. ID), /* UNIQUE indicates a key; typically implies NOT NULL */ LRElec. Sensor. On. Line. Date DATE, LRWater. Sensor. ID INTEGER, PRIMARY KEY (Dorm. Name) ); Just change [LRElec. Sensor S] to [Low. Res. Dorm S] in query at top?

e) Write an SQL query that lists, for each dorm (low or high res), the AVERAGE ambient (outside) temperature and the AVERAGE electricity readings on each day in which the MINIMUM ambient temperature exceeds 70 degrees. Thus, the output should list dorm name, average electrical reading (listed as Ave. Elec), and average ambient temperature (as Ave. Temp), and the reading date. (20 points) The Low Res result after minor change SELECT S. Dorm. Name, AVG(R. LREValue) AS Ave. Elec, Qual. Days. Ave. Temp, R. LREReading. Date FROMLow. Res. Dorm S, LREReading R, (SELECT AV. Ambient. Readings. Date, AVG(AV. Ambient. Temp) AS Ave. Temp FROM Ambient. Values AV GROUP BY AV. Ambient. Readings. Date HAVING MIN(AV. Ambient. Temp) > 70) AS Qual. Days WHERE Qual. Days. Ambient. Readings. Date = R. LREReading. Date AND R. LRElec. Sensor. ID = S. LRElec. Sensor. ID GROUP BY S. Dorm. Name, R. LREReading. Date, Qual. Days. Ave. Temp /* which can be unioned with High Res Dorm results for full results required by question */ UNION SELECT S. Dorm. Name, AVG(R. HREValue) AS Ave. Elec, Qual. Days. Ave. Temp, R. HREReading. Date FROMHRElec. Sensor S, HREReading R, (SELECT AV. Ambient. Readings. Date, AVG(AV. Ambient. Temp) AS Ave. Temp FROM Ambient. Values AV GROUP BY AV. Ambient. Readings. Date HAVING MIN(AV. Ambient. Temp) > 70) AS Qual. Days WHERE Qual. Days. Ambient. Readings. Date = R. HREReading. Date AND R. HRElec. Sensor. ID = S. HRElec. Sensor. ID GROUP BY S. Dorm. Name, R. HREReading. Date, Qual. Days. Ave. Temp Give max of 18 points for answer that approximates this one. Adapt grading guidelines from previous problems (e. g. , extra tables, etc) to this one.

e) Write an SQL query that lists, for each dorm (low or high res), the AVERAGE ambient (outside) temperature and the AVERAGE electricity readings on each day in which the MINIMUM ambient temperature exceeds 70 degrees. Thus, the output should list dorm name, average electrical reading (listed as Ave. Elec), and average ambient temperature (as Ave. Temp), and the reading date. (20 points) The previous query computes Qual. Days twice. The following does it once by unioning at finer granularity SELECT S. Dorm. Name, AVG(R. Value) AS Ave. Elec, Qual. Days. Ave. Temp, R. Reading. Date FROM(SELECT Dorm. Name, LRElec. Sensor. ID AS ID FROM Low. Res. Dorm UNION SELECT Dorm. Name, HRElec. Sensor. ID AS ID FROM HRElec. Sensor) AS S, (SELECT LRElec. Sensor. ID AS ID, LREReading. Date AS Reading. Date, LREValue AS Value FROM LREReading UNION SELECT HRElec. Sensor. ID AS ID, HREReading. Date AS Reading. Date, HREValue AS Value FROM HREReading) AS R, Give max of 20 points for answer that approximates this one. Adapt grading guidelines from previous problems (e. g. , extra tables, etc) to this one. (SELECT AV. Ambient. Readings. Date, AVG(AV. Ambient. Temp) AS Ave. Temp FROM Ambient. Values AV GROUP BY AV. Ambient. Readings. Date HAVING MIN(AV. Ambient. Temp) > 70) AS Qual. Days WHERE Qual. Days. Ambient. Readings. Date = R. Reading. Date AND R. ID = S. ID GROUP BY S. Dorm. Name, R. Reading. Date, Qual. Days. Ave. Temp What hidden assumptions underlie this query? The previous version? Does this query assume that sensor ids are unique ACROSS HRElec. Sensor. ID and Low. Res. Dorm, for example?

f) Write an SQL query that lists the average electrical readings for each floor that were viewed by an observer with ‘Floor. Web. Page. Observer. Network. ID = X’, for each day in which the MAX ambient temperature was greater than 80 degrees (Ambient. Temp > 80). Include ‘Floor. Web. Page. Observer. Network. ID = X’ as is. Note that ‘X’ is a variable; when an unbound variable appears in a query, it is a parameter that is requested at interpretation time). Each row of the output should list network id (which should be the same across all rows), the AVERAGE ambient temperature for the day (as Ave. Temp), the dorm name, floor, sensor id, electrical reading date, and average value (as Ave. Elec). (15 points) SELECT FWP. Observer. Network. ID, AVG(AV. Ambient. Temp) AS Ave. Temp, FWP. Dorm. Name, FWP. Floor. Num, S. HRElec. Sensor. ID, R. HREReading. Date, AVG(R. HREValue) AS Ave. Elec FROM Floor. Web. Page FWP, Ambient. Values AV, HRElec. Sensor S, HREReading R WHERE FWP. Observer. Network. ID = X AND AV. Ambient. Readings. Date = R. HREReading. Date AND R. HRElec. Sensor. ID = S. HRElec. Sensor. ID AND S. Floor. Num = FWP. Floor. Num AND FWP. Dorm. Name = S. Dorm. Name GROUP BY R. HREReading. Date, FWP. Observer. Network. ID, FWP. Dorm. Name, FWP. Floor. Num, S. HRElec. Sensor. ID HAVING MAX(AV. Ambient. Temp) > 80 All the attributes listed in the Group By should be given

3) Reflect on these questions. You do NOT need to answer and submit them in writing, but they will be topics of discussion. a) If you try to delete a row in Observer that has one or more ‘associated’ entries in Dorm. Web. Page, what will happen? b) If you try to delete a Dorm, for which no one has ever looked at (Observed) a Dorm. Web. Page for it, what will happen? c) If you try to delete a Dorm, for which there have been one or more recorded views of Dorm. Web. Pages for it, what will happen? d) How many electricity sensors are associated with a low res dorm (so far as the DB encodes)? And vice versa? e) How many electricity sensors are associated with a high res dorm (so far as the DB encodes)? f) Could the current database design (tables and assertions) support the situation of a low res dorm becoming a high res dorm WITHOUT losing past data from its low res days? If so, explain, and if not, explain what changes to the DB design you might make to support this (high likelihood eventuality) PRIOR to DB implementation. g) How could the DB design be changed to support records of room and plug level measurements of electricity (and perhaps faucet level water readings)?