Docstoc

Examinations

Document Sample
Examinations Powered By Docstoc
					      4. Basics of object-relational
         databases

= The foils of this chapter are partly foils from chapter 18 of the
  following book

   =A. Kemper, A. Eickler: Datenbanksysteme, Eine Einführung
    6. Auflage, Oldenbourg-Verlag 2006

   =http://www-db.in.tum.de




                                                                      1
Object-relational and extendable
database systems




      =Extensibility
      =SQL:1999
      =Object relational modelling
Concepts of object-relational databases

= Many applications are relational
= Pure object orientation breaks with the relational world
  → Idea:
      =Starting point: relational databases
      =Extension by object-oriented concepts
= SQL1999 (formerly SQL3)
   =standard of object-relational database systems




                                                             3
Concepts of object-relational databases
= Large objects (Large OBjects, LOBs)
   =Data types which permit to store very large attribute values
     for example Multimedia data. The size can reach several
     gigabytes. Often the large objects are included into the
     object-relational concepts of a relational database system,
     although they are „pure" values.
= Set-valued attributes
   =A set of values is assigned to an attribute of a tuple (object).
   =Now it is possible to assign to the students a set-valued
     attribute ProgrLanguageKnowledge.
   =Nesting/Unnesting in the query language




                                                                   4
Concepts of object-relational databases
= Nested relations
   =Nested relations not only allow set-valued attributes but also
    attributes which are relations themselves.
   =Example: in a relation Students an attribute
    passedExaminations represents the set of examination
    tuples.
   =Each tuple of this nested relation again consists of
    attributes, e.g. Grade and Examiner.
= Type declarations
   =Object-relational database systems support the definition of
    application specific types – often called User Defined Types
    (UDTs).
   =Often differentiation between value based (attribute-) and
    object types (row type).
                                                                 5
Concepts of object-relational databases
= References
   = Attributes can have direct references to tuples/objects (same or other
     relations) as value.
   = No longer limited only to the use of foreign keys for the realization of
     relationships.
   = In particular an attribute can also have a set of references as value, thus
     N:M relationships can be represented without a separate relationship.
   = Example: Students.attend is a set of references to Lectures
= Object identity
   = References require that objects (tuples) can be identified unambiguously
     by a constant object identity.
= Path expressions
   = Attributes with references lead inevitably to the necessity to support
     path expressions in the query language.

                                                                                6
Concepts of object-relational databases
= Inheritance
   =The complex structured types can inherit from a super type.
   =Further relations are defined as a subrelation of a super
    relation.
   =All tuples of the super relation are implicitly part of the sub
    relation.
   =Thus the concept of generalization/specialization is realized.
= Operations
   =Assigned to the object types (or not)
   =Simple operations can be implemented directly in SQL
   =More complex operations are realized in a host language
    "externally"
      =Java, C, PLSQL (Oracle), C++, etc.

                                                                      7
Standard in SQL:1999
= SQL2, SQL:1992
   =At the moment: realized standard of the commercial
    relational database systems
   =Attention: different stages of observance
      =Entry level is the weakest stage
= SQL:1999
   =Object-relational extensions
   =Trigger
   =Stored procedures
   =Extended query language
= Unfortunately many systems already realized their own
  proprietary syntax (and semantics)
   =Adjustment to the standard can last

                                                          8
Large Objects:
= CLOB
   = Long text is stored in a Character Large OBject.
   = The advantage over long varchar(...) data types lies in the improved
     efficiency because the database systems offer special procedures (so-
     called Locators) for the access of the application program to the
     database system LOBs.
= BLOB
   = Application data which are not interpreted but only to be stored and/or
     archived are stored in Binary Large Objects.
= NCLOB
   = CLOBs are reduced to text-inputs with 1-Byte Character-Data. For the
     storage of text inputs with special characters (e.g. uni coded text) so-
     called National Character Large Objects (NCLOBs) must be used.
   = In DB2 this data type is called (different to the SQL:1999 standard)
     DBCLOB -- as abbreviation for Double Byte Character Large OBject



                                                                                9
Example of LOBs
CREATE TABLE Professors
  ( PersNo INTEGER PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Status CHARACTER(2) CHECK (Status IN ('C2', 'C3', 'C4')),
    Room INTEGER UNIQUE,
    Photo BLOB(2M),
    Vita CLOB(75K) );


