| Contents | |
| Material: |
A schema gathers up database items belonging to one administrative block, such as all the tables of an order processing system. The schema is defined with the statement 'create schema' that specifies the name and the owner of the schema. All database management systems, for example Oracle, do not provide the schema concept.
| syntax | create schema schema_name
authorization user_name |
||||||
| example | create schema pizza_db authorization alfonso | ||||||
The whole schema can be deleted with the statement 'drop schema'. If there are items in the schema, the operation will fail unless the cascade keyword is included in the statement.
| syntax | drop schema schema_name [cascade] | ||||||
| example | drop schema pizza_db cascade | ||||||
It is possible to specify named domains on the basis of the basic data types of SQL. The values of the domain may be restricted by specifying a constraint that must be satisfied by the acceptable values. The values in two different domains are comparable if the domains a based on the same basic data type. The condition will be described more closely in connection of queries.
| syntax | create domain domain_name [as]
data_type
[defaul default_value] [[ constraint constraint_name] check (value condition) ] |
||||||
| examples | create domain month as integer
default 1 check (value between 1 and 12); create domain workday varchar(11)
|
||||||
In the first example, the default value for domain month is 1, and the value must be between 1 and 12. In the latter example, no default value has been given, thus it is the value NULL. Only the listed day names can be accepted into the columns specified with the help of this domain.
A domain can be dropped and changed with the statements
| syntax | drop domain domain_name
;
alter domain domain_name drop default; alter domain domain_name
alter domain domain_name
alter domain domain_name add
|
||||||
| examples | drop domain month; alter domain workday drop constraint c_workday; alter domain workday add constraint
c_workday
|
||||||
The base tables of a database are defined by the statement 'create table'
| syntax | create table table_name
( column definition [,... ] [, constraint [,…]] ) |
||||||
| example | create table product (
productId varchar(8) not null ,
modelId varchar(20) not null,
price decimal(5,2) not null,
primary key (productId,modelId),
constraint fk_ptype foreign key (productId)
references productType );
|
||||||
A table may have many columns. The maximum number of columns depends on the implementation, but it is often 256. The column specifications are part of the table definition. Several constraints, specific for that table, can also be included in each table definition.
According to the standard, the name of the column and the table can be 128 characters long. The name cannot begin with a number. The alphabet a-z, numbers and some special characters are allowed in the name. If the name is enclosed in citation marks, other characters are also allowed, such as spaces (e.g. "office number").
Examine the schema of our example database.
The specification of a table can be dropped, and the table deleted with 'drop table' statement. Drop table will not work if there are foreign keys defined to refer to the table being dropped.
| syntax | drop table table_name
|
||||||
| example | drop table product | ||||||
The structure of a table can be changed with the statement 'alter table'. This statement can add new columns, change the specifications for existing columns (e.g. length specifications for values), remove columns (not possible in all implementations), and add and remove constraints.
| syntax | alter table tablename
add (
[column definition 1 [,...] [constraint definition1 [,...] );
alter table tablename
modify (
alter table tablename drop column column_name;
alter table tablename
drop constraint
|
||||||
| examle | alter table product add (weight
decimal(6,2)); alter table product modify (price decimal(6,2)); alter table product drop constraint fk_ptype ; |
||||||
The specification of a column is part of the table definition. The specification assigns a name for the column. In addition, at least the data type has to be specified for the column, either with the help of SQL's basic data types (reference) or with named domains (reference).
| syntax | column_name
{ data_type | domain_name
}
[default default_value ] [ column constraint [...]] |
||||||
| examples |
customerId varchar(15) not null primary key
meeting_day workday default 'Monday' price decimal(5,2) check (value <100.00)
|
||||||
A column can be given a default value ('Monday' as a meeting day above), which is used for a new row in case the column has not been given a value when adding the row. A column specification may also contain column specific constraints. A typical constraint is to deny the null values in the column by 'not null' clause. If the column constitutes the key of the table by itself, the clause 'primary key' can be given in the column specification. This may also be defined at the end of the table definition as a table specific constraint. Values in the column may be restricted with check clauses.
Both table and column definition may contain constraints. The most important of them are the definitions of the key and the the foreign keys.
If the key consists of only a single column, it may be defined within a column specification. Otherwise a separate table specific key definition constraint is needed. It is useful to give names for constraints, though it is not compulsory, because the constraints can then be removed easily. Each column that is included in the primary key must be defines as not null..
| syntax | [constraint constraint_name ] primary key ( column_name [,...] ) | ||||||
| example |
constraint pk_product
primary key (productId, modelId) |
||||||
A foreign key composed of a single column only may be defined within the column definition. Multi-column foreign keys have to be specified with a separate foreign key constraint.
| sql_syntax | [constraint constraint_name
] foreign key
( column_name [,...] ) references table_name [( foreign_column_name [,...] )] [on event1 action1 [, event 2 action 2 [,...] ] |
||||||
| example |
constraint fk_ptype foreign key
(productId) references productType on delete cascade, on update restrict |
||||||
In the example above, the foreign key productId refers to the primary key of the table productType. In this case, the columns referred to need not be listed. If the row referred to is removed, all the rows that refer to it are also removed due to 'on delete cascade' clause. The primary key values of productType being used as foreign key values cannot be changed because of the 'on update restrict' clause.
The foreign key definition may contain an action part, describing what to do in case one of the maintenance operations violates the referential integrity, i.e. the requirement that rows that are referred must also exist. As a default, operations that violate the referential integrity are not allowed. There are, however, cases where the violation of the referential integrity can be fixed automatically. The action part controls the fixing.
ON DELETE - action takes place when a row that is referred to is to be deleted. The possible actions are:
ON UPDATE -action takes place when the primary key of a referred row is to be changed. The possible actions are:
Database procedures (persistent stored modules) are programs stored as precompiled in the database to process the database. Database procedures can be classified into two types
To program them, a separate database programming language close to the Ada programming language has been created. In Oracle, the language is called PL/SQL. Database procedures offer an efficient way of packaging database operations into one, and hiding the structure of the database from the user. The users need not know details about the database. They only need to know the interface the database procedures offer.
Automatically started database procedures, called triggers, can be specified to activate when a change takes place in the database, for example, a row is deleted from a table. The control of the referential integrity, for example, is typically done with the help of triggers that are generated on the basis of foreign key definitions.
Below you will find an example written in Oracle's PL/SQL of what a database function looks like. The function computes the price of an order.
create function compute_price (
in order_number integer) return decimal
is
cursor tprice is
select amount*price tpr
from ItemOrdered, Product
where ItemOrdered.OrderId= order_number and
Product.ProductID=ItemOrdered.productId and
Product.ModelId=ItemOrdered.ModelId
union
select amount*AddOnPrice
from AddOn, Material
where AddOn.OrderId= order_number and
AddOn.materialId=Material.MaterialId;
total mumeric(9,2);
begin
open cursor tprice;
fetch tprice into total;
if tprice%notfound then
total=-1; -- return a geative value
end if;
close tprice;
return total;
end;
|