Universit ¨at Konstanz Assignments
Lehrstuhl dbis Informationssysteme
Prof.Marc H.Scholl/Svetlana Mansmann WS 2006/07
1.How To:Connecting to PostgreSQL
1 Where is the Database Server
In this course we will use a popular open-source relational DBMS PostgreSQL in its recent version
8.1.4.PostgreSQL is installed on the host with the DNS name phobos29.inf.uni-konstanz.de.
Each of you who got registered for this course using the account tool has a database user account
named after your linux login name.
The data in PostgreSQL is arranged into database instances where each database is a collection of
schemata and each schema is a set of tables.Schema is used similarly to a namespace for grouping
tables.Each database contains a default schema called public which contents is readable for all
Our course’s database is called infosys.In addition to schema public where our shared tables will
be placed,infosys has a schema for each user (schema’s name corresponds to the user’s name).
You are the owner of your schema,i.e.you have all privileges in it.Feel free to create tables,populate
themwith data and query it.Your schema is the right place for storing your SQL-related homework.
2 Web-Client access - anytime anywhere
You can conveniently access the database server using a web-client phpPgAdmin running at
http://phobos29.inf.uni-konstanz.de/pgAdmin and enjoy the graphical interface with the over-
view of existing databases,schemata,tables,constraints,data,priviledges,etc.
2.1 Change Your Password!
Your account has been initialized with the password identical to your login name.Hurry up to change
it!Open http://phobos29.inf.uni-konstanz.de/pgAdmin/in your webbrowser and login.Follow
the link Account,and then Change password.Follow the instructions on the page.
2.2 Snifﬂe around!
Take advantage of the web interface - you can explore the data without much knowledge of SQL by
simply clicking on the links.Navigation on the left side shows the available databases.Navigate to your
schema in the course’s database and visit schema public as well.
Take some time to ﬁnd out how to create tables,deﬁne constraints,insert/edit/delete data,with and
without having to enter the SQL code.
3 GUI Client access
You can install a GUI client of your choice on your own PCand connect to the database remotely.Con-
sider installing pgAdmin3 – it is free and available for different platforms.The most popular shareware
GUI with a 30-day free trial is Navicat.
4 Command line access - for “hard core”users
You can access the database directly in the shell from any lab machine by executing the command
psql.It is somewhat less comfortable but you should learn at least the basics in order to be able to
create a log fromthe console output for the electronic submission of your homework.
4.1 Conﬁgure your shell environment
Since you connect to a remote database,you need to store its location as an environment variable in
the.bashrc.<username> (e.g.,.bashrc.mueller for user mueller) in your home directory.Open
.bashrc.<username> in a text editor and add the following line to it:
In order for the change to become effective you must open a new console window.Thats all!
4.2 Connecting to the database server
• Log on to any lab machine (make sure to be logged under your name and not use the session
of your teammate!)
• In the console,execute the following command:
You will be prompted for the password,type it in and press Enter.
• If you succeed,you will see a greeting display similar to this one:
Welcome to psql 8.0.8,the PostgreSQL interactive terminal.
Type:\copyright for distribution terms
\h for help with SQL commands
\?for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
Each psql session is limited to the database speciﬁed at the connection.To connect to another
database,exit and re-connect with psql <dbname>
• The SQL command prompt infosys=#allows you to enter SQL statements.By default,you are
logged into your schema,so all the statements will be executed within that schema.
• Enter an SQL statement,completed by a semicolon
,and press Enter to execute it.The result
will be diplayed directly in the colsole window.For example:
infosys=#CREATE table Foo (ID INT NOT NULL PRIMARY KEY,Name VARCHAR(15));
infosys=#INSERT INTO Foo VALUES (5,’Bar’);
INSERT 0 1
infosys=#SELECT * FROM Foo;
id | name
5 | Bar
• To disconnect,type\q or press Strg D to return to the normal shell mode.
4.3 Some useful commands
You need to be logged in to the database as described in section 4.2,in order to be able to execute
any of the commands below.
• Changing the password:
ALTER USER username WITH PASSWORD ’password’;
where username is your database account name and password is your new password.
• Displaying all the tables in the schema:
• Querying tables in another schema:
simply address each table by its qualiﬁed name schema_name.table_name,e.g.public.employee
for table employee in schema public.
• Executing SQL scripts:
You can store your SQL statements (separate multiple statements by semicolon) in a text ﬁle,for
tables.sql,and execute themby passing your ﬁle to psql as follows:
psql infosys < path_to_your_file
ﬁle shows the path to your ﬁle on the local mashine,such as
A comprehensive PostgreSQL manual can be accessed online at
5 Let’s get started
To get a warmup exercise with SQL,you may connect to a database called cddb which contains a
large part of the freedb
database.It contains the titles of the songs,genres,artists,and albums for
thousands of CDs (i.e.millions of song titles!).
Practice your SQL skills by querying this database.Be extremely careful when using psql command
interface:queries like SELECT * FROM songs;may overﬂow your console or even damage the entire
session.Instead,look for particular albums,artists or songs.You can limit the results of any query to
any number n by appending LIMIT n to your statement,e.g.SELECT * FROM songs LIMIT 20;
freedb is the most complete music database on the internet used by music software (players,burners,rippers etc.) for
retrieving audio CD information