4/9/2019 Assignment 1
https://moodle.cqu.edu.au/mod/quiz/review.php?attempt=1518645&cmid=887686 1/22
COIT11237 DATABASE DESIGN & IMPLEMENTATION
Started on Tuesday, 9 April 2019, 9:16 PM
State Finished
Completed on Tuesday, 9 April 2019, 9:16 PM
Time taken 31 secs
Marks 14.00/14.00
Grade 24.00 out of 24.00 (100%)
https://moodle.cqu.edu.au/
4/9/2019 Assignment 1
https://moodle.cqu.edu.au/mod/quiz/review.php?attempt=1518645&cmid=887686 2/22
Information
COIT11237 Assignment 1
Due date: Friday of Week 5 (Midnight AEST time) ASSESSMENT
Weighting: 30%
1Length: Typically 20-30 hours
Objectives
This assessment item relates to the following unit learning outcomes in the unit
profile:
Demonstrate competency in using Structured Query Language
Differentiate database applications, systems, and their role in supporting
business processes.
Task Description
You will complete an online quiz for which you will write SQL queries.
You will be randomly allocated questions. You can attempt the quiz as many times
as you like. Your mark will be determined by your highest grade. You will receive
feedback as soon as you submit. You are encouraged to re-attempt the quiz until
you have received full marks. If you are marked incorrect for an answer that you
think is correct, please read the feedback and then discuss the question with your
lecturer or email Jamie:
[email protected]Important notes:
Your answers will not be saved until you click "Finish Attempt" and
then "Submit All and Finish". You may then reattempt the quiz.
This assignment is an individual piece of work. Discussing the questions
with other students is encouraged; however, the answers you submit
for this assignment must be individual work, produced by you.
The sample solution will usually be released on the Friday of Week 7.
Submissions made after release of the sample solution will receive
ZERO marks.
A penalty of 5% of total marks “Per Calendar Day” will apply to late
submissions. A late penalty can be avoided by applying for an
extension before the due date through unit website.
Database Description
The assignment database contains the following sets of tables:
https://moodle.cqu.edu.au/
4/9/2019 Assignment 1
https://moodle.cqu.edu.au/mod/quiz/review.php?attempt=1518645&cmid=887686 3/22
KWare tables: sales orders and product information tables for the KWare
organisation.
Table Name Description
Primary
key
Foreign
key(s)
KWCustomer
Customer details
of, for example,
Lee Chin.
CID
KWSupplier
Supplier details
of, for example,
Billy Cookware.
SID
KWProduct
Product details
of, for example,
a Billy 2L
Saucepan.
PID
SID: the
product's
supplier
KWSalesOrder
Records
customer
purchases, for
example, Sales
Order
#10000001.
SOID
CID: the
customer
who made
the
purchase
KWSalesOrderProduct
Records the
items that a
customer has
purchased on a
sales order, for
example, a Billy
2L Saucepan
was purchased
on Sales Order
#10000001.
SOPID
PID: the
product
purchased
SOID: the
sales
order on
which the
product
was
purchased
KWPurchaseOrder
Records the
orders that
KWare has sent
to its suppliers,
for example,
Purchase Order
#10000002.
POID
SID: the
supplier
we are
purchasing
from
https://moodle.cqu.edu.au/
4/9/2019 Assignment 1
https://moodle.cqu.edu.au/mod/quiz/review.php?attempt=1518645&cmid=887686 4/22
KWPurchaseOrderProduct
Records the
items that
KWare has
purchased from
its suppliers, for
example, we
ordered 2 Billy
2L Saucepans
on Purchase
Order
#10000002.
POPID
PID: the
products
purchased
POID: the
purchase
order on
which the
item was
ordered
Geocode tables: geocode information for some Australian addresses.
Incorporates or developed using G-NAF ©PSMA Australia Limited
licensed by the Commonwealth of Australia under the Open Geo-coded
National Address File (G-NAF) End User Licence Agreement..
Table Name Description Primary key
Address_Detail
Details about
Australian
addresses,
for example,
a building
named
BELCONNEN
COURT has
postcode
4500.
ADDRESS_DETAIL_PID
Address_Default_Geocode
The longitude
and latitude
of an
Australian
address.
ADDRESS_DEFAULT_GEOC
https://data.gov.au/dataset/geocoded-national-address-file-g-naf/resource/09f74802-08b1-4214-a6ea-3591b2753d30
https://moodle.cqu.edu.au/
4/9/2019 Assignment 1
https://moodle.cqu.edu.au/mod/quiz/review.php?attempt=1518645&cmid=887686 5/22
Shares tables: these tables keep a track of the shares we have
purchased in companies such as BHP. Share registries such as
Computer Share track who owns which shares.
Table Name Description Primary key Foreign key(s)
SHRegistry
The contact
number of
each...