Normal Form vs. Non-First Normal Form

(1)

Normal Form vs. Non-First Normal Form

Kristian Torp

Department of Computer Science

Aalborg Univeristy

www.cs.aau.dk/ torp

[email protected]

September 1, 2009

daisy.aau.dk

(2)

Outline

1 Introduction

2 Creating Nested Tables

Constraints

Indexing

3 Modifications

Insert

Update

Delete

4 Querying

Introduction

Simple Queries

Unnesting

Empty and Member Keywords

Summary

(3)

Outline

1 Introduction

2 Creating Nested Tables

Constraints

Indexing

3 Modifications

Insert

Update

Delete

4 Querying

Introduction

Simple Queries

Unnesting

Empty and Member Keywords

Summary

5 Summary

(4)

University ER-Diagram

7 tables flat

student

,

studentemail

,

takes

,

course

,

courseprerequest

,

lecture

,

exercise

3 tables nested

student

,

takes

,

course

(5)

The Student Table I

ssn

student name

address

email

202 Ann Elm 20 9000 Aalborg [email protected] [email protected] 102 Pete Pie 31 9000 Aalborg [email protected] [email protected] 101 Curt Elm 11 8000 Aarhus [email protected]

Create a type for the

composite attribute

address

c r e a t e o r r e p l a c e

t y p e

a d d r e s s t

as

o b j e c t

(

s t r e e t

v a r c h a r 2

( 2 5 5 ) ,

num

v a r c h a r 2

( 2 5 5 ) ,

z i p

i n t e g e r

,

c i t y

v a r c h a r 2

( 2 5 5 ) ) ;

Create a type for the

multivalued attribute

email

c r e a t e

t y p e

e m a i l t

as

t a b l e

o f v a r c h a r 2

( 2 5 5 ) ;

(6)

The Student Table II

ssn

student name

address

email

Create the

nested table

using the new types

c r e a t e t a b l e

s t u d e n t (

ssn

v a r c h a r 2

( 2 5 5 )

c o n s t r a i n t

s t u d e n t p k

p r i m a r y key

,

student name

v a r c h a r 2

( 2 5 5 )

n o t n u l l

,

address

a d d r e s s t

n o t n u l l

,

e m a i l

e m a i l t

)

nested

t a b l e

e m a i l

s t o r e as

s t u d e n t e m a i l n t ;

Note

(7)

Query Nested Tables

ssn

student name

address

email

Find the students that live on Elm streeet

s e l e c t

∗

from

s t u d e n t s

where

s . address . s t r e e t =

’ Elm ’

ssn

student name

address

email

202 Ann Elm 20 9000 Aalborg [email protected] [email protected] 101 Curt Elm 11 8000 Aarhus [email protected]

Note

Use the composite attribute

The

correlation name

s

is required

The dot notation

s.address.street

(8)

Query Nested Tables, cont.

ssn

student name

address

email

Find where ’[email protected]’ is listed as an email address

s e l e c t

∗

from

s t u d e n t

where

’ ann@nice . org ’

member o f

s t u d e n t . e m a i l

ssn

student name

address

email

202 Ann Elm 20 9000 Aalborg [email protected] [email protected]

Note

Use the multivalued attribute

The new keyword

member

(9)

Motivation

Supported by Oracle in more than 10 years, little used

Background knowledge to understand Oracle’s technology, e.g.,

Spatial

Data mining

Semantic web

Fewer tables (simpler schema)

(10)

Covered in This Lecture

Looks at

Creating

Single nested

Multiple levels of nesting

Modifying

Querying

Table() function

Value() function

Do not look at

Advanced OO features

Reference types

(REF/DEREF)

Physical design (a little)

VARRAY vs. nested tables

Metadata views

Implementation details

Multiset operations

All examples are Oracle specific

(11)

Terminology

Concept

Description

Nested column

A non-atomic column

Flat table

A table with only atomic columns

Nested table

A table with one or more nested columns

Unnest

Convert a nested table to a flat table with same content

Object table

Table where each row corresponds to an object

Row object

When a row corresponds to an object

Column object

A column of an object type in a table with other columns

(12)

Outline

1 Introduction

2 Creating Nested Tables

Constraints

Indexing

3 Modifications

Insert

Update

Delete

4 Querying

Introduction

Simple Queries

Unnesting

Empty and Member Keywords

Summary

(13)

A More Complicated Nested Table

coursename ects prerequest lectures

Alg 5 null

1 Introduction 1.1 1.3 1.5 2 Abstract Data Types 2.2 2.4 3 Searching

3.2 3.6 3.8

4 Graphs 4.1

4.2 5 NP and NP Complete

4.1 5.2 5.4

C 5 null

1 The C Language C1.1 C2.1 C2.4 2 Control Structures C3.1_C4.1 3 Functions and Parameters 5.1 6.1 4 The Joy of Pointers C7.1_C8.1

OOP 5 Alg_C

1 Introduction to OOP OO Stuff On with the OO Stuff 2 Classes and Objects Class 2.2_{Class 4.2} 3 Inheritance Inheritance 1.1

Inheritance 2.2

DBS 5 OOP

1 Introduction to DBMSs DBMS in Colors DBMS in More Colors

2 ER Modeling

Class 2.2 Class 2.4 Class 2.6 Class 2.8 3 Query Languages including SQL 3.8

3.10 4 Concurrency Control 4.4 5 Recovery

Recov 1.1 Recov 2.1 Recov 3.1

(14)

Create The Types

c r e a t e o r r e p l a c e t y p e

s t r i n g t

as t a b l e o f v a r c h a r 2

( 2 5 5 ) ;

This is a

collection type

c r e a t e o r r e p l a c e t y p e

