text: drop table AGENDA cascade constraints;
  drop table LOCAL cascade constraints;
  drop table ATRACAO cascade constraints;
  drop table ACONTECE cascade constraints;
  drop table COMPETICAO cascade constraints;
  drop table CONCURSOCOSPLAY cascade constraints;
  drop table PALESTRA cascade constraints;
  drop table SHOW cascade constraints;
  drop table ARTSTREET cascade constraints;
  drop table EXTERNA cascade constraints;
  drop table PESSOA cascade constraints;
  drop table IMPRENSA cascade constraints;
  drop table FUNCIONARIOS cascade constraints;
  drop table COLABORADORES cascade constraints;
  drop table EQUIPE cascade constraints;
  drop table COMPETIDOR cascade constraints;
  drop table INFLUENCIADOR cascade constraints;
  drop table MUSICO cascade constraints;
  drop table ARTISTA cascade constraints;
  drop table PARTIDA cascade constraints;
  drop table COBRE cascade constraints;
  drop table COMUNICA cascade constraints;
  drop table ADMINSTRA cascade constraints;
  drop table OPERA cascade constraints;
  drop table CUIDA cascade constraints;
  drop table ZELA cascade constraints;
  drop table FISCALIZA cascade constraints;
  drop table NARRA cascade constraints;
  drop table JULGACOSPLAY cascade constraints;
  drop table APRESENTAPALESTRA cascade constraints;
  drop table EXIBE cascade constraints;
  -- Script Criação da Base de Dados:
  -- COMECANDO A CRIACAO DO BANCO DE DADOS PELO CERNE DO E-SCHEDULIG{
  CREATE TABLE AGENDA(
      DATA VARCHAR(10) NOT NULL,
      HORAS VARCHAR(5) NOT NULL,
      CONSTRAINT PK_AGENDA PRIMARY KEY (DATA, HORAS),
      CONSTRAINT CK1_AGENDA_DATA CHECK ( DATA LIKE ('__/__/____') ),
      CONSTRAINT CK2_AGENDA_HORAS CHECK (HORAS LIKE ('__:__'))
  );
  
  CREATE TABLE LOCAL(
      ESPACO VARCHAR(50) NOT NULL,
      NOME VARCHAR(50) NOT NULL,
      CONSTRAINT PK_LOCAL PRIMARY KEY (ESPACO, NOME),
      CONSTRAINT CK1_LOCAL_ESPACO CHECK (UPPER(ESPACO) IN  ('GAMEPLAY ARENA 1', 'GAMEPLAY ARENA 2', 'INOVA ARENA', 'OI GAME ARENA', 'PALCO GENESIS', 'AREA EXTERNA')),
      CONSTRAINT CK2_LOCAL_NOME CHECK (UPPER(NOME) LIKE ('STAND%')) 
  );
  
  CREATE TABLE ATRACAO(
      ID INTEGER NOT NULL,
      TIPO varchar(30) NOT NULL,
      CONSTRAINT PK_ATRACAO PRIMARY KEY (ID),
      CONSTRAINT CK_ATRACAO_TIPO CHECK (UPPER(TIPO) IN ('COMPETICAO','PARTIDA','CONCURSOCOSPLAY', 'PALESTRA', 'SHOW', 'ARTSTREET','EXTERNA') ) 
  );
  
  CREATE TABLE ACONTECE(
      ID INTEGER PRIMARY KEY ,
      DATA VARCHAR(10) NOT NULL,
      HORAS VARCHAR(5) NOT NULL,
      ESPACO VARCHAR(50) NOT NULL,
      LOCAL VARCHAR(50) NOT NULL,
      CONSTRAINT UK_ACONTC_DIA_HORA_SPACE_LOCAL UNIQUE (DATA, HORAS, ESPACO, LOCAL),
      CONSTRAINT FK1_ACONTECE_DATA_HORAS FOREIGN KEY (DATA, HORAS) REFERENCES AGENDA (DATA, HORAS) ON DELETE CASCADE,
      CONSTRAINT FK2_ACONTECE_ESPACO_LOCAL FOREIGN KEY (ESPACO, LOCAL) REFERENCES LOCAL (ESPACO, NOME) ON DELETE CASCADE,
      CONSTRAINT FK3_ACONTECE_ID FOREIGN KEY (ID) REFERENCES ATRACAO (ID) ON DELETE CASCADE
  );
  --}
  
  --PARTIMOS PARA A CRIACAO DAS ESTIDADES ESPECIALIZADAS DE ATRACAO {
  -- CRIAMOS A TABELA ATRACAO, POIS ESCOLHEMOS A ALTERNATIVA 3 PADRAO 8 PARA REPRESERNTARMOS A ESPECIALIZACAO NO NOSSO BD
  
  
  CREATE TABLE COMPETICAO(
      NOMEJOGO varchar(100) NOT NULL,
      CATEGORIA varchar(50) NOT NULL,
      PREMIACAO INTEGER,
      CONSTRAINT PK_COMPETICAO PRIMARY KEY (NOMEJOGO, CATEGORIA)
  );
  
  CREATE TABLE CONCURSOCOSPLAY(
      ID INTEGER PRIMARY KEY,
      ETAPA VARCHAR(16),
      CONSTRAINT FK_CONCURSOCOSPLAY FOREIGN KEY (ID) REFERENCES ATRACAO (ID) ON DELETE CASCADE
  );
  
  CREATE TABLE PALESTRA(
      ID INTEGER NOT NULL,
      TEMA VARCHAR(50),
      CONSTRAINT PK_PALESTRA PRIMARY KEY (ID),
      CONSTRAINT FK_PALESTRA_ID FOREIGN KEY (ID) REFERENCES ATRACAO (ID) ON DELETE CASCADE
  );
  
  CREATE TABLE SHOW(
      ID INTEGER UNIQUE,
      NOMEDABANDA VARCHAR(50) PRIMARY KEY ,
      CONSTRAINT FK_SHOW_ID FOREIGN KEY (ID) REFERENCES ATRACAO (ID) ON DELETE CASCADE
  );
  
  CREATE TABLE ARTSTREET(
      ID INTEGER PRIMARY KEY,
      NOME VARCHAR(50), 
      CONSTRAINT FK_ARTSTREET_ID FOREIGN KEY (ID) REFERENCES ATRACAO (ID) ON DELETE CASCADE
  );
      
  CREATE TABLE EXTERNA(
      ID INTEGER PRIMARY KEY,
      NOME VARCHAR(50),
      CONSTRAINT FK_EXTERNA_ID FOREIGN KEY (ID) REFERENCES ATRACAO(ID) ON DELETE CASCADE
  );
  --}
  
  -- VAMOS ADIANTE COM A CRIACAO DAS ESTIDADES ESPECIALIZADAS DE DE PESSOA {
  -- CRIAMOS A TABELA PESSOA , POIS ESCOLHEMOS A ALTERNATIVA 3 PADRAO 8 PARA REPRESERNTARMOS A ESPECIALIZACAO NO NOSSO BD
  CREATE TABLE PESSOA(
      CREDENCIAL INTEGER primary key, 
      SETOR varchar(11) NOT NULL,
      CONSTRAINT CK_SETOR CHECK (UPPER(SETOR) IN ('FUNCIONARIO', 'IMPRENSA', 'COLABORADOR') ) 
  );
  
  CREATE TABLE IMPRENSA(
      CREDENCIAL INTEGER PRIMARY KEY,
      CPF VARCHAR(14) UNIQUE,
      SEXO VARCHAR(20), 
      NOME VARCHAR(50),
      ENDERECO VARCHAR(100),
      CIDADE VARCHAR(50),
      ESTADO VARCHAR(50),
      PAIS VARCHAR(50),
      IDADE INTEGER,
      DESIGNADO VARCHAR(50),
      GRUPO VARCHAR(50),
      FUNCAO VARCHAR(50) NOT NULL,
      CONSTRAINT FK_IMPRENSA_CREDENCIAL FOREIGN KEY (CREDENCIAL) REFERENCES PESSOA (CREDENCIAL) ON DELETE CASCADE
  );
  
  CREATE TABLE FUNCIONARIOS(
      CREDENCIAL INTEGER PRIMARY KEY,
      CPF VARCHAR(14) UNIQUE,
      SEXO VARCHAR(20), 
      NOME VARCHAR(50),
      ENDERECO VARCHAR(100),
      CIDADE VARCHAR(50),
      ESTADO VARCHAR(50),
      PAIS VARCHAR(50),
      IDADE INTEGER,
      DESIGNADO VARCHAR(50),
      FUNCAO VARCHAR(50) NOT NULL,
      REAIS_HORA INTEGER NOT NULL,
      CONSTRAINT FK_FUNCIONARIOS_CREDENCIAL FOREIGN KEY (CREDENCIAL) REFERENCES PESSOA (CREDENCIAL) ON DELETE CASCADE,
      CONSTRAINT CK_FUNCAO CHECK (UPPER(FUNCAO) IN ('ASSESSOR', 'STAFF', 'TECNICO', 'SEGURANCA', 'PARAMEDICO', 'JURADO', 'NARRADOR') ) 
  );
  
  CREATE TABLE COLABORADORES(
      CREDENCIAL INTEGER PRIMARY KEY,
      CPF VARCHAR(14) UNIQUE,
      SEXO VARCHAR(20), 
      NOME VARCHAR(50),
      ENDERECO VARCHAR(100),
      CIDADE VARCHAR(50),
      ESTADO VARCHAR(50),
      PAIS VARCHAR(50),
      IDADE INTEGER,
      DESIGNADO VARCHAR(50),
      CACHEE INTEGER NOT NULL,
      TIPO VARCHAR(50) NOT NULL,
      CONSTRAINT FK_COLABORADORES_CREDENCIAL FOREIGN KEY (CREDENCIAL) REFERENCES PESSOA (CREDENCIAL) ON DELETE CASCADE,
      CONSTRAINT CK_TIPO CHECK (UPPER(TIPO) IN ('ARTISTA', 'MUSICO', 'INFLUENCIADOR', 'COMPETIDOR') ) 
  );
  
  CREATE TABLE EQUIPE(
      NOMEEQUIPE VARCHAR(100) PRIMARY KEY,
      QUANTIDADE INTEGER  CHECK(QUANTIDADE >1)
  );
  
  CREATE TABLE COMPETIDOR(
      COLABORADOR INTEGER PRIMARY KEY,
      APELIDO VARCHAR(50),
      NOMEEQUIPE VARCHAR(50),
      CONSTRAINT FK1_COMPETIDOR_COLABORADOR FOREIGN KEY (COLABORADOR) REFERENCES COLABORADORES (CREDENCIAL) ON DELETE CASCADE,
      CONSTRAINT FK2_COMPETIDOR_NOMEEQUIPE FOREIGN KEY (NOMEEQUIPE) REFERENCES  EQUIPE (NOMEEQUIPE) --ON DELETE CASCADE 
  );
  
  CREATE TABLE INFLUENCIADOR(
      COLABORADOR INTEGER PRIMARY KEY,
      APELIDO VARCHAR(50),
      CONSTRAINT FK_INFLUENCIADOR_COLABORADOR FOREIGN KEY (COLABORADOR) REFERENCES COLABORADORES (CREDENCIAL) ON DELETE CASCADE
  );
      
  CREATE TABLE MUSICO(
      COLABORADOR INTEGER PRIMARY KEY,
      FUNCAO VARCHAR(50),
      NOMEBANDA VARCHAR(50) DEFAULT NULL,
      CONSTRAINT FK1_MUSICO_COLABORADOR FOREIGN KEY (COLABORADOR) REFERENCES COLABORADORES (CREDENCIAL) ON DELETE CASCADE,
      CONSTRAINT FK2_MUSICO_NOMEBANDA FOREIGN KEY (NOMEBANDA) REFERENCES SHOW (NOMEDABANDA) --ON DELETE NO ACTION
  );
      
  CREATE TABLE ARTISTA(
      COLABORADOR INTEGER PRIMARY KEY,
      ESTILO VARCHAR(50),
      CONSTRAINT FK_ARTISTA_COLABORADOR FOREIGN KEY (COLABORADOR) REFERENCES COLABORADORES (CREDENCIAL) ON DELETE CASCADE
  );
  --}
  
  CREATE TABLE PARTIDA(
      ID INTEGER PRIMARY KEY,
      CATEGORIA VARCHAR(50) NOT NULL,
      NOMEJOGO VARCHAR(100) NOT NULL,
      ETAPA VARCHAR(16) NOT NULL,
      EQUIPE1 VARCHAR(100) NOT NULL,
      EQUIPE2 VARCHAR(100) NOT NULL,
      CONSTRAINT UK_PARTIDA UNIQUE (CATEGORIA, NOMEJOGO, ETAPA),
      CONSTRAINT FK1_PARTIDA_ID FOREIGN KEY (ID) REFERENCES ATRACAO (ID) ON DELETE CASCADE,
      CONSTRAINT FK2_PARTIDA_CATEGORIA_NOMEJOGO FOREIGN KEY (CATEGORIA, NOMEJOGO) REFERENCES COMPETICAO (CATEGORIA, NOMEJOGO) ON DELETE CASCADE,
      CONSTRAINT FK3_PARTIDA_EQUIPE1 FOREIGN KEY (EQUIPE1) REFERENCES EQUIPE (NOMEEQUIPE) ON DELETE CASCADE,
      CONSTRAINT FK4_PARTIDA_EQUIPE2 FOREIGN KEY (EQUIPE2) REFERENCES EQUIPE (NOMEEQUIPE) ON DELETE CASCADE
  );
  
  -- POR FIM CRIAMOS AS TABELAS NECESSARIAS DOS RELACIONAMENTOS ENTRE AS ENTIDADES ESPECIALIZADAS {
  CREATE TABLE COBRE(
      ID INTEGER NOT NULL,
      IMPRENSA INTEGER NOT NULL,
      CONSTRAINT PK_COBRE PRIMARY KEY (ID, IMPRENSA),
      CONSTRAINT FK1_COBRE_IMPRENSA FOREIGN KEY (IMPRENSA) REFERENCES IMPRENSA (CREDENCIAL) ON DELETE CASCADE,
      CONSTRAINT FK2_COBRE_ID FOREIGN KEY (ID) REFERENCES ATRACAO (ID)  ON DELETE CASCADE
  );
  
  CREATE TABLE COMUNICA(
      IMPRENSA INTEGER NOT NULL,
      ASSESSOR INTEGER NOT NULL,
      CONSTRAINT PK_COMUNICA PRIMARY KEY (IMPRENSA, ASSESSOR),
      CONSTRAINT FK1_COMUNICA_IMPRENSA FOREIGN KEY (IMPRENSA) REFERENCES IMPRENSA (CREDENCIAL) ON DELETE CASCADE,
      CONSTRAINT FK2_COMUNICA_ASSESSOR FOREIGN KEY (ASSESSOR) REFERENCES FUNCIONARIOS (CREDENCIAL)  ON DELETE CASCADE
  );
  
  CREATE TABLE ADMINSTRA(
      ID INTEGER NOT NULL,
      STAFF INTEGER NOT NULL,
      FUNCAO VARCHAR(50) NOT NULL,
      CONSTRAINT PK_ADMINSTRA PRIMARY KEY (ID, STAFF),
      CONSTRAINT FK1_ADMINSTRA_ID FOREIGN KEY (ID) REFERENCES ATRACAO (ID) ON DELETE CASCADE,
      CONSTRAINT FK2_ADMINSTRA_STAFF FOREIGN KEY (STAFF) REFERENCES FUNCIONARIOS (CREDENCIAL)  ON DELETE CASCADE
  );
  
  CREATE TABLE OPERA(
      ID INTEGER NOT NULL,
      TECNICO INTEGER NOT NULL,
      ESPECIALIDADE VARCHAR(100) NOT NULL,
      CONSTRAINT PK_OPERA PRIMARY KEY (ID, TECNICO), 
      CONSTRAINT FK1_OPERA_ID FOREIGN KEY (ID) REFERENCES ATRACAO (ID) ON DELETE CASCADE,
      CONSTRAINT FK2_OPERA_TECNICO FOREIGN KEY (TECNICO) REFERENCES FUNCIONARIOS (CREDENCIAL)  ON DELETE CASCADE 
  );
      
  CREATE TABLE CUIDA(
      ID INTEGER NOT NULL,
      SEGURANCA INTEGER NOT NULL,
      CONSTRAINT PK_CUIDA PRIMARY KEY (ID, SEGURANCA),
      CONSTRAINT FK1_CUIDA_SEGURANCA FOREIGN KEY (SEGURANCA) REFERENCES FUNCIONARIOS (CREDENCIAL) ON DELETE CASCADE ,
      CONSTRAINT FK2_CUIDA_ID FOREIGN KEY (ID) REFERENCES ATRACAO (ID)   ON DELETE CASCADE 
  );
  
  CREATE TABLE ZELA(
      ID INTEGER NOT NULL,
      PARAMEDICO INTEGER NOT NULL,
      CONSTRAINT PK_ZELA PRIMARY KEY (ID, PARAMEDICO),
      CONSTRAINT FK1_ZELA_PARAMEDICO FOREIGN KEY (PARAMEDICO) REFERENCES FUNCIONARIOS (CREDENCIAL) ON DELETE CASCADE,
      CONSTRAINT FK2_ZELA_ID FOREIGN KEY (ID) REFERENCES ATRACAO (ID)  ON DELETE CASCADE
  );
      
  CREATE TABLE FISCALIZA(
      ID INTEGER NOT NULL,
      JURADO INTEGER NOT NULL,
      CONSTRAINT PK_FISCALIZA PRIMARY KEY (ID, JURADO),
      CONSTRAINT FK1_FISCALIZA_JURADO FOREIGN KEY (JURADO) REFERENCES FUNCIONARIOS (CREDENCIAL) ON DELETE CASCADE,
      CONSTRAINT FK2_FISCALIZA_ID FOREIGN KEY (ID) REFERENCES PARTIDA (ID)  ON DELETE CASCADE
  );
      
  CREATE TABLE NARRA(
      ID INTEGER NOT NULL,
      NARRADOR INTEGER NOT NULL,
      CONSTRAINT PK_NARRA PRIMARY KEY (ID, NARRADOR),
      CONSTRAINT FK1_NARRA_NARRADOR FOREIGN KEY (NARRADOR) REFERENCES FUNCIONARIOS (CREDENCIAL) ON DELETE CASCADE,
      CONSTRAINT FK2_NARRA_ID FOREIGN KEY (ID) REFERENCES PARTIDA (ID)  ON DELETE CASCADE
  );
  
  CREATE TABLE JULGACOSPLAY(
      ID INTEGER NOT NULL,
      JURADO INTEGER NOT NULL,
      CONSTRAINT PK_JULGACOSPLAY PRIMARY KEY (ID, JURADO),
      CONSTRAINT FK1_JULGACOSPLAY_JURADO FOREIGN KEY (JURADO) REFERENCES FUNCIONARIOS (CREDENCIAL) ON DELETE CASCADE,
      CONSTRAINT FK2_JULGACOSPLAY_ID FOREIGN KEY (ID) REFERENCES CONCURSOCOSPLAY (ID) ON DELETE CASCADE
  );
  
  CREATE TABLE APRESENTAPALESTRA(
      ID INTEGER NOT NULL,
      INFLUENCIADOR INTEGER NOT NULL,
      CONSTRAINT PK_APRESENTAPALESTRA PRIMARY KEY (ID, INFLUENCIADOR),
      CONSTRAINT FK1_APRESENTA_INFLUENCIADOR FOREIGN KEY (INFLUENCIADOR) REFERENCES INFLUENCIADOR (COLABORADOR) ON DELETE CASCADE,
      CONSTRAINT FK2_APRESENTAPALESTRA_ID FOREIGN KEY (ID) REFERENCES PALESTRA (ID) ON DELETE CASCADE
  );
      
  CREATE TABLE EXIBE(
      ID INTEGER NOT NULL,
      ARTISTA INTEGER NOT NULL,
      CATEGORIA VARCHAR(100),
      CONSTRAINT PK_EXIBE PRIMARY KEY (ID, ARTISTA),
      CONSTRAINT FK1_EXIBE_ARTISTA FOREIGN KEY (ARTISTA) REFERENCES ARTISTA (COLABORADOR) ON DELETE CASCADE,
      CONSTRAINT FK2_EXIBE_ID FOREIGN KEY (ID) REFERENCES ARTSTREET (ID) ON DELETE CASCADE
  );
  
  --}
  -- FIM DO SCRIPT DE CRIACAO DO BANCO DE DADOS E-SCHEDULING 