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 ([email protected]). 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 – 32-bit – 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 213jsnU7 NotSoSecure IloveDatabases Insecure Password VeryInsecure (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