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.