l e c t u r e t as o b j e c t

(

num

i n t

,

s u b j e c t

v a r c h a r 2

( 2 5 5 ) ,

e x e r c i s e s

s t r i n g t

) ;

This is an

object type

Create the lecture serie type

c r e a t e o r r e p l a c e t y p e

l e c t u r e s e r i e t

as

t a b l e o f

l e c t u r e t

;

(15)

Create The Types

c r e a t e o r r e p l a c e t y p e

s t r i n g t

as t a b l e o f v a r c h a r 2

( 2 5 5 ) ;

This is a

collection type

c r e a t e o r r e p l a c e t y p e

l e c t u r e t as o b j e c t

(

num

i n t

,

s u b j e c t

v a r c h a r 2

( 2 5 5 ) ,

e x e r c i s e s

s t r i n g t

) ;

This is an

object type

Create the lecture serie type

c r e a t e o r r e p l a c e t y p e

l e c t u r e s e r i e t

as

t a b l e o f

l e c t u r e t

;

Is this a collection type or and object type? Collection!

(16)

Create The Types

c r e a t e o r r e p l a c e t y p e

s t r i n g t

as t a b l e o f v a r c h a r 2

( 2 5 5 ) ;

This is a

collection type

c r e a t e o r r e p l a c e t y p e

l e c t u r e t as o b j e c t

(

num

i n t

,

s u b j e c t

v a r c h a r 2

( 2 5 5 ) ,

e x e r c i s e s

s t r i n g t

) ;

This is an

object type

Create the lecture serie type

c r e a t e o r r e p l a c e t y p e

l e c t u r e s e r i e t

as

t a b l e o f

l e c t u r e t

;

Is this a collection type or and object type?

(17)

Create The Types

c r e a t e o r r e p l a c e t y p e

s t r i n g t

as t a b l e o f v a r c h a r 2

( 2 5 5 ) ;

This is a

collection type

c r e a t e o r r e p l a c e t y p e

l e c t u r e t as o b j e c t

(

num

i n t

,

s u b j e c t

v a r c h a r 2

( 2 5 5 ) ,

e x e r c i s e s

s t r i n g t

) ;

This is an

object type

Create the lecture serie type

c r e a t e o r r e p l a c e t y p e

l e c t u r e s e r i e t

as

t a b l e o f

l e c t u r e t

;

Is this a collection type or and object type? Collection!

(18)

Create the Table

c r e a t e t a b l e

course (

course name

v a r c h a r 2

( 2 5 5 )

c o n s t r a i n t

c o u r s e p k

p r i m a r y key

,

e c t s

i n t e g e r check

( e c t s

>

0 ) ,

p r e r e q u e s t s

s t r i n g t

,

l e c t u r e s

l e c t u r e s e r i e t

)

nested

t a b l e

p r e r e q u e s t s

s t o r e as

c o u r s e p r e r e q u e s t s n t

nested

t a b l e

l e c t u r e s

s t o r e as

c o u r s e l e c t u r e s n t

(

nested

t a b l e

e x e r c i s e s

s t o r e as

c o u r s e l e c t u r e e x e r c i s e n t ) ;

Have two nested columns

The column

prerequest

is nested one level

The column

lectures

is nested two levels

Note the

store as

clauses

(19)

Outline

1 Introduction

2 Creating Nested Tables

Constraints

Indexing

3 Modifications

Insert

Update

Delete

4 Querying

Introduction

Simple Queries

Unnesting

Empty and Member Keywords

Summary

5 Summary

(20)

On Types vs. On Tables

Overall Design

Constraints and defaults

are not

supported in type specifications

Constraints and defaults

are

supported in create table statements

Example

The

not null

is not allowed in the following

c r e a t e o r r e p l a c e t y p e

a d d r e s s t n o t a l l o w e d

as o b j e c t

(

s t r e e t

v a r c h a r 2

( 2 5 5 )

n o t n u l l

,

−−

n o t a l l o w e d

num

v a r c h a r 2

( 2 5 5 )

n o t n u l l

,

−−

n o t a l l o w e d

z i p

i n t e g e r

n o t n u l l

,

−−

n o t a l l o w e d

c i t y

v a r c h a r 2

( 2 5 5 )

n o t n u l l

) ;

−−

n o t a l l o w e d

It will be shown in the following how support constraints when

creating the tables

(21)

Not Null on Nested Object Column

c r e a t e t a b l e

s t u d e n t (

ssn

v a r c h a r 2

( 2 5 5 )

c o n s t r a i n t

s t u d e n t p k

p r i m a r y key

,

student name

v a r c h a r 2

( 2 5 5 )

n o t n u l l

,

address

a d d r e s s t

n o t n u l l

,

e m a i l

e m a i l t )

nested t a b l e

e m a i l

s t o r e as

s t u d e n t e m a i l n t ;

Make sure the street name is not null, note a level lower than above

a l t e r t a b l e

s t u d e n t

add c o n s t r a i n t

s t u d e n t a d d r e s s s t r e e t n n

check

( address . s t r e e t

i s

n o t n u l l

) ;

(22)

Not Null on Collection Type Column

c r e a t e t a b l e

c o u r s e n o t n u l l (

course name

v a r c h a r 2

( 2 5 5 )

c o n s t r a i n t

c o u r s e n n p k

p r i m a r y key

,

e c t s

i n t e g e r

check

( e c t s

>

0 ) ,

p r e r e q u e s t s

s t r i n g t

n o t n u l l

,

l e c t u r e s

l e c t u r e s e r i e t

n o t n u l l

)

nested t a b l e

p r e r e q u e s t s

s t o r e as

c o u r s e n n p r e r e q u e s t s n t

