Sample Final 2006
THE AUSTRALIAN NATIONAL UNIVERSITY
SAMPLE Second Semester Examination – not a real one
RELATIONAL DATABASES
(COMP2400/6240)
Reading Time: 15 minutes
Writing Time: 2 hours for COMP2400 2½ hours for COMP6240
Permitted Materials: One A4 sheet with notes on both sides
Notes may be handwritten or printed/typed
The A4 sheet may be a photocopy
Total Marks: 60 for COMP2400 75 for COMP6240
If your examination paper is missing any pages, or if any pages are illegible,
raise your hand and ask for another examination paper.
In addition to the examination paper, you should have received a sheet of
scribble paper. You may ask for more scribble paper.
All answers are to be written on the examination paper.
Write your student number on every page of your examination paper.
Write your name on this cover sheet in UPPERCASE.
At the end of the examination, hand in your A4 sheet of notes and
your scribble paper as well as your examination paper.
Your A4 sheet of notes and your scribble paper will be destroyed.
Only the examination paper will be marked.
If you are ill during the examination, or otherwise suffer adverse circumstances
during the examination, advise an invigilator immediately.
STUDENT NUMBER:
FAMILY NAME: GIVEN NAME:
SAMPLE Page 1 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
GENERAL INSTRUCTIONS
℘ Write all your answers on the examination paper.
℘ Write your Student Number neatly, especially on the pages which contain
a Part header (eg Page 3).
℘ Do not write in the boxes in the Part headers.
℘ All answers should be written legibly, preferably in blue or black ink
or in very dark pencil (eg 2B). Do not write in red ink.
℘ Answer all questions in the context of the material covered in this course,
including the assignments for this course.
℘ More marks will be awarded for answers that are simple, short and concrete than
will be awarded for answers that are of a sketchy or rambling nature.
℘ Generally, you will receive more marks for answers that show an understanding of
the material than for answers which are simply transcriptions from lecture outlines or
solution notes.
℘ You may ask for clarification of anything written on this paper,
especially if you suspect that there is a typographical error.
℘ However, you should not ask for help with answering any of the questions.
for COMP2400 only
℘ There are no optional questions on this examination paper.
for COMP6240 only
Part F MISCELLANEOUS contains optional questions.
Part F MISCELLANEOUS is the only Part which contains optional questions.
SAMPLE Page 2 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Part A (10 marks) A
TRUE/FALSE
Nominate whether each of the following statements is true or false
by circling either True or False after each statement on this paper.
You will receive 1 mark for each correct nomination;
zero marks for each absent nomination.
You will lose 1/2 mark for each incorrect or unclear nomination.
(1) {r, a, t, b, e, r, t} is a set.
• True • False
(2) {x, y} is a subset of {x, y}.
• True • False
(3) A prime attribute in a relation is an attribute which is a subset of
at least one candidate key of the relation.
• True • False
(4) In the term superkey, “super” means “very good”.
• True • False
SAMPLE Page 3 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
(5) All relations with primary keys are in BCNF.
• True • False
(6) A relation which is in 3NF is in 2NF.
• True • False
(7) “Relation” is just a shorter way of writing “relationship”.
• True • False
(8) Foreign keys can be null.
• True • False
(9) The concept of a transaction provides a mechanism for describing
logical units of database processing.
• True • False
(10) All transactions should maintain a database’s integrity.
• True • False
SAMPLE Page 4 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Part B (10 marks) B
SHORT ANSWER
Complete the following truth table.
You will receive up to 2 marks for your answer.
p q p OR q
T T
T F
F T
F F
Complete each of the following sentences using words or phrases.
The length of the line in the sentences is constant and does not necessarily indicate
the length of the answer to be supplied.
You will receive up to 1 mark for each answer;
zero marks for each absent or incorrect answer.
(1) Given the relational schema
WidgetSupplier (W_no, S_code, [pk] W_cost)
and the associated functional dependency
W_no → W_cost
W_cost is ______________________________ dependent on {W_no, S_code}.
SAMPLE Page 5 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
The three types of modification anomaly are:
(2) ______________________________
(3) ______________________________
(4) ______________________________
When considering database transactions, ACID stands for:
(5) ______________________________
(6) ______________________________
(7) ______________________________
(8) ______________________________
SAMPLE Page 6 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Part C (10 marks) C
SQL
Write SQL queries to answer each of the questions on the following pages.
Write one statement for each question.
The relational schemas for the tables against which the queries will be run:
Actor (ActorId, [pk] ActorName, BirthPlace, Nationality, YearBorn, YearDied, Sex)
ActsIn (MovieId [fk1], ActorId [fk2], [pk] StarsIn)
Customer (CustomerId, [pk] CustomerName, CustomerAddress, CustomerPhoneAH,
CustomerPhoneBH)
Director (DirectorId, [pk] DirectorName, YearBorn, YearDied)
Movie (MovieId, [pk] MovieTitle, MovieYear, Genre, CriticRating, Classification,
Nominations, Awards, Length, InColor, DirectorId [fk1], StudioName [fk2],
CertNoProducer [fk3])
MovieExec (CertNo, [pk] Name, Address, NetWorth)
Studio (StudioName, [pk] Address, CertNoHead [fk])
Video (VideoId, [pk] StockDate, SupplierName, SoldTo [fk1], MovieId [fk2])
Simplified syntax for the SQL select command:
select [distinct]
from
[group by
[order by
SAMPLE Page 7 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
You will receive up to 5 marks for your answer to the following.
(1) Which actor has starred in the most movies?
SAMPLE Page 8 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
You will receive up to 5 marks for your answer to the following.
(2) Which actors acted in the movie Mary Poppins ?
SAMPLE Page 9 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Part D (10 marks) D
INTEGRITY
SQL, Bugs, and Rock ‘n’ Roll Database
This is a music database that catalogues certain albums released by certain artists,
and the musicians who contributed to each album.
Relational Schema
Album (Album_Id, Title, Label, Year, Artist_Id)
Artist (Artist_Id, Name)
Gig (Album_Id, Musician_Id)
Musician (Musician_Id, Family_Name, Given_Name, Birth_Date, Birth_Country,
Death_Date, Death_Country)
Informally, the tables may be described as follows:
Artist
Identifiers and names of artists who release albums.
The name of an artist is a string of characters that might appear on the cover or spine
of albums released by that artist, and has no formal relationship with a musician who
might have a similar name!
Musician
Identifiers, names, birth and death details of musicians.
Album
Identifiers, titles, artists and release details of albums.
Gig
Correspondences between musicians and albums.
A musician may contribute to many albums.
Many musicians may “play a gig” (or just “play”) on a particular album.
SAMPLE Page 10 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
For each of the following rules, specify appropriate constraints that will ensure that
the data in the SQL, Bugs, and Rock ‘n’ Roll Database is consistent with the rule.
Use the constraint syntax available in the Oracle 9i CREATE TABLE command.
You must identify the name of the table in which the constraint is to be placed,
and any other relevant information, but you do not have to write out the full
CREATE TABLE command for each rule.
If it is not possible to ensure adherence to a rule in this way, briefly explain why not.
You will receive up to 2 marks for your answer for each rule.
(1) All albums must be released by a label.
(2) Only musicians who are listed in the Musician table may play on gigs.
SAMPLE Page 11 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
(3) Musicians cannot play gigs for albums which were released before they were born.
(4) The Gig table must have a primary key, formed from one or more attributes shown in
the relational schema for the table.
(5) No two albums are allowed to have the same title.
SAMPLE Page 12 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Part E (20 marks) E
RELATIONAL THEORY
Below are three relations with their associated minimal FD diagram or
minimal FD list.
For each relation,
a) List the candidate key(s) of the relation.
b) Nominate the highest normal form (1NF, 2NF, 3NF, or BCNF) of the
relation.
You will receive up to 1 mark for listing the candidate key(s).
If the candidate key(s) you nominate is(are) correct,
you will receive 1 mark for correctly nominating the highest normal form
of the relation.
You must enclose each candidate key in braces ie {
You will lose up to 1 mark if you do not enclose each and every candidate key
in braces.
You do not need to show your working.
(1) DRUG (Drug-id, Country, Major-source, UN-Category)
CountryDrug-id
UN-categoryMajor-source
SAMPLE Page 13 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
(2) QWERTY (Q, W, E, R, T, Y)
Q → W
W, E → R
W, E → T
W, E → Y
(3) ALICE’S-RESTAURANT (Mock, Turtle, Soup, Humble, Pie)
Mock, Turtle → Soup
Pie → Humble
Humble → Turtle
SAMPLE Page 14 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
You will receive up to 1 mark for your answer to each of the following.
TVS Director Table
Row DirectorId DirectorName YearBorn YearDied
1 1 ALLEN, Woody 1935 null
2 2 Alfred HITCHCOCK 1899 1980
3 3 Cecil B. De Mille 1881 1959
4 7 FORD, John 1895 1973
5 5 Kubrick, Stanley 1928 null
6 14 FORD, John 1895 1968
(1) Suggest a meaning for the null in Row 1.
Given the functional dependency: YearBorn → DirectorName
(2) Is the functional dependency Consistent or Inconsistent
with the data in the table above?
(3) What are the semantics of the functional dependency?
ie State the meaning of the functional dependency in a simple English sentence.
SAMPLE Page 15 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
You will receive up to 2 marks for your answer to each of the following.
(1) Define First Normal Form.
(2) Define the term Primary Key.
(3) Define the term Partial Dependency.
SAMPLE Page 16 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
You will receive up to 5 marks for your answer to the following.
A programmer suggests that The Video Store (TVS) should change its relational database
structure to add a list of all the movies that a director has directed to the Director table.
(The current TVS relational database structure is shown in Part C above.)
Do you think that this suggestion is a good idea?
Give brief reasons for your answer.
SAMPLE Page 17 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
for COMP6240 only
Part F (15 marks) F
MISCELLANEOUS
This Part contains optional questions.
There are five questions in this Part.
Each question is worth 5 marks.
You should answer three (3) questions.
If you answer more than three questions, then the first three questions
which you have answered will be marked.
For example:
If you answer Questions 1, 3, 4, and 5
then you will receive marks for Questions 1, 3, and 4.
If you do not want your answer to a particular Question to be marked,
then clearly cross out the entire answer.
(One cross covering the entire answer is advisable.)
SAMPLE Page 18 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Question 1
A programmer who runs the following query is confused because it looks to him
as though SQL cannot count.
SQL> select distinct genre
2 from movie;
GENRE
——————————
Animation
Comedy
Drama
Family
Horror
6 rows selected.
Explain to the programmer:
a) Why the message 6 rows selected is correct.
b) How to change his code to make the output clearer.
SAMPLE Page 19 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Question 2
The Video Store (TVS) is thinking of adding a column SoldDate to its Video table.
The current structure of the Video table is:
Video (VideoId, [pk] StockDate, SupplierName, SoldTo [fk1], MovieId [fk2])
If TVS were to do this, what domain should be specified for SoldDate?
And what integrity constraints should be added to the updated table?
SAMPLE Page 20 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Question 3
Explain the difference between the Relational Algebra operator SELECT and
the SQL keyword SELECT.
SAMPLE Page 21 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Question 4
Explain the meaning of the following ER diagram.
SHOP
MACHINE
houses
SKILLOPERATOR
requires
has
MACHINE
A FACTORY
SAMPLE Page 22 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Question 5
This question refers to the relational database schema shown in Part C.
Using Oracle9i syntax:
a) Grant read-only access to any database user on the TVS table Director.
b) Create a role for all the programmers who work in TVS.
c) Allow all the programmers who work in TVS to insert rows into the table Director.
d) Allow all the programmers who work in TVS to update the columns
Nominations and Awards in the table Movie.
e) A new programmer has been hired by TV and given the username FredNurk.
Allow this programmer to insert rows into the table Director and
to update the columns Nominations and Awards in the table Movie.
SAMPLE Page 23 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
2006/2 COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
SAMPLE Page 24 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
EXTRA PAPER FOR ANSWERS
Clearly indicate which question(s) you are answering, from which Part(s).
____________________________________
_______________________________
THE AUSTRALIAN NATIONAL UNIVERSITY
SAMPLE Second Semester Examination – not a real o
RELATIONAL DATABASES
(COMP2400/6240)
STUDENT NUMBER: