Technology Source

a monthly report on technology

Creating a database and integrating it with University data

Whether we are aware of it or not, we are constantly in contact with databases. A phone book is one of the simplest databases. An airline reservation system, on the other hand, is likely to be a very complex database. But what is a desktop database system? A desktop database system is a computer program that manages information and can run on a personal computer.

Modern database systems present information to the user in the form of tables, which are made up of horizontal rows and vertical columns. Rows are commonly called "records," whereas columns are called "fields." If we considered a hypothetical (and simplified) registrar's database, we could divide student information into three tables: "student address," "class roster," and "course location."

A record in the student address table would have the following fields: Social Security number (SSN), last name, first name, street, city, state and Zip code. A record in the class roster would have three fields: SSN, course ID and grade; and a record in the course location table would have three fields: course ID, building name and room number.

When the idea of organizing information in the form of tables was originally introduced, tables were called "relations," which is why software vendors refer to their database products as "relational" databases. Typical functions of a relational database system are: creating tables; creating, deleting and modifying records; and responding to queries (requests for information) issued by users. For example, a user might want to know the names of students who come from the state of Florida. The database system would translate this query into a search of the address table for those student records whose "state" field contains the word "FL." The result of this search would be presented as a table containing rows with two columns: First Name, Last Name.

Another important function of relational databases is to allow the user to indicate how tables are related to other tables. Two tables are related to each other when they share a given field. For instance, using the hypothetical registrar's database, the address table is related to the class roster because both contain SSN as a common field. By designing tables that share fields, a database administrator would be able to support queries involving multiple tables.

Database systems offer several attractive features:

* the ability to have fields containing multimedia elements, such as sound files or pictures;

* the ability to pass query results to other programs such as word processors or spreadsheets; and

* the ability to import tables created by other commercial database systems.

In fact, I received a few weeks ago a mailing list created on the database system DBASE, and my database system, ACCESS, was able to read it, freeing me from typing in 300 records.

The University maintains a relational database system, the Data Warehouse, which contains student information, personnel records and financial information. The Data Warehouse relieves departments from the task of duplicating some University information and from having to create their own databases. To access the Data Warehouse, a remote workstation needs a "client" program that can talk over the campus network with the Data Warehouse's "server" program. The server responds to queries submitted by clients by returning data in row and column format. Clients have the ability of passing the query results to spreadsheet or word processing programs if needed. The Data Warehouse offers training for departmental staff in the use of the "client."

More information on the Data Warehouse is available at: <>, and information on database products for sale is available at: <>.

Bernardo Gomez is a system developer in Computing Resource Services in the Information Technology Division.

Return to the May 6, 1996 contents page