CIS-3010 Lab Assignment #3: PeakBaggers Database

Due: Friday, February 10, 2023

Reading: Refer to the full definition of the VariableStars database to see examples of several CREATE TABLE commands being used. The PostgreSQL documentation on CREATE TABLE is very complete, although a bit daunting. Similarly, the PostgreSQL documentation on supported data types is very complete.

In the text, section 7.2 on page 149 talks about the SQL Data Definition language (of which CREATE TABLE is one aspect. The text discusses CREATE TABLE in more detail in section 7.2.2 on page 151. Section 7.2.3 on referential integrity is also good to look at. That discusses the foreign key constraints I talked about in class.

In this lab assignment you will do a "version 1.0" design of a database for peakbaggers. A peak bagger is a hiker or climber who sets out to ascend all the mountains on some checklist of mountains. There are proponents and opponents to this activity; discussing the pros and cons of peak bagging is outside the scope of this assignment. Ultimately the database you set up here could potentially be used by a peak bagger website, similar to the one linked above, that allows hikers to find mountains to climb and record information about their ascents.

Before you start, create a new database in your server instance by using the createdb command as you did in Lab #1. Use the name xyzPeakBaggers, replacing xyz with your initials as before.

At this point you have two ways of working. You can choose to either...

Overall Database Structure

This lab is intentionally open-ended in certain respects. However, you will want to be sure your database can accept the mountain data that I provide. There are also various requirements as described below.

For our purposes we will only consider mountains in the United States. The significance of this decision is twofold. First, it will be meaningful to talk about which state each mountain is in and to use a two letter abbreviation for the states. Second, we will record the mountain elevations in feet (rather than meters, as would be appropriate for a more international system). We also won't worry about "international" character sets being used for the names of mountains.

Your goal is to create an SQL script containing four CREATE TABLE statements to define the base tables of the database. These tables are:

  1. state. This table should contain a mapping between the full name of a state and its two-letter abbreviation. Use the abbreviation as the primary key.

    The script state-data.sql provides information about the 50 states in the US that you can use to populate your table.

  2. mountain. This table contains information about the mountains themselves. It should contain at least: the name of the mountain, the state and town in which the mountain is located, the longitude and latitude of the mountain's summit, and its elevation. Use an artificial id number for the key (why?). I suggest calling the key attribute id and have the database system automatically assign values to it (see the more complete variable star example for how to do this).

    The script mountain-data.sql provides information about some mountains in New England that you can use to seed your mountain table.

  3. climber. This table contains information about the people doing the climbs. It should contain at least: the name of each person (separate the first and last name so queries against just the last name are easier to express), an email address as contact information, and the climber's birthdate. Again, use an artificial id number for the key (why?).

    The script climber-data.sql provides information about some climbers that you can use to seed your climber table.

  4. ascent. This table contains information about the actual climbs done (sometimes called "ascents"). It should contain at least: a reference to the climber who did the climb, a reference to the mountain climbed, the date of the climb, and a field for a URL pointing to a web location for the climber's comments about the climb (if any). For example, the climber might have a blog and the URL attribute would specify the location of the blog entry for the climb. Again, use an artificial id number for the key (why?)

    The script ascent-data.sql provides information about some ascents that you can use to seed your ascent table.

Feel free to include other fields that seem reasonable. It is my intention to leave the specification of this database somewhat open-ended to encourage you to reflect on the various possibilities. In a realistic application you would have long conversations with other stakeholders about the requirements of the database, and that would lead to specific choices about the data that needs to be stored and the data types used to represent that data.

Other Considerations

As I mentioned above, it is my intention to let you finalize some details of this database. However, here are some things to consider:

Submit your SQL script with the four CREATE TABLE statements to Canvas. Include, for example in comments, some of your reasons behind whatever choices you made. Also be sure to include your name in the script at the top (20 points).


Last Revised: 2023-02-01
© Copyright 2023 by Peter C. Chapin <pchapin@vtc.edu>