(the principle behind attribute domains is similar to that behind typing of variables in programming languages.)
We have seen that a domain of possible values must be associated with every attribute. We know a no of standard domain types and data and time types defined in SQL. Declaring an attribute to be of a particular domain acts as a constraint on the values that it can take. The system tests them easily whenever a new data item is entered into the database.
A domain is a set of values that may be assigned to an attribute. A domain definition usually consists of the following components: domain name, meaning, data type , size(or length), and allowable values or allowable range.
As we know that every attribute must have a specific domain (in general data types) that accepts the associated values of its own kind. We know a number of standard domain types, such as integer types, character types, and date/time types defined in SQL. Declaring an attribute to be of a particular domain acts as a constraint on the values that it can take. Domain constraints are the most elementary form of integrity constraint. The system tests them easily whenever a new data item is entered into the database.
It is possible for several attributes to have the same domain. For example, the attributes customer-name and employee-name might have the same domain. At the implementation level, both customer names and branch names are character strings.
The create domain clause can be used to define new domains.
create domain
For example, the statements
create domain dollars number(12,2);
create domain pounds number(10,2);
Here, these statements define the domains dollars and pounds to be decimal numbers with a total of 12 digits and 10 digits respectively, two of which are placed after the decimal point. An attempt to assign a value of type dollars to a variable of type pounds would result in a syntax error, although both are of the same numeric type.
The check clause in SQL permits domains to be restricted in powerful ways that most programming language-type systems do not permit. Specifically, the check clause permits the schema designer to specify a predicate (selection condition) that must be satisfied by any value assigned to a variable whose type is the domain.
For example, a check clause can ensure that an hourly wage domain allows only values greater than a specified values.
create domain hourly wage numeric(5,2)
constraint wage-value-test check(value>=4.00)
The domain hourly wage has a constraint that ensures that the hourly wage is greater than 4.00. the clause constraint wage-value-test is optional and is used to give the name wage-value-test to the constraint. The name is used to indicate which constraint an update violated.
The check clause can also be used to restrict a domain not to contain any null values.
create domain account char(10)
constraint account-no-null-test check(value not null)
Another example: here the domain can be restricted to contain only a specified set of values by using the in the clause.
create domain accounType char(10)
constraint account-type-test check(value in(Checking, Saving))