nested t a b l e

l e c t u r e s

s t o r e as

c o u r s e n n l e c t u r e s n t

(

nested t a b l e

e x e r c i s e s

s t o r e as

c o u r s e n n l e c t u r e e x e r c i s e n t ) ;

Results in an ORA-02331

Must be do at the type level instead, e.g.,

c r e a t e o r r e p l a c e t y p e

s t r i n g n o t n u l l t

(23)

Not Null on Collection Type Column

c r e a t e t a b l e

c o u r s e n o t n u l l (

course name

v a r c h a r 2

( 2 5 5 )

c o n s t r a i n t

c o u r s e n n p k

p r i m a r y key

,

e c t s

i n t e g e r

check

( e c t s

>

0 ) ,

p r e r e q u e s t s

s t r i n g t

n o t n u l l

,

l e c t u r e s

l e c t u r e s e r i e t

n o t n u l l

)

nested t a b l e

p r e r e q u e s t s

s t o r e as

c o u r s e n n p r e r e q u e s t s n t

nested t a b l e

l e c t u r e s

s t o r e as

c o u r s e n n l e c t u r e s n t

(

nested t a b l e

e x e r c i s e s

s t o r e as

c o u r s e n n l e c t u r e e x e r c i s e n t ) ;

Results in an ORA-02331

Must be do at the type level instead, e.g.,

c r e a t e o r r e p l a c e t y p e

s t r i n g n o t n u l l t

as t a b l e o f v a r c h a r 2

( 2 5 5 )

n o t n u l l

;

(24)

Primary and Unique Keys on Nested Object Columns

c r e a t e t a b l e

s t u d e n t (

ssn

v a r c h a r 2

( 2 5 5 )

c o n s t r a i n t

s t u d e n t p k

p r i m a r y key

,

student name

v a r c h a r 2

( 2 5 5 )

n o t n u l l

,

address

a d d r e s s t

n o t n u l l

,

e m a i l

e m a i l t )

nested t a b l e

e m a i l

s t o r e as

s t u d e n t e m a i l n t ;

Make sure that no student live on the same street and number

a l t e r t a b l e

s t u d e n t

add c o n s t r a i n t

s t u d e n t a d d r e s s u k

unique

( address . s t r e e t , address . num ) ;

(25)

Primary and Unique Keys on Nested Object Columns

c r e a t e t a b l e

s t u d e n t (

ssn

v a r c h a r 2

( 2 5 5 )

c o n s t r a i n t

s t u d e n t p k

p r i m a r y key

,

student name

v a r c h a r 2

( 2 5 5 )

n o t n u l l

,

address

a d d r e s s t

n o t n u l l

,

e m a i l

e m a i l t )

nested t a b l e

e m a i l

s t o r e as

s t u d e n t e m a i l n t ;

Make sure that no student live on the same street and number

a l t e r t a b l e

s t u d e n t

add c o n s t r a i n t

s t u d e n t a d d r e s s u k

unique

( address . s t r e e t , address . num ) ;

Primary keys are supported in a similar fashion

(26)

Primary and Unique Keys on Collection Type Columns

A nested column that is a collection cannot be part of a primary key

A nested column cannot be part of a unique key

(27)

Foreign Keys and Nested Columns I

The prerequests must be known courses

The idea in a relational context

a l t e r t a b l e

c o u r s e p r e r e q u e s t s n t

add c o n s t r a i n t

c o u r s e c o u r s e f k

f o r e i g n key

(

c o l u m n v a l u e

)

r e f e r e n c e s

course ( course name ) ;

However, this does not work: A nested column cannot be used in a

foreign key

a l t e r t a b l e

c o u r s e p r e r e q u e s t s n t

add c o n s t r a i n t

c o u r s e c o u r s e f k

check

(

c o l u m n v a l u e i n

(

s e l e c t

course name

from

course ) ) ;

Not allowed either

(28)

Foreign Keys and Nested Columns II

Must do manual checking, the following must return zero

s e l e c t c oun t

(

∗

)

from

(

s e l e c t

cs .

c o l u m n v a l u e as

c o u r s e p r e r e

from

course c ,

t a b l e

( c . p r e r e q u e s t s ) cs

minus

s e l e c t

course name

from

course ) ) ;

Bad

It is extremely ugly, and cannot be forgiven, that values in collections

cannot be used in foreign key constraints! Fix this Oracle!

(29)

Summary: Constraints

Very weak support of constraint on collection type columns

Good support of constraints on object type columns

(30)

Outline

1 Introduction

2 Creating Nested Tables

Constraints

Indexing

3 Modifications

Insert

Update

Delete

4 Querying

Introduction

Simple Queries

Unnesting

Empty and Member Keywords

Summary

(31)

Non-Unique Indexes on a Nested Column

c r e a t e t a b l e

s t u d e n t (

ssn

v a r c h a r 2

( 2 5 5 )

c o n s t r a i n t

s t u d e n t p k

p r i m a r y key

,

student name

v a r c h a r 2

( 2 5 5 )

n o t n u l l

,

address

a d d r e s s t

n o t n u l l

,

e m a i l

e m a i l t )

nested t a b l e

e m a i l

s t o r e as

s t u d e n t e m a i l n t ;

Create an index on an object type

c r e a t e i n d e x

s t u d e n t a d d r e s s z i p i d x

on

s t u d e n t (

address

.

z i p

) ;

Create an index on a collection type

c r e a t e i n d e x

s t u d e n t e m a i l i d x

on

s t u d e n t e m a i l n t (

c o l u m n v a l u e

) ;

Note this special value

column value

(32)

Non-Unique Indexes on a Nested Column

c r e a t e t a b l e

