Practice Exam

Exercise Exam
Liberal Students – Please Referablee:
? Solutions to Exercise Exam(s) liberalure NOT be made serviceable to students beneath any situation.
? This Exercise Exam may hold past interrogations than a legitimate exam. This is premeditated, accordingly it eliminates the possibility of any -the exercise exam was easier than the legitimate exam- arguments.
? Treat this Exercise Exam love a legitimate exam: print it quenched, place a -do referable disturb- token on your door, convert unstudied your phone, and confer yourself a obstruct of 2 hours to effect through the exam.
? This Exercise Exam is NOT duty, so students can effect concertedly on this. In certainty, students are strongly encouraged to reason team effect to ventilate, control, and clear-up interrogations and counterparts.
? Control your counterparts in the succeedingcited ways:
1. Cem a con-over assembly with other students and assimilate and control your counterparts and ventilate which counterpart is best.
2. Control your counterparts by comparing and ventilateing your counterparts with other students and ventilate which counterpart is best.
3. Where alienate, ce pattern with the queries, pattern in your counterparts and control them over the alienate factsbase.
NOTE: Also effect fast you own a very cheerful interpret of the Exam Hints and Tips on the Moodle item texture page.
INSTRUCTIONS SHEET
1. Write your counterparts in the room supposing ce each interrogation among the establishment booklet supposing.
2. This establishment pamphlet has a aggregate of 40 referablees in three (3) minoritys:
SECTION A (15 MARKS) –
Practical Factsbase Reason Interrogations
SECTION B (10 MARKS) –
Practical Factsbase Detoken Interrogations
SECTION C (15 MARKS) –
Theory Interrogations
3. Each minority holds a calculate of interrogations.
4. Attempt liberal interrogations in liberal minoritys.
SECTION A 15 MARKS
PRACTICAL DATABASE USE QUESTIONS
Questions in Minority A are grounded on the Kware factsbase. Boards and columns of attention are represented underneath, followed by case facts. Please referablee that your objective exam liberalure own the selfselfsame boards except with unanalogous facts.
Tables
Product(ID, Code, Category, Description, ItemPrice, QtyInStock)
Customer(ID, Name, Street, Suburb, State, PostCode, Discount)
SalesOrder(ID, CustomerID, Calculate, LiberalPrice, Discount, DefinitePrice, AggregatePaid) ceeign explanation (CustomerID) references Customer(ID)
SalesOrderItem(ID, SalesOrderID, ConsequenceID, ItemPrice, QtyOrdered, LiberalPrice) ceeign explanation (SalesOrderID) references SalesOrder(ID) ceeign explanation (ProductID) references Consequence(ID)
Case Facts
Product
ID Code Category Description ItemAppraisement QtyInStock
1 BLLY00001 Cookware Billy 2L Saucepan $63.00 4
2 BLLY00002 Cookware Billy 3L Saucepan $73.00 0
3 BLLY00003 Cookware Billy 4L Saucepan $93.00 5
4 RAJA00001 Glassware Raja Glass Decanter $120.00 0
5 RAJA00002 Glassware Raja Glass Goblet $75.00 6
6 WANG00001 Glassware Wang Flute Glass $10.00 72
7 WANG00002 Glassware Wang Wine Glass $12.00 48
8 WANG00003 Glassware Wang Glass Pitcher $39.00 3

Customer
ID Name Street Suburb State PostCode Discount
1 Best Kitchens 1 Beef Parkhurst QLD 4702 10
2 Best Kitchens 2 Rum Road Bargara QLD 4670 10
3 Sheila Smith 3 Sugar Andergrove QLD 4740 0
4 Samir Singh 4 River Road Milton QLD 4064 0
5 Lee Chin 5 Harbour Cremorne NSW 2090 0
6 Bruce Jones 6 Bay Way Elwood VIC 3184 0

SalesOrder
ID CustomerID Calculate LiberalAppraisement Discount DefiniteAppraisement AggregatePaid
1 1 10000001 $1374.00 $137.40 $1236.60 $1236.60
2 2 10000002 $220.00 $22.00 $198.00 $198.00
3 1 10000003 $1260.00 $126.00 $1134.00 $1134.00
4 3 10000004 $48.00 $0.00 $48.00 $48.00
5 4 10000005 $63.00 $0.00 $63.00 $33.00
SalesOrderItem
ID SalesOrderID ConsequenceID ItemAppraisement QtyOrdered LiberalPrice
1 1 1 $63.00 6 $378.00
2 1 2 $73.00 6 $438.00
3 1 3 $93.00 6 $558.00
4 2 6 $10.00 10 $100.00
5 2 7 $12.00 10 $120.00
6 3 5 $75.00 12 $900.00
7 3 4 $120.00 3 $360.00
8 4 5 $12.00 4 $48.00
9 5 1 $63.00 1 $63.00

