SQL: Databases

views updated

SQL: Databases

Databases are designed, built, and populated with data for a specific purpose and for an intended group of users. Databases help people keep track of things. For example, one's school probably has a database with information about its students—name, identification number, address, year in school, grade point average, and so on. These data are used by the school administration to keep track of how many students are enrolled in the school and to plan how many and what kinds of classes are needed for these students. Databases like this one (called SCHOOL here) are designed, built, maintained, and queried using a set of tools called a database management system (DBMS). The most common language used with DBMS is called SQL.

SQL (Structured Query Language) is the set of instructions used to define and manipulate data in many relational databases . Some people pronounce SQL as "sequel," while others pronounce each letter separately. In the 1980s, SQL become the industry standard adopted by many database vendors including Oracle, Ingres, Informix, RDB, and Sybase.

SQL is used with a particular type of database called relational. Relational databases are easy to understand since they store data in one or more tables. Each table represents a different person, place, object, or event (entity) in a database. For example, in our SCHOOL database there are two tables: STUDENT, with data about our students, and TEACHERS, with data about teachers. Each table has one or more columns representing specific attributes (facts or fields) for each student or teacher. In the STUDENT table, we store student names, addresses, grade point averages (GPAs), class in school, and other related items. Each row in the STUDENT table represents a different student (called a record or tuple). If our school has 1,000 students, the STUDENT table will have 1,000 records. Figure 1 demonstrates an abbreviated set of attributes for one record in the STUDENT table.

SQL statements fall into two major categories: DDL (Data Definition Language) and DML (Data Manipulation Language). The DDL statements allow one to create, modify, or delete objects such as tables in a database, and they allow you to restrict access to the database by granting privileges. That is, the administration of a high school could allow students to see their class schedule while preventing them from changing their grades. DDL statements include create, insert, alter, drop, and grant. The SQL command that follows creates the STUDENT table with eight attributes (FNAME, LNAME, IDNO, BDATE, ADDRESS, GENDER, GPA, and CLASSNO). Attribute names are sometimes cryptic abbreviations of what they represent in the real world. For example, FNAME is a student's first name and LNAME is his or her last name.

CREATE TABLE STUDENT (FNAME VARCHAR(15) NOT NULL, LNAME VARCHAR(15) NOT NULL, IDNO CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), GENDER CHAR, GPA DECIMAL(6,2), CLASSNO INT NOT NULL, PRIMARY KEY (IDNO) FOREIGN KEY (CLASSNO) REFERENCES CLASS (CLASSNUMBER));

This SQL command creates a table in the SCHOOL database called STUDENT. Each attribute is given a name, a data type, and a size. This tells the database what to expect when we insert actual records into the database, for example, whether the data element is numeric or alphabetic and how much space should be reserved for it. Some of the attributes are labeled with "NOT NULL." This qualifier indicates the attribute must have a value when entered into the STUDENT table. For example, the database will not accept a student's record if the identification number (IDNO) is blank.

Right now the table would be empty; no data about students have been entered. The table can be populated by the SQL command insert as shown in the two statements that follow.

INSERT INTO STUDENT VALUES ('Eric', 'Mace', 123456789, 1991-8-23, '332 Cunningham Lane', 'M', 3.7, 3); INSERT INTO STUDENT VALUES ('Lyndsey', 'Vogler', 987654321, 1989-3-5, '104 Golf Club Drive', 'F', 3.8, 4);

Now our SCHOOL database has two records in the STUDENT table— one for Eric Mace and one for Lyndsey Vogler. The insert command is one of the DML commands that allow you to manipulate the data in the database. The most common SQL statements are insert, update, delete, and select. The following three statements show how to see the data stored in our database using the select command. We can ask for all attributes or just specific ones. In these examples, only the student's name (last name and first name) is requested.

SELECT LNAME, FNAME FROM STUDENT; SELECT

LNAME, FNAME FROM STUDENT ORDER BY LNAME; SELECT LNAME, FNAME FROM STUDENT WHERE LNAME

"Mace";

The first statement would list all students in the order they were inserted into the STUDENT table. The second statement sorts the students alphabetically by last name. The final statement retrieves only those records that match a last name of 'Mace'. Since we have just one record with that last name, only Eric Mace is retrieved. However, if we insert a record for Eric's brother, Daniel Mace, and resubmit the third SQL statement, two records will be retrieved.

Another useful SQL command is update. This command allows you to change data for a particular record. Suppose another student's GPA has changed. We must indicate the exact record to change (Jon Roberts), the attribute to change (GPA), and its new value (3.9).

UPDATE STUDENT SET GPA 3.9 WHERE LNAME 'Roberts' AND FNAME 'Jon';

Using these SQL commands, we can create a database with multiple tables, populate the database with records, modify the database, and most importantly, query the database to find the information stored in it. The classical database applications track items such as students, orders, customers, jobs, employees, or other items of interest to a business person. Many individuals who like organization create a database to track their CD or book collections, for example. In the 1990s, databases became more readily available and database technology was applied to multimedia (video and sound), geographic information systems (maps, satellite, and weather data), data warehouses (very large repositories of data from a wide range of sources), and Internet-based databases.

see also Client-Server Systems; Database Management Software; Operating Systems.

Terri L. Lenox and Charles R. Woratschek