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 updateDateRequired 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.
版权所有:编程辅导网 2021 All Rights Reserved 联系方式:QQ:99515681 微信:codinghelp 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。