Introduction to Linux and pgaccess

Laboratory Exercise 1

CMP 420: Database Systems
Lehman College, City University of New York
13 February 2003

General Information About the Lab

The machines in this lab run RedHat Linux 7 with KDE. Linux is a variant of the unix operating system designed to run on Pentium systems. The student accounts use the KDE window manager, one of many different choices for the "the look and feel" of the user interface. The directions below are specific to our systems and might not generalize to other systems (for example, those in other labs on campus or your home machines). See the section at the end of this lab for working outside the lab.

The department has the following policies for the lab:

There is no eating or drinking in the lab.
Due to restricted class sizes, children are not allowed in the computer labs.
Do not waste paper-- only print files that directly relate to the class.
Do not enter the lab until the scheduled class time. If the lab is open before class time, it is because another class is using it or for maintenance.
Always log out and turn off the monitor when you leave the lab.

Getting Started

First, turn on the computer-- the button is in the front. The monitor should also come on, if it doesn't, the button for it is also in the front. It will take a few moments for the computer to get started. You will then see a RedHat logo on the left hand side of the screen, and the words linux and win in a white box on the right. Choose linux and hit return. These machines are "dual-boot," with both Linux and Windows. By choosing linux, the machine will boot up under the Linux operating system. This will take a little bit and you will see many system messages scroll by.

When the computer is ready, you will see a login screen. Type the login and password given to you in lab. This will be your account for the semester and will also work if you would like to log directly into comet from home. These accounts will be deleted at the end of the semester. Any student wishing a permanent unix account should inquire about an alpha account at the Information Technology Resource Center.

Click on the terminal icon to bring up a new terminal window (it has a picture of computer on it and is located either on the left hand side of the screen or in the tool bar). In this window, type:

    yppasswd

You will then be asked to type your password, along with a new password. For your password, you should choose at least 6 characters, including at least one non-alphabetic character, and you should choose a password not based on a word in the dictionary. Choose something you can remember since if you forget your password, it can only be reset from Mondays to Thursday from 9-4.

Some Basic Commands

You can start and control programs either by using their graphical interface or at a terminal window. For example, you can start Netscape by single clicking on the netscape icon, or by launching it from the terminal window by typing netscape.

Here are some basic commands in unix:

ls lists all files in your directory
cd sampledir changes the current directory to sampledir.
lpr -Ppr219 filename prints the file filename to the printer in the lab. You can only print while in the lab and not from a remote location.
w shows all users currently on your system
hostname tells the name of the machine you're logged onto.
telnet comet logs you into the server comet

All the user accounts have been created on the server comet and the /home filesystem is remotely mounted using NFS. There are no accounts created locally on any of the workstations. Because of this and the fact that these machines are dual booting systems and they are turned off, you will only be able to send mail out of these workstations. All mail should be addressed as follows:

    username@comet.lehman.cuny.edu

To read your mail from these workstations, you will have to telnet to comet and retrieve your mail.

All files for web pages should be placed in the public_html directory located inside your directory. The home page should be named index.html. The URL for each account is:

http://comet.lehman.cuny.edu/username

The dial up phone numbers are: 960-7278 and 960-7279. On the menu choose number 4 for comet.

Some Basic Database Commands

We will be using the PostgreSQL database package for Linux. It follows the client/server architecture discussed in class. There are several different "front-ends" for the user to interact with the server. Today, we will work with one developed by Great Bridge, called pgaccess.

First, telnet into comet by typing

telnet comet

This is necessary, since most, but not all of the software on comet is available directly in the lab. Next, type

createdb MYNAMElab1

where MYNAME is your name. This will create a database for today's lab.

Now, type

pgaccess MYNAMElab1

This will launch a graphical interface for modifying your database. Select the Tables tab on the left, and then click on New. This will bring up a window that allows you to create a new table. Enter the Movie database from p 71 (second edition) or p 99 (first edition) of the textbook. You will first need to enter the name of the table (movie), then the attributes, or fields, choosing the type of the field as you go. Choose char for the type of filmType When you are done entering fields, click the create button in the bottom right hand corner.

Now that the table has been created, click on the Open button to open it, and enter the rows from p 99 into your table. When you're done, close the table.

Choose the Queries tab and click on the New button. This will launch an SQL window. Type in the following query:

    SELECT title
    FROM "Movie"

Execute the query, and you should see the titles of the movies in the table.

You can also build queries using the Query Builder, which can be accessed by choosing the Visual Designer button from the new query window. The query builder allows you to select the table (you need to highlight it with the mouse), and drag the fields you are interested to the grid on the bottom. You can show and execute the corresponding SQL command with the buttons on top. Build the simple query above, using the query builder. How does the SQL it generates differ from that you typed above?

Write queries that will do the following:

Find the actors in "Star Wars."
Find all films made after 1990.
(Hint: you can use WHERE to introduce a condition on your query. For example: SELECT title FROM movie WHERE length < 100; will print out all the movies that are shorter than 100 minutes.)
Find the length of the shortest film (this should be a query that will still work, even more rows are added to the table).
(Hint: in SQL, there are aggregrate operators that take a group of items and return a single number representing quantities like the sum, the average, the minimum, and the maximum. For example: SELECT MAX(year) FROM movie; will print out the most recent (the largest) year a movie was produced.)

Design Your Own Database

If you finish the lab early, you can use the time to begin designing your project database, described below. More information about the project can be found on the project webpage.

As the course progresses you will be building a substantial database application for a real-world scenario of your choosing. You will design a relational schema for the database, and you will create an actual database using a relational database management system. You will populate the database with sample data, write interactive queries and modifications on the database, and develop user-friendly tools for manipulating the database. For ideas on possible databases, see the book's webpage.

Your first step is to identify the domain you would like to manage with your database, and to construct an entity-relationship diagram for the data. Pick an application that you will enjoy working with, since you'll be stuck with it for the rest of the semester.

Try to pick an application that is relatively substantial, but not too enormous. For example, when expressed in the entity-relationship model, you might want your design to have in the range of five or so entity sets, and a similar number of relationships. Note that this is a ballpark figure only! You should certainly include different kinds of relationships (e.g., many-one, many-many) and different kinds of data (strings, integers, etc.), but your application is not required to use advanced features, such as subclassing, multiway relationships, or weak entity sets, if they are not appropriate for your application.

The following are part of the project and are due on Thursday, 20 February in lab. See the project page for more details.

Describe the database application you propose to work with throughout the course. Your description should be brief and relatively informal. If there are any unique or particularly difficult aspects of your proposed application, please point them out. Your description will be graded only on suitability and conciseness.
Specify an entity-relationship diagram for your proposed database. Don't forget to underline key attributes and include arrowheads indicating the multiplicity of relationships. If there are weak entity sets, indicate them by double lines, as described in class.

Submitting Your Lab

Show your database, queries, and individual database to Professor St. John, before leaving the lab today.

Logging Out

Before leaving the lab, you need to log out and shut down your computer. First, you should quit all applications. Then, go to the K menu in the bottom left hand corner and select Log Out. Choose the Shut Down option, and shut off the monitor when you're done.

Remember to pick up any files you sent to the printer and to take any personal belongings. It is difficult to get back into the lab later (since it's locked when not in use), so, it's worth making sure you haven't forgotten anything.