CS 530 Spring 2005

Installing MS SQL Server

Installing SQL Server from the CD is really straightforward (if you have one of the loaner laptops). Insert the CD. The installation should begin automatically. If it does not, run the file AUTORUN.exe. This will bring up a window like

Select SQL Server 2000 Components, then in the next (similar) window that appears select Install Database Server.

You are doing a completely standard installation, so for the next few windows simply accept the defaults and click Next in each window.

Eventually, you will be asked to specify an “administrator account” as follows:

Select Use the Local System Account and click Next.

The following screen should ask for an Authentication Mode:

Select Mixed Mode and enter a password for the sa account, which will be used to log in your database system to administer it. Click Next yet again.

At this point, installation of SQL Server will begin in earnest. Your machine will think for a few minutes, then bring up a dialog box saying the installation was successful.

Database Administration

Once SQL Server has been installed, you need to create databases and user accounts.

There are two client programs you can use for database administration:

• the Enterprise Manager, a GUI tool found in your Start menu under the (just-installed) Microsoft SQL Server group. This is the tool to use for one-off tasks like adding a new user or creating a database.
• the Query Analyzer, a command-line tool, also found in the Start menu under the Microsoft SQL Server group. The QA can run scripts, so it is the tool to use for repetitive tasks like bulk-loading data into a database.

Creating a Database

To create a new database, follow the tutorial at http://www.treadmark.net/lab/index.cfm (scroll down to the SQL Server link). The only difference will be that after you have opened

your screen should look like

When you select (local)(Windows XP) there will be some delay, reflecting the time it takes to start up and connect to the database server. Don’t worry about it.

Using the treadmark.net on-line tutorial as a guide, create a database called CS530DB0. This database will be used in the steps below.

Note: Microsoft SQL Server pre-installs some example databases, including NorthWind and msdb. Since this is your personal database system, you can experiment with these as much as you like.

Creating a Database User

To create a new database user, select

Right-click to get a context menu, and select New Login as shown:

This will bring up the following screen:

Enter the username as you like; for example CS530USR. Check the SQL Server Authentication radio button and enter the new user’s initial password. At the bottom, select the user’s default database.

Next, click the Database Access tab to reveal the following screen:

This screen is where you set user access rights for individual databases. Give the CS530USR user access rights to the CS530DB0 database in the db_owner rôle. At this point you might also want to experiment with giving the user different rights (e.g. read-only) to other databases (e.g. NorthWind and msdb).

Creating Tables

You can now create some tables in the CS530DB0 database. It is usually easiest to create tables using EM, as follows:

1. In the Enterprise Manager, open
   Microsoft SQL Servers  >  SQL Server Group  >  (local)(Windows XP)  >  Databases  >  CS330HW03DB

   and select Tables. You should see a list of the tables (most of them system-defined) that exist in your database.

2. Right-click Tables to get a context menu, and select New Table.
3. In the window that appears, you can enter the names and types of all the columns of the new table.
4. To create a (single-column) primary key, select the column, right click to get a context menu, and select Set Primary Key.
5. To give the new table a name, right-click to get a context menu and select Save. A dialog box will appear, in which you can enter the new table name.
6. The “New Table” window is now bound to the name you just assigned (even though it doesn’t say so anywhere–this is not the way to design a user interface, but …). Unless you want to continue making changes to the new table, you should immediately destroy this window.
7. A natural situation in which you do want to continue making changes to the table is defining a multi-column primary key (PK). Initially define the table with a single column PK. After you have named the table using Save, you can right-click and select Indexes/Keys from the context menu. The window that pops up allows you to add extra columns to the PK.

Executing SQL

The simplest way to execute SQL is to use the Query Analyzer (QA), found in the Start menu under the Microsoft SQL Server group. You will be asked to provide a database user name and password. When the main window appears, check that the desired database is the one selected (near the middle at the top of the window).

Use the Object Browser (left column) to explore (refresh your memory about) the database schema.

In the Query Window you can type a batch of one or more queries, each terminated by a semicolon (‘;’). These queries are not executed immediately when you hit RETURN, but are saved up and executed together (in a “batch”) when you choose

from the menu, or click the equivalent button on the Toolbar. Note this enables you to cut-and-paste large quantities of text into the Query window and edit it before execution. This feature is handy for bulk loading.

Definitive information about the “Transaction-SQL” language implemented by SQL Server is (of course) found at the Microsoft website: http://www.microsoft.com/sql/.

See also tutorial sites http://www.sqlcourse.com/ and http://www.sqlcourse2.com/.

Good luck!