The goal of this practice assignment is to familiarize you with PostgreSQL and give you some hands-on knowledge on creating tables and populating them with data.
Familiarize yourself with the PostgreSQL relational DBMS by reading the document Managing a Database in the Interactive PostgreSQL documentation, logging into PostgreSQL, trying some of the examples in the document, and experimenting with the various commands. The PostgreSQL web page has links to documentation and other information. There's also a PostgreSQL At A Glance document describing PostgreSQL's features.
a) Create your first database using the shell command:
createdb
where "username" is your cats login username.
b) Access your database with the command:
psql -a
which will start a command line interface to PostgreSQL connected to the database with your username. (The -a is so input is 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 may create other database names, but they must all start with your username. (By the way, username "wan" should not create databases starting with "wang"!)
To start a command line interface to PostgreSQL connecting to a different database, use the command:
psql -a databasename
where "databasename" is the name of your database (which should begin with your cats username).
c) Create relations (tables) for your database. See the CREATE TABLE
command.
d) To populate your database you can use the INSERT command. You can
also do a bulk data loading using the Copy command.
e) To drop a relation you can use the DROP TABLE command. To delete tuples from a table, use the DELETE command.
f) To make structural changes to your relations such
as adding, modifying or deleting attributes then
look at the "ALTER TABLE" command in the PostgreSQL documentation.
g) To see the contents in a database table, use "SELECT *".
You will be using the psql command line interface to interact with your data base. See Managing a Database for information on starting using psql. You can use a Script file which is text file of psql commands which can be executed like a batch file using the \i command in psql. The syntax is \i filename entered following the psql prompt, where file name is the complete (case sensitive) name of the script file you desire to run.
|
psql -a databasename |
to run PostgreSQL's command line inteface using your database |
|
\i data.script |
to import an execution script |
|
\q |
to exit pqsl (PostgreSQL's command line interface) |
The execution script file you create can consist of most any series of commands which you could enter following the psql prompt. This includes all of the SQL commands which you will be using to create, modify and test your data base. Examples include the CREATE TABLE and SELECT commands. Just like when using the psql command line interface you must terminate each SQL command in your script file with a semicolon. \i and \q do not need semicolons.
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 '\.'