The SQL Language : Principle Concepts

The most fundamental concept of SQL is the table. A table is a logical unit of data that has one or more columns, each of which has a name and a data type. Data in a table is stored in rows that have columns corresponding to those of the table. Each column of a table has a single data type for all
rows in that table. (A column in a row is sometimes called a “cell", though the SQL standard does not use or define that term.) Fig. 1 illustrates these concepts.

Figure 1. Illustration of Table concepts.

SQL provides a number of data types, broken into the categories of numeric, string, datetime, and others. Table 1 shows each category, the further breakdown of those categories, and the specific data types.  All data in an SQL database belongs to one of those data types, even if some data has the null value. The concept of null doesn’t have a data type itself, but the cell in which a null is stored always has one of the SQL data types.

In addition to representing data, SQL databases are self-describing; that is, besides the tables they contain that hold the application data, they contain tables with metadata Meta data that describes the tables in the database (and describing the tables containing the metadata). While the SQL standard doesn’t define the word “database”, it does define the words catalog and schema. A catalog is a  amed collection of schemas, including the special schema that contains the metadata for all objects in the catalog. A schema is a named collection of tables (and their columns), character sets, and other SQL-defined objects. Catalog names qualify schema names, allowing multiple schemas with the same name to exist in different catalogs; similarly, schema names qualify the names of tables and other objects, and table names qualify the names of columns. Qualified names are represented
by the various components of the name separated by periods. For example, the name of a table might be 

CATALOG3.MYSCHEMA.EMPLOYEES

Part of the power of SQL lies in the aids that it provides database and application designers. SQL databases can contain constraints, including:

• semantic integrity constraints that instruct the database system how to enforce business rules associated with the data stored in the database, and

• referential integrity constraints that tell the database system how to keep its data internally consistent when changes are made by applications.

If an application attempts to violate a semantic integrity constraint (for example, a rule that says “all salaries must be greater than 0”), then it is notified of the error and the statement attempting that violation is not executed. Attempted violations of some referential constraints (e.g., a rule prohibiting
elimination of departments having one or more employees) are handled similarly. However, referential constraints can be more sophisticated – a database designer might permit resignation of a project’s manager, but require the database to effect resolution of the status of the project. One design
could result in the project’s automatic deletion, while a second design might assign the project to someone responsible for “orphaned” projects, and a third design leaves the project in an unassigned state pending explicit action at a later time. Each of these designs results in automatic resolution
without execution of any additional SQL statements by the application.

A related feature, called triggers, allows a database designer to force the database system to take certain specific actions whenever certain tables are accessed in specified ways. For example, a trigger could be defined to add a row to a log table whenever changes are made to the salary column of an employee table, or to adjust the budgets for departments whenever new projects are assigned to them. Triggers can be arbitrarily complex and “intelligent” and their actions can cause additional triggers to be invoked. When rows are created in a table, an application programmer can choose to provide a value for every column in each created row; alternatively, some rows might have an obvious default value. For example, employees might be hired as members of the Staff department often enough that the application assumes that department assignment for new employees if no specific department is provided. SQL allows the database designer to specify a default value for each column in a table; if no default value is specified, then a default of null is implied.

It sometimes happens that database designers find themselves using a particular combination of data type, constraint, and default value frequently (perhaps in various tables). SQL allows the definition of a domain 92 to give a name to that combination; the domain name can then be used in place of the data type (and constraint and default value) when defining columns in tables. For example, the name MONEY might be applied to a domain providing a data type of DECIMAL(8,2) – decimal with
8 total digits of precision, two of them after the decimal point – along with a constraint saying that the value must never be negative, and a default value of null. Columns such as SALARY and BUDGET could then be defined to be MONEY, providing a convenient shorthand as well as ensuring
consistency of specification. SQL programmers have several alternatives for using the language. The
most widely-used alternative is to embed SQL statements into programs written in ordinary third-generation programming languages (3GLs). This technique, called embedded SQL, requires the application programmer to write the application in a 3GL (the SQL standard supports Ada, C, COBOL, Fortran, MUMPS, Pascal, and PL/I; SQL implementations often support other languages and standard support is likely for increasingly important languages such as Java). 

 Each embedded SQL statement starts with a distinguished string, such as “EXEC SQL”. In a typical
SQL implementation, this embedded SQL program is processed by a preprocessor that extracts the SQL statements and (conceptually, at least) replaces them with a “call statement” to invoke the (conceptual or literal) procedure that the system creates to contain the SQL statement. The SQL statement (contained in that procedure) is then compiled and optimised by the SQL system to prepare it for later execution, while the remaining application program is compiled in the normal way. When the program executes, the optimized SQL statements are executed as specified by the 3GL
code.

In some SQL implementations and in the SQL standard, it is possible to write actual SQL procedures (each containing a single SQL statement), collecting related procedures together into a module. Called module language, this technique permits applications to be written in a more modular
fashion – database-related operations are coded in “pure SQL” and processed by an SQL compiler, while other application operations are coded in the appropriate 3GL and processed by that language’s compiler. The SQL procedures are invoked through actual “call statements” by the application
program. The two techniques are completely isomorphic with one another. In implementations that support both techniques, the choice of which to use is often a matter of taste or of organization policy.

In many applications, such as traditional mainframe applications, the SQL statements to be executed are well-known when the application is written. Embedded (or module language) SQL is appropriate for such applications. In other situations, such as ad hoc query generators, graphical database browsers, or client-server systems with widely-varying users, the SQL statements that will be executed are often not known until execution time, when the user formulates a question. A technique called dynamic SQL allows SQL statements to be formulated at runtime, prepared for execution by the database system, and executed on demand. Dynamic SQL is typically slower than static SQL because of its inability to precompile and optimize statements. Of course, the benefits of flexibility often make this a worthwhile cost.

Source : "International Handbooks on Information Systems"
Series Editors
Peter Bernus, Jacek Błaz˙ewicz, Günter Schmidt, Michael Shaw