CIS-3010 Lab Assignment #2: PeakBaggers Database

Due: Thursday, February 6, 2020

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

Before you start, you might want to first create a new database in your server instance. I suggest using the sqlcmd program:

      1> CREATE DATABASE PeakBaggers;
      2> go
      1> :quit
    

Start SSMS, connect to your new database, and define a new project/solution for your SQL scripts. We will be using this database for more than one lab.

Overall Database Structure

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).

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

  1. mountain. This table contains information about the mountains themselves. It should contain at least the name of the mountain, a way of characterizing its location, and its elevation. Use an artifical id number for the key (why?). I suggestion calling the key attribute mountain_id. I suggest using the MSSQL IDENTITY modifier to get the database server to automatically generate ID values.

    To specify a location, consider storing the town in which the mountain is located along with a longitude and latitude for the mountain (are these independent?). The GPS position will help when it comes to supporting queries like "find all mountains within 100 miles of Boston." Unlike what I did in the VariableStar database, I strongly recommend using a single value for longitude and a single value for latitude. Thus: +43.5 degrees instead of 43 degrees, 30 minutes, 0 seconds. This will make things easier later on.

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

  2. climber. This table contains information about the people doing the climbs. It should contain at least the name of each person, some form of contact information (such as an email address), and the climber's birth date. Use an artifical id number for the key (why?)

  3. 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.

Be sure to include foreign key references between the ascent table and the other two tables. The ascent table should record the mountain and climber id numbers, but those numbers should reference the keys in the other two tables!

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. In this lab you are both the database designer and the "other stakeholders" which gives you the flexibility, within the limits defined here, to do what you want.

Other Considerations

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

Submit your SQL script with the three CREATE TABLE statements to Canvas. Include, for example in comments, some of your reasons behind whatever choices you made. (20 points).


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