Defining a Relation Schema in SQL

Defining a Relation Schema in SQL

Now we shall begin a discussion of data definition, the portions of SQL that involve explaining the structure of information in the database. On the contrary, the aspects of SQL discussed previously - queries and modifications - are often called data manipulation.

The subject of this section is declaration of the schemas of stored relations. We shall see how to explain a new relation or table as it is called in SQL. “View Definitions” covers the declaration of "views" which are virtual relations that are not actually stored in the database, while some of the more difficult issues regarding constraints on relations are postponed to “Constraints and Triggers”.

Data Types

To begin, let us introduce the principal atomic data types that are supported by SQL systems. All attributes must have a data type.

1. Character strings of fixed or varying length. The type CHAR(n) denotes a fixed-length string of n characters. That is, if an attribute has type CHAR(n), then in any tuple the component for this attribute will be a string of n characters. VARCHAR(n)  denotes a string of up to n characters. Components for an attribute of this type will be strings of between 0 and n characters. SQL permits reasonable coercions between values of character-string types. Usually, a string is padded by trailing blanks if it becomes the value of a component that is a fixed-length string of greater length. For instance, the string ’foo’, if it became the value of a component for an attribute of type CHAR(5), would assume the value ’foo’ (with two blanks following the second o). The padding blanks can then be ignored if the value of this component were compared (see “Comparison of Strings”) with another string.

2. Bit strings of fixed or varying length. These strings are similar to fixed and varying-length character strings, but their values are strings of bits rather than characters. The type BIT(n) denotes bit strings of length n, while BIT VARYING(n) denotes bit strings of length up to n.

3. The type BOOLEAN denotes an attribute whose value is logical. The possible values of such an attribute are TRUE, FALSE, and - although it would surprise George Boole - UNKNOWN.

4. The type INT or INTEGER (these names are synonyms) denotes typical integer values. The type SHORTINT also denotes integers, but the number of bits permitted may be less, depending on the implementation (as with the types int and short int in C).

5. Floating-point numbers can be represented in a variety of ways. We may use the type FLOAT or REAL (these are synonyms) for typical floating point numbers. A higher precision can be obtained with the type DOUBLE PRECISION; again the distinction between these types is as in C. SQL also has types that are real numbers with a fixed decimal point. For instance, DECIMAL(n,d) allows values that consist of n decimal digits, with the decimal point assumed to be d positions from the right. Thus, 0123.45 is a possible value of type DECIMAL(6,2). NUMERIC is almost a synonym for DECIMAL, although there are possible implementation-dependent differences.

6. Dates and times can be represented by the data types DATE and TIME, respectively. Remember our discussion of date and time values in “Dates and Times”. These values are basically character strings of a special form. We may, in fact, coerce dates and times to string types, and we may do the reverse if the string "makes sense" as a date or time.