Web Programming

Copyright © 2005
Yaodong Bi

This Web page is designed for students at the University of Scranton, who either are taking Database Systems course or are interested in using PostgreSQL for their projects.

PostgreSQL at the University

PostgreSQL is a open-source relational database management system. A PostgreSQL server has been installed on the department DB server (server8.cs.uofs.edu). Students may have their own databases created on this server and access the databases through the interactive user interface, psql, of Postgresql.

Becoming a User of PostgreSQL

To access PostgreSQL one must be a user of PostgreSQL and have permission to access a database. To become a user, you should make a request by sending an email to the DBA. In your email, use "PostgreSQL Account" as the subject and in the body describe the purpose of the database and provide your department user id and an initial password you would like to use. The DBA will inform you when your account is set up via email.

If you are taking CMPS341 or SE521, your instructor may have told you which database(s) you should use. Go to Using Psql to see how to connect to and interact with the database. If you wish to have our own database, go to Creating a Database.

Creating a Database

Currently no student is allowed to create databases under the PostgreSQL. If you wish to have your own database for your senior project or thesis project, send the DBA a message describing the purpose of the database and provide a name for the database. Normally you want to use a name that describes the contents of your database or your project. The DBA will inform you via email when the database is created.

Using psql via server1 or server2

Normally a user does not log in to our database server directly to interact with our PostgreSQL. The user must use a remote connection to the PostgreSQL. Currently the PostgreSQL server only accepts connections from server1.cs.uofs.edu, server2.cs.uofs.edu, or the department Web server, www.cs.uofs.edu. You can interact with your database from server1 or server2 using the psql client application. Assume the database you need and have permission to access is mydb and your Postgresql user ID is toc2.

To use psql, first log in to either server1 or server2. Then enter:

psql -h server8 -d mydb -U toc2

If you get an error message saying psql is not found, you can either modify your PATH environment variable or enter

/usr/local/bin/psql -h server8 -d mydb -U toc2

Here, option -h specifies the host on which PostgreSQL server is running, -d the database you want to connect to, and -U your postgresql user id. Note that your postgresql user id may not be the same as your department account user id.

If the psql command is executed successfully, the following should be displayed,

Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
mydb=#

Now you are successfully connected to your database. Just in case you don't remember which database you are using, you can find its name in the command prompt.

Assume this is a new database, then it should be empty now. The next step is to create tables.

Creating Tables

You may create tables in two different ways. The first is to create tables interactively.

Interactive
You need to type in SQL statements word by word. For example, to create a student table with three attributes, type in the following:

mydb=# create table student (
mydb=# id        char(5),
mydb=# name      varchar(20)
mydb=# address   varchar(30),
mydb=# primary key (id)
mydb=# );

For availabe data types of PostgreSQL click here. You may type in a SQL statement on multiple lines. The statement is not executed until a simicolon(;) is entered. After a simicolon is entered, the following should be displayed.

CREATE
mydb=#

Now you can execute the \d command to list the tables in the database.

mydb=# \d

The following should be displayed. Owner will be replaced with your user ID.

    List of relations
   Name   | Type | Owner
----------+-------+-------
student   | table | toc2
(1 rows)

One disadvantage of the interactive method is that you have to type perfectly, without a single typing error, since it is very hard to correct or change what you have typed in.

Using Script Files

A script file can be created using any text editor and stored in any directory on either server1.cs.uofs.edu or server2.cs.uofs.edu. This should be done outside the psql client application.

For example, use vi (or pico or any text editor) to create a file and name it table.sql with the following statements as its contents.

create table student (
id        char(5),
name      varchar(20),
address   varchar(30),
primary key (id)
);
create table faculty (
id char(5),
name varchar(20),
office varchar(20),
phone varchar(11),
primary key (id)
);
create table course (
id char(5),
title varchar(30),
instructor char(5),
primary key (id),
foreign key (instructor) references faculty(id)
);
create table stakingc (
sid char(5),
cid char(5),
grade float4,
primary key (sid, cid),
foreign key (sid) references student(id),
foreign key (cid) references course(id)
);

After the file is created, change to the directory where the file is stored and then start psql

/usr/local/bin/psql -h server8 -d mydb -U toc2

and then at the prompt enter the following to execute the script,

\i table.sql

The following will be displayed.

     CREATE
     CREATE
     CREATE
     CREATE
     mydb=#

You may use command \d to list tables in the database. it would list

Now your tables have been successfully created in your mydb database. Our next step is to enter data into the tables.

Insert Data into Tables

To insert data into a table, you may insert one entry at a time by entering a INSERT statement or you can put multiple INSERT statements in a script file and then use \i to execute the script. The following shows both methods.

Enter One Entry at a Time

First use psql to connect to your database (mydb):

/usr/local/bin/psql -h server8 -d mydb -U toc2

then at the psql prompt enter:

INSERT INTO "student" VALUES ('S1001','John','100 Monroe, Scranton');

You may enter the statement on multiple lines, it would not be executed until a semicolon is entered.

Enter Data in Batch Mode

