DAD 220 Database Documentation Template - Taylor

docx

School

Southern New Hampshire University *

*We aren’t endorsed by this school

Course

220

Subject

Mechanical Engineering

Date

Feb 20, 2024

Type

docx

Pages

Uploaded by haleytaylor624

DAD 220 Database Documentation Template Complete these steps as you work through the directions for Project One. Replace the bracketed text with your screenshots and brief explanations of the work they capture. 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 located in the Project One Supporting Materials for assistance. Step One: Create a Database 1. Navigate to your online integrated development environment (IDE). List and record the SQL commands that you used to complete this step here: Command(s) Used: chmod +x change_perm.sh ./change_perm.sh mysql

2. Create a database schema called QuantigrationUpdates. List out the database name. Provide the SQL commands you ran against MySQL to successfully complete this in your answer: Created data base called QuantigrationUpdates. This is an empty database. The USE function is to select the database to work with. The SHOW DATABASES command lists all of the current databases that are available to use. Command(s)Used: CREATE DATABASE QuantigrationUpdates; USE QuantigrationUpdates; SHOW DATABASES;

3. 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 QuantigrationUpdates database, as defined on the project ERD. Provide the SQL commands you ran against MySQL to complete this successfully in your answer: Created table called Customers within the QuantigrationUpdates database. The table has several columns for pertinent customer information. The primary key of this table is the CustomerID which can only be a unique value. The command DESCRIBE lists out all the columns associated with the table as well as a description of the attributes that can be entered. For example, INT is associated with a number and VARCHAR(25) refers to any character that can be entered with a limit of 25 characters. Command Used: CREATE TABLE Customers (CustomerID INT, FirstName VARCHAR(25), LastName VARCHAR(25), Street VARCHAR(50), City VARCHAR(50), State VARCHAR(25), ZipCode VARCHAR(10), Telephone VARCHAR(15), PRIMARY KEY(CustomerID)); DESCRIBE Customers;

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

b. A table named Orders in the QuantigrationUpdates database, as defined on the project ERD. Provide the SQL commands you ran against MySQL to complete this successfully in your answer: Created table called Orders within the QuantigrationUpdates database. The table has four columns for pertinent orders information such as SKU and Description. The primary key of this table is the OrderID which can only be a unique value and not NULL. This table also has a foreign key, which is CustomerID from the Customers table. The command DESCRIBE lists out all the columns associated with the table as well as a description of the attributes that can be entered. For example, INT is associated with a number and VARCHAR(20) refers to any character that can be entered with a limit of 20 characters. Command(s) Used: CREATE TABLE Orders (OrderID INT NOT NULL PRIMARY KEY, CustomerID INT, SKU VARCHAR(20), Description VARCHAR(50), FOREIGN KEY(CustomerID) REFERENCES Customers(CustomerID)); SHOW Orders;

c. A table named RMA in the QuantigrationUpdates database, as defined on the project ERD. Provide the SQL commands you ran against MySQL to complete this successfully in your answer: Created table called RMA within the QuantigrationUpdates database. The table has five columns for product return information including Step, Status, and Reason. The primary key of this table is the RMAID which can only be a unique value and not NULL. This table also has a foreign key, which is OrderID from the Orders table. The command DESCRIBE lists out all the columns associated with the table as well as a description of the attributes that can be entered. For example, INT is associated with a number and VARCHAR(50) refers to any character that can be entered with a limit of 50 characters. Command(s) Used: CREATE TABLE RMA (RMAID INT NOT NULL PRIMARY KEY, OrderID INT, Step VARCHAR(50), Status VARCHAR(15), Reason VARCHAR(15), FOREIGN KEY(OrderID) REFERENCES Orders(OrderID)); DESCRIBE RMA;

