The SQL Data definition language (DDL) allows database items such as table and views to be created and to be deleted.
4.3.1 Creating a Database
The authority to create a database is done by the Database Administrator (DBA).
A schema is named collection of database objects that are related to one another.
These objects in the schema include the tables, views, domains etc. SQL provides for the definition of the schema as:
CREATE SCHEMA [ ] AUTHORISATION
Therefore if the schema is OrderProcessing and the creator is Lim the SQL statement is:
CREATE SCHEMA OrderProcessing AUTHORISATION Lim;
A schema can be deleted using the following command:
DROP SCHEMA OrderProcessing
4.3
SELF-CHECK 4.2
1. How do you define Primary key and Foreign key?
2. Identify the actions the SQL support. Briefly explain these actions.
4.3.2 Creating a Table
We now create the table structure using the CREATE TABLE statement which has the following syntax:
CREATE TABLE tablename
( columnName dataType [ NOT NULL]
[ DEFAULT defaultOption] [CHECK (searchCondition)]
[PRIMARY KEY (listofcolumns)]
[FOREIGN KEY (listofForeignKeyColumns)
REFERENCES ParentTableName[(listOfCandidateKeyColumns)]
[ON UPDATE referentailAction]
[ ON DELETE referentailAction])
(Connoly and Begg, 2005) The CREATE TABLE statement creates a table consisting of one or more columns of the defined data type.
The optional DEFAULT clause provides for default values in a column.
Whenever an INSERT statement fails to specify a column value, SQL will use the default value.
The NOT NULL is specified to ensure that the column must have a data value.
The remaining clauses are constraints and are headed by the clause:
CONSTRAINT constraintname.
The PRIMARY KEY clause specified the column(s) that comprise the primary key. It is assumed by default that the primary key value is NOT NULL.
The FOREIGN KEY clause specifies a foreign key in the child table and it relationship to the parent table. This clause specifies the:
• A listofForeignKeyColumns, the column(s) that form the foreign key.
• A REFERENCES subclause indicting to the parent table that holds the matching primary key.
• An optional ON UPDATE clause to specify the action taken on the foreign key value of the child table if the matching primary key in the parent table is updated. These actions were discussed in section 4.2.4.
• An optional ON DELETE clause to specify the action taken on the child table if the row(s) in the parent table are deleted whose primary key values
matches the foreign key value in the child table. These actions were discussed in section 4.2.4.
The following 3 examples show CREATE TABLE statements for the Order Entry Database using the tables Customer, Order and OrderDetail.
(i) Creating the Customer table using the features of the CREATE TABLE statement. (ii) Creating the Order table
CREATE TABLE Order CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES
Employee
ON DELETE NO ACTION
ON UPDATE CASCADE ) (iii) Creating the Order table
CREATE TABLE OrderDetail
( OrderNo CHAR(4) NOT NULL ProductNo CHAR(5) NOT NULL
QtyOrdered INTEGER(4) NOT NULL
CONSTRAINT PKOrderDetail PRIMARY KEY (OrderNo,ProductNo) CONSTRAINT FKOrderNo FOREIGN KEY (OrderNo) REFERENCES
Order
ON DELETE NO ACTION
ON UPDATE CASCADE
CONSTRAINT FKProductNo FOREIGN KEY (ProductNo) REFERENCES
Product
ON DELETE NO ACTION
ON UPDATE CASCADE )
(iv) The reader can now create the rest of the tables in the Order Entry Database as an exercise.
4.3.3 Changing a Table Definition
The ALTER TABLE statement supports modification of a table definition. The definition of the ALTER TABLE statement consists of the options:
• Adding a new column to a table and dropping an existing column
• Adding a new table constraint and dropping an existing table constraint
• Setting a default for a column and dropping a existing default for a column The basic syntax of the statement is:
ALTER TABLE TableName
[ADD[COLUMN] columnName dataType [NOT NULL]
[DEFAULT defaultOption]
[DROP [COLUMN] columnName [RESTRICT|CASCADE]]
[ADD[CONSTRAINT][ConstraintName]] tableConstraintDefinition]
[DROP CONSTRAINT ConstraintName [RESTRICT|CASCADE]]
[ALTER [COLUMN] SET DEFAULT defaultOption]
[ALTER [COLUMN] DROP DEFAULT]
A tableConstraintDefinition include the PRIMARY KEY, FOREIGN KEY or the CHECK clauses.
The ADD COLUMN clause is the same as the definition of a column in the CREATE TABLE statement.
The DROP COLUMN clause defines the name of the column to be dropped and has the option RESTRICT or CASCADE.
• RESTRICT. The DROP operation is rejected if the column is referenced by another database object.
• CASCADE. The DROP operation proceeds and drops the column from any database items it is referenced by.
(Connoly and Begg, 2005) For example if we want to add an extra column that is Branch_no to the Employee table the SQL statements would be:
ALTER TABLE Employee
ADD Branch_no CHAR (4) NOT NULL;
4.3.4 Removing a Table
We can remove a table from the database by using the DROP TABLE statement which has the syntax;
DROP TABLE TableName
For example, to remove the OrderDetail table we specify it as:
DROP TABLE OrderDetail
The DROP TABLE statement should be carried out with care as the total effect can be damaging to the rest of the database tables. It is recommended that this clause be used if a table is created with an incorrect structure. Then the DROP TABLE clause can be used to delete this table and the structure created again.
SELF-CHECK 4.3
1. What does the CREATE TABLE statement do?
2. What does the ALTER TABLE statement do?
3. How can we remove a table from the database?
VIEWS
A view is a virtual or derived relation that may be derived from one or more base relations. Views do not physically exist in the database. It allows users to customize the data according to their needs and hides part of the database from certain users. Let us look at how views are created.
4.4.1 Creating a View
The format of the CREATE VIEW is:
CREATE VIEW ViewName [(newColumnName[,⁄])]
AS subset [WITH [CASCADED|LOCAL] CHECK OPTION]
A view is defined by specifying an SQL SELECT statement. A name may optionally be assigned to each column in the view. If the column name is omitted, each column in the view takes the name of the corresponding column in the subselect statement. The subselect is known as the defining query.
(Connoly and Begg, 2005)
We now examine the different types of views with examples.