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 statement 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 on the database management system we will be using throughout the course. You will also start getting experience with some tools that we will be using.

Part 1: Using Lemuria

  1. Make sure you can log into Lemuria using an SSH client program of your choice. If you are using Windows, PuTTY is a popular option. Be aware that you must override the default port of 22 and use 2221 instead (the SSH server on Lemuria has been configured to listen to port 2221). Lemuria's host name is lemuria.cis.vtc.edu. You should have received Lemuria credentials in email, or you may already have an existing Lemuria account.

  2. Create a folder on Lemuria named cis-3010 off your home directory. Put all your work for this class into that folder. This facilitates making backups as described below.

  3. Download the lab starter archive. Use a tool such as WinSCP (or similar) to transfer the archive to Lemuria. Alternatively you can use the links program on Lemuria to download the archive directly there. However, you will likely want to set up WinSCP (or similar) anyway to transfer your work to/from Lemuria later.

  4. On Lemuria (via SSH) create a default database for yourself. The example commands below, and throughout this course, are for a hypothetical user named "Jill J. Jones." Her VSC username is "jjj00000@vsc.edu", and her username on Lemuria is "jjones." You should replace her name with yours in this command, and in other examples as appropriate. Sample commands on Lemuria show the prompt, usually as $, to provide context. You do not need to type the prompt.

          $ createdb jjones
        

    Jill's default database has the same name as her username on Lemuria. Although not strictly necessary, you may find it convenient to have a default database. You can use it as a sandbox area for experimentation without having to worry about damaging a "real" database. If you ever create a mess out of your default database, you can always delete it and create a new one like this:

          $ dropdb jjones
          $ createdb jjones
        
  5. Next create a database to hold the variable stars example. You can use this for certain assignments and to follow along with my development of this database in the lecture.

          $ createdb jjjVariableStars
        

    Here 'jjj' are Jill's initials. Replace this with your own initials as they appear in your VSC username. This is necessary so that we don't get conflicting database names. If everyone named their database 'VariableStars' there would be a problem. In the future, prefix all the database names you create on Lemuria (other than your default database) with your initials.

  6. Unpack the starter archive (in your cis-3010 folder) and issue the psql command, attaching to your VariableStars database. The command below shows Jill's database as an example. Text after '#' is a comment and not part of the command.

          $ cd cis-3010
          $ unzip lab-01-starter.zip  # You may have to move this file to cis-3010
          $ psql -d jjjVariableStars
          jjjVariableStars=>
        

    The psql command displays a prompt showing you the database to which you are attached. If you don't specify a -d option at all, you will connect to your default database. You can leave the psql command by typing \q (for 'quit'). Use \? for help on psql commands.

  7. Create the constellation and star tables in your database by running the SQL script in star-tables-simple.sql. Here the \i command "inputs" an SQL script into psql and causes it to be executed.

          jjjVariableStars=> \i star-tables-simple.sql
          CREATE TABLE
          CREATE TABLE
        

    The "CREATE TABLE" output means a table was successfully created. Since the script defines two tables, there are two lines of output.

  8. Load the constellation data and the star data into your database using the following commands:

          jjjVariableStars=> \i star-data-constellation.sql
          INSERT 0 88
          jjjVariableStars=> \i star-data.sql
          INSERT 0 1
          ... (many repetitions)
        

    The output from the first command shows that 88 records were successfully inserted (the zero is related to OIDs, which is no longer a supported feature; ignore it). This is information about the 88 constellations in the sky. The output of the second command should be 47660 insertions of single records, one at a time. The star data is inserted one record at a time, whereas the constellation data is inserted all at once. WARNING! It takes a long time to insert the star data (several minutes). It may appear as if nothing is happening because the same text is continuously scrolling. Be patient!

    In fact, there is one star record that fails to insert because the period of the variable is greater than 10,000 days. The data type used to store the period is limited to 9999.999 days. This isn't important for our purposes, but it means the total number of star records, when the insertion is finished should be 47659. You can verify that using a command such as:

          jjjVariableStars=> SELECT COUNT(*) FROM star;
           count
          -------
           47659
          (1 row)
        
  9. You are now ready to try a query. For example:

          jjjVariableStars=> SELECT name, const, max_bright, min_bright
          jjjVariableStars-> FROM   star
          jjjVariableStars-> WHERE  const = 'Ori' AND min_bright <= 7.5;
        

    The query ends with the semicolon (';') at the end of the last line. Thus, this is a multi-line query. Notice how the prompt changes (slightly) when accepting the additional lines. This query prints information about all variable stars in the constellation Orion that never fade below 7.5th magnitude (i.e., with a minimum brightness that is brighter than 7.5). Note that smaller magnitudes are brighter. My favorite star, CK Orionis should be in this list!

  10. Use the \q command to quit psql.

  11. Lemuria is not backed up. It is also running on old hardware. You are advised to back up your work regularly as a protection from catastrophic data loss should Lemuria fail during the semester. Run the following backup script:

          $ backup.sh
        

    This script backs up only the material in your cis-3010 folder, or which ever course folder you specify when asked. This is why you should put everything related to this course in the course folder. It leaves a dated tarball in your home directory. Use a tool such as WinSCP to transfer that file elsewhere, such as to your personal computer. I recommend doing this at least once per week.

    Note that this does not actually back up your database contents, only the files in your cis-3010 folder. In a later lab I will describe how to back up your databases.

Part 2: Using DBeaver

In the earlier part you used psql on Lemuria to manipulate and access your database. You will also want to use a graphical tool on your own computer to do this. We will use both approaches throughout this course. Proceed as follows:

  1. Download and install DBeaver Community to your personal machine (or a lab machine, as appropriate). DBeaver Community is a free tool that allows you to access a wide variety of database management systems, including, but not limited to, PostgreSQL.

  2. DBeaver will require that your database account have a password. Normally Jill can log into the PostgreSQL DBMS on Lemuria as jjones without a password provided she is logged into Lemuria under the same username (jjones). The PostgreSQL server has been configured to trust the Linux authentication mechanism. However, remote login attempts must be done using a password. Note that your password in the database system is not necessarily the same as your Lemuria password (unless you make them the same).

    Log into Lemuria with SSH, and use psql to attach to your default database (or any of your databases). Then issue the ALTER ROLE command as follows:

          $ psql
          jjones=> ALTER ROLE jjones WITH PASSWORD 'secretpassword';
        

    Replace 'jjones' with your username and, of course, use a real password. It is important to enclose the password with single quotes and to include a semicolon at the end of the command. If you forget the semicolon, psql will think the command continues to the next line. You can just type a semicolon on that line to complete the command.

    You can now exit from psql and log out of Lemuria.

  3. I have prepared an 18-minute video that describes how to set up DBeaver to access one of your databases on Lemuria via an SSH tunnel. Please review the video if you aren't sure how to proceed.

Part 3 (OPTIONAL): Setting up PostgreSQL on Windows

Some of you may wish to run an instance of PostgreSQL locally on your own machines. This is not a requirement for this course, but you may find it convenient or interesting. It would also allow you, potentially, to complete assignments without Internet connectivity, which might be valuable for some of you. Here I assume you will be using a modern Windows system (Windows 10 or Windows 11). However, the general approach I describe here should apply to Linux or macOS as well.

  1. Download and install the latest version of the PostgreSQL DBMS for your platform.

  2. I have prepared a 19-minute video that describes how to configure the PostgreSQL server on Windows.

There is no submission for this lab.


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