ORDBMS

Question 1

Set type

Answer 1

setof(varchar(20));

Question 2

arrays

Answer 2

varchar(20) array[10]

Question 3

Large object type

Answer 3

clob - character large object

blob - binary large object

Question 4

creating types

Answer 4

create type [name] as (
[attributes]
)

the type ‘name’ ca now be the attribute for another type or table

Question 5

Create table over type objects

Answer 5

Create table [table name] of [obj name];

Question 6

define composite attribute in line

Answer 6

create type Book as (
      ...
      row publisher (
          name varchar(20),
           year int
      ),
      ....
);

Question 7

add methods to table/type

Answer 7

create type Book as (
      ...
      method read(pages 
       integer)
)

create method body separately

Question 8

how to write a method

Answer 8

create method method name for [type or table]

begin
[do stuff]
end

Question 9

insert complex values into relation

Answer 9

insert into books
values
(‘Compilers’ array[‘smith’, ‘jones’], Publisher(‘McGraw Hill’, ‘New york’), set(‘a’, ‘b’))

Question 10

inheritance of types

Answer 10

create type [name] under [super class type]
(
[only need to define new attributes here]
)

Question 11

multiple inheritance

Answer 11

create type TA under
under

Student with (attribute as a)
Teacher with (attribute as b)

there is a collision of a similarly named attribute so we have to rename them to avoid the collision

Question 12

Reference declaration

Answer 12

an attribute of our table can be a reference (or essentially a pointer) to an object in another table

• this prevents having to create duplicate objects
• it also prevents the user from create long cumbersome insert statements if the object is large and has say 100s of attributes

Question 13

Reference declaration syntax

Answer 13

create type Department (
    name varchar(20),
    head ref(Person) 
    scope people
)

head = name of attribute

ref(Person) = head is a reference to a Person type

scope people = the Person to which head will reference is from the people table

Question 14

insert into table with reference attribute

Answer 14

insert into departments values (‘CS’, null)

update departments
set head = (select ref(p) from people as p where name = ‘John)
where name = ‘CS’;

Question 15

self-referential attribute

Answer 15

to specify how objects are to be referenced

ex)
create table people of Person ref is oid system generated

oid = object identifier

Question 16

User created object identifiers

Answer 16

create type Person as (
…
ref using varchar(20)
)

create table people of Person
ref is oid user generated

insert into departments values
(‘CS’, ‘023956203’)

Question 17

Derived object identifier

Answer 17

we can also have object identifiers use an existing PK as the identifier.

ex) create type Person(name varchar(20) primary key,address varchar(20))ref from(name)create table people of Person ref is oid derived

nsert into departments values(CS’,John’)

Question 18

path expressions

Answer 18

select head –> name, head –> address

head - a reference to a person.

name, address - The attribute on the other side of the arrow is the attribute from the referenced person that we want to retrieve.

Question 19

Why use path expressions

Answer 19

We can get attributes from a reference object without needing to use a join.

easier to express queries

Question 20

Querying structured types

Answer 20

use dot operator to get attribute of a composite type

select title, publisher.name
From …

Question 21

Querying data with collection attributes - Sets - in the where clause

Answer 21

select title
from books
where ‘database’ in( unnest([set name]))

the word ‘unnest’ puts the set in a form dbms can search through

Question 22

Querying data from arrays

Answer 22

select author-array[1], author-array[2]
from books
where title = ‘blah’

simply access elements by use of indices

Question 23

Nesting

Answer 23

can retrieve an unnested relation as a nested relation (one with multi valued attributes)

select title, set(author)
from flat-books

use the word ‘set’

Question 24

SQL 1999 functions

Answer 24

create function [function name]({arguments})
returns [return type]
begin
[declarations go here]
[rest of code goes here]
end

Question 25

SQL procedures

Answer 25

create procedure [name] ({[in/out] argument})
begin
[code here]
end