Docstoc

Labs

Document Sample
Labs Powered By Docstoc
					Labs
                             Lab 1.1: Explore SQL Server 2005

What is the purpose?
This lab will familiarize you with SQL Server Management Studio and common database
components. You will use SQL Server Management Studio to explore a sample database and
answer questions about Management Studio and the database. You will need to turn in a
Word document that contains the answers to the questions asked in this lab. To run this lab,
you will have to use Microsoft Virtual PC to launch an instance of SQL Server 2005 where
you have full administrative rights.

What are the steps?
• Task 1: Launch SQL Server Management Studio inside Microsoft Virtual PC, and
then navigate to a sample database.

                                          Procedure

To bring up the Virtual XP system and launch SQL Server 2005, perform the following steps:

1. On the hosting system, go to the Start menu, locate Microsoft Virtual PC, and open it. If
ITT-Lab is already showing in the left panel, omit Steps 2-10 and jump to Step 11 directly.

2. Click New in the Virtual PC Console and Next in the New Virtual Machine Wizard.

3. Select Add an existing virtual machine in the Options dialogue box.

4. In the Existing Virtual Machine Name and Location, browse to Local Disk C:/ Virtual
Mchines and select ITT-Lab.vmc and Open. The path will show in the Name and location
box. Click Next to proceed.

5. Click Finish in the next dialog box.

6. ITT-Lab will show in the Virtual PC Console and the Settings pane for ITT-Lab should be
open. If the Settings pane is not open, click the Settings button in the Virtual PC Console.

7. If the previous step does not work, you may use Create a New Virtual Machine option
by mapping to the ITT-Lab.vhd, and the Virtual PC Console will create a new .vmc instance
on your local machine.

8. In the Settings for ITT-Lab pane, click on Undo Disks and ensure this option is Disabled.
This step is critical in preventing any changes made by the user from being saved in order to
retain the original integrity of the virtual image.

9. In the Settings for ITT-Lab pane, click on Networking and select the network adapter
models on the right-side pane that match the actual NIC installed on the physical machine.
This step is critical to ensuring the networking function of the guest system.

10. Click OK to quit Settings.
11. Boot the Virtual XP (either by double-clicking the ITT-Lab in Virtual PC Console or by
highlighting it, and click the Start button in the console). The guest system will boot as a
full-blown OS and has the built-in admin rights for the user.

12. When the Virtual PC is being used, it is critical that all other applications on the hosting
system are closed to ensure maximum system resources are available for the guest system.

13. Click Start All Programs Microsoft SQL Server 2005 SQL Server
Management Studio Express.
Question A: What type of authentication is used by default?

14. When prompted to connect to the server, click Connect.

15. Expand your computer, expand Databases, and expand AdventureWorks.

• Task 2: View and identify database components.

Procedure

1. Expand Tables.

Question B: What does each database table represent?
 2. Expand HumanResources Employee and select Columns. A primary key is represented
     as a gold-colored key icon. A foreign key is represented as a silver-colored key icon.
Question C: What is the table’s primary key?
3. Explore several other tables to see their primary and foreign keys.

• Task 3: View table data.

Procedure
1. Right-click HumanResources.Employee and click Open Table.
               2. Scroll horizontally through the table to view the table columns.
3. Click Home to return to the first column. Attempt to change the value.
                Question D: What happens when you try to change the value?

4. Exit SQL Server Management Studio. To turn off the guest system, follow the “graceful
shutdown”" procedure as you would with any Windows XP system.

Did it work?

           Were you able to locate AdventureWorks entities?
           Were you able to verify that AdventureWorks is a relational database? How?
           Did you submit your written answers in a Word document using 12-pt. Times
            New Roman font to your instructor for grading?
Labs
Lab 2.1: Entity and Referential Integrity
What is the purpose?
These lab exercises will help you apply concepts of entity and referential integrity in
resolving database design problems. It is important to get practice applying these concepts
before implementing them in software and getting used to resolving technical problems in
teams as this is how they are resolved in the field. In teams of two to three students,
formulate solutions to the various database design problems below. Your deliverable will be
a document with your solution to each scenario below. To keep everyone focused on
resolving the design issues in question, hand-drawn diagrams are adequate for this exercise.
What are the steps?

• Task 1:
Procedure
1. Read Practice question 1 on page 27 of the textbook.
2. As a team, discuss possible solutions.
3. Document your selected solution and explain your choice.

