DAD 220 Module Three Major Activity Database Documentation- Adam Mason (1)
docx
keyboard_arrow_up
School
The University of Oklahoma *
*We aren’t endorsed by this school
Course
122
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
6
Uploaded by BailiffIce6649
DAD 220 Module Three Major Activity Database Documentation- Adam Mason 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:
Commands used in order: use Mason, CREATE DATABASE QuantigrationRMA;, show databases;
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:
Commands used: CREATE TABLE Customers (
CustomerID INT NOT NULL PRIMARY KEY,
FirstName VARCHAR(25),
LastName VARCHAR(25),
Street VARCHAR(50),
City VARCHAR(50),
S
tate VARCHAR(25),
ZipCode VARCHAR(10),
Telephone VARCHAR(15));
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:
Commands used:
CREATE TABLE Orders ( OrderID INT NOT NULL PRIMARY KEY, CustomerID INT, SKU VARCHAR(20),
Description VARCHAR(50),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));
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:
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
Commands 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));
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.
Command used to insert Customer data:
INSERT INTO Customers VALUES (1001, ‘Jack’, ‘Marston’, ‘2222 Humbridge Rd’, ‘Houston’, ‘TX’, ‘22345’, ‘212-243-3454’),
(1002, ‘Jane’, ‘Marson’, ‘2323 Humbridge Rd’, ‘Austin’, ‘TX’, ‘24545’, ‘214-233-3784’),
(1003, ‘Jackson’ ,’White’ ,‘2572 Umbridge Rd’ ,’Rockport’, ‘TX’ , ‘22145’, ‘219-223-3490’),
(1004, ‘Jim’, ‘Horton’,‘3222 Balidge Rd’,’Harmony’,’TX’,’22328’,’263-249-3474’),
(1005,’James’,’Mason’,‘1998 Humbridge Dr’,’Homestead’,’TX’,’29635’,’212-243-3454’),
(1006,’Timothy’,’Toolidge’,‘2902 Ember Rd’,’Houston’,’TX’,’22345’,’212-243-3454’),
(1007,’Thomas’,’Hartgrove’,‘2272 Humbridge St’,’Houston’,’TX’,’22345’,’212-243-3454’),
(1008,’Jimmy’,’Buffet’,‘4622 Cheeseburger Rd’,’Margaritaville’,’TX’,’21946’,’212-852-3321’),
(1009,’Elvis’,’Kitridge’,‘2122 Washington Rd’,’Lousiville’,’TX’,’22789’,’252-253-3544’),
(1010,’Jackie’,’Welles’,‘2077 Corporate Rd’,’Night City’,’CA’,’12020’,’212-202-9454’);
Command used to see data: SELECT * FROM Customers;
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." Command used: CREATE VIEW Collaborators AS
SELECT CustomerID AS CollaboratorID, FirstName, LastName, Street, City, State, ZipCode, Telephone FROM Customers;
Command used: SELECT * FROM Collaborators LIMIT 5;
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