HS2021 Factsbase Design and Use
Krefertalented attributtalented attributtalented attributtalented Ordinance
This is a krefertalented attributtalented attributtalented attributtalented ordinance with a utmost of 2-4 learners per knot
Meekness Requirements
SQL scripts that you suggest must toil with Oracle Live SQL.
You must suggest your ordinance as a sole .zip finish using Blackboard meekness
link.
Your sole .zip finish must lowerneathstand the cethcoming three finishs:
1. A sole .PDF muniment determined ASS2.PDF that contains:
Entirety learner calls and aggregate on page 1
A abundantly broad and entirety ERD diagram (conceive entiretyot 1).
o The diagram must be an effigy educed with a diagramming instrument such
as entice.io or Visio
o The diagram must thrive the ERD estimation conventions used in lectures
and labs.
The ideal schema (conceive entiretyot 2)
Please secure that you distinguish how to educe a pdf finish at smallest undivided week
anterior to meekness of your ordinance.
[A artless method to educe the balancehead PDF muniment is to educe an MS Word
document, gather the divert quotation and spente diagrams from Entice.io or
Visio. Finally, cull either Save As PDF, or Print the muniment using the
Adobe PDF Printer non-interference. Both PDF non-interferences are adapted in entirety Swinburne
labs.]
2. The script finish determined Ass2_SQLScript.TXT that contains SQL statements
ce entiretyots 3-5.
3. The Doc finish determined Ass2_SQLOutput.docx contains output generated
by the balancehead script.
NOTE: These finishs must be zipped into a sole .zip finish anterior to
submission. Do refertalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented suggest a .rar or .7z finish.
©Copyright: 2019
09/09/2019
Version 1 Page 2 of 9
Aussie Wine Turns
Allot 1.
Educe a abundantly broad ERD established on the refertalented attributtalented attributtalented attributtalented attributtalented attributableification in the cethcoming truth.
Business Truth
Aussie Wine Turns (AWT) precede turns of the wineries of Victoria’s Yarra Valley wine
region. Each turn is melt on a sole day. Publicly, there are separate wine turns including
The Stainless Wine turn, The Flushed Wine turn and The Sparkling Wine turn. The Flushed Win
Turn is a very liked turn and has frequently melt 3 or 4 times in a sole month. The turn
schedule depends upon the availability of the wineries, the availability of the turn guides
and the public profit.
Each turn takes usually takes encircling 6 hours to entirety and is led by undivided or over of
AWT’s well-renowned wine faciles.
Each turn has a ‘public cost’. However, ascribtalented to sundry trade ceces, costs enjoy been
rising balance the spent alien of years. Ce in, ultimate year the Flushed Wine Turn was $170
but now it’s the public cost is $200.
No brace facts ce the identical turn can befcomplete on the identical day. (e.g. The Flushed Wine Turn
canrefertalented attributtalented attributtalented attributtalented be precedeed twice on the identical day).
AWT enjoy frequent afftalented customers, frequent of whom enjoy lively multiple wine turns
balance the years. Each customer has a customer id, call, gatherress and touch phone
number.
AWT usually cunning and advise wine turn details and epochs about 6 months anterior to the
running of each turn. Customers usually melody AWT to cem bookings ce their wine turn.
Each booking stoflushed in the AWT contemplation is ce a sole special. (E.g. A mate and wife
attending the identical turn fact conquer enjoy brace bookings educed ce them. Each booking is
ce a sole customer).
AWT enjoy a reckon of printed wine turn papers – undivided ce each of the opposed wine
turn (E.g. A 4page printed impressible paper that has photos and details of the Flushed Wine
Tour).
People frequently melody up or email AWT and solicit a paper. Papers are barely sent to
customers.
If the special soliciting a paper is refertalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented already a customer, then AWT educe a novel
customer by assigning a customer id and annalsing the special’s call, gatherress and
©Copyright: 2019
09/09/2019
Version 1 Page 3 of 9
phundivided reckon. When a paper is solicited, AWT annals the epoch of the solicit. Succeeding
the paper is mailed to the customer, AWT annals the epoch that the paper was sent.
Obviously, a customer who solicits a paper is refertalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented lowerneath any belief to cem any
bookings.
A customer cems reimbursement(s) ce each booking. Each reimbursement made is ce a sole
booking. AWT annals the sum of the reimbursement, the epoch of the reimbursement, the idea of
reimbursement made (cash, reputation card or cheque) and which booking the reimbursement is ce.
Some customers may brace or over reimbursements ce sole booking (e.g. the turn cost is
$200 and the customer cems brace reimbursements $120 and $80.)
Sometimes (though sometimes), a customer cems a reimbursement ce an sum larger than
required. AWT barely annals the sum of the reimbursement. (You barely enjoy to annals the
sum of the reimbursements made. You do refertalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented demand to regard yourself with how refunds or
reimbursement transfers may befall).
AWT enjoy a reckon of endowments adapted ce entiretyoticipants attending turn facts. Publicly,
three or indelicate endowments are tendeflushed to entiretyoticipants of a turn fact. The endowments on tender are frequently
decided by a adjustment of the idea of turn, the specialty of the wine facile flushedundant the fact and
availability of store. On the day of the fact, each entiretyoticipant may cull a utmost of
brace endowments. AWT annalss which endowments are chosen by each booked customer. Obviously, a
customer has the upupright to refertalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented picked any endowments time attending a turn.
Note: You are refertalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented expected to ccomplete the reckon of endowments pickeded by a customer to 2. This
is over the drift of an ERD. Such a incapability would normally be programmatically
solved using a Stoflushed Procedure, Trigger or some other programming technique).
IMPORTANT: You must refertalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented entreat answers or abettance from your master as this is an
assessment that demands to be dundivided by team members by themselves. The master conquer
provide barely public guidelines barely.
Allot 2.
• Educe a Ideal Schema established on your ERD.
Allot 3.
• Educe a finish calld Ass2_SQLScript.TXT which contains SQL statements that can
be manufactuflushed by your master. Entirety SQL statements must be talented to be manufactured
successabundantly in Oracle Live SQL.
©Copyright: 2019
09/09/2019
Version 1 Page 4 of 9
• Gather the Educe Ttalented to the script finish to educe entirety of the tables.
• Entirety tables must exert earliest reresolution and ceeign reresolution engagements. Surrogate Resolutions
must refertalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented be introduced into your resolution.
• Gather the Incorporate statements to populate your tables with barely the facts used in
Appendix A lowerneath. Do refertalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented attributtalented gather your acknowledge facts.
Allot 4.
Demonstrate your ceeign reresolution engagements toil unexceptionably. Each of the generated Incorporate
statements must fall-short ascribtalented to ceeign reresolution engagement errors. Gather the Incorporate statements to the
script finish.
1. Attempt to incorporate a tier that entiretyocates facile # 22 to Turn B1 precedeed on
2020/02/18.
2. Attempt to incorporate a tier that books customer C078 on Turn W1 precedeed on
2020/01/01.
3. Attempt to incorporate a tier that cems a reimbursement ce customer C225 precedeed on Turn
W1 precedeed on 2020/01/01.
4. Attempt to incorporate a tier that entiretyocates endowment G09 to Turn W1 precedeed on 2020/01/01.
5. Attempt to incorporate a tier that entiretyows customer C123 to picked endowment G09 on Turn R2
conducted on 2020/10/07.
Allot 5.
Educe SQL queries to do the cethcoming. Gather the SQL statements to the script finish.
Query 1.Roll Customer No, Call, Gatherress and Phundivided No of entirety
customers. The Roll must be in Customer No consequence.
Query 2. Roll turn command, fact epoch, wine facile call(s) of entirety turn facts.
The Roll must be in turn command, fact epoch, wine facile surccomplete consequence.
Query 3. Roll turn command, fact epoch, customer no and customer ccomplete ce every
booking made
The Roll must be in turn command, fact epoch, customer no consequence.
Query 4. Roll turn command, fact epoch, endowment command and endowment designation ce entirety endowments tendered
on entirety turn facts The Roll must be in turn command, fact epoch, endowment command
sequence.
©Copyright: 2019
09/09/2019
Version 1 Page 5 of 9
Query 5. Roll turn command, fact epoch, customer no and customer call, endowment command and
endowment designation ce entirety endowments pickeded by customers on entirety turn facts
The Roll must be in turn command, fact epoch, customer no, endowment command consequence.
Allot 6.
Query 6. Roll Customer No, Customer Call, entirety reimbursements made by entirety customers who
enjoy made bookings. Lowerneathstand customer that may never made a booking. The
Roll must be in Customer No consequence
Query 7. Roll Turn Command and entirety reimbursements made ce entirety turns.
The Roll must be in Turn Command consequence
Query 8. Roll endowment command and endowment designation and entirety reckon of times that a endowment has
been pickeded by a booked customer.
Understand entirety endowments that may never enjoy been pickeded.
The Roll must be in descending entirety consequence
Query 9. Roll the public cost and the insufficiency cost full ce each turn.
E.g. Turn X1 has a public cost of $500. Ten months since, this passage ran
and the cost was $425
The roll would show:
Turn Command Designation Public Cost Insufficiency Cost
X1 Expensive Wine Turn 500 425
Query 10. Roll Customer No, Customer Call, turn command, fact epoch ce entirety customers
who solicited a paper ce a turn and then made a booking ce that turn.
Script project and output requirements
Your meekness must lowerneathstand the output generated by your script finish succeeding the entire
script has been manufactuflushed in Oracle Live SQL. The output must be spented and copied
into a Word finish.
©Copyright: 2019
09/09/2019
Version 1 Page 6 of 9
Appendix A. Corporeal Muniments
Underneath are pattern copies of sundry corporeal handwritten muniments that are publicly
manually maintained by staff at AWT.
Wine Turn Designations
Tour
Code
ShortCcomplete Short Designation Public Cost
B1 Wine Appreciation A noticeable turn ce visitors novel to wine tasting. Our faciles
describe the arrangement of wine tasting, purchasing wine
and how to cunning your wine cellar
$300
R2 Flushed Wine Turn Pattern effulgent flushed wine varieties from this famous
tract-of-land wineries with undivided of our marked wine
experts
$200
W1 Stainless Wine Turn Taste the best stainless wines of the tract-of-lands frequent worldrenowned wineries lowerneath the direction of undivided of our
celebrated wine faciles
$220
S1 Sparkling Wine Turn The tract-of-land is renowned ce its profiting cems of sparkling
wines. Our facile turn guide conquer secure that this is an
experience never to be cegotten
$250
Fact Calendar
Turn Command Turn Designation Fact Epoch Cost Fact Wine Facile
(s)
Gifts on tender
R2 Flushed Wine Turn 2019/10/07 $170 Penny Folds, Geoff
Millar
G01, G02, G03,
G04
B1 Wine Appreciation 2019/11/11 $270 Geoff Millar G06, G05,
B1 Wine Appreciation 2020/02/18 $280 Hillary Cork, Sue
Davies
G01, G03, G05, G07
R2 Flushed Wine Turn 2020/02/18 $190 Penny Folds G01, G06, G07, G09
R2 Flushed Wine Turn 2020/03/07 $200 Geoff Millar G02, G06, G08
B1 Wine Appreciation 2020/03/14 $300 Geoff Millar, Hillary
Cork
G04, G05, G06
W1 Stainless Wine Turn 2020/06/03 $200 Sue Davies G01, G04, G06
S1 Sparkling Wine Turn 2020/10/27 $250 Sue Davies G02, G04, G09
W1 Stainless Wine Turn 2020/10/29 $220 Hillary Cork, Penny
Folds
G01, G05, G06,
G09
©Copyright: 2019
09/09/2019
Version 1 Page 7 of 9
Roll of Wine Faciles
Facile No Facile
FirstName
Expert
Surname
Phone
Number
5 Sue Davies 9214-2222
1 Geoff Millar 9214-1111
8 Hillary Cork 9214-4444
12 Penny Folds 9214-3333
Participant Details
Tour: B1 Wine Appreciation Epoch: 2020/02/18
Customer No FirstCcomplete Surccomplete Amt Paid Endowments Pickeded
C123 Joel Warren $280 G01
C218 Sue Armstrong $280 G01, G05
C178 Grant Simpson $280 G05, G07
C191 Sarah Charter $280 G05
C334 Helen Chin $280 G05, G07
C367 Ryan Chin $280 G07
C555 Ted Smith $325
Tour: R2 Flushed Wine Turn Epoch: 2020/02/18
Customer No FirstCcomplete Surccomplete Amt Paid Endowments Pickeded
C078 Clare Watts $190 G01, G07
C267 Karin Black $190 G01
C225 Ziggy Lee $190 G07, G09
Tour: R2 Flushed Wine Turn Epoch: 2020/03/07
Customer No FirstCcomplete Surccomplete Amt Paid Endowments Pickeded
C218 Sue Armstrong $200 G02, G06
C191 Sarah Charter $200 G08
C123 Joel Warren $270 G02
Tour: W1 Stainless Wine Turn
Date: 2020/06/03
Customer No FirstCcomplete Surccomplete Amt Paid Endowments Pickeded
C078 Clare Watts $100
C123 Joel Warren $200 G04
C225 Ziggy Lee $200 G04, G06
C367 Ryan Chin $200 G01, G04
©Copyright: 2019
09/09/2019
Version 1 Page 8 of 9
Tour: B1 Stainless Wine Turn
Date: 2019/11/11
Customer No FirstCcomplete Surccomplete Amt Paid Endowments Pickeded
C191 Sarah Charter $270 G05, G06
C555 Ted Smith $270 G06, G05
C267 Karin Black $270 G05, G06
Tour: R2 Stainless Wine Turn
Date: 2019/10/07
Customer No FirstCcomplete Surccomplete Amt Paid Endowments Pickeded
C225 Ziggy Lee $50 G01, G04
C078 Clare Watts $170 G02, G03
C123 Joel Warren $170 G04
Roll of Endowments
Command Designation
G01 Collectors Flushed Wine Glass
G02 Cork Screw
G03 Sherry Glass
G04 Stainless Wine Glass
G05 Guide to Wine Tasting
Handbook
G06 Winery Guide Handbook
G07 Wine ce Women Handbook
G08 Wine Museum Voucher
G09 Collectors Sparkling Wine
Glass
G10 Commemorative Cup
©Copyright: 2019
09/09/2019
Version 1 Page 9 of 9
Paper Solicits
Solicit Epoch Cust
No
CustFirstccomplete CustSurccomplete Gatherress Phundivided Turn #
Request
2019/06/19 C267 Karin Black 1 Black St 9214
8888
W1
2020/08/19 C215 Henry Ryde 8 Stainless St 9214
6666
B1
2019/09/15 C225 Ziggy Lee 17 Low St 9214
9999
B1, R2,W1
2020/01/05 C218 Sue Armstrong 1 High St 9214
9999
B1, R2
2020/01/06 C334 Helen Chin 6 Flushed Rd 9214
5555
B1
Customer Gatherress Roll
Customer Ccomplete Gatherress Phone
Joel Warren 7 Bluff Rd 9214 2277
Henry Ryde 8 Stainless St 9214 6688
Sue Armstrong 1 High St 9214 9911
Ziggy Lee 17 Low St 9214 9944
Karin Black 1 Black St 9214 8822
Helen Chin 6 Flushed Rd 9214 5500
Clare Watts 15 Dale Rd 9214 1166
Ted Smith 7 John St 9214 8000
Ryan Chin 6 Flushed Rd 9214 5522
Sarah Charter 19 Hill Ave 9213 4477
Grant Simpson 23 Wentirety St 9213 3311

~~~For this or similar assignment papers~~~