CSSE333 Introduction to Databases Lab Assignment 1 Lab Assignment 1 Getting familiar with SQL Server Objective After completing this lab you should have SQL Server 2005 installed on your local machine Start becoming familiar with the Northwind and Pubs databases and be able to create tables views and database diagrams with SQL Server Management Studio Required Materials Laptop Assignment Details Task 1 Installing SQL Server 2005 Standard Edition Each of the Labs will require access to SQL Server 2005 Standard Edition software If you have already installed SQL Server 2005 Standard Edition skip to Task 2 Registering SQL Server Your instructor has distributed DVDs containing the software installer if you have not had the opportunity to install from these disks you can download the installer image from MSDNAA 1 Log on to MSDNAA website at http msdnaa cs rose hulman edu If you don t know your username and password email Mike McLeish mcleish cs rosehulman edu 2 Once you logged in click on the SOFTWARE button at the top left of the page 3 Use the Search by Product Titles to find SQL Server 2005 DVD and choose the download option Follow the installation instructions install only the client tools Please be sure to download SQL Server 2005 Standard Edition 32bit DVD Other versions and distributions may not work correctly with our database server and written lab instructions 4 Prior to installing SQL Server 2005 be sure to remove any existing database software from your computer Potential software packages include SQL Server 2000 SQL Server Express or MySQL Task 2 Registering SQL Server For this course you will need to register DYKNOW CS SQL Server with your copy of Management Studio 1 Launch SQL Server Management Studio 2 A dialog box should come up with information to fill out to connect to the server 3 Here is the information that you ll need to connect Server type Database Engine Server name DYKNOW CS Authentication mode SQL Server Login User name RHIT Kerberos Username Password RHIT Kerberos Username SQL Server Group Accept the default option 4 After completing the server registration wizard you will be prompted to reset your SQL Server account password You must change this password to receive full credit on this portion of the Lab CSSE333 Introduction to Databases Lab Assignment 1 5 If it does not ask for a password change immediately or if you wish to change it again in the future a Log in to SQL server in Management Studio b Open the SECURITY tree c Click on LOGINS d Double click your user name e Change password This requires you to type the new password twice as well as type the old password once Task 3 Verify Test Databases Once you have configured SQL Management Studio for use with the DYKNOW CS server you will see a number of databases listed in the server Before you proceed verify that you have two databases named NwindXyz62 and PubsXyz62 where Xyz is your user name and 62 stands for second quarter of the year 06 07 If you do not have one or both of these databases contact your instructor or TA for assistance The remainder of this lab will be done inside your personal copy of Northwind Task 4 Creating a Login Table Once you have your database up and running you need to start getting a feel for how SQL Management Studio works Many applications take a username and password and use a database for verification Make a table called Login with the following properties Create an integer column called LoginID which is a primary key Login ID s should automatically increment themselves and should be multiples of 5 If you get stuck look for the Identity field Create a variable length character column called Username Usernames cannot be blank and they cannot be longer than 15 characters Create a text column called Password Passwords can be left blank but the default value is badgerbadger Now enter the following usernames and passwords into your table One easy way to do this is to right click on the table and select Open table You can then type them in directly Username Password ReallySecure NotSoSecure Insecure VeryInsecure 213jsnU7 IloveDatabases Password leave blank SQL Server 2005 does not immediately show default values in new records You will need to Re run the query to show the automatic values Navigate through the Management Studio and find a way to Execute SQL Once you do this you will see updated values in the columns Feel free to ask for help if you get stuck CSSE333 Introduction to Databases Lab Assignment 1 Task 5 Creating a View Views enhance the readability of data by only displaying selected columns from tables We could of course just open the table s and look at the data but it can also be composed together in views Create a view named LoginView which displays the Username and Password You should not be able to see the LoginID in this view As you manipulate this new view you may notice text being composed in another panel in the Management Studio This text is the raw SQL code that generates the view do not worry about how the text works right now we will explore that later Task 6 Creating a Database Diagram Database diagrams are useful in visually representing the components of your database You will find that these diagrams can provide quick reference to the columns and constrains on the data in the database Please create a database diagram for your Northwinds database The first time you open the Database Diagrams folder you will be prompted to create support objects click the Yes button As with previous tasks you can quickly create a database diagram by right clicking Name this diagram DemoDatabaseDiagram From the subsequent dialog box select the following tables then click Add Tables CustomerCustomerDemo Customers Employees Orders OrderDetails Task 7 Examining Your Databases Take the next few minutes to become familiar with the Northwind and Pubs databases and the data they contain Be sure to look at the Tables Columns Views and Diagrams that have been provided by default If you have questions about any of the items within the database consult your instructor or TA Turn in Instructions This assignment will be graded in SQL Server and there is nothing extra to submit Revision History Dec 2 2004 Dec 6 2004 Nov 11 2004 Oct 23 2006 Oct 31 2006 Nov 29 2006 Created by Jennifer Ford Updated and section on views added by Jennifer Ford Updated by Curt Clifton Updated for SQL Server 2005 by Andrew Foltz Updated and revised Tasks by Bryan Musial Minor edits by Curt Clifton