Massachusetts Institute of Technology - Department of Urban Studies and Planning

Spatial Database Management and Advanced Geographic Information Systems
Fundamentals of Spatial Database Management

In-Lab Exam - 20 March 2012


This is an open-book, open-note examination. You are free to use the web (especially the class web pages and Oracle documentation) to help you. Like any examination, however, we expect your submission to reflect your work exclusively. Hence any dialogue via any means (e.g., voice, paper, e-mail, IM, texting) with anyone other than the class instructors is prohibited.

The exam starts at 4:00 PM in the Room 37-312 Computing Lab. It is intended to be finished by 5:30 PM but you can continue until 7:00 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 or Word 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, upload it to Stellar:

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.

Good luck!

Data Preparation

Before beginning your work, copy the 'exam12_data' folder from the 'data' folder in the class locker into a local drive such as C:\TEMP. All the data that you will need for the exam can be found either in your local copy of this folder or in the Oracle database. Unless specified otherwise, all tables referenced in this document are the names of tables in the Oracle database CRL that we have used for the class. All the queries required for the test can be done either in Oracle (via SQL*Plus) or in MS-Access (in 11.521_exam12_data..mdb). The 'exam12_data' folder contains the following:

Type of Data File or Table Name Description
Shapefile MA_TOWNS00.SHP Shapefile of Mass municipalities (which we call Towns). This shapefile is a copy of the same one that we have used in class exercises.
Shapefile TAZ2727.SHP Shapefile of 2,727 Traffic Analysis Zones (TAZ) in the Eastern Mass region included in the MetroFuture regional plan of Boston's Metropolitan Area Planning Council (MAPC).
Oracle Table TAZ2727SHP Attribute table for traffic analysis zone shapefile of TAZ in MetroFuture area.
Oracle Table TAZ2727_MFDATA Current population and housing unit estimates and projections to 2030 from alternative MetroFuture scenarios for 2,727 TAZ .
Oracle Table COMMUNITY_TYPES Community type names and codes used by MAPC to categorize each community included in the MetroFuture scenario modeling.
Oracle Table BOS05EB The same East Boston table of 2005 assessing data that we have used for lab exercises.
Oracle Table STCLASS The same lookup table of State land use codes STCLASS that we have used in class exercises. In the MS-Access database the table is called MASS_LANDUSE and has an additional column, STCLASS_TXT, with the state class code expressed as a text string instead of as an integer.
Spreadsheet 11.521_exam12_dictionary.xlsx Spreadsheet containing data dictionary and code sheets for the above tables.
MS-Access database 11.521_exam12_data..mdb MS-Access database containing the five Oracle tables listed above, TAZ2727, TAZ2727_MFDATA, COMMUNITY_TYPES, BOS05EB, STCLASS.


Part I: Short Answer and 'Fix SQL' Questions (15 points)

Question I-1. (15 points total)

Consider the zoning variance tables used in the Lab exercises. We wish to examine those zoning variances that proposed to convert vacant land to various land uses.

Question I-1a. (4 points)

We write a query to count the number of zoning variance cases in ZONING that were approved out of all the zoning variances for which the existing use is vacant land (EXISTUSE=10). We consider 'approved' to be BOARDDECIS=1 or BOARDDECIS=2 . However, the query does not return the correct results:

select EXISTUSE, count(BOARDDECIS) approve_count
from zoning
where EXISTUSE = 10

Explain why the query does not do what is intended and edit the query so that it yields the correct result.

Question I-1b. (5 points)

Next, write a query that counts the number of zoning variances with EXISTUSE=10 that proposed each possible type of proposed use (PRPSEDUSE). Include all the vacant land zoning variances in your count not just those that were approved, and sort the results by PRPSEDUSE.

Question I-1c. (6 points)

Modify your query from the previous question to include the text description (LANDUSE in the table USE) of the proposed land use in your result.

Question I-2. (18 points total)

Consider the seven illustrative PARCELS tables that we have used in lecture and lab exercises.

Question I-2a (8 points): What would you suggest as the primary key for the SALES table? Explain briefly. What foreign key in the SALES table would you use when joining that table with the PARCELS table?

Question I-2b (10 points): Suppose we wanted to allow parcels to have more than one owner. How might you restructure the tables in the parcels schema to accomodate this possibility? You don't need to provide an E-R diagram but explain briefly and be sure to specify the table name, meaning, and primary and foreign keys of any tables that you create or restructure.

Question I-3. (8 points total)

Consider the seven illustrative PARCELS tables that we have used in lecture and lab exercises. Explain briefly why it is helpful to have a separate table for ownership information instead of listing the owner name directly in a column of the parcel table. Identify at least one drawback that results if owner names are listed in a separate table.

Part II: GIS and Thematic Mapping of SQL results (43 points)

In Part II, we will examine population and housing estimates for the metro Boston area that were generated by Boston's Metropolitan Area Planning Council (MAPC) as part of the recent MetroFuture planning process. The estimates were made for several development scenarios including a business-as-usual scenario called "Let It Be" (LIB) and an alternative that became the MetroFuture plan (abbreviated MF). The relevant data are available in the exam data directory (exam12_data) and contain the shapefiles and data tables listed earlier in this exam. Note that the shapefile, TAZ2727, showing the TAZ boundaries does not always line up precisely with Town boundaries in MA_TOWNS00. For questions involving which Town contains which TAZ, rely on the tabular attribute information in TAZ2727. All the data tables are stored in the MS-Access database, 11.521_exam12_data.mdb, and in Oracle tables that you can access from your individual Oracle accounts.

