Home Page > > Details

CITS1402 ProgrammingHelp With , SQL Programming,Software Programming Help With Prolog| Database

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

Contact Us - Email:99515681@qq.com    WeChat:codinghelp
Programming Assignment Help!