SQL Client - Server Databases - Brief Introduction

An SQL Database is a database program where you can use "Standardized Query Language (= SQL)" to manipulate your data. Before the SQL standard, you had to use different commands for each different database program you were planning to use (such as Paradox, DBase, etc). That would of course mean that if a company invested a lot of money for programmers to design their particular database system, (almost) all the work would have to be redone if another, perhaps more powerful database system, was to be used in the future.

If you use an SQL database system, you can instead design your database system using the "standardized query language". Then, if you switch to another SQL system, most of your programming would still work, since the query language remains mainly the same accross SQL systems from different vendors. Of course, the action behind the scences may be different when using a different system, but you do not usually need to be concerned with that.

For example, a particular SQL command is "CREATE". That command would create a new table in a database, regardless of which particular SQL system you are using. Some systems would of course be faster and better than others, but the SQL command "CREATE" will create a new table in all systems.

In addition, many SQL database system operate in a "client / server" fashion:

The SQL Server programming would be running on a large system with large and fast storage systems. The SQL server, like most servers, do not have a nice user interface. Instead, they understand the SQL language, which is pretty difficult to master for a novice.

The SQL Client, on the other hand, does not by itself have access to any data. Instead, it allows you to build a "query" using a nice graphical interface, translates that query into the appropriate SQL commands, connects to the SQL server, and passes the SQL command to that server. The server instead would process the command, find the data, and return it to the client. The client, then, would format the data appropriately, and display it in a nice format on the screen.

In addition, many SQL clients can connect to one SQL server. That allows you to easily share your data among many clients. In addition, if one client modifies the data, the modification is stored by the SQL server, and hence is (almost) immediately available to the other clients as well. That allows for a relatively easy multi-user database system.

All SQL database systems are "relational databases". We will not at all discuss the particulars of a relational database here. Instead, we will use very simple sample databases to understand the basic principles of SQL client server database systems.

One such SQL system is called "Mini SQL", or MSQL. It is a system that understands some, but not all, of the standard SQL query language, and is just powerful enough to be useful for simple situations such as ours. However, it is a system that is freely available for educational institutions, and can be downloaded from http://www.hughes.com.au.MSQL is a relation database system, but again we will not focus on the "relational" aspects at all.

MSQL - just as any other SQL system - stores data in "databases". Each database can contain one or more "tables". The tables contain the actual data in rows and columns. The columns describe the name and type of the data to be stored, while each row contains the actual information.

Example:
We want to create a simple address book, storing names, phone numbers, and email addresses of friends. Therefore, we setup a table named, perhaps, "addressbook", in some database that has been assigned to us by a database administrator. In that table, we define columns such as "fname", "lname", "phone", and "email". Then each row in the table will consist of four strings, as follows:
 
 

fname	lname	phone	email
Bert	Wachsmuth	(201) 761-9000 x5164	wachsmut@shu.edu
Silke	von der Emde		vonderemde@vassar.edu
Leah	Wachsmuth

Note that Silke has no phone number, and Leah has neither a phone number nor an email address - that's just fine. Once a table has been defined, data can be added, removed, modified, and searched for, using standard SQL commands.

I have setup one database called "csas4083" for our class on our machine sciris.shu.edu that can be accessed with a simple "msql client" program. The msql client program is very simple, and actually not so easy to use. Our goal will be to access a database not with the msql client, but with our own client written in the Java language.

Before doing that, however, you should try to use the "msql client" to create a simple database for yourself. Here's what you have to do:
 

	Telnet to sciris.shu.edu and login, as usual.
	Type: msql csas4083 to start the msql client.

The msql client will connect to the msql server and open the database "csas4083". That database already contains one table for me, named "wachsmut_address". Every command you issue in the "msql client" must be ended with "backslash g", i.e. with "\g" for "go". You can break a line anywhere you like; the command will only be passed to the msql server after you type "\g" and hit return. If you make an error, the msql client will let you know, and you have to reissue the entire command. To look at all rows in the table "wachsmut_address", you type (after you started the msql client as above) exactly the following:

select * from wachsmut_address \g

You should see several rows of data, formated not so nicely, but readable, with information. Add your own information to that database as follows:

insert into wachsmut_address values
('John', 
 'Smith', 
 '(123) 456-7890',
 'whatever@mymail.com') 
\g

Note that the lines are broken up for convenience. The command is only passed to the server when the "\g" followed by return is encountered. Of course you should use your real information instead of the above names and numbers. If you do not want to provide your phone number, leave it blank by including nothing inside quotes. In other words, to not provide your phone number, but everything else, use:

insert into wachsmut_address values
('John', 
 'Smith', 
 '', 
 'whatever@mymail.com') 
\g

Make extra-sure your quotes are all correct. If you receive an error message, try again. Once you don't receive an error message, but rather some confirmation message, take a look at the table again to see if it includes you data by typing, as before:

select * from wachsmut_address

Next, you should provide a table for yourself before trying to create a Java SQL client to manipulate your data. When using your own user name on Sciris, you will have the appropriate rights to create tables in the "csas4083" database. If you do create your own table, preface it with your username, then an understore, then a short name describing your table. Everybody else also has full access to that table, so make sure not to manipulate somebody else's table.

When you create a table, you need to specify the names of the fields, and the types for each field. Msql can handle only three types:

	char(number)  a field containing at most "number" characters
	int a field containing an integer number
	real a field contaiing a real number

To create a table that in turn will hold your own, personal address book, you use the command (once you have started the msql client as above):

create yourname_address 
(fname char(20), 
 lname char(40), 
 phone char(30), 
 email char(40)) 
\g

It will create a table named "yourname_address" in the csas4083 database with four fields, each of which can contain 20, 30, or 40characters, respectively. Next, add some data to your own database by typing, as above:

insert into yourname_address values
('John', 
 'Smith', 
 '', 
 'whatever@mymail.com') 
\g

Now take a look at your data by typing:

select * from yourname_address

Add a couple more rows, similar to above. Add some with all info, some with phone number, but no email, some with email, but no phone number, and some with neither email nor phone number.

For a summary of all SQL commands that are available for the MSQL server, please take a look at the "MSQL Commands" in the next section. Experiment with anything you like. Before issuing a command, telnet to sciris, login as usual, then type "msql csas4083" to start the "msql client". Make sure not to manipulate anybody else's table.

Next, we will learn how to write our own SQL client program in Java that makes it a lot easier to manipulate our data.

(bgw)