CITS1402代写编程代做、代写SQL程序设计、Software程序语言代写

日期：2020-11-05 11:29

Department of Computer Science and Software Engineering

SAMPLE EXAMINATION, SEMESTER 2 2018

CITS1402

RELATIONAL DATABASE MANAGEMENT SYSTEMS

FAMILY NAME: GIVEN NAMES:

STUDENT ID: SIGNATURE:

This Paper contains: 14 pages (including title page)

Time allowed: 2 hours

INSTRUCTIONS:

? This exam has 7 single-choice questions, worth 14 marks, and 7 short-answer

questions, worth 46 marks. So, the full mark of this exam is 60 in total.

? Answer the single-choice questions by ticking the correct choice in this examination

booklet, and answer the short-answer questions in the spaces provided

in this examination booklet.

? This is a closed book examination.

? UWA Approved Calculator with Sticker is allowed.

***Note***: This is just sample questions only for your exam preparation.

PLEASE NOTE

Examination candidates may only bring authorised materials into the examination

room. If a supervisor finds, during the examination, that you have unauthorised

material, in whatever form, in the vicinity of your desk or on your person, whether

in the examination room or the toilets or en route to/from the toilets, the matter

will be reported to the head of school and disciplinary action will normally be

taken against you. This action may result in your being deprived of any credit

for this examination or even, in some cases, for the whole unit. This will apply

regardless of whether the material has been used at the time it is found.

Therefore, any candidate who has brought any unauthorised material whatsoever

into the examination room should declare it to the supervisor immediately.

Candidates who are uncertain whether any material is authorised should ask the

supervisor for clarification.

Sample Examination, Semester 2

2018

2.

CITS1402

1. Consider a relation Store1(id,name,type,amount) and Store2(id,name,price) containing

the following tuples

id name type amount

2 iPad 1 1000

3 iPad 2 1000

4 iPod 6 1000

id name price

2 iPad 1000

3 iPad 900

4 iPod 400

How many tuples are returned by the following relational algebra expression?

πname,amount(Store1) ./ πname,price(Store2)

[2 marks]

2. Consider a relation Store1(id,name,type,amount) and Store2(id,name,price) containing

the following tuples

id name type amount

2 iPad 1 1000

3 iPad 2 1000

4 iPod 6 1000

id name price

2 iPad 1000

3 iPad 900

4 iPod 400

How many rows would the following query produce?

SELECT * FROM Store1 NATURAL JOIN Store2;

(a) 0

QUESTION 2 CONTINUES OVER THE PAGE

Sample Examination, Semester 2

2018

2 (Continued)

3.

CITS1402

(b) 3 ***

(c) 4

(d) 5

(e) 9

[2 marks]

3. Consider a relation Store1(id,name,type,amount) and Store2(id,name,price) containing

the following tuples

id name type amount

2 iPad 1 1000

3 iPad 2 1000

4 iPod 6 1000

id name price

2 iPad 1000

3 iPad 900

4 iPod 400

How many rows would the following query produce?

SELECT *

FROM Store1 LEFT JOIN Store2 on Store1.name=Store2.name;

(a) 2

(b) 3

(c) 4

(d) 5 ***

(e) 6

[2 marks]

SEE OVER

Sample Examination, Semester 2

2018

4.

CITS1402

4. Consider two relations R(A,B,C) and T(A,C,D) containing the following tuples

A B C

1 2 4

2 1 4

2 2 1

A C D

3 2 5

2 2 4

2 2 1

2 2 4

How many rows would the following relational algebra produce?

πA,CR ./ πA,DT

(a) 3

(b) 4 ***

(c) 5

(d) 8

(e) 9

[2 marks]

5. Consider two relations R(A,B,C) and T(A,C,D) containing the following tuples

A B C

1 2 4

2 1 4

2 2 1

A C D

3 2 5

2 2 4

2 2 1

2 2 4

How many rows would the following query produce?

SELECT * FROM

(Select A,B From R) AS T1 right join (Select A,C From R) AS T2

ON T1.A=T2.A;

QUESTION 5 CONTINUES OVER THE PAGE

Sample Examination, Semester 2

2018

5 (Continued)

5.

CITS1402

(a) 0

(b) 3

(c) 4

(d) 5 ***

(e) 9

[2 marks]

6. Consider a relation R(A, B, C, D, E) with functional dependencies

D → C, CE → A, D → A, AE → D

Given the three attribute sets, namely BE, ABE and BDE, which of them can

be the key of the relation R?

(a) BE only

(b) BDE only

(c) BDE and BE only

(d) ABE and BDE only ***

