Chapter 4: Advanced SQLChapter 4: Advanced SQLBuilt-in Data Types in SQLBuild-in Data Types in SQL (Cont.)User-Defined TypesDomain ConstraintsLarge-Object TypesIntegrity ConstraintsConstraints on a Single RelationNot Null ConstraintThe Unique ConstraintThe check clauseThe check clause (Cont.)Referential IntegrityReferential Integrity in SQL – ExampleReferential Integrity in SQL – Example (Cont.)Referential Integrity in SQL and deletesAssertionsAssertion ExampleSlide 20TriggersTrigger ExampleTrigger Example in SQL:1999Triggering Events and Actions in SQLExternal World ActionsExternal World Actions (Cont.)When Not To Use TriggersAuthorization Specification in SQLJDBCJDBC CodeJDBC Code (Cont.)JDBC Code DetailsJDBCProcedural Extensions and Stored ProceduresAuthorizationPrivileges in SQLAuthorization and ViewsView ExampleGranting of PrivilegesAuthorization Grant GraphSecurity Specification in SQLSlide 42Privilege To Grant PrivilegesRolesRevoking Authorization in SQLRevoking Authorization in SQL (Cont.)Limitations of SQL AuthorizationEnd of ChapterDatabase System Concepts, 5th Ed.©Silberschatz, Korth and SudarshanSee www.db-book.com for conditions on re-use Chapter 4: Advanced SQLChapter 4: Advanced SQL©Silberschatz, Korth and Sudarshan4.2Database System Concepts, 5th Edition, Oct 5. 2006Chapter 4: Advanced SQLChapter 4: Advanced SQLSQL Data Types and SchemasIntegrity Constraints AuthorizationEmbedded SQLDynamic SQLFunctions and Procedural Constructs**Recursive Queries**Advanced SQL Features**©Silberschatz, Korth and Sudarshan4.3Database System Concepts, 5th Edition, Oct 5. 2006Built-in Data Types in SQL Built-in Data Types in SQL date: Dates, containing a (4 digit) year, month and dateExample: date ‘2005-7-27’time: Time of day, in hours, minutes and seconds.Example: time ‘09:00:30’ time ‘09:00:30.75’timestamp: date plus time of dayExample: timestamp ‘2005-7-27 09:00:30.75’interval: period of timeExample: interval ‘1’ daySubtracting a date/time/timestamp value from another gives an interval valueInterval values can be added to date/time/timestamp values©Silberschatz, Korth and Sudarshan4.4Database System Concepts, 5th Edition, Oct 5. 2006Build-in Data Types in SQL (Cont.)Build-in Data Types in SQL (Cont.)Can extract values of individual fields from date/time/timestampExample: extract (year from r.starttime) Can cast string types to date/time/timestamp Example: cast <string-valued-expression> as dateExample: cast <string-valued-expression> as time©Silberschatz, Korth and Sudarshan4.5Database System Concepts, 5th Edition, Oct 5. 2006User-Defined TypesUser-Defined Typescreate type construct in SQL creates user-defined typecreate type Dollars as numeric (12,2) final create domain construct in SQL-92 creates user-defined domain typescreate domain person_name char(20) not nullTypes and domains are similar. Domains can have constraints, such as not null, specified on them.©Silberschatz, Korth and Sudarshan4.6Database System Concepts, 5th Edition, Oct 5. 2006Domain ConstraintsDomain ConstraintsDomain constraints are the most elementary form of integrity constraint. They test values inserted in the database, and test queries to ensure that the comparisons make sense. New domains can be created from existing data typesExample: create domain Dollars numeric(12, 2) create domain Pounds numeric(12,2)We cannot assign or compare a value of type Dollars to a value of type Pounds. However, we can convert type as below (cast r.A as Pounds) (Should also multiply by the dollar-to-pound conversion-rate)©Silberschatz, Korth and Sudarshan4.7Database System Concepts, 5th Edition, Oct 5. 2006Large-Object TypesLarge-Object TypesLarge objects (photos, videos, CAD files, etc.) are stored as a large object:blob: binary large object -- object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system)clob: character large object -- object is a large collection of character dataWhen a query returns a large object, a pointer is returned rather than the large object itself.©Silberschatz, Korth and Sudarshan4.8Database System Concepts, 5th Edition, Oct 5. 2006Integrity ConstraintsIntegrity ConstraintsIntegrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency. A checking account must have a balance greater than $10,000.00A salary of a bank employee must be at least $4.00 an hourA customer must have a (non-null) phone number©Silberschatz, Korth and Sudarshan4.9Database System Concepts, 5th Edition, Oct 5. 2006 Constraints on a Single Relation Constraints on a Single Relation not nullprimary keyuniquecheck (P ), where P is a predicate©Silberschatz, Korth and Sudarshan4.10Database System Concepts, 5th Edition, Oct 5. 2006Not Null Constraint Not Null Constraint Declare branch_name for branch is not null branch_name char(15) not nullDeclare the domain Dollars to be not null create domain Dollars numeric(12,2) not null©Silberschatz, Korth and Sudarshan4.11Database System Concepts, 5th Edition, Oct 5. 2006The Unique ConstraintThe Unique Constraintunique ( A1, A2, …, Am)The unique specification states that the attributes A1, A2, … Amform a candidate key.Candidate keys are permitted to be null (in contrast to primary keys).©Silberschatz, Korth and Sudarshan4.12Database System Concepts, 5th Edition, Oct 5. 2006The check clauseThe check clausecheck (P ), where P is a predicateExample: Declare branch_name as the primary key for branch and ensure that the values of assets are non-negative.create table branch (branch_name char(15), branch_city char(30), assets integer, primary key (branch_name), check (assets >= 0))©Silberschatz, Korth and Sudarshan4.13Database System Concepts, 5th Edition, Oct 5. 2006The check clause (Cont.)The check clause (Cont.)The check clause in SQL-92 permits domains to be restricted:Use check clause to ensure that an hourly_wage domain allows only values greater than a specified value.create domain hourly_wage numeric(5,2)constraint value_test check(value > = 4.00)The domain has a constraint that ensures that the hourly_wage is greater than 4.00The clause constraint