Question II-1a. (4 points total)

How many Mass Towns are included among the 2727 traffic analysis zones? ________ Show your SQL query to count the number of unique Towns.

Question II-1b. (5 points total)

Which Town contains the third largest number of traffic analysis zones? ___________ How many TAZ does that Town contain? ___________ Show your SQL query.

Question II-1c. (6 points total)

Which TAZ is expected to see the largest increase in housing units between 2000 and 2030 according to the LIB scenario? __________
How many housing units are projected to be added to this TAZ? __________

For this LIB scenario, w hich Town is this TAZ within? _____________

Show your SQL queries.

Question II-1d. (6 points total)

Now, let's examine the TAZ-level differences between scenarios in the estimated housing unit growth. Write a query that computes the difference in expected housing unit growth between the MF and LIB scenarios (from 2000 to 2030) for each TAZ. Call this difference MF_less_LIB. Show your SQL queries.

Which TAZ has the largest value of MF_less_LIB? ___________ For that TAZ, what is MF_less_LIB? ___________

Which TAZ has the smallest (most negative) value of MF_less_LIB? ___________ For that TAZ, what is MF_less_LIB? ___________

Question II-1e. (6 points total)

Now, let's aggregate these differences by Town. Write a query that computes the net difference in projected housing unit growth by Town from 2000-2030 between the two scenarios. Call this difference MF_less_LIB_byTown. Show your SQL queries..

Which Town has the largest value of MF_less_LIB_byTown? ___________ For that Town, what is MF_less_LIB_byTown? ___________

Which Town has the smallest (most negative) value of MF_less_LIB_byTown? ___________ For that TAZ, what is MF_less_LIB_byTown? ___________

Question II-1f. (6 points total)

Redo the previous question, but this time compute MF_less_LIB_byTown as a percentage of the Town's 2000 housing unit count (HU2000). Show your SQL queries and call this percentage difference MF_diff_pct_byTown

Which Town has the largest value of MF_diff_pct_byTown? ___________ For that Town, what is MF_diff_pct_byTown? ___________

Which Town has the smallest (most negative) value of MF_diff_pct_byTown? ___________ For that TAZ, what is MF_diff_pct_byTown? ___________

Question II-1g. (10 points total)

Develop a thematic map of metro Boston Towns that is shaded to indicate the percentage difference, MF_diff_pct_byTown, computed in the previous question. Briefly discuss any pattern that you see and explain briefly your choice of classification method and color scheme to visualize the differences appropriately. (Note, if you had trouble with Question II-1f, then plot MF_less_LIB_byTown (or even HU2000 by town) so you can get partial credit for showing you can pull data into ArcMap for thematic mapping.)

On top of the Town-level map, overlay a TAZ level map that highlights only those two TAZ from Question II-1d that had the highest and lowest differences in added housing units, MF_less_LIB. Submit a PDF-formatted map with sufficient readability to see the highlighted TAZ and interpret whatever pattern you see in the Town-level map.

Part III: SQL Queries (16 points)

Question III-1: (16 points total)

This question uses the East Boston assessing data in BOS05EB that we used in labs #2 and #5. This table is available in Oracle as BOS05EB along with the lookup table for the state land use codes, STCLASS. The variable PTYPE in BOS05EB indicates the state land use code and matches the STATECLASS column in STCLASS. The column OWN_OCC in BOS05EB indicates 'Y' if the assessing record is for an owner-occupied unit and 'N" if it is not. We are interested in computing the percentage of assessing records that are for owner-occupied units and we would like to break this percentage down by PTYPE.

Question III-1a. (4 points total)

Write a SQL query that counts, for each value of PTYPE, the number of non-condo assessing records in BOS05EB that have OWN_OCC equal to 'Y'. Restrict your results to show only those PTYPE values that have at least 20 owner-occupied units associated with them. Show your SQL query.

Question III-1b. (8 points total)

Write one or more queries that counts the number of 'Y' and 'N' assessing records for each PTYPE and computes the percentage of cases that are owner-occupied (OWN_OCC='Y') for each PTYPE. Restrict the results to show only those PTYPE values with at least 40 cases. Show your SQL query (or queries).

Question III-1c. (4 points total)

Rewrite your queries from the previous question so that the text explanation of PTYPE is included along with the PTYPE value. The relevant lookup table is STCLASS in Oracle and MASS_LANDUSE in the East Boston MS-Access database used in Labs #2 and #5. PTYPE in BOS05EB matches STATECLASS in these lookup tables and the land use description is given in the text field, DESCRIPT.

Exam Turnin Checklist

Is your name and Athena ID at the top of your exam? If not, you'll lose 2 points!

Is your completed test saved in a plain text (ASCII) file named username_exam.txt or HTML (web page) file named username_exam.html, or MS-Word file name username_exam.doc?

Did you upload your answers to Stellar:

Did you confirm with an instructor that your exam was received?

Home | Syllabus | Lectures | Labs | CRN | 37-312 lab | MIT

Last modified: 19 March 2012 [jf]