The ISO SQL Data Types

(Connolly3, Pg 157)

1.      SQL Identifiers;

·         SQL identifiers are used to identify objects in the database;

·         The characters that can be used in a user-defined SQL identifier must appear in a character set. The ISO standard provides a default character sets which consists of the upper-case letters A…Z, the lower-case letters a…z, the digits 0…9, and the underscore character ( _ ).

·         The following restrictions are imposed on an identifier:

Ø      An identifier cannot be longer than 128 characters (most dialects have a much lower limit than this);

Ø      An identifier must start with a letter;

Ø      An identifier cannot contain spaces.

2.      SQL Scalar Data Types;

·         The following are the SQL scalar data types defined in the ISO standard:

·         Sometimes the data types character and bit are collectively referred to as string datatypes, and exact numeric and approximate numeric are referred to as numeric data types.

3.      Boolean data;

·         Boolean data consists of the distinct truth values TRUE and FALSE;

·         Unless prohibited by a NOT NULL constraint, Boolean data also supports the UNKNOWN truth value as the NULL value;

·         All Boolean data type values and SQL truth values are mutually comparable and assignable. The value TRUE is greater than the value FALSE, and a ny comparison involving the NULL value or an UNKNOWN truth value returns an UNKNOWN result.

4.      Character data;

·         Boolean data consists of the distinct truth values TRUE and FALSE;

·         branchNo CHAR(4)

·         address VARCHAR(30)

5.      Bit data;

·         The bit data type is used to define bit strings, that is, a sequence of binary digits (bits).

·         bitString BIT(4)

6.      Exact numeric data;

·         The exact numeric data type is used to define numbers with an exact representation. The number consists of digits, an optional decimal point, and an optional sign;

·         An exact numeric data type consists of a precision and a scale. The precision gives the total number of significant digits, the scale gives the total number of decimal places. The default scale is always 0, the default precision is implementation-defined.

·         rooms SMALLINT(4)

·         salary DECIMAL(7,2)

7.      Approximate numeric data;

·         The approximate numeric data type is used for defining numbers that do not have an exact representation, such as real numbers. Approximate numeric, or floating point, is similar to scientific notation in which a number is written as a mantissa times some power of ten (the exponent);

·         The precision controls the precision of the mantissa. The precision of REAL and DOUBLE PRECISION is implementation-defined.

8.      Datetime data;

·         The datetime data type is used to define points in time to a certain degree of accuracy;

·         The ISO standard subdivides the datetime data type into YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE. The latter two fields specify the hour and minute part of the time zone offset from Universal Coordinated Time which used to be called Greenwich Mean Time);

·         DATE is used to store calendar dates using the YEAR, MONTH, and DAY fields. TIME is used to store time using HOUR, MINUTE, and SECOND fields. TIMESTAMP is used to store date and times;

·         The timePrecision is the number of decimal places of accuracy to which the SECOND field is kept. If not specified, TIME defaults to a precision of 0 (that is, whole seconds), and TIMESTAMP defaults to 6 (that is, micrososeconds);

·         The WITH TIME ZONE keyword controls the presence of the TIMEZONE_HOUR and TIMEZONE_MINUTE fields.

·         viewDate DATE

Integrity Enhancement Feature (IEF)

(Connolly3, Pg 163)

·         Integrity control consists of constraints which we impose in order to protect the database from becoming inconsistent. We consider 5 types of integrity constraint:

Ø      Required data;

Ø      Domain constraints;

Ø      Entity integrity;

Ø      Referential integrity;

Ø      Enterprise constraints.

·         These constraints can be defined in the CREATE and ALTER TABLE statements.

9.      Required Data;

·         Some columns must contain a valid value, they are not allowed to contain nulls. The ISO standard provides the NOT NULL column specifier in the CREATE and ALTER TABLE statements to provide this type of constraint. When NOT NULL is specified, the system rejects any attempt to insert a null in the column. If NULL is specified, the systems accepts nulls. The ISO default is NULL;

·         position VARCHAR(10) NOT NULL

10.  Domain Constraints;

·         The ISO standard provides two mechanisms for specifying domains in the CREATE and ALTER TABLE statements;

·         The first is the CHECK clause, which allows a constraint to be defined on a column or the entire table;

·         sex CHAR NOT NULL CHECK(sex IN (‘M’,‘F’))

·         The ISO standard allows domains to be defined more explicitly using the CREATE DOMAIN statement;

·         CREATE DOMAIN SexType AS CHAR

DEFAULT ‘M’

CHECK (VALUE IN (‘M’,‘F’)) ;

·         CREATE DOMAIN BranchNumber AS CHAR(4)

CHECK (VALUE IN (SELECT branchNo FROM Branch)) ;

·         The preferred method of defining domain constraints is using the CREATE DOMAIN statement;

