Overview: In this problem set you will: Construct a relational database design for a generic housing authority

The Database Design

Imagine you work for the public housing agency of a city, and you have been charged with keeping track of who is living in the agency's apartments over time. To help you in this task, you have decided to use a relational database for your record keeping. Your task is to design a database that allows you to capture the facts described below:

The city has three public housing developments. You want to record their names, locations, the year they opened, and their height in stories.

For each apartment unit in the development, you want to keep track of the number of bedrooms, the number of bathrooms, whether the unit has a kitchen or living room, and the square footage.

The database should keep track of the households living in the units. For each member of a household, you want to record their name, date of birth, sex, and indicate whether or not they are the head of the household (more than one person can share that distinction but, for the purpose of this exercise, assume that each household has a single 'head').

You also want to keep track of when a household moved into and out of a particular unit. You want to be able to follow households as they move from one unit to another or from one development to another. Think about how you will find the unit that the household is currently occupying (i.e., what query would you write to find the current unit of each household).

You will invent data for the three developments; two units in each development; and three families, one with 2 members, one with 3 members, and one with 4 members. Include records for each housing unit and household tracking the occupancy of each housing unit during a hypothetical period beginning January 1, 2010, through the current time. Assume that all 6 housing units are empty but ready for occupancy on day 1. Move your three families in at varying times and account for at least one move to another of the 6 housing units between then and now. You may insert these data records into your database using INSERT statements.

You may be interested to know that this assignment was originally inspired by records kept by the Boston Housing Authority (during the late 1990s) about their developments. This type of problem has definite real-world importance. The BHA is big enough to (eventually) hire consultants to design and implement their tenant tracking system, but many smaller non-profits and community groups face similar data management problems (with timevarying

data and one-to-many relationships) in order to track their clients, projects, funding sources, workshops, etc. A little relational thinking about how to structure the data can greatly facilitate the maintenance of data integrity and the practicality of subsequent program analysis.

The Process

Follow this process while designing your database:

Digest the problem and identify the entities involved, their attributes, and the relationships among them.

Draw an entity-relationship diagram that captures your thinking. Turn in your E-R diagram with your problem set. You may either create your diagram by hand or use software such as MS-Access (but, even if done on paper, we prefer that you scan the picture so that you can submit it electronically). Often one starts on paper and then ends up with the final E-R diagram generated by a tool such as that in MS-Access after the table schema are finalized.

Make sure that your schema adheres to at least second or preferably third normal form (discussed in the lecture on database design).

In a text editor, construct a script that implements a schema that represents your entities and relationships. you want it in your network locker - and follow these guidelines as you write your script:

Include the "SET ECHO ON" command at the start of the file. This will allow you to see your commands as they execute.

Use the 'spool <>' command to create your log file automatically. Don't forget to use the 'SPOOL OFF'command at the end of the script. (See Oracle SQL Help) so that the last page or two of output is flushed to the disk and the output file is finalized.

Include CREATE TABLE statements for all your tables. Include DROP TABLE statements for all your tables (before the 'create table' statement). This will help if you have to run your script more than once, which will almost certainly be necessary. Include the clause "CASCADE CONSTRAINTS' at the end of your 'drop' statement. This makes sure any foreign key constraints that refer to this table are dropped too. If these constraints are not dropped, Oracle will report an error when you try to drop the table (because another table may depend upon the existence of the table you are trying to drop). For example:

DROP TABLE parcels CASCADE CONSTRAINTS;

Include comments that indicate the meaning of your statements. SQL*Plus ignores any text such as XXXX contained within /* and */ characters even if the text spans multiple lines.. Single line comments must begin with two dashes together (--). Everything after the dashes is ignored on that line only. For example:

Reference: Massachusetts Institute of Technology

Department of Urban Studies and Planning