Module 4: Advanced SQLChapter 4: Advanced SQLBuild-in data types in SQLBuild-in Data Types in SQL (Cont.)User Defined TypesDomain ConstraintsIntegrity 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.)AssertionsAssertion ExampleSlide 18AuthorizationAuthorization (Cont.)Authorization and ViewsView ExampleView Example (Cont.)Authorization on ViewsAuthorization Specification in SQLPrivileges in SQLPrivilege To Grant PrivilegesRolesRevoking Authorization in SQLRevoking Authorization in SQL (Cont.)Limitations of SQL AuthorizationGranting of PrivilegesAuthorization Grant GraphTriggersTrigger ExampleTrigger Example in SQL:1999Triggering Events and Actions in SQLStatement Level TriggersExternal World ActionsExternal World Actions (Cont.)Triggers in MS-SQLServer SyntaxWhen Not To Use TriggersEmbedded SQLExample QueryEmbedded SQL (Cont.)Updates Through CursorsDynamic SQLProcedural Extensions and Stored ProceduresFunctions and ProceduresSQL FunctionsSQL MethodsSQL Functions and Procedures (cont.)External Language Functions/ProceduresExternal Language Routines (Cont.)Security with External Language RoutinesProcedural ConstructsProcedural Constructs (Cont.)Procedural Constructs (cont.)Other SQL FeaturesSchemas, Catalogs, and EnvironmentsTransactionsTransactions (Cont.)ODBCODBC (Cont.)ODBC CodeODBC Code (Cont.)Slide 67More ODBC FeaturesODBC Conformance LevelsJDBCJDBC CodeJDBC Code (Cont.)JDBC Code DetailsPrepared StatementEnd of ChapterDatabase System Concepts.©Silberschatz, Korth and SudarshanSee www.db-book.com for conditions on re-use Module 4: Advanced SQLModule 4: Advanced SQL©Silberschatz, Korth and Sudarshan4.2Database System Concepts, 5th Ed.Chapter 4: Advanced SQLChapter 4: Advanced SQLBasic Types in SQLIntegrity Constraints AssertionsAuthorizationEmbedded SQLDynamic SQLTriggersFunctions and ProceduresOther SQL Features©Silberschatz, Korth and Sudarshan4.3Database System Concepts, 5th Ed.Build-in data types in SQL Build-in data types in SQL date. Dates, containing a (4 digit) year, month and dateExample: date ‘2001-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 ‘2001-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 Ed.Build-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 Ed.User 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 Ed.Domain 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E.g. 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 Ed.Integrity 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.8Database System Concepts, 5th Ed. 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.9Database System Concepts, 5th Ed.Not 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.10Database System Concepts, 5th Ed.The 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 non null (in contrastTo primary keys).©Silberschatz, Korth and Sudarshan4.11Database System Concepts, 5th Ed.The 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.12Database System Concepts, 5th Ed.The 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 value-test is optional; useful to indicate which constraint an update violated.©Silberschatz, Korth and Sudarshan4.13Database System Concepts, 5th Ed.Referential IntegrityReferential IntegrityEnsures that a value that