1. Overview

For this project, you will be assessed on:

(1.) Two written reports (Part A and Part B)

(2.) Implement a database system using Oracle in Part B

(3.) Video presentation in Part B

•    The individual assignment (Part A) is worth 15% of the marks of the course; it also includes 5% group reflection.

•    The team assignment is worth 25% (Part B) of the mark of the course. Part B is to be undertaken in groups of four (three is allowed to fit everyone - TBC)

students, all of which must be from the same tutorial class. Further information on Part B will be published after Week 05.

Assignment Part B is the focus of this document.

2. Key Dates

4. Aim of the Assignment

This case study (individual assignment + team assignment) addresses all the Learning Outcomes of the course. By completing each requirement of this assignment, you will   demonstrate your mastery of the principles and practice of Business Databases:

. CLO1: Apply conceptual and relational database modelling methods.

. CLO2: Design and implement database systems.

. CLO3: Apply Structured Query Language to access and update databases.

. CLO4: Carry out professional teamwork.

. CLO5: Discuss professional responsibilities and ethical issues regarding databases.

The aim of the assignment is to design and create a Databases based on a case study - Diplo Paints to solve their problems.

5. Case Study – Diplo Paints

Diplo Paints (DP) is an Australian business selling high-end quality paints across the country. A household name, the company is a popular retailer of paints for both businesses (such as painters and designers) and consumers. They sell over six hundred  colour  variations,  including  primary  and  secondary  colours,  and  tertiary colours made from a combination of pigments (mixed in exact proportions to the nearest millilitre (mL).

There are three ways that the company makes sales — through its physical retail stores,  via  its  website,  and  through  trade  centres  (essentially  marketplaces  for businesses to connect and trade)).

1.  Physical retail stores

These stores are established in all states of Australia and New Zealand. Purchases made in store by consumers can be made using cash, debit, or credit card. The company also offers their own  ‘buy  now,  pay  later’ option,  DipPay, which  allows consumers to payoff their purchase in four monthly payments (up to a maximum value of $1,000). Small quantities of paint can be purchased in store; larger purchases (over 5L) require placing an online order (similar to shopping online).

2.  Online website

The online sales component allows consumers (but not businesses) to purchase large amounts of paint delivered via two shipping options (regular and expedited). The same in-store purchase options are available through the website. Customers must first create an account to place an order, by recording personal details such as name, address, etc.

3.  Trade centres

Trade  centres  are  monthly  markets  for  businesses  to  buy  products  from  other businesses. Goods are usually purchased on credit, with the full amount payable up to 90 days after settlement. These sales are usually large deals, with a Diplo sales representative responsible for making the sale.

Since Diplo Paints offer a wide range of different paint products, it is impossible for the company to keep an appropriate amount on hand to sell. Instead, volumes of paint, delivered by various international suppliers, are stored in distribution centres and mixed onsite. At the same time, whenever a sale  is  made  an  order  is  created. Sometimes more than one order is created (e.g. when a large purchase is made, paints are often shipped in multiple orders). Each order is then shipped to the customer in either a ‘regular’, or ‘expedited’ status (depending on which option the customer opted for).

Occasionally, an order is mixed incorrectly. In these cases,a consumer interacts with the Complaints team to resolve the problem. A record of the complaint is held, and the customer is afterwards asked to rate their experience on a 5-star scale.

At times, the  Diplo  Marketing  team  will  conduct  mass-communication  marketing campaigns including all previous customers, informing them of special promotions or deals. For this purpose, the marketing team relies on a list of e-mails of previous customers. An employee can be part of both the Complaints and Marketing team. The special promotion or deal usually involves offering customers a promotional code which will give them a certain percentage off their purchase (provided the terms and conditions of the deal are met — these could be a minimum order quantity private shipping a minimum order value).

Although the  business  has  been  operating  successfully for years,  a  recent  audit revealed the finding that:

-    Costs could not be accurately allocated: e.g. the business was aware of $1.5m paid  to  the  trucking  company  responsible  for  delivering  paints  to  online customers, but there was no data relating to the breakdown of this amount.

Fixing  this  is  a  priority  for  the  company.  Furthermore,  there  were  long-standing problems that have caused issues in the past, including:

-    Poor data governance: the company often relied solely on paper records. There was also an incident in 2021 that resulted in an employee spilling paint on tax records for the previous year.

-    Strategy and marketing deficiencies: these teams were not sure which paints and/or colours were the best sellers, which products to advertise in different states, or target to different markets.

In this light, the company has approached you to advise them on the best way forward. They have heard of databases and are open to investing in a new information system to improve on current performance.

6. Tasks and Deliverables

6.1     Report B: Tasks

Note: students will work in a group of 3 (or 4).

1.  Based on the above scenario:

a.  List TWO [2] major problems that are facing Diplo. In your own words,

explain how each of these problems is affecting Diplo’s performance. [150 words in total]

b.  Explain how does a well-designed database for Diplo contribute to improving operational efficiency, inventory management, and customer satisfaction?

Please provide TWO [2] specific examples where such a database system    can effectively support Diplo’s sales and distribution processes and enhance its overall performance. Your answer should be specific to Diplo, any

answers copied from ChatGPT will be marked as Fail. [250 words in total]

2.  Diplo needs to manage customer data. There are ethical responsibilities and

considerations that should be taken into account by Diplo when collecting,

storing, and utilizing customer information in Diplo’s database. Diplo needs to

ensure the ethical use of customer data while still leveraging it to improve

business operations and provide personalized services. Discuss TWO [2]

potential challenges or risks and propose ethical strategies that Diplo must adopt to maintain trust and respect privacy in its database practices.

Provide specific examples. Your answer should be specific to Diplo, any answers copied from ChatGPT will be marked as Fail. [ 250 words maximum]

3.  Use the ER solution provided to make a relational model/relational database schema normalized to 3NF (Do NOT include the Relational model in your  report). Implement a relational database in Oracle based on your relational

database schema. Populate the tables of the database with at least ten

sample records in each table. Ensure that referential integrity between the tables is enforced. Use data from the above scenario or add data from your

own knowledge and understanding. Ensure that you have adequate data to run and test queries and showcase the database.

4.  Develop the below SQL queries for the database. The SQL query scripts must

be inserted as TEXT (NOT as a snapshot and NOT in an appendix). Printouts of the results of each query (e.g., screenshots) should also be in the report

following each query (not part of the word limit). SQL query scripts inserted as screenshots will NOT be marked.

a.  List all the paints (ID and Colour) that Diplo Paints sell. If the colour is

primary (red, yellow or blue): list 'Primary' in a field labelled "Colour Type" , Otherwise, list 'Tertiary'.

b.  List all the paints that make up other paints, as well as the paint(s) that they make up.

c.  Diplo would like to track the same KPIs as above, however management

would now like a monthly breakdown to see which months in the year are

their best performers. -- Do not differentiate between years. List the months with the highest average purchase amounts first.

d.  List all consumers (name and email address) who have shopped at a retail store in the last year.

e.  For each industry, list the total number of businesses, and then the total number of businesses that have not made any purchases, then the % of   businesses that have not made any purchases.

f.   Which shipping companies have shipped the most amount of paint to   customers? List the total amount for each company Measure this by total (i.e. quantity) of paint purchased.

6.2    Pitch Video

Final presentation: This is the project’s final presentation. Your team will be

presenting your proposed solution to Diplo and businesses similar to Diplo. (i.e. you need to convince your audience to purchase your solution).

Video Pitch:

-    Each group’s video  pitch should  be a maximum of 4 minutes. Not all members need to have a role in the video (you can if you want), but at least 2 members.

-    Audience: the business managers (non-technical audience, not familiar with Databases).

-    Purpose: to convince Diplo to implement your DB design.

Requirements:

-    The slides AND the presenter’s face should be shown in the video. Slides are optional. (you could use Zoom or Teams to record your video)

-    All Team Leaders: you must submit your video on Moodle before the due date.

-    The  file name should be as follows: GroupID-VideoPresentation.mp4 (you should only use .mp4 video format).

-    zIDs and names of all members should be included on the first slide of the presentation and video.

-    Include the  name of the person presenting on the corresponding slide.

-    The video file could not be more than 200MB in size.

A possible structure for the video presentation could be:

-     Clearly and briefly introduce your team, the presentation’s structure and key points.

-     Clearly and concisely identify how your Database solution makes things different from what the business does today.

-     Clearly and concisely identify the benefits of your solution to the business.

6.3    Format and Deliverables

For the written report of Part B, you need to consider:

•    UNSW Coversheet. Submit your assignment with a signed coversheet (typed

signatures or initials are allowed). Failure to include the UNSW coversheet with  signatures/initials will lead to 5% penalty, and no marks will be released until the coversheet is received.

•    Length. Please see the limit for each question.

•    Table of Contents. Should not exceed one page, restricted to two levels of headline. The headline and report structure should follow the above deliverables.

•    Format. The style/format of the report can be as you find it appropriate and useful. You should use headings, sub-headings, bullet points, diagrams, and tables as

appropriate. The file format of the report is only Microsoft Word.

•    References. References and citations (if any) should follow either the UNSW (Harvard) or the APA 7th  citation style standard.

•    Turnitin Title and File Naming Convention: Your Turnitin Title and your submitted file should be GroupID-Assignment-B.PDF and GroupID-Assignment-B.ZIP.

Failure to use the correct file naming will lead to a 5% penalty.

•    Text inserted as pictures will NOT be marked. For example, a table cannot be

screenshotted as a picture. Only figures (e.g. ER model, Relational model) can be inserted as pictures.

•    The clarity and readability of your report are very important.

7. Assessment Criteria, Grading and Feedback

In assessing your work, two questions will be considered:

1.    Have you fulfilled ALL of the requirements of the assessment task?

2.    How well have you demonstrated your achievement of the learning outcomes associated with the task?

Feedback on this individual assessment task will be provided:

. Summative, by the teaching team within 10 working days.

8. Diaries

An important part of a project is to record and to evaluate the teamwork. The purpose of the diary is to reflect and learn from the project (in addition to achieving the project’s aims in a narrow sense). The project requires that each team member to keep a personal diary, and the group to keep a group diary (diaries might be asked for in cases of disputes) and that the group includes a critical reflection in their report.

8.1     Individual Diary

You, as an individual member of a group, are required to keep a diary of your work activities. The individual dairy should record your work on the assignment and should note details such as what you did and when you did it. You may also record meeting notes in the diary. The individual diary is NOT to be submitted with the assignment and will NOT be marked.  The individual diary maybe consulted by the LiC in case of group disputes. Failure to produce an individual diary on request by the LiC will betaken as evidence for a lack of a structured effort in the project.

8.2    Group Diary

Your group’s dairy must record all the activities that take place in completing the assignment. The group diary is NOT to be submitted with the assignment and will NOT be marked. The group diary may be consulted by the LiC in case of group disputes.

The format of your group’s diary is up to you, but it must record the following details for each group activity:

•  A group needs to agree on who is responsible for keeping the group diary.

•  Record what the activity (meetings, work) entailed.

•  Record location, time, date and duration of a group activity.

•  Record who was present at the activity.

•  For “next actions”: specify who is doing what by when (Action plan)

•  Signatures of all members, or other suitable forms of demonstrating that all members have seen and approved meeting minutes and other records in the group diary.

The  Team  Leader  is  responsible  for  keeping  the  group  diary  unless  other arrangements are made within the group.

9. Peer-Review

9.1    Within-Group Peer Review

In general, equal contribution of group members is expected. Significantly unequal contributions should be discussed in due time within the group. In the case that a dispute  emerges,  the  group  needs  to  discuss  with  the  LiC.  At  the  end  of the assignment period, you need to perform a within-group peer review to evaluate the contribution of all group members (including yourself) to the group project. The purpose of the within-group peer review is to critically reflect on the group work, to prevent “free-riding” and to redistribute marks between group members in cases where free-riding or unequal contributions occur. Claims of unequal contributions, especially if contradicting the evaluation of others, will need to be substantiated with evidence  (e.g.,  group  diary,  personal  diary,  meeting  notes,   emails,  Facebook messages, etc.). The LiC will make a final judgement in the case that a dispute emerges,  calling  in  the  group,  considering  earlier  discussions  and  submitted evidence.

10.   General Rules

10.1  Proper Academic Conduct

All assignments need to follow UNSW’s guidelines regarding proper academic

conduct. The submission of materials that are non-original or have been submitted

elsewhere will be considered plagiarism. Plagiarism is unacceptable. All instances of plagiarism or other academic misconduct will be pursued. Plagiarism may lead to

you failing this course and may have negative consequences for your studies at UNSW. The general UNSW guideline on academic conduct is available online.

10.2  Assignment Submission

Assignments are to be submitted via Moodle on, or better before, the due date. Late

submission of assignments is not desirable, disrupts the course timelines and is a sign of poor time management, and will lead to reduced marks. The late submission of

assignments carries a penalty of 5% of the awarded marks for that assignment per day of lateness (including weekends and holidays). For example, a 70 marking would be

reduced by 3.5 marks per day of lateness. An assessment will not be accepted after 5 days (120 hours) of the original deadline unless special consideration has been

approved. An assignment is considered late if the requested format, such as hard copy or electronic copy, has not been submitted on time or where the ‘wrong’ assignment

has been submitted.

Students must plan ahead and submit on time. Providing an extension (via Special Consideration) is unlikely to be granted for this assignment task.