|
The purpose of this assignment is to help the students get familar with basic concepts and commands in Postgresql, an open-source object-relational database management system originated from UC Berkeley. You will learn how to connect to a DB server, use a psql command line DB client and basic operations like creating a new database, setting up the tables and insert data.
- DB server: A Postgresql server is running at db.ic.ucsc.edu, use your CATS account to login. For off-campus users, you can use SSH clients to connect the server, telnet is not supported for security concern. SSH clients are available for most *NIX systems and for Windows platform, you can try SSH Secure Shell.
- Set up the path: Once you login, you will have access to your CATS home directory. The basic Postgresql commands, e.g., psql, are at /home/bin. So to make life easier, you may want to add that directory to your PATH variable in the .login file (Use your favorite editor, e.g., vi, to open .login and add a line like "set path=( $path /home/bin)" at the end of that file). If you dont want to change your .login file, you need to change to /home/bin directory or append that path before any Postgresql command.
- DB authentication: Postgresql uses a seperate authentication mechanism from UNIX authentication, so you need to obtain another password to access the DB server. When you try to use the Postgresql client, you will be asked for the password. The DB password is your CATS username+1234.
- Part 1: read Postgresql online manual: Preface, Part I Tutorial, and Managing databases in Part III Server Administration. If you have time, read other parts and prepare for the following lab assignments. Some parts of the manual are designed for DB server administration, so you may not have the permission to touch some directories and configuration files. If you want to learn the administration stuff, you are encouraged to install one on either windows or UNIX platform.
- Part 2: create and use your own DB: the basic architecture of Postgresql DBMS is client/server: a DB server daemon is running at db.ic.ucsc.edu and users use DB clients to access the DB server. The DB server hosts a number of databases(catalogs). Each database consists of a set of tables with useful data. There are many available clients, either command line clients or GUI clients. The one comes with Postgresql package is called psql. Refer to Postgresql manual or the other resources or use "psql --help".
- create a new database: follow the steps in manual 1.3.
Use your CATS username as the new DB name. This is the configuration constraints we imposed on the server. You can create DBs with other names, but you may not be able to access them because of the configuration in pg_hba.conf.
- Access the database: follow the steps in manual 1.4. The general format is "psql -a dbname", where
the -a is to have the input echoed to the console for saving a script log. If you don't want your input echoed, you can omit the -a, but please do include it when you are running an execution script to hand in. You can try other options other than -a as well. If you omit dbname, then the psql client connects to the default DB with the same name as your username.
- Create the tables: follow the steps in manual 2.3. We will have two tables: weather and cities. Note that as a custom, SQL statements are written in UPPER case, although it is executed the same way if you write them in lower case. For the identifiers, unless your put them in double-quote("), they are treated as case-insensitive. See this post for details and Postgresql Archive for discussion.
- Populate the tables with actual data and query the tables: follow the steps in manual 2.4 and 2.5. Use Insert command or import from an external data file.
- Aggregation functions: follow the steps in manual 2.7.
- Update existing rows use UPDATE: follow the steps in manual 2.8.
- Delete rows with DELETE and delete tables with DROP: follow the steps in manual 2.9 to delete rows. To delete tables, use DROP TABLE tablename command.
- Part 3: record your session: In the following lab assignments, uses need to record their sessions, i.e. the commands and the results. One way is to cut and paste to a file if you have windowing capabilities. Another better way is to use script command, please read "record your session" in the lab policies document. You should learn how to use script command, please note the difference with and without -a option.
- Part 4: use script files: Users can use a script file which include text form of SQL commands with \i command in psql. The syntax is "\i data.script" in the psql client.
- Here is an example script file that creates a table (relation) named products:
CREATE TABLE products (
productID INT,
name VARCHAR(80),
price NUMERIC(10,2),
retailPrice NUMERIC(10,2)
);
Here is an example script file that loads four tuples into the table (relation) named products created using the previous script file:
COPY products FROM stdin USING DELIMITERS '|';
1419|American Greetings CreataCard Gold V4.0|21.49|25.24
1424|Barbie(R) Nail Designer(TM)|20.74|25.99
1427|Panzer Commander|21.99|30.24
1431|Riven: The Sequel to Myst|31.99|40.24
\.
This is the format you will use for the files that load data into your tables. The USING DELIMITERS '|' and the use of '|' as a delimiter is optional. The default delimiter is the tab character. The delimited data on each line must match the attributes and their types in your table in a one to one manner and in the order they were defined in your CREATE TABLE commands. The COPY data must be terminated with "\.".
NO submission for this lab assignment.
-
|