s t u d e n t (

ssn

v a r c h a r 2

( 2 5 5 )

c o n s t r a i n t

s t u d e n t p k

p r i m a r y key

,

student name

v a r c h a r 2

( 2 5 5 )

n o t n u l l

,

address

a d d r e s s t

n o t n u l l

,

e m a i l

e m a i l t )

nested t a b l e

e m a i l

s t o r e as

s t u d e n t e m a i l n t ;

Create an index on an object type

c r e a t e i n d e x

s t u d e n t a d d r e s s z i p i d x

on

s t u d e n t (

address

.

z i p

) ;

Create an index on a collection type

c r e a t e i n d e x

s t u d e n t e m a i l i d x

on

s t u d e n t e m a i l n t (

c o l u m n v a l u e

) ;

(33)

An Index on a Multi-Level Nested Column

c r e a t e t a b l e

course (

course name

v a r c h a r 2

( 2 5 5 )

c o n s t r a i n t

c o u r s e p k

p r i m a r y key

,

e c t s

i n t e g e r check

( e c t s

>

0 ) ,

p r e r e q u e s t s

s t r i n g t ,

l e c t u r e s

l e c t u r e s e r i e t )

nested t a b l e

p r e r e q u e s t s

s t o r e as

c o u r s e p r e r e q u e s t s n t

nested t a b l e

l e c t u r e s

s t o r e as

c o u r s e l e c t u r e s n t

(

nested t a b l e

e x e r c i s e s

s t o r e as

c o u r s e l e c t u r e e x e r c i s e n t ) ;

Create an index on the lecture subjects

c r e a t e i n d e x

c o u r s e l e c t u r e s u b j e c t i d x

on

c o u r s e l e c t u r e s n t

(

s u b j e c t

) ;

Must know that

subject

is a column in the nested table

The name of the nested table is needed, otherwise like other indexes

(34)

An Index on a Multi-Level Nested Column

c r e a t e t a b l e

course (

course name

v a r c h a r 2

( 2 5 5 )

c o n s t r a i n t

c o u r s e p k

p r i m a r y key

,

e c t s

i n t e g e r check

( e c t s

>

0 ) ,

p r e r e q u e s t s

s t r i n g t ,

l e c t u r e s

l e c t u r e s e r i e t )

nested t a b l e

p r e r e q u e s t s

s t o r e as

c o u r s e p r e r e q u e s t s n t

nested t a b l e

l e c t u r e s

s t o r e as

c o u r s e l e c t u r e s n t

(

nested t a b l e

e x e r c i s e s

s t o r e as

c o u r s e l e c t u r e e x e r c i s e n t ) ;

Create an index on the lecture subjects

c r e a t e i n d e x

c o u r s e l e c t u r e s u b j e c t i d x

on

c o u r s e l e c t u r e s n t

(

s u b j e c t

) ;

Must know that

subject

is a column in the nested table

(35)

Unique Indexes on a Nested Column

Create an unique index

c r e a t e

unique

i n d e x

s t u d e n t e m a i l i d x

on

s t u d e n t e m a i l n t (

c o l u m n v a l u e

) ;

ssn

student name

address

email

Insert with an existing email address

i n s e r t i n t o

s t u d e n t

v a l u e s

( 6 0 6 ,

’ Fred ’

,

a d d r e s s t (

’ Pie ’

,

’ 32 ’

, 9000 ,

’ A a l b o r g ’

) ,

e m a i l t (

’ curt@cs . com ’

) ) ;

Results in an integrity constraint violation

(36)

Quiz: Unique Indexes on a Nested Column

ssn

student name

address

email

Will this insert statement with unique index on column value work?

i n s e r t i n t o

s t u d e n t

v a l u e s

( 6 0 6 ,

’ Fred ’

,

a d d r e s s t (

’ Pie ’

,

’ 32 ’

, 9000 ,

’ A a l b o r g ’

) ,

e m a i l t (

’ fred@nice . org ’

,

’ ann@nice . org ’

) ) ;

No the uniqueness is specified at the column value!

Will this insert statement work?

i n s e r t i n t o

s t u d e n t

v a l u e s

( 6 0 6 ,

’ Fred ’

,

a d d r e s s t (

’ Pie ’

,

’ 32 ’

, 9000 ,

’ A a l b o r g ’

) ,

e m a i l t (

’ fred@nice . org ’

) ) ;

(37)

Quiz: Unique Indexes on a Nested Column

ssn

student name

address

email

Will this insert statement with unique index on column value work?

i n s e r t i n t o

s t u d e n t

v a l u e s

( 6 0 6 ,

’ Fred ’

,

a d d r e s s t (

’ Pie ’

,

’ 32 ’

, 9000 ,

’ A a l b o r g ’

) ,

e m a i l t (

’ fred@nice . org ’

,

’ ann@nice . org ’

) ) ;

No the uniqueness is specified at the column value!

Will this insert statement work?

i n s e r t i n t o

s t u d e n t

v a l u e s

( 6 0 6 ,

’ Fred ’

,

a d d r e s s t (

’ Pie ’

,

’ 32 ’

, 9000 ,

’ A a l b o r g ’

) ,

e m a i l t (

’ fred@nice . org ’

) ) ;

Yes!

(38)

Quiz: Unique Indexes on a Nested Column

ssn

student name

address

email

Will this insert statement with unique index on column value work?

i n s e r t i n t o

s t u d e n t

v a l u e s

( 6 0 6 ,

’ Fred ’

,

a d d r e s s t (

’ Pie ’

,

’ 32 ’

, 9000 ,

’ A a l b o r g ’

) ,

e m a i l t (

’ fred@nice . org ’

,

’ ann@nice . org ’

) ) ;