• Task 2:
Procedure
1. Read Practice question 2 on page 27 of the textbook.
2. As a team, discuss possible solutions.
3. Document your selected solution and explain your choice.

• Task 3:
Procedure
1. Read Practice question 3 on page 28 of the textbook.
2. As a team, discuss possible solutions.
3. Document your selected solution and explain your choice.

• Task 4:
Procedure
1. Read Practice question 5 on page 29.
2. As a team, discuss possible solutions.
3. Document your selected solution and explain your choice.
Did it work?

• Were you able to determine which table was the many in the relationship in Practice
question 1?
    Were you able to determine where the associative table should be placed in the
       diagram in Practice question 2?
    Were you able to determine the columns necessary for a primary key in Practice
       question 3?
    Were you able to determine the integrity and referential violations in Practice
       question 5?
    Did you submit your written answers in a Word document using 12-pt. Times New
       Roman font to your instructor for grading?
Labs
Lab 3.1: Making an ERD
What is the purpose?
This lab will familiarize you with Microsoft Visio. You will through the steps of creating a
simple ERD using Visio and answer questions about the process. At the end of the lab, you
will need to turn in a Word document that contains the answers to the questions asked in this
lab and copies of the drawing files you created.
The steps given here are specific to Microsoft Visio 2003. Your procedures might vary
slightly if you are using a different Visio version.
What are the steps?

               • Task 1: Start an ERD drawing.

Procedure
1. Click Start All Programs Microsoft Office Microsoft Office Visio. This
assumes that Visio was installed as part of Microsoft Office.
2. Select Database in the Category list.
3. Under Templates, select Database Model Diagram. If you are given the option, select (US
units).
Question A: What Entity Relationship shapes are available?

               • Task 2: Add an entity to the ERD drawing.

Procedure
1. Drag an entity shape to your drawing.
2. Type Person as the Physical Name.
Question B: What else happens when you type the Physical Name?
3. Click Columns in the Categories list.
4. Type ID in the Physical Name column.
Question C: What Data Type is entered by default?
5. Check PK.
6. Click Add.
7. Repeat Steps 4 through 6 to add the following columns:

Table 2-1: Person columns


Physical         Data Type         Req’d             PK
Name
PersonIF         CHAR(10)          Checked           Checked
FirstNam         VARCHAR(2         Checked           Not checked
                 0)
LastName         VARCHAR(2         Checked           Not checked
                 0)
Sex              BIT               Not checked       Not checked
BirthDate        DATETIME          Checked           Not checked
8. Click Add.
Question D: How can a single bit represent sex?
Question E: How else might this value be stored?

                • Task 3: Add a second entity.

Procedure
1. Drag an entity shape to your drawing.
2. Type Place as the Physical Name.
3. Click Columns in the Categories list.
4. Enter the following columns:
Table 2-2: Place entity

Physical           Data Type          Req’d              PK
Name
AddressID          CHAR(10)           Checked            Checked
PersonID           CHAR(10)           Checked            Not checked
Address1           VARCHAR(40         Not checked        Not checked
                   )
Address2           VARCHAR(40         Not checked        Not checked
                   )
City               VARCHAR(40         Not checked        Not checked
                   )
State              CHAR(2)            Checked            Not checked

   Task 4: Add a relationship.
Procedure

    1. Drag a relationship so that one end points to the center of the Person entity and release.

Question F: How does the relationship indicate that it has been placed in the table?

    2. Click to grab the other end of the relationship, drag it to the center of the Place entity,
        and release.

Question G: How does this change the Place entity?

3. In the relationship properties, select ID in both column lists and click Disconnect.
4. Select ID in the Parent column list and PersonID in the Child column list.
5. Click Associate.
6. Select Miscellaneous in the Categories list.
7. Select Exactly one in the Cardinality list.

    • Task 5: Finish the ERD.
Procedure
1. Select the Place entity.
2. Locate the ID column in the entity column list.
3. Click Remove. Click in the drawing so that no drawing shapes are selected.
4. Click File Save.
5. Type FirstERD as the file name and click Save.
6. Click File Save as.
7. Select JPEG File Interchange Format from the Save as type list.
8. Click Save, then click OK.
9. Launch My Computer, navigate to the destination folder, and double-click FirstERD.jpg to
verify that the file opens.
10. Close the drawing file and close Visio.
Did it work?

• How is entity integrity enforced?

• How is referential integrity enforced?

• How can you visually identify which columns are required?

    • Did you submit your written answers in a Word document using 12-pt. Times New
          Roman font to your instructor for grading?
