CIS-3010 Lab Assignment #1: Introduction to Databases

Read/Skim Chapter 1 and Chapter 2 in the text. Chapter 7 in the text gives information on SQL and Section 7.3.1 on page 156 talks about the SELECT statment specifically. 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 VariableStars;
            2> go
  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

    You can also populate the table with some data on 41949 variable stars by doing:

            1> :r star-data.sql
            2> go

    The :r (run) command executes the SQL script indicated by its argument. 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.

    If you need to run sqlcmd again, you might want to specify the database to use on the command line by providing the -d option.

  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 VariableStars database. You do not want to use the default database on the server!

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

  8. 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 variables with type "SR:" (semi-regular; the trailing colon means the type is uncertain) in Orion that rise to a maximum brightness that is enough to see with the unaided eye (i. e., brighter than 6th magnitude)? If so, which ones are there?

Submit a short document that shows the SQL queries you used in the part above (20 points).

Last Revised: 2020-01-23
© Copyright 2020 by Peter C. Chapin <>