No the uniqueness is specified at the column value!

Will this insert statement work?

i n s e r t i n t o

s t u d e n t

v a l u e s

( 6 0 6 ,

’ Fred ’

,

a d d r e s s t (

’ Pie ’

,

’ 32 ’

, 9000 ,

’ A a l b o r g ’

) ,

e m a i l t (

’ fred@nice . org ’

) ) ;

(39)

Quiz: Unique Indexes on a Nested Column

ssn

student name

address

email

Will this insert statement with unique index on column value work?

i n s e r t i n t o

s t u d e n t

v a l u e s

( 6 0 6 ,

’ Fred ’

,

a d d r e s s t (

’ Pie ’

,

’ 32 ’

, 9000 ,

’ A a l b o r g ’

) ,

e m a i l t (

’ fred@nice . org ’

,

’ ann@nice . org ’

) ) ;

No the uniqueness is specified at the column value!

Will this insert statement work?

i n s e r t i n t o

s t u d e n t

v a l u e s

( 6 0 6 ,

’ Fred ’

,

a d d r e s s t (

’ Pie ’

,

’ 32 ’

, 9000 ,

’ A a l b o r g ’

) ,

e m a i l t (

’ fred@nice . org ’

) ) ;

Yes!

(40)

Summary: Creating Nested Table

Collection type vs. object type

The special value

column value

Good support for primary key and unique key on nested columns

No support for foreign keys on nested columns

This is clearly a missing feature

Recommendations

Composite attribute from ERD can quite naturally be used in NFNF

Multivalue attributes are well-suited for using with NFNF

(41)

Summary: Creating Nested Table

Collection type vs. object type

The special value

column value

Good support for primary key and unique key on nested columns

No support for foreign keys on nested columns

This is clearly a missing feature

Recommendations

Composite attribute from ERD can quite naturally be used in NFNF

Multivalue attributes are well-suited for using with NFNF

Be careful nesting more than two levels

(42)

Outline

1 Introduction

2 Creating Nested Tables

Constraints

Indexing

3 Modifications

Insert

Update

Delete

4 Querying

Introduction

Simple Queries

Unnesting

Empty and Member Keywords

Summary

(43)

Introduction

Rules of Thumb

At the outer-table level insert, update, and delete syntax is as in plain

SQL

At the inner-table level the syntax has changed

(44)

Outline

1 Introduction

2 Creating Nested Tables

Constraints

Indexing

3 Modifications

Insert

Update

Delete

4 Querying

Introduction

Simple Queries

Unnesting

Empty and Member Keywords

Summary

(45)

Insert

ssn

student name

address

email

i n s e r t i n t o

s t u d e n t

v a l u e s

(

102 ,

’ Pete ’

,

a d d r e s s t

(

’ Pie ’

,

’ 31 ’

, 9000 ,

’ A a l b o r g ’

) ,

e m a i l t

(

’ pete@verynice . org ’

,

’ pete@example . com ’

)

) ;

i n s e r t i n t o

s t u d e n t

v a l u e s

(

101 ,

’ C u r t ’

,

a d d r e s s t

(

’ Elm ’

,

’ 11 ’

, 8000 ,

’ Aarhus ’

) ,

e m a i l t

(

’ curt@cs . com ’

)

) ;

ssn

student name

address

email

Even if only a single row in the nested column

email t

must be used

(46)

Insert

ssn

student name

address

email

i n s e r t i n t o

s t u d e n t

v a l u e s

(

102 ,

’ Pete ’

,

a d d r e s s t

(

’ Pie ’

,

’ 31 ’

, 9000 ,

’ A a l b o r g ’

) ,

e m a i l t

(

’ pete@verynice . org ’

,

’ pete@example . com ’

)

) ;

i n s e r t i n t o

s t u d e n t

v a l u e s

(

101 ,

’ C u r t ’

,

a d d r e s s t

(

’ Elm ’

,

’ 11 ’

, 8000 ,

’ Aarhus ’

) ,

e m a i l t

(

’ curt@cs . com ’

)

) ;

ssn

student name

address

email

(47)

Insert with Null I

ssn

student name

address

email

i n s e r t i n t o

s t u d e n t

v a l u e s

(

303 ,

’ Nick ’

,

a d d r e s s t (

’ Sun ’

,

’ 21 ’

, 9000 ,

’ A a l b o r g ’

) ,

n u l l

) ;

ssn

student name

address

email

202 Ann Elm 20 9000 Aalborg [email protected] [email protected] 102 Pete Pie 31 9000 Aalborg [email protected] [email protected] 101 Curt Elm 11 8000 Aarhus [email protected] 303 Nick Sun 21 9000 Aalborg

null

The address cannot be a

null

value, decleared as

not null

(48)

Insert with Null II

i n s e r t i n t o

s t u d e n t

v a l u e s

( 7 0 7 ,

’ Jim ’

,

n u l l

,

n u l l

)

Disallowed because

address

is declared

not null

i n s e r t i n t o

s t u d e n t s

v a l u e s

( 7 0 7 ,

’ Jim ’

, a d d r e s s t (

n u l l

,

n u l l

,

n u l l

,

n u l l

) ,

n u l l

)

Disallowed, because

address.street

is declared

not null

i n s e r t i n t o

s t u d e n t s

v a l u e s

( 7 0 7 ,

’ Jim ’

, a d d r e s s t (

’ Boulevarden ’

,

n u l l

,

n u l l

,

n u l l

) ,

n u l l

)

(49)

Insert with Column Using Column Names

i n s e r t i n t o

s t u d e n t ( ssn , student name , address )

v a l u e s

( 6 0 6 ,

’ Jim ’

, a d d r e s s t (

’ Boulevarden ’

, 20 , 9000 ,

’ A a l b o r g ’

) )