LOB (Vita) STORE AS
                                          Oracle
      ( TABLESPACE Vitas
        STORAGE (INITIAL 50M NEXT 50M) );

                                                                10
= Compression, e.g. DB2:
   =COMPACT
   =NOT COMPACT

= LOCATOR (Implementation concept):
  refers to LOB Data
   =LOB Data are transferred not directly to the Client, only if
     really needed
   =Execution of operations only if unavoidable
      =For example concatenation of CLOB Objects, first of all only logically




                                                                            11
Simple user defined types :
Distinct Types

= Simple data types which can be mapped directly on built-in
  data types

= Goal: prevention of a semantically unreasonable use, e.g.
  addition of grades or similar




                                                               12
Simple user defined types :
Distinct Types

= Example:

  CREATE DISTINCT TYPE GradesType AS DECIMAL (3,2) WITH
      COMPARISONS

  =Only GradesType values can be compared among
   themselves
  =If PrediplomaGrade is of type GradesType, the following
   instruction is not possible :

  SELECT s FROM Students s
  WHERE s.HourlyWages > s.PrediplomaGrade
                                                             13
Simple user defined types :
Distinct Types
CREATE DISTINCT TYPE GradesType AS DECIMAL (3,2) WITH COMPARISONS;

CREATE FUNCTION GradesAverage(GradesType) RETURNS GradesType
   SOURCE AVG(DECIMAL());

CREATE TABLE Examine (
   StudID INTEGER,
   LectNo INTEGER,
   PersNo INTEGER,
   Grade GradesType);

INSERT INTO Examine VALUES (28106,5001,2126,GradesType(1.00));
INSERT INTO Examine VALUES (25403,5041,2125,GradesType(2.00));
INSERT INTO Examine VALUES (27550,4630,2137,GradesType(2.00));

SELECT GradesAverage(Grade) AS UniAverage
FROM Examine;
                                                                 14
Simple user defined types :
Distinct Types
SELECT *
                            Wrong
FROM Students s
WHERE s.HourlyWage > s.PrediplomaGrade;

= Wrong: GradesType vs. decimal fails because of the
  inadmissible comparison of two different data types
= In order to compare different data types with one another, it
  must be transformed first of all to a same data type (casting).
                                       Determine
SELECT *                           overpaid students
FROM Students s                       (salary in €)
WHERE s.HourlyWage >
         (9.99 - CAST(s.PrediplomaGrade AS DECIMAL(3,2)));
                                                                    15
Conversion between GradesTypes
CREATE DISTINCT TYPE US_GradesType AS DECIMAL (3,2) WITH
  COMPARISONS;

CREATE FUNCTION UStoG_SQL(us US_GradesType)
                          RETURNS GradesType
  RETURN
    (CASE WHEN DECIMAL(us) < 1.0 THEN GradesType(5.0)
          WHEN DECIMAL(us) < 1.5 THEN GradesType(4.0)
          WHEN DECIMAL(us) < 2.5 THEN GradesType(3.0)
          WHEN DECIMAL(us) < 3.5 THEN GradesType(2.0)
          ELSE GradesType(1.0) END);

CREATE TABLE TransferFromAmerica (
   StudID INTEGER,
   LectNo INTEGER,
   University VARCHAR(30),
   Grade US_GradesType);                                16
Conversion in a query
INSERT INTO TransferFromAmerica VALUES (28106,5041,
           'Univ. Southern California', US_GradesType(4.00));

SELECT StudID, GradesAverage(Grade)
FROM
   (
       (SELECT Grade, StudID FROM Examine) UNION
       (SELECT UStoG_SQL(Grade) AS Grade, StudID
        FROM TransferFromAmerica)
   ) AS AllExaminations
GROUP BY StudID



                                                                17
Conversion as external function
CREATE FUNCTION UStoG(DOUBLE) RETURNS DOUBLE
    EXTERNAL NAME 'Converter_UStoG'
    LANGUAGE C                                 Data type of C which is
    PARAMETER STYLE DB2SQL                      closest to DECIMAL
    NO SQL
    DETERMINISTIC         -- same parameter → same result
    NO EXTERNAL ACTION
    FENCED;      -- own process, means errors do not lead to a database
                     systems crash
CREATE FUNCTION UStoG_Decimal (DECIMAL(3,2)) RETURNS DECIMAL(3,2)
    SOURCE UStoG (DOUBLE);

