CIS-3010 Lab Assignment #1: Introduction to Databases

Read/Skim Chapter 1 and Chapter 2 in the text. Review the slides on the SQL SELECT statement, particularly the early slides.

The purpose of this lab assignment is for you to get set up with an example database. The assignment is about getting your tools installed and working.

  1. If you haven't already, install Microsoft's SQL (MSSQL) Server on your system, or make some other arrangement to get access to an instance of MSSQL Server. See my document on setting up MSSQL Server for more information.

  2. Create an empty folder in some suitable place, then download and unpack my lab starter archive. The archive contains a solution file and project file for SSMS as well as a few SQL script files to get started.

  3. To avoid some overflow errors when loading the database with data, you might change the definition of the star table in star-tables-simple.sql so that the period uses the type NUMERIC(7,3). The data set contains some variables with periods that are more than 999.999 days. If you don't do this you can ignore the errors produced for the handful of stars that have such long periods.

  4. Open a Windows console and use the sqlcmd program to connect to your database server. If you are using the locally installed server, and are logged into your Windows system with a user account that has administrative rights, you do not need to provide any options to sqlcmd. Create a database in the program as follows:

            1> CREATE DATABASE Variables;
            2> go
            1>
          
  5. While you are here, you can load the SQL script that creates the star table and execute it:

            1> :r star-tables-simple.sql
            2> :list
            2> go
          

    The :list command shows you the contents of sqlcmd's buffer so you can verify that it is what you expect. Exit from sqlcmd using the :quit command.

  6. Next, double click on the solution file to open SSMS on that solution. Open the "Solution Explorer" window. You will have to remove the connection defined there (it is for my home system). Create a new connection to your database server. Be sure you select "Options" and choose the Variables database. You do not want to use the default database on the server!

  7. Open the file star-data.sql and execute that script to populate the star table with the sample data.

  8. Open the file sandbox.sql and execute that script to observe the results of a simple query against the sample data.

  9. Edit the sandbox.sql file to make the following queries:

    1. All stars in the constellation 'Cas' (Cassiopeia) that have a maximum brightness less than (that is, brighter than) 6.0.
    2. Variables of type 'I', 'IA', or 'IB' are "poorly studied irregular variables." Write a query that displays all of these (show the names and constellations for them).
    3. Refine the previous query so it shows only the poorly studied irregular variables in Sagittarius (abbreviation 'Sgr'). Display the maximum and minimum brightness of each.
    4. Are there any irregular variables in Orion that rise to a maximum brightness that is enough to see with the unaided eye? If so, which ones are there?

There is no submission for this lab. If you work through the steps above you will get full credit (10 points).


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