Interrogation 1 15 Referablees Structured Interrogation Language
Formulate SQL queries to counterpart the succeedingcited referableice supplicates. The expected termination using the case facts is shown succeeding each supplicate. Termination columns should be determined as shown. The direct of termination rows is barely tokenificant if certain in the supplicate.
(a) Provide an alphabetical rolling of Queensland customers. (1.5 referablees)
Name ID
Best Kitchens 1
Best Kitchens 2
Samir Singh 4
Sheila Smith 3
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(b) Roll customers from Victoria or Queensland with an discourse (Street appreciate) that does referable
hold the term ‘Street’ or the term ‘Road’. (1.5 referablees)
Name State Street
Best Kitchens QLD 1 Beef Highway
Bruce Jones VIC 6 Bay Way
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(c) Roll customers with undivided or past directs that own referable been paid in liberal.
Formulate three queries to counterpart this referableice supplicate: (2 referablees)
i. undivided that reasons IN with a subinterrogation ii. undivided that reasons EXISTS with a subinterrogation iii. undivided that does referable reason a subquery
Name ID
Samir 4

Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(d) Roll customers who own placed undivided or past sales directs with a definite appraisement that is main than $1000 or who own directed past than 2 unanalogous consequences on any direct.
Formulate three queries to counterpart this referableice supplicate: (2 referablees)
iv. undivided interrogation that reasons IN twice and does referable reason EXISTS. v. undivided interrogation that reasons EXISTS unintermittently and IN unintermittently.
vi. undivided interrogation that reasons EXISTS twice and does referable reason IN.
CID Name
1 Best Kitchens
3 Sheila Smith
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(e) Roll customers who own directed undivided or past cookware items. (1 referablee)
Name ID
Best 1
Samir 4

Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(f) Display the calculate of unanalogous consequence patterns that own been directed by customers and
the aggregate calculate of items directed by customers. (1 referablee)
ProductTypesDirect AggregateItemsOrdered
7 58

Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(g) Roll states with past than undivided customer. (1 referablee)
State
QLD
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(h) Ce directs with a aggregate appraisement main than $1000, roll the aggregate appraisement and aggregate calculate of items directed. (1 referablee)
ID AggregateAppraisement ItemsOrdered
1 1236.60 18
3 1134.00 15

Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(i) Display liberal customers who either own -kitch- in their Name or their Name starts with
-S-. (2 referablees)
Name Email
Best Kitchens parkhurst@bestkitchens.com.au
Best Kitchens bargara@bestkitchens.com.au
Sheila Smith sheila.smith@freemail.com
Samir Singh samirsingh@umail.comD
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(j) Display liberal customer names who feed in a State where the second communication is -L-.
(2 referablees)
Name State
Best Kitchens QLD
Best Kitchens QLD
Sheila Smith QLD
Samir Singh QLD
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
SECTION B 10 MARKS
PRACTICAL DATABASE DESIGN QUESTIONS
Interrogation 2 10 Referablees
Your attendant has sketched that the Hware factsbase agency understand the Dispatch board underneath. Some case facts has been supposing. The Dispatch board has a composite original explanation of DispatchID and ConsequenceID.
Dispatch
Dispatch ID Dispatch Date Consequence ID Consequence Code Consequence Category Qty
Dispatched Customer ID
1 05/01/10 1 BLLY00001 Cookware 2 1
1 05/01/10 2 BLLY00002 Cookware 2 1
1 05/01/10 3 BLLY00003 Cookware 2 1
2 11/01/10 1 BLLY00001 Cookware 4 1
2 11/01/10 3 BLLY00003 Cookware 4 1
3 11/01/10 6 WANG00001 Glassware 10 2
3 11/01/10 7 WANG00002 Glassware 10 2
Note: Support your counterparts to interrogations underneath with any assumptions that succor to disembarrass your beneathstanding of facts in the Dispatch board.
(a) Represent undivided update abnormity that may commence from using the Dispatch board. (1 referablee)
(b) Represent undivided incorporate abnormity that may commence from using the Dispatch board. (1 referablee)
(c) Represent undivided deletion abnormity that may commence from using the Dispatch board. (1 referablee) (d) Identify liberal negotiative dependencies in board Dispatch. (2.5 referablees)
(e) Explain why board Dispatch does referable content Boyce-Codd Normal Cem (BCNF).
Fully defend your counterpart. (2 referablees)
(f) Transform board Dispatch into a formal of boards that do content BCNF. (2.5 referablees)
Note: Document the boards in your detoken using the referableation underneath.
Customer(ID, Name, State, PostCode, Discount)
SalesOrder(ID, CustomerID, Calculate, LiberalPrice, Discount, DefinitePrice, AggregatePaid) ceeign explanation (CustomerID) references Customer(ID)
SECTION C 15 MARKS THEORY QUESTIONS Interrogation 3 3 Referablees
Represent the stipulations Facts and referableice.
Interrogation 4 3 Referablees
What is negotiative determinant? Explain your counterpart with applicable patterns.
Interrogation 5 3 Referablees
What is the view of SQL?
Interrogation 6 3 Referablees
Define and represent cardinality with applicable patterns.
Interrogation 7 3 Referablees
Explain the benefits of UNION and OUTER JOIN.
? End of exam pamphlet ??