CREATE FUNCTION GradesType(US_GradesType) RETURNS GradesType
    SOURCE UStoG_Decimal (DECIMAL());


                                                                          18
Conversion in a query

SELECT StudID, GradesAverage(Grade)
FROM
   (
       (SELECT Grade, StudID FROM Examine) UNION
       (SELECT GradesType(Grade) AS Grade, StudID
        FROM TransferFromAmerica)
   ) AS AllExaminations
GROUP BY StudID




                                                    19
Table Functions: Use of external
information

= Goal:
   =External information as view on a table


= Example:
   =Biographies of professors from other sources; non-database
    sources, e.g. WWW




                                                             20
Table Functions: Use of external
information

= Table Function:   supplies a table, here a table of biographies

Biographies(STRING): {[ URL: VARCHAR(40),
                        Language: VARCHAR(20),
                        Ranking: DECIMAL ]}

  STRING-Parameter: Name of a Professor

  URL:              URL under which the biography is shown
  Language:         Language in which the biography is written
  Ranking:          Relevance of the data source

                                                                    21
Table Functions: Use of external
information
Biographies(STRING): {[ URL: VARCHAR(40),
                        Language: VARCHAR(20),
                        Ranking: DECIMAL ]}

= Use in queries:

SELECT bio.URL, bio.Ranking
FROM TABLE(Biographies('Sokrates')) AS bio
WHERE bio.Language = 'English'
ORDER BY bio.Ranking;                      Table Function as
                                            correlated subquery

SELECT prof.Name, bio.URL, bio.Ranking
FROM Professors AS prof, TABLE(Biographies(prof.Name)) AS bio
WHERE bio.Language = 'german'
ORDER BY prof.Name, bio.Ranking;
                                                                  22
Realization of a Table Function
CREATE FUNCTION Biographies(VARCHAR(20))
   RETURNS TABLE (URL VARCHAR(40),
                   Language VARCHAR(20),
                   Ranking DECIMAL )
   EXTERNAL NAME '/usr/..../Wrappers/Biographies'
   LANGUAGE C
   PAARAMETER STYLE DB2SQL
   NO SQL
   NOT DETERMINISTIC
   NO EXTERNAL ACTION
   FENCED
   NO SCRATCHPAD
   NO FINAL CALL
   CARDINALITY 20;
                                                    23
Table Functions: Use of external
information

= external name:          Name of file with the implementation
= language:               Language of the implementation, e.g.
                          C, C++, Java, …
= parameter style:        Parameter transfer conventions
= (no) SQL:               indicates if SQL instructions are in the
                          code
= (not) deterministic:    indicates whether calls always
                          supply the same result
= (no) external action:   indicates whether external data are
                          written, i.e. whether there are
                          side effects


                                                                     24
Table Functions: Use of external
information
= (not) fenced:      indicates whether the function runs
                     as own process
                      not fenced:
                            Crash of the function
                                   →
                            Crash of database system
                       fenced:
                         higher expenditure (own process)
= (no) scratchpad:   indicates whether the function stores
                     intermediate results from call to call
= (no) final call:   indicates whether the DBMS must
                     signal the end of the process
= cardinality:       hint to the optimizer about the size of
                     the result per call
                                                               25
User defined structured object
types

= Objects

= Object identity

= Reference

= How does this get along with tuples of relational databases ?

= Example: Database of a university

= UML schema and entity relationship diagram:

                                                                  26
         Students                       +Listener
                                                                                     require
        +StudID : int                                           +Successor      *
       +Name : String                   1..*
       +Semester : int
                                                                 Lectures                *
   +GradesAverage() : float                    attend
     +TotalHours() : short                                     +LectNo : int
                                                        *      +Title : String
             1    +Examinee                                     +Hours : int
                                                            +NumbListener() : int
                      *                                     +FailureRate() : float        *
                  Examinations
                                    *                              1   +Examination content
                 +Grade : Decimal




                                                                                                   lecturedBy
                   +Date : Date
                     +move()

                     *                                      1    +Examiner

                                                                 Professors
    Assistents                                      +Boss       +Status : String               1
+Specific_field : String                                    +GradesAverage() : float
                                        workFor
  +Salary() : short        *                            1      +Salary() : short               +Lecturer
                                                            +Teaching hours() : short




                                     Employees
                                     +PersNo : int
                                    +Name : String
                                    +Salary() : short
                                    +Taxes() : short                                                            27
