SQL1999 standards NCST 2001 SQL1999 standards Introduction SQL1999

SQL-1999 standards NCST 2001

SQL-1999 standards Introduction : SQL-1999 • Previously known as SQL 3 standards – the third generation SQL. • Superset of SQL 92 – i. e maintaining the upward compatibility. – Significant enhancement over SQL 92 • Making SQL object oriented. • As well some other new features too. . . NCST 2001 2

SQL-1999 standards SQL 99 Overview • Object-Relational Extensions – – – • • User defined data types Reference types Collection types Large object supports Table hierarchies Triggers Stored Procedures and user defined functions Recursive queries OLAP extensions NCST 2001 3

SQL-1999 standards SQL 99 Overview • • SQL Procedural contsructs Update through unions and joins SQLJ and many other stuff. . NCST 2001 4

SQL-1999 standards • Is a multi-part standard – – – – Part 1: SQL/Framework Part 2: SQL/Foundation Part 3: SQL/CLI Part 4: SQL/PSM Part 5: SQL/Bindings Part 7: SQL/Temporal Part 9: SQL/MED (Management of External Data) Part 10: SQL/OLB (Object Language Bindings) NCST 2001 5

SQL-1999 standards SQL/Framework • Overview – Provides an overview of the complete standard – Defines how different parts fit together – Contains common specifications(e. g. definitions, concepts, conventions) • Conformance NCST 2001 6

SQL/Foundation SQL-1999 standards • The epicentre of the SQL 1999 standard. • Traditional SQL + Object Oriented SQL. • Traditional SQL includes – – – Data Types Predicates Semantics Security Active Database • SQL/Foundation adds on the object oriented flavour to tradtional SQL NCST 2001 7

SQL-1999 standards SQL: 1999 Data types NCST 2001 8

SQL-1999 standards Object-Relational extensions to SQL • User defined data types – ADTs – row objects – distinct types • Type constructors – row types – reference types • Collection types – sets , lists , multisets NCST 2001 9

SQL-1999 standards Object-Relational extensions to SQL • User defined methods, functions and procedures. • Typed tables and views – table hierarchies – view hierarchies NCST 2001 10

SQL-1999 standards User Defined Types (UDT) • aka Structured Types • Abstract data types(ADT’s), named row types, and distinct types • Used in the same way as built in data types. • For e. g – the column in the relational table may be defined as taking values of user defined types, as well as built in types. NCST 2001 11

distinct UDT SQL-1999 standards • Simplest form of user defined type in SQL 1999. • E. g. – CREATE DISTINCT TYPE us_dollar AS DECIMAL(9, 2) – CREATE DISTINCT TYPE canadian_dollar AS DECIMAL(9, 2) • Treating us_dollar and canadian_dollar as same will result in an error even though each type has same representation. (strong typing) Error SELECT …… WHERE CDN. TOTAL > USA. TOTAL Proper way SELECT …. WHERE CDN. TOTAL = CDN_DOLLAR(USA. TOTAL) NCST 2001 12

SQL-1999 standards User defined ADT’s • A abstract data type definition encapsulates attributes and operations in a single entity. • For e. g CREATE TYPE note ( sender char (20), receiver char(20), sdate, contents blob (1 M), function subject(note) returns varchar(256) ) NCST 2001 13

SQL-1999 standards Encapsulation • Access to ADT attributes is restricted to functions • no distinction between stored or virtual attribute • for every attribute, an observer and mutation function will be generated FUNCTION sender(note) returns char(20) FUNCTION sender(note, char(20)) returns note • physical representation of ADT can be changed without affecting any application NCST 2001 14

