Create a Database

DAD 220 Module Three Major Activity Guidelines and Rubric

Overview

Quantigration, a rapidly growing networking equipment manufacturer, has been having problems with their equipment returns. You’ve been hired to create a database using SQL commands for processing all returns.

Directions

You’ll use the return merchandise authorization (RMA) entity relationship diagram (ERD) to build a database. Each of the tables in the ERD represents a database entity. The ERD is the blueprint for building your relational database.

• Before you begin, do the following:Make sure to download the Module Three 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 to see expectations of what your assignment should look like.Make sure to review the example RMA entity relationship diagram (ERD) that you should be using as a guide before you begin.Review the module resources on how to capture screenshots, if necessary.

1. Go to your online integrated development environment (Codio), log in, start a new terminal session, and then create a database schema called QuantigrationRMA that can hold tables. List it out on the screen. Then, connect to the QuantigrationRMA schema.
2. Using the ERD as a reference, create the following tables with the appropriate attributes and keys:A table to store customer information with a primary key of Customer IDA table to store order information with a primary key of Order ID and foreign key of Customer IDA table to store RMA information with a primary key of RMA ID and foreign key of Order ID
3. Manually add 10 records into the Customers table. The data can be made up for now, as you’ll populate all three tables later from the provided CSV files.
4. You’ve been asked to establish a database view called “Collaborators” that is based on the “Customers” table. Create a view from the existing Customers table by using the SQL command provided below to say “Collaborators.” The view should show all instances of “Customer” renamed as “Collaborator.” Execute the following statements and provide one or more supporting screenshots showing the database view:The following command is partially complete. Fill in the missing information in the brackets to complete it and run the commands correctly: CREATE VIEW Collaborator ASSELECT CustomerID AS CollaboratorID, [Enter in the correct column names from that customer table that you want to change in the collaborator table] FROM Customers;DESCRIBE Collaborator;SELECT * FROM Collaborator LIMIT 5;

What to Submit

Submit your responses in the Module Three Major Activity Database Documentation Template. All of your answers should go in their respective 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 its description written below. This activity and the feedback from it will directly connect to the first step in Project One.

Supporting Materials

The following resources support your work on the project:

Document: Database Documentation Template ExampleUse this document for expectations of what your assignment should look like.

Document: Quantigration RMA DiagramMake 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.

Reading: MySQL 5.6 Reference ManualUse this documentation for reference while completing your assignment.

DAD 220 Module Three Major Activity Database Documentation Template
Overview
Complete these steps as you work through the directions for this activity. Replace the bracketed text
with your screenshots and brief explanations of the work they show. Each screenshot and its
explanation should be sized to approximately one quarter of the page, with the description written
below the screenshot. Follow these rules for each of the prompts and questions below. Review the
example document for help.
Create a Database
1. In your integrated development environment (IDE), create a database schema called
QuantigrationRMA. List out the database name. Provide the SQL commands you ran to
successfully complete this in your answer, then connect to it:
[Insert screenshot and brief explanation here.]
2. Using the entity relationship diagram (ERD) as a reference, create the following tables with the
appropriate attributes and keys:
a. A table named customers in the QuantigrationRMA database as defined on the project
ERD. Provide the SQL commands you ran against MySQL to complete this successfully in
your answer:
[Insert screenshot and brief explanation here.]
b. A table named orders in the QuantigrationRMA database as defined on the project ERD.
Provide the SQL commands you ran against MySQL to complete this successfully in your
answer:
[Insert screenshot and brief explanation here.]
c. A table named rma in the QuantigrationRMA database as defined on the project ERD.
Provide the SQL commands you ran against MySQL to complete this successfully in your
answer:
[Insert screenshot and brief explanation here.]
3. Manually add 10 records into the Customers table. The data can be made up for now, as you
you’ll populate all three tables later from the provided CSV files.
[Insert screenshot and brief explanation here.]
4. Create a view from the existing Customers table by using the SQL command provided below to
say “Collaborators.” The view should show all instances of “Customer” renamed as
“Collaborator.”
[Insert screenshot and brief explanation here.]