Assessment Overview

Weighting 

30% of unit total 

Due Date 

Week 12, Wednesday, 30 April, by 23:55 AEST/AEDT [Ensure this matches the Unit  Study Guide]

Assessment Type

Group assessment

Word Count / Length 

2500 words +/- 10%

Unit Learning Outcomes 

In this assessment, you will be tested on whether you have successfully met the  following Unit Learning Outcomes (ULOs):  

• ULO-1: Understand and evaluate the role of database management  systems in information technology applications within organisations. 

• ULO-2: Identify organisational requirements for data and develop data  models using conceptual data modelling techniques. 

• ULO-3: Explain and apply database design techniques for relational  database systems. 

• ULO-4: Derive a physical design for a database from its logical design.

Submission Type and  Required Format

The type of assessment you will be completing is an Applied Project. 

It should include: 

• A data warehouse physical design 

• Creation of the database in XAMPP MySQL 

• The database population 

• Writing SQL queries according to the assignment requirements 

Assessment Details

Assessment Purpose 

The purpose of this assessment is to provide an introduction into a data  warehouse start schema design. Students will learn and practice how the star  schema is used to organize data for efficient querying and analysis, particularly in  business intelligence applications.

Assessment Instructions 

The Global Trading company sells various building and gardening tools. It has sales  offices and warehouses all over Australia. The company developed a data warehouse  to store information about sales, customers and products. These data are stored in  the MySQL files provided for you. 

The database consists of five dimension tables and one fact table:

• ShopDim – with the sales office address, state, and email 

• ProductDim – product information 

• WarehouseDim – warehouse locations 

• CustomerDim – information about customers 

• DateDim – sales dates 

• SaleFacts – information about sales 

All data (customers, sales, dates, warehouses, and products) are randomly  generated. Table and field names are self-explanatory. 

Create the assignment MS Word document including the cover sheet. Start  XAMPP database development environment and create the Applied Project 2  database. 

Download the “AP2_script_create_tables.doc” MS Word file. The file contains  scripts to create dimension tables for the Applied Project 2 database. There  are no primary key declarations.  

Remember that you must copy all the queries in the assignment document,  and the images of the XAMPP MySQL database development environment  containing the results of running these queries.  

Your assignment consists of the following tasks: 

1. Update the SQL queries of creating the dimension tables: • Declare primary keys 

• Declare the primary keys as AUTO INCREMENT 

• Change the “saledate” field TEXT datatype in the “datedim” table  to the DATE datatype 

• All fields (columns) in all tables must be declared as NOT NULL Create the tables in XAMPP. 

2. Write SQL query to create the “SaleFacts” table declaring the primary and  foreign keys. The table has the following columns:

Column name 

Datatype 

Primary/Foreign key

ID 

Integer 

Primary key, AUTO  

INCREMENT

DateKey 

Integer 

Foreign key, NOT NULL

ShopKey 

Integer 

Foreign key, NOT NULL

CustomerKey 

Integer 

Foreign key, NOT NULL

ProductKey 

Integer 

Foreign key, NOT NULL

WarehouseKey 

Integer 

Foreign key, NOT NULL

SalesQuantity 

Integer 

NOT NULL

discount 

Integer 

NOT NULL

The “discount” field values can only be 0 or 1. If the value is 1 then 10% discount is  applied to the sale amount. 

Run the SQL query in XAMPP to create the table. 

Download the “AP2_populate_tables.doc” file and run the queries to populate the tables.  

3. Draw the Conceptual Model diagram in app.diagrams.net, export the  diagram as an image and insert it in the assignment document.

4. Create the Relational Model diagram and insert the image in the  assignment document. 

5. Create a new customer in the ‘CustomerDim’ table. The data should be  your name, your APIC campus address (Sydney, Melbourne, or Brisbane),  state where you live, and your APIC email address. 

6. Create a new sale inserting a record in the ‘SaleFacts’ table. The record  must contain the reference to the new customer (i.e., your data). 7. Write a query to display customer names and emails. 

8. Display customer addresses from New South Wales. 

9. Display the total amount ($) of sales in the first quoter of 2016. The  discount amount should be subtracted for each sale. 

10. Display the total amount of sales for each customer separately. The result  must have two columns: a customer name and the total for this customer. 11. Display the total amount of sales for each customer and product  separately. The result must contain 3 columns: a customer name, a  product name and the total. 

12. Display a number of sales (not $!) for each warehouse. The result must  contain two columns: a warehouse division and the number of sales for  this warehouse.  

13. For each sale, display the date, the customer’s name, the shop address, the shop state, the sale quantity and the discount number (0 or 1). 14. Calculate a total amount of sales in the last quarter of 2017.

15. Calculate a total amount of sales from 1st to 10th January for each year  separately. 

Submission requirements. 

The assignment document must have the group Cover Sheet. 

The diagrams must be created using app.diagrams.net drawing tool.  The database must be created in XAMPP. 

For each query question, the XAMPP image containing the SQL query and the result  must be inserted in the assignment document. In addition, the SQL query should be  copied and pasted as a text in the document. 

What to submit. 

1. The MS Word assignment document. 

2. The database script exported from XAMPP.

Artificial Intelligence (AI)  Use

In this assessment, the use of generative artificial intelligence (AI) tools is NOT  PERMITTED. In this assessment, you must NOT use generative artificial  intelligence (AI) to generate any materials or content in relation to the assessment  task.  

This means you are NOT allowed to use any AI tools such as chatbots, text  generators, paraphrasers, summariser, or solvers to complete your assessment.  You are expected to produce your own work and cite any sources properly.  

Engaging with AI tools for this assessment will be treated as a breach of academic  integrity. Refer to the Academic Integrity Policy and Procedure for more details.

Other Important Information

Assessment Resources 

It is strongly recommended that you use the following to find academic peer reviewed sources of information. 

• ECA Library catalogue and databases  

• Unit Readings (Course Reserves) Subject Guide: Information Technology  

For in-text citation and referencing, follow the ECA HE Student Guide to APA 7th Referencing.

Submission  

Requirements

On completion of your assessment:  

• Follow the Assessment Presentation Guidelines 

• Submit your document as a PDF or MS Word document via the assessment  submission link in Canvas.  

• Include a completed Assessment Cover Sheet, including the AI Declaration,  and attach it to the assessment. 

• Save drafts of your work.

[Include more information or remove not applicable information as required for  your assessment]

Assessment Support 

For academic support or feedback on a draft of your assessment, please email  academic.support@eca.edu.au 

For assistance with finding resources, such as books and journal articles, please  email library@eca.edu.au  

For information and guides on tackling assessments and developing your academic  skills, please visit in the ECA Library and Learning Support website:   

For queries about this specific assessment task, please contact the Unit  Coordinator.

Question No Max mark

Q 1 

5

Q 2 

6

Q 3 

6

Q 4 

6

Q 5 

3

Q 6 

3

Q 7 

2

Q 8 

2

Q 9 

6

Q 10 

6

Q 11 

8

Q 12 

8

Q 13 

5

Q 14 

7

Q 15 

7

Cover sheet provided 

App.diagrams.net images provided 

XAMPP images, containing SQL queries and results inserted SQL queries copied