This is the same

i n s e r t i n t o

s t u d e n t s ( ssn , student name , s . address )

v a l u e s

( 6 0 6 ,

’ Jim ’

, a d d r e s s t (

’ Boulevarden ’

, 20 , 9000 ,

’ A a l b o r g ’

) )

Weird correlation name is optional when inserting

(50)

Insert with Column Using Column Names

i n s e r t i n t o

s t u d e n t ( ssn , student name , address )

v a l u e s

( 6 0 6 ,

’ Jim ’

, a d d r e s s t (

’ Boulevarden ’

, 20 , 9000 ,

’ A a l b o r g ’

) )

This is the same

i n s e r t i n t o

s t u d e n t s ( ssn , student name , s . address )

v a l u e s

( 6 0 6 ,

’ Jim ’

, a d d r e s s t (

’ Boulevarden ’

, 20 , 9000 ,

’ A a l b o r g ’

) )

(51)

Insert with Column Using Column Names

i n s e r t i n t o

s t u d e n t ( ssn , student name , address )

v a l u e s

( 6 0 6 ,

’ Jim ’

, a d d r e s s t (

’ Boulevarden ’

, 20 , 9000 ,

’ A a l b o r g ’

) )

This is the same

i n s e r t i n t o

s t u d e n t s ( ssn , student name , s . address )

v a l u e s

( 6 0 6 ,

’ Jim ’

, a d d r e s s t (

’ Boulevarden ’

, 20 , 9000 ,

’ A a l b o r g ’

) )

Weird correlation name is optional when inserting

(52)

Illegale Inserts

i n s e r t i n t o

s t u d e n t s

( ssn , student name ,

s . address . s t r e e t , s . address . num , s . address . z i p , s . address . c i t y )

v a l u e s

( 6 0 6 ,

’ Jim ’

,

’ Boulevarden ’

, 20 , 9000 ,

’ A a l b o r g ’

)

Does not work: only simple column names are allowed, not that

logical

i n s e r t i n t o

s t u d e n t

v a l u e s

( 6 0 6 ,

’ Jim ’

,

’ Boulevarden ’

, 20 , 9000 ,

’ A a l b o r g ’

)

(53)

Illegale Inserts

i n s e r t i n t o

s t u d e n t s

( ssn , student name ,

s . address . s t r e e t , s . address . num , s . address . z i p , s . address . c i t y )

v a l u e s

( 6 0 6 ,

’ Jim ’

,

’ Boulevarden ’

, 20 , 9000 ,

’ A a l b o r g ’

)

Does not work: only simple column names are allowed, not that

logical

i n s e r t i n t o

s t u d e n t

v a l u e s

( 6 0 6 ,

’ Jim ’

,

’ Boulevarden ’

, 20 , 9000 ,

’ A a l b o r g ’

)

Too many values, this is quite logical!

(54)

Insert Empty Collection

i n s e r t i n t o

s t u d e n t

v a l u e s

(

404 ,

’ E r i c ’

,

a d d r e s s t (

’ B l v ’

,

’ 29 ’

, 9000 ,

’ A a l b o r g ’

) ,

e m a i l t ( ) ) ;

ssn

student name

address

email

null

404 Eric Blv 29 9000 Aalborg <empty>

Note the empty cell value

(55)

Insert Collection

ssn

student name

address

email

null

Giv Eric an email address

update

s t u d e n t

s e t

e m a i l = e m a i l t (

’ e r i c @ n i c e . org ’

)

where

ssn = 404;

ssn

student name

address

email

null

404 Eric Blv 29 9000 Aalborg [email protected]

Updates the entire collection column

Multiple email addresses can be provided

(56)

Insert Collection, Alternative

ssn

student name

address

email

null

i n s e r t i n t o t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s

where

s . ssn = 404)

v a l u e s

(

’ e r i c @ n i c e . org ’

) ;

ssn

student name

address

email

null

This insert is actually an update!

(57)

Insert Multi-Level Collection

Alg 5 null

1 Introduction 1.1 1.3 1.5 2 Abstract Data Types 2.2 2.4 3 Searching

3.2 3.6 3.8

4 Graphs 4.1

4.2 5 NP and NP Complete

4.1 5.2 5.4

i n s e r t i n t o course v a l u e s ( ’ Alg ’,

5 ,

n u l l,

l e c t u r e s e r i e t(

l e c t u r e t( 1 , ’ I n t r o d u c t i o n ’,

s t r i n g t(’ 1 . 1 ’, ’ 1 . 3 ’, ’ 1 . 5 ’) ) ,

l e c t u r e t( 2 , ’ A b s t r a c t Data Types ’,

s t r i n g t(’ 2 . 2 ’, ’ 2 . 4 ’) ) ,

l e c t u r e t( 3 , ’ Searching ’,

s t r i n g t(’ 3 . 2 ’, ’ 3 . 6 ’, ’ 3 . 8 ’) ) ,

l e c t u r e t( 4 , ’ Graphs ’,

s t r i n g t(’ 4 . 1 ’, ’ 4 . 2 ’) ) ,

l e c t u r e t( 5 , ’NP and NP Complete ’,

s t r i n g t(’ 4 . 1 ’, ’ 5 . 2 ’, ’ 5 . 4 ’) ) )

) ;

(58)

Outline

1 Introduction

2 Creating Nested Tables

Constraints

Indexing

3 Modifications

Insert

Update

Delete

4 Querying

Introduction

Simple Queries

Unnesting

Empty and Member Keywords

Summary

(59)

Update Part of a Nested Column

ssn

student name

address

email

Ann lives on Sun Boulevard not Elm Street

