DATABASE7

2020-03-01 272浏览

  • 1.DATABASE Principles, Programming, and Performance Patrick O’Neil and Elizabeth O’Neil Lectured by Dr. Lican HuangEmail:licanhuang@zist.edu.cn huang_lican@yahoo.co.ukhttp://virgo.sourceforge.net/licanhuang.htmlZhejiang Sci-Tech University Lican Huang 1
  • 2.7 Integrity Constraints CREATE TABLE tablename ((colname datatype [DEFAULT {default _constant NULL}] [col_constr {col_constr. . .}] table_constr {, {colname datatype [DEFAULT {defa ult_constant NULL}] [col_constr {col_constr. . .}] table_constr} . . .}); Zhejiang Sci-Tech University Lican Huang 2
  • 3.7 Integrity Constraints          Def 7.1.2. Column Constraints. The col_constr form that constrains a single column valuefollows:{NOT NULL [CONSTRAINT constraintname] UNIQUE PRIMARY KEY CHECK (search_cond) REFERENCES tablename [(coln ame) ] [ON DELETE CASCADE]} Zhejiang Sci-Tech University Lican Huang 3
  • 4.7 Integrity Constraints          Def 7.1.3. Table Constraints. The table_constr form that constrains multip le columns at oncefollows:[CONSTRAINT constraintname] {UNIQUE (colname {, colname. . .}) PRIMARY KEY (colname {, colname. . .}) CHECK (search_condition) FOREIGN KEY (colname {, colname. . .}) REFERENCES tablename [(colname {, coln ame. . .})] [ON DELETE CASCADE]} Zhejiang Sci-Tech University Lican Huang 4
  • 5.7 Integrity Constraints          example 7.1.2 create table orders ( ordno integer not null, month c har(3), cid char(4) not null, aid char(3) not null, pid char(3) not null, qty integer not null che ck(qty >= 0), dollars float default 0.0 check(dollars >= 0. 0), primary key ( ordno ), foreign key (cid) references customers, foreign key (aid) references agents, foreign key (pid) references products); Zhejiang Sci-Tech University Lican Huang 5
  • 6.7 Integrity Constraints   Referential Integrity See pg. 419, Definition 7.1.4. We define an ordered set of columns F to make up a Foreign key in table T1 to match with an ordered set of columns P in table T2. (foreign key . . . references . . .) Zhejiang Sci-Tech University Lican Huang 6
  • 7.7 Integrity Constraints   Referential Integrity A referential integrity constraint is in force if the columns of a foreign key F in any row of T1 must either (1) have a null value in at least one column that permits null values, or (2) have no null values and equate to the values of a primary key P on some row of T2.  Zhejiang Sci-Tech University Lican Huang 7
  • 8.7 Integrity Constraints   Referential Integrity Thus if we want "optional participatio n" in a relationship from the referenci ng table, must allow at least one colu mn of F to be nullable. Zhejiang Sci-Tech University Lican Huang 8
  • 9.7 Integrity Constraints               ALTER TABLE tblname [ADD ({colname datatype [DEFAULT {default_const NULL}] [col_constr {col_constr...}] table_constr} -- choice of colname-def. or table_const r {, ...})] -- zero or more added colname-defs. or table_c onstrs. [DROP {COLUMN columnname (columnname {, columnname …})}] [MODIFY (columnname data-type [DEFAULT {default_const NULL}] [[NOT] NULL] {, . . .})] -- zero or more added colname-defs. [DROP CONSTRAINT constr_name] [DROP PRIMARY KEY] [disk storage and other clauses (not covered, or deferred)] [any clause above can be repeated, in any order] [ENABLE and DISABLE clauses for constraints]; Zhejiang Sci-Tech University Lican Huang 9
  • 10.7 Integrity Constraints        CREATE TRIGGER trigger_name BEFORE AFTER {INSERT DELETE UPDATE [OF colna me {, colname...}]} ON tablename [REFERENCING corr_na me_def {, corr_name_def...}] [FOR EACH ROW FOR EACH STATEME NT] [WHEN (search_condition)] {statement -action (single state ment) BEGIN ATOMIC statement; { stateme nt;...} END} -- action (mult. stmts.) Zhejiang Sci-Tech University Lican Huang 10
  • 11.7 Integrity Constraints  Views Idea is that since a Select statement looks like a Virtual Table, want to be able to use this table in FROM clause of other Select. Zhejiang Sci-Tech University Lican Huang 11
  • 12.7 Integrity Constraints      Create view agentorders (ordno, mon th, cid, aid, pid, qty, charge, aname, acity, percent) as select o.ordno, o.month, o.ci d, o.aid, o.pid, o.qty, o.dollars, a.aname, a.city, a.percent from orders o, agents a wh ere o.aid = a.aid; Zhejiang Sci-Tech University Lican Huang 12
  • 13.7 Integrity Constraints     7.3 Security. Basic SQL (X/Open) Sta ndard. pg 443 GRANT {ALL PRIVILEGES privilege {,privilege…}} on [TABLE] tablename viewna me TO {PUBLIC user-name {, use r-name…}} [WITH GRANT OPTION] Zhejiang Sci-Tech University Lican Huang 13
  • 14.7 Integrity Constraints    7.4 System Catalogs. Ideaclear:all objects created by SQL commands are listed as objects in tables maintained by the system. Oracle callsthis:data dictionary The names of these tables are very non-standard, but at least there is a table for tables (ALL_TABLES, DBA_TABLES, and USER_TABLES in Oracle) and a table for columns (e.g., USER_TAB_COLUMNS). Zhejiang Sci-Tech University Lican Huang 14
  • 15.7 Integrity Constraints In Oracle, USER_TABLES, for example,contains:number of rows, and disk spa ce usage statistics, such as average r ow length in bytes, etc. DESCRIBE command to find columnname s & columntypes of a user table. describe customers; Zhejiang Sci-Tech University Lican Huang 15