next up previous contents index
Next: Object-oriented data model Up: Data model Previous: Relational data model

SQL

Relational algebra and calculus are formal data manipulation languages. As such they lack data definition constructs, and because of their formal nature, they are not well suited for interactive data manipulation. The language SQL ( Structured Query Language) is a relational database language which covers both data definition and manipulation and is close to English. It has become the de-facto language standard for relational DBMSs, and has been formally standardised by the ISO.

Example

The following example is a sample speaker and recording database schema implemented in SQL. First, relation tables are created using the data definition command CREATE with the appropriate arguments:

CREATE TABLE SPEAKER (
   ID, CHAR(8), PRIMARY KEY
   NAME, CHAR(20)
   FNAME, CHAR(20)
   SEX, CHAR(1)
   DBIRTH, DATE)

CREATE TABLE RECORDING (
   ID, DECIMAL, PRIMARY KEY
   RECDATE, DATE
   MEDIUM, CHAR(8)
   LOCATION, CHAR(20)
   SPK, CHAR(8)
   FOREIGN KEY SPK REFERENCES SPEAKER)

The data types available in SQL are restricted to very simple character or number types of fixed length. Bit-stream data and complex data structures are not supported.

Then queries are formulated using the data manipulation language of SQL:

SELECT S.ID, S.NAME, S.FNAME, R.RECDATE
FROM SPEAKER S, RECORDING R
WHERE S.DBIRTH > 12/27/60 AND R.SPK = S.ID

Besides being a database language for interactive database access, SQL has become, through its standardisation, increasingly popular as a programming language interface. External applications generate SQL code, which is then transmitted to the DBMS and evaluated there. The result relation is returned to the calling application for further processing (either as a whole or one tuple after the other with a cursor mechanism).

Most commercial relational DBMSs support SQL, but add their own extensions to overcome the limitations of the standard data types, e.g. with binary large objects (BLOBs) for image, audio or video data, or complex data structures for graphics objects.

Summary



next up previous contents index
Next: Object-oriented data model Up: Data model Previous: Relational data model

EAGLES SWLG SoftEdition, May 1997. Get the book...