Assignment Answers Pdf Of Primary Key Street Varchar:2814ICT

Assignment Answers Pdf Of Primary Key Street Varchar:2814ICT

2814
ICT & 7003ICT

Assignment Part 1
Page
1
2814
ICT
D
ATA
M
ANAGEMENT
7003ICT
D
ATABASE
D
ESIGN
School of Informatio …

Preview text

2814
ICT & 7003ICT

Assignment Part 1
Page
1
2814
ICT
D
ATA
M
ANAGEMENT
7003ICT
D
ATABASE
D
ESIGN
School of Information & Communication Technology
Trimester
1
, 20
22
Assignment Part
2
Designing a Database for
WareMart
Aims
To
analyse and comprehend a given ER diagram and Relational Database schema
.
To implement a database based on the given ER diagram and Database schema
.
To write required SQL statements to query the database
.
To write SQL statements to manipulate the data in the
database
.
Learning Objectives
In the process of this assessment task you will:
p
lan, schedule and execute project tasks with a view to improving your personal productivity;
gain awareness of the typical chall
enges related to the practical implementation of databases;
learn how to use Data Definition statements to implement a database from a given ER diagram
and the corresponding Database schema
; and
learn how to use Data Manipulation statements to query a database, and insert and update
data in the tables
.
Due date:
Week
11
,
3
Jun
Fri 5:00 pm
One submission per group, file
named
as
‘a
2

.pdf
‘.
Each group member will have a short presentation or viva.
Late
submission:
Assessment items submitted after the due time on the due date
will be penalize
d at a rate of
5% for each calendar day
the
assessment item is late. Assessment items submitted more than
7
calendar days
after the due date will be
awarded zero
marks.
Marks:
(a) Group submission:
a
total 100 marks and it is worth
7
% out
of the total
assessment
, plus (b) individual viva/presentation: 3%
out of the total assessment
.
Extensions:
An extension will only be
considered
with supporting
documentation from a health professional
and
if the
problem/illness occurred
within the week prior to the d
ue
date
. If an extension is granted the extension will then equal the
number of days specified on the doctor’s certificate, with a
maximum limit of
seven
(
7
)
calendar
days
.
Authorship:
This assignment is a
Group
assignment
and it shall be
completed by
the
student
s in each group
only. The final
submission must be identifiably the work of the individual
group members
. Breaches of this requirement will result in an
assignment not being accepted for assessment and may result in
the offending student or
students being required to present
before the Disciplinary Committee.
2814
ICT & 7003ICT

Assignment Pa
rt
2
Page
2
of
4
Designing a Database for
WareMart Logistics
Assignment Specification
WareMart now requires a partial implementation of the design made in Assignment Part 1. In order
to keep consistency between the assignments, database specification containing the ER diagram and
the corresponding schema are provided in this document. You s
hould create your database according
to this documentation. Irrespective of your Part 1 submission, make sure that your implementation is
consistent with this design, i.e., your table names, field names, and data types are according to the
specifications p
rovided in this document. The implementation phase includes writing SQL statements
to create a database and its tables, populating the tables with appropriate test data, and writing a
number of queries to create reports that can be used by the WareMart man
agement team. You need
to
insert at least five records
in each of the tables and
ensure that each of the query returns at least
one record
.
Implementation of the Database and Manipulation of the Data
You are required to perform the followings tasks:
1.
Task
1:
Create a text file named
Create_
Student_ID
.sql
(for example,
Create_
s2976800
.sql)
that will contain SQL statements to:
I.
Create a database named
WareMart
_
Student_ID
.
II.
Create all of the tables for the database according to the Database schema given with
this document (separately attached).
2.
Task 2:
Create a text file named
Insert_
Student_ID
.sql
that will contain SQL statements to:
I.
Insert
at least five records
in each of the tables. The test data inserted into the table
must ensure that each of the queries
, specified in Task 3, outputs
at least one record
.
3.
Task 3:
Create a text file named
Query_
Student_ID
.sql
that will contain all the queries to
display the following
I.
An alphabetically sorted list of all clients. Only client number and name are required.
II.
Li
st of names and complete address of all employees sorted by their salary.
III.
The
date on which the most recent stock request has been made. The date itself will
suffice.
IV.
List of all the client names and their residential addresses.
V.
A list of all clients that
have not placed a stock request yet. Displaying client number
will be sufficient.
VI.
A list containing the name (surname and first name) of any employee that has picked
any product(s) for a stock request.
VII.
A list containing the total quantity on hand for each
product (product no and
description) regardless of warehouses and location.
VIII.
A list showing each product requested on each client stock request. Show
client name, product number and quantity requested, sorted by client name and then
product number.
IX.
A list
of employees (surname and first name are sufficient) and their salary for all
employees whose salary is less than or equal to average salary.
X.
A list of employees as in Question IX, but show their salary with a 7.5% increase.
Additional queries for
7003I
CT
students only:
XI.
A list of all products (product number and description) and the quantity on hand for
that product for each location at which it is stored within each warehouse. Sort it by
product number and then place all locations (warehouse number and
location
number) for a given product together.
2814
ICT & 7003ICT

