代写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.



热门主题

课程名

mktg2509 csci 2600 38170 lng302 csse3010 phas3226 77938 arch1162 engn4536/engn6536 acx5903 comp151101 phl245 cse12 comp9312 stat3016/6016 phas0038 comp2140 6qqmb312 xjco3011 rest0005 ematm0051 5qqmn219 lubs5062m eee8155 cege0100 eap033 artd1109 mat246 etc3430 ecmm462 mis102 inft6800 ddes9903 comp6521 comp9517 comp3331/9331 comp4337 comp6008 comp9414 bu.231.790.81 man00150m csb352h math1041 eengm4100 isys1002 08 6057cem mktg3504 mthm036 mtrx1701 mth3241 eeee3086 cmp-7038b cmp-7000a ints4010 econ2151 infs5710 fins5516 fin3309 fins5510 gsoe9340 math2007 math2036 soee5010 mark3088 infs3605 elec9714 comp2271 ma214 comp2211 infs3604 600426 sit254 acct3091 bbt405 msin0116 com107/com113 mark5826 sit120 comp9021 eco2101 eeen40700 cs253 ece3114 ecmm447 chns3000 math377 itd102 comp9444 comp(2041|9044) econ0060 econ7230 mgt001371 ecs-323 cs6250 mgdi60012 mdia2012 comm221001 comm5000 ma1008 engl642 econ241 com333 math367 mis201 nbs-7041x meek16104 econ2003 comm1190 mbas902 comp-1027 dpst1091 comp7315 eppd1033 m06 ee3025 msci231 bb113/bbs1063 fc709 comp3425 comp9417 econ42915 cb9101 math1102e chme0017 fc307 mkt60104 5522usst litr1-uc6201.200 ee1102 cosc2803 math39512 omp9727 int2067/int5051 bsb151 mgt253 fc021 babs2202 mis2002s phya21 18-213 cege0012 mdia1002 math38032 mech5125 07 cisc102 mgx3110 cs240 11175 fin3020s eco3420 ictten622 comp9727 cpt111 de114102d mgm320h5s bafi1019 math21112 efim20036 mn-3503 fins5568 110.807 bcpm000028 info6030 bma0092 bcpm0054 math20212 ce335 cs365 cenv6141 ftec5580 math2010 ec3450 comm1170 ecmt1010 csci-ua.0480-003 econ12-200 ib3960 ectb60h3f cs247—assignment tk3163 ics3u ib3j80 comp20008 comp9334 eppd1063 acct2343 cct109 isys1055/3412 math350-real math2014 eec180 stat141b econ2101 msinm014/msing014/msing014b fit2004 comp643 bu1002 cm2030
联系我们
EMail: 99515681@qq.com
QQ: 99515681
留学生作业帮-留学生的知心伴侣!
工作时间:08:00-21:00
python代写
微信客服:codinghelp
站长地图