Using SQL Server

SQL Server, as we all know, is a database management system. However, that may not be enough information to let you get started on the assignment.

So here's all the information you should need to get started.

Starting SQL Server

If you look at the SQL Server 7.0 menu accessible from the start menu, you'll see that there are a number of different programs that you can run, including SQL Server Enterprise Manager, and Query Analyzer. Since at the moment none of your databases have any data in them, the first thing that you'll want to do is open up the enterprise manager and make sure that the server that we're using for the project is registered (meaning that SQL Server knows to look for where your database is stored). The machine that we'll be using is named ISQL01 (that's zero one at the end), so if you see that in the list, you don't need to worry about this next step.

Registering ISQLO1

To register ISQL01, right click on "SQL Server Group", and then choose "New SQL Sever Registration". This will bring up a wizard. The wizard will tell you what the wizard can accomplish... click next. At this point you should see something like this: Type in ISQL01 in the list of available servers, and click "add" so that you see it in the list of added servers. Click next again.

The next window will ask you what authentication you want to use; you want to connect using the second option, SQL Server authentication. Click next and add it to the existing SQL Server group, click next again. Again, select the second option: Prompt for the SQL Server account information when connecting. Click next again. Add the ISQL01 (the SQL server) to a server group (the default is fine). Click next again, and click finish; this should end the registration process. You should now get a dialog saying "ISQL01 - Registered successfully"; click close - you are ready for the next step.

Accessing your database

Now that we've ensured that ISQL01 is registered, you need to open your your database. Click on the little plus symbol next to ISQL01. You should be prompted for a login name and password. Each group shares a login and password, listed here. Do not store this information for future sessions. After entering the correct login and password, you should see a list of folders under ISQL01. Click on the plus next to Databases, which will expand to show you all of the databases on the server (this could take a minute or two). Your group's database name is based on your group name, look here again for the exact names.

The Console window should now look something like: Chose your database; the name should be based upon your group name, but their may be some differences. You can check it on the Project groups page. So expand that by clicking on the plus symbol again (again, this may take a few minutes), and you'll see a list of things specific to your database including Diagrams, Tables, Views, etc. This is your database. :) Enjoy. Each group has 65 megs of space, so use it wisely. Keep in mind that you'll be using the database space for a few other homeworks as well, so don't go crazy importing the entire web.

Creating the homework database

Before you can write queries to retrieve data, you need to have data in your database. So the next thing to do is to create tables and "populate" them with data. We have made this easy for you by giving you all the create table and insert statements. All you have to do is cut and paste. Table creation and insertions are considered to be queries, just like select queries. So we go to the Query Analyser.

In the Console window, choose "Tools," "SQL Server Query Analyzer" It may ask you which database you want; type in ISQL01. You should also need to re-authenticate with the same login and password you used (i.e. the one you found here). Make sure that your group's database is the one showing in the DB window, (circled in red on the image) and use the green arrow (circled in green in the image) to run the query. The main window is where you enter your query.

To create the database, cut and paste this entire file into the main window. Click the green arrow. The results of the query are listed in the lower window. It should be a long series of (1 row(s) affected) messages.

Your database is now built. If you go back to the Enterprise Manager, you can expand tables and see the list of tables you just added. From the Enterprise Manager you can also see what the table's attributes are and what data is in the tables. Click on the "tables" and this will bring up the tables in the right panels. You'll notice that there are a number of tables there other than the ones that you've created; don't delete them, they're needed by SQL Server. To look at the data in a table, right click in a table, select "Open Table" and "Return All Rows."

To see what the attributes of a relation are, double click on the table.

Querying the database

To query the database, use the query analyser just as you did to create the tables. Enter your queries, and click the green arrow. To get started, you might try something simple like select * from department.