Assignment Pa
rt
2
Page
3
of
4
XII.
A list showing product number, the quantity requested, the quantity picked
and the difference between the two. For products stored in more than one location
within a warehouse the quantities should be added to
gether.
XIII.
A list of supervisors (staffid, surname and first name) and all of their
subordinates (staffid, surname and first name).
Note: Tasks 1 to
3
are
for all students, only 7003ICT students should
d
o
extra
Queries XI
to
XII
I
in
Task 3.
You are required
to adhere to the following output formatting conventions:
Any query requiring names of people should be printed as GivenName FamilyName (e.g.
John S
mith) in a column labelled NAME;
Any query requiring addresses should be printed as Street, Suburb State Pos
tcode (e.g. 123
Anzac Pde, Maroubra NSW 2038) in a column labelled ADDRESS
; and
All monetary values should be printed with a dollar symbol ($), two digits after the decimal
point.
What to submit
?
An electronic copy of your assignment should be subm
itted online and should
include a copy of
your report and the four files described in Task 1
to
Task
3
above. Zip all the files into a single file
named
‘a2
Student_ID
.zip’
before uploading.

Your report should include:
Use the supplied template for your A
ssignment
Report
.
A
n
a
pp
r
op
r
ia
t
e
t
i
t
le
p
a
g
e
t
h
at
i
n
cl
u
d
e
s
the signatures of all students in the
group and
a
n
a
c
k
n
o
wl
e
d
g
e
m
e
n
t
o
f
all
s
t
ud
e
n
t
s
and staff
y
o
u
h
a
v
e
s
p
o
k
e
n
t
o a
b
o
u
t
t
h
e
ass
i
gn
m
e
n
t.
A table of contents and page numbers.
A report of the results from running the SQL queries (Task 3) by using
c
opy/paste of their
output.
A bibliography containing a list of all resources used to complete the assignment. If no
resources, apart from the course materials, have been used please
indicate this.
Assessment Criteria
How clear and well organised your presentation is. On the
2nd
page of your report you
should include a list of acknowledgements of all people who have assisted you with this
assignment including fellow students.
Adherenc
e to our standards. How clear and well organised your presentation is. You should
write all the queries in consistent style and use indent format.
Data correctness and quality. Please use appropriate data for your examples (e.g.
,
do not use
inappropriate p
erson names)
Joining of data from multiple tables should be completed using a WHERE statement only.
JOINs are not to be used within any of the SQL statements. Use of any JOINs will result in
0 (zero) marks being allocated for each SQL statement that utili
zes them.
Please refer to the provided marking guide (below) to see the distribution of marks.
Assignment Resources
Find the following information in attached files:
The
logical
ER Diagram
, and
Relational Database Schema
.
2814
ICT & 7003ICT

Assignment Pa
rt
2
Page
4
of
4
Hint: you need to decide the order that tables need to be created; and the order of tables in
which data need to be inserted into.
Assessment Criteria and Marking Overview
For
2814
ICT
students only:
ITEM
Marks
1. Presentation
How clear and well
presented your submission is.
6
2. Creation of database and tables (Task 1)
Create the relevant tables: Database correctly named and
created,
includes all of the PKs and FKs in the database. No
evidence that
tables have been created using the GUI. They wo
rk
properly.
20
3. Insertion (Task 2)
Successfully inserts data into the tables. N
o
evidence exporting
from
GUI. They work properly.
30
4. Query (Task 3)
Use of appropriate query statements. They work as intended.
Each
query should output at least one row of valid data.
44
Total
100
For
7003ICT
students only:
ITEM
Marks
1. Presentation
How clear and well
presented your submission is.
5
2. Creation of database and tables (Task 1)
Create the relevant tables:
Database correctly named and
created,
includes all of the PKs and FKs in the database. No
evidence that
tables have been created using the GUI. They work
properly.
20
3. Insertion (Task 2)
Successfully inserts data into the tables. N
o
evidence exporting
f
rom
GUI. They work properly.
30
4. Query (Task 3)
Use of appropriate query statements. They work as intended.
They
work as intended.
Each query should output at least one row of valid
data.
45
Total
100
Notes:
Students will be awarded only half of the
allocated marks for Tasks 1 and 2, if there is a
clear sign of using the GUI in their submitted files.
O
nly concepts taught in this course are to use.
If concepts not covered in this course are
used, mark
s
will
be
dedu
c
t
ed
accordingly
.

QUALITY: 100% ORIGINAL PAPER – NO PLAGIARISM – CUSTOM PAPER