·         Domains can be removed from the database using the DROP DOMAIN statement;

11.  Entity Integrity;

·         The ISO standard supports entity integrity with the PRIMARY KEY clause in the CREATE and ALTER TABLE statements;

·         PRIMARY KEY (propertyNo)

·         To define a composite primary key, we specify multiple column names in the PRIMARY KEY clause;

·         PRIMARY KEY (clientNo,propertyNo)

·         The PRIMARY KEY clause can be specified only once per table. However, it is still possible to ensure uniqueness for any alternate keys in the table using the keyword UNIQUE. Every column that appears in a UNIQUE clause must also be declared NOT NULL. There may be as many UNIQUE clauses per table as required. SQL rejects any INSERT or UPDATE operation that attempts to create a duplicate value within each candidate key (that is, primary key or alternate key);

·         clientNo VARCHAR(5) NOT NULL,

UNIQUE(clientNo,propertyNo)

12.  Referential Integrity;

·         Referential integrity means that, if a foreign key contains a value, that value must refer to an existing, valid row in the parent table;

·         The ISO standard supports the definition of foreign keys with the FOREIGN KEY clause in the CREATE and ALTER TABLE statements;

·         FOREIGN KEY (branchNo) REFERENCES Branch

·         SQL rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table without a matching candidate key value in the parent table. The action SQL takes for any UPDATE or DELETE operation that attempts to update or delete a candidate key value in the parent table that has some matching rows in the child table is dependent on the referential action specified using the ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause.

Ø      CASCADE: Delete the row from the parent table and automatically delete the matching rows in the child table. Since these deleted rows may themselves have a candidate key that is used as a foreign key in another table, the foreign key rules for these tables are triggered, and so on in a cascading manner.

Ø      SET NULL: Delete the row from the parent table and set the foreign key value(s) in the child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier specified.

Ø      SET DEFAULT: Delete the row from the parent table and set each component in the child table to the specified default value. This is valud only if the foreign key columns have a DEFAULT value specified.

Ø      NO ACTION: Reject the delete operation from the parent table. This is the default setting if the ON DELETE rule is omitted.

·         FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL

13.  Enterprise Constraints;

·         Updates to tables may be constrained by enterprise rules governing real-world transactions that are represented by the updates. The CREATE ASSERTION statement is an integrity constraint that is not directly linked with a table definition;

·         CREATE ASSERTION StaffNotHandlingTooMuch

CHECK(NOT EXISTS(SELECT staffNo

                  FROM PropertyForRent

                  GROUP BY staffNo

                  HAVING COUNT(*)>100));

Data Definition

(Connolly3, Pg 167)

·         The main SQL data definition language statements are:

·         Although not covered by the SQL standard, the following two statements are provided by many DBMSs:

14.  Creating a Database;

·         The ISO standard does not specify how databases are created. According to the standard, relations and other database objects exist in an environment. Among other things, each environment consists of one or more catalogs, and each catalog consists of a set of schemas. A schema is a named collection of database objects that are in some way related to one another. The objects in a schema can be tables, views, domains, assertions, collations, translations, and character sets. All the objects in a schema have the same owner and share a number of defaults;

·         CREATE SCHEMA SqlTests AUTHORIZATION Smith;

·         A schema can be destroyed using the DROP SCHEMA statement:

·         If RESTRICT is specified, which is the default if neither qualifier is specified, the schema must be empty or the operation fails. If CASCADE is specified, the operation cascades to drop all objects associated with the schema. If any of these drop operations fail, the DROP SCHEMA fails.

15.  Creating a Table (CREATE TABLE);

·         Creation of base relations in a database is achieved using the CREATE TABLE statement:

·         The CREATE TABLE statement creates a table called TableName consisting of one or more columns of the specified dataType;

·         The optional DEFAULT clause can be specified to provide a default value for a particular column. SQL uses this default value when an INSERT statement fails to specify a value for the column;

·         The NOT NULL, UNIQUE and CHECK clauses were discussed previously;

·         The remaining clauses are known as table constraints and can optionally be preceded with the clause:

CONSTRAINT ConstraintName

which allows the constraint to be dropped by name using the  ALTER TABLE statement.

CREATE DOMAIN OwnerNumber AS VARCHAR(5)

    CHECK(VALUE IN(SELECT OwnerNo FROM PrivateOwner));

CREATE DOMAIN StaffNumber AS VARCHAR(5)

    CHECK(VALUE IN(SELECT StaffNo FROM Staff));

CREATE DOMAIN BranchNumber AS VARCHAR(4)

    CHECK(VALUE IN(SELECT BranchNo FROM Branch));

CREATE DOMAIN PropertyNumber AS VARCHAR(5);

CREATE DOMAIN Street AS VARCHAR(25);

