CSIT115 Spring 2021 Page 1 of 9





School of Computing and Information Technology

Student to complete:

Family name

Other names

Student number



CSIT115
Database Management and Security
South Western Sydney and Wollongong


Final Examination Paper
Spring 2021


Exam duration 3 hours + 30 minutes of submission time
Weighting 60 %
Items permitted by examiner OPEN BOOK - any reference materials permitted

Aids supplied Nil

Directions to students 7 questions to be answered.
Submit an answer to each question in a separate pdf file
through Moodle.
Mark the question number and sub-question number
clearly for each solution.
If an answer is hand-written then take a picture or scan it
and submit a file in either pdf, jpeg, jpg, gif,
bmp, png, or tiff format through Moodle.
In all cases, it is your responsibility to make sure that the
submitted files are readable. The files that cannot be read
will not be evaluated.

Marks will be deducted for the late submissions at a
rate of 1 mark per 1 minute late.


CSIT115 Spring 2021 Page 2 of 9




QUESTION 1 (10 marks)
Read and analyse the following specification of a sample database domain.

We would like to store in a database information about the
restaurants, menu items, employees, and customers.
A restaurant is described by a name, address, phone number and
status. Name and address can uniquely identify a restaurant. A
phone number of a restaurant is unique too.
A restaurant employs employees. An employee is a cook, server, or
dishwasher. An employee is described by an employee number, name,
and salary. Employee number is unique within a restaurant
Meal menus are available in restaurants. A meal menu is described
by an item number, item name, category, and price. An item number
is unique within a restaurant.
Customers can order meals from the meal menus. A customer is
described by name, address, phone number. The name and address can
identify a customer. The phone number can identify a customer too.
Whenever a meal is ordered by a customer, an order date and time
must be recorded in a database. A customer is allowed to order one
meal at one date and time. It is optional that a customer chooses
to delivery meal when order meals.
Draw a conceptual schema for the specification of a database domain listed above. Use a notation of
UML simplified class diagrams explained to you during the lecture classes in the subject.

There is NO NEED to provide a detailed analysis how a conceptual schema has been created. The
final conceptual schema expressed in a notation of UML simplified classes is completely sufficient.

It is not allowed to add any attributes that are not mentioned in a specification above.

CSIT115 Spring 2021 Page 3 of 9


QUESTION 2 (10 marks)
Consider a conceptual schema given below.


Your task is to perform a step of logical database design, i.e. to transform a conceptual
schema given above into a collection of relational schemas.

For each relational schema clearly list the names of attributes, primary key, candidate keys
(if any), and foreign keys (if any). Assume, that a superset method must be used to
implement a generalization.


CSIT115 Spring 2021 Page 4 of 9


THE QUESTIONS 3, 4, 5, 6, and 7 REFER TO THE RELATIONAL TABLES
LISTED BELOW
CREATE TABLE BANK(
bank_name VARCHAR(50) NOT NULL, /* Bank name */
hq_country VARCHAR(30) NOT NULL, /* Country part of headquarters address */
hq_city VARCHAR(30) NOT NULL, /* City part of headquarters address */
hq_street VARCHAR(30) NOT NULL, /* Street part of headquarters address */
hq_bldg_num DECIMAL(4) NOT NULL, /* Building number part of headquarters address */
web_site VARCHAR(200) NOT NULL, /* Link to Web site */
email VARCHAR(100) NOT NULL, /* Email address */
bank_type VARCHAR(30) NULL, /* Bank type */
CONSTRAINT BANK_PK PRIMARY KEY (bank_name),
CONSTRAINT BANK_CK1 UNIQUE (hq_country, hq_city, hq_street, hq_bldg_num),
CONSTRAINT BANK_CK2 UNIQUE (web_site),
CONSTRAINT BANK_CK3 UNIQUE (email),
CONSTRAINT BANK_CHECK CHECK (bank_type IN ('investment', 'commercial', 'central', 'agriculture',
'construction', 'industrial') ) );