update

s t u d e n t

s

s e t

s

. address . s t r e e t =

’ Sun ’

where

ssn = 202;

Note the correlation name is required, i.e., both below are wrong

update

s t u d e n t

−−

wrong

s e t

address . s t r e e t =

’ Sun ’

where

ssn = 202;

update

s t u d e n t

−−

wrong

s e t

s t u d e n t . address . s t r e e t =

’ Sun ’

where

ssn = 202;

(60)

Update Entire Nested Column

ssn

student name

address

email

Move Pete to Aarhus

update

s t u d e n t

s e t

address =

a d d r e s s t

(

’ Elm ’

,

’ 13 ’

, 8000 ,

’ Aarhus ’

)

where

ssn = 102;

Looks like plain SQL update

Note the use of the constructor

(61)

Update a Collection using Table Syntax

ssn

student name

address

email

null

Changes Curt’s cs address to a .edu domain

update

t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s

where

s . ssn = 404) s t

s e t v a l u e

( s t ) =

’ curt@cs . edu ’

where v a l u e

( s t ) =

’ curt@cs . com ’

Restriction on the Query

Must return a single collection

Select list must contain this single collection

(62)

Quiz: Update a Collection using Table Syntax

ssn

student name

address

email

Changes Ann’s nice.org address to a .cs.org domain

update t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s

where

s . ssn = 202) x

s e t v a l u e

( x ) =

’ ann@cs . org ’

ssn

student name

address

email

202 Ann Elm 20 9000 Aalborg [email protected] [email protected] 102 Pete Pie 31 9000 Aalborg [email protected]

[email protected] 101 Curt Elm 11 8000 Aarhus [email protected]

(63)

Quiz: Update a Collection using Table Syntax

ssn

student name

address

email

Changes Ann’s nice.org address to a .cs.org domain

update t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s

where

s . ssn = 202) x

s e t v a l u e

( x ) =

’ ann@cs . org ’

ssn

student name

address

email

Both rows are updated!

(64)

Quiz: Update a Collection using Table Syntax, Again

ssn

student name

address

email

update t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s

where

s . ssn = 202) x

s e t v a l u e

( x ) =

’ ann@cs . org ’

where v a l u e

( x ) =

’ ann@nice . org ’

Alternative notation is

update t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s

where

s . ssn = 202)

s e t c o l u m n v a l u e

=

’ ann@cs . org ’

where c o l u m n v a l u e

=

’ ann@nice . org ’

ssn

student name

address

email

(65)

Quiz: Update a Collection using Table Syntax, Again

ssn

student name

address

email

update t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s

where

s . ssn = 202) x

s e t v a l u e

( x ) =

’ ann@cs . org ’

where v a l u e

( x ) =

’ ann@nice . org ’

Alternative notation is

update t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s

where

s . ssn = 202)

s e t c o l u m n v a l u e

=

’ ann@cs . org ’

where c o l u m n v a l u e

=

’ ann@nice . org ’

ssn

student name

address

email

This is the result wanted!

(66)

Quiz: Update a Collection using Table Syntax, Again

ssn

student name

address

email

update t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s

where

s . ssn = 202) x

s e t v a l u e

( x ) =

’ ann@cs . org ’

where v a l u e

( x ) =

’ ann@nice . org ’

Alternative notation is

update t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s

where

s . ssn = 202)

s e t c o l u m n v a l u e

=

’ ann@cs . org ’

where c o l u m n v a l u e

=

’ ann@nice . org ’

ssn

student name

address

email

(67)

Update a Part of Multiple Collections I

ssn

student name

address

email

Update all ’[email protected]’ domains to ’[email protected]’

Assume that email addresses are not unique!

update t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s )

s e t c o l u m n v a l u e

=

’ pete@nice . org ’

where c o l u m n v a l u e

=

’ pete@example . com ’

Gives an ORA-01427 error, more than one collection in table()

Cannot be done in SQL must be done in PL/SQL

(68)

Update a Part of Multiple Collections II

c r e a t e o r r e p l a c e procedure

u p d a t e s t u d e n t e m a i l (

f r o m v a l u e

i n varchar2

,

t o v a l u e

i n v a r c h a r 2

)

i s

t y p e

t s n n t a b

i s t a b l e o f

s t u d e n t . ssn%

t y p e

;

v

t s n n t a b

;

be gin

s e l e c t

s . ssn

b u l k c o l l e c t

i n t o

v

from

s t u d e n t s

where

f r o m v a l u e

member o f

s . e m a i l ;

f o r a l l

i

i n

v .

f i r s t

. . v .

l a s t

update t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s

where

s . ssn = v ( i ) )

s e t c o l u m n v a l u e

= t o v a l u e

where c o l u m n v a l u e

= f r o m v a l u e ;

end

;

Find all outer rows to update

(69)

Update a Part of Multiple Collections III

ssn

student name

address

email

execute

u p d a t e s t u d e n t e m a i l (

’ pete@example . com ’

,

’ pete@nice . org ’

) ;

ssn

student name

address

email

Note

Procedural need because table function can use only one collection

Ugly Oracle, fix this!

(70)

Update a Collection using Constructor

ssn

student name

address

email

null

Give Eric an email address

update

s t u d e n t

s e t

e m a i l = e m a i l t (

’ e r i c @ n i c e . org ’

)

where

ssn = 303;

ssn

student name

address

email

null

(71)

Updating a Multiple-Level Nested Column

Alg 5

null

1 Introduction

1.1 1.3 1.5 2 Abstract Data Types 2.2 2.4

3 Searching

3.2 3.6 3.8

4 Graphs 4.1

4.2

5 NP and NP Complete 4.1 5.2 5.4

