G51DBS 2007-2008 Coursework 2
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
We will ask to see your file when we are marking the exercise.
The comment character in SQL is --. For example files, look at the last year's
exercise solutions here .
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.
- Room with attributes roomName and roomLocation. Values in both
are strings of at most 20 characters. roomName can not be NULL. It is the primary key.
There are no foreign keys.
- Activity with attributes actModCode, actDescription. Values in actModCode
are exactly 6 characters long, values in actDescription are strings of at most 10 characters.
Neither can be NULL. Both together are the primary key. There are no foreign keys.
- Schedule with attributes schSlot (where values are stings of at most
10 characters), and roomName, actModCode, actDescription from
the tables above. roomName and (actModCode, actDescription) are foreign keys. The primary key
Insert data about your own timetable this semester (for the modules you are taking this
semester, usually it is 6 modules), using the university room names. For example,
LT2 is JC-EXCHGE-C.LT2, for Location you can put Exchange or Central Teaching Facility.
The timetable is available here .
Activity is supposed to represent a lecture or a lab session for a particular module.
For example, for the G51DBS lecture on Monday use actModCode G51DBS and actDescription
Lecture1. For G51PRG lab use actModCode G51PRG and actDescription Lab.
In Schedule, use strings like Mon 9-10 for slots.
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
If you want to see which tuples are in Sometable, type
This will list the column names, data types, and whether NULLs are
allowed for Sometable.
SELECT * FROM Sometable;
We will use these commands to mark the exercise. Here is
the model output
for just G51DBS and G51PRG assuming PRG labs are 14:00-16:00. To make
the column names actModName and actDescription display in full,
you can add
This will list all the rows in Sometable.
COLUMN actModCode FORMAT A10;
to your file.
COLUMN actDescription FORMAT A14;
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 room with the same name
as some room already in the database, and a different location (should not succeed).
To check whether you defined foreign keys
properly, try to remove one of the rooms mentioned in the Schedule or drop
the whole table Room - this should be impossible since rooms are referenced by Schedule.
Mistakes such as not declaring constraints properly will be penalised by 1
or 2 marks.