IT 330 Database Design and Management

Critical Read, Review Description, attached documents and Follow Instructions. Thoroughly discuss and answer all Questions and follow steps 1-4. Kindly use follow instructions in attached document steps 1-4 to complete the assignment. Apply Grade rubric.
STEP1.
4-1 Lab 4
Now that you have your design elements in place, start on the implementation phase. Refer to the attached Lab 4: Intro SQL document to complete this lab exercise.
1. Confirm the AdventureWorks2012 database is attached. If the database is not attached, follow these steps to place the database in the engine::
a. Create a new folder called DBs on the C: drive
b. Go to: http://msftdbprodsamples.codeplex.com/releases/view/55330
c. Locate and download the “AdventureWorks2012_Data.zip” file
d. Decompress the downloaded file. The will create a folder called “AdventureWorks2012_Data” that contains the following files:
1. <AdventureWorks2012_Data.mdf
2. AdventureWorks2012_log.ldf
e. Copy both of the above files into the following location: C:DB
f. Grant permissions for BOTH files to users following these instructions
o Write DML commands such as: INSERT, SELECT, COMMIT, UPDATE, ROLLBACK, DELETE
o Submit screenshots, SQL codes, and answers
For additional details, please refer to the Lab Rubric document in the Assignment Guidelines and Rubrics section of the course

Lab 4: Attach Database Write DML SQL
STEP2.
Objective:
• Confirm “AdventureWorks2012” database attachment
a. Attach the AdventureWorks2012 Database if it is not already attached
• Write DML commands such as:
o INSERT
o SELECT
o COMMIT
o UPDATE
o ROLLBACK
o DELETE
• Answer the questions
• Submit screenshots, SQL codes, and answers

STEP3.
Submission requirements:
• For all text and image submission(s), use MS Word, which is available to you within the Virtual Desktop Infrastructure (VDI).
• For all SQL code submission(s), use MS Word, which is available to you within VDI.
• For all diagram(s) submissions, use MS Visio, which is available to you within VDI.
o Note: If you need assistance on how to get started with this tool, go to the references section at the end of this document.
• If the submission is more than one file:
1. Name each item appropriately
a. For example: LAB2-ERD-yourName.vsd, LAB2-Questions-yourName.docx
2. Save each item in a single folder
3. This folder should also be named appropriately
a. For example: LAB2-yourName
4. Compress the folder
5. Submit the compressed file in Blackboard
STEP4.
LAB:
1. Confirm AdventureWorks2012 database has been attached. If the database is already attached, proceed to Step 2.

1a. if the AdventureWorks2012 database is not already attached:
i. Create a new folder called DBs on the C: drive
ii. Go to: http://msftdbprodsamples.codeplex.com/releases/view/55330
iii. Locate and download the “AdventureWorks2012_Data.zip” file
iv. Decompress the downloaded file. The will create a folder called “AdventureWorks2012_Data” that contains the following files:
a. AdventureWorks2012_Data.mdf
b. AdventureWorks2012_log.ldf
v. Copy both of the above files in the following location: C:DBs
vi. Grant permissions for BOTH files to users following these instructions
vii. Attaching the database
a. Start Microsoft SQL Server Management Studio
b. When prompted, connect to the database engine clicking the “Connect” button

c. Right-click on the database and select “Attach”

d. Click the “Add” button

e. Navigate to data set, locate and attach the database, and then click on the OK button
Location: C:DBs
Database name: AdventureWorks2012_Data.mdf

f. Confirm the database has been attached:
2. Write DML SQL queries and answer the questions:
a. Get the AdventureWorks Data Dictionary from
technet.microsoft.com/en-us/library/ms124438(v=sql.100).aspx
b. Use the SELECT and the WHERE clause to find the, Name, ProductNumber, and ReorderPoint, where the ProductID is 356
i. Submit the SQL statement used to accomplish this task
ii. Submit the value for the following fields:
? Name
? ProductNumber
? ReorderPoint
iii. How many record(s) were listed?

3. Create two tables called YourName_STORES and SALES. Use the data dictionary below to create these tables.

TABLE attribute data type Null? Key
YourName_STORES StoreCode char(5) Not Null PK
Name varchar(40) Not Null
Address varchar(40) Not Null
City varchar(20) Not Null
State char(2) Not Null
Zip char(5) Not Null

SALES OrderNumber varchar(20) Not Null PK
StoreCode char(5) Not Null FK (ref Table: YourName_STORES)
OrderDate date Not Null
Quantity int Not Null
Terms varchar(12) Not Null
TitleID int Not Null

a. Submit the SQL statements used to create these tables.
4. Add a record to the YourName_STORES table with the following information:

StoreCode = IT330, Name = Test_YourName, Address = 1234 Somewhere Street, City = Here, State = MA, Zip = 00333.

a. Submit the SQL statement used to accomplish this task.
b. Write an SQL statement to validate the record added in the previous step. Submit the SQL statement used to accomplish this task.

5. Add a record to the SALES table with the following information:
OrderNumber = TESTORDER, StoreCode = IT330, OrderDate = 01/01/2014, Quantity = 10, Terms = NET 30, TitleID = 1234567
a. What is the SQL statement you used to do this task?
b. Write a SQL statement to validate the record added in the previous step. Submit the SQL statement used to accomplish this task.

STEP5.
References:
The following is referenced from Microsoft:
Database Notations tap the full power of Visio
How to create a Crow’s Foot ER Diagram
1. Open Microsoft Visio
2. Click the File menu, select New, then Database, and then Crow’s Foot Database Notation

1. Drag and drop Shapes onto Drawing to create Diagram
2. Double-click Entities to adjust properties such as Name, Column, and Primary KeyDouble-click Relationships to adjust properties such as Name