數(shù)據(jù)庫最后一次復(fù)習(xí)課件

1、Chapter 7 Integrity, Views, Security, and Catalogs2022/7/18Integrity,Views,Security,and Catalogs2from Database Design to Physical FormCREATE TABLEintegrity constraints (完整性約束)CREATE VIEWSecurityThe GRANT & REVOKE statementsCatalogsSchemas2022/7/18Integrity,Views,Security,and Catalogs37.1 Integrity C

2、onstraintsDef.7.1.1 Clauses of the Create Table Command (Figure 7.1)schema name & table namecolumn defintioncolumn name & data typean optional DEFAULT clauseDEFAULT default_constant | NULL column constraintstable constraints2022/7/18Integrity,Views,Security,and Catalogs4Integrity Constraint (Figure

3、7.1, pg. 411)Integrity Constraints in a single column NOT NULL | CONSTRAINT constraint_name UNIQUE| PRIMARY KEY| CHECK ( search_condition )| REFERENCES table_name ( column_name ) ON DELETE CASCADE | RESTRICT | SET NULL ON UPDATE CASCADE | RESTRICT | SET NULL 2022/7/18Integrity,Views,Security,and Cat

4、alogs5Integrity Constraints in multiple columns CONSTRAINT constraint_name UNIQUE ( colname , colname . )| PRIMARY KEY ( colname , colname . )| CHECK ( search_condition )| FOREIGN KEY ( colname , colname . ) REFERENCES tab_name (colname , .) ON DELETE CASCADE|RESTRICT|SET NULLON UPDATE CASCADE|RESTR

5、ICT|SET NULL2022/7/18Integrity,Views,Security,and Catalogs67.1 Integrity ConstraintsTrigger (Figure 7.10, pg. 425) (觸發(fā)器)CREATE TRIGGER trigger_name BEFORE|AFTER INSERT | DELETE | UPDATE OF colname , colname . ON table_name REFERENCING corr_name_def , . FOR EACH ROW | FOR EACH STATEMENT WHEN ( search

6、_condition ) statement| BEGIN ATOMIC statement; . END7CREATE TRIGGER trigger_name BEFORE | AFTER INSERT | DELETE | UPDATE OF colname , colname . ON table_name REFERENCING corr_name_def , . FOR EACH ROW | FOR EACH STATEMENT WHEN ( search_condition ) statement| BEGIN ATOMIC statement; statement; . END

7、觸發(fā)事件結(jié)果事件觸發(fā)方式2022/7/18Integrity,Views,Security,and Catalogs87.2 Creating ViewsViewidea ?The data retrieved by any SQL SELECT statement is in the form of a table.We want to use this TABLE in FROM clause of other Select statement.Method？Subquery in the FROM clause (Fig 3.11)Creating Views2022/7/18Integ

8、rity,Views,Security,and Catalogs97.2 Creating ViewsView Table ( or View )DefinitionIt is a table that results from a subquery, but which has its own nametable name & attributes nameIt can be used in most ways as a Base Table created by SQL CREATE TABLE statement2022/7/18Integrity,Views,Security,and

9、Catalogs107.2 Creating ViewsView Table ( or View )Propertyno data storage in its own right, just window on data it selects fromso, it is regarded as a Virtual TableWeaknesslimits to View Updates2022/7/18Integrity,Views,Security,and Catalogs117.2 Creating ViewsUpdatable and Read-Only ViewsThe problem

10、How do we translate updates on the View into changes on the base tables?Figure 7.15Restrictions on the Subquery Clause for an Updatable View2022/7/18Integrity,Views,Security,and Catalogs12A view table is said to be updatable when the following conditions hold for its Subquery clause.The FROM clause

11、of the Subquery must contain only a single table, and if that table is a view table it must also be an updatable view table.Neither the GROUP BY nor HAVING clause is present.The DISTINCT keyword is not specified.The WHERE clause does not contain a Subquery that references any table in the FROM claus

12、e, directly or indirectly via views.All result columns of the Subquery are simple column names: no expressions, no column name appears more than once.2022/7/18Integrity,Views,Security,and Catalogs137.3 SecurityThe Grant Statement in SQLused by the owner of a tablethe owner of a table has ALL PRIVILE

13、GES on the table.other user can not access the table if it does not have the PRIVILEGES on the table.column privileges can be implemented through views.GRANT ALL PRIVILEGES|privilege , privilege ON TABLE tablename | viewnameTO PUBLIC | user-name , user-name WITH GRANT OPTION 2022/7/18Integrity,Views,Security,and Catalogs14The Grant Statement in SQL (cont.)privilegesSELECT, DELETE, INSER