Assingments % - Populating the QuantigrationRMA Database and Running Queries

Overview
It’s now time to populate the QuantigrationRMA database from the Module Three major activity with records and run some basic queries against those tables. You’ll need to import data from the CSV data files into the tables, perform queries against those tables, and create an output listing to the screen of the query results.
Description of QuantigrationRMA Entity Relationship Diagram
An entity relationship diagram (ERD) is a visual representation of a database’s objects and relationships between those objects. It is a blueprint for creating a database, its tables (entities), and their attributes (fields). It also shows the data types and the relationships between entities (cardinality constraints) and keys in each table. In our example, we have three tables named customers, orders, and rma. These tables are the boxes that have several fields such as FirstName, LastName, OrderID, and so on. Each table also contains a primary key denoted by “PK” on the ERD. There is a standard notation called crow’s feet that shows the connection between tables. These lines represent different entity relation types such as one-to-one (1:1) or one-to-many (1:N), as visually depicted in the ERD.
Directions
In the Module Three Major Activity, you created a customer return merchandise authorization (RMA) database using SQL commands. To complete your objectives in this activity, follow these directions:
Before you begin, do the following:Make sure to download the Module Four Major Activity Database Documentation Template for this assignment. You’ll need to place your answers and screenshots in this document and then submit it for grading.
Check the Database Documentation Template Example for clarity on expectations of what yours should look like.
Make sure to review the example RMA ERD that you should be using as a guide before you begin.
Review the module resources on how to capture screenshots, if necessary.
Please note before you begin that the three data files being used for this project are preloaded into Codio:
rma.csv
customers.csv
orders.csv
Import the data from each file into tables.
Use the QuantigrationRMA database, the three tables you created, and the three CSV files preloaded into Codio.
Use the import utility of your database program to load the data from each file into the table of the same name. You’ll perform this step three times, once for each table.
Write basic queries against imported tables to organize and analyze targeted data.
For each query, include a screenshot of the query and its output. You should also include a 1- to 3-sentence description of the output.
Write an SQL query that returns the count of orders for customers located only in the city of Framingham, Massachusetts.
This query will use a table join between the Customers and Orders tables. The query will also use a WHERE clause.
How many records were returned?
Write an SQL query to select all of the customers located in the state of Massachusetts.
Use a WHERE clause to limit the number of records in the customers table to only those that are located in Massachusetts.
Record an answer to the following question: How many records were returned?
Write an SQL query to insert four new records into the Orders and Customers tables using the following data:Customers Table CustomerIDFirstNameLastNameStreetAddressCityStateZip CodeTelephone
100004 LukeSkywalker17 Maiden LaneNew YorkNY10222212-555-1234
100005WinstonSmith128 Sycamore Street GreensboroNC27401919-555-6623
100006MaryAnneJenkins2 Coconut WayJupiterFL33458321-555-8907
100007JanetWilliams58 Redondo Beach BlvdTorrenceCA90501310-555-5678
Orders TableOrderIDCustomerIDSKUDescription
1204305 100004ADV-24-10C Advanced Switch 10GigE Copper 24 port
1204306100005ADV-48-10F Advanced Switch 10 GigE Copper/Fiber 44 port copper 4 port fiber
1204307100006ENT-24-10F Enterprise Switch 10GigE SFP+ 24 Port
1204308100007ENT-48-10F Enterprise Switch 10GigE SFP+ 48 port
In the Customers table, perform a query to count all records where the city is Woonsocket and the state is Rhode Island.
How many records are in the customers table where the field “city” equals “Woonsocket”?
In the RMA database, update a customer’s records.
Write an SQL statement to select the current fields of status and step for the record in the RMA table with an OrderID value of “5175.”
What are the current status and step?
Write an SQL statement to update the status and step for the OrderID, 5175 to status = “Complete” and step = “Credit Customer Account.”
What are the updated status and step values for this record? Provide a screenshot of your work.
Delete RMA records.
Write an SQL statement to delete all records with a reason of “Rejected.”
How many records were deleted? Provide a screenshot of your work.
Create an output file of the required query results.Write an SQL statement to list the contents of the orders table and send the output to a file that has a .csv extension.
What to Submit
Submit your responses in the Module Four Major Activity Database Documentation Template. All of your answers should go in the correct locations in that document and then be submitted for grading and feedback. Each screenshot and its explanation should be sized to approximately one quarter of the page, with a description written below the screenshot. This activity and the feedback from it will directly connect to the second step in Project One.
Supporting Materials
The following resources support your work on the project:
Document: Database Documentation Template Example
Use this document for expectations of what your assignment should look like.
Document: Quantigration RMA Diagram
Make sure to review the example RMA entity relationship diagram (ERD) that you should be using as a guide before you begin. A text version is also available: Quantigration RMA ERD Text Version.

