CIS-3010 Lab Assignment #2: PeakBaggers Database

Due: Friday, February 8, 2019

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 appropriate list. 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
      3> :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

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

  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 of 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 "notes" or a URL pointing at the climber's comments about the climb (if any).

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 Moodle. Include, for example in comments, some of your reasons behind whatever choices you made. (20 points).


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