Final_Project_cis467_to_post_FallA_2023

docx

School

University of Rochester *

*We aren’t endorsed by this school

Course

467

Subject

Computer Science

Date

Dec 6, 2023

Type

docx

Pages

Uploaded by CountBoulder12465

CIS 467 final group project (due by Thursday, October 26 at 11:59 PM). This is a group project (total 300 points). The groups have been created on Blackboard. Please make only one submission per group and put all your team members’ full names into this Word document. Please also submit a Tableau Workbook file .twb into the Final Team Project folder on Blackboard together with the Excel file of your Data Warehouse which you uploaded to Tableau and used for visualizations. The script file final_project_database_cis467.sql creates a database which contains the 11 tables (the database schema is below), with transactional data related to some company operations. Please check early that you can create the final project database on your machine. Please review Panopto video for week 7 for a description of this dataset. Very Important! All three parts of the final project should be on One topic/subject of the data warehouse. For example, if you decide to track customers as your topic/subject, part 1 (Data Warehouse), part 2 (Queries) and part 3 (visualizations) should only be related to customers and should NOT include any other topics. If you use Chat GPT, please use the “Share” button (looks like ‘upward arrow’) in the right corner of ChatGPT chat, and ‘copy link’ and share the link to that chat in this Word document and briefly explain how you used it for your Final Project (for each question if you used it). No points off will be taken for using ChatGPT (it is allowed to use it for Final Project) but you are required to share the link to a chat if you used it. More information on how to share a chat here: https://help.openai.com/en/articles/7925741- chatgpt-shared-links-faq

Please put all of your work into this single Word doc and also submit a Tableau Workbook file .twb and the Excel file of your Data Warehouse that you used for Tableau visualizations . Please see instructions for Tableau below in the question 3. 1. (121 points) Design and create a data warehouse for the provided database. The decisions about which fields to include and how to aggregate the data are left to you. You do not need to include every single data point from the 11 tables given. Use your judgement as to what will be interesting/useful for the organization. But please make sure that you pull (combine) data from at least six tables and compute relevant aggregate statistics. Please compute relevant aggregate statistics for each table that you join. In your queries later in part 2, you may join your Data Warehouse with other tables to answer useful questions. Please see many examples from class lectures and you may adapt those codes for your purpose (for this dataset). Submit a screenshot of the first 25 rows of your data warehouse (paste into this Word document) and the SQL code that you used to create it. Please copy and paste your SQL code into this Word document. If your PC does not show 25 rows of data, please submit what you have (i.e., rows you can see on a screenshot) with a comment that you cannot show 25 rows of data. Please add a description of what your Data Warehouse will be tracking for a company. 2. (104 points) Create eight SQL queries on your data warehouse (not on the original dataset) that answer interesting questions. At least 6 queries should be more complex queries. For example, more complex queries could include Joins, a Group By, UNION elements or a subquery or use some aggregate functions and summary calculations (see examples in the class lectures’ slides). Submit a copy of each query SQL code (paste into this Word document), and the screenshot of each query results (or the first 25 rows if it is longer or how many rows you can get on your PC) and full description of the question your SQL code was addressing and what you found in the results. The question that each query answers should be useful for a company to make decisions and act upon. 1.Find the most frequent product, category, and supplier country for the top 5 customers who have spent the most SELECT CustomerID, Order_Spent_Total, Most_Frequent_Product, Most_Frequent_Category, Most_Frequent_Supplier_Country FROM customer_report ORDER BY Order_Spent_Total DESC LIMIT 5;

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

2.Find customers who have a higher total spend than the average total spend of all customers SELECT CustomerID, Order_Spent_Total FROM customer_report WHERE Order_Spent_Total > (SELECT AVG(Order_Spent_Total) FROM customer_report );

（ I cannot show 25 rows of data ） 3.Find the most frequent supplier country for the customer who has spent more than global average SELECT CustomerID, Most_Frequent_Supplier_Country FROM customer_report WHERE

Order_Spent_Total > ( SELECT AVG(Order_Spent_Total) FROM customer_report ); （ I cannot show 25 rows of data ） 4.Find the top 25 consumers with the highest total spending among those who purchased more than 6 distinct categories

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

SELECT cr.CustomerID, COUNT(DISTINCT ca.CategoryID) as total_spend_categories, Order_Spent_Total FROM customer_report cr JOIN orders o ON cr.CustomerID = o.CustomerID JOIN order_details od ON o.OrderID = od.OrderID JOIN products p ON od.ProductID = p.ProductID JOIN categories ca ON p.CategoryID = ca.CategoryID GROUP BY cr.CustomerID HAVING COUNT(DISTINCT ca.CategoryID) > 6 order by Order_Spent_Total desc limit 25; （ I cannot show 25 rows of data ）

3. ( 75 points ) Create five Tableau individual visualizations (graphs) on your data warehouse with valuable information to present findings to senior management of the company. Save each visualization as a png file (as we will practice in the lab 5) and paste each individual visualization png file into this Word document with the full explanation of what the visualizations show, how they are useful to a company and how company management could make decisions based on what you show. Finally, combine those five visualizations into one Dashboard (as we will practice in the lab 5), and save this Dashboard as a png file and paste the Dashboard into this Word document. Please also save the whole Tableau project as a Tableau Workbook file .twb (In Tableau use File - Save as) and submit to the Final Team Project folder on Blackboard together with this Word document and together with the Excel file of your Data Warehouse which you uploaded to Tableau and used for visualizations. General grading criteria: Your completed work will be evaluated using the criteria below. I encourage you to use your creativity and other business skills (communication, presentation, critical thinking) in addition to the data management concepts and the SQL and Tableau skills that we have covered in CIS467. High score Score between high and good Good/medium score Low score All required parts of the final project are complete and technically correct. Queries are useful/interesting and provide valuable information for senior management to act upon. Not just random queries. Tableau visualizations provide interesting useful information based on which senior management of the company can make important decisions. All required parts of the final project are complete and technically correct (with possibly a few minor errors). Queries are useful/interesting and provide valuable information for senior management to act upon. Not just random queries (with possibly a few minor errors). Tableau visualizations provide interesting useful information based on which senior management of the Some required parts of the final project are missing and/or there are more significant errors. Some queries appear random and do not answer any useful/interesting questions. Tableau visualizations are very simple but may still provide interesting useful information based on which senior management of the company can make important decisions. The final project has large portions missing and/or major conceptual errors. Most/all queries (if any) appear random and do not answer any useful/interesting questions. Tableau visualizations are very simple and do not provide interesting useful information based on which senior management of the company can make important decisions.

company can make important decisions (with possibly a few minor errors).

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Final_Project_cis467_to_post_FallA_2023

Related Documents