Labs
Lab 4.1: Normalization
What is the purpose?
This lab will help you recognize denormalized data and practice applying the first three
normal forms.
What are the steps?

   • Task 1: Scenario:

You are given the data shown below. You need to correctly normalize the data. An employee
is part of only one department. Each department has only one manager. Assume
EmployeeName is a single-valued field.




Procedure

1. Normalize the data to 3NF.

Question A: How many tables will be required? Why?
Question B: What relationship will you need to establish?
2. Create an ERD for the normalized data.
Question C: Did 2NF apply to during the process? Why or why not?

   • Task 2: Scenario:

You are given the data shown below. You need to correctly normalize the data. Each student
is identified by a Student ID number. Each class is identified with a course and session
number. The combination of course and session number is unique.
Procedure

   1. Normalize to 3NF.

Question D: What tables did you create?
Question E: Which tables, if any, have a multi-valued key?
2. Create an ERD for the normalized data.
Did it work?

• Were you able to normalize the data?

• Why is it important to normalize data?

• Why is a primary key important?

• Were you able to identify primary and foreign keys for relationships?

• Did you submit your written answers in a Word document using 12-pt. Times New Roman
    font to your instructor for grading?
Labs
Lab 5.1: Use Management Studio to Create Databases
What is the purpose?
This lab will let you practice the procedure for creating databases and tables. In particular
you will create the Hospital database in Practice question 1 on pages 143-145 of the text.
What are the steps?

                • Task 1: Use the Management Studio graphic interface to create a
                    database.

Procedure
1. Inside Virtual PC, launch SQL Server Management Studio Express and connect to your
local SQL Server instance.
2. Right-click Databases and click New Database.
3. Name the database Hospital.
Question A: What is the initial database size?

                4. Scroll and review the database physical file information.

Question B: What is the default file location for the database and log file?
5. Click OK to create the database.
6. Expand databases and verify that the database was created.
7. Expand Hospital, right-click Tables, and click New Table.
8. In the table Properties window, name the table Doctor. Tip: Click View | Properties
Window if the window is not displayed.
9. Create three columns named id, fname, lname, phone, and beeper. Set the data types and
lengths according to the details specified in the question.
Question C: By default, which columns allow NULL values?
10. Right-click id and click Set Primary Key.
11. Run File Save Doctor.
12. Repeat the above procedure to create the other tables in the Hospital database.
13. Using the procedure specified on pages 139-142 in the text, create the specified
relationships for the database. Use the Alt—PrintScreen key combination to paste a screen
shot of the Relationship diagram into a Word document. Print a copy to hand in.
14. Using the procedure specified on pages129-130 in the text, enter data into the database
tables as indicated on page 145 in the text. Paste another screen shot of the data in the tables
into a Word document to print and hand in.
15. Make sure you save the database—you will use it again in Unit 6.
Did it work?

• Were you able to create the database?
• Were you able to create the required tables?
• Were you able to create the proper primary keys?
• Were you able to create the proper foreign keys and relationships?
• Did you submit your written answers in a Word document using 12-pt. Times New Roman
font to your instructor for grading?
Labs
Lab 6.1: Using SQL Server 2005 to Query Data in a Database using SQL
What is the purpose?
This lab will further familiarize you with SQL Server 2005 Express Edition, SQL Server
Management Studio Express Edition, and the use of SQL scripts to conduct queries.
What are the steps?

               • Task 1: Create and run single-table SQL queries.

Procedure
1. Launch SQL Server Management Studio Express and connect to your local SQL Server
Express instance.
2. Select AdventureWorks in Object Explorer and click New Query.
3. Type the following query in Object Explorer:
USE AdventureWorks
Go
SELECT COUNT(*)
FROM HumanResources.Employee
Go

               4. Save the file to My Documents\EmployeeCount.sql

               5. Run the query.

               6. Open the table to verify that the number of rows is correct.

Question A: How many rows does the table contain?

7. Create and save the following two queries as EmployeeFilter1.sql and EmployeeFilter2.sql
    respectively then run them:

USE AdventureWorks
Go
SELECT *
FROM HumanResources.Employee
WHERE EmployeeID < 5
Go
USE AdventureWorks
Go
SELECT *
FROM HumanResources.Employee
WHERE EmployeeID <= 5
Go

Question B: How do the results differ?

