| 11.521 | Spatial
Database Management and Advanced Geographic Information Systems |
| 11.523 | Fundamentals
of Spatial Database Management |
The exam starts at 3:30 PM in the Room 37-312 Computing Lab. It is intended to be finished by 5:00 PM but you can continue until 6:30 PM so that there is less time pressure. All the tables needed for the exam questions are accessible in Oracle via SQL*Plus using your personal Oracle account that we have been using for lab exercises and homework.
The exam is worth a total of 100 points.
An effective way for you to prepare your exam answers is to use the same familiar methods you have used for the lab assignments and homework. Create an ASCII text or HTML file in your Athena locker to contain your answers. Name your exam username_exam.txt if you use text format or username_exam.html if you use HTML format. (An MS-Word formatted document is also okay.) For your own security, we suggest that you save it in the "Private" subdirectory of your locker (i.e., the user smith might create the text file on her I:\ drive from a WinAthena PC and name it I:\Private\smith_exam.txt). We strongly recommend that you retain the file containing your answers until we return the graded exams to you.
To turn in your exam, send it in an email message to jf@mit.edu.
Finally, don't spend all your time on one or two questions. Start by looking over all the questions to get a general sense of what, and how much, is being asked, and the grade points associated with each question. Then start work on your answers but move on to the next question if you've spent more than 10 minutes on any one of them.
Take a look at the query below, which is intended to list information about the parcels that had fires:
SELECT f.fdate, SUM(p.sqft) sqft
FROM parcels p, fires f
GROUP BY f.fdate
ORDER BY sqft;
This query returns the following erroneous results:
FDATE SQFT --------- ---------- 02-AUG-88 574800 26-JUL-89 574800 02-JUL-89 574800 02-APR-89 574800 26-JUL-90 574800 01-AUG-87 574800 6 rows selected.Question I-1a (6 points).
Why doesn't the query do what is intended?Question I-1b (6 points).
How would you fix this query to obtain the right result? Show your SQL and its output.
Explain briefly what is meant by primary key and foreign key. Illustrate your explanation in terms of the relationship between these two tables from the 'zoning' database: zoning and use. (The tables are available in Oracle and in 11.521_lab3.mdb in the class data locker.)
The query below computes the average assessed value per condo unit and the average assessed value per gross square foot of building area for each of the condominiums (with known assessing and gross area values) that are recorded in the Fiscal Year 2005 assessing data for East Boston. The table, bos05eb, is available in Oracle and in the MS-Access database used for lab #2 (see: \data\eboston05\bos05eb_lab2.mdb within the class data locker). We have included some column formatting commands for readability and we have sorted the output in descreasing order based on the value of the 3rd column.
COLUMN assess_per_sqft FORMAT 99990.00 COLUMN assess_per_unit FORMAT 99999990 SELECT cm_id, count(*) condo_units, sum(fy2005_total)/sum(gross_area) assess_per_sqft, sum(fy2005_total)/count(*) assess_per_unit from bos05eb where cm_id is not null and gross_area > 0 group by cm_id order by 3 desc;
Question I-3a (8 points): Modify this query (or create a view and use the view in a new query) to determine the average assessed value per gross square foot for (a) all condominiums (combined), and (b) all condominiums (combined) with more than 5 units.
Question I-3b (10 points): Develop a thematic map showing East Boston parcels and building footprints at a scale of 1:15000 or larger. Shade the parcels thematically based on the assessed value per gross square foot (as calculated using the SELECT query shown above) for the 91 condominiums. Submit a PDF-formatted map with sufficient readability to identify the condo locations and the relatively high and low-valued locations.
Consider the table ZRESULT described below which lists each zoning variance (from Lab #3) for which the BRA recommendation and Board decision is known and labels them as 'same' or 'diff' depending upon whether the recommendation and decision are the same.
CREATE TABLE zresult AS
SELECT casenumber, brarecom, boarddecis, 'unknown' zgroup
FROM zoning
WHERE brarecom > 0 and boarddecis > 0;
update zresult
set zgroup = 'same'
where brarecom = boarddecis;
update zresult
set zgroup = 'diff'
where brarecom <> boarddecis;
Question II-1a (8 points): Write a SQL query using this 'zresult' table that counts the number of zoning variances with 'same' and 'diff' labels.
Question II-1b (10 points): Write a SQL query that counts the number of cases associated with each possible combination of BRARECOM and BOARDDECIS included in ZRESULT. List only those combinations that have at least 10 cases. Include in your query the ZGROUP field and a column that counts the number of cases. Sort the output by this count (in descending order).
Question II-1c (8 points): Write a SQL query that counts the number of 'same' and 'diff' zoning variances that involve vacant land converted to housing (i.e., EXISTUSE= 10 and PRPSEDUSE=1).
For this question, we will use the table assess_data.sales from lab #5 containing residential property sales information for Boston between 1986 and 1995, together with the table mass_landuse (from lab #2) that explains the meaning of the state land use codes in the sales table.
Question II-2a (5 points): Write a SQL query to compute the average sale price (price)of all East Boston property sales in assess_data.sales that involved residential condos or one to three-family dwellings - that is, sales satisfying the condition: "stateclass in (101, 102, 104, 105)". Include in the output a count of the total number sales with any of those four stateclass codes. (Recall that East Boston parcels begin with '01' indicating Ward #1.)
Question II-2b (7 points): Write another SQL query that computes the average sale price (as in part II-2a) but broken down by the four different stateclass codes considered in part II-2a. Include in the output a count of the number of sales within each of the 4 stateclass code categories. Also, use the mass_landuse lookup table to get the text describing each of the 4 included stateclass codes and include this 'descript' text in the output table of your query.
Consider these two SQL queries of the ZONING table used in class and homework:
SELECT count(*) rowcount, count(hringdate) non_null,
min(hringdate) early_d, max(hringdate) late_d
FROM zoning;
ROWCOUNT NON_NULL EARLY_D LATE_D
---------- ---------- -------- --------
1799 1799 19871021
SELECT min(hringdate)early_d FROM zoning
WHERE hringdate <> ' ';
EARLY_D
--------
19840402
Question II-3a (5 points): Evidently, the hearing dates are missing for some zoning variance cases and these missing dates have a blank (' ') in the field. Write a SQL query that counts the number of zoning variances for which the hearing date is missing. What is that number?
Question II-3b (7 points): The hearing dates are not encoded as a 'date' data type but are encoded as an 8-character string using year/month/day format - that is, yyyymmdd.. Write a SQL query that counts the number of zoning variances with hearing dates occurring during each year.
Question II-3c (10 points): Write one or more SQL queries in order to determine the fraction of zoning variances (with known board decision) that were approved out of those zoning variances with hearing dates during 1985 and 1986. Your final query should display, for each of the two years, the count of zoning variances with known board decision, the number approved and the fraction (or percent) approved. (Consider 'approved' to mean boarddecis = 1 or 2).
Home | Syllabus | Lectures | Labs | CRN | 37-312 lab | MIT
Last modified: 11 March 2008 [jf]