Problem Set B: Relational Database Design

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

The Database Design

• 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 may be interested to know that this assignment was inspired by records kept by the Boston Housing Authority about their developments. Hence, this type of problem has definite real-world importance.

The Process

• Consider the problem, identifying the entities involved, their attributes, and 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).
  
  
• Make sure that your schema adheres to third normal form (discussed in today's lecture notes).
  
  
• In a text editor, construct a script that implements a schema that represents your entities and relationships. (Such a script for the 'websis' example we discussed in today's lecture is linked to those lecture notes: http://mit.edu/11.521/www/lectures/lecture10/lecture10.html. Name this file username_dbschema.sql, where username is your Athena username. Record the output from running your SQL script in the file username_dbschema_log.txt. 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 <<your-path-and-filename>>' 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)


• Include CREATE TABLE statements for all your tables.
  
  
• Review the notes entitled "Enforcing Referential Integrity in Oracle." Then, write:
• ALTER TABLE ADD CONSTRAINT statements to define the tables' primary keys.
ALTER TABLE ADD CONSTRAINT statements to define the tables' foreign keys.

• 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:
  
  

/*****************
These two lines and the one above and below are 
entirely ignored.
******************/
-- This line is also a comment.
SELECT * FROM cat; 
-- However, the one line SELECT statement above is not part
-- of the comment.

• Review your database design using the database design lecture notes (in lecture 10). Pay particular attention to the database design rules of thumb. Are you breaking any of these rules? If so, why? Remember, breaking the rules is often OK, provided you have a good reason. If you make some choices that you think might raise some questions, make sure to address them in your comments.
  
  
• Run your script in SQL*Plus using the command '@youruser_dbschema.sql'. Correct any errors in your script and run it again. Keep trying until it works. Errors generated by attempts to drop tables that you haven't created yet are normal and can be ignored. Note that you can use the '@file.sql' syntax to run any SQL script. The .sql extension is assumed if you leave it out.
  
  
• Add some sample data to your tables. Create another script called username_dbinsert.sql. Record the output from running your SQL script in the file username_dbinsert_log.txt. Insert about five rows in each table using 'INSERT INTO table VALUES (...)' statements. For example, consider the table mytable created by this CREATE TABLE statement:

CREATE TABLE mytable (
    mycomment   VARCHAR2(10),
    mylowvalue  NUMBER,
    myhighvalue NUMBER,
    mydate      DATE);

You could insert two rows into mytable this way:

INSERT INTO mytable
    VALUES ('Hi Mom', 1, 5, '30-OCT-98');
	
INSERT INTO mytable (mycomment, mydate)
    VALUES ('Hi Dad', '1-NOV-98');

Note that Oracle is fussy about date values and will, by default, accept dates only in the format 'DD-MON-YY' within single quotes as shown above. You may use the TO_DATE function to use a different format in a particular instance, for example:

INSERT INTO mytable (mycomment, mydate)
    VALUES ('Bye Mom', TO_DATE('11/10/1998', 'MM/DD/YYYY'));

Alternatively, you can use the  'ALTER SESSION SET NLS_DATE_FORMAT' command to change the format for a particular session, as shown below:

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';

After executing the statement above, the following statement should work:

INSERT INTO mytable (mycomment, mydate)
    VALUES ('Bye Dad', '2/15/2002');

Refer to the Oracle8i SQL Reference for the details of TO_DATE, ALTER SESSION SET NLS_DATE_FORMAT, and date format models. Be forewarned: dabbling in date format models has many potential gotchas (most notably confusing MM (months) with MI (minutes)). Read carefully! (But keep in mind that you do not need to learn all the intricacies about date handling to complete this homework set.)

• Remember to use a COMMIT statement after inserting rows to make sure the data is saved permanently. A SQL error may cause an automatic ROLLBACK of your changes. (You can use the ROLLBACK statement on purpose if you want to undo changes since the last COMMIT.) Many statements include implied COMMITs, and not all errors cause a ROLLBACK. Query your tables often to make sure that your SQL statements are having the desired effect!

• Test all your primary and foreign key constraints with some INSERT statements that are intended to fail. Try to insert a duplicate primary key. Also try to insert a foreign key that does not exist as a primary key in the referenced table. In general, for every constraint you create, you should have an INSERT statement that tests the constraint. For this exercise, however, just include two such 'intended-to-fail' insert statements (following a 'commit' statement) at the end of your username_dbinsert.sql. Also record the output from running your SQL tests in your file username_dbinsert_log.txt.
  
  
• Extra credit: Write a series of statements that record the 'long transaction' associated with two households swapping apartments. At least one transaction control statement such as COMMIT or ROLLBACK is required.

What to Turn In

• Entity relationship diagram for the housing database: 30 points
• Schema definition file and log file: 40 points
• Data insert file and log file: 10 points
• Constraint test file and log file: 20 points
• Extra credit: 5 extra credit points