SQL
SQL
Databases are designed, built, and populated with data for a specific purpose and for an intended group of users. Databases are built for many different users, including banks, hospitals, high schools, government agencies, and manufacturing companies. The data contained in databases vary and can include account, patient, student, employee, planning, or product data. Users of a database can add new records, insert or change data in existing records, retrieve data from existing records, or delete records from the database.
Databases are designed, built, maintained, and queried using a set of tools called a database management system (DBMS). Often, the DBMS is considered the user interface to the database system because everything else is invisible to the user. The DBMS defines the data in a database using the Data Definition Language (DDL) and handles requests to retrieve, update, or delete existing data or add new data to the database using the Data Manipulation Language (DML). In addition, the DBMS must monitor user requests and reject any attempts to violate integrity or security constraints defined for the data. The DBMS must be able to recover the data in case of problems. The DBMS also performs a function called concurrency control . Finally, the DBMS provides a tool called the data dictionary that stores data about the data. One language used with the DBMS to provide this functionality is called SQL. It is the industry standard adopted by many database vendors for relational databases.
Database architecture can be configured in one of three basic ways:
- Host-based, where users are connected directly to the same computer on which the database resides;
- Client/server, where a user accesses the data from a microcomputer (client ) via a network and the database sits on a separate computer (server );
- Distributed processing, where users access a database that resides on more than one computer. The database is distributed across these computers in various ways.
In the client/server architecture, the server supports all basic DBMS functions (data definition, data manipulation, data security, and integrity). The clients are the various applications that run on top of the DBMS. These applications are provided by the DBMS vendor or a third party to query the database, write reports, produce graphics and spreadsheets, and many other functions. The server machine in a client/server architecture can be tailored to the DBMS function and thus provide better performance. Typically, several clients share the same server.
When more than one person can access a database, several SQL commands can be given at the same time. Each of these commands is called a transaction. A transaction is simply a logical unit of work. Since many transactions can access the same database at the same time, some means of controlling them is necessary. This is called concurrency control. One way to understand the need for concurrency control is to consider what would happen if Emily and Christopher both wish to modify a particular record for Jon Vogler from the SCHOOL table at the same time. Emily wishes to change the grade point average (GPA) for Jon, and Christopher wishes to change Jon's address, as the two SQL commands that follow show.
UPDATE STUDENT SET GPA = 3.9 WHERE LNAME = 'Vogler' AND FNAME = 'Jon'; UPDATE STUDENT SET ADDRESS = '3 Soccer Lane' WHERE LNAME = 'Vogler' AND FNAME = 'Jon';
If both retrieve the record at approximately the same time but Emily updates the record before Christopher does, Emily's modifications are lost and only Christopher's changes are made to the record, so Jon's address changes, but his GPA does not. This situation is called the lost update problem and it can reduce the quality of the data in a database.
Locking is the most common method of concurrency control. When a transaction needs to access a part of the database (typically a database record), the DBMS locks other transactions out of that part. The first transaction can perform its processing without being affected by any other changes that might be made to the record. In our SCHOOL example, if Emily is the first to request Jon's record, Christopher is prevented from requesting that record until Emily finishes. This is a type of locking called exclusive locking. However, if another type of locking called shared locking is used for concurrency control, Christopher would be permitted to see Jon's record, but not change it.
see also Database Management Software; Information Systems; Storage Devices.
Terri L. Lenox and Charles R. Woratschek
Bibliography
Abbey, Michael, and Michael J. Corey. Oracle: A Beginner's Guide. Berkeley, CA: Osborne McGraw-Hill, 1995.
Date, Chris J. An Introduction to Database Systems. Reading, MA: Addison-Wesley, 2000.
Elmasri, Ramez A., and Shamkant B. Navathe. Fundamentals of Database Systems, 3rd ed. Reading, MA: Addison-Wesley, 2000.