=========================preview======================
(comp231)[2007](s)final~PPSpider^_10178.pdf
Back to COMP231 Login to download
======================================================

HONG KONG UNIVERSITY OF SCIENCE & TECHNOLOGY
COMP231: Database Management Systems
Spring Semester, 2007
Final Examination
May 21, 2007 12:30am C 3:30pm

Name: ___________________ Student Number: ______________________

Email: ___________________ Lecture Session:______L1 or L2__________

















Questions
Marks
Scores

1
10


2
16


3
12


4
14


5
13


6
17


7
18


Total
100








Instructions:

1. This examination paper consists of 22 pages and 7 questions.

2. Please write your name, student ID and Email on this page.

3. For each subsequent page, please write your student ID at the top of the page in the space provided.

4. Please answer all the questions within the space provided on the examination paper. You may use the back of the pages for your rough work and afterwards draw a diagonal line through it to show that it is not part of your answer.

5. Please read each question very carefully and answer the question clearly and to the point. Make sure that your answers are neatly written, readable and legible.

6. Leave all pages stapled together.

7. The examination period will last for 3 hours.

8. Stop writing immediately when the time is up.



Question 1 General Issues in Database Management Systems (10 marks)
Given the following create statements that construct three tables, where the meaning of the attributes and table names is self-explanatory.

create table CUSTOMER
(
customer-name char(20),
customer-address char(30),
primary key (customer-name))

create table ACCOUNT
(
branch-name char(15),
account-number char(10),
balance number,
primary key(account-number))

create table DEPOSITOR
(
customer-name char(20),
account-number char(10),
primary key (customer-name, account-number),
foreign key (account-number) references account on delete cascade,
foreign key (customer-name) references customer on delete cascade)

(a) (2 marks) Referring to the create statement for the ACCOUNT table, modify the statement to enforce that the branch-name cannot be empty and the balance should always be positive.













(b) (4 marks) Assume the database (three relations) created by the above statements is as follows:



CUSTOMER
customer-name
Customer-address

Wilfred
HKUST

Keith
POLYU

Jeffery
CU



ACCOUNT
branch-name
account-number
balance

SaiKung
A1
1000

ChoiHung
A2
500

Shatin
A3
200




DEPOSITOR
customer-name
account-number

Wilfred
A1

Keith
A2

Jeffery
A3




Explain, with the aid of appropriate updated tables if necessary, what happens to the above database if we run the following SQL statements. (We assume that the result of a st