G51DBS 2009-2010 Coursework 2
Worth 5% of the total mark.
Deadline: I would prefer to mark it in the lab on the 16th or the 23rd (please also
submit your coursework to cw system, id 263, even if you had it marked in the lab -
for the record). You can submit to cw without any points deducted until
Friday the 26th of February, 23:59. After that you can still submit until
the 5th of March at 23:59 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 263.
The comment character in SQL is --. For example files, look at 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:
- Book with columns title, publisher, year, isbn.
Select appropriate datatypes for the columns (year can be an integer) and a primary key which is isbn. For examples
of books you can use the following bibtex records from a big computer science bibliography
database DBLP :
@book{DBLP:books/ph/UllmanW97,
author = {Jeffrey D. Ullman and
Jennifer Widom},
title = {A First Course in Database Systems},
publisher = {Prentice-Hall},
year = {1997},
isbn = {0-13-861337-0},
bibsource = {DBLP, http://dblp.uni-trier.de}
}
@book{DBLP:books/aw/HopcroftU79,
author = {John E. Hopcroft and
Jeffrey D. Ullman},
title = {Introduction to Automata Theory, Languages and Computation},
publisher = {Addison-Wesley},
year = {1979},
isbn = {0-201-02988-X},
bibsource = {DBLP, http://dblp.uni-trier.de}
}
- Person with columns personID, firstName, lastName.
Select appropriate datatypes for the columns (assume ID is an integer)
and a primary key.
- Author with columns which are foreign keys into Book
and Person , namely personID and isbn of the book where the person
is one of the authors of the book.
Tuples:
Insert data about the two books above in the database. Use a sequence to generate
personIDs. [Comment: a better solution would be to use something like JeffreyDUllman1, JeffreyDUllman2 etc.
for IDs and generate them by hand or using a program which checks for the person's affiliation,
research area and so on to decide if it is the same person or a new author with the same
name. But I would like you to practice using sequences.]. To avoid looking up generated
IDs when inserting tuples into Author you can use
INSERT INTO Author VALUES ('0-13-861337-0', (SELECT PersonID FROM Person WHERE LastName = 'Ullman'));
which is generally not a good idea, but works in our example because last names happen to be unique
(they will not be in general of course).
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. Note that to
make Book display nicer I included at the end of the file formatting
instructions for columns in my cw2.sql solution:
COLUMN Title FORMAT A30;
COLUMN Publisher FORMAT A15;
This wraps up the text in the Title column to 30 alphanumerical characters.
Marking scheme
You get full marks (5/5) if your output is similar to
the model output (ID values could be different)
and keys are declared properly. To check whether you defined
primary keys correctly try, for example, adding a new book
with the same isbn number
as some book already in the database, and a different title
(should not succeed).
To check whether you defined foreign keys
properly, try to delete
the whole table Book - this should be impossible since books
are referenced by the Author table.
Mistakes such as not declaring constraints properly will be penalised by 1
or 2 marks.