University Schema                                    require
                                                           Successor
  StudID                                  Predecessor                LectNo
                                                      N         M
   Name              Students           attend            Lectures          Hours
                                    N             M
                       N                                       N
 Semester                                                 M                 Title



                       Grade            examine                 lecture


  PersNo
                                                      1          1          Status
  Name           Assistents         workFor                Professors
                                N                     1                     Room
Specific_field
                                                  PersNo             Name            28
Type Declaration in Oracle


CREATE OR REPLACE TYPE ProfessorsType AS OBJECT (
  PersNo NUMBER,
  Name VARCHAR(20),
  Status CHAR(2),
  Room NUMBER,
  MEMBER FUNCTION GradesAverage RETURN NUMBER,
  MEMBER FUNCTION Salary RETURN NUMBER
)




                                                    29
Type Declaration in Oracle


CREATE OR REPLACE TYPE AssistentsType AS OBJECT (
  PersNo NUMBER,
  Name VARCHAR(20),
  Specific_field VARCHAR(20),
  Boss REF ProfessorsType,
  MEMBER FUNCTION Salary RETURN NUMBER
)




                                                    30
Type Declaration of Oracle
CREATE OR REPLACE TYPE LectRefListsType AS TABLE OF REF
  LecturesType

CREATE OR REPLACE TYPE LecturesType AS OBJECT (
  LectNo NUMBER,
  Title VARCHAR(20),
  Hours NUMBER,
  lecturedBy REF ProfessorsType,
  Requirements LectRefListsType,
  MEMBER FUNCTION FailureRate RETURN NUMBER,
  MEMBER FUNCTION NumbListener RETURN NUMBER
)

                                                          31
Illustration of a LecturesType object

   LectNo       Title    Hours   lecturedBy   Requirements
    5049       Mäeutik    2          ...           ...

                                                   ...

                                                   ...

                                                   ...




     Reference to
ProfessorsType-Object

                                                                 Re
                                                             Lectu ferences
                                                                  resT
                                                                       ype to
                                                                          -Obje
                                                                                cts




                                                                                      32
Implementation of Operations
CREATE OR REPLACE TYPE BODY ProfessorsType AS
MEMBER FUNCTION GradesAverage RETURN NUMBER IS
  BEGIN
    /* Find all examinations of Profs and
       determine average */
  END;

MEMBER FUNCTION Salary RETURN NUMBER IS
  BEGIN
    RETURN 1000.0; /* uniform salary for everybody */
  END;
END;

                                                        33
Creation of relations / tables

= Each object can be referenced
= The object identity is generated automatically on creation time
  of an object (Oracle)
= Objects are never "free-floating", they "always live" in a table
= An object is a row in a table with object identity

             →

= Creation of containers for objects: CREATE TABLE
= Creation of an object and storage in a table: INSERT INTO



                                                                 34
Creation of relations / tables
CREATE TABLE ProfessorsTab OF ProfessorsType (PersNo PRIMARY KEY) ;

CREATE TABLE LecturesTab OF LecturesType
  NESTED TABLE Requirements STORE AS PredecessorTab;

   = Oracle: nested relations must get explicit names
                 (in the example: PredecessorTab)

CREATE TABLE AssistentsTab OF AssistentsType;

INSERT   INTO   ProfessorsTab   VALUES   (2125,   'Sokrates', 'C4', 226);
INSERT   INTO   ProfessorsTab   VALUES   (2126,   'Russel', 'C4', 232);
INSERT   INTO   ProfessorsTab   VALUES   (2127,   'Kopernikus', 'C3', 310);
INSERT   INTO   ProfessorsTab   VALUES   (2133,   'Popper', 'C3', 52);
INSERT   INTO   ProfessorsTab   VALUES   (2134,   'Augustinus', 'C3', 309);
INSERT   INTO   ProfessorsTab   VALUES   (2136,   'Curie', 'C4', 36);
INSERT   INTO   ProfessorsTab   VALUES   (2137,   'Kant', 'C4', 7);

                                                                              35
Insertion of references
INSERT INTO LecturesTab
  SELECT 5041, 'Ethik', 4, REF(p), LectRefListsType()
  FROM ProfessorsTab p
  WHERE Name = 'Sokrates';

INSERT INTO LecturesTab
   SELECT 5216, 'Bioethik', 2, REF(p), LectRefListsType()
   FROM ProfessorsTab p
   WHERE Name = 'Russel';

