Outline Complex Data Types and Object Orientation Structured Data Types and Inheritance in SQL ▣Table Inheritance Array and Multiset Types in SQL Object Identity and Reference Types in SQL Implementing O-R Features Persistent Programming Languages Comparison of Object-Oriented and Object-Relational Databases Database System Concepts-7th Edition 29.2 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 29.2 ©Silberschatz, Korth and Sudarshan th Edition Outline ▪ Complex Data Types and Object Orientation ▪ Structured Data Types and Inheritance in SQL ▪ Table Inheritance ▪ Array and Multiset Types in SQL ▪ Object Identity and Reference Types in SQL ▪ Implementing O-R Features ▪ Persistent Programming Languages ▪ Comparison of Object-Oriented and Object-Relational Databases
Object-Relational Data Models Extend the relational data model by including object orientation and constructs to deal with added data types. Allow attributes of tuples to have complex types,including non-atomic values such as nested relations. ■ Preserve relational foundations,in particular the declarative access to data,while extending modeling power. Upward compatibility with existing relational languages. Database System Concepts-7th Edition 29.3 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 29.3 ©Silberschatz, Korth and Sudarshan th Edition Object-Relational Data Models ▪ Extend the relational data model by including object orientation and constructs to deal with added data types. ▪ Allow attributes of tuples to have complex types, including non-atomic values such as nested relations. ▪ Preserve relational foundations, in particular the declarative access to data, while extending modeling power. ▪ Upward compatibility with existing relational languages
Complex Data Types ■Motivation: Permit non-atomic domains (atomic indivisible) Example of non-atomic domain:set of integers,or set of tuples Allows more intuitive modeling for applications with complex data Intuitive definition: Allow relations whenever we allow atomic(scalar)values -relations within relations Retains mathematical foundation of relational model Violates first normal form. Database System Concepts-7th Edition 29.4 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 29.4 ©Silberschatz, Korth and Sudarshan th Edition Complex Data Types ▪ Motivation: • Permit non-atomic domains (atomic indivisible) • Example of non-atomic domain: set of integers, or set of tuples • Allows more intuitive modeling for applications with complex data ▪ Intuitive definition: • Allow relations whenever we allow atomic (scalar) values — relations within relations • Retains mathematical foundation of relational model • Violates first normal form
Example of a Nested Relation Example:library information system ■Each book has ·Title, A list (array)of authors, Publisher,with subfields name and branch,and ·A set of keywords Non-1NF relation books title author_array publisher keyword_set (nane,branch） Compilers [Smith,Jones] (McGraw-Hill,New York) parsing,analysis] Networks [Jones,Frick] (Oxford,London) Internet,Web} Database System Concepts-7th Edition 29.5 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 29.5 ©Silberschatz, Korth and Sudarshan th Edition Example of a Nested Relation ▪ Example: library information system ▪ Each book has • Title, • A list (array) of authors, • Publisher, with subfields name and branch, and • A set of keywords ▪ Non-1NF relation books
4NF Decomposition of Nested Relation Suppose for simplicity that title title author position uniquely identifies a book Compilers Smith 1 ·In real world ISBN is a Compilers Jones 2 unique identifier Networks Jones 1 Networks Frick 2 Decompose books into 4NF authors using the schemas: .(title,author,position) title keyword Compilers parsing ·(title,keyword) Compilers analysis (title,pub-name,pub- Networks Internet branch) Networks Web 4NF design requires users to keywords include joins in their queries. title pub_name pub_branch Compilers McGraw-Hill New York Networks Oxford London books4 Database System Concepts-7th Edition 29.6 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 29.6 ©Silberschatz, Korth and Sudarshan th Edition 4NF Decomposition of Nested Relation ▪ Suppose for simplicity that title uniquely identifies a book • In real world ISBN is a unique identifier ▪ Decompose books into 4NF using the schemas: • (title, author, position) • (title, keyword ) • (title, pub-name, pubbranch ) ▪ 4NF design requires users to include joins in their queries
Complex Types and SQL ■ Extensions introduced in SQL:1999 to support complex types: 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 Not fully implemented in any database system currently But some features are present in each of the major commercial database systems Read the manual of your database system to see what it supports Database System Concepts-7th Edition 29.7 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 29.7 ©Silberschatz, Korth and Sudarshan th Edition Complex Types and SQL ▪ Extensions introduced in SQL:1999 to support complex types: • 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 ▪ Not fully implemented in any database system currently • But some features are present in each of the major commercial database systems ▪ Read the manual of your database system to see what it supports
Structured Types and Inheritance in SQL Structured types (a.k.a.user-defined 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 can be used to create tables with composite attributes create table person name Name, address Address, dateOfBirth date) Dot notation used to reference components:name.firstname Database System Concepts-7th Edition 29.8 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 29.8 ©Silberschatz, Korth and Sudarshan th Edition Structured Types and Inheritance in SQL ▪ Structured types (a.k.a. user-defined 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 can be used to create tables with composite attributes create table person ( name Name, address Address, dateOfBirth date) ▪ Dot notation used to reference components: name.firstname
Structured Types (cont.) User-defined row types create type PersonType as( name Name, address Address, dateOfBirth date) not final Can then create a table whose rows are a user-defined type create table customer of CustomerType Alternative using unnamed row types. create table person r name row(firstname varchar(20), lastname varchar(20)), address row(street varchar(20), city varchar(20), zipcode varchar(20)), dateOfBirth date) Database System Concepts-7th Edition 29.9 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 29.9 ©Silberschatz, Korth and Sudarshan th Edition Structured Types (cont.) ▪ User-defined row types create type PersonType as ( name Name, address Address, dateOfBirth date) not final ▪ Can then create a table whose rows are a user-defined type create table customer of CustomerType ▪ Alternative using unnamed row types. create table person_r( name row(firstname varchar(20), lastname varchar(20)), address row(street varchar(20), city varchar(20), zipcode varchar(20)), dateOfBirth date)
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 We can now find the age of each customer: select name.lastname,ageOnDate(current_date) from customer Database System Concepts-7th Edition 29.10 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 29.10 ©Silberschatz, Korth and Sudarshan th Edition 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 ▪ We can now find the age of each customer: select name.lastname, ageOnDate (current_date) from customer
Constructor Functions Constructor functions are used to create values of structured types E.g., create function Name(firstname varchar(20),lastname varchar(20)) returns Name begin set self.firstname firstname; set self.lastname lastname, end To create a value of type Name,we use new Name(John','Smith') Normally used in insert statements insert into Person values (new Name('John','Smith), new Address(20 Main St','New York','11001) date‘1960-8-22); Database System Concepts-7th Edition 29.11 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 29.11 ©Silberschatz, Korth and Sudarshan th Edition Constructor Functions ▪ Constructor functions are used to create values of structured types ▪ E.g., create function Name(firstname varchar(20), lastname varchar(20)) returns Name begin set self.firstname = firstname; set self.lastname = lastname; end ▪ To create a value of type Name, we use new Name(‘John’, ‘Smith’) ▪ Normally used in insert statements insert into Person values (new Name(‘John’, ‘Smith), new Address(’20 Main St’, ‘New York’, ‘11001’), date ‘1960-8-22’);