SQL-1999 standards ADT attributes • Have name and type • types of attributes can be pre defined or any other user defined type(distinct types, ADT) CREATE TYPE address( street char(30, city char(30), zip integer); CREATE DISTINCT TYPE bitmap AS BLOB; CREATE TYPE real_esate( rooms integer, size decimal(8, 2), location address, front-view-image bitmap); • Have optional defaults NCST 2001 15

SQL-1999 standards Creating instances of ADTs • Created by system supplied constructor function • generated automatically when ADT is defined • returns a new instance when attribute is initialized to its default value • signature: type_name() -> type_name NCST 2001 16

SQL-1999 standards Accessing attributes • Observer and mutation functions are used • automatically generated when ADT is defined • DOT notation -- syntactic sugar for invocation of functions BEGIN DECLARE r real_estate; SET r. . size = 2000. 00; set y = r. size ; set z = r. . location. . city; NCST 2001 /* same as size(r, 2000. 00) */ /* same as size ( r ) */ /* same as city(location ( r ) ) */ 17

SQL-1999 standards Defining visible interface • Public • private • protected CREATE type employee ( public name char(20), e_address, hiredate, private base_sal decimal(8, 2), private commision decimal(8, 2), public function salary ( p employee) returns decimal < code here > ); NCST 2001 18

Virtual attributes • SQL-1999 standards Do not have stored values • modeled with pair of user defined functions(visible) • CREATE type employee ( public name char(20), e_address, hiredate, private base_sal decimal(8, 2), private commision decimal(8, 2), public function salary ( p employee) returns decimal <code here> public function working_years( p employee) returns integer <observer func code here> public function working years (p employee, year integer) returns employee <mutator code here> NCST 2001 19

SQL-1999 standards Initializing instances of ADTs • ADT instance is generated by system defined constructor function • ADT instance is modified by mutator functions • user can define any number of ‘constructor’ functions with parameters to initialize attributes • CREATE FUNCTION real_estate( r integer, s decimal(8, 2)) RETURNS real_estate BEGIN DECLARE re real_estate; SET re = real_estate( ); SET re. . rooms = r; SET re. . size = s; END; NCST 2001 20

SQL-1999 standards Use of ADTs • ADTs can be used whenever other types can be used in SQL • type of attributes of other ADTs • type of parameters of functions and procedures • type of SQL variable • type of domain or columns in tables NCST 2001 21

SQL-1999 standards Inheritance • All attributes are inherited • name conflicts are resolved by function resolution • precedence is defined by the order of supertypes in the UNDER clause • Overloading • function resolution NCST 2001 22

SQL-1999 standards • ADTs can be subtype of one or more ADTs • ADTs inherit structure and behavior from their supertypes CREATE TYPE real_estate … CREATE TYPE condo UNDER real_estate. . . CREATE TYPE house UNDER real_estate. . . CREATE TYPE vila UNDER HOUSE … CREATE TYPE vacation_property UNDER real_estate. . . CREATE TYPE summer_lodge UNDER vacation_property, house … • Additional functions, attributes can be defined • Functions on supertypes can be overloaded • instance of subtype can be used in every context where supertype instance can be used NCST 2001 23

SQL-1999 standards Row Types • Create table employees ( name char(40), address row ( street char(30), city char(30), zip row ( base char(4), ext char(4)) ) ) NCST 2001 24

SQL-1999 standards Reference Types. . . • Create row type account_t( acctno int, cust ref (cust_t), type char(1), opened date, balance double precision); • create table account of account_t(primary key acctno , scope for cust is customer); • references can be scoped NCST 2001 25

SQL-1999 standards Type constructors • Create table employees ( id integer primary key, name varchar(30), address row ( street char(20), city char(2). . ), manager integer references employees, projects set(integers), children list(person), hobbies set( varchar (20)) ) NCST 2001 26

SQL-1999 standards Queries on collection types • Select name from employees e where ‘travel’ in (select * from table (e. hobbies) Tmp) and 5 > (select count(*) from table (e. children ) tmp 2) • instances of set, lists and multisets can be treated as tables for queries • final result of query is always a table NCST 2001 27

SQL-1999 standards Other new data types. . • Large Objects – Character Large Objects (CLOB) • for e. g. mahabharta CLOB(25 M) – Binary Large Objects (BLOB) • for e. g. Arjun_photo BLOB(1 M) – Can store data upto gigabytes. . . • only ‘=‘ and ‘<>’ predicates • No group by & order by • No distinct • No primary key or foreign key • ARRAY – Variable length arrays – for e. g. marks integer ARRAY [50] – no multidimensional arrays. NCST 2001 28

SQL-1999 standards Other new data types. . • BOOLEAN – Boolean literals • TRUE • FALSE • UNKNOWN – COL 1 AND (COL 2 OR NOT COL 3) IS NOT FALSE NCST 2001 29

SQL-1999 standards New Predicates • SIMILAR - UNIX like regular expression – More powerful than LIKE – for e. g. NAMES SIMILAR TO ‘(SQL-(86|89|92|99)) | (SQL(1|2|3))’ • DISTINCT - accounts for null values – differs from equality test. – (10, ‘abc’, null) = (10, ‘abc’, null) is UNKNOWN – (10, ‘abc’, null) IS DISTINCT FROM (10, ‘abc’, null) is FALSE NCST 2001 30

SQL-1999 standards New Semantics • • View updation Recursion Locators Savepoints NCST 2001 31

SQL-1999 standards New Security features - Roles • • • Privileges assigned to authorization IDs Privileges assigned to roles Roles assigned to authorization Ids Roles assigned to other roles Improves manageability of databases. NCST 2001 32

SQL-1999 standards Activate Database - Triggers • Database object tightly bound to the table. • Fires when certain even happens on table – Per statement or row activation – Before or after statement or row action CREATE TRIGGER upd_balance BEFORE INSERT ON aacount_history /* event */ REFERENCING NEW AS ta FOR EACH ROW WHEN (ta. TA_TYPE = ‘W’) / *condition */ UPDATE accounts SET balance = balance - ta. amount WHERE account_# = ta. account_#; /* action */ NCST 2001 33

SQL-1999 standards Uses of Triggers • • • Enforces the business rules Cross reference other tables Maintain summary, audit or mirror tables validate the input data External actions such as E-Mail notifications. NCST 2001 34

SQL-1999 standards SQL/PSM • PSM stands for Persistent Stored Modules • PSM 99 specified: – SQL-server modules – Procedural extensions NCST 2001 35

SQL-1999 standards SQL/PSM : SQL-server modules • CREATE MODULE modulename [options] routine-def; . . NCST 2001 36

SQL-1999 standards SQL/PSM : Procedural Extensions • • Compound Statement SQL variable declaration Assignment declaration CASE statement IF statement ITERATE and LEAVE statements LOOP, WHILE, REPEAT, and FOR statements. NCST 2001 37

SQL-1999 standards SQL/Bindings • Embedded SQL – ADA, C, COBOL, FORTRAN, PASCAL. . . • Dynamic SQL • Direct SQL NCST 2001 38

SQL-1999 standards Embedded SQL • An embedded host language program is transformed into the host language module and SQL module. Embedded SQL Processor Host language NCST 2001 SQL module 39

SQL-1999 standards Dynamic SQL • Needed when the tables, columns or predicates are not known when the application is written • Execute statement immediately (once) s = “INSERT INTO people VALUES(‘abc’, ’. . . ) EXEC SQL EXECUTE IMMEDIATE : s; • Execute statement more than once EXEC SQL PREPARE stmt FROM : s; EXEC SQL EXECUTE stmt; NCST 2001 40

SQL-1999 standards Dynamic SQL • Dynamic parameter makers – – – s- “INSERT INTO people VALUES(? , . . ) EXEC SQL PREPARE stmt FROM : s; lname=“James” fname=“Bond” EXEC SQL EXECUTE stmt USING : lname, : fname, . . . ; NCST 2001 41

SQL-1999 standards Direct SQL • Impelementation defined mechanism for executing direct SQL statements – In effect, prepared immediately before execution – Cannot issue dynamic SQL using direct SQL NCST 2001 42

SQL-1999 standards SQL/CLI • An alternative mechanism for invoking SQL from application programs – Similar to dynamic SQL • CLI does not require pre-compilation of the application programs • Based on – CLI from SQL Access Group (SAG) and X/Open – ODBC NCST 2001 43

SQL-1999 standards SQL/CLI • An alternative mechanism for invoking SQL from application programs – Similar to dynamic SQL • CLI does not require pre-compilation of the application programs • Based on – CLI from SQL Access Group (SAG) and X/Open – ODBC NCST 2001 44

SQL-1999 standards SQL/CLI Application CLI call handler Database Manager Database NCST 2001 45

SQL-1999 standards SQL/OLB • SQLJ – Part 0 • Embedded SQL in Java – Part I • Java static methods as SQL UDFs and stored procedures in SQL • Currently a working draft – Part 2 • Use of classes to define SQL types NCST 2001 46

SQL-1999 standards SQL/MED • Still undergoing revisions • Purpose is to tie SQL with the management of data outside of the database (such as files) – Adds new data type : datalink – Abstract LOB type: used to define routines that are allowed on a LOB. NCST 2001 47

SQL-1999 standards And on the way is SQL 4. . . . NCST 2001 48

SQL-1999 standards References • UNDERSTANDING THE NEW SQL: A COMPLETE GUIDE by Jim Melton • http: //www. cse. iitb. ernet. in: 8000/proxy/db/~db ms/Data/Papers-Other/SQL 1999/. . . THANK YOU. . . NCST 2001 49