Course number 2 should be number 7 instead

update t a b l e

(

s e l e c t

l e c t u r e s

from

course

where

course name =

’ Alg ’

) s t

s e t

s t .

no

= 7

where

s t .

no

= 2 ;

(72)

Updating a Multiple-Level Nested Column, cont.

Alg 5

null

1 Introduction

1.1 1.3 1.5 2 Abstract Data Types 2.2 2.4

3 Searching

3.2 3.6 3.8

4 Graphs 4.1

4.2

5 NP and NP Complete 4.1 5.2 5.4

The first exercise for the third lecture should be 3.3 and not 3.2

update t a b l e

(

s e l e c t

l . e x e r c i s e s

from

course ,

t a b l e

( l e c t u r e s ) l

where

course name =

’ Alg ’

and

l .

no

= 3 ) e

s e t

v a l u e

( e ) =

’ 3 . 3 ’

where v a l u e

( e ) =

’ 3 . 2 ’

(73)

Outline

1 Introduction

2 Creating Nested Tables

Constraints

Indexing

3 Modifications

Insert

Update

Delete

4 Querying

Introduction

Simple Queries

Unnesting

Empty and Member Keywords

Summary

5 Summary

(74)

Delete Entire Rows

ssn

student name

address

email

null

Delete Eric and Nick

d e l e t e from

s t u d e n t

where

ssn = 404

o r

ssn = 303;

ssn

student name

address

email

(75)

Delete Entire Rows Based on Nested Column

ssn

student name

address

email

Delete the students that live in Aalborg

d e l e t e from

s t u d e n t s

where

s . address . c i t y =

’ A a l b o r g ’

ssn

student name

address

email

101 Curt Elm 11 8000 Aarhus [email protected]

As before the correlation name is needed!

Straight-forward compared to plain SQL

(76)

Delete Entire Rows Based on Collection I

ssn

student name

address

email

Delete student that have the email address ’[email protected]’

d e l e t e from

s t u d e n t

where

’ ann@nice . org ’

member o f

s t u d e n t . e m a i l

ssn

student name

address

email

102 Pete Pie 31 9000 Aalborg [email protected] [email protected] 101 Curt Elm 11 8000 Aarhus [email protected]

(77)

Delete Entire Rows Based on Collection II

ssn

student name

address

email

Delete students that have an email address like ’example.com’

Find the rows that have the email address like ’example.com’

s e l e c t

∗

from

s t u d e n t s ,

t a b l e

( s . e m a i l ) x l

where

x l .

c o l u m n v a l u e l i k e

’%example . com ’

;

However, more than one table is involved, therefore the following is

illegale

d e l e t e from

s t u d e n t s ,

t a b l e

( s . e m a i l ) x l

where

x l .

c o l u m n v a l u e l i k e

’%example . com ’

;

This is not logical at the conceptual level!

(78)

Delete Entire Rows Based on Collection III

ssn

student name

address

email

Delete students that have an email address like ’example.com’

d e l e t e from

s t u d e n t s

where

1 = (

s e l e c t

1 from

t a b l e

( s . e m a i l ) x l

where

x l .

c o l u m n v a l u e l i k e

’%example . com ’

)

ssn

student name

address

email

101 Curt Elm 11 8000 Aarhus [email protected]

Note that only one row is returned for each outer row!

Sorry, could not find nicer formulation

(79)

Delete Single Value from a Collection

ssn

student name

address

email

Deletes Ann’s email address at nice.org (but not the other addresses)

d e l e t e from t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s

where

s . ssn = 202) s t

where v a l u e

( s t ) =

’ ann@nice . org ’

ssn

student name

address

email

202 Ann Elm 20 9000 Aalborg [email protected] 102 Pete Pie 31 9000 Aalborg [email protected] [email protected] 101 Curt Elm 11 8000 Aarhus [email protected]

(80)

Delete a Single Value from a Number of Collections I

ssn

student name

address

email

Deletes .com email addresses

d e l e t e from t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s ) s t

where v a l u e

( s t )

l i k e

’ %.com ’

Results in ORA-01427 error

(81)

Delete a Single Value from a Number of Collections II

c r e a t e o r r e p l a c e procedure

d e l e t e s t u d e n t e m a i l l i k e (

p a t t e r n

i n v a r c h a r 2

)

i s

t y p e

t s n n t a b

i s t a b l e o f

s t u d e n t . ssn%

t y p e

;

v

t s n n t a b

;

be gin

s e l e c t

d i s t i n c t

s . ssn

b u l k c o l l e c t

i n t o

v

from

s t u d e n t s ,

t a b l e

( s . e m a i l )

where

c o l u m n v a l u e l i k e

p a t t e r n ;

f o r a l l

i

i n

v .

f i r s t

. . v .

l a s t

d e l e t e from

t a b l e

(

s e l e c t

s . e m a i l

from

s t u d e n t s

where

s . ssn = v ( i ) )

where c o l u m n v a l u e l i k e

p a t t e r n ;

end

;

/

Find the outer rows where collection must be modified

Modify the collection for each outer row

(82)

Delete a Single Value from a Number of Collections III

ssn

student name

address

email

execute

d e l e t e s t u d e n t e m a i l l i k e (

’ %.com ’

) ;

ssn

student name

address

email

202 Ann Elm 20 9000 Aalborg [email protected] 102 Pete Pie 31 9000 Aalborg [email protected] 101 Curt Elm 11 8000 Aarhus <empty>

The Curt tuple has an

empty

set of emails

Recall

{ }

,

∅

(83)

Delete All Values from a Collection

OOP 5 Alg

C

1 Introduction to OOP OO Stuff On with the OO Stuff 2 Classes and Objects Class 2.2