GEOG 868
Spatial Database Management

Project 2: Designing a Relational Database


Project 2: Designing a Relational Database

This lesson focused on concepts involved in the design of a relational database. To demonstrate your understanding of these concepts, you'll now complete an exercise in database design. Here is the scenario; you work for a UFO investigation organization that has collected a lot of information related to sightings, and you've been tasked with designing a database that will be used as the back end for a website. The information to be stored in the database includes the following:

  • sighting date/time
  • object shape (saucer, cigar, etc.)
  • sighting coordinates (latitude, longitude)
  • witness name, phone # and email address
  • textual descriptions of the sighting

In addition to this basic information, the organization has many digital files that must be made available through the website:

  • photos
  • videos
  • drawings
  • audio (interviews with witnesses)

While most RDBMSs make it possible to store such data in the form of BLOBs (Binary Large OBjects), doing so can be tricky. An alternative approach is to store the names of the files and use front-end application logic to present the file data appropriately.

If you have access to software that can be used to produce an entity-relationship diagram of your proposed database structure, feel free to use it. However, hand-drawn and scanned diagrams are perfectly acceptable as well. If you don't have access to a scanner at home or work, try your local library or FedEx Office store.

As part of your submission, please include notations of the key fields (primary and foreign), the relationships and cardinality between tables, and a short narrative that explains the thinking that went into your design.


This project is one week in length. Please refer to the Canvas Calendar for the due date.

  1. Upload your ER diagram and narrative to the Project 2 Dropbox, 100 of 100 points.
  2. Complete the Lesson 2 quiz.