Instructions

The exam starts at 4:15 PM in the Room 37-312 Computing Lab. It is intended to be finished by 5:45 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, a Word .doc, or an 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 or PDF-formatted document is also okay.) For your own security, we suggest that you save it in the "Private" subdirectory of your locker (i.e., user smith might create a 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, PDF, or HTML 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 sucessfully upload 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!

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

Question I-1. (8 points total)

Consider the ZONING tables used in the Lab exercises. We wish to compute the zoning variance count by neighborhood (NEIGHBRHD) of all zoning variances that involved a change of occupancy (CHNGEOCCUP = 2). We try this query:

select n.NEIGHBRHD, count(*) as change_count
  from zoning z, neighbrhd n
 group by n.NEIGHBRHD
having CHNGEOCCUP = 2
 order by n.NEIGHBRHD

Question I-2. (20 points total)

Consider the table, JF.MATOWN2000, that contains attribute data for each polygon in the MassGIS shapefile for Masss Town boundaries. (This is one of the shapefiles that we used to illustrate the difficulties in computing meaningful Mass population densities.) Be sure to include your SQL queries on your answer sheet (to enable earning partial credit)..

Question I-2(a) (6 points): Which Massachusetts town is represented in JF.MATOWN2000 by the most number of polygons?__________ How many polygons are in that Town? ____________

Question I-2(b) (6 points): Which Massachusetts town has the largest difference in area (in square meters) between the largest and smallest polygon within the Town? ____________ What is that difference in area? ______________

Question I-2(c) (8 points): 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-3. (18 points total)

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

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

Question I-3(b) (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.

Part II: Writing SQL Statements (34 points)

Question II: (34 points)

For this question, we will examine some of the East Boston properties that went into foreclosure during the last downturn in the Boston housing market (around 1990). To do this, we will use the ASSESS_DATA.SALES table documenting 68,134 residential property sales in Boston between 1986 and 1995. We introduced this table in Lab #5 but you need not have done that lab to answer the questions below.

Here is a description of the schema for ASSESS_DATA.SALES:

describe assess_data.sales

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 SALE_ID                                   NOT NULL NUMBER
 PARCEL_ID                                 NOT NULL VARCHAR2(10)
 SALEDATE                                  NOT NULL DATE
 STATECLASS                                NOT NULL NUMBER(3)
 PRICE                                     NOT NULL NUMBER(38,2)
 SELLER                                             VARCHAR2(20)
 BUYER                                              VARCHAR2(20)
 SALECODE                                           VARCHAR2(2)
 BASE_ID                                            VARCHAR2(7)
 CONDOTAG                                           VARCHAR2(1)

set pause 'more...'
set pause on
set pagesize 30
set linesize 130

select * from assess_data.sales
 where substr(parcel_id,1,2)='01';
 
SALE_ID PARCEL_ID  SALEDATE  STATECLASS      PRICE SELLER               BUYER                SA BASE_ID C
------- ---------- --------- ---------- ---------- -------------------- -------------------- -- ------- -
    682 0102870000 12-NOV-92        105      27000 FEDERAL HOME LOAN MG JOSHI SHUBH RAMESH E L1 010287
    683 0102877000 13-SEP-94        105     128000 CONTILLI EDWARD ETAL BUENRROSTRO JOSE     X1 01028
    684 0102881000 21-JUL-95        105      85000 SHIKLES JACKIE G     IRAHETA GERMAN H     W1 0102881 N
    685 0102882000 14-DEC-90        104     148000 GALLO DANTE ET AL    IANNELLI RALPH J JR  D3 01
    686 0102885000 15-DEC-87        105      75000 VISCAY RICHARD       CUNNINGHAM JOHN      O1 0102885 N
    687 0102886000 15-DEC-87        105      75000 VISCAY RICHARD       CUNNINGHAM JOHN      O1 0102886 N
etc....

For our questions, we will focus on foreclosure sales of non-condo, 1-6 family residences. Foreclosures are indicated by SALECODE = 'L1' in the sales records and 1-6 family non-condo residences have STATECLASS in (101, 103, 104, 105, 111). To assist you in identifying the desired sales information, we have already created a view, jf.v_ebforeclose, that lists 321 sales in ASSESS_DATA.SALES that involve an East Boston non-condo, 1-6 family residence going into (or out of) foreclosure. The view is a little complicated since we also want to consider only those sales that occurred during the ten years (1986-1995) and have parcel IDs that match those in the Oracle table, parcel96shp, that includes the attributes from the 1996 parcel shapefile for East Boston (ebos_parcels96.shp). For the exam questions, you will not need to know the specific definition of the view but, for your information, here it is:

create view v_ebforeclose as
select parcel_id, saledate
  from assess_data.sales s,
      (select distinct parcel_i_1 as parcelshp
         from parcel96shp 
        where substr(parcel_i_1,1,2) = '01') p
 where s.parcel_id = p.parcelshp
   and to_char(saledate,'YYYY') > 1985
   and stateclass in (101, 103, 104, 105, 111)
   and salecode = 'L1';

Question II-a (8 points): Many of the parcels in these 321 foreclosure sales show up more than once in the listing. (For example, if the foreclosure resulted in a Bank assuming ownership, then a subsequent sale would record someone else buying the property from the bank. Both transactions would be recorded as SALECODE = 'L1' to indicate that the saleprice was probably not a 'fair market value' since it involved going into or coming out of foreclosure.). Create a view (or table) called v_fcount that lists every parcel involved in these 321 foreclosure sales along with a count of the number of times that parcel_id is involved in a foreclosure. Be sure to include the SQL query on your answer sheet. How many unique parcels are involved in these 321 foreclosure sales? ___________

Question II-b (5 points): Write a SQL query that counts the number of parcels in v_ebforeclose that were involved in more than one foreclosure during the 10 years. What is this count? _________

Question II-c (8 points): How many of the 321 foreclosure sales involved triple-deckers (stateclass = 105)? _________ Be sure to include your SQL query with your answer.

Question II-d (5 points): Among all those parcels listed in jf.v_ebforeclose that had exactly 2 foreclosures, what is the parcel_id and saledate of the triple-decker (stateclass = 105) parcel that had the earliest foreclosure sale? _______________ Be sure to include your SQL query with your answer.

Question II-e (8 points): Among all those parcels listed in jf.v_ebforeclose that had exactly 2 foreclosures, how many sold for more money on the latest saledate? ___________ Be sure to include your SQL query with your answer.

Part III: Joins with Thematic Mapping (20 points)

Question I-3: (20 points total)

For this question, we will use the 1996 East Boston parcel shapefile to map some of the parcels involved in the foreclosures.

Question III-a (8 points): Write a SQL query (creating a view or table called v_3dforeclose) that lists the parcel_id of each triple-decker (stateclass = 105) that had at least one foreclosure along with a count of the number of foreclosures. How many such parcels are there? _________ Be sure to include your SQL query with your answer..

Question III-b (12 points): Create a thematic map of East Boston Parcels (similar in extent to the maps you created in Lab #2) where those parcels involved in triple-decker foreclosures are shaded (darker or lighter) based on the number of times they were involved in foreclosures during the 10 year period. Use the same ebos_parcels96.shp shapefile that we are using in Lab #5 (so parcel_i_1 in ebos_parcels96.shp will match parcel_id in assess_data.sales). This shapefile is in the eboston05 subfolder of the class data locker: K:\data\eboston05. Submit a PDF-formatted map with sufficient readability to distinguish the degree of shading of various parcels. (You can be zoomed in a bit so that only a sizeable but incomplete part of East Boston is visible on the map.)

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 or a PDF-formatted document?
• Did you upload your answers to Stellar: http://stellar.mit.edu/S/course/11/sp09/11.521/
• Did you confirm with an instructor that your exam was received?