Question C: Describe which columns are returned by the queries.
9. Execute a query to return EmployeeID, HireDate, and Title, in that order. Save query as
EmplyeeData.sql.
10. Modify the query to sort the results by HireDate, oldest hire first. Save query as
OldestHireSort.sql
11. Modify the query to sort the results by HireDate, newest hire first. Save query as
NewestHireSort.sql.
12. Execute the following queries and save them as CountVacHours.sql and
SumVacHours.sql:
USE Adventureworks
Go
SELECT COUNT(VacationHours)
FROM HumanResources.Employee
Go
USE Adventureworks
Go
SELECT SUM(VacationHours)
FROM HumanResources.Employee
Go

Question D: Why do the values returned differ?

• Task 2: Create and run multiple-table SQL queries using inner joins.

Procedure

1. Execute the following query and review the results:

SELECT FirstName, LastName
FROM Person.Contact, HumanResources.Employee
WHERE Person.Contact.ContactID =
HumanResources.Employee.ContactID
AND EmployeeID < 5

2. Execute the following query and review the results:
SELECT FirstName, LastName
FROM Person.Contact C, HumanResources.Employee E
WHERE C.ContactID = E.ContactID
AND EmployeeID < 5
Question E: How do the query results compare?
3. Execute the following query and review the results:
SELECT FirstName, LastName
FROM Person.Contact C, HumanResources.Employee E
WHERE C.ContactID = E.ContactID
AND LastName LIKE 'M'
Question F: What does the query return?
Did it work?
    • Were you able to create, run, and save single-table queries?
    • Were you able to create, run, and save multiple-table queries?
    • What are inner joins?
    • What are outer joins?
    • Did you submit your written answers in a Word document using 12-pt. Times New
    Roman font to your instructor for grading?
Labs
Lab 7.1: Using SQL Scripts
What is the purpose?
This lab will further familiarize you with SQL Server 2005 Express Edition, SQL Server
Management Studio Express Edition, and the use of SQL scripts.
What are the steps?

• Task 1: Create and save SQL scripts using Notepad.

Procedure
1. Launch My Computer and create a folder named TestScript in My Documents.
                                  2. Exit My Computer.
3. Launch Notepad.
4. Type the following in Notepad:
USE AdventureWorks
Go
CREATE TABLE dbo.TestTable(
First CHAR(10) NOT NULL PRIMARY KEY,
Second CHAR(20) NOT NULL,
Third INT NOT NULL)
Go
INSERT dbo.TestTable VALUES ('test one', 'test two', 42)
Go
5. Save the file to My Documents\TestScript1.sql

• Task 2: Load and run scripts.
Procedure
1. Launch SQL Server Management Studio Express Edition and connect to your local SQL
Server instance.
2. Run File Open File.
3. Navigate to C:\TestScript and select TestScript1.sql.
Question A: What happens in Management Studio when you open the script?
4. Execute the script.
Question B: What do you receive in the results pane?

• Task 3: Verify script results.
Procedure
1. In Management Studio, expand Databases, expand AdventureWorks, and then expand
Tables.
2. Locate and expand dbo.TestTable.
3. Expand columns. You should see the columns specified in the CREATE TABLE statement
in your script.
Did it work?
     Was the table created?
     Was the column named “First” set as the primary key? How can you tell?
     Were all of the columns specified in the script created?
     Did you submit your written answers in a Word document using 12-pt. Times New
        Roman font to your instructor for grading?
Labs
Lab 8.1 Data Mining, Data Warehousing, and XML
In this lab, you will investigate the importance of data mining, data warehousing, and XML
applications.
What are the steps?

       • Task 1: Data mining and data warehousing applications

Procedure

       1. Using the ITT Tech Virtual Library, research several vendor data mining and data
       warehousing applications.

   2. Given each application’s price and complexity level, state what type of organizations
       would most likely use it (large, medium, or small businesses).

       3. Identify any differences and similarities a data warehouse has with a traditional
       database. State any advantages or disadvantages of a data warehouse versus a
       traditional database.


       • Task 2: XML

Procedure

       1. Compare and contrast XML implementations with traditional databases; state any
       advantages or disadvantages.

Did it work?

   • Were you able to identify and evaluate different data mining and data warehouse
      applications?

       • Were you able to compare data warehouses with traditional databases?

   • Were you able to compare XML implementations with traditional databases?

       • Did you properly credit all sources from your research?

   • Did you submit your written answers in a Word document using 12-pt. Times New
       Roman font to your instructor for grading?

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:31
posted:8/19/2011
language:English
pages:13