aula15-sql

aula15-db1.sql

drop table "cliente"     cascade;
drop table "animal"      cascade;
drop table "tipo"        cascade;
drop table "treinamento" cascade;
drop table "relatorio"   cascade;

create table "cliente" ( "codigo" integer not null
                       , "nome" varchar(16) not null
                       , "RG" integer not null
                       , "sexo" varchar(1) not null
                       );

create table "animal" ( "codigo" integer
                      , "nome" varchar(16) not null
                      , "raca" varchar(16) not null
                      , "dtaNasc" date not null
                      , "sexo" varchar(1) not null
                      , "codDon" integer not null
                      );

create table "tipo" ( "codigo" integer
                    , "nome" varchar(16) not null
                    );

create table "treinamento" ( "codAni" integer not null
                           , "codTip" integer not null
                           , "dtaInicio" date not null
                           , "dtaFim" date not null
                           , "aproveitamento" varchar(16) not null
                           , "valorPago" decimal not null
                           );

create table "relatorio" ( "codAni" integer not null
                         , "codTip" integer not null
                         , "dtaInicio" date not null
                         , "observacoes" varchar(36) not null
                         );

-- btree structure

create index "idx_don" on "animal" ("codDon");
create index "idx_ani" on "treinamento" ("codAni");
create index "idx_tip" on "treinamento" ("codTip");
create index "idx_tripla1" on "relatorio" ("codAni", "codTip", "dtaInicio");

/* primary keys */

alter table "cliente" add primary key ("codigo");
alter table "animal" add primary key ("codigo");
alter table "tipo" add primary key ("codigo");

alter table "relatorio" add primary key ( "codAni"
                                        , "codTip"
                                        , "dtaInicio"
                                        );

alter table "treinamento" add primary key ( "codAni"
                                          , "codTip"
                                          , "dtaInicio"
                                          );

/* foreign keys */

alter table "relatorio" add constraint "fk_tripla1" foreign key
  ( "codAni"
  , "codTip"
  , "dtaInicio" ) references "treinamento"
  ( "codAni"
  , "codTip"
  , "dtaInicio"
  );

alter table "animal" add constraint "fk_don" foreign key ("codDon") references "cliente" ("codigo");
alter table "treinamento" add constraint "fk_ani" foreign key ("codAni") references "animal" ("codigo");
alter table "treinamento" add constraint "fk_tip" foreign key ("codTip") references "tipo" ("codigo");

-- add uniqueness

alter table "cliente" add constraint "unique_rg" UNIQUE ("RG");

-- add default value

alter table "treinamento" alter "aproveitamento" set default 'RUIM';

-- insert into relations

insert into "cliente" values (1, 'Lucas',   659909700, 'M');
insert into "cliente" values (2, 'Amanda',  659909701, 'F');
insert into "cliente" values (3, 'Paula',   659909702, 'F');
insert into "cliente" values (4, 'Mariana', 659909703, 'F');
insert into "cliente" values (5, 'Marcela', 659909704, 'F');

insert into "animal" values (1, 'Tango', 'Boder Collie', '22-11-2010', 'M', 3);

insert into "tipo" values (1, 'Nome');

insert into "treinamento" values (1, 1, '2011-03-15', '22-3-2011', 'OTIMO', 2000);

insert into "relatorio" values (1, 1, '15-3-2011', 'Sintomas de alguma doenca canina');