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 | |
Bert | Wachsmuth | (201) 761-9000 x5164 | wachsmut@shu.edu |
Silke | von der Emde | vonderemde@vassar.edu | |
Leah | Wachsmuth |
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. |
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 |
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.