G51DBS 2008-2009 Coursework 2
Worth 5% of the total mark.
Deadline: I would prefer to mark it in the lab on the 18th (please also
submit your coursework to cw, id 183, even if you had it marked in the lab -
for the record). You can submit to cw without any points deducted until
Friday the 20th of February, 23:59. After that you can still submit until
the 27th of February at 16:00 but I will subtract points for lateness.
General instructions
Make sure you have an Oracle account
Please make sure you can use Oracle first. Instructions are in
Getting started with Oracle.
Run SQL from a file
It is possible to create the same database and insert the tuples interactively,
and you are welcome to practice this way, but for the exercise
please place all your SQL statements in a file. Call it cw2.sql,
save it in the directory from which you start sqlplus,
and run the statements by typing start cw2.sql; at the
sqlplus prompt.
We will ask to see your file in the lab when we are marking the
exercise. Please also submit it to cw, coursework id is 183.
The comment character in SQL is --. For example files, look at the last year's
exercise solutions here .
Exercise
Create a file containing SQL statements which will create tables
described below and populate them with specified tuples. Run statements from the file
to create a database instance.
Tables:
- Participant with attributes pnumber and name. Values for
pnumber are of type INT and values for name are of type string of at most
50 characters (VARCHAR(50)). Neither can be NULL. The primary key is pnumber.
There are no foreign keys.
- Competition with attributes cdate and place. Values in
cdate are of type DATE, values in place are strings of at most 20 characters.
Both together are the primary key. There are no foreign keys.
- Result with attributes pnumber (foreign key into Participant),
cdate, place (foreign key into Competition) and points which is of type INT.
The primary key is pnumber, cdate and place together.
Tuples:
Insert data about:
- participants Alice with pnumber 113, Bob with pnumber 115, and Chris
with pnumber 120.
- competitions in Nottingham on the 16th of February 2009, Birmingham
on the same date, and London on the 17th of February 2009. You can input
dates as strings in DD-MON-YY format, for example '16-Feb-09'.
- results for Alice in Nottingham on the 16th of 20 points
and for Bob in Nottingham on the 16th of 30 points.
How to fix mistakes
If you make a mistake and want to remove a table called Sometable
(together with all its contents), type DROP TABLE Sometable;
at the sqlplus prompt.
If you want to remove or change a tuple, use DELETE FROM Sometable WHERE ...
or UPDATE Sometable SET ... WHERE ...
How to check the result
To see the result, type SELECT table_name FROM user_tables;
This will give you a list of all the tables you have made.
If you want to see the details for a table called Sometable, type
DESCRIBE Sometable;
This will list the column names, data types, and whether NULLs are
allowed for Sometable.
If you want to see which tuples are in Sometable, type
SELECT * FROM Sometable;
This will list all the rows in Sometable.
We will use these commands to mark the exercise. Here is
the model output
Marking scheme
You get full marks (5/5) if your output for DESCRIBE is as in
the model output, rows in the tables are similar to the model output,
and keys are declared properly. To check whether you defined
primary keys correctly try, for example, adding a new participant
with the same number
as some participant already in the database, and a different name
(should not succeed).
To check whether you defined foreign keys
properly, try to remove Nottingham competition on the 16th
drop
the whole table Competition - this should be impossible since competitions
are referenced by the Result table.
Mistakes such as not declaring constraints properly will be penalised by 1
or 2 marks.