Business Intelligence
Nick122
3 years ago
50
files (4)ERDAssignmentonDatawarehouse_-607679685.docx
ERD_Assignment1_532328726.docx
SQLAssignmentPart2_1113064223.docx
SQLAssignmentPart1_-73180751.docx
ERDAssignmentonDatawarehouse_-607679685.docx
Fall 2023 Dr. Alam, CS 538 Datawarehouse ERD Assignment
CS 538 Business Intelligence and Data Minning Data Warehouse ERD Creation Assignment
Prerequisite:
Before beginning this assignment, ensure that you've thoroughly read and understood Chapters 9 (pages 197 to 235) and 10 (pages 237 to 251) on Dimensional Modeling from the course textbook.
Assignment Context:
You are stepping into the shoes of a Junior BI developer involved in a data warehouse project. As part of the requirements gathering phase, you have a discussion with Jim Riner, the Sales Manager. Jim identifies a crucial need for deeper sales data analysis that encompasses the following dimensions:
1. Products
2. Customers
3. Dates (Seasonality)
4. Orders
5. Sales Territory
Specific Dimension Requirements:
1. Product Dimension:
· Analyze sales based on categories, subcategories, product names, colors, and models.
· This will help in identifying top-selling items in various categories and attributes.
2. Customer Dimension:
· Explore sales data to determine which customers purchase which items, pinpoint top customers, and analyze sales by the customer's zip, territory, country, and city.
· This information can aid in tailoring promotional offers and understanding buying patterns of valued customers.
3. Date (Seasonality) Dimension:
· Analyze which products have high sales during specific seasons, days, weeks, or years.
· The granularity of this dimension should include: Date Surrogate Key, Date Value, Month, Year, IsHoliday, and Holiday Name.
4. Order Dimension:
· Sales analysis based on Order ID, Order Detail ID, and Customer ID.
5. Sales Territory Dimension:
· The analysis should cover territory name, territory group, country, or region codes.
· The objective is to determine the profitability of specific geographic locations, products sold there, and revenue comparison between regions.
Assignment Task:
Given the requirements and understanding from chapters 9 and 10 on Star Schema, your task is to:
1. Design an ERD diagram for a Star Schema that will integrate the central fact table with the required dimension tables.
2. Refer to Figures 9.10 and 9.18 (for the date dimension) in the textbook as guidance.
Submit your ERD diagram by 10/02/2023.
Good luck, and ensure your design captures the depth and granularity necessary for effective sales data analysis.
ERD_Assignment1_532328726.docx
Dr. Alam, CS 488 Project
Title: Design and Implement a Database System for a Restaurant Operation CS 538 Business Intelligence and Data Mining Fall 2023
Design Entity Relationship Diagram (ERD)
Due: 09/14/2023
Objective:
The objective of this course project is to design an Entity Relationship Diagram (ERD) for a restaurant sales and management system. The project aims to provide students with hands-on experience in analyzing complex business rules and modeling a database schema that effectively captures the data requirements and relationships of a restaurant operation. Through this project, students will enhance their understanding of conceptual database design, normalization, and data modeling techniques.
Business Scenario Description:
PG (Papa Georgios), the owner of a pizzeria and restaurant, wishes to capture all sales data with detailed order information. An order can consist of multiple items, such as pizzas, breadsticks, and beverages, placed by a customer. Employees are hired for various roles, including a restaurant manager, assistant managers, chefs, cooks, waiting staff, and delivery drivers. Reporting hierarchies exist among the employees, with the assistant managers reporting to the manager, and the chefs, waiting staff, and delivery drivers reporting to the assistant managers. Employees can have multiple supervisors. Job history for each employee needs to be maintained.
The restaurant operates from 11 am to 11 pm, and employees are required to be present between 10 am and 12 am to open and close the restaurant. The manager, assistant managers, and chefs receive salaries, while the cooks, waiters, and delivery drivers are paid hourly wages. Chefs require certifications to prepare specific dishes, which need to be up to date. The restaurant manager and assistant managers are required to have special degrees in restaurant management. Employee information such as names, addresses, and dates of birth also needs to be stored.
Employees on salaries do not need to clock in or out, whereas those on hourly wages must record their work hours for payroll calculations. Historical data regarding employment status and promotions of each employee must be stored. The restaurant serves various food items such as pizzas, pasta, garlic bread, chicken wings, beverages, desserts, and sushi. Each item has different serving sizes, prices, and historical prices. Payment methods accepted include cash, checks, and major credit cards.
The restaurant wishes to provide an app that updates customers on the food preparation stage of their order, indicated by coded numbers for stages such as making, baking, preparing, and on the way. Inventory tracking is required to ensure timely reordering of ingredients, including fresh dough, sauces, and other food supplies. Information about food vendors and other supplies, such as cleaning supplies, should be stored in the database.
The restaurant offers a club card (PG Card) to registered customers, awarding points for every dollar spent. Customers accumulate points and can redeem a $10 coupon upon reaching 100 points, which resets their point balance to zero. At the time of payment, customers are prompted to provide a rating for food and service quality on a scale of 1 to 5.
Deliverable(s):
· An ERD for the entire database
Instructions:
1. Data Analysis and Requirements Gathering:
· Start by understanding the problem domain thoroughly. Knowing what you're trying to represent in your database is half the battle.
· Analyze the provided business scenario in detail.
· Identify entities, relationships, and attributes based on the requirements.
· Consider complex business rules and data dependencies while gathering requirements.
2. ERD Design and Modeling:
· Create an Entity Relationship Diagram (ERD) that accurately represents the entities, relationships, and attributes identified.
· Consider different types of relationships (one-to-one, one-to-many, many-to-many), cardinalities, and attributes required for each entity.
· Incorporate advanced concepts such as weak entities, ternary relationships, and inheritance (sub-type, super-type relationships) if relevant to the scenario.
· Use Crow's Foot notations to depict entities, relationships, and cardinalities.
· Apply normalization techniques to ensure data integrity and minimize redundancy. Entities should be normalized to 3NF.
3. Documentation and Presentation:
· Prepare comprehensive documentation that explains the rationale behind the design decisions and justifies the chosen relationships, attributes, and cardinalities.
· Create a professional presentation to showcase the ERD, discussing the entities, relationships, and complex business rules depicted in the diagram.
· Highlight any additional considerations or design
4. Evaluation Criteria:
· Accuracy and completeness of the ERD in reflecting the selected scenario and its complex business rules.
· Adherence to normalization principles and best practices for efficient and effective database design.
· Clarity and organization of the documentation and presentation.
· Demonstration of an in-depth understanding of the scenario and the ability to translate complex business rules into a structured ERD.
SQLAssignmentPart2_1113064223.docx
Please comply with the following instructions:
1. Upload your answer in a text file; one text file per submission on Canvas.
2. Indent your queries appropriately as shown in class.
3. Execute each query and fix any error before turning in the assignment.
4. Write the question number before each query.
5. Name the file in the following format: “SQL Assignment Part 1 Your Last Name, Your First Name”.
6. Only submit a text file with the .txt extension that will include all the queries separated by the question number.
7. Questions 14, 15 require the MOVIE database. You can find the data in an Excel file uploaded on Canvas (under module). Please import the data from that Excel file to SQL Server. If you are not familiar with how to import data from Excel please watch the clip in the following URL: https://www.youtube.com/watch?v=seS8Yd_6nzs
At :44 in the clip, choose “Microsoft Excel 97-2003” for yourself. The Excel file containing the movie database is in 97-2003 format.
A query that does not return the right result or has wrong syntax will receive zero point.
You do not need to write sub-queries to work on this assignment.
Using the AdventureWorks database write SQL statements to execute the following queries:
1. Write the SQL code required to list the Product Name, Product Number, and Product Subcategory Name from Production.Product, and Production.ProductSubcategory tables. Use left/right outer join to display Product information regardless of association to a subcategory or not.
2. Write the SQL code required to list the Name, ListPrice (from the Production.Product table), product sub-category name (from Product.ProductSubcategory table), and category name (table Production.ProductCategory table) for each product regardless of their association to a sub-category name or a category name. Give the Name columns from Production.Product the alias “ProductName”, Production.ProductSubcategory the alias “ProductSubcategoryName”, and Production.ProductCategory the alias “ProductCategoryName”. Order the results by the “ProductCategoryName” in descending order and then the “ProductSubcategoryName” in ascending order.
3. Write the SQL code required to list the sales person IDs, first and last names, sales territory names, and sales amounts by joining the tables Person.Person, Sales.SalesPerson, and Sales.SalesTeritory. Group your results by the SalesPersonID, First Name, Last Name, and territory name.
4. Write the SQL code required to list the number of rows in the Person.Person table.
5. Write the SQL code required to list how many rows in the Person.Person table do not have a NULL value in the Middle Name column?
6. Write the SQL code required to list the average StandardCost (in Production.Product table) for the products.
7. Write the SQL code required to list What is the average Freight amount for each sale (in Sales.SalesOrderHeader) where TrritoryID= 4.
8. Write the SQL code required to list the most expensive product based on the ListPrice in the Production.Product table.
9. Write the SQL code required to list how many persons are included in each PersonType in the Person.Person table.
10. Write the SQL code required to list how many products in the Production.Product table have colors red, and black.
11. Write the SQL code required to list the number of sales in each territory between July 1, 2005, and December 31, 2006 (One of the tables to be used is Sales.SalesOrderHeader table). Order the results by the sale count in descending order.
12. Expanding on the previous query, write the SQL code required to join the Sales.SalesOrderHeader table with the SalesTerritory table and group the results by the territory name.
13. Write the SQL code required to list TerritoryID, Name AS [Territory Name], and TotalDue from the Sales.SalesOrderHeader and SalesTerritory tables. Show aggregated TotalDue for each Territory where TotalDue>$10,000,000.
14. Write the SQL code required to list ACTOR_ID, ACTOR_NAME and MOVIE_NAME. Show the results in such a way that every actor name from the ACTORS table is listed regardless they acted in a movie or not.
15. Take the previous question as a base and then group the ACTOR_ID and ACTOR_NAME, and show how many movie(s) each actor has acted on. Order the query results by the ACTOR_NAME. Your MOVIE table has different data so your results will not exactly look like the following screen shot:
image5.png
image4.png
image7.png
image6.png
image9.png
image8.png
image12.png
image10.png
image11.png
image13.png
image14.png
image15.png
image1.png
image3.png
image2.png
SQLAssignmentPart1_-73180751.docx
Please follow these instructions for submitting your SQL assignment:
· Upload your answers in a text file
· Only submit a text file with the .txt extension. The file should contain all of your queries, separated by the question number
· Indent your queries whenever needed
· Execute each query and fix any errors before submitting your assignment
· Write the question number before each query
· Name the file in the following format: SQL Assignment Part 1 [Your Last Name] [Your First Name]
Here are some additional tips for submitting your assignment:
· Make sure that your file is named correctly and that it has the correct extension
· Double-check your queries to make sure that they are correct and that they do not contain any errors
· Save your file before submitting it
A query that does not return the right result or has wrong syntax will receive a zero.
The sample output of the queries is provided in the form of screenshots. Write the SQL statements for the following queries using the AdventureWorks database.
1. Write the SQL code to list the BusinessEntityId, FirstName, MiddleName, and LastName columns from the Person.Person table, and return the top 500 rows. Use column aliases of "ID", "First Name", "Middle Name", and "Last Name".
2. Write the SQL code required to list BusinessEntityID, Name, ContactType, FirstName, LastName, PhoneNUmber, EmailAddress, EmialPromotion from the view called Sales.vStoreWithContacts and return all the records where EmailPromotion equal to 1 and ContactType equal to “Owner”. Order your results by the “FirstName” in descending order.
3. Write the SQL code required to list SalesPersonID and TotalDue columns from the Sales.SalesOrderHeader table and return all the records where TotalDue > $70,000 and SalesPersonID is not null.
4. Write the SQL code required to return all the records from the view called Sales.vIndividualCustomer where LastName ends with the letter“r”. (Hint: a wildcard character can assist you with this query).
5. Write the SQL code to list the ProductID, Name, Color, listPrice, and Style columns from the Production.Product table, and return all records where the Color is Grey and the listPrice is less than or equal to $2,500. Additionally, return ProductID between 600 and 700 regardless of their color.
6. Write the SQL code required to list SalesPersonID, and TotalDue from the table Sales.SalesOrderHeader. Return all records where TotalDue value > $70,000 and SalesPersonID is not NULL.
7. Write the SQL code required to list SalesOrderID, OrderDate, CustomerID, SalesPersonID, and TotalDue from the table Sales.SalesOrderHeader. Filter the records where TotalDue is between $10,000 and $15,000. Additionally, all the sales for the SalesPersonID = 277 should be included regardless of the TotalDue amount for that SalesPersonID.
8. Write the SQL code required to show all customer records from the view Sales.vIndividualCustomer whose LastName is either “Lopez”, “Martin”, or “Wood”, and whose FirstName starts with any letter between “C” and “L” in the alphabet. (Hint: multiple wildcard characters will be used in this query)
9. Write the SQL code required to list Name, AnnualSales, YearOpened, SquareFeet, and NumberEmployees columns from the view called Sales.vStoreWithDemographics. Give the SquareFeet column the alias “Store Size” and the NumberEmployees “Total Employees”. Return only those rows with AnnualSales > $1,000,000 and NumberEmployees>= 45. Order your results by the “Store Size” alias in descending order, and then by the “Total Employees” alias in descending order.
10. Write the SQL code required to list FirstName, LastName and CountryRegionName columns from the view called Sales.vIndividualCustomer. Sort the results by the CountryRegionName column. Use the column ordinal in the ORDER BY clause.
11. Write the SQL code required to list Production.Product.Name, Production.Product.ProductNumber, and Production.ProductSubcategory.Name by inner joining the tables Production.Product, and Production.ProductSubcategory.
12. Write the SQL code required to list Person.Person.FirstName, Person.Person.LastName, Person.EmailAddress, Person.PhoneNumber by joining the tables Person.Person, Person.EmailAddress, Person.PersonPhone.
13. Write the SQL code required to list Production.Product.Name AS [Product Name], Production.Product.Color, Production.Product.ListPrice, Production.ProductSubcategory.Name, Production.ProductReview.Rating, Production.ProductReview.Comments by joining the following tables: Production.Product, Production.ProductSubcategory, Production.ProductCategory, Production.ProductReview.
14. Write the SQL code required to list BusinessEntityID, SalesYTD, Sales.SalesTerritory.Name colums by joining the tables Sales.SalesPerson and Sales.SalesTerritory. Join the two tables in a way that every in Sales.SalesPerson records will be returned regardless of whether they are assigned to a territory or not.
15. Use the previous query result as the foundation for the current query and inner join the Person.Person table to return the salesperson’s first name and last name in addition to the previous query. Only show the records where the territory name is either “Northeast” or “Central.”
image4.png
image7.png
image6.png
image9.png
image8.png
image12.png
image10.png
image11.png
image13.png
image14.png
image15.png
image1.png
image3.png
image2.png
image5.png
- DQ in two hours
- for nyanya ccmh 535 week 3 8 questyions
- Why Do Some Managers Hate the Performance Evaluation?
- Individual Report on Business Management
- Use this
- Module 2B
- can anyone write this paperon prostitution? 2.5 pages
- Week 7 serial killers
- Database Admin Job Aid pt2
- Product Design*****Already A++ Rated Tutorial*****Use as Guide Paper*****