(e) All sets

[2 marks]

7. Consider a database used by a university that contains three relations:

Create Table Student (

id int Primary Key,

name Varchar(32));

Create Table Unit(

code Varchar(10) Primary Key,

name Varchar(32));

Create Table Enrolled( sid int, ucode Varchar(10), mark int,

QUESTION 7 CONTINUES OVER THE PAGE

Sample Examination, Semester 2

2018

7 (Continued)

6.

CITS1402

Foreign Key (sid) References Student (id),

Foreign Key (ucode) References Unit (code));

and a view is defined as

CREATE VIEW AcademicRecord AS

SELECT S.name as S Name, U.name as U Name, mark

FROM Student S JOIN Unit U JOIN Enrolled E

On S.id=E.sid and U.code=E.ucode;

If the tables are initially empty, then which of the following choices can make the

view to have at least one row added?

I.

INSERT INTO Student VALUES(101, ’Rob’);

INSERT INTO Unit VALUES(’202’, ’Java’);

INSERT INTO Enrolled VALUES(101,’201’,80);

II.

INSERT INTO Student VALUES(101, NULL);

INSERT INTO Unit VALUES(’201’, NULL);

INSERT INTO Enrolled VALUES(101,’201’,80);

III.

INSERT INTO Student VALUES(101, ’Rob’);

INSERT INTO Unit VALUES(’201’, Java);

INSERT INTO Enrolled VALUES(101,’201’,NULL);

(a) None of them

(b) Just III

(c) Just II and III

(d) Just II ***

(e) All of them

QUESTION 7 CONTINUES OVER THE PAGE

Sample Examination, Semester 2

2018

7 (Continued)

7.

CITS1402

[2 marks]

8. Consider a relation R(A, B, C) and S(A, B, D) containing the following tuples

What to be produced from the expression πA,D (R ./ σB=8 (S))?

Solution:

A D

6 6

[2 marks]

What to be produced from the expression πA (R ./ σB=8 (S))×πD (R ./ σA=6 (S))?

Solution:

A D

6 6

6 7

[2 marks]

SEE OVER

Sample Examination, Semester 2

2018

8.

CITS1402

9. A pizza shop manages its customer orders in a database with three tables called

Customers, CustomerOrder and Suburbs which have the following schemas:

Customers (id INT, name VARCHAR (32));

Suburbs (code CHAR(4), name VARCHAR(32));