Step Two: Load and Query the Data 1. Import the data from each file into tables.  Use the QuantigrationUpdates 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 will perform this step three times, once for each table. Loaded data from CSV fata files saved in the codio workspace. The LOAD DATA INFILE command followed by the location of the data is where the data it located. The INTO TABLE give the table information in which the data should be loaded. The FIELDS TERMINATED BY ',' indicates what columns are separated by a comma. The LINES TERMINATED BY '\r\n' tells where the line ends in the CSV file when there is no more data returned. This command was used to load data as follows: customers.csv was loaded to the Customers tables, orders.csv was loaded to the Orders table and the rma.csv was loaded to the RMA table. Command(s) Used: LOAD DATA INFILE '/home/codio/workspace/customers.csv' INTO TABLE Customers FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; LOAD DATA INFILE '/home/codio/workspace/orders.csv' INTO TABLE Orders FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; LOAD DATA INFILE '/home/codio/workspace/rma.csv' INTO TABLE RMA FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

2. Write basic queries against imported tables to organize and analyze targeted data. For each query, replace the bracketed text with 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. i. How many records were returned? The query to count the number of orders for customers located in Framingham, MA resulted in 505 orders. The INNER JOIN command combines the Customers and the Orders table and locates the customer by the CustomerID. The WHERE command filters the data to the particular city (Customers.City) and state (Customers.state). Command(s) Used: SELECT COUNT(*) FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Customers.City = 'Framingham' AND Customers.State = 'Massachusetts';

 Write an SQL query to select all of the Customers located in the state of Massachusetts. i. Use a WHERE clause to limit the number of records in the Customers table to only those who are located in Massachusetts. ii. Record an answer to the following question: How many records were returned? The query to count the number of customers in the state of MA returned 982 customers. The WHERE command limits the number of customers from the state (Customers.State) in which they are locate in. Command(s) Used: SELECT COUNT(*) FROM Customers WHERE Customers.State = 'Massachusetts';  Write a SQL query to insert four new records into the Orders and Customers tables using the following data: Customers Table CustomerI D FirstName LastNam e StreetAddress City State ZipCod e Telephone 100004 Luke Skywalker 15 Maiden Lane New York NY 10222 212-555-1234 100005 Winston Smith 123 Sycamore Street Greensbor o NC 27401 919-555-6623 100006 MaryAnne Jenkins 1 Coconut Way Jupiter FL 33458 321-555-8907 100007 Janet Williams 55 Redondo Beach Blvd Torrence CA 90501 310-555-5678

The INSERT INTO command is used to enter customer information into the Customers table. The VALUES command is the attribute information entered which is the CustomerID, FirstName, LastName, Street, City, State, ZipCode, and Telephone. The SELECT Command specifies which customer information to display based on the CustomerID. Command(s) Used: INSERT INTO Customers Values (100004, 'Luke', 'Skywalker', '15 Maiden Lane', 'New York', 'NY', '10222', '212-555-1234'), (100005, 'Winston', 'Smith', '123 Sycamore Street', 'Greensboro', 'NC', '27401', '919-555-6623'), (100006, 'MaryAnne', 'Jenkins', '1 Coconut Way', 'Jupiter', 'FL', '33458', '321-555-8907'), (100007, 'Janet', 'Williams', '55 Redondo Beach Blvd', 'Torrence', 'CA', '90501', '310-555-5678'); SELECT * FROM Customers WHERE CustomerID IN (100004, 100005, 100006, 100007); Orders Table OrderID CustomerID SKU Description 1204305 100004 ADV-24-10C Advanced Switch 10GigE Copper 24 port 1204306 100005 ADV-48-10F Advanced Switch 10 GigE Copper/Fiber 44 port copper 4 port fiber 1204307 100006 ENT-24-10F Enterprise Switch 10GigE SFP+ 24 Port 1204308 100007 ENT-48-10F Enterprise Switch 10GigE SFP+ 48 port

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

