Transcript Normalization Salman Azhar (based on Jeff Ullman`s Database

Object-Relational Databases
Salman Azhar
User-Defined Types
Object IDs
Nested Tables
These slides use some figures, definitions, and explanations from ElmasriNavathe’s Fundamentals of Database Systems
and Molina-Ullman-Widom’s Database Systems
2/1/04
Database Systems: Salman Azhar
1
Merging Relational and Object
Models
Object-oriented models support
 interesting data types --- not just flat files.
• E.g., Maps, multimedia, etc.
The relational model supports
 very-high-level queries
Object-relational databases are an
attempt to get the best of both.
2/1/04
Database Systems: Salman Azhar
2
Evolution of DBMSs
Object-oriented DBMSs fell short
 because they did not offer the
efficiencies of a relational DBMS.
Object-relational extensions to
relational DBMSs
 capture much of the advantages of OO
 yet retain the relation as the fundamental
abstraction.
2/1/04
Database Systems: Salman Azhar
3
SQL-99 and DBMS Features
SQL-99 includes many of the objectrelational features to be described.
However, being so new, different
DBMSs use different approaches.
 We’ll sometimes use features and syntax
from Oracle and SQL Server.
2/1/04
Database Systems: Salman Azhar
4
User Defined Types
 A user-defined type, or UDT, is
essentially a class definition, with a
structure and methods.
 Two uses:
1. As a row-type, that is, the type of a
relation.
2. As an column-type, that is, the type if
attribute in a relation.
2/1/04
Database Systems: Salman Azhar
5
UDT Definition
CREATE TYPE <typename> AS (
<list of elements, as in
CREATE TABLE>
);
 Oracle syntax:
1. Add “OBJECT” as in
CREATE TYPE <name> AS OBJECT.
2. Follow with / to have the type stored.
2/1/04
Database Systems: Salman Azhar
6
Example: UDT Definition
CREATE TYPE DealerType AS (
name
CHAR(20),
addr
CHAR(20)
);
CREATE TYPE CarType AS (
name
CHAR(20),
manf
CHAR(20)
);
2/1/04
Database Systems: Salman Azhar
7
References
If T is a type, then REF T is the type
of a reference to T
 this means a pointer to an object of type T.
 often called an “object ID” in OO systems.
Unlike object ID’s, a REF is visible,
 although it is usually gibberish.
2/1/04
Database Systems: Salman Azhar
8
Example: REF
CREATE TYPE SellsType AS (
dealer REF DealerType,
car
REF CarType,
price
FLOAT
);
 SellsType objects look like:
REF DealerType
REF CarType
FLOAT
30000
Reference to a
DealerType object
2/1/04
Reference to a
CarType object
Database Systems: Salman Azhar
9
UDTs as Row Types
A table may be defined to have a
schema that is a row type, rather than
by listing its elements.
Syntax:
 CREATE TABLE <table name> OF
<type name>;
Creates a table where each row is of
<type name>
2/1/04
Database Systems: Salman Azhar
10
Example: Creating Tables
CREATE TABLE Dealer OF DealerType;
CREATE TABLE Car OF CarType;
CREATE TABLE Sells OF SellsType;
2/1/04
Database Systems: Salman Azhar
11
Values of Relations with a Row-type
Technically, a relation declared to have
a rowtype DealerType (such as Dealer),
 is not a set of pairs
 it is a unary relation
• whose tuples are objects with two components:
– name and addr.
2/1/04
Database Systems: Salman Azhar
12
Type Constructors
Each UDT has a type constructor of
the same name that wraps objects of
that type.
2/1/04
Database Systems: Salman Azhar
13
Example: Type Constructor
The query
SELECT * FROM Dealer;
Produces “tuples” such as:
DealerType(‘AutoNation’, ‘Maple St.’)
2/1/04
Database Systems: Salman Azhar
14
Aliasing:
Accessing Values From a Rowtype
In Oracle and MS SQL Server, the dot
works as expected
 but it may not work in all DBMS
 so it is a good idea, to use an alias for every
relation, when O-R features are used.
Example:
SELECT dd.name, dd.addr
FROM Dealer dd;
2/1/04
Database Systems: Salman Azhar
15
Accessing Values: SQL-99 Approach
In SQL-99, each attribute of a UDT has:
 Get method
• Called Generator methods (get the value) and
 Set method
• Called Mutator methods (change the value)
These methods have the same name as the
attribute.
 The generator for A takes no argument, as A( ).
 The mutator for A takes a new value as
argument, as A(v).
2/1/04
Database Systems: Salman Azhar
16
Example: SQL-99 Value Access
Consider
SELECT dd.name, dd.addr
FROM Dealer dd;
The same query in SQL-99 is
Explicitly
access
generators
SELECT dd.name( ), dd.addr( )
FROM Dealer dd;
2/1/04
Database Systems: Salman Azhar
17
Inserting Row Type Values
(Oracle Style)
We can use a standard INSERT statement,
 remembering that a relation with a row type is
really unary and needs that type constructor.
Example:
INSERT INTO Dealer VALUES(
DealerType(‘AutoNation’, ‘Maple St.’)
);
Note: DealerType is a constructor
2/1/04
Database Systems: Salman Azhar
18
Inserting Values: SQL-99 Style
1. Create a variable X of the suitable
type,
using the constructor method for that type.
2. Use the mutator methods for the
attributes
to set the values of the fields of X.
3. Insert X into the relation.
2/1/04
Database Systems: Salman Azhar
19
Example: SQL-99 Insert
The following must be part of a
procedure, so we have a variable
newDealer.
SET newDealer = DealerType( );
newDealer.name(‘AutoNation’);
newDealer.addr(‘Maple St.’);
INSERT INTO Dealer
VALUES(newDealer);
2/1/04
Database Systems: Salman Azhar
Insert new
values
Construct a
new dealer
Mutator
methods
change
newDealer’s
name and addr
components.
20
UDTs as Column Types
A UDT can be the type of a column (an
attribute)
 in either another UDT definition
 or in a CREATE TABLE statement
Use the name of the UDT as the type of
the attribute
2/1/04
Database Systems: Salman Azhar
21
Example: Column Type
CREATE TYPE AddrType AS (
street CHAR(30),
city
CHAR(20),
zip
INT
);
CREATE TABLE Buyers (
name
CHAR(30),
addr
AddrType,
favCar CarType
);
2/1/04
Database Systems: Salman Azhar
Values of addr and
favCar components
are objects with 3 and
2 fields, respectively.
22
Oracle Problem With Field Access
You can access a field F of an object
that is the value of an attribute A by
A.F .
However, you must use an alias, say tt,
for the table T with attribute A, as
tt.A.F .
2/1/04
Database Systems: Salman Azhar
23
Example: Field Access in Oracle
Wrong (can’t have nothing):
SELECT favCar.name
FROM Buyers;
Wrong (can’t have the table name):
SELECT Buyers.favCar.name
FROM Buyers;
Right (must have alias name):
SELECT b.favCar.name
FROM Buyers b;
2/1/04
Database Systems: Salman Azhar
24
Following References (REFs)
 A  B denotes the value of the B
component of the object pointed to by A
 A  B makes sense if:
1. A is of type REF T
2. B is an attribute (component) of objects of
type T
2/1/04
Database Systems: Salman Azhar
25
Following REF’s: Oracle Style
REF-following is implicit in the dot
Just follow a REF by a dot and a field of
the object referred to
Field name in
object car in
Example:
table aliases as ss
SELECT ss.car.name
FROM Sells ss
WHERE ss.dealer.name =
‘AutoNation’;
2/1/04
Database Systems: Salman Azhar
26
Oracle’s DEREF Operator -Motivation
If we want the set of car objects for the
cars sold by AutoNAtion, we might try:
object car (ref) in
SELECT ss.car
table aliases as ss
FROM Sells ss
WHERE ss.dealer.name =
‘AutoNation’;
Legal, but ss.car is a REF, hence gibberish!
 Need to add DEREF
2/1/04
Database Systems: Salman Azhar
27
Using DEREF
To see the CarType objects, use:
SELECT DEREF(ss.car)
FROM Sells ss
WHERE ss.dealer.name =
‘AutoNation’;
Produces values like:
CarType(‘MiniCooper’,
2/1/04
Database Systems: Salman Azhar
‘BMW’)
28
Methods
Classes are more than structures; they
may have methods.
 Declare in CREATE TYPE
 Define methods in a CREATE TYPE BODY
statement
 Use T-SQL syntax for methods.
• (T-SQL: MS SQL Server = PL/SQL: Oracle)
 Variable SELF refers to the object to which
the method is applied (this)
2/1/04
Database Systems: Salman Azhar
29
Method Definition
Form of create-body statement:
CREATE TYPE BODY <type name> AS
PROCEDURE
methodName(arg <ARGTYPE>)
RETURN <RETURNTYPE>
BEGIN
…
END;
END;
2/1/04
Database Systems: Salman Azhar
30
Method Definition – Oracle Style
Method definitions are called PL/SQL
procedure definitions in Oracle
Oracle uses “MEMBER FUNCTION”
in place of “PROCEDURE”
2/1/04
Database Systems: Salman Azhar
31
Example: Method Definition
(parenthesis only
if an argument is
passed)
CREATE TYPE BODY SellsType AS
MEMBER FUNCTION
priceConvert(rate FLOAT) RETURN FLOAT IS
Argument & argument type
BEGIN
RETURN rate * SELF.price;
END;
END;
Sorta like “this”
keyword in
C++/Java
2/1/04
Database Systems: Salman Azhar
32
Method Use
 objectName.methodName(arguments if any)
Example:
SELECT ss.car.name,
ss.priceConvert(1.33)
FROM Sells ss
WHERE ss.dealer.name = ‘AutoNation’;
2/1/04
Database Systems: Salman Azhar
33
Example: Nested Table Type
CREATE TYPE CarType AS OBJECT (
name
CHAR(20),
edition
CHAR(20)
);
GO
CREATE TYPE CarTableType AS
TABLE OF CarType;
GO
2/1/04
Database Systems: Salman Azhar
34
Example --- Continued
Use CarTableType in a Manfs relation that
stores the set of cars by each manufacturer in
one tuple for that manufacturer.
CREATE TABLE Manfs (
name
CHAR(30),
addr
CHAR(50),
car
carTableType
);
2/1/04
Database Systems: Salman Azhar
35
Nested Tables
Allows values of row components to be
whole relations
If T is a UDT,
 we can create a type S whose values are
relations with rowtype T, by:
CREATE TYPE S
2/1/04
AS TABLE OF T ;
Database Systems: Salman Azhar
36
Querying a Nested Table
 We can print the value of a nested
table like any other value
 But these values have two type
constructors:
1. For the table
2. For the type of tuples in the table
2/1/04
Database Systems: Salman Azhar
37
Example: Query a Nested Table
Find the cars by Toyota:
SELECT car FROM Manfs
WHERE name = ‘Toyota’;
Produces one value:
CarTableType(
CarType(‘Camry’, ‘LE’)
CarType(‘Corolla’, ‘L’)
….
)
2/1/04
Database Systems: Salman Azhar
38
Querying Within a Nested Table
A nested table can be converted to an
ordinary relation by applying THE(…)
This relation can be used in FROM
clauses like any other relation
2/1/04
Database Systems: Salman Azhar
39
Example: Use of THE
Find the cars made by Toyota:
SELECT dd.edition
FROM THE(
SELECT car FROM Manfs
WHERE name = ‘Toyota’;
) dd
WHERE dd.name = ‘Camry’;
2/1/04
Database Systems: Salman Azhar
40