Lab 1 exercises (pdf) - Department of Computer Science

frightenedfroggeryData Management

Dec 16, 2012 (4 years and 11 months ago)

462 views

Computer Science COMP2400,Lab 1
July 23,2007
1 Introduction
This exercise will introduce the PostgreSQL database,and demonstrate simple
SQL queries.In this exercise you will create a database reflecting the relational
schema from Figure 5.5 in Elmasri and Navathe (p136 in the 4th Edition).We
will modify this schema slightly:in the schema as published,employees are
uniquely identified by a social security number (ssn)—we will use an employee
number (enumber) instead.
2 Using PostgreSQL
At the Unix command line,type psql to start the PostgreSQL interactive in-
terface.You are now connected to your own dedicated PostgreSQL database.
psql accepts SQL queries as well as psql commands which start with the ’\’
character.Some useful psql commands are:
\?
Print help on psql commands
\q
Quit psql and return to the Unix shell
\dt
List the currently defined tables
\d <table-name>
Describe a table,listing its columns and their data types
\i <file-name>
Input commands from a file.
3 SQL Exercises
1.Create the schema using the SQL scripts provided.
(a) Download the file lab1.zip from the comp2400 web site.Unzip this
in a fresh directory (eg comp2400/lab1).
(b) Fromthe psql prompt,execute the commands in the file create
schema.sql.
(c) Use the\dt command to check that the tables employee,department,
project and dept
location have been created.
(d) Using the SQL command select * from table;,take a look at the
data that has been inserted into these tables.
2.Write SQL queries to answer the following questions or perform these
actions:
1
(a) What are the first and last names of the person with employee ID
20915?
(b) List the last name and employee ID of all employees with a salary of
more than $60,000.
(c) Add the employee <‘Alan’,‘M’,‘Turing’,20584,‘23/6/1912’,
‘Bletchley Park,Bucks,UK’,‘M’,20584,1000> to the database.
(d) Remove the Canberra location of the Finance department from the
database.
(e) Create a ‘Research’ department.Locate it in Brisbane.
(f) Give everyone in department number 1000 a raise in salary of $1,000.
(g) harder What is the name of the manager of the ‘Information Tech-
nology’ department?
3.The following operation should not work.Verify that this is the case,
and explain why.What would need to be done to make this operation
possible?
(a) Delete the ‘Information Technology’ department.
4.Populate the project table with some data of your own.
5.Create tables for the works
on and dependent relations as per the example
in the text.works
on has attributes empid (employee ID),pno (project
number) and hours.dependent has attributes empid,dependent
name,
sex,bdate (date of birth) and relationship.
Take into account the relationships in the schema when designing the
tables.
6.Optional.Questions to ponder.
(a) What would happen if we deleted ’Dijkstra’ from the employee ta-
ble?(hint think about query 2g above).
(b) How could we change the database schema to prevent this problem
from occurring?
(c) What additional problems would occur if we made this change to the
schema?
7.Optional.More SQL queries.These examples cover SQL features that
may not have been covered yet in lectures,so you may need to read ahead
to work out how to do them.
(a) What is the home address of the manager of the Finance Depart-
ment?
(b) Where are the people who work for ‘Dijkstra’ located?
(c) How long have the managers of each department managed their re-
spective departments?
(d) How many people work in the Finance department?
(e) What is the total annual salary bill of the Canberra branch?
2