SQL Data Definition - Circus of LifeSQL数据定义的马戏团的生活.pptVIP

Appendix-A SQL: Data Definition Transparencies Chapter - Objectives Data types supported by SQL-92. How to define integrity constraints under IEF (Integrity Enhancement Feature). How to create and delete tables. How to create and delete views using SQL. How the DBMS performs operations on views. Under what conditions views are updatable. Advantages and disadvantages of views. How to use the GRANT and REVOKE statements as a level of security. ISO SQL Data Types Integrity Enhancement Feature Integrity control consists of constraints that we wish to impose in order to protect the database from becoming inconsistence. Consider five types of integrity constraints: Required data. Domain constraints. Entity integrity. Referential integrity. Enterprise constraints. IEF –Required Data Domain Constraints 1. Required Data (some columns must contain a valid value) position VARCHAR(10) NOT NULL 2. Domain Constraints (a) CHECK sex CHAR NOT NULL CHECK (sex IN (M, F)) IEF –Domain Constraints (b) CREATE DOMAIN --A domain is given a name, domain name, a data type, an optional default value, and an optional check option. CREATE DOMAIN domain_name [AS] data_type [DEFAULT default_option] [CHECK (search_condition)] For example: CREATE DOMAIN sex_type AS CHAR CHECK (VALUE IN (M, F)); sex SEX_TYPE NOT NULL IEF –Domain Constraints (cont’d) Domains can be removed using DROP DOMAIN: DROP DOMAIN domain_name [RESTRICT | CASCADE] IEF –Entity Integrity Primary key of a table must contain a unique, non-null value for each row. SQL-92 supports FOREIGN KEY clause in CREATE and ALTER TABLE statements: PRIMARY KEY(sno) PRIMARY KEY(rno, pno) PRIMARY KEY clause can be specified only once per table. Can still ensure uniqueness for alternate keys using UNIQUE. IEF –Referential Integrity FK is column or set of columns that links each row in child table containing foreign FK row of parent table containing matching PK. Referential integrity means that, if FK contains a value,