GEOG 868
Spatial Database Management

Project 2: Designing a Relational Database

PrintPrint

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 e-mail 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 RDBMS's 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, Kinko's, or FedEx office.

Deliverables

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

  1. Upload your ER diagram to the Project 2 Drop Box in Canvas, 100 of 100 points.
  2. Complete the Lesson 2 quiz.