INSERT INTO TABLE
       (SELECT succ.Requirements
        FROM LecturesTab succ
        WHERE succ.Title = 'Bioethik')
   SELECT REF(predec)
   FROM LecturesTab predec
   WHERE predec.Title = 'Ethik';
                                                            36
Representation of LecturesTab
                        LecturesTab                                   References to
                                                              LecturesType-Objects
  LectNo      Title       Hours   lecturedBy   Requirements
   5049      Mäeutik          2       ...          ...

                                                   ...
               Reference to
     ProfessorsType-Object                         ...

                                                   ...

   5041       Ethik           4       ...          ...

                                                   ...

   5216     Bioethik          2       ...          ...
                                                   ...

                                                   ...




                                                                                      37
„Pure“ nested relations
= Modelling of aggregations
= Relationships of the kind
   =Exclusive Relationship (1:N)
   =Existence-dependent Relationship
= Nesting of weak entities in the superordinate object


         Students
        +StudID : int                                      Examinations
                              +Examinee                                    *
       +Name : String                                     +Grade : Decimal
       +Semester : int        1           attend        *   +Date : Date
   +GradesAverage() : float                                   +move()
    +TotalHours() : short
                                                               *               +Content     1
                                                    1
                                            ...                                           ...
                                                   +Examiner



                                                                                           38
Real Nested Relations (Oracle)
CREATE OR REPLACE TYPE ExaminationsType AS OBJECT (
  Content REF LecturesType,
  Examiner REF ProfessorsType,
  Grade DECIMAL(3,2),
  Date DATE,
  MEMBER FUNCTION move(newDate DATE) RETURN DATE
)

CREATE OR REPLACE TYPE ExaminationListsType AS TABLE OF
  ExaminationsType

CREATE OR REPLACE TYPE StudentsType AS OBJECT (
  StudID NUMBER,
  Name VARCHAR(20),
  Semester NUMBER,
  attend LectRefListsType,
  passedExaminations ExaminationListsType,
  MEMBER FUNCTION GradesAverage RETURN NUMBER,
  MEMBER FUNCTION TotalHours RETURN NUMBER
)                                                         39
Real Nested Relations (Oracle)

CREATE OR REPLACE TYPE BODY StudentsType AS
 MEMBER FUNCTION TotalHours RETURN NUMBER IS
      i INTEGER; lect LecturesType;
      Total NUMBER := 0;
      BEGIN
              FOR i IN 1..SELF.attend.COUNT LOOP
                      /* Explicit Dereferentiation: */
                      UTL_REF.SELECT_OBJECT(attend(i),lect);
                      Total := Total+lect.Hours;
              END LOOP;
              RETURN Total;
      END;

 MEMBER FUNCTION GradesAverage RETURN NUMBER IS
      …
END;
                                                               40
Students-Table
CREATE TABLE StudentsTab OF StudentsType
      (StudID PRIMARY KEY)
  NESTED TABLE attend STORE AS AllocationsTab
  NESTED TABLE passedExaminations STORE AS ExaminationsTab;

INSERT INTO StudentsTab VALUES(24002, 'Xenokrates', 18,
   LectRefListsType(), ExaminationsListsType());

INSERT INTO StudentsTab VALUES (29120, 'Theophrastos', 2,
   LectRefListsType(), ExaminationsListsType());

INSERT INTO TABLE
       (SELECT s.attend
        FROM StudentsTab s
        WHERE s.Name = 'Theophrastos') /* big fan of Sokrates */
   SELECT REF(l)
   FROM LecturesTab l
   WHERE l.lecturedBy.Name = 'Sokrates';
                                                                   41
Representation of StudentsTab
                                    StudentsTab
StudID      Name        Semester   attend             passedExaminations
24002     Xenokrates      18                Content   Examiner    Grade        Date
                                                                    ...         ...

                                                                    ...         ...

                                                                    ...         ...



StudID      Name        Semester   attend              passedExaminations
29120    Theophrastos      2                Content   Examiner    Grade        Date
                                                                   1.3      May 6, 2001
                                                                   1.7      May 2, 2001
StudID      Name        Semester   attend              passedExaminations
28106      Carnap          3                Content   Examiner    Grade        Date
                                                                    ...         ...

                                                                    ...         ...




                                                                                      42
