代写BISM7206 Assessment 2: Team Project代做SQL语言

BISM7206 Assessment 2: Team Project

Due: 18 October 2024 06:00 PM

Weight: 40% (35% submission + 5% Peer Assessment Mark) Group-based

Overview

In  a  project team  consisting  of four  (4)  to five  (5)  members,  develop  an  information  system application for a client. The purpose of this assignment is to test your ability to:

-  engage  in  critical  thinking  and analytical skills by justifying the  implementation of a  new system.

-  develop conceptual models such as ER diagram to capture important aspects of a system which need to be stored in a database.

-  use MySQL to implement the model and retrieve specific subsets of information from the designed database.

Submission

Final Submission	Week 12- 18 October 2024 5.00 p.m.	35%
Peer Evaluation	25 October 2024 5.00 p.m.	5%

All submissions must be made through Blackboard using Turnitin. One submission per team is sufficient. You are required to submit the followings: MySQL database (this is a copy the backup with the data), MySQL files for queries,. All submissions must have a front coversheet that clearly identifies each student that contributed to the final submission. Please use enrolled names only. Please include your group number. More details about submission requirements can be found under final submission requirements.

Peer Assessment: due one week after the final submission due date. A Buddycheck link will be provided after the due date of the final submission.  Refer to Peer Assessment sheet for more information.

Project Description:

Company: Silver Harmonic Sounds Headquarters: Brisbane

Number of Employees: 500 (February 2016) Revenue: $500+million (FY2015)

Industry: Retail

Ownership: Privately held Founded: 1990

Silver Harmonic Sounds is an entertainment retailer specialising in music, films, and audio books. It has 20 online stores operating in the United States, Germany, France, the United Kingdom, Spain, Australia, Italy, South Africa, China and India. It has 100 brick and mortar (physical) stores operating in those countries as well. Customers can buy individual products such as a song, an audio book, or a film, or they can subscribe to a package, which enables them to download a certain quantity of products over a set period. For example, with the Primer package, you can download 100 songs, 50 books, and 50 films a month for $50. Customers can also listen or watch a song, a book, or a film once for one-tenth (1⁄10) of the cost of purchasing it. So , if a film is $5, to watch it once it’s only 50 cents. Customers use online streaming for this, so a good Internet connection is required.

Silver Harmonic Sounds has four main delivery channels: Internet, mobile phone, cable TV, and post. There are several payment methods for customer subscriptions, such as annual, in advance, and monthly direct debit. The company purchases products in bulk, such as any 10,000 songs from a record company, of any title, for a set cost. For online streaming, the company pays a central provider (called Geo Broadcasting Ltd.) based on usage (monthly invoice).

Silver Harmonic Sounds has been very successful in recent years and is a highly profitable business. As the business is growing, the managers are facing a number of challenges in three business areas: Purchasing, and Customer Relationship Management (CRM) for Sales. First, they have difficulty aggregating  worldwide  sales  and  profit  at  any  time.  Second,  they  need  to  evaluate  supplier performance to improve their inventory management. Lastly, they are considering adding a loyalty program to their CRM system. However, in order to make these decisions they require a better understanding of their products, sales and customers.

To address these challenges, Silver Harmonic Sounds started developing a data warehouse at the in March 2024.Unfortunatley, the inhouse analytics team have discovered that the CRM which includes sales has very dirty data due to the design of the database. The Enterprise Resource Planning (ERP) system, that supports the purchasing, inventory and finance areas, appears to well designed. Its issues of over and under stocking are due to the Sales system not having the correct data. The organisation could purchase a new off the shelf solution. However, the inhouse DBA group believe that the solution to the issue is to redesign the part of the database with the issues, clean the data and reload. They would then hook the front end of the system to the new database. They do not have the resources to do all the required work.

Accepting the advice of the inhouse staff, the CEO, Taylor Fleetsound, has decided to hire a group of consultants (your group) to propose a solution to Silver Harmonic Sounds to modify the current CRM Sales system to address the major issues and minimise other issues, clean the data and demonstrate that the new system works by providing example queries. However, the CEO would also require that the proposal include the modifications to include a simple customer loyalty program. The program is to be ethical and ensure that customer privacy is not compromised.

