iLab 1, Part a: Using action queries in SQL
You have been requested to import a comma delimited text file into an existing SQL 2008 database. Using the SQL 2008 Import
Utility, you will create field mappings for a new SQL 2008 table and then upload your text file into this new table. After you
upload your text file, you will perform some action queries to add, delete, and modify data in your new table.
Upon completing this lab, you will be able to:
Import text file data into an existing SQL 2008 database and create a 1. new SQL 2008 table.
2. Use action queries to add, delete, and modify data.
Submit the YourName_Lab1A_Questions.docx to the Week 1 iLab Dropbox.
Section Deliverable Points
Part A Import comma delimited text file. 6
Part B Review uploaded data. 6
Part C Add records into a table. 6
Part D Delete records in a table. 6
Part E Modify records in a table. 6
Download the Lab1A_Questions.docx from Doc Sharing. You will answer the questions and provide screen prints as
required for each part of the iLab.
1.
2. Create a folder on your C drive named Transfer.
Print
Submit your assignment to the Dropbox located on the silver tab at the top of this
page.
(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)
i L A B O V E R V I E W
Scenario and Summary
Deliverables
i L A B S T E P S
Preparation Back to Top
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
1 of 20 9/7/2014 3:07 PM
Download the CustomerData.txt file from Doc Sharing to your folder 3. named Transfer.
Login to the Citrix remote lab.
a. Follow the login instructions located in the iLab tab in Course Home.
Upload your txt file to your Citrix drive using the instructions provided in the iLab tab in Course Home. Pay
particular attention to the Saving from Citrix video.
b.
4.
Part A: Import CustomerData.txt File into a SQL 2008 Database
Import a comma delimited file into an existing SQL 2008 database. You will need to include the column headers, map the
comma delimited data to an appropriate data type, and then upload the data into a new table in a pre-existing SQL 2008
database. If at any point you receive a Client File Security pop up window, select Full Access and then click OK.
Open your Citrix Laboratory, click the SQL 2008 Import Utility and then click Next to get to the Choose a Data Source
screen.
a.
b. On the Choose a Data Source screen select from the Data Source pull down the Flat File Source.
Click the Browse button and navigate to your Transfer folder, select your CustomerData text file and then click Open to
select your CustomerData.txt file for the File Name.
c.
d. Check Column names in the first data row on the Choose a Data Source screen.
e. Your screen should look similar to the following:
STEP 1: Problem Description Back to Top
STEP 2: Label Title Back to Top
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
2 of 20 9/7/2014 3:07 PM
Click Next twice to get to the Choose f. A Destination screen.
g. Leave the default SQL Server Native Client 10.0 for Destination.
h. Insert into the Server Name combo box your server: BIS445SQL2008
In the Database combo box area select BIS445_CoffeeMerchant for your Database. You do not have the access rights
in the Citrix Lab area to create a New database.
i.
j. Your screen should look similar to the following:
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
3 of 20 9/7/2014 3:07 PM
Click Next to get to the Select Source Tables k. and Views screen.
Click Edit Mappings on the Select Source Tables and Views screen. Change the Type column to get more meaningful
SQL field types.
l.
Note: This is only necessary for first-time use. If this is not your first-time through this process simply click Next.
m. Your field type selections should look similar to the following:
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
4 of 20 9/7/2014 3:07 PM
Click OK on the Column Mappings screen n. and then click Next.
o. Scroll down in the Review Data Type Mapping screen until you see for the Source column CreditLimit.
Scroll to the right until you see the On Error Column. Click in the On Error cell for CreditLimit to activate the combo box
and then select Ignore.
p.
Click the next cell to the right, On Truncation column in the CreditLimit cell, and change the combo box entry to
ignore.
q.
r. Your Review Data Type Mapping screen should look like the following:
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
5 of 20 9/7/2014 3:07 PM
Click Next twice and then Finish until you see the end of the SQL s. 2008 Import wizard.
Your results at the end of the import wizard should look similar to this.
STEP 3: Sample Output Back to Top
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
6 of 20 9/7/2014 3:07 PM
Using the Lab1A_Questions, answer the questions for Part A and paste a copy of your import wizard execution results.
Click the Close button to end your import wizard.
Part B: Review uploaded data
After importing your comma delimited text file into a new temporary table in an existing SQL 2008 database, you will need to
create new dimension tables and a fact table.
Click the SQL 2008 Server a. Management Studio.
b. Select Full Access for the Citrix File Security screen as follows:
STEP 4: Deliverable Back to Top
Problem Description Back to Top
STEP 2: Create the dimension and fact tables for your star schema data warehouse. Back to Top
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
7 of 20 9/7/2014 3:07 PM
On the Connect to Server screen, Server type should be Database Engine, Server Name should be BIS445SQL2008,
Authentication is Windows Authentication.
c.
Click Connect to see the Object Explorer in your Microsoft SQL Server Management d. Studio area.
e. Expand your Databases node, the BIS445_CoffeeMerchant node, and then the Tables node.
f. You should see a new CustomerData table in your Tables node area.
g. Right click your dbo.CustomerData node and select Design.
h. Inspect your table for field types.
i. Create a new Project.
j. Create a new SQL Query.
Create a SQL query using the following to show your uploaded data. You will need to insert the appropriate script in the
‘??’ position to make the query run:
k.
l. Save your query as SQLQuery1A-B and execute your SQL script.
m. Take a screen shot of your query results.
Your screen shot should look similar to this:
STEP 3: Sample Output Back to Top
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
8 of 20 9/7/2014 3:07 PM
Using the Lab1A_Questions, answer the questions for Part B.
Part C – Use action queries to insert data into a SQL 2008 table.
After reviewing your uploaded data, you will need to add records to your new table.
Create a new SQL script to insert the following information into the 1. CustomerData table.
2. Save the SQL as SQLQuery1A-C. Execute the SQL Statement.
3. Take a screen shot of your query results.
STEP 4: Deliverable Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Write a SQL query to insert a record into your CustomerData table. Back to Top
ConsumerID: 45678
FirstName: Mary
LastName: Alvarez
Street: 123 A Street
City: Los Angeles
State: CA
Zipcode: 91234
Phone: (123) 456-7890
Fax: (987) 654-3210
CreditLimit: 500
Your new script should look similar to the following:
Step 3: Sample Output Back to Top
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
9 of 20 9/7/2014 3:07 PM
Your results should look similar to this:
Using the Lab1A_Questions, answer the questions for Part C.
Part D – Use SQL Statements to delete data from a SQL 2008 table
Your CustomerData table needs to be maintained so you have been asked to delete some records based on different criteria.
You have been asked to write a SQL statement that will delete a. ConsumerID 45678.
b. Save your SQL query as SQLQuery1D1 and execute the statement.
c. Create a SQL statement that will delete all records that are from the state of California or Texas.
d. Save you SQL query as SQLQuery1D2 and execute the statement.
e. Make a note of many records that were deleted.
Write 1 SQL query that will prove that no records exist for ConsumerID 45678 or the Consumer is from California or
Texas.
f.
g. Save you SQL query as SQLQuery1D3 and execute the statement.
h. Take a screen shot of your query results.
Your results should look similar to this:
Step 4: Deliverable Back to Top
STEP 1: Problem Description Back to Top
Step 2: Enhance SQL script and execute it Back to Top
STEP 3: Sample Output Back to Top
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
10 of 20 9/7/2014 3:07 PM
Using the Lab1A_Questions, answer the questions for Part D.
Part E: Use Update queries
You need to update information in your CustomerDepartment table.
Write an SQL statement to change ConsumerID 30164’s City to Darlington and a. Zipcode to 19111.
b. Save it as SQLQuery1E1 and execute the SQL Statement.
c. Write an SQL statement to change Lynn H. Crosley’s Phone to (322) 654-8524 and her Credit Limit to 2000.
d. Save it as SQLQuery1E2 and execute the SQL Statement.
e. Write a SQL statement to show the results of your Update queries.
f. Save it as SQLQuery1E3 and execute the SQL Statement.
g. Take a screen shot of your results.
Your results should look similar to this:
Using the Lab1A_Questions, answer the questions for Part E.
Step 4: Deliverable Back to Top
STEP 1: Problem Description Back to Top
Step 2: Create a SQL script to modify data and execute it Back to Top
STEP 3: Sample Output Back to Top
Step 4: Deliverable Back to Top
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
11 of 20 9/7/2014 3:07 PM
Submit your completed YourName_Lab1A_Questions.docx to the Week 1 iLab Dropbox.
iLab 1, Part b: Creating a Star Schema for a Data Warehouse
You are a systems analyst working for an industrial supply company. To analyze orders, vendors, and products, you decide to
develop a data warehouse. Based upon the requirements for this data warehouse, you will need to design the fact and
dimension tables in Visio.
The fact and dimension tables are the core of the data warehouse. Facts store the data that is used by the business for analysis
and decision-making. The dimensions contain the related records which qualify the fact data. In order to determine which vendor
or product name is associated with a specific order, we must reference the dimensions. As you can see in the diagram below,
FactOrders, contain all of the order specific data:
Submit the YourName_Lab1B_Questions.docx to the Week 1 iLab Dropbox.
Section Deliverable Points
Part A Design Dimension tables and Fact table for Star Schema. 10
Part B Create relations between your Fact table and Dimension tables. 10
Part C Create a University Star Schema based on user requirements. 10
Final Deliverable Back to Top
Submit your assignment to the Dropbox located on the silver tab at the top of this
page.
(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)
i L A B O V E R V I E W
Scenario and Summary
Deliverables
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
12 of 20 9/7/2014 3:07 PM
Download the Lab1B_Questions.docx from Doc Sharing. You will answer the questions and provide screen prints as
required for each part of the lab.
1.
Launch MS Visio 2010.
You can complete this lab on your home computer if you a. have MS Visio 2010.
If you don’t have MS Visio, you can complete this lab on our remote server. Login to the Citrix remote lab from the
iLab Tab in Course Home. Follow the login instructions located in the iLab tab in Course Home.
b.
2.
Part A: Design Dimension and Fact Tables for the Star Schema in Visio 2010
Design a data warehouse system for analyzing orders, vendors, and products for an industrial supply company. Using Visio
2010, you will create fact and dimension tables for a Star Schema.
a. Launch MS Visio 2010.
b. In the Template Categories area select Software and Database.
c. Double click on the Database Model Diagram.
d. You might want to click on the zoom window to increase the size of the zoom to 100 percent to see your diagram better:
At this time in the project we are going to create the dimension tables for the star schema starting with the DimCustomer
table.
e.
Drag an Entity object from the stencil located to the left of your screen to your page. A properties window should open up
at the bottom of the screen. Select the definition category and in the Physical name textbox, type DimCustomer into
the Physical name textbox. Do not try to type the name into the Entity itself, just use the Database Properties area.
f.
i L A B S T E P S
Preparation Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Label Title Back to Top
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
13 of 20 9/7/2014 3:07 PM
After naming the table, click on the Columns category and type the column names listed in the screen shot below,
including the Req’d and PK checkboxes for the CustomerID:
g.
Repeat steps f and g for the DimTime, DimVendor, and DimProducts tables using the properties window to add the Table
name and the associated columns. Make sure you identify the primary key for each table. When you finish your dimension
tables, your diagram should look like the following:
h.
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
14 of 20 9/7/2014 3:07 PM
After you create your dimension tables, you will need to create your fact table. Add an entity to your diagram and name it
FactOrders. Give this table a primary key named OrderSurrogateKey.
i.
When you finish your fact table and dimension tables, your diagram should look like the following:
Using the Lab1B_Questions, answer the questions for Part A and paste a copy of your design of the dimension tables and fact
STEP 3: Sample Output Back to Top
STEP 4: Deliverable Back to Top
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
15 of 20 9/7/2014 3:07 PM
table results.
Part B: Link All of the Dimension Tables to Your Fact Table.
At this stage we need to link up all the dimension tables to the FactOrders table.
Remember in Visio this rule: NEVER TYPE A FOREIGN KEY INTO VISIO. USE A RELATIONSHIP LINE TO DO THIS
AUTOMATICALLY. We will use this rule to link the DimProduct table to the FactOrders table. It is an important rule because it
automatically labels foreign keys with an “FK” label and it ensures consistency in column names.
Drag a relationship object from the stencil at the left of the screen and drop the relationship in an empty area on your
page grid. In the following screen shot the relation is circled:
a.
Pick up the relation line and place the arrowhead end on the ProductID column of the DimProduct table. Make sure it
lights up the entire DimProduct table in red BEFORE you drop it on the DimProduct table. See screenshot below:
b.
Problem Description Back to Top
STEP 2: Create the dimension and fact tables for your star schema data warehouse. Back to Top
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
16 of 20 9/7/2014 3:07 PM
After you release your relation arrowhead, the DimProduct area of your diagram should look c. like the following:
Now you need to pick up the non-arrowhead end of the relation line and drop it on the primary key of the FactOrders
table (OrderSurrogateKey). The FactOrders table should light up red just like the process you used to connect the
arrowhead to the DimProducts table. Notice also that a ProductID FK (foreign key) now appears in the FactOrders table
as shown below:
d.
Do steps a through d over again for each of the DimTime, DimVendor, and DimCustomer tables. Make sure that your
tables light up red before you drop your relation lines and be certain NOT to type your foreign keys into the column
category of the FactOrders properties window. When you are finished, your database schema should look like this:
e.
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
17 of 20 9/7/2014 3:07 PM
The final step is to add the NON-KEY columns to the FactOrders table. These are the basic columns for quantity,
price, and sales total. They are called non-key columns because they are not primary or foreign keys. Select the
FactOrders table and then the columns property and add the fields shown in the schema below:
f.
Congratulations! You have just completed your first schema in g. Microsoft Visio 2010.
Your final star schema should look similar to this:
STEP 3: Sample Output Back to Top
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
18 of 20 9/7/2014 3:07 PM
Using the Lab1B_Questions, answer the questions for Part B.
Part C – Create new star schema.
After completing the above star schema, you will create a new star schema for a university from scratch. You will be given some
specifications, and then you will create on a new page in your previous Visio project a University Star Schema.
Add a new page to your current Visio project by clicking on Insert -> New Page a. and then Select OK.
On your new page create a university star schema based on the following information:
Tables as follows:
b.
Table Type Primary Key Non-Key Column(s)
factStudentTable Fact StudentSurrogateKey GPA
CoursesCompleted
CoursesWithdrawnFrom
dimProgram Dimension Program_ID Format_Description
dimAdvisor Dimension AdvisorID Advisor_LastName
Advisor_FirstName
dimFormat Dimension FormatID Format_Description
All Dimension Tables must participate in a relationship with the Fact Table. You are responsible for making sure a
Foreign Key for each Dimension Table appears in the Fact Table.
STEP 4: Deliverable Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Create a new star schema based on certain user requirements. Back to Top
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
19 of 20 9/7/2014 3:07 PM
When finished, your star schema should look like this, except your tables should be named with the appropriate fact and dim
prefixes. It appears upside down so you don’t look at it immediately. Try to create the solution yourself and then check your work
against the one below:
Using the Lab1B_Questions, answer the questions for Part C.
Submit your completed YourName_Lab1B_Questions.docx to the Week 1 iLab Dropbox.
Step 3: Sample Output Back to Top
Step 4: Deliverable Back to Top
Final Deliverable Back to Top
Submit your assignment to the Dropbox located on the silver tab at the top of this
page.
(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)
iLab https://devry.equella.ecollege.com/file/a30186d7-8d7b-436e-82d8-cda7f…
20 of 20 9/7/2014 3:07 PM