BIS1002 Data and Information Management Trimester 1, 2025 Assessment 3: Applied Project 2
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 [email protected] For assistance with finding resources, such as books and journal articles, please email [email protected] 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. |
Assessment 3 Rubric
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 | 20 |
Leave A Comment