CREATE TABLE BRANCH(
bank_name VARCHAR(50) NOT NULL, /* Bank name */
br_name VARCHAR(50) NOT NULL, /* Branch name */
br_country VARCHAR(30) NOT NULL, /* Country part of branch address */
br_city VARCHAR(30) NOT NULL, /* City part of branch address */
br_street VARCHAR(30) NOT NULL, /* Street part of branch address */
br_bldg_num DECIMAL(4) NOT NULL, /* Building number part of branch address */
total_emps DECIMAL(3) NULL, /* Total number of employees at a branch */
CONSTRAINT BRANCH_PK PRIMARY KEY (bank_name, br_name, br_country, br_city),
CONSTRAINT BRANCH_CK1 UNIQUE (br_country, br_city, br_street, br_bldg_num),
CONSTRAINT BRANCH_FK1 FOREIGN KEY (bank_name) REFERENCES BANK(bank_name) );

CREATE TABLE EMPLOYEE(
emp_num DECIMAL(7) NOT NULL, /* Employee number */
first_name VARCHAR(20) NULL, /* Employee first name */
last_name VARCHAR(20) NOT NULL, /* Employee last name */
date_of_birth DATE NOT NULL, /* Date of birth */
position VARCHAR(100) NOT NULL, /* Position occupied */
bank_name VARCHAR(50) NOT NULL, /* Bank name */
br_name VARCHAR(50) NULL, /* Branch name */
br_country VARCHAR(30) NULL, /* Country location of a branch */
br_city VARCHAR(30) NULL, /* City location of a branch */
CONSTRAINT EMPLOYEE_PK PRIMARY KEY (bank_name, emp_num),
CONSTRAINT EMPLOYEE_FK1 FOREIGN KEY (bank_name, br_name, br_country, br_city)
REFERENCES BRANCH(bank_name, br_name, br_country, br_city) );

CREATE TABLE ACCOUNT(
account_num DECIMAL(10) NOT NULL, /* Account number */
bank_name VARCHAR(50) NOT NULL, /* Bank name */
balance DECIMAL(9,2) NOT NULL, /* Account balance */
account_type VARCHAR(8) NOT NULL, /* Account type: savings, checking, loan */
CONSTRAINT ACCOUNT_PK PRIMARY KEY (account_num, bank_name),
CONSTRAINT ACCOUNT_FK1 FOREIGN KEY (bank_name) REFERENCES BANK(bank_name),
CONSTRAINT ACCOUNT_CHK1 CHECK( account_type IN ('savings','checking','loan') ) );

CREATE TABLE TRANSACTION(
acc_num DECIMAL(10) NOT NULL, /* Account number */
bank_name VARCHAR(50) NOT NULL, /* Bank name */
tr_date_time DATETIME NOT NULL, /* Transaction date */
amount DECIMAL(7,2) NOT NULL, /* Amount involved */
type VARCHAR(10) NOT NULL, /* Transaction type */
CONSTRAINT TRANSACTION_PK PRIMARY KEY(acc_num, bank_name, tr_date_time),
CONSTRAINT TRANSACTION_FK2 FOREIGN KEY(acc_num, bank_name) REFERENCES ACCOUNT(account_num,
bank_name),
CONSTRAINT TRANSACTION_CHK1 CHECK( type IN ('deposit', 'withdrawal' ) ) );

CSIT115 Spring 2021 Page 5 of 9


QUESTION 3 (10 marks)
Write the data definition statements of SQL that modify the structures and/or constraints of a
database listed on a page 4 of the examination paper in the way described below.

Note that some of the modification may require more than one data definition statements of SQL
statement.

(1) Modify a structure and consistency constraint of the sample database such that after a
modification it is possible to record in the database information about the balance of an account
that is non-negative and not larger than 99999999.99.
(2 marks)

(2) Modify a structure and consistency constraint of the sample database such that after a
modification, it is possible to store in the database information about the total number of
branches for each bank. The total number of branches is a non-negative integer not larger than
999.
(2 marks)

(3) Modify a consistency constraint of a sample database such that after a modification, the
employee’s first name, last name, and date of birth can identify an employee.
(2 marks)

(4) Modify a structure of a sample database such that after a modification, it is possible to store in
the database the phone number of each bank. Assume that the phone number is no longer than
16 digits 16.
(2 marks)

