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.

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.

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)

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)

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)

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)

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  <insert account number here>  of <insert  bank name here> has been withdrawn at  <insert  transaction date and time here>.

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.