Extending Relational Databases.ppt

Complex Types and SQL:1999 Extensions to SQL to support complex types include: Collection and large object types Nested relations are an example of collection types Structured types Nested record structures like composite attributes Inheritance Object orientation Including object identifiers and references Structured Types and Inheritance in SQL Structured types can be declared and used in SQL create type Name as (firstname varchar(20), lastname varchar(20)) final create type Address as (street varchar(20), city varchar(20), zipcode varchar(20)) not final Note: final and not final indicate whether subtypes can be created Structured Types and Inheritance in SQL Structured types can be used to create tables with composite attributes create table customer ( name Name, address Address, dateOfBirth date ) Dot notation used to reference components: Name.firstname Methods Can add a method declaration with a structured type. method ageOnDate (onDate date) returns interval year Method body is given separately. create instance method ageOnDate (onDate date) returns interval year for CustomerType begin return onDate - self.dateOfBirth; end Inheritance Suppose that we have the following type definition for people: create type Person (name varchar(20), address varchar(20)) Using inheritance to define the student and teacher types  create type Student under Person (degree varchar(20), department varchar(20)) create type Teacher under Person (salary integer, department varchar(20)) Object-Identity and Reference Types Define a type Department with a field name and a field head which is a reference to the type Person, with table people as scope: create type Department ( name varchar (20), head ref (Person) scope people) We can then create