Major issues identified include update anomalies, missing data due to deletions, and the absence of data archiving before updates, which results in incorrect historical data. For example, a customer purchases three items using an accountId 12344. Two months later the customer no longer may use this accountId as they are no longer part of the family, and they have married and have a new accountId and address in a new city. The records are updated but now the purchases are connected to the incorrect accountId and city.

The current tables that need to be redesigned (there are tables that contain some of the needed codes that are not to be redesigned and they can be found with sample data in the attached Excel files):

Note:

-         Your team is not responsible for a full CRM system. Just the parts identified and the addition of a simple customer loyalty program.

-         The organisation uses lowerCamelCase as the naming standard for attributes and lower-case non-plural only for table names. Marks will be deducted if there are more than a couple of errors with this and/or the names have been changed with insufficient reason.

customer custId (unique for organisation) acctId (unique for organisation more than one customer can belong to an account as maybe family or business and a customer can belong to more than one account) custType (customers can be multiple types) name gender emailAddress dateOfBirth ageGroup addresslLine1 addressLine2 city state postalCode (be careful of different formats) country phoneNumber1 phonNumber1Type phoneNumber2 phonNumber2Type phoneNumber3 phonNumber3Type occupation householdIncome dateRegistered customerStatus permission updateDate preferredChannel1 preferredChannel2 interest1 custorder orderId (unique for store) orderLineId (unique for order) storeId (unique for region) region orderDate productCode customerId currencyId (one currency per order) orderQty unitPricePaid saleAdjustmentCode1 saleAdjustmentCode2 deliveryType deliveryName deliveryAddressLine1 deliveryAddressLine2 city state postalcode country deliveryInstructions status (note: no partial order line completions only completed, on route; back ordered; confirmed order; cancelled; returned; returned and refunded) updateDate product (this is not part of the CRM sales. However, for the purposes of the assessment it is required to create the table and the ERD needs to show the relationship(s). There is archival data kept when there is a change in price and/or cost, and status: that is, not just correcting errors in other attributes. There are two different dates to achieve this. updateDate all updates. This is how the Purchasing System deals with changes; your group may propose another method for other tables.) productId effectiveDate description name title artistCode productType productTypeDescription productCategory status format broadcastConsent (may be used by business for internal or external or not available e.g. external can use on websites) unitPrice (in Australian Dollars) unitCost (in Australian Dollars) updateDate account accountId accountName (could be family name or business name) custId (authorising contact) addresslLine1 addressLine2 city state postalCode country phoneNumber1 phonNumber1Type updateDate store storeId (unique for region) region storeName storeType addressLine1 addressLine2 city state postalCode country phoneNumber website divison marketSectorName updateDate subscription subscriptionId (unique for a store) storeId region startDate endDate customerId accountId currencyCode packageId status subscriptionQty unitPricePaid renewalType package packageId (unique for organisation) name description packageType packagePrice updateDate channel channelId (unique for organisation) name description startDate endDate status updateDate

Required data for the data warehouse

Most of the data is to be made available to the data warehouse. Exceptions are noted below.

Customer

A formula  has  been  developed  to deidentify  customers  in  the data warehouse.  They  are given a  data warehouse identifier and a group number based on if they belong to a family or business group. All identifying information is not passed to the data warehouse such as names, addresses (apart from postcodes), telephone numbers etc, email addresses, full date of birth (ageGroup is passed, however has been found to be incorrect or missing). Further, the analytics team are not happy with the limitations on interests and other similar data collected.

Orders

All data is passed except for delivery address which only has the postcode passed to the data warehouse.

Assignment Specification and Requirements

Your database design consultancy team has been asked to design and develop a well-structured database for Silver Harmonic Sounds based on the previous background narrative. You are required to write a professional business report that includes or addresses the following  (please read in conjunction with the rubric):

