QuantigrationRMA Database Documentation Walkthrough

Henderson: DAD 220 Module Three Major Activity Database Documentation Template 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 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 for assistance. Step One: 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: I first opened the session with the command mysql; I then created a new database schema called QuantigrationRMA. I listed out the databases using show databases; and then prompted the use of the new database so that I could create all of the new elements into the database. 2. Using the 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: To create the new Customers table, I used the command CREATE TABLE and then I typed out each of the attributes CustomerID, FirstName, LastName, Street, City, State, ZipCode, and Telephone and I told the code that I either wanted the listing to be an integer or a string of characters as well as how many characters for each attribute. I also made sure that the primary key was set in the CustomerID field and set it NOT NULL so that no other data within the CustomerID field could occupy the same information. After all the information was put in, I used the command describe Customers so that the table would generate. 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: Creating this table was nearly the same as the first with a few exceptions. As before, I used CREATE TABLE and named it Orders. I then typed all of the information. I made sure that I added the PRIMARY KEY command and set the OrderID to NOT NULL so that no other orders within the field would repeat. On the last step I needed to create the join command based on the Quantigration RMA Diagram. I did this by adding the FOREIGN KEY for CustomerID and used REFERENCES to let mysql know that the information would join and coincide with the attribute in the Customers(CustomerID) field. There are several ways I could have competed this step, but, to me, it seemed easier to input the command while creating the table instead risking an error and needing to go back and do it all over again. For the last step, I listed out the table with the command describe Orders; at which point I took the screenshot. 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: [ ] Just as in creating the first and second, I used the CREATE TABLE and named it RMA. I, again, made sure to set the primary key. I did the same with this table as I did the Orders table and used the FOREIGN KEY to join the information so that mysql would know where to reference the information. I had an error when describing the RMA table because I spelled describe incorrectly, but I fixed my error and it listed just fine. Using the diagram as a reference, I made sure that all of the tables and foreign keys were joined as they were supposed to according to the diagram. In both the second and last tables there were references to one and only one customers - zero to many orders and one and only one – zero to many RMA. This is the reason for the NOT NULL and FOREIGN KEY commands. It allows the joining of the tables and tells the database how many of what information can be tied to the next table and how they are all tied together. 3. Edit the provided SQL query to create a view from the existing table from “Customers” to “Collaborators” using SQL based on this change in requirements. Provide the updated SQL commands you completed and ran against MySQL to complete this successfully in your answer: This section gave me a lot of problems because the instructions as to what was expected were not clear. I noticed that there were references to Collaborator and Collaborators. I tried to delete what I thought I had messed up on, but realized that I was trying to describe Collaborators where there was only Collaborator. I know this sounds confusing, but I learned a very valuable lesson; all characters are 100% relevant and must be typed correctly. The process I took in completing this process is as follows. I first entered the information for 10 new customers. I choose to input them one by one to ensure that I did not generate an error. I then asked mysql to populate a table using all of the information from the Customers table. It was at this point that I began to have problems; however, I realized where and why the error was occurring and corrected my coding. Using the correct table name, I put in a command line to create the view for Collaborators as they reference CustomerID. I then told mysql that I wanted to import the given CustomerIDs, which I choose to do all of them, and then told it where I wanted it to pull the information from. Here are the commands I used: CREATE VIEW Collaborators AS -> SELECT CustomerID AS CollaboratorID, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109 -> FROM Customers; After I completed that step, I asked mysql to describe Collaborators and it populated the correct table with the correct information. As per the directions, I then told mysql to select only five customers within the Collaborators table, which it did properly.