Struggling with where to start this assignment? Follow this guide to tackle your assignment easily!

Guide to Populating the QuantigrationRMA Database and Running Queries

Step 1: Understand the Database and the ERD

The QuantigrationRMA database consists of three main tables: Customers, Orders, and RMA (Return Merchandise Authorization). The Entity Relationship Diagram (ERD) visually depicts how these tables are related. Here’s a breakdown of each table and its relationship with others:

Customers Table: Contains information about the customers, such as their FirstName, LastName, Email, etc.
Orders Table: Contains details of customer orders, such as OrderID, CustomerID (foreign key referencing the Customers table), OrderDate, and the amount spent.
RMA Table: Contains records for merchandise returns, including ReturnID, OrderID (foreign key referencing Orders table), ReturnDate, and the reason for the return.

The ERD also shows the relationships between these tables:

A one-to-many relationship exists between Customers and Orders (one customer can have many orders).
A one-to-many relationship exists between Orders and RMA (one order can have many RMAs).

Before proceeding, ensure you understand the database structure based on this ERD.

Step 2: Import Data from the CSV Files into the Tables

To begin, you need to import the data from the provided CSV files into the QuantigrationRMA tables. Follow these general steps:

Open your SQL environment (e.g., MySQL Workbench, SQL Server Management Studio, etc.).
Create the database and tables based on the ERD you have been provided with.
Import the CSV data into the corresponding tables:
- For Customers, import data related to customer details.
- For Orders, import data such as the OrderID, CustomerID, OrderDate, and Amount.
- For RMA, import data like ReturnID, OrderID (to link to Orders), ReturnDate, and ReturnReason.

Example SQL command to import data from a CSV file:

Repeat the process for the Orders and RMA tables.

Step 3: Run Basic Queries

Once the data has been imported, you will need to run some basic SQL queries to interact with the data. Below are a few examples of queries you might need to run:

1. Query to View All Customers

This query retrieves all records from the Customers table.

2. Query to View Orders by Customer

This query shows all orders placed by a specific customer (replace customer_id_value with an actual customer ID).

3. Query to View All RMAs for a Specific Order

This query shows the RMA records for a specific order (replace order_id_value with an actual order ID).

4. Query to Find Customers with Returns

This query retrieves all customers who have made a return by joining the Customers and RMA tables.

Step 4: Document and Provide Query Outputs

As you perform each query, document the following in your Database Documentation Template:

The SQL query you executed.
The output or results of the query, showing the relevant data that is returned. If needed, take screenshots of the output to include in your template.

Example for the “View All RMAs for a Specific Order” query:

SQL Query: SELECT * FROM rma WHERE OrderID = 101;
Query Output: [Insert output from query here, such as a table of returned items for that order].

Step 5: Save and Submit the Documentation

Once you’ve executed the queries and documented the results, save your Database Documentation Template with screenshots and explanations, ensuring you meet the length requirements of 2-3 pages (1-1.5 pages for each question). Submit the completed documentation for grading.