To enter data in a batch mode, put INSERT statements (the following statements) into a file (let's name it data.sql),
connect to your database (mydb):

/usr/local/bin/psql -h server8 -d mydb -U toc2

INSERT INTO "student" VALUES ('S1002','Tony','200 Adams, Scranton');
INSERT INTO "student" VALUES ('S1003','Alex','209 WiLlow, Dalton');
INSERT INTO "student" VALUES ('S1010','Some Name','AMH 118');
INSERT INTO "student" VALUES ('S1020','From the WWWW','WWW@Home');
INSERT INTO "student" VALUES ('F1040','G.W. Bush','The Whitel House');
INSERT INTO "student" VALUES ('S1050','Info System','amh 118-2001');
INSERT INTO "student" VALUES ('S2000','Mike Beckish','STT 414');

INSERT INTO "faculty" VALUES ('F3001','Yaodong Bi','STT 478','6108');
INSERT INTO "faculty" VALUES ('F3002','Dennis Martin','STT 477','6107');
INSERT INTO "faculty" VALUES ('F3003','Paul Jackowitz','STT 479','6109');
INSERT INTO "faculty" VALUES ('F3040','Dick Cheney','The White House','xxx-xxx-xxx');
INSERT INTO "faculty" VALUES ('F9999','TBA','Registrar','941-6108');
INSERT INTO "faculty" VALUES ('F0000','TBA','Registrar','941-6108');

INSERT INTO "course" VALUES ('C2001','Programming in Java','F3001');
INSERT INTO "course" VALUES ('C2002','Operating Systems','F3002');
INSERT INTO "course" VALUES ('C2003','Computer Archiecture','F3003');
INSERT INTO "course" VALUES ('C2004','Software Engineering','F3001');
INSERT INTO "course" VALUES ('C2020','Prog the WWW','F3001');
INSERT INTO "course" VALUES ('C2030','Prog the Web','F3001');

INSERT INTO "stakingc" VALUES ('S1001','C2001',3.5);
INSERT INTO "stakingc" VALUES ('S1001','C2002',3.9);
INSERT INTO "stakingc" VALUES ('S1002','C2003',3.2);
INSERT INTO "stakingc" VALUES ('S1003','C2002',3.9);
INSERT INTO "stakingc" VALUES ('S1003','C2003',4);
INSERT INTO "stakingc" VALUES ('S1001','C2004',NULL);
INSERT INTO "stakingc" VALUES ('S1002','C2001',3.99);

then execute the script using \i at the psql prompt:

\i data.sql

The above data will be inserted into the database in a batch.

Next section is to delete data from your database.

Delete Data from Tables

The SQL DELETE statement may be used to delete rows of data from a table. The syntax of DELETE is:

DELETE FROM table [WHERE condition];

For example, suppose table stakingc has the following tuples,

mydb=# select * from stakingc;
sid | cid | grade
-------+-------+-------
S1001 | C2001 |   3.5
S1001 | C2002 |   3.9
S1002 | C2003 |   3.2
S1003 | C2002 |   3.9
S1003 | C2003 |     4
S1001 | C2004 |
S1002 | C2001 | 3.99
(7 rows)

and the following DELETE is executed,

mydb=# delete from stakingc where sid='S1002';

then, the table would have the following tuples left.

uofsis=# select * from stakingc;
sid | cid | grade
-------+-------+-------
S1001 | C2001 |   3.5
S1001 | C2002 |   3.9
S1003 | C2002 |   3.9
S1003 | C2003 |     4
S1001 | C2004 |
(5 rows)

Of course, you may put multiple DELETE statements in a script file and use \i command to execute all the DELETE in a batch.

Update Data in Tables

You can use the UPDATE statement to replace values of columns (attributes) in a table. The syntax of UPDATE is as follows:

UPDATE table SET col = expression [, ...]
[ FROM fromlist ]
[ WHERE condition ]

For example, if the following UPDATE is executed on the above table stakingc

uofsis=# UPDATE stakingc SET grade=3.99 where sid='S1001' and cid='C2004';

after the statement, the stakingc table would look like:

uofsis=# select * from stakingc;
sid | cid | grade
-------+-------+-------
S1001 | C2001 |   3.5
S1001 | C2002 |   3.9
S1001 | C2004 | 3.99
S1003 | C2002 |   3.9
S1003 | C2003 |     4
(5 rows)

Query Data from Database

Basic SQL Queries

The syntax of basic SQL queries:

    SELECT <attribute list>
    FROM   <table list>
    FROM   <condition>

Example: retrieve the name, office, and phone of faculty whose id is 'F3001'. The SELECT statement for this query can be as follows:

    SELECT name, office, phone
    FROM   faculty
    FROM   id = 'F3001';

Enter the SELECT statement in psql:

uofsis=# SELECT name, office, phone
uofsis-# FROM faculty
uofsis-# WHERE id = 'F3001';

The following result is displayed:

name | office | phone
------------+---------+-------
Yaodong Bi | STT 478 | 6108
(1 row)

SQL Queries with Multiple Tables

When an SQL statement need information from more than one table, the WHERE clause of the SELECT statement may be used to specify how the tables are to be JOINed together.

Example: retrieve for all student the student name, course title and the grade the student received for the course.

    SELECT s.name, c.title, stc.grade
    FROM   student s, stakingc stc, course c
    WHERE s.id = stc.sid AND stc.cid = c.id;

Enter the SELECT statement in psql:

uofsis=# SELECT s.name, c.title, stc.grade
uofsis-# FROM student s, stakingc stc, course c
uofsis-# WHERE s.id = stc.sid AND stc.cid = c.id;

The following result would be displayed:

PostgreSQL On-Line Resources

Home page of PostgreSQL

PostgreSQL: Introduction and Concepts by Bruce Momjian. You may read the book online. It is the most authoritive book for PostgreSQL.