Homework 5: Star Schemas, SQLite3, & BigQuery OverviewA company named MOVIE GEN Corp. needs your help to create and access a movie database. You are interested in helping them create this database. In this assignment, you will load a CSV file containing IMDB data into a database and run queries on it to retrieve records.In industry, typically, SQL databases are designed to support either transactions or analysis. Transactional databases are often kept in normal form (which means they obey DRY). Each data element is unique a particular table and row. This helps guarantee updates will work correctly, as will transactions involving things like selling a limited number of tickets. However, accessing collections of variables used in typical analysis tasks requires queries with a lot of join operations, which can be slow to code. Complex queries also tend to slow down the transactional database system while they are being run. Because of this, must companies run transform their transactional databases (typically nightly) into a second database amenable for analysis called a data warehouse. The star schema or a variant (like snowflake schema) are typically used in data warehouses. These schemas allow users to quickly query transactional variables of interest using a simple query structure.DetailsFor any database construction task, understanding the data is the first step. After you understand the variables and their relationships you can build a schema, and then write code to create, import and update the database.Notebook and dataset provided:●SQL-Stencil.ipynb●all_data-raw.csvTask 5.1-5.5 Finish Part 5.1 - 5.5 in the Jupyter Notebook. GCP access should now be enabled for your accounts BigQuery BigQuery is a serverless, highly-scalable, and cost-effective cloud data warehouse with an in-memory Business Intelligence Engine and machine learning built-in (watch the video). It enables super-fast SQL queries using the processing power of Google’s infrastructure. User companies are freed from building their own data centers and infrastructure by paying $5 per TeraByte of data queried. Setup: Visit https://console.cloud.google.com/bigquery and login to Google Cloud with your account. Note: the first TeraByte used in queries per month is free. On the top navbar, create a project named DATA1050. In the BigQuery interface, the left side of the page is the navigation bar. In the “Resources” section, choose “ADD DATA > Explore public datasets”. (See screenshot) Search for “Stack Overflow” and click “View Dataset”. This opens up a new browser tab with the public dataset project added to the “Resources” section. The “Resources” section shows us the Stack Overflow dataset as well as other publicly-available ones on the left corner. The dataset we are querying is `bigquery-public-data.stackoverflow`. Click on any table to view its schema (see screenshot). There are other useful buttons in the same view.Preview  —  shows us the first few rows of the tableQuery Table  —  expands the query editor, where we will write our SQL queriesIn the query editor, run the following sample queries.SELECT * FROM `bigquery-public-data.stackoverflow.post_history` LIMIT 10;Make sure to add a semicolon at the end of each query to separate queries. SQL is not case-sensitive. However, we ask you to always use uppercase for the reserved keywords like SELECT and FROM. Write names for the table and columns in lower case. Stack OverflowStack Overflow is a question and answer site for professional and enthusiast programmers. Programmers post/answer questions and reputation score is granted upon upvotes. The voting system rewards high-quality and responsible content across the website. A question can have multiple answers (or none) posted by multiple users. Posts (questions or answers) can be upvoted by users. Now that you have a basic idea of how to work with SQL in BigQuery. Let’s analyze a very common problem that a lot of public forums with a reputation system faces. The following tasks will build up to a query that detects if a user is farming reputations using spam accounts. Voting fraud most often happens with a single user up-vot代写Star Schemas作业作业、代写BigQuery课程作业、SQL程序设计作业调试、代做SQL语言作业 代做Daes or down-votes on many posts of another user within a short period of time. This is not considered normal behavior and the system tries to disallow it.Note: because votes are anonymous in this dataset, in this assignment we will focus on question-answer relation rather than answer-upvote relation to detect reputation farm. This means, a user is considered fraudulent if his questions are answered too frequently by another user. Tables and fields you may use:Table Name Field Name Descriptionsbigquery-public-data.stackoverflow.posts_questions id ID of a question owner_user_id ID of the user who posts the questionbigquery-public-data.stackoverflow.posts_answers parent_id ID of the question this answer belongs to owner_user_id ID of the user who posts the answerTask 5.6 Write SQL in BigQuery to answer the following tasks. You should only write one SQL statement per task (sub queries are OK). For each of the tasks, record your solution in the python notebook by inserting a copy of your query and a screenshot of its results. The following information must be present in the screenshot for you to receive credits:●The first few lines of the query results●The time it takes to complete the query (Note: the results should not be cached. If the completion time is super small, the query is probably cached.)●The number of bytes processed to complete the queryAdditionally, you must comment on each subquery and complicated join in your SQL statement. It is suggested you indent subqueries properly as shown in the hints given for 5.6.3 & 5.6.4.See an example screenshot.Task 5.6.1: Question CountWrite a query on table `bigquery-public-data.stackoverflow.posts_questions` to check how many questions a certain user U (with ID=426) has posted. (Hint: look at owner_user_id).Task 5.6.2: Question-answer PairsWrite a query to check how many questions a user U (with ID=426) made that were answered by another user V (with ID=2142539). This time, use table `bigquery-public-data.stackoverflow.posts_answers` as well.Task 5.6.3: Question-answer RatioNormalize the question-answer pair count by the total number of question user U asks. More specifically, combine the total questions from a user U (with ID=426) and questions from U answered by V (ID=2142539), into a ratio consisting of the total questions from U answered by V divided by the total questions from U. In short, calculate where the ratio function is defined above.Hint: use a sub-queryRead the hint for 5.6.3 and 5.6.4 in this document.Task 5.6.4: Top AudienceFind the top 5 users V that maximizes the ratio in Task 3. The user U is still fixed to ID=426. In short, find the top 5 V’s which maximize Hint: use GROUP BY, ORDER BY, LIMIT Task 5.6.5: Top OffendersFind the top 5 U-V user pairs that maximizes the ratio in Task 3. Here neither U nor V is fixed. Hint: also use a sub-query, and a WITH statementIf you think finding top offenders this way may be over-simplified, be ready to explain why in 5.6.6.Task 5.6.6: Refinements (Written Question)Your boss wants you to label the top-ranking pairs from Query 5 as fraudulent (i.e. reputation farming). You both agree that answer-vote relation might be a better indicator than question-answer relation. Other than that, describe at least 3 aspects where Query 5 needs to be improved to reduce false positive or false negative in fraudulent detection. You only need to describe those improvements. For example:-Users will be falsely considered fraudulent in Query 5 if they … but this is actually innocent behavior. We should ...-Users will be falsely considered innocent in Query 5 if they … but this is actually fraudulent behavior. We should ...-A better indicator of the probability of fraudulence can be derived with XXX taken into consideration because … We should ...Submission-Push the notebook to your repo. -Output of each cell should be present in your submission-Clean up all unnecessary cells before submission.-Save your notebook as a PDF Rubric-Task 5.1 - 5.5: 60%-12% each-Task 5.6: 40%-6% each for Task 5.6.1 - 5.6.5-10% for Task 5.6.6转自：http://www.daixie0.com/contents/15/4485.html