Ex BD2 - Banco de Dados Objeto-Relacional

manipulação.sql

INSERT INTO alunos VALUES (
  ALUNO_TY (
    '55522214800',
    '1234567890',
    'Manuel',
    'Avenida Qualquer',
    'emaiu@serv.dot',
    '123456789',
    'Tramandai',
    'RS',
    TELEFONES_NT(4564, 8596, 6321),
    0,
    'Todas',
    'privada',
    0,
    0,
    'Tem olhos',
    null
  )
);

INSERT INTO turmas VALUES (
  TURMA_TY (
    1,
    'Manha',
    'Turminha',
    2017,
    '6A',
    null
  )
);

INSERT INTO funcionarios VALUES (
  FUNCIONARIO_TY (
    '33322214800',
    '3214567890',
    'Joaquim',
    'Rua uberaba',
    'jmquimu@bol.lol',
    '987654321',
    'Pederneiras',
    'SP',
    TELEFONES_NT(9754, 2856, 9456),
    CONTA_NT(CONTA_TY('Inter', 10, 54)),
    null,
    null
  )
);

UPDATE TABLE
(
  SELECT alunos.telefones
    FROM alunos
  WHERE alunos.cpf = '55522214800'
) telefones
SET telefones.column_value = 1234
WHERE telefones.column_value = 4564;

UPDATE turmas SET ano = ano + 1
WHERE idt = 1;

UPDATE TABLE (
  SELECT contas
    FROM funcionarios
  WHERE cpf = '33322214800'
) conta
SET conta.banco = 'Outro banco';

DELETE FROM TABLE
(
  SELECT alunos.telefones
    FROM alunos
  WHERE cpf = '55522214800'
);

DELETE FROM alunos WHERE cpf = '55522214800';

DELETE FROM turmas WHERE idt = 1;

DELETE FROM TABLE
(
  SELECT contas
    FROM funcionarios
  WHERE cpf = '33322214800'
);

DELETE FROM funcionarios WHERE cpf = '33322214800';

modelagem.sql

CREATE OR REPLACE TYPE TELEFONES_NT AS TABLE OF INTEGER;

CREATE OR REPLACE TYPE PESSOA_TY AS OBJECT
(
    cpf       VARCHAR2(11),
    rg        VARCHAR2(10),
    nome      VARCHAR2(30),
    endereco  VARCHAR2(30),
    email     VARCHAR2(30),
    cep       VARCHAR2(9),
    cidade    VARCHAR2(30),
    estado    VARCHAR2(2),
    telefones TELEFONES_NT
) NOT FINAL;

-- MINISTRA (VAZIO)
CREATE OR REPLACE TYPE MINISTRA_TY;
CREATE OR REPLACE TYPE MINISTRA_NT AS TABLE OF REF MINISTRA_TY;

-- TURMA
CREATE OR REPLACE TYPE TURMA_TY AS OBJECT
(
    idt       INTEGER,
    periodo   VARCHAR2(20),
    nome      VARCHAR2(30),
    ano       INTEGER,
    sala      VARCHAR2(10),
    ministra  MINISTRA_NT
)

CREATE OR REPLACE TYPE TURMA_NT AS TABLE OF REF TURMA_TY;

CREATE TABLE turmas OF TURMA_TY (idt PRIMARY KEY)
    NESTED TABLE ministra STORE AS turma_ministra_st; 


-- ALUNO
CREATE OR REPLACE TYPE ALUNO_TY UNDER PESSOA_TY
(
    desistiu      NUMBER(1),
    justificativa VARCHAR2(200),
    tipo_escola   VARCHAR2(20),
    material      NUMBER(1),
    cursou        NUMBER(1),
    observacao    VARCHAR2(200),
    turmas        TURMA_NT
);

CREATE TABLE alunos OF ALUNO_TY (cpf PRIMARY KEY)
    NESTED TABLE turmas STORE AS aluno_turmas_st
    NESTED TABLE telefones STORE AS aluno_telefones_st;


-- CONTA
CREATE OR REPLACE TYPE CONTA_TY AS OBJECT
(
    banco     VARCHAR2(30),
    agencia   INTEGER,
    conta     INTEGER
)

CREATE OR REPLACE TYPE CONTA_NT AS TABLE OF CONTA_TY;


-- CARGO
CREATE OR REPLACE TYPE CARGO_TY AS OBJECT
(
    idc   INTEGER,
    nome  VARCHAR2(30)
)

CREATE OR REPLACE TYPE CARGO_NT AS TABLE OF REF CARGO_TY;

CREATE TABLE cargos OF CARGO_TY (idc PRIMARY KEY);


-- DISCIPLINA
CREATE OR REPLACE TYPE DISCIPLINA_TY AS OBJECT
(
    idd   INTEGER,
    nome  VARCHAR2(30)
)

CREATE OR REPLACE TYPE DISCIPLINA_NT AS TABLE OF REF DISCIPLINA_TY;

CREATE TABLE disciplinas OF DISCIPLINA_TY (idd PRIMARY KEY);


-- MINISTRA
CREATE OR REPLACE TYPE MINISTRA_TY AS OBJECT
(
    turmas        TURMA_NT,
    disciplinas   DISCIPLINA_NT
)

CREATE TABLE ministra OF MINISTRA_TY
    NESTED TABLE turmas STORE AS ministra_turmas_st
    NESTED TABLE disciplinas STORE AS ministra_disciplinas_st;


-- FUNCIONÁRIO
CREATE OR REPLACE TYPE FUNCIONARIO_TY UNDER PESSOA_TY
(
    contas    CONTA_NT,
    cargos    CARGO_NT,
    ministra  MINISTRA_NT
);

CREATE TABLE funcionarios OF FUNCIONARIO_TY (cpf PRIMARY KEY)
    NESTED TABLE contas STORE AS funcionario_contas_st
    NESTED TABLE cargos STORE AS funcionario_cargos_St
    NESTED TABLE ministra STORE AS funcionario_ministra_st
    NESTED TABLE telefones STORE AS funcionario_telefones_st;

[Databases 2 Class] Solution of Object-Relational modeling and manipulating exercises