(5) Modify a structure of a sample database such that after a modification, the first name and last
name are no longer than 30 characters each.
(2 marks)


CSIT115 Spring 2021 Page 6 of 9


QUESTION 4 (10 marks)
Write the data manipulation statements of SQL that modify the contents of a database listed on page
4 of the examination paper in the ways described below.

Note, that you are not allowed to modify and/or to drop any consistency constraints. Also note, that
to implement some of the modifications listed below you may need more than one data
manipulation statement of SQL.

(1) Add a new account (the account number is 8211623456) opened at National
Australia Bank into the sample database. Add a new transaction for the new account into
the sample database. The other values of attributes are up to you.
(2 marks)

(2) An account (the account number is 7621312345) earlier opened at Commonwealth Bank
Australia has been closed by the customer. Delete from the database information about this
account and all the transaction information related to it. Remember that the foreign keys in all
CREATE TABLE statements have no ON DELETE CASCADE clause.
(2 marks)

(3) Decrease the total number of employees for the branches located in Sydney, Australia by
5 people.
(2 marks)

(4) Use an advanced DML statement to create a new table TRANSACTION2019 and copy all the
transactions finalized in 2019 in the new table. There is no need to enforce any consistency
constraints on the new table.
(2 marks)

(5) Use an advanced DML statement to delete the accounts such that the balance of each account is
equal to zero and has no related transactions.
(2 marks)

CSIT115 Spring 2021 Page 7 of 9


QUESTION 5 (10 marks)
Write SELECT statements that implement the following queries on a database listed on page 4 of
the examination paper in the ways described below.

(1) Find the bank name and headquarter country of banks that have a branch with more than 30 but
less than 50 employees.
(2 marks)

(2) Find the bank name and total amount of balance of saving account for each bank. Display
zero if a bank has no saving account. Sort the data in descending order of the total amount
of balance for each bank.
(2 marks)

(3) Find the account number and bank name of all transactions that happened between 1st January
2021 and 5th January 2021.
(2 marks)

(4) Find the bank name of the banks that have no checking accounts.
(2 marks)

(5) Find the account number, bank name and amount of deposit transactions that happened in
October 2021.
(2 marks)


CSIT115 Spring 2021 Page 8 of 9


QUESTION 6 (7 marks)
Assume that a user root with a password 'csit115' created a database called banks. The user
executed CREATE TABLE statements given on page 4 of the examination paper to create the
relational tables in the database banks. The user root nominates a database banks as a default
database.

Write SQL script that performs the following operations as a user root. Assume that a user root is
already connected to the database.


(1) The user root creates two users: clerk and manager.
(1 mark)

(2) The user root grants the read and write access rights on relational table ACCOUNT to the
manager. The read and write access rights cannot be propagated to other roles or users.
(1 mark)

(3) The user root grants the rights to insert the rows into relational tables TRANSACTION to
clerk. The access rights can be propagated to other roles or users.
(1 mark)

(4) The user root grants the update privilege on all relational tables in the banks database to the
manager. The privilege cannot be propagated to other roles or users.
(1 mark)

(5) The user root grants the read access rights to information about the account number, bank
name, and the total number of transactions happened for each account to the manager. The
privilege cannot be propagated to other roles or users.
(2 marks)

(6) The user root sets the resource limits for the users created in step (2) and it allows maximum 3
concurrent connections.
(1 mark)


CSIT115 Spring 2021 Page 9 of 9


QUESTION 7 (3 marks)
MySQL does not allow for specification of complex data consistency constraints over the contents of a
database within CREATE TABLE or ALTER TABLE statements.

Write one SQL statement to display information about the contents of a database listed on page 4 of
the examination paper to verify the consistency constraint for each loan account.

"A loan account must not be withdrawn."

The script must display the outcomes of the consistency constraint verifications as a single column
with the following messages.

A loan account of has been
withdrawn at .

For example, if a loan account 6121212345 of National Australia Bank has a withdrawal transaction
on 2021-10-01 at 10:00, then verification of the consistency constraint must display the following
message.

A loan account 6121212345 of National Australia Bank has been withdrawn at 2021-10-01 10:00.

Use the function CONCAT to create the messages like the one listed above.