Insertion of Examinations
INSERT INTO TABLE
  (SELECT s.passedExaminations
   FROM StudentsTab s
   WHERE s.Name = 'Theophrastos')
   VALUES ((SELECT REF(l) FROM LecturesTab l WHERE l.Title='Maeeutik'),
       (SELECT REF(p) FROM ProfessorsTab p WHERE p.Name='Sokrates'),
       1.3, SYSDATE);

INSERT INTO TABLE
  (SELECT s.passedExaminations
   FROM StudentsTab s
   WHERE s.Name = 'Theophrastos')
   VALUES ((SELECT REF(l) FROM LecturesTab l WHERE l.Title='Ethik'),
       (SELECT REF(p) FROM ProfessorsTab p WHERE p.Name='Sokrates'),
       1.7, SYSDATE);

                                                                          43
Query on nested relations
SELECT s.Name, p.Grade
FROM StudentsTab s, TABLE(s.passedExaminations) p;

NAME                       GRADE
-----------------------------------
Theophrastos                  1.3
Theophrastos                  1.7


SELECT s.Name, p.Examiner.Name, p.Content.Title, p.Grade
FROM StudentsTab s, TABLE(s.passedExaminations) p;

NAME                  EXAMINER.NAME CONTENT.TITLE                       GRADE
---------------------------------------------------------------------------------------------
Theophrastos            Sokrates              Maeeutik                    1.3
Theophrastos            Sokrates              Ethik                       1.7
                                                                                                44
Query on nested relations: via Cursor
(Oracle)

SELECT s.Name, CURSOR (
         SELECT p.Grade
         FROM TABLE (s.passedExaminations) p
      )
FROM StudentsTab s;

Result:

          Name     Cursor

    Xenokrates     no rows selected

    Theophrastos   Grade
                    1.3
                    1.7
                                               45
Query on nested relations
=Better:

SELECT s.Name, p.Examiner.Name, p.Content.Titel, p.Grade
FROM StudentsTab s, TABLE(s.passedExaminations) p

Result:

          Name     Examiner.Name Content.Title    Grade

    Theophrastos      Sokrates     Maeeutik         1.3
    Theophrastos      Sokrates     Ethik            1.7




                                                           46
Inheritance of object types(DB2)
CREATE TYPE Employees_t AS                         Employees
   (PersNo INTEGER,
    Name VARCHAR(20))                      Professors     Assistents
   INSTANTIABLE
   REF USING VARCHAR(13) FOR BIT DATA
   MODE DB2SQL;

CREATE TYPE Professors_t UNDER Employees_t AS
   (Status CHAR(2),
    Room INTEGER)
   MODE DB2SQL;

CREATE TYPE Assistents_t UNDER Employees_t AS
   (Specific_field VARCHAR(20),
    Boss REF(Professors_t))
   MODE DB2SQL;
                                                                   47
Inheritance of object types(DB2)

= REF USING: Object identity is set by user
               → Support of application systems with which
                  the identifiers were already assigned through
                  objects by the user before these are stored
                  in the DB
               → user-defined object identity
               → Uniqueness of the object identifier is user
                   responsibility
= INSTANTIABLE: There are objects of the types
= NOT INSTANTIABLE: Corresponds to abstract classes in JAVA



                                                             48
Inheritance of Object Types (DB2)
ALTER TYPE Professors_t
   ADD METHOD numbStaff()
   RETURNS INTEGER
   LANGUAGE SQL
   CONTAINS SQL
   READS SQL DATA;

CREATE TABLE Employees OF Employees_t
   (REF IS Oid USER GENERATED);

CREATE TABLE Professors OF Professors_t UNDER Employees
   INHERIT SELECT PRIVILEGES;

CREATE TABLE Assistents OF Assistents_t UNDER Employees
   INHERIT SELECT PRIVILEGES
   (Boss WITH OPTIONS SCOPE Professors);
                                                          49
Inheritance of Object Types (DB2)

= Interpretation:
   = Each tuple of the table “Professors“ is also a tuple of the
     table “Employees“
   = Each tuple of the table “Assistents“ is also a tuple of the
     table “Employees“
   = “Boss“ must reference a tuple of the table “Professors“
      = Stronger restriction than with OO programming languages and OO
        database systems, because different tables of the type
        "Professors_t" can exist




                                                                         50
