Baixe o app para aproveitar ainda mais
Prévia do material em texto
CURSO SUPERIOR EM BACHARELADO DE SISTEMAS DE INFORMAÇÃO Equipe: Roniele, Sidnelson Gubert e Victor Lima PROJETO DE BANCO DE DADOS: ATUV – Cadastro Emissão de Bilhete Eletrônico Vitória da Conquista-BA Outubro/2018 Equipe: Roniele, Sidnelson Gubert e Victor Lima PROJETO DE BANCO DE DADOS: ATUV – Cadastro Emissão de Bilhete Eletrônico Trabalho apresentado ao Curso Superior em Sistemas de Informação do Instituto Federal de Educação, Ciência e Tecnologia da Bahia campus Vitória da Conquista como requisito parcial para a aprovação na disciplina Banco de Dados I. Professor Orientador: Me. Pablo Freire Matos Vitória da Conquista-BA Outubro/2018 HISTÓRICO DE PARTICIPAÇÃO Período Participante Etapa Função 01/10 a 12/10 Sidnelson Análise de Requisitos Escrever na introdução sobre a empresa/setor Victor Análise de Requisitos Fazer a descrição do minimundo Sidnelson/Victor Modelagem Conceitual Criar o esquema conceitual Ronielle Modelagem Conceitual Criar 10 consultas RESUMO Neste projeto usamos os conceitos da disciplina Banco de Dados I para desenvolver um banco de dados que será utilizado pela ATUV, situada em Vitória da Conquista -BA. A ATUV- Associação das Empresas do Sistema de Transporte Coletivo Urbano de Vitória da Conquista-, representa as concessionárias do serviço de transporte coletivo de passageiros nas relações com o poder concedente, o Executivo Municipal, representado pela Secretaria de Mobilidade Urbana (SEMOB) e a sociedade em geral. Por ser a empresa responsável pela emissão, venda e controle dos cartões eletrônicos e créditos tarifados (passes) ao usuário do sistema coletivo, necessita de um sistema que ajude na gestão dos seus serviços. Para isso nos foi designado a tarefa de modelar um banco de dados onde possa ser possível manter um controle maior sobre esses cadastros, gerenciando os dados e relatórios sobre cadastros de alunos, cadastro de novos cartões e recargas de cartões. Para que assim se tenha um maior controle sobre quem está apto a usar o sistema de bilhete eletrônico. Nesse banco de dados serão armazenadas as informações sobre os alunos, tais como dados básicos. Será armazenado também os dados sobre as Instituições de Ensino, tais como, nome, endereço, etc. A principal função desse sistema será somente manter todos esses dados armazenados, e analisada as condições entre o aluno e a instituição de ensino, emitir o cartão para o aluno poder utilizar este serviço. Outra Funcionalidade do Banco é emitir relatórios que contenham todos os dados e registros que aconteceram perante todo este cadastro na ATUV neste período. Foi feito a descrição do minimundo, e a partir dele foi desenvolvido o esquema conceitual, representado através do Diagrama Entidade-Relacionamento (DER), que irá servir de base para o esquema lógico e por fim este irá dar base para a criação do esquema físico. Palavras-chave: Minimundo. Cadastro. Bilhete Eletronico. ATUV. BrModelo. LISTA DE FIGURAS Figura 1-Diagrama Entidade-Relacionamento 8 INTRODUÇÃO O banco de dados armazena e gerencia os bens mais valiosos de uma empresa. Isso acontece porque o mercado está cada vez mais competitivo e acelerado, exigindo das empresas respostas rápidas e assertivas, além de estratégias bem planejadas e executadas. Como dissemos acima, o banco de dados armazena informações e, nessa batalha de competitividade, informação é poder. Por meio dessas informações, todo o funcionamento da empresa é pautado. Pode-se dizer que em todo lugar há um banco de dados. No menor dos negócios, ao utilizar um software para organização financeira, por exemplo, está sendo usada uma espécie de banco de dados, porém, simplificada e automatizada, que armazena, processa e dispõe as informações sobre as finanças do negócio. Quando se possui tanto um banco de dados bem administrado como informações de qualidade, o resultado geralmente é o sucesso. Com isso, é possível que o gestor analise em qual direção a sua empresa está caminhando, a fim de corrigir a rota ou intensificar a atuação. Caracterização da Empresa A Empresa, ATUV, recebeu autorização Municipal no dia 07/11/2007 para operacionar o Sistema de Bilhetagem Eletrônica vigente em Vitória da Conquista. Ela tem a missão de atender ao usuário da melhor forma possível no que se refere ao Sistema de Bilhetagem Eletrônica, e ao sistema de transporte coletivo, buscando sempre uma excelência no atendimento ao usuário investindo em tecnologia e em qualificação profissional. A transparência, respeito e serenidade no atendimento ao usuário, fazem parte dos valores desta grande empresa. A empresa, localizada na cidade, tem seu horário de atendimento de segunda a sábado, sendo de segunda a sexta nos horários de 07h30min até 18h30min e aos sábados das 07h30min às 13h00min. DESCRIÇÃO DO MINIMUNDO A ATUV Trabalha com o BEM – Bilhete Eletronico Municipal. Dentre esse bilhete, o bilhete de passe escolar será emitido com o auxilio deste banco de dados. O benefício de poder usar esse sistema irá depender de vários fatores, como distancia entre aluno e instituições, data de matricula, dentre outros. A contratação de professores é feita pelo setor administrativo. A função do professor é instruir e avaliar seus alunos (um ou mais alunos podem ser avaliados por um ou mais professores), através da avaliação é feito um parecer e o treino de cada aluno sendo registrado pelo sistema, junto com a data da avaliação. Cada professor tem pelo menos uma função/especialidade. A remuneração corresponde a 30% do valor das mensalidades devidamente pagas pelos seus alunos. Para cadastro do profissional é criado um código de identificação único. É solicitado também, o CREF, CTPS, RG, CPF, nome, sexo, endereço (Rua, Bairro, número, Cidade, CEP), conta (Agência, Número, Nome do Banco) e telefone. O salário de cada professor é calculado no dia 5 de cada mês, com base nos valores pagos pelos alunos. Na Golds Academia o recepcionista é responsável por cadastrar, autorizar a entrada, agendar os horários de avaliação dos alunos e receber pagamentos. Para isso é feito login no sistema informando usuário e senha, que leva para o ambiente onde será feito todas essas atividades. Para o cadastro do recepcionista no sistema é necessário CTPS, RG, CPF, nome, endereço (Rua, Bairro, número, Cidade, CEP), conta (Agência, Número, Nome do Banco) sexo, login e senha. É preciso também a criação de um código único de identificação. Para o cadastro do aluno é criado um nº de matrícula único, nome, RG, CPF, número de telefone, endereço (Rua, Bairro, número, Cidade, CEP), e-mail, data do cadastro, sexo, o tipo do serviço/modalidade (Musculação, ginástica ou ambos) e o valor da mensalidade. No ato do cadastro deve ser pago a primeira mensalidade do mês corrente. A depender do pacote pode ser feito um desconto no valor e a data da matrícula é registrada pelo sistema. Devem ser emitidos avisos do dia de vencimento para os alunos, os quais são enviados via email e/ou sms. O pagamento da mensalidade pode ser feito via cartão de crédito/débito ou em dinheiro pago na própria recepção da academia (o recepcionista recebe o pagamento de nenhum ou vários alunos e os alunos pagam para um ou mais recepcionistas). A autorização ou não da entrada é feita mediante verificação da situação do aluno. O aluno é autorizado caso sua matrícula esteja ativa. O sistema registra o horário de entrada e saída (o recepcionista autoriza nenhum ou vários alunos e os alunos são autorizados por um ou mais recepcionistas). No caso de vencimento da matrícula é necessário renovação da matrícula. Para a renovação é somente verificado os dados do aluno, não é cobrado nenhuma taxa. O recepcionista também é encarregado de agendar o dia e hora das avaliaçõesdos alunos para os professores (nenhum ou vários recepcionistas agendam para um ou mais professores e nenhum ou vários alunos). No final do dia é gerado um relatório, que contém matrículas efetuadas no dia e matrículas expiradas no dia. E mensalmente, é solicitado um relatório completo com o histórico de cadastros e pagamentos realizados. Controle A academia visa controlar todo o cadastro de funcionários e alunos, como também a parte financeira mantendo o controle das mensalidades dos alunos da academia, emitindo recibos e relatórios administrativos do negócio. Controla também as fichas dos alunos preservando informações importantes como o treino e o parecer de cada um e dá permissão de acesso aos devidos funcionários. ESQUEMA CONCEITUAL O modelo conceitual é uma representação abstrata que demonstra todas as relações entre as entidades, suas especializações, seus atributos e auto-relações. A partir da descrição do minimundo foi possível a formulação do Diagrama Entidade-Relacionamento (DER) (Figura 1) através do programa brModelo (CÂNDIDO, 2005). Figura 1-Diagrama Entidade-Relacionamento z A partir do DER apresentado, pode-se ter uma ideia de algumas consultas possíveis ao banco de dados, de acordo com a necessidade da empresa, como a seguir: 1) Quais os alunos matriculados no dia “x”? 2) Quais alunos cadastrados são do sexo feminino? 3) Qual a data do fim da matrícula e o valor a ser pago pelo Aluno “x”? 4) Quais alunos entraram na academia do horário “x” a horário “y”? 5) Quais os Alunos (nome e telefone) fizeram avaliação com Professor “x”? 6) Qual a frequência de aluno “x” no período de “y” a “z”? 7) Quais alunos (Matricula, CPF e nome) de determinada modalidade “x”? 8) Qual o treino atual do aluno “x”? 9) Qual o valor total de pagamentos recebidos no dia “x”? 10) Quais os nomes dos professores e recepcionistas e suas respectivas contas? ESQUEMA LÓGICO O modelo lógico descreve a estrutura de um banco de dados. Este modelo é representado através de tabelas e suas relações. Neste esquema, as tabelas são as entidades, as colunas são os atributos e a linhas são os dados. Este modelo já leva em conta algumas limitações do banco e faz implementação de algumas de suas prioridades como: chave primária e estrangeira, integridade referencial entre outras. Mapeamento ER-Relacional Professor(Cod_prof, nome, sexo, CPF, conta_agencia, conta_num, conta_banco, CTPS, tel, CREF, RG, end_rua, end_bairro, end_cep, end_cidade, end_numero) Funcao(Id,especialidade) Funcao_Professor(Cod_prof, Id) representa a relação tem Cod_prof referencia Professor Id referencia funcao Aluno(Nmatricula, CPF, nome, sexo, RG, tel, email, data_cadastro, end_rua, end_bairro, end_cep, end_cidade, end_numero) Avalia(dataAvaliacao, Nmatricula, Cod_prof, treino, parecer) cod_prof referencia Professor Nmatricula referencia Aluno Recepcionista(Cod_recep, nome, login, senha, sexo, CPF, conta_agencia, conta_num, conta_banco, CTPS, tel, RG, end_rua, end_bairro, end_cep, end_cidade, end_numero) Autoriza(dataHora_entrada, Nmatricula, Cod_recep, dataHora_saida) cod_recep referencia Recepcionista Nmatricula referencia Aluno Agendamento(datahora, Cod_prof, Cod_recep, Nmatricula) Nmatricula referencia Aluno Cod_prof referencia Professor Cod_recep referencia Recepcionista Servico(Id, modalidade, valor) Matricula(dataInicio, Id, Nmatricula, dataFim, valor) Nmatricula referencia Aluno Id referencia Servico Pagamento(data_hora, Cod_recep, data_inicio, Nmatricula, Id, forma, valor) Cod_recep referencia recepcionista data_inicio referencia Matricula Nmatricula referencia Matricula Id referencia Matricula Modelo Lógico Na figura 2 está representada a conversão do Modelo Conceitual (Figura 2) para o Modelo Lógico através do programa BrModelo (CÂNDIDO, 2005). Figura 2-Esquema Lógico Álgebra Relacional A Álgebra Relacional é uma linguagem de consulta procedural e formal a qual a técnica utilizada é fundamental para a extração de dados de um banco de dados, além de ser um conjunto de operações, os quais utilizam como recurso de entrada uma ou mais relações, produzindo então, uma nova relação. Consultas listadas no Capitulo 3 utilizando a álgebra relacional: 1) Quais os alunos matriculados no dia “x”? Aluno|x| (σ dataInicio = “DataX” (Matricula)) 2) Quais alunos cadastrados são do sexo feminino? σ sexo = “F” (Aluno) 3) Qual a data do fim da matrícula e o valor a ser pago pelo Aluno “x”? πdataFim,valor(Matricula|x|(σ nome= “x” (Aluno))) 4) Quais alunos entraram na academia do horário “x” a horário “y”? Aluno |x| (σ dataHora_entrada >= “x” ^dataHora_entrada <= “y” (Autoriza)) 5) Quais os Alunos (nome e telefone) fizeram avaliação com Professor “x”? temp σ nome = “x”(Professor) π Aluno.nome, Aluno.telefone (Aluno |x| Avalia |x| temp) 6) Qual a frequência de aluno “x” no período de “y” a “z”? σ dataHora_entrada>=“y”^dataHora_entrada<=“z”(Autoriza|x|(σ nome= “x” (Aluno))) 7) Quais alunos (Matricula, CPF e nome) de determinada modalidade “x”? temp πId(σ modalidade = “x” (Servicos)) temp2 Matricula |x| temp π Aluno.matricula, Aluno.CPF, Aluno.CPF (Aluno |x| temp2) 8) Qual o treino atual do aluno “x”? temp πdataAvaliacao,treino (avalia |x| (σ nome=“x” (Aluno)) temp3 temp X (ρ temp2 (temp)) temp4πdataAvaliacao,treino(σ temp.dataAvaliacao< temp2.dataAvaliacao(temp3)) πtreino(temp-temp4) 9) Qual o valor total de pagamentos recebidos no dia “x”? πvalor(σ data = “dataX” (pagamento)) 10) Quais os nomes dos professores e recepcionistas e suas respectivas contas? temp πnome,conta_agencia,conta_banco,conta_num(Professor) temp2 πnome,conta_agencia,conta_banco,conta_num (Recepcionista) temp U temp2 ESQUEMA FÍSICO Um esquema de banco de dados físico define como os dados são armazenados fisicamente em um sistema de armazenamento. Esse modelo depende do SGBD que está sendo usado. A partir daqui, são detalhados os componentes da estrutura física do banco, como tabelas, campos, tipos de valores, índices, dentre outros. Para o desenvolvimento deste projeto, utilizou-se o MySQL SGBD, que utiliza a linguagem SQL (Structured Query Language). Esquema físico em linguagem SQL A partir do esquema lógico apresentado na Figura 2 foi gerado o esquema físico da Academia Golds, desenvolvendo assim o banco de dados do mesmo e das tabelas que compõe. Para o desenvolvimento do esquema físico foi utilizado o programa brModelo (CÂNDIDO, 2005). No APÊNDICE A – Script SQL de Criação é possível ver a implementação do esquema físico. Povoamento das tabelas do banco de dados Academia Golds A partir da criação das tabelas do banco é possível a inserção dos dados que serão guardados para possíveis consultas posteriores. Os dados inseridos são apenas exemplos de como é possível povoar as tabelas. Os códigos de inserção podem ser vistos no APÊNDICE B – Povoamento das tabelas do banco de dados Academia Golds. Consultas no Banco de Dados Academia Golds Após a criação do banco de dados e povoamento das respectivas tabelas, é possível a visualização das consultas que serão utilizadas no banco, a saber: Quais os alunos matriculados no dia “x”? SELECT A.* FROM Matricula JOIN Aluno AS A ON FK_Aluno_Nmatricula=Nmatricula WHERE dataInicio='2016/04/10'; Quais alunos cadastrados são do sexo feminino? SELECT * FROM Aluno WHERE sexo='f'; Qual a data do fim da matrícula e o valor a ser pago pelo Aluno “x”? SELECT A.dataFim, A.valor FROM Matricula AS A JOIN Aluno AS B ON FK_Aluno_Nmatricula=Nmatricula WHERE B.nome LIKE 'gustavo%'; Quais alunos na academia do horário “x” a horário “y”? SELECT A.* FROM Aluno AS A JOIN Autoriza ON FK_Aluno_Nmatricula=Nmatricula WHERE (dataHora_entrada >= '2017/02/01 9:00:00') AND dataHora_entrada <= '2017/02/01 12:00:00'; Quais os Alunos (nome e telefone) fizeram avaliação com Professor “x”? SELECT A.nome, A.tel FROM Aluno AS A JOIN Avalia AS B ON FK_Aluno_Nmatricula=Nmatricula JOIN Professor AS C ON FK_Professor_Cod=CodWHERE C.nome LIKE 'Augusto%'; Qual a frequência de aluno “x” no período de “y” a “z”? SELECT A.nome, COUNT(*) AS 'Frequencia' FROM Aluno AS A JOIN Autoriza AS B ON FK_Aluno_Nmatricula=Nmatricula WHERE A.nome LIKE 'gustavo%' AND dataHora_entrada>='2016/07/01' AND dataHora_entrada<='2017/02/31'; Quais alunos (Matricula, CPF e nome) de determinada modalidade “x”? SELECT C.Nmatricula, C.nome, C.CPF FROM Servico AS A JOIN matricula AS B ON id = FK_Servicos_Id JOIN aluno AS C ON FK_Aluno_Nmatricula = Nmatricula WHERE A.modalidade='Ginastica'; Qual o treino atual do aluno “x”? SELECT B.treino FROM aluno AS A JOIN avalia AS B ON FK_Aluno_Nmatricula = Nmatricula WHERE B.dataAvaliacao=(SELECT MAX(dataAvaliacao) FROM avalia AS B JOIN Aluno AS A ON FK_Aluno_Nmatricula = Nmatricula WHERE A.nome LIKE 'Ana%'); Qual o valor total de pagamentos recebidos no dia “x”? SELECT SUM(valor) FROM pagamento WHERE data_hora BETWEEN '2018/01/17 00:00:00' AND '2018/01/18 00:00:00'; Quais os nomes dos professores e recepcionistas e suas respectivas contas? SELECT nome, agencia, banco, num FROM Professor UNION SELECT nome, agencia, banco, num FROM Recepcionista; CONCLUSÃO Com o desenvolvimento deste projeto foi possível perceber a importância de sistemas de gerenciamento de dados e sua aplicação prática. Esse projeto vai proporcionar à Golds Academia uma base de dados que servirá para a construção de uma aplicação que atenda suas necessidades, tanto na gestão de dados, como na geração de relatórios que servirão de base para se ter uma maior noção do funcionamento da academia. Através deste projeto foi possível vivenciar as necessidades de gerenciamento de dados por parte das empresas, a dinâmica que é necessária para que tudo funcione de acordo com o planejado. Através dos conhecimentos obtidos no semestre, e as correções feitas em cada etapa, esperamos que a base de dados atenda às necessidades que nos foram passadas, que possa servir para melhorar o funcionamento e a gestão de dados da empresa. Por fim, este projeto representou um desafio para nós, pois não tínhamos total conhecimento do que nos era pedido, mas através das aulas e monitorias foi possível entregar algo pelo menos próximo do que nos foi incumbido. Com tudo que aprendemos, agora é possível ter uma visão mais ampla sobre gerenciamento de dados, e como isso pode ajudar empresas a obter resultados mais satisfatórios. REFERÊNCIAS CÂNDIDO, C. H. Aprendizagem em Banco de Dados: Implementação de Ferramenta de Modelagem E.R. Trabalho de Conclusão de Curso (Pós-Graduação em Banco de Dados) – Universidade de Várzea Grande, Várzea Grande, MT, 2005. HELSER, Carlos Alberto. Projeto de Banco de Dados. 4ª ed. Porto Alegre: Sagra Luzzato, 2001. O que é um esquema de banco de dados?. 2018. Disponível em: <https://www.lucidchart.com/pages/pt/o-que-%C3%A9-um-esquema-de-banco-de-dados>. Acesso em: 12 jul. 2018. WIKI. Modelagem de Dados. 2018. Disponível em: <https://pt.wikipedia.org/wiki/Modelagem_de_dados>. Acesso em: 05 jun. 2018. Álgebra Relacional. 2018. Disponível em: <https://www.devmedia.com.br/algebra-relacional/9229>. Acesso em: 13 jul. 2018. APÊNDICE A – Script SQL de Criação CREATE DATABASE Academia; USE Academia; CREATE TABLE Professor ( Cod SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, CTPS VARCHAR(8) NOT NULL, RG VARCHAR(10) NOT NULL, rua VARCHAR(50) NOT NULL, bairro VARCHAR(50) NOT NULL, cep VARCHAR(8) NOT NULL, cidade VARCHAR(50) NOT NULL, numero SMALLINT UNSIGNED, sexo ENUM ('M','F') NOT NULL, CPF VARCHAR(11) NOT NULL, tel VARCHAR(13) NOT NULL, CREF VARCHAR(11) NOT NULL, agencia VARCHAR(4) NOT NULL, num VARCHAR(8) NOT NULL, Banco VARCHAR(15) NOT NULL, nome VARCHAR(50) NOT NULL, UNIQUE (CTPS, CPF) ); CREATE TABLE Aluno ( Nmatricula SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, RG VARCHAR(10) NOT NULL, tel VARCHAR(13) NOT NULL, email VARCHAR(50) NOT NULL, nome VARCHAR(50) NOT NULL, data_cadastro DATE, bairro VARCHAR(50) NOT NULL, numero SMALLINT UNSIGNED, cidade VARCHAR(50) NOT NULL, rua VARCHAR(50) NOT NULL, cep VARCHAR(8) NOT NULL, sexo ENUM ('M','F') NOT NULL, CPF VARCHAR(11) NOT NULL, UNIQUE (CPF) ); CREATE TABLE Recepcionista ( Cod SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sexo ENUM ('M','F') NOT NULL, CPF VARCHAR(11) NOT NULL, RG VARCHAR(10) NOT NULL, CTPS VARCHAR(8) NOT NULL, login VARCHAR(25) NOT NULL, senha VARCHAR(25) NOT NULL, cep VARCHAR(8) NOT NULL, cidade VARCHAR(50) NOT NULL, rua VARCHAR(50) NOT NULL, bairro VARCHAR(50) NOT NULL, numero SMALLINT UNSIGNED, nome VARCHAR(50) NOT NULL, num VARCHAR(8) NOT NULL, Banco VARCHAR(15) NOT NULL, agencia VARCHAR(4) NOT NULL, UNIQUE (CPF, CTPS, login) ); CREATE TABLE Servico ( Id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY UNIQUE, modalidade VARCHAR(10) NOT NULL, valor DECIMAL(5,2) UNSIGNED NOT NULL ); CREATE TABLE Funcao ( Id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY UNIQUE, especialidade VARCHAR(50) NOT NULL ); CREATE TABLE Matricula ( dataInicio DATE, datafim DATE NOT NULL, valor DECIMAL(5,2) UNSIGNED NOT NULL, FK_Aluno_Nmatricula SMALLINT UNSIGNED, FK_Servicos_Id SMALLINT UNSIGNED, PRIMARY KEY (dataInicio, FK_Aluno_Nmatricula, FK_Servicos_Id) ); CREATE TABLE Agendamento_Professor_Aluno_Recepcionista ( dataHora TIMESTAMP NOT NULL PRIMARY KEY, FK_Professor_Cod SMALLINT unsigned, FK_Aluno_Nmatricula SMALLINT unsigned, FK_Recepcionista_Cod SMALLINT unsigned ); CREATE TABLE Autoriza ( dataHora_entrada TIMESTAMP PRIMARY KEY, FK_Recepcionista_Cod SMALLINT unsigned, FK_Aluno_Nmatricula SMALLINT unsigned, dataHora_saida TIMESTAMP ); CREATE TABLE Avalia ( dataAvaliacao DATE PRIMARY KEY, FK_Professor_Cod SMALLINT unsigned, FK_Aluno_Nmatricula SMALLINT unsigned, treino VARCHAR(250) NOT NULL, parecer VARCHAR(250) NOT NULL ); CREATE TABLE Funcao_Professor ( FK_Funcao_Id SMALLINT unsigned, FK_Professor_Cod SMALLINT unsigned ); CREATE TABLE Pagamento ( data_hora TIMESTAMP PRIMARY KEY, FK_Matricula_dataInicio DATE, FK_Recepcionista_Cod SMALLINT unsigned, valor DECIMAL(5,2) UNSIGNED, forma VARCHAR(10) NOT NULL, FK_Matricula_Nmatricula SMALLINT unsigned, FK_Matricula_Id SMALLINT unsigned ); ALTER TABLE Matricula ADD CONSTRAINT FK_Matricula_1 FOREIGN KEY (FK_Aluno_Nmatricula) REFERENCES Aluno (Nmatricula); ALTER TABLE Matricula ADD CONSTRAINT FK_Matricula_2 FOREIGN KEY (FK_Servicos_Id) REFERENCES Servico (Id); ALTER TABLE Agendamento_Professor_Aluno_Recepcionista ADD FOREIGN KEY (FK_Professor_Cod) REFERENCES Professor (Cod); ALTER TABLE Agendamento_Professor_Aluno_Recepcionista ADD CONSTRAINT FK_Agendamento_Professor_Aluno_Recepcionista_1 FOREIGN KEY (FK_Aluno_Nmatricula) REFERENCES Aluno (Nmatricula) ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE Agendamento_Professor_Aluno_Recepcionista ADD CONSTRAINT FK_Agendamento_Professor_Aluno_Recepcionista_2 FOREIGN KEY (FK_Recepcionista_Cod) REFERENCES Recepcionista (Cod) ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE Autoriza ADD CONSTRAINT FK_Autoriza_1 FOREIGN KEY (FK_Recepcionista_Cod) REFERENCES Recepcionista (Cod) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE Autoriza ADD CONSTRAINT FK_Autoriza_2 FOREIGN KEY (FK_Aluno_Nmatricula) REFERENCES Aluno (Nmatricula) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE Avalia ADD CONSTRAINT FK_Avalia_1 FOREIGN KEY (FK_Professor_Cod) REFERENCES Professor (Cod) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE Avalia ADD CONSTRAINT FK_Avalia_2 FOREIGN KEY (FK_Aluno_Nmatricula) REFERENCESAluno (Nmatricula) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE Funcao_Professor ADD CONSTRAINT FK_Funcao_Professor_0 FOREIGN KEY (FK_Funcao_Id) REFERENCES Funcao (Id) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE Funcao_Professor ADD CONSTRAINT FK_Funcao_Professor_1 FOREIGN KEY (FK_Professor_Cod) REFERENCES Professor (Cod) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE Pagamento ADD CONSTRAINT FK_Pagamento_1 FOREIGN KEY (FK_Matricula_dataInicio) REFERENCES Matricula (dataInicio) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE Pagamento ADD CONSTRAINT FK_Pagamento_2 FOREIGN KEY (FK_Recepcionista_Cod) REFERENCES Recepcionista (Cod) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE Pagamento ADD CONSTRAINT FK_Pagamento_4 FOREIGN KEY (FK_Matricula_Nmatricula) REFERENCES Matricula (FK_Aluno_Nmatricula) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE Pagamento ADD CONSTRAINT FK_Pagamento_5 FOREIGN KEY (FK_Matricula_Id) REFERENCES Matricula (FK_Servicos_Id) ON DELETE SET NULL ON UPDATE CASCADE; APÊNDICE B – Povoamento das tabelas do banco de dados Academia Golds -- INSERT TABELA ALUNO -- insert into Aluno (RG, tel, email, nome, data_cadastro, bairro, numero, cidade, rua, cep, sexo, CPF) values ("7768093247", "71988903821","jeansilva@gmail.com", "Jean Silva Lopes", "2017/10/10", "urbis v", 10, "Vitoria da Conquista", "A", "45290000", "M", "161961658"); insert into Aluno (RG, tel, email, nome, data_cadastro, bairro, numero, cidade, rua, cep, sexo, CPF) values ("2679008772", "77998679421", "mauricio990@gmail.com", "Mauricio da Silva Santos", "2016/09/22", "Cidade Maravilhosa", 22, "Vitoria da Conquista", "B", "45899031", "M", "14578905328"); insert into Aluno (RG, tel, email, nome, data_cadastro, bairro, numero, cidade, rua, cep, sexo, CPF) values ("1218898892", "77998210431", "gustavo_santos22@gmail.com", "Gustavo Santos Maia", "2018/07/22", "Vila Serrana 2", 07, "Vitoria da Conquista", "E", "41849970", "M", "06778929310"); insert into Aluno (RG, tel, email, nome, data_cadastro, bairro, numero, cidade, rua, cep, sexo, CPF) values ("1994013882", "73988210111", "leticia_73988210111@gmail.com", "Leticia Alcantara", "2018/06/10", "Miro Cairo", 022, "Vitoria da Conquista", "A", "49129100", "F", "16254647800"); insert into Aluno (RG, tel, email, nome, data_cadastro, bairro, numero, cidade, rua, cep, sexo, CPF) values ("0127163891", "77991428312", "carolina80@gmail.com", "Carolina Sousa", "2016/02/13", "Vila Serrana", 37, "Vitoria da Conquista", "G", "42839328", "F", "20378949410"); insert into Aluno (RG, tel, email, nome, data_cadastro, bairro, numero, cidade, rua, cep, sexo, CPF) values ("9024263700", "77988800101", "fernandomiguel@gmail.com", "Fernando Miguel Sousa", "2017/04/22", "Vila Serrana 3", 141, "Vitoria da Conquista", "D", "02301068", "M", "12028900192"); insert into Aluno (RG, tel, email, nome, data_cadastro, bairro, numero, cidade, rua, cep, sexo, CPF) values ("0227163891", "77991428171", "anacarolina180@gmail.com", "Ana Carolina Sousa", "2016/02/12", "Vila Serrana 2", 27, "Vitoria da Conquista", "G", "42839178", "F", "26558949812"); insert into Aluno (RG, tel, email, nome, data_cadastro, bairro, numero, cidade, rua, cep, sexo, CPF) values ("254452395", "77983974780", "agathabarbara21@hotmail.com", "Agatha Bárbara Larissa Vieira", "2016/10/28", "Jatobá", 861, "Vitoria da Conquista", "Rua Vinte", "45066298", "F", "61691884561"); insert into Aluno (RG, tel, email, nome, data_cadastro, bairro, numero, cidade, rua, cep, sexo, CPF) values ("275936533", "77981177553", "suelimarciaraimundacarvalho_@hotmail.com", "Sueli Márcia Carvalho", "2016/10/28", "Espírito Santo", 882, "Vitoria da Conquista", "Caminho Cinco", "45037338", "F", "25285065542"); insert into Aluno (RG, tel, email, nome, data_cadastro, bairro, numero, cidade, rua, cep, sexo, CPF) values ("143166542", "77989676062", "oliveremanuel_@gmail.com", "Oliver Emanuel César", "2016/08/19", " Zabelê", 793, "Vitoria da Conquista", "Rua Ipanema", "45077608", "M", "50172329582"); -- INSERT TABELA RECEPCIONISTA -- insert into Recepcionista (RG, nome, bairro, numero, cidade, rua, cep, sexo, CPF, CTPS, login, senha, num, Banco, agencia) values ("4163846", "Marco Tulio", "urbis v", 10, "Vitoria da Conquista", "A", "1568168", "M", "161961658", "15644986", "marcos001", "1792", "446", "Bradesco", "1245"); insert into Recepcionista (RG, nome, bairro, numero, cidade, rua, cep, sexo, CPF, CTPS, login, senha, num, Banco, agencia) values ("5123940", "Joana de Sousa ", "Vila Serrana II", 22, "Vitoria da Conquista", "B", "0528961", "F", "862031228", "24785988", "joanasousa001", "9902", "117", "Banco do Brasil", "1185"); insert into Recepcionista (RG, nome, bairro, numero, cidade, rua, cep, sexo, CPF, CTPS, login, senha, num, Banco, agencia) values ("4413984", "Luis Sousa Silva", "Patagonia", 17, "Vitoria da Conquista", "G", "4101660", "M", "901287120", "02465018", "luissousa001", "1672", "012", "Bradesco", "0121"); -- INSERT TABELA PROFESSOR -- insert into Professor(CTPS,RG,rua,bairro,cep,cidade,numero,sexo,CPF,tel,CREF,agencia,num,banco,nome) values("15644986", "1619616589", "Corcovado", "Cidade Maravilhosa", "01746212","Vitoria da Conquista", 21, "M", "04388700122", "7798843129000","90065128420","2210","17890027","Banco do Brasil","Augusto Alves Santos"); insert into Professor(CTPS,RG,rua,bairro,cep,cidade,numero,sexo,CPF,tel,CREF,agencia,num,banco,nome) values("02681977", "2460016588", "Antonio Carlos", "Lagoa das Flores","01746212", "Vitoria da Conquista", 67, "M", "00281774132", "7799844219800","78162429709","8021","10298712","Banco do Brasil","Marcelo Nunues Aguiar"); insert into Professor(CTPS,RG,rua,bairro,cep,cidade,numero,sexo,CPF,tel,CREF,agencia,num,banco,nome) values("07648921", "1018816907", "Augusto Miguel", "Candeias", "01746212","Vitoria da Conquista", 190, "F", "22392710123", "7398811120200","15565248400","1590","2702122","Banco do Brasil","Marina Silva Santos"); insert into Professor(CTPS,RG,rua,bairro,cep,cidade,numero,sexo,CPF,tel,CREF,agencia,num,banco,nome) values("46640211", "2112416097", "Caminho 18", "Vila Serrana", "11709220","Vitoria da Conquista", 112, "M", "44392410908", "7398898142212","22564348490","1241","2712144","Banco do Brasil","Joao Pedro Santos"); insert into Professor(CTPS,RG,rua,bairro,cep,cidade,numero,sexo,CPF,tel,CREF,agencia,num,banco,nome) values("22110201", "0412412144", "Rua M", "Patagonia", "23719290","Vitoria da Conquista", 21, "M", "41392498991", "7398898011000","22294332498","1401","4412121","Bradesco","Paulo Roberto Morais"); insert into Professor(CTPS,RG,rua,bairro,cep,cidade,numero,sexo,CPF,tel,CREF,agencia,num,banco,nome) values("54670202", "2234016012", "Rua G", "Centro", "21709070","Vitoria da Conquista", 89, "M", "21392010990", "7398892109222","10564548401","1161","9822100","Bradesco","Carlos Eduardo Sampaio"); -- INSERT TABELA AGENDAMENTO_PROFESSOR_ALUNO_RECEPCIONISTA -- insert into agendamento_professor_aluno_recepcionista(dataHora, FK_Professor_Cod, FK_Aluno_Nmatricula, FK_Recepcionista_Cod) values ('2017-01-20', 1, 1, 1); insert into agendamento_professor_aluno_recepcionista(dataHora, FK_Professor_Cod, FK_Aluno_Nmatricula, FK_Recepcionista_Cod) values ('2017-04-12', 2, 2, 2); insert into agendamento_professor_aluno_recepcionista(dataHora, FK_Professor_Cod, FK_Aluno_Nmatricula, FK_Recepcionista_Cod) values ('2018-01-30', 3, 3, 3); insert into agendamento_professor_aluno_recepcionista(dataHora, FK_Professor_Cod, FK_Aluno_Nmatricula, FK_Recepcionista_Cod) values ('2017-07-18', 3, 4, 1); insert into agendamento_professor_aluno_recepcionista(dataHora, FK_Professor_Cod, FK_Aluno_Nmatricula, FK_Recepcionista_Cod) values ('2018-02-20', 4, 5, 1); insert into agendamento_professor_aluno_recepcionista(dataHora, FK_Professor_Cod, FK_Aluno_Nmatricula, FK_Recepcionista_Cod) values ('2017-11-30', 5, 6,2); insert into agendamento_professor_aluno_recepcionista(dataHora, FK_Professor_Cod, FK_Aluno_Nmatricula, FK_Recepcionista_Cod) values ('2018-04-22', 6, 7, 3); insert into agendamento_professor_aluno_recepcionista(dataHora, FK_Professor_Cod, FK_Aluno_Nmatricula, FK_Recepcionista_Cod) values ('2018-01-29', 6, 8, 1); insert into agendamento_professor_aluno_recepcionista(dataHora, FK_Professor_Cod, FK_Aluno_Nmatricula, FK_Recepcionista_Cod) values ('2017-02-14', 2, 9, 1); insert into agendamento_professor_aluno_recepcionista(dataHora, FK_Professor_Cod, FK_Aluno_Nmatricula, FK_Recepcionista_Cod) values ('2017-12-02', 4, 10, 2); -- INSET TABELA AUTORIZA – insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2017-04-29 10:02:42', 1, 1, '2017-04-29 11:12:02'); insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2016-12-02 13:37:02', 1, 2, '2016-12-02 14:42:12'); insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2017-02-01 09:29:08', 2, 3, '2017-02-01 10:12:58'); insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2017-12-02 13:37:02', 1, 4, '2017-12-02 14:42:12'); insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2018-03-19 21:22:19', 3, 5, '2018-03-19 22:14:10'); insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2018-07-09 07:02:33', 1, 6, '2018-07-09 08:18:01'); insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2018-07-11 11:00:52', 2, 7, '2018-07-11 12:18:21'); insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2016-05-29 11:00:42', 1, 8, '2016-05-29 12:08:02'); insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2018-04-02 07:12:22', 3, 9, '2018-04-02 08:20:12'); insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2018-01-02 09:44:08', 2, 10, '2018-01-02 11:01:04'); insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2018-06-12 09:12:34', 2, 1, '2018-06-12 10:22:58'); insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2018-02-02 22:20:19', 3, 2, '2018-02-02 23:12:01'); insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2016-07-09 05:02:21', 1, 3, '2016-07-09 06:08:21'); insert into autoriza (dataHora_entrada, FK_Recepcionista_Cod, FK_Aluno_Nmatricula, dataHora_saida) values ('2017-02-11 11:02:12', 2, 4, '2017-02-11 12:32:02'); -- INSERT TABELA FUNCAO -- insert into funcao(especialidade) values ('Personal trainer'); insert into funcao(especialidade) values ('Professor de dança'); insert into funcao(especialidade) values ('Professor de ginastica'); -- INSERT TABELA AVALIA -- insert into avalia(dataAvaliacao, FK_Professor_Cod, FK_Aluno_Nmatricula, treino, parecer) values ('2017-10-02',1,1,'ABC','O aluno apresenta.....'); insert into avalia(dataAvaliacao, FK_Professor_Cod, FK_Aluno_Nmatricula, treino, parecer) values ('2016-11-11',2,2,'ABCDE','O aluno apresenta.....'); insert into avalia(dataAvaliacao, FK_Professor_Cod, FK_Aluno_Nmatricula, treino, parecer) values ('2018-06-10',3,3,'ABC 2x','O aluno apresenta.....'); insert into avalia(dataAvaliacao, FK_Professor_Cod, FK_Aluno_Nmatricula, treino, parecer) values ('2017-11-02',4,4,'AB','O aluno apresenta.....'); insert into avalia(dataAvaliacao, FK_Professor_Cod, FK_Aluno_Nmatricula, treino, parecer) values ('2018-02-02',5,7,'ABC','O aluno apresenta.....'); insert into avalia(dataAvaliacao, FK_Professor_Cod, FK_Aluno_Nmatricula, treino, parecer) values ('2016-12-20',6,6,'ABCDE','O aluno apresenta.....'); insert into avalia(dataAvaliacao, FK_Professor_Cod, FK_Aluno_Nmatricula, treino, parecer) values ('2018-02-10',1,7,'ABC 2x','O aluno apresenta.....'); insert into avalia(dataAvaliacao, FK_Professor_Cod, FK_Aluno_Nmatricula, treino, parecer) values ('2017-10-11',5,8,'AB','O aluno apresenta.....'); insert into avalia(dataAvaliacao, FK_Professor_Cod, FK_Aluno_Nmatricula, treino, parecer) values ('2017-01-10',5,9,'ABC 2x','O aluno apresenta.....'); insert into avalia(dataAvaliacao, FK_Professor_Cod, FK_Aluno_Nmatricula, treino, parecer) values ('2017-08-22',6,10,'AB','O aluno apresenta.....'); -- INSERT TABELA SERVICO – insert into servico (modalidade, valor) values ('Musculaçao', 80); insert into servico (modalidade, valor) values ('Dança', 50); insert into servico (modalidade, valor) values ('Ginastica', 50); -- INSERT TABELA FUNCAO_PROFESSOR – insert into funcao_professor(FK_Funcao_Id, FK_Professor_Cod) values (1, 1); insert into funcao_professor(FK_Funcao_Id, FK_Professor_Cod) values (2, 2); insert into funcao_professor(FK_Funcao_Id, FK_Professor_Cod) values (3, 3); insert into funcao_professor(FK_Funcao_Id, FK_Professor_Cod) values (1, 4); insert into funcao_professor(FK_Funcao_Id, FK_Professor_Cod) values (1, 5); insert into funcao_professor(FK_Funcao_Id, FK_Professor_Cod) values (2, 6); -- INSERT TABELA MATRICULA – insert into matricula(dataInicio,valor,FK_Aluno_Nmatricula, FK_Servicos_Id, dataFim) values ('2017-10-10', 80.00, 1,1, '2017-11-10'); insert into matricula(dataInicio,valor,FK_Aluno_Nmatricula, FK_Servicos_Id, dataFim) values ('2016-08-22', 80.00, 2,1, '2016-09-22'); insert into matricula(dataInicio,valor,FK_Aluno_Nmatricula, FK_Servicos_Id, dataFim) values ('2018-06-05', 80.00, 3,1, '2018-07-05'); insert into matricula(dataInicio,valor,FK_Aluno_Nmatricula, FK_Servicos_Id, dataFim) values ('2018-07-20', 50.00, 4,2, '2018-08-20'); insert into matricula(dataInicio,valor,FK_Aluno_Nmatricula, FK_Servicos_Id, dataFim) values ('2016-09-20', 50.00, 5,3, '2016-10-20'); insert into matricula(dataInicio,valor,FK_Aluno_Nmatricula, FK_Servicos_Id, dataFim) values ('2017-11-12', 80.00, 6,1, '2017-12-12'); insert into matricula(dataInicio,valor,FK_Aluno_Nmatricula, FK_Servicos_Id, dataFim) values ('2016-09-24', 80.00, 7,1, '2016-10-24'); insert into matricula(dataInicio,valor,FK_Aluno_Nmatricula, FK_Servicos_Id, dataFim) values ('2018-01-17', 80.00, 8,1, '2018-02-17'); insert into matricula(dataInicio,valor,FK_Aluno_Nmatricula, FK_Servicos_Id, dataFim) values ('2018-03-09', 50.00, 9,2, '2018-04-09'); insert into matricula(dataInicio,valor,FK_Aluno_Nmatricula, FK_Servicos_Id, dataFim) values ('2016-04-10', 50.00, 10,3,'2016-05-10'); -- INSERT TABELA PAGAMENTO -- insert into pagamento(data_hora, FK_Matricula_dataInicio, FK_Recepcionista_Cod, valor, forma, FK_matricula_Nmatricula, FK_Matricula_Id) values('2018-02-09 12:02:30','2017-10-10',1,80.00,'Credito',1,1); insert into pagamento(data_hora, FK_Matricula_dataInicio, FK_Recepcionista_Cod, valor, forma, FK_matricula_Nmatricula, FK_Matricula_Id) values ('2018-03-09 08:32:12', '2018-03-09', 2, 50.00,'Credito', 9,2); insert into pagamento(data_hora, FK_Matricula_dataInicio, FK_Recepcionista_Cod, valor, forma, FK_matricula_Nmatricula, FK_Matricula_Id) values ('2018-01-17 18:25:03', '2018-01-17', 1, 80.00,'Dinheiro', 8, 1); insert into pagamento(data_hora, FK_Matricula_dataInicio, FK_Recepcionista_Cod, valor, forma, FK_matricula_Nmatricula, FK_Matricula_Id) values ('2018-01-17 14:25:36', '2016-09-20', 1, 50.00,'Dinheiro', 5, 3); 6 Link para os Scripts SQL e Inserts https://goo.gl/i1oCcf
Compartilhar