Massachusetts Institute of Technology - Department of Urban Studies and Planning


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

Referential Integrity and Relational Database Design

[Additional notes for use with Problem Set B]


Introduction to Relational Database Design (based on Lecture by Tom Grayson)

Review: The Relational Model
Review: Qualities of a Good Database Design
Introduction to Entity-Relationship Modeling
E-R Modeling Process
Database Normalization
Review: Database Design Rules of Thumb
Enforcing Referential Integrity in Oracle

[Optional: Correlated Updates - examples of UPDATEs only for SELECTed rows]

Review: The Relational Model

Review: Qualities of a Good Database Design

Introduction to Entity-Relationship Modeling

E-R Modeling Process

From E-R Model to Database Design

Database Normalization

Much of formal database design is focused on normalizing the database and ensuring that the design conforms to a level of normalization (e.g., first normal form, second normal form, etc.). Although there are higher normal forms, Third Normal Form is generally considered good enough for typical applications. Normalization generally involves taking a design with fewer tables and many columns and transforming it into a design with more tables with fewer columns -- after conducting some tests and applying some rules. First normal forms requires that there be no multi-valued attributes (e.g., for the URISA database, do not put multiple keyword codes in the same cell, and do not have keyword1, keyword2, etc. columns). Second Normal Form requires that non-key fields must be dependent upon the entire key (e.g., don't put the owner name as well as an owner ID in the parcel table). Third Normal Form prohibits transitive dependencies whereby a non-key attribute is dependent on another non-key attribute (e.g., only put a unique owner ID in the parcel table and not any other owner information such as their age or mailing address). Here are some online sites with useful database management concepts and tips including some good discussion of database normalization: and first, second, and third normal form: (In particular, PHPBuilder has understandable examples of the different levels of normalization.)

Review: Database Design Rules of Thumb

Enforcing Referential Integrity in Oracle

See Tom Grayson's notes regarding the syntax of SQL statements to define primary and foreign keys and to constrain table manipulation in order to enforce referential integrity - that is, to keep you from making changes to tables that destroy their internal consistency.

 


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

For more information about this page, please contact the 11.521 Staff Mailing List <11.521staff@MIT.EDU>.
Written February, 2001 (Tom Grayson), Last Modified 11 March 2010 (jf)