Introduction

A common undertaking for a data analyst is to connect an external data collection to an existing dataset. This process involves the exploration of the source and target datasets to merge the new data in a meaningful and logical way.

You will replicate this common process in this task. You will take external data from a comma$-$separated values $($CSV$)$ file and design conceptual, logical$,$ and physical models that describe the data. You will load the new data into an existing database alongside the existing data and run queries across it$.$ The existing tables and CSV data files are located on this Labs on Demand website, as well.

You must use the rubric to direct the creation of your submission because it provides detailed criteria that will be used to evaluate your work. Each requirement below may be evaluated by more than one rubric aspect. The rubric aspect titles may contain hyperlinks to relevant portions of the course.

EXECUTE THE TASK IN pgAdmin$4$

Perform the following tasks to combine an existing dataset with external data:

A$.$ Provide a question that can be answered using structured query language $($SQL$)$ to acquire data from both the original database and the add$-$on CSV file data. The question should require data from both data sources.

$1.$ Justify how your question provided in part A will be answered.

$2.$ Identify which data from both the original dataset and the add$-$on CSV file are needed to answer the question provided in part A$,$ including all tables, columns, and data types.

B$.$ Create an entity relationship diagram $($ERD$)$ for the add$-$on CSV file and any other tables and columns used to answer the question from part A by evaluating the data contained in the file and identifying the $1$:$1,1$:M$,$ or M:N relationships and relational constraints.

$1.$ Describe the relationship between the existing table and the table created in part B and discuss any issues with the relationship in the ERD.

$2.$ Write a SQL statement, in text format, that creates a table for the add$-$on CSV based on the ERD and specifies the columns and relevant keys.

$3.$ Write a SQL statement, in text format, that loads the data from one of the add$-$on CSV files into the table created in part B$.$

Note: Do not include SQL statements as a screenshot.

C$.$ Write one or more SQL statements in text format for a query or queries that answer the question provided in part A$.$

Note: Do not include SQL statements as a screenshot.

$1.$ Provide data files that capture the results from the query or queries.

D$.$ Identify the specific time period for how often the add$-$on file should be acquired and refreshed in the database for the data to remain relevant to the business activities and the question from part A$.$

$1.$ Explain why the time period identified in part D is relevant to the specific business activities.

E$.$ Explain the whole process

$$ A demonstration of each of the following:

$-$ All code used

$-$ The code being executed

$-$ The results of all code used in the task

$$ A description of all programs used to complete the task

F$.$ Acknowledge two web sources for any code used to support the application. Be sure the web sources are reliable.