Instructions

The exam starts at 4:05 PM in the Room W31-301 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. These tables are also available in a personal geodatabase that can be opened using MS-Access (as well as ArcMap).

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.

• Remember to turn in your exam (in plain ASCII text or MS-Word or HTML format or PDF format) before leaving the room.

• Make sure to include your name and Athena user ID near the beginning of your file (failure to include this will cost you 2 points)!

• Please confirm with an instructor that your exam has been received.

To turn in your exam, upload it to Stellar: https://stellar.mit.edu/S/course/11/sp16/11.521/homework

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 'exam16_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_exam16_data.mdb). The 'exam16_data' folder contains the following:

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

Question I-1. (6 points each, 12 points total)

Question I-1a: The following query is intended to list the Massachusetts municipalities along with their 1980-1990 change in population for those municipalities that have more than ten polygons associated with them in the shapefile. However, the query does not work properly. Briefly explain why and show the corrected SQL query. (Note: the Oracle table jf.matown2000 is the attribute table associated with the ma_towns00.shp shapefile that we have used in lab exercises.)

SELECT town, count(town) tcount, (pop90 - pop80) pop_change
  FROM jf.matown2000
 WHERE count(town) > 10 
 GROUP BY town
 ORDER BY tcount desc;

Question I-1b: Consider the following query:

select count(*), count(pop80), count(town), count(distinct town)
  from jf.matown2000;

The query returns the counts: 632, 632, 611, and 351 respectively. Explain briefly why all four numbers are not the same and what each number means.

Question I-2. (8 points)

Question I-2: Explain briefly what is meant by the phrase 'many-to-many relationship.' How are many-to-many relationships typically handled when designing relational databases? Illustrate your points using one of the database schemas that we have considered in class lectures and exercises. (A verbal explanation is sufficient. No need for an ER diagram or table definitions.)

Question I-3. (6 points total)

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

Question I-3: 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?

Part II: GIS and Thematic Mapping of SQL results (58 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 (exam16_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_exam16_data.mdb, and in Oracle tables that you can access from your individual Oracle accounts.

Question II-1a. (6 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. (6 points total)

Which Town contains the second 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, which 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)

Next, let's aggregate these differences across TAZ that are considered suitable for development. The MetroFuture scenario (MF) targeted housing additions toward those communities that were considered to have areas suitable for development (in terms of infrastructure, access, and redevelopment opportunities). The attribute CODA in TAZ2727_MFDATA indicates which TAZ are considered to be suitable areas (CODA=1) for further development. Write a SQL query that reports the net difference in projected housing unit growth (MF_less_LIB) for each CODA group. Show your SQL query.

Question II-1f. (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 Town, what is MF_less_LIB_byTown? ___________

Question II-1g. (8 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-1h. (14 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. Also, the town name is listed in upper case in some of the tables but with only leading capital letters in others. The TAZ2727SHP tables in Oracle and MS_Access includes both forms of the town name in order to facilitate table joins.)

On top of the Town-level map, overlay a TAZ level map that highlights 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)

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. (8 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 non-condo, OWN_OCC='Y' and OWN_OCC='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).

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: https://stellar.mit.edu/S/course/11/sp16/11.521/homework/

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