Posted: September 10th, 2021

DATABASE MANAGEMENT School of Information & Communication Technology, Designing a Database for BigM

1804ICT – DATA MANAGEMENT
7003ICT – DATABASE MANAGEMENT
School of Information &
Communication Technology Trimester
2, 2018: 2024 – Write My Essay For Me | Essay Writing Service For Your Papers Online
Assignment Part 2:
Designing a Database for BigM
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:
plan, schedule and execute project tasks with a view to improving your personal productivity;
gain awareness of the typical challenges 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
learn how to use Data Manipulation statements to query a database, and insert and update
data in the tables
Due date: Week 11, 25 September Friday, 5:00 PM
Submit the individual work file, named ‘a2-.zip ‘, by the
above due date.
Late Any submission after the due date will receive a deduction of submission: 10% per day.
Marks: This assignment has a total 100 marks and it is worth 15% 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 due 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 five (5) working days.
Authorship: This assignment is an individual assignment and it shall be
completed by the individual student only. The final submission
must be identifiably the work of the individual. 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.
1804ICT & 7003ICT – Assignment Part 2 Page 1
Designing a Database for BigM
Assignment Specification
BigM 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 should
create your database according to this documentation. 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 provided 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 management 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_.sql (for
example, Create_s3087654.sql) that will contain SQL statements to:
I. Create a database named BigM_.
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_.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_.sql that will contain all
the queries to display the following
I. List of names and complete postal address of all employees sorted by their
salary.
II. The date on which the most recent customer order has been made. The
customer name and date of the order will be sufficient.
III. List of all the store names and their addresses, sorted in dictionary order
of the store name.
IV. A list of all customers that have not placed an order yet.
Displaying customer number and name will be sufficient.
V. A list containing the name of employees, which work as accountant.
VI. A list containing the total quantity on hand for each product (product
number and description) regardless of stores.
VII. A list showing each product sold (picked) on or before May 20, 2018: 2024 – Write My Essay For Me | Essay Writing Service For Your Papers Online. Show
product number, name and quantity sold, sorted by product number and
then quantity sold.
VIII. A list of products (show product number, description and price) whose price
is less than or equal to the average product price.
IX. Increase each employee’s salary by 7.5% and show the updated salary of all
employees (name and salary).
X. Show the pay information (employee name, hours paid, amount paid) of
all employees in the most recent pay date.
1804ICT & 7003ICT – Assignment Part 2 Page 2 of 4
Additional queries for 7003ICT students only:
XI. Make a list of all products of brand “Armani” and their price in ascending
order of price. Show the product description and price.
XII. A list of supervisors (employee id, name) and all of their subordinates
(employee id, name).
4. Task 4: Create a text file named Update_.sql that will perform
the followings. Insert additional data in the tables appropriately if needed.
A customer named Daniel Ortega, from 11 Fuller Road, Marsden
QLD 4132 and having a mobile number 0431xxx668, orders two shirts
of size “XL” of brand “Prada” on 6 Sep 2018: 2024 – Write My Essay For Me | Essay Writing Service For Your Papers Online. The full order is ready
on 8 Sep 2018: 2024 – Write My Essay For Me | Essay Writing Service For Your Papers Online for pickup, but the customer picks up on 10 Sep 2018: 2024 – Write My Essay For Me | Essay Writing Service For Your Papers Online.
You should insert data into appropriate tables for the above order and make
sure you update the data in the relevant tables after the order is picked up.
Note: Tasks 1 to 4 for all students, only 7003ICT students should do Queries XI and XII
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 Smith) in a column labelled NAME;
Any query requiring addresses should be printed as Street, Suburb State Postcode
(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 submitted online and should include a
copy of your report and the four files described in Task 1 to Task 4 above. Zip all the
files into a single file named ‘a2‐.zip’ before uploading.
Your report should include:
Use the supplied template for your Assignment Report.
An appropriate title page that includes an acknowledgement of all students
you have spoken to about the assignment.
A table of contents and page numbers.
A report of the results from running the SQL queries (Task 3) by using
copy/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 front page of your
report you should include a list of acknowledgements of all people who have
assisted you with this assignment including fellow students.
Adherence to our standards. How clear and well organised your presentation
is. You should write all the queries in consistent style and use indent format.
1804ICT & 7003ICT – Assignment Part 2 Page 3 of 4
Data correctness and quality. Please use appropriate data for your examples
(e.g., do not use inappropriate person names)
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.
Assessment Criteria and Marking Overview
For 1804ICT students only:
ITEM Marks
1. Presentation 8
How clear and well‐presented your submission is.
2. Creation of database and tables (Task 1) 20
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.
3. Insertion (Task 2) 30
Successfully inserts data into the tables. NO evidence exporting from
GUI. They work properly.
4. Query (Task 3) 32
Use of appropriate query statements. They work as intended. Each
query should output at least one row of valid data.
5. Update (Task 4) 10
Use of appropriate data manipulation statements to perform the
required actions. They work as intended.
Total 100
For 7003ICT students only:
ITEM Marks
1. Presentation 5
How clear and well‐presented your submission is.
2. Creation of database and tables (Task 1) 20
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.
3. Insertion (Task 2) 30
Successfully inserts data into the tables. NO evidence exporting from
GUI. They work properly.
4. Query (Task 3) 35
Use of appropriate query statements. They work as intended. They
work as intended. Each query should output at least one row of valid
data.
5. Update (Task 4) 10
Use of appropriate data manipulation statements to perform the
required actions. They work as intended.
Total 100
1804ICT & 7003ICT – Assignment Part 2 Page 4 of 4

Order | Check Discount