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 21⁄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)
COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Part A (10 marks) 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}isaset.
• 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)
COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Part B (10 marks) SHORT ANSWER
Complete the following truth table.
You will receive up to 2 marks for your answer.
TT TF FT FF
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)
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)
COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Part C (10 marks) SQL
C 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 ActsIn Customer
Director Movie
MovieExec Studio Video
(ActorId, [pk] ActorName, BirthPlace, Nationality, YearBorn, YearDied, Sex) (MovieId [fk1], ActorId [fk2], [pk] StarsIn)
(CustomerId, [pk] CustomerName, CustomerAddress, CustomerPhoneAH, CustomerPhoneBH)
(DirectorId, [pk] DirectorName, YearBorn, YearDied)
(MovieId, [pk] MovieTitle, MovieYear, Genre, CriticRating, Classification, Nominations, Awards, Length, InColor, DirectorId [fk1], StudioName [fk2], CertNoProducer [fk3])
(CertNo, [pk] Name, Address, NetWorth)
(StudioName, [pk] Address, CertNoHead [fk])
(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)
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)
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 ?
SAMPLE Page 9 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Part D (10 marks) 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 Artist Gig Musician
(Album_Id, Title, Label, Year, Artist_Id)
(Artist_Id, Name)
(Album_Id, Musician_Id)
(Musician_Id, Family_Name, Given_Name, Birth_Date, Birth_Country, Death_Date, Death_Country)
Informally, the tables may be described as follows:
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!
Identifiers, names, birth and death details of musicians.
Identifiers, titles, artists and release details of albums.
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)
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)
COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Part E (20 marks) 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)
Major-source
UN-category
SAMPLE Page 13 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
QWERTY (Q, W, E, R, T, Y)
Q→W W, E → R W, E → T W, E → Y
ALICE’S-RESTAURANT (Mock, Turtle, Soup, Humble, Pie)
Mock, Turtle → Soup Pie → → Turtle
SAMPLE Page 14 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
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
ALLEN, HITCHCOCK Cecil B. FORD, , ORD, Born YearDied
1899 1980 1881 1959 1895 1973
(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)
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)
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)
COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
for COMP6240 only Part F (15 marks)
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)
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;
——————————
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)
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)
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)
COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
Question 4
Explain the meaning of the following ER diagram.
MACHINE SHOP
Page 22 of 24 – RELATIONAL DATABASES – (COMP2400/6240)
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)
COMP2400/6240/SAMPLE Final Examination
STUDENT NUMBER:
EXTRA PAPER FOR ANSWERS
Clearly indicate which question(s) you are answering, from which Part(s).
____________________________________ _______________________________
SAMPLE Page 24 of 24 – RELATIONAL DATABASES – (COMP2400/6240)