Generalization/Specialization
CREATE METHOD numbStaff()
   FOR Professors_t                             ->: Dereferencing
   RETURN (SELECT COUNT (*)                     .. : Attribute access
            FROM Assistents
            WHERE Boss->PersNo = SELF..PersNo);

INSERT INTO Professors (Oid, PersNo, Name, Status, Room)               User-
   VALUES(Professors_t('s'), 2125, 'Sokrates', 'C4', 226);            defined
                                                                      Object-
INSERT INTO Professors (Oid, PersNo, Name, Status, Room)              identity
   VALUES(Professors_t('r'), 2126, 'Russel', 'C4', 232);

INSERT INTO Professors (Oid, PersNo, Name, Status, Room)
   VALUES(Professors_t('c'), 2137, 'Kant', 'C4', 7);

INSERT INTO Assistents (Oid, PersNr, Name, Specific_field, Boss)
   VALUES(Assistents_t('p'), 3002, 'Platon', 'Ideenlehre', Professors_t('s'));
                                                                                 51
Generalization/Specialization

INSERT INTO Assistents (Oid, PersNo, Name, Specific_field, Boss)
   VALUES(Assistents_t('a'), 3003, 'Aristoteles', 'Syllogistik',
   Professors_t('s'));

INSERT INTO Assistents (Oid, PersNo, Name, Specific_field, Boss)
   VALUES(Assistents_t('w'), 3004, 'Wittgenstein',
            'Sprachtheorie', Professors_t('r'));




                                                               52
Generalization/Specialization

SELECT a.Name, a.PersNo
FROM Employees a;

                   Name           PersNo

                   Sokrates       2125
                   Russel         2126
                   Kant           2137
                   Platon         3002
                   Aristoteles    3003
                   Wittgenstein   3004

                                           53
Generalization/Specialization

SELECT a.Name,
     a.Boss->Name AS Chief,
     a.Boss->numbStaff()-1 AS numbColleagues
FROM Assistents a;

              Name            Chief      numbColleagues

              Platon         Sokrates          1
              Aristoteles    Sokrates          1
              Wittgenstein   Russel            0

                                                          54
Complex Attribute/Column Types
CREATE TYPE GradeObjType AS
   (Country VARCHAR(20),
    NumValue DECIMAL(3,2),
    StringValue CHAR(10))
   MODE DB2SQL;

CREATE TYPE US_GradeObjType UNDER GradeObjType AS
    (WithHonors CHAR(1))
    MODE DB2SQL;

CREATE TYPE G_GradeObjType UNDER GradeObjType AS
    (Latin VARCHAR(20))
    MODE DB2SQL;

CREATE TYPE ECTS_GradeObjType UNDER GradeObjType AS
    (CreditPoints INTEGER)
    MODE DB2SQL;
                                                      55
Complex Attribute/Column Types
CREATE TABLE Performances (
    Student VARCHAR(20),
    Lecture VARCHAR(20),
    Grade GradeObjType );

INSERT INTO Performances VALUES ('Feuerbach', 'Java', US_GradeObjType()
   ..Country('USA')
    ..NumValue(4.0)
    ..StringValue('excellent')
    ..WithHonors('y'));

INSERT INTO Performances VALUES ('Feuerbach', 'C++', G_GradeObjType()
    ..Country('G')
    ..NumValue(1.0)
    ..StringValue('high distinction')
    ..Latin('summa cum laude'));
                                                                          56
Complex Attribute/Column Types

CREATE FUNCTION G_GradeObjType
  (c VARCHAR(20), n DECIMAL(3,2), s Char(10), lt VARCHAR(20))
     RETURNS G_GradeObjType                        Constructor
     LANGUAGE SQL                                     with
     RETURN G_GradeObjType()..Country(c)           Parameters
                  ..NumValue(n)
                  ..StringValue(s)
                  ..Latin(lt);

INSERT INTO Performances VALUES ('Fichte', ‚Java',
                  G_GradeObjType('G',3.0,'satisfactory','rite'));

                                                                    57
Queries on complex/structured
attributes

SELECT Student, Lecture, Grade..Country, Grade..NumValue
FROM Performances;

 Student         Lecture     Grade..Country Grade..NumValue

  Fichte           Java            G               3.0
 Feuerbach         Java            USA             4.0
 Feuerbach         C++             G               1.0




                                                           58

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:6/14/2013
language:English
pages:58
renata.vivien renata.vivien
About