CS W4111.001 Introduction to Databases Fall 2020
Computer Science Department Columbia University
Object-Relational Databases, or ORDBMSs
2
ORDBMS DDL Statements
CREATE TABLE Frames (frameno integer, image jpeg_image, category integer)
CREATE TABLE Categories (cid integer, name text, lease_price float, comments text)
CREATE TYPE theater_t AS
ROW(tno integer, name text, address text, phone text)
REF IS SYSTEM GENERATED
CREATE TABLE Theaters OF theater_t REF is tid SYSTEM GENERATED
CREATE TABLE Nowshowing (film integer,
theater ref(theater_t) SCOPE Theaters, start date, end date)
CREATE TABLE Films (filmno integer, title text,
stars VARCHAR(25) ARRAY [10], director text, budget float)
CREATE TABLE Countries (name text, boundary polygon, population integer, language text)
13
ORDBMSs: “Extended” SQL
• User-defined data types possible for attributes
• Complex attributes possible (not 1NF)
First Normal Form: A relation is in 1NF if every attribute contains only “atomic” values (i.e., no lists, sets, etc.)
• Reference types
• Inheritance
14
Defining New ADTs
• Define how to read in and how to output objects of the new type
• Define the size of the objects of the new type
• Define new methods for the new type (e.g., compress, rotate, shrink, crop, etc. for an image type)
15
“Packaged” ADTs Common and Useful
• DataBlades (IBM’s Informix)
• time series, spatial extensions, …
• Data Cartridges (Oracle) • Oracle Text, …
• DB2 Extenders (IBM) •…
16
Object Relational Features of SQL
• Type constructors to specify complex objects: ROW, ARRAY, MULTISET (plus LIST and SET)
• Encapsulation of operations through user-defined types that might include operations as part of their definition
• Object identity via REFERENCE type
• Inheritance
17
ROW Type Examples
CREATE TYPE Addr_Type AS ROW ( street VARCHAR(45),
city VARCHAR(25),
zip CHAR(5));
CREATE TYPE Emp_Type AS ROW ( name VARCHAR(35),
addr Addr_Type,
age INTEGER)
INSTANTIABLE;
18
ARRAY Type Example
CREATE TYPE Comp_Type AS ROW ( compname VARCHAR(20),
location VARCHAR(20) ARRAY[10]) INSTANTIABLE;
• “location”canholdamaximumof10elements(numbercan also be left unspecified)
• location[1]:firstelementinarray
• location[CARDINALITY(location)]:lastelementinarray
19
REFERENCE Examples
20
REFERENCE Examples
CREATE TABLE Employee OF Emp_Type ( REF IS emp_id SYSTEM GENERATED, PRIMARY KEY (emp_id));
CREATE TABLE Company OF Comp_Type ( REF IS comp_id SYSTEM GENERATED, PRIMARY KEY (comp_id));
CREATE TYPE Employment_Type AS ROW ( employee REF(Emp_Type) SCOPE (Employee), company REF(Comp_Type) SCOPE (Company)) INSTANTIABLE);
CREATE TABLE Employment OF Employment_Type;
21
Example OR Query: “List the name of each employee who works at company ‘ABCD’”
First, “non-OR” design:
22
Example OR Query: “List the name of each employee who works at company ‘ABCD’”
First, “non-OR” design:
CREATE TABLE Employment ( eid INTEGER,
cid INTEGER,
FOREIGN KEY (eid) REFERENCES Employee(emp_id), FOREIGN KEY (cid) REFERENCES Company(comp_id));
SELECT M.name
FROM Employment E, Employee M, Company C
WHERE E.eid=M.emp_id AND E.cid=C.comp_id AND C.compname=‘ABCD’
23
Example OR Query: “List the name of each employee who works at company ‘ABCD’”
Now, “OR” design:
24
Example OR Query: “List the name of each employee who works at company ‘ABCD’”
Now, “OR” design:
SELECT E.employee → name
FROM Employment E
WHERE E.company → compname = ‘ABCD’
→ is used for “dereferencing”
25
MULTISETs
• INTEGER MULTISET can have values 5, 30, 45, -8, 9, 5, for example
• Nesting is allowed, so an element type can itself be a collection type
• Creating MULTISET values:
• By enumeration: MULTISET [1, 2, 3, 4, 2, 5]
• By a query expression: MULTISET (SELECT C.grades
FROM Courses C)
26
MULTISET Can Be Used As a Table, Using UNNEST
SELECT T.A, T.A*2 AS TIMES_TWO
FROM UNNEST (MULTISET [4, 3, 2, 1]) AS T(A)
A
TIMES_TWO
4
8
3
6
2
4
1
2
27
MULTISET Operations
• UNION, EXCEPT, INTERSECT
Options: ALL (default: keeps multiset semantics with duplicates), DISTINCT
• New aggregate functions:
• COLLECT: creates a multiset from attribute values
• FUSION: creates a multiset by combining multiset attribute values
• INTERSECTION: creates a multiset as intersection of multiset attribute values
28
MULTISET Example
Friends
Friend Hobbies
John Susan James
MULTISET [‘reading’, ‘pop music’, ‘running’] MULTISET [‘movies’, ‘opera’, ‘reading’] MULTISET [‘movies’, ‘reading’]
All_Friends
MULTISET [‘John’, ’Susan’, ‘James’]
All_Hobbies
MULTISET [‘reading’, ‘pop music’, ‘running’, ‘movies’, ‘opera’, ‘reading’, ‘movies’, ‘reading’]
Common_Hobbies
MULTISET [‘reading’]
29
MULTISET Example
Friends
Friend
John
Susan
James
Hobbies
MULTISET [‘reading’, ‘pop music’, ‘running’]
MULTISET [‘movies’, ‘opera’, ‘reading’]
MULTISET [‘movies’, ‘reading’]
SELECT COLLECT (F.Friend) AS All_Friends, FUSION (F.Hobbies) AS All_Hobbies,
INTERSECTION (F.Hobbies) AS Common_Hobbies FROM Friends F
All_Friends
All_Hobbies
Common_Hobbies
MULTISET [‘John’, ’Susan’, ‘James’]
MULTISET [‘reading’, ‘pop music’, ‘running’, ‘movies’, ‘opera’, ‘reading’, ‘movies’, ‘reading’]
MULTISET [‘reading’]
30
MULTISETs: Additional Operations
• Removing duplicates (SET (
• Returning number of elements, counting repetitions (CARDINALITY (
• Checking membership (
• Checking inclusion (
• Checking if multiset has no repeated elements (
•…
31