CREATE DOMAIN City AS VARCHAR(15);

CREATE DOMAIN PostCode AS VARCHAR(8);

CREATE DOMAIN PropertyType AS CHAR(1)

    CHECK(VALUE IN(‘B’, ‘C’, ‘D’, ‘E’, ‘F’, ‘M’, ‘S’));

CREATE DOMAIN PropertyRooms AS SMALLINT

    CHECK(VALUE BETWEEN 1 AND 15);

CREATE DOMAIN PropertyRent AS DECIMAL(6,2)

    CHECK(VALUE BETWEEN 0 AND 9999.99);

CREATE TABLE PropertyFroRent(

    propertyNo  PropertyNumber    NOT NULL,

    street      Street            NOT NULL,

    city        City              NOT NULL,

    postcode    Postcode,

    type        PropertyType      NOT NULL DEFAULT ‘F’,

    rooms       PropertyRooms     NOT NULL DEFAULT 4,

    rent        PropertyRent      NOT NULL DEFAULT 600,

    ownerNo     OwnerNumber       NOT NULL,

    staffNo     StaffNumber

               CONSTRAINT StaffNotHandlingTooMuch

                  CHECK(NOT EXISTS  (SELECT staffNo

                                    FROM PropertyForRent

                                    GROUP BY staffNo

                                    HAVING COUNT(*)>100)),

    branchNo    BranchNumber      NOT NULL,

PRIMARY KEY(propertyNo),

FOREIGN KEY(staffNo) REFERENCES Staff ON DELETE SET NULL

                                   ON UPDATE CASCADE,

FOREIGN KEY(ownerNo) REFERENCES PrivateOwner ON DELETE NO ACTION

                                   ON UPDATE CASCADE,

FOREIGN KEY(branchNo) REFERENCES Branch ON DELETE NO ACTION

                                   ON UPDATE CASCADE);

16.  Changing a Table Definition (ALTER TABLE);

·         The ISO standard provides an ALTER TABLE statement for changing the structure of a table once it has been created;

·         The definition of the ALTER TABLE statement in the ISO standard consists of six options to:

Ø      Add a new column to a table / Drop a column from a table;

Ø      Add a new table constraint / Drop a table constraint;

Ø      Set a default for a column / Drop a default for a column.

·         The parameters are as defined for CREATE TABLE;

·         A tableConstraintDefinition is one of the clauses: PRIMARY KEY, UNIQUE, FOREIGN KEY, or CHECK;

·         The ADD COLUMN clause is similar to the definition of a column in the CREATE TABLE statement;

·         The DROP COLUMN clause specifies the name of the column to be dropped from the table definition, and has an optional qualifier that specifies whether the DROP action is to cascade or not:

Ø      RESTRICT: The DROP operation is rejected if the column is referenced by another database object (such as by a view definition). This is the default setting;

Ø      CASCADE: The DROP operation proceeds and automatically drops the column from any database objects it is referenced by. This operation cascades, so that if a column is dropped from a referencing object, SQL checks whether that column is referenced by any other object and drops it from there if it is, and so on.

·         ALTER TABLE Staff

    ALTER position DROP DEFAULT;

·         ALTER TABLE Staff

    ALTER sex SET DEFAULT ‘F’;

·         ALTER TABLE PropertyForRent

    DROP CONSTRAINT StaffNotHandlingTooMuch;

·         ALTER TABLE Client

    ADD prefNoRooms PropertyRooms;

17.  Removing a Table (DROP TABLE);

·         We can remove a redundant table from a database using the DROP TABLE statement;

·         DROP TABLE PropertyForRent ;

·         The DROP TABLE statement allows us to specify whether the DROP action is to be cascaded or not:

Ø      RESTRICT: The DROP operation is rejected if there are any other objects that depend on their existence upon the continued existence of the table to be dropped;

Ø      CASCADE: The DROP operation proceeds and SQL automatically drops all dependent objects (and objects dependent on these objects).

18.  Creating an Index (CREATE INDEX);

·         An index is a structure that provides accelerated access to the rows of a table based on the values of one or more columns;

·         The creation of indexes is not standard SQL;

·         The specified columns constitute the index key, and should be listed in major to minor order;

·         Indexes can be created only on base tables not on views;

·         If the UNIQUE clause is used, uniqueness of the indexed column or combination of columns will be enforced by the DBMS;

·         CREATE UNIQUE INDEX StaffNoInd ON Staff(StaffNo) ;

·         For each column we may specify that the order is ascending (ASC) od descending (DESC), with ASC being the default setting;

·         CREATE INDEX RentInd ON PropertyRent(city,rent) ;

19.  Removing an Index (DROP INDEX);

·         We can use the DROP INDEX statement to remove an index from a database;

·         DROP INDEX RentInd ;