Install DBMS Assignment

 

The SQL test web page is good for testing queries. However, since one database is shared by all, it is not such a good place for modifications. Therefore we will install a database and SQL client on each computer. In this way each student will be able to create tables, insert, delete and modify rows. In this case we will use Microsoft's SQL Server Express for the DBMS. A personal version can be downloaded from the Microsoft web site. We will also use the Squirrel SQL client, which is an open-source item that we will also download and install.

There are several steps that to be performed.

Install and configure SQL Server Express.

• Download SQL Server Express from the Microsoft web site. This is a free program, designed for developers. Near the bottom of the page you may download or register and download. You may do either. You only need Express, but you may load the tools and advanced services if you want.
• Execute the install program for Express after you download it. You may accept all the defaults. When you get to the Authentication mode dialog leave using Windows authentication checked.
• In your Programs menu you should now be able to find Microsoft SQL Server. Start the configuration manager.
• Observe under SQL Server Services that Express is running.
• Open the SQL Server Network Configuration. The only protocol enabled should be Shared Memory. Right click TCP/IP and click Enable. It should say that you are to stop and start the service. Instead we will do a full restart in next step.

Since Vista is so fragile, it is time to do a Restart.

We will now create an ODBC connection to this database.

• Open Control Panel then Administrative Tools.
• Open Data Sources (ODBC).
• Click the System DSN tab and click Add.
• Near the bottom of the list is SQL Server, click this and the Finish button.
• You will be prompted for a name, description and which server. Use LocalServer as your name, which will be used elsewhere. The description does not matter. Select your machine name \ SQLEXPRESS from the server list. Click next on this dialog.
• The Windows NT authentication may be used. Click client configuration and make sure TCP/IP is checked. Click OK. Make sure the bottom check box: Connect to SQL Server... is checked and click Next.
• Keeping clicking Next until you get to final dialog. Click Test Data Source and if that passes then OK and finish.
• The System DSN tab should then have one entry in it. Click OK, ODBC is ready.

You should download the Squirrel SQL Client. This is an open-source package maintained at Source Forge. You should download the install jar files. You may put it anywhere.

Execute the downloaded file. When it asks for plugins, check Standard and Microsoft SQL Server

We will now try to configure Squirrel to communicate with SQL Server.

• Start the Squirrel client.
• On the left it should show a list of clients. Scroll down to JDBC ODBC Bridge and double click.
• It will show the Change Driver dialog. Change the Example URL to:
  jdbc:odbc:LocalServer
  and click OK.
• Click on the menu Aliases/Connect.
• Use your login and password and click Connect.

We should change the SQL Server Service start type to manual. If we do not SQL Server will always be running, consuming both memory and CPU.

• Start Control Panel and Administrative Tools.
• Open Services from the list.
• Scroll down to SQL Server and right click. Select Properties.
• Change Manual instead of Automatic.

You should now attempt to use Squirrel to create the tables and populate them.

• Download the following:
  A zip file with several SQL files in it.
• You will need to modify first.sql to use a directory structure on your disk.
• If there are any Drops in the SQL then you should delete them.
• You will also need to consider the order in how the tables should be added.
• You should check your database with queries of the form:
  select count(*) from course
  for each of the tables. Check your table counts with that on the web.

Once your data is loaded, run some of your previous queries with Squirrel.

After everything works correctly you may delete the Squirrel downloaded jar file.