The INSERT INTO command is used to enter customer information into the Orders table. The VALUES command is the attribute information entered which is the OrderID, CustomerID, SKU, and Description. The SELECT Command specifies which customer information to display based on the OrderID. Command(s) Used: INSERT INTO Orders VALUES (1204305, 100004,'ADV-24-10C', 'Advanced Switch 10GigE Copper 24 port'), (1204306, 100005, 'ADV-48-10F', 'Advanced Switch 10 GigE Copper/Fiber 44 port copper 4 port fiber'), (1204307, 100006, 'ENT-24-10F', 'Enterprise Switch 10GigE SFP+ 24 Port'), (1204308, 100007, 'ENT-48-10F', 'Enterprise Switch 10GigE SFP+ 48 port'); SELECT * FROM Orders WHERE OrderID IN (1204305, 1204306, 1204307, 1204308);

 In the Customers table, perform a query to count all records where the city is Woonsocket, Rhode Island. i. How many records are in the Customers table where the field “city” equals “Woonsocket”? There are 7 records of customers from the Customers table located in Woonsocket, RI. The SELECT COUNT counts the number of customers from the Customers table and the WHERE command give the city and state information from where to count from. Command(s) Used: SELECT COUNT(*) FROM Customers WHERE City = 'Woonsocket' AND State = 'Rhode Island';

 In the RMA database, update a customer’s records. i. 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.” 1. What are the current status and step? The Status of OrderID 5175 is “Awaiting customer Documentation” and the Status of the Order is “Pending”. The SELECT command followed by the requested columns, which are Step and Status, WHERE command specifies which record to pull which is OrderID 5175. Command(s) Used: SELECT Step, Status FROM RMA WHERE OrderID = 5175;

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

ii. Write an SQL statement to update the status and step for the OrderID , 5175 to status = “Complete” and step = “Credit Customer Account.” 1. What are the updated status and step values for this record? The Step and Status column for OrderID 5175 was updated using the UPDATE SET command. The UPDATE refers to the table and the SET command refers to the entity being updated. Command(s) Used: UPDATE RMA SET Step = 'Credit Customer Account.', Status = 'Complete' WHERE OrderID = 5175; SELECT Step, Status FROM RMA WHERE OrderID = 5175;  Delete RMA records. i. Write an SQL statement to delete all records with a reason of “Rejected.” 1. How many records were deleted? There were 596 rows that were deleted due to the Reason column equaling “Rejected”. The DELETE FROM command tells what table to delete the information from which is the RMA tables. The WHERE command specifies which column and attribute to delete, which is where the word “Rejected” was in the Reason column. The SELECT COUNT(*) FROM RMA WHERE Reason = 'Rejected’ command is a validation step to ensure that there are no longer any RMA records that have the Reason as Rejected. Command(s) Used: DELETE FROM RMA WHERE Reason = 'Rejected'; SELECT COUNT(*) FROM RMA WHERE Reason = 'Rejected';

3. Update your existing tables from “Customer” to “Collaborator” using SQL based on this change in requirements. Provide the SQL commands you ran against MySQL to complete this successfully in your answer: a. Rename all instances of “Customer” to “Collaborator.” The ALTER TABLE command was used for all the below commands. First the FOREIGN KEY (CustomerID) had to be dropped from the Orders Table as well as the PRIMARY KEY from the Customers Table (CustomerID). Next, the table name had to be changed from Customers to Collaborators. Then the Collaborators tables column CustomerID was changed to CollaboratorID and established as the PRIMARY KEY. The last step was to change the CustomerID in the Orders table to CollaboratorID and ADD CollaboratorID as the FORIENG KEY. The DECRIBE statements were used in between all of the commands to validate that the column names were updated. Command(s) Used; ALTER TABLE Orders DROP FOREIGN KEY Orders_ibfk_1;

ALTER TABLE Customers DROP PRIMARY KEY; RENAME TABLE Customers TO Collaborators; SHOW TABLES IN QuantigrationUpdates;

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

ALTER TABLE Collaborators CHANGE COLUMN CustomerID CollaboratorID INT PRIMARY KEY; DESCRIBE Collaborators; ALTER TABLE Orders CHANGE COLUMN CustomerID CollaboratorID INT; ALTER TABLE Orders ADD FOREIGN KEY(CollaboratorID) REFERENCES Collaborators(CollaboratorID); DESCRIBE Orders;

4. 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. Created a CSV file that contains the data from the Orders table called quantigrationupdates6- _1.csv. Command(s) U sed: SELECT * FROM Orders into outfile'/home/codio/workspace/ quantigrationupdates6_1.csv' FIELDS TERMINATED BY',' LINES TERMINATED BY '\r\n';