1 / 26

Object-Relational SQL

Object-Relational SQL. Introduction: Object-Relational SQL (ORSQL) capabilities Objects and Tables OR-Create, -Insert, -Update, -Select User Defined Functions. ORSQL capabilities. Definition of table will enlarged with user-defined types:

udell
Download Presentation

Object-Relational SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Object-Relational SQL • Introduction: Object-Relational SQL (ORSQL) capabilities • Objects and Tables • OR-Create, -Insert, -Update, -Select • User Defined Functions Datenbanken 2

  2. ORSQL capabilities Definition of table will enlarged with user-defined types: Columns that look like multi-valued fields or have any internal structure (like a record) are not permitted (non 1NF) Example: eid ename position dependents dep_name dep_age e01 Smith, John Agent Michael J. 9 Susan R. 7 e02 Andrews, David Superintendent David M. Jr. 10 e03 Jones, Franklin Agent Andrew K. 11 Mark W. 9 Louisa M. 4 Datenbanken 2

  3. ORSQL capabilities In OO-modeling there are methods defined to operate on the objects. The only possibility to operate on objects is to use this class-specific methods. There is no other posibiltiy to work on the data (encapsulation). In ORDBMS there exists the language ORSQL (like SQL in relational databases). This language allows to work on all defined multi-valued and structured tables. In addition you can define functions belonging to the defined OR tables (encapsulation). Datenbanken 2

  4. Objects and Tables An object type has attributes of various types, analogous to columns of a table. Example (Oracle): create type name_t as object -- „Create Object Type“ ( lname varchar(10), -- last name fname varchar(10), -- first name mi char(1) -- middle initial ); / -- SQL*Plus Datenbanken 2

  5. Objects and Tables After making the SQL statement „create type“ you can use this new defined type like an attribute type. Example (Oracle): create table teachers -- „Create Table Scheme“ ( tid int, -- identifier tname name_t, -- object type defined above room int, -- room number primary key (tid) ); There is still no object exisiting from object type name_typ. Datenbanken 2

  6. Objects and Tables To insert a row into the table defined above you can use the insert statement like in relational SQL, extended with the possibility of using object types Example (Oracle): insert into teachers values ( 1234, name_t('Einstein', 'Albert', 'E'), 120 ); Object constructor Datenbanken 2

  7. Objects and Tables After the insert-statement used above the table content can be shown as follows: Example (Oracle): tname lname fname mi Einstein Albert E room 120 tid 1234 Datenbanken 2

  8. Objects and Tables To select values of the object type we use a „dot“-notation: Example (Oracle): select t.tid from teachers t where t.room = 120; -- normal SQL-select select t.tid, t.tname.fname, t.tname.lname from teachers t where t.room = 120; -- extended SQL select tid, tname.fname, tname.lname from teachers where room = 120; -- doesn´t work We must use the alias t for table teachers in the extended SQL. Datenbanken 2

  9. Objects and Tables It is possible to use the object type-definition within another object type-definition. Example: drop type person_t; -- drop a possibly existing type create type person_t as object ( ssno number(5), pname name_t, -- must be defined first age number(5) ); person_typ is dependent on name_typ, that means you cannot drop name_typ before person_typ is dropped. Datenbanken 2

  10. Objects and Tables A table is called object table if its rows are of object type, that is, each of its rows consists of an object of that type. Example: create table people of person_t ( primary key(ssno) ); Datenbanken 2

  11. Objects and Tables An example of a state of the table people: select value(p) people nameless top-level column holding the row object select * column objects row objects named columns (also known as top-level attributes) ssno pname age pname.lname pname.fname pname.mi attributes within pname row 1 123550123 March Jacquelin E 23 row 2 245882134 Delaney Patrick X 59 row 3 023894455 Sanchez Jose F 30 Datenbanken 2

  12. Objects and Tables (Select-statements) select p.age from people p where p.ssno = 245882134 (like relational statements) select p.pname from people p where p.age > 35 The columns will delivered in the form name_t(´Sanchez´, ´Jose´, ´F´) select * from people p where p.age > 35 delivers the top-level attributes (row objects) ssno pname(lname,fname,mi) age --------- ------------------------------- ---- 245882134 name_typ(´Delaney´,´Patrick´,´X´)59 023894455 name_typ(´Sanchez´,´Jose´,´F´) 30 Datenbanken 2

  13. Objects and Tables (Select-statements) select value(p) from people p where age > 35 The nameless top-level column will be shown. Value(p) doesn´t mean VALUES within the insert-statement. Value(p) (ssno, pname(lname,fname,mi), age) -------- ------ ---------------------- ---- person_t (245882134, name_t(´Delaney´,´Patrick´,´X´), 59) person_t (023894455, name_t(´Sanchez´,´Jose´,´F´), 30) Person_t is the object-constructor with which the table was defined, it is another result than in „select *“-queries. New functionality is given with this object constructor functionality. Datenbanken 2

  14. Objects and Tables (Select-statements) select value(p) from people p where p.pname = name_t(´Sanchez´,´Jose´,´F´) The object constructor „name_t(´Sanchez´,´Jose´,´F´)“ is allowed within the where-condition. Value(p) (ssno, pname(lname,fname,mi), age) -------- ------ ---------------------- ---- person_t (023894455, name_t(´Sanchez´,´Jose´,´F´), 30) Datenbanken 2

  15. Objects and Tables (Select-statements) select p.pname, p.age from people p where p.pname.fname like ‘Pat%‘= and p.age > 50; Nested dot notation is allowed. The first name starts with Pat and the age is over 50. pname(lname,fname,mi) age --------------------- --- name_t(´Delaney´,´Patrick´,´X´) 59 Nested dot notation can be used in any ORACLE select-statement to access an attribute of an object that is an attribute of an object ... Datenbanken 2

  16. Objects and Tables (Select-statements) Remark: You have to use the fully qualified name with an alias if you use an attribute accessing expression. select pname.fname from people This is not a top-level attribute -> This statement doesn´t work. select people.pname.fname from people Alias must be used because of attributes below the top level. -> This statement doesn´t work Datenbanken 2

  17. Objects and Tables (create and insert) create table scientists of name_t (primary key (lname)); Create a table with name scientists of object type name_t (defined above). insert into scientists select p.pname from people p; Insert with selection of all pnames of people. All rows of people table will be inserted. insert into scientists values (‘Einstein’, ‘Albert’, ‘E’); Insertion of one row into the table scientists with direct values. Datenbanken 2

  18. Objects and Tables (insert) update scientists s set s = name_t(‘Eisenstein’, ‘Andrew’, ‘F’) where values(s) = name_t(‘Einstein’, ‘Albert’, ‘E’); Update of rows using object constructor name_t. insert into people values (123441998, name_t(’Einstein’, ’Albert’, ’E’), 100); Insertion of one row into the table scientist with direct values. insert into people values (321341223, null, null); insert into people values (ssno)(321341223); Equivalent statements to insert a row with null-values. Pname and age have to be optional attributes. A qualifier in the second example is not allowed. Datenbanken 2

  19. Objects and Tables (update) update people p set p.pname = name_t(‘Gould’, ‘Ben’, null) where ssno = 321341223; Update of the row with null values. The middle initial is still null. update people p set p.pname.mi = ‘C’ where ssno = 321341223; Update the middle initial of the row above. update people p set p = Person_t(332341223, name_t(‘Gould’, ‘Glen’, ‘A’), 55) where ssno = 321341223; Update of one person with row object constructor. The primary key ssno can also be updated. Datenbanken 2

  20. User Defined Functions • User Defined Functions (UDF) can be used to bind functions to objects. • The definition of UDF follows in two steps: • Definition of the function header • Definition of the function body • The function header will be defined together with the attributes of the object type. • The function body will be defined in a special command with a reference to the header. Datenbanken 2

  21. User Defined Functions Example (Persons and dependent Persons): create type name_t as object -- „Create Object Type“ ( lname varchar(30), -- last name mi char(1), -- middle initial fname varchar(30) -- first name ); / create type person_t as object ( ssno int, pname name_t, -- must be defined first age int ); / create type depPerson_t as table of person_t; / Datenbanken 2

  22. User Defined Functions Example (Function Header): create type Employee_t as object ( ENR int, Person person_t, depPerson depPerson_t, member function NumberOfDep return integer, member function BigBoss return varchar ); / The function headers will be defined after all attribute definitions. They include the key words “member function” and “return” with a data type. Datenbanken 2

  23. User Defined Functions Example (Function Body): create type body Employee_t as member function NumberOfDep return integer is begin return self.depPerson.count; end NumberOfDep; member function BigBoss return varchar is begin if self.depPerson.count > 2 then return 'Big Boss'; else return 'Boss'; end if; end BigBoss; end; / The body of the functions get the same name as the above defined object type. The name and the return type of the function has to be written again. Within “begin” and “end” the function is defined. One return value is needed. Datenbanken 2

  24. User Defined Functions Example (Table Definition): create table Employee of Employee_t ( primary key (ENR) ) nested table depPerson store as dep_tab; The table definition makes the objects real! The primary key must be defined in the table definition. The “nested table” clause allows the use of tables within the table. Datenbanken 2

  25. User Defined Functions Example (Insertion): insert into Employee values (1, person_t(11, name_t('Josef', 'R', 'Ewing'), 59), depPerson_T(person_t(33, name_t('Franz', 'X', 'Nonsense'), 33), person_t(44, name_t('Uschi', 'K', 'Glas'), 48), person_t(55, name_t('Mika', 'L', 'Most'), 52)) ); insert into Employee values (2, person_t(22, name_t('Karla', 'M', 'Hut'), 34), depPerson_T(person_t(66, name_t('Hans', 'L', 'Moser'), 72), person_t(77, name_t('Paul', 'A', 'Popp'), 41)) ); Insertions will be made like in the examples before! Datenbanken 2

  26. User Defined Functions Example (Selection): select ENR, E.NumberOfDep() from Employee E; ENR E.NUMBEROFDEP() ---------- --------------- 1 3 2 2 The value of the result of the function (method) will be shown as an own column. The alias is mandatory! Example (Selection): select ENR, E.BigBoss() from Employee E; ENR E.BIGBOSS() ---------- ------------ 1 Big Boss 2 Boss The result can go over more than one line in PL/SQL. Datenbanken 2

More Related