CustomerOrder (cid INT, scode CHAR(4), cost double,

order_date DATETIME NOT NULL DEFAULT (GETDATE(),

Foreign Key (cid) References Customers(id),

Foreign Key (scode) References Suburbs (code));

The field Customers.id represents a customer number, the field Suburbs.code

represents a suburb’s area code (such as 6009 for Nedlands), and these two fields

are keys for their tables respectively. The fields cid and scode in CustomerOrder

are foreign keys to Customers.id and Suburbs.code. Assume every suburb only

has one such pizza store and the table Suburbs has the full data, i.e., all records

like (6009, Nedlands) existed in the tables.

(1) Consider a new customer named John Smith who ordered a few pizza with

30 and wants to pick up from a suburb 6009. Write two SQL statements with

the correct sequence that will add the customer and his order into the database,

assuming his customer ID is 1000.

Solution:

INSERT INTO Customers VALUES(1000, ’John Smith’);

Insert CustomerOrder Values(1000, ’6009’, 30);

(2) Write a SQL statement that will list each suburb’s name and the total amount

of sales the pizza shop did on the suburb. The list should be sorted in descending

order using DESC.

Solution:

QUESTION 9 CONTINUES OVER THE PAGE

Sample Examination, Semester 2

2018

9 (Continued)

9.

CITS1402

Select S.name, SUM(CO.cost) AS Total Sale

From CustomerOrder CO JOIN Suburbs S ON CO.scode=S.code

Group By CO.scode

Order By Total Sale DESC;

(3) Write a SQL statement that will list all the suburbs information on which

the pizza shop has sold the total amount of sales no less than $10,000.

Solution:

Select S.code, S.name

From CustomerOrder CO JOIN Suburbs S ON CO.scode=S.code

Group By CO.scode

Having SUM(CO.cost)>=10000;

10. Suppose a bank company has a table BankAccount to maintain their customers’

balance information as below.

Create Table BankAccount(

ID INT NOT NULL,

Name Varchar(24),

Balance Real,

Primary Key(ID));

Create a Trigger named ’CheckBalance’ on Table BankAccount. The trigger

’CheckBalance’ can automatically check the balance of customers when they make

withdraw from their bank accounts. If the balance after withdraw is less than

0, then we require the trigger to send a message ’Balance is not enough’. [Hint:

SIGNAL SQLSTATE ’45000’ SET Message Text=’Balance is not enough.’]

QUESTION 10 CONTINUES OVER THE PAGE

Sample Examination, Semester 2

2018

10 (Continued)

10.

CITS1402

### Write your code from here

DELIMITER ++

Create Trigger CheckBalance

Solution:

BEFORE UPDATE

ON BankAccount

FOR EACH ROW

BEGIN

IF NEW.balance < 0 THEN

SIGNAL SQLSTATE ’45000’

SET MESSAGE TEXT = ’Balance too low’;

END IF;

END++

DELIMITER ;

[6 marks]

11. Transaction isolation is an important part of any transactional system. MYSQL

permits the users to choose how “isolated” they wish each transaction to be by

choosing between READ UNCOMMITTED, READ COMMITTED, REPEATABLE

READ, SERIALIZABLE. Explain what each means in a transactional

database system.

Solution:

Refer to the lecture notes.

[6 marks]

SEE OVER

Sample Examination, Semester 2

2018

11.

CITS1402

12. Strict Two-Phase Locking is the widely used locking protocol to manage the

transactions. Assume two users Kevin and Rod to read and write one item

’Item001’ at the same time from the same table as below:

User Kevin User Rod

Step 1. Read Item001 - value is 10 Step 1. Read Item001 - value is 10

Step 2. Change Item001 - reduce value by 5 Step 2. Change Item001 - reduce value by 3

Step 3. Write Item001 Step 3. Write Item001

List the order of processing at the database server and show the value of Item001

after the two users’ transactions complete.

Solution:

Refer to the lecture notes.

[4 marks]

13. A company manages the salespersons, their sales area, their customers, the

shipped warehouse and the sales amount of salesperson to a customer. The

table below provides the sales-report information.

SalesPersonID SalesPersonName SalesArea CustomerID CustomerName WarehouseID WarehouseLocation SalesAmount

31001 Peter North 15001 Delta Datta 4 Perth 5000

15002 Kevin Smith 3 Nedlands 4500

15003 Ben Rode 3 Nedlands 500

31012 John South 18442 S. Press 2 East Victoria Park 3000

18432 Stodoch Inc 2 East Victoria Park 8000

18542 Flood Repair 1 Canning Vale 3000

(a) Normalise the relation to 2NF and identify primary keys in all the relations

using the underlines.

QUESTION 13(a) CONTINUES OVER THE PAGE

Sample Examination, Semester 2

2018

13(a) (Continued)

12.

CITS1402

Solution:

SalesPerson(SalesPersonID,SalesPersonName, SalesArea)

Customer(CustomerID,CustomerName, WarehouseID,

WarehouseLocation)

Sales(SalesPersonID,CustomerID,SalesAmount)

[4 marks]

(b) Normalise the tables to 3NF. Please identify primary keys in all the relations

using the underlines.

Solution:

SalesPerson(SalesPersonID,SalesPersonName, SalesArea)

Sales(SalesPersonID,CustomerID,SalesAmount)

Customer(CustomerID,CustomerName)

Shipment(CustomerID, WarehouseID)

Warehouse (WarehouseID, WarehouseLocation)

Or

Solution:

SalesPerson(SalesPersonID,SalesPersonName, SalesArea)

Sales(SalesPersonID,CustomerID,SalesAmount)

Customer(CustomerID,CustomerName, WarehouseID)

Warehouse (WarehouseID, WarehouseLocation)

[4 marks]

SEE OVER

Sample Examination, Semester 2

2018

14 (Continued)

13.

CITS1402

14. NAB is a bank to provide home loads for its customers. To do this, NAB needs to

develop a data management system to record all the information about customers,

home loans and their borrows. A loan should have a load number, amount and the

rate. A borrow should include the access date (e.g., home loan commencement

day) and the overall borrow length (e.g., 30 years). One borrowed loan needs

to be linked to a bank account that can be owned by more than one customers

together with different shares, e.g., a couple can share a joint bank account. The

bank account should include the basic information, such as bank ID and balance.

Customers should have their unique ID, personal information such as name and

address.

Please draw an Entity Relationship diagram that captures this information about

the above description of NAB home loan system, including all Entities, Attributes,

Relationships and Minimum Cardinality Constraints, and Primary

Keys of each entity. You must use the formal notations taught in lecture

notes.

Solution:

[6 marks]

SEE OVER

Sample Examination, Semester 2

2018

14.

CITS1402

Blank Page For Working