1. Database system overview (max 1000 words): The first part of your report will detail and justify the design of a database for the case study provided. It needs to include an explanation of how the redesigned database will address the business problems and minimise dirty data without compromising security and privacy. The design areas to be covered should include but are not limited  to:  normalisation,  OLTP  relational  database  design  guidelines;  input  controls  and security.

2. Entity Relational (ER) diagram: You will need to create an  Entity-Relational (ER) diagram based on the case study. If specific aspects of the case study are unclear, you may note your assumptions under the ER diagram. However, ensure that these assumptions do not conflict with or violate any details provided in the case study. ER diagrams must be computer generated using MySQL Workbench and should follow UML class diagram notation.

3. Data dictionary: provide a data dictionary that include details for each attribute such as the attribute name, data type, domain, null/not-null, uniqueness, primary key, foreign key(s) and definition and the purpose of each attribute. The data dictionary must be organised by table.

4. Cleanse and import data: It is essential to address any errors present in the dataset. You are required to cleanse and filter this data so that data imported into the new database is accurate, complete, and consistent. Use the cleansed data to populate part of the new database , and add any required additional data. This section of the report must present a plan. It must show the parts of the plan completed and give recommendations to minimise dirty data in the future. Explain how each table in the database was imported and checked has part of quality control and if needed cleansed. Include the SQL scripts that were used to cleanse the data as part of the explanation with before and after screenshots of examples of the data for each table, this includes any data that needed to be manually cleared and why.

5. SQL queries: You are required to generate five (5) SQL statements that retrieve data from the database system to address key insights required by management as part of the case study provided. Briefly explain how the results of the queries can help improve management’s planning and/or  decision-making  processes.  These  questions  must  be  insightful  rather  than  daily transactions such as finding the email address of a particular customer. At least two of these queries must demonstrate how the queries used by the data warehouse can be replicated but use more joins as the OLTP database is now normalised.

Please note that: Format of this section is the code copied and pasted in the report so that Turnitin can read the code , then a screenshot from MySQL of the query and the output, followed by the explanation.

Final Submission Requirements

1.  Include a title page as the first page with your team's name

2.  Include every student's name and number on the title page

3.  The second page is to have a table of contents with page numbers

4.  Save your assignment in the required format: your tutorial number and group number, then the title of the piece of assessment. Please use only an underscore for spacing, e.g.

T02_G03_Feedback_1.

5.  Include the course code, course title, assignment title and your team's name on every page of your assignment using the footer.

6.  Insert page numbers in the footer

7.  Save your whole assignment as a Word document in the correct order as outlined, above, before uploading it on Blackboard to Turnitin. Only Turnitin submissions will be marked.

8.  Upload your final version of your MySQL database with accompanying SQL query files and data to the assignment area.

9.  Submit an individual confidential peer evaluation using Buddycheck (submitted via separate individual Blackboard link to preserve confidentiality. The link will open after the due date of   Assessment 2 and close one week later.

Formatting and Resources

Use a word processor to prepare your submissions and include all diagrams, which you can prepare  using  MySQL  Workbench  (UML  class  diagram  notation).  These  diagrams  are to  be submitted  in  your  single  report  file  for  each  submission.  Follow  the  below  instructions  for submission formatting:

1- Written components: Word Times New Roman font, 12-point font, single-spaced (tables may use 10pt font)

2- Database tool: MySQL

4- Referencing Style. APA please see Library guides

Marking

Your final submission will be graded according to your  tutor’s  comments and  marking  rubric available on Blackboard. In the event of multiple submissions, the last version of each deliverable will be the one graded. Refer to Peer Assessment sheet for more information about criteria for peer evaluation.

Use of AI Tools

Given the above purpose, it should be clear that the use of AI tools will be limited to supporting and not generating the assessment outputs. For example, AI tools are useful for starting the process of generating test data. This data will need further changes by your team for it to be suitable for testing your database. Therefore, the following statement from UQ applies:

This task has been designed to be challenging, authentic and complex. Whilst students may use AI technologies, successful completion of assessment in this course will require students to critically engage in specific contexts and tasks for which artificial intelligence will provide only limited support and guidance. To pass this assessment, students will be required to demonstrate detailed comprehension of their written submission independent of AI tools.