Chapter7 SQL:Data Definition

Chapter7 SQL:Data Definition

7.1.2SQL Scalar Data Types

Boolean data
Character data
Bit data: The format for specifying the bit data: BIT[VARYING][length]

7.1.2Exact Numeric Data

An exact numeric data type consists of a precision and a scale.

There are serval way of specifying an exact numeric data type:

NUMERIC[percision[,scale]]

DECIMAL[percision[,scale]]

INTEGER

SMALLINT

SMALLINT is used for small positive or negative whole numbers.

Approxiamate numeric data

The approxiamate numeric data type is used for defining numbers that do not have an exact representation.

TYPE:

FLOAT

REAL

DOUBLE PRECISION

Datetime data
TYPE:

DATA
TIME[timePrecision][WITH TIME ZONE]
TIMESTAMP[timePrecision][WITH TIME ZONE]
Interval data represent periods of time.

7.2Integrity Enhancement Feature
Five type of integrity constraints

• required data;
• domain constraitnts;
• entity integrity
• referential integrity
• general constraints

7.2.1Requred Data
The ISO standard provides the NOT NULL column specifierin the CREATE
TABLE and ALTER TABLEstatements to provide this type of constraints.
7.2.2Domain Constraints
The ISO standard provides two mechanisms for specifying domain in the CREATE TABLE and ALTER TABLEstatements.
The first is the CHECK clause,the format of the CHECK clause is :

CHECK(searchconditioin)

However,the ISO standard allows domain to be defined more explicitly using
the CREATECOMAIN statement:

CREATE DOMAIN DomainName[AS]datatype
[DEFAULT dafaultOption]

[CHECK(searchCondition)]

Domain can be removed from the database using the DROP DOMAIN statement:

DROP DOMAIN DomainName[RESTRICT|CASCADE]

If RESTRIC is specified and the domain is used in a existing table,view,or assertion definition,the drop will fail.

In the case of CASCADE,any table column that is based on the domain is automatically changed to use the domain underlying data type.

7.2.3 Entity Integrity
The ISO standard provides supports entity integrity with the PRIMARY KEY clause in the CREATE TABLE and ALTER TABLEstatements.

The format of PRIMARY KEY clause is PRIMARY KEY(column name(,...))

it is still possible to ensure uniqueness for any alternate keys in the table using the keyword UNIQUE.

7.2.4 Referential Integrity
Referential integrity means that , if the foreign key contain a value,that value must refer to an existing,valid row in the parent table.

Eg:
FOREIGN KEY(branchNo) REFRERNCES Branch

When a user attempts to delete a row from a parent table,...,SQL supports four options regarding the action to be taken

• CASCADE
• SET NULL
• SET DEFAULT
• NO ACTION
CASCADE:Delete the row from the parent table and automatically deletd the matching rows in the child table.

NO ACTION:Reject the delete operation from the parent table.

7.2.5 General Constraints

The ISO standards allows general constraints to be specified using the CHECK and UNIQUE clause of the CREATE TABLE and ALTER TABLE statements and the CREATE ASSERTION statements.

The CREATE ASSERTION statement is an integrity constraint that is not  directly linked with a table definition.

The fornat of the statements is

CREATE ASSERTION AssertionName

CHECK(searchCondition)

7.3 Data Definition
The main SQL data definition

CREATE|DROP
CREATE|DROP|ALTER
CREATE|DROP|ALTER
CREATE|DROP

language statements are:

SCHEMA
DOMAIN
TABLE

VIEW

7.3.1 Create a Database
CREATE SCHEMA[Name|AUTHORIZATION creatoridentifier]

DROP SCHEMA name [RESTRICT|CASCADE]

7.3.2 Create a Table

CREATE TABLE TableName
{(columnName dataType)[NOT NULL][UNIQUE]
[DAFAULT dafaultOption][CHECK(searchCondition)][,...]
}
[PRIMARY KEY(listOfColumns),]
{[UNIQUE(listOfColumns)[,...]]}
{[FOREIGN KEY]
REFERENCES ParentTable[(listOfColumns)]
[MATCH{PARTIAL|FULL}]
[ON UPDATE referentialAction]
[ON DELETE referentialAction]

{[CHECK](searchCondition)[,...]}

7.3.3Changing a Table Definition
The basic format of the statement is
ALTER TABLE table
[ADD|[COLUMN]columnName dataTpye[NOT NULL][UNIQUE]]
[DEFAULT defaultOption][CHECK(searchCondition)]]
[DROP[COLUMN] columnName[RESTRICT|CASCADE]]
[ADD[CONSTRAINT[constraintName]]tableconstraintDefinition]
[DROP CONSTRAINT constraintname[RESTRICT|CASCADE]]

[ALTER[COLUMN]SET DEFAULT defaultOption]

7.4View
...When the DBMS encounter a reference to a view ,one approach is to look up this definition and translate the request into an equivalent request against the source table of the view and then perform the equivalent request.This merging process,called view resolution.An alternative approach ,called view

materialization, store the view as a temporary table in the database and maintains the currency of the view as the underlying base table are update.

The format of the CREATE VIEW statement is

CREATE VIEW ViewName [(newColumnName[,...])]
AS subselect [WITH[CASCADEED|LOCAL]CHECK OPTION]

The subselect is known as the defining query.

A horizontal view restricts a user's access to selected row of one or more tables.

A vertical view restricts a user's access to selected columns of one or more tables.

7.4.2 Removing a View(DROP VIEW)
A view is removed from the database with the DROP VIEW statement:

DROP VIEW viewName [RESTRICT|CASCADE]

7.4.3 View Resolution
This query is based on the StaffProCnt view of Example:
SELECT staffNo , cnt
FROM StaffProCnt
WHERE branchNO = 'B003'

ORDER BY staffNo;

(1)The view column name in the SELECT list are translated into their corresponding column names in the defining query.This give:
SELECT s.staffNo AS staffNo,COUNT(*) AS cnt
(2)View names in the FROM clause are replaced with the corresponding FROM lists of the defining query:
FROM Staff s,PropertyForRent p
(3)The WHERE clause from the user query is combined with the WHERE clause of the defining query using the logical operator AND ,Thus:
WHERE s.staffNo = p.staffNo AND branch = 'B003'
(4)The GROUP BY and HAVING clauses are copied from the defining query.In this example,we have only a GROUP BY clause:
GROUP BY s.branchNo , s.staffNo
(5)Finally , the ORDER BY clause is copied from the user query with the view column name transalted into the defining query column name:
ORDER BY s.staffNo
(6)The final merger query becomes:

...

7.4.4 Restrictions on Views
• If a column in the view is based on an aggregate function,the column may appear only in SELECT and ORDER BY clauses of queries that access the view.In particular , such a column may not be used in a WHERE clause and may not be an argument to an aggregaton function in any query based on the view.

• A group view may never be joined with a based table or a view.

7.4.5 View Updatability

An alternative approach is to store the view as a temporary table in the database when the view is first queried...

7.5Transactions

A transaction is a logical unit of work consisting of one or more SQL statement that is guaranteed to be atomic with respect to recovery.

An SQL transaction automatically begins with a transaction-iitiating SQL statement executed by a user or program.

Changes made by a transaction are not visible to other concurrently executing transaction until the transaction completes.

A translation can complete in one of four ways:
• A COMMIT statement ends the transaction successfully , making the database change permanent.
• A ROLLBACK statement aborts the transaction , backing out any changes made by the transaction.

• For programmatic SQL....

7.6 Discretionary Access Control
7.6.1Granting Privileges to Other Users(GRANT)
The format of the GRANT statement is:

GRANT
{PrivilegeList|ALLPRIVILEGES}
ON
ObjectName
TO
{AuthorizationidList|PUBLIC}
[WITH GRANT OPTION]

PrivilegeList consists of one or more of the following privileges,separate by commas:

SELECT
DELECT
INSERT

[(columnName[,...])]
UPDATE [(columnName[,...])]

REFERENCES [(columnName[,...])]

USAGE

[WITH GRANT OPTION]:If these users pass a privilege on specifying WITH GRANT OPTION , the users receving the privilege may in turn grant it to still other users.

7.6.2 Revoking Privileges from Users(REVOKE)
The format of the statement is:

REVOKE[GRANT OPTION]{PrivilegeList|ALL PRIVILEGES}
ON
ObjectName
FROM
{AuthorizationalList|PUBLIC}{RESTRICT|CASCADE}