Baixe o app para aproveitar ainda mais
Prévia do material em texto
BANCO DE DADOS E-book 3 Gratuliano Lucena Neste E-Book: INTRODUÇÃO ����������������������������������������������4 UTILIZAÇÃO DE COMANDOS SQL PARA CRIAÇÃO E MANUTENÇÃO DE DADOS ���������������������������������������������������� 5 CRIAÇÃO DE BANCOS DE DADOS ������� 6 Criação de Schema ��������������������������������������������������6 Exclusão de Banco de Dados ou Schema ��������������8 Criação de colunas ��������������������������������������������������8 Alteração de colunas �������������������������������������������� 11 Exclusão de colunas ��������������������������������������������� 11 Incluir uma coluna������������������������������������������������� 12 Criação de Tabelas ����������������������������������������������� 12 Concessão de acesso a tabelas – privilégios ����� 19 Criação de chave estrangeira ������������������������������� 20 Execução de Scripts de um modelo de dados para Criação de Tabelas ��������������������������������������� 21 Modelo de dados após criação de tabelas no IDE Workbench MySQL ����������������������������������������� 30 MANIPULAÇÃO DE DADOS������������������� 33 Inclusão de Dados ������������������������������������������������ 34 Inclusão de Dados da tabela Aluno ��������������������� 34 Inclusão de Dados da tabela disciplina ��������������� 35 Inclusão de Dados da tabela Alunoxdisciplina ��� 36 2 Exclusão de Dados������������������������������������������������ 38 Alteração de Dados ���������������������������������������������� 39 Consulta de Dados ������������������������������������������������ 41 CONSIDERAÇÕES FINAIS ��������������������� 44 SÍNTESE �������������������������������������������������������45 3 INTRODUÇÃO O Modelo Lógico abrange, em primeiro lugar, enten- der a regra de negócio que envolve o entendimento dos requisitos dos usuários. Com isso, encaminha- -se uma solução intelectual de dados que atendam simultaneamente os requisitos do usuário e também às funções de inteligência do sistema. A par t i r de um Diagrama de Ent idade e Relacionamento (DER), em que se planeja a com- posição de tabelas, formulação dos índices, formas de relacionamentos entre tabelas, por meio da fer- ramenta Case DBDesignerfork, gerou-se um script em linguagem DML (linguagem de manipulação de dados). Os scripts em linguagem DML são executados na ferramenta IDE Workbench MySQL para entender a prática de criação de Bancos de Dados. Após a criação do banco de dados e das tabelas, são executados comandos do SQL, na linguagem DDL (Data Definition Language), para praticar a in- clusão, alteração, exclusão e consultas de dados. 4 UTILIZAÇÃO DE COMANDOS SQL PARA CRIAÇÃO E MANUTENÇÃO DE DADOS Todo o processo de aprendizado é realizado numa ferramenta IDE Workbench MySQL para fixar os co- nhecimentos explicados de forma prática� Há uma preocupação em esclarecer de forma ilustra- da e prática cada passo, desde a criação de Bancos de Dados e “Schema”, a conversão de modelo lógico em linguagem DML (linguagem de manipulação de dados) para criação de tabelas, que mostra os resul- tados após a execução dos scripts� O aprendizado de bancos se apoia na prática da manutenção de “Schema”, tabelas, linhas e colunas, construção de índices� Também são demonstra- dos: a execução de comandos DDL (Data Definition Language) de forma prática, a manipulação de dados com comandos do SQL, por exemplo, a Inclusão de dados (Insert), alteração de dados (Update), exclusão de dados (Delete) e consulta de Dados (Select)� Vamos demonstrar a criação de um banco de dados tomando como exemplo um modelo completo de dados que contempla todas as situações que com- põem o entendimento de conhecimento da engenha- ria de bancos de dados, possibilitando ao leitor uma capacitação para desempenhar com facilidade as atividades pertinentes a banco de dados� 5 CRIAÇÃO DE BANCOS DE DADOS A criação de tabela tem o objetivo de reunir um con- junto de tabelas, o qual podemos referenciar para o acesso às tabelas e para realizar as conexões com aplicativos ou servidor de banco de dados� Um exemplo para criarmos um banco de dados, de nome “test”, é o seguinte: CREATE DATABASE test; CREATE USER ‘test’ IDENTIFIED BY; Quando criamos um banco dados, torna-se necessário conceder um acesso, que pode ser im- plementado com a execução da instrução “CREATE USER ‘test’ IDENTIFIED BY”, sendo que o que vem após o BY é a informação de uma senha, por exem- plo, “1234”, que deverá ser informada toda vez que se fizer uma conexão com o banco de dados. Criação de Schema A permissão de um “Schema” pode ser concedida para usuários e grupos de usuários� Quando o usuário tem acesso a um “Schema”, ele tem autorização para acessar todas às tabelas que compõem o “Schema”� 6 Em segmentação de dados por grupo de usuários, você pode ter na mesma database duas tabelas com o mesmo nome, estando cada uma em um “Schema” diferente� Conceitualmente, um “Schema” é um conjunto de objetos de banco de dados inter-relacionados, como tabelas, colunas, tipos de dados, índices, chaves es- trangeiras, e assim por diante� Esses objetos estão conectados por meio da sintaxe SQL porque as co- lunas compõem as tabelas, chaves estrangeiras e referenciam essas colunas� No MySQL, fisicamente, um “Schema” é sinônimo de um banco de dados� É possível substituir a palavra- -chave SCHEMA por DATABASE na sintaxe SQL do MySQL, por exemplo, utilizando CREATE SCHEMA em vez de CREATE DATABASE� O comando para criar um “Schema” tem a seguinte sintaxe: CREATE SCHEMA ‘teste1’� Abaixo – conforme ilustrado na figura 2 – criamos um exemplo dentro da ferramenta Workbench do MySQL, chamado de “teste1”� Figura 1: Exemplo de criação de Schema� Fonte: Elaboração própria� 7 Quando criamos o “Schema” tem-se o mesmo efeito de criar o Banco de Dados� Por exemplo, conforme demonstrado na figura 1, podemos notar que o “test” que foi criado com a sintaxe 2 CREATE DATABASE test gerou um “Schema” chamado “test” e utilizan- do CREATE SCHEMA ‘teste1’ também gerou um “Schema” chamado “teste2”� Exclusão de Banco de Dados ou Schema Para excluir um “Schema”, utilizamos a seguinte sin- taxe de comandos: DROP SCHEMA ‘teste2’ Criação de colunas Há configurações que precisamos entender quando da criação das colunas (campos) dentro de uma ta- bela� Temos que estabelecer um tamanho de coluna que seja suficiente para comportar o conteúdo da informação, de acordo com a regra de negócio; e que o dimensionamento do tamanho da coluna possa suportar, por um bom tempo, frente ao aumento das informações por parte do usuário� Também deve ser definido qual o tipo da informação ideal – que deve ser compatível com a regra de ne- 8 gócio do usuário� A escolha deve ser feita conforme demonstrado nas tabelas 1, 2 ou 3� Tipos de Dados TINYINT Número inteiro muito pequeno (tiny)� SMALLINT Número inteiro pequeno. MEDIUMINT Número inteiro de tamanho médio. INT Número inteiro de tamanho comum. BIGINT Número inteiro de tamanho grande. FLOAT Número de ponto flutuante de precisão simples (32 bits)� DOUBLE Número de ponto flutuante de precisão dupla (64 bits)� DECIMAL (N,D) Número decimal, com N = inteiro e D = Decimal� Tabela 1: Tipos de Dados Numéricos� Tipos de Alfanuméricos – Strings CHAR() Cadeia de caracteres (string), de tamanho fixo de acordo com N. VARCHAR() String de tamanho variável de acordo com N. BINARY Número inteiro de tamanho médio. VARBINARY String binária de tamanho fixo. BLOB Binary Large OBject – Objeto Grande Binário TINYBLOB Um BLOB muito pequeno. MEDIUMBLOB Um BLOB de tamanho médio. LONGBLOB Um BLOB grande. TINYTEXT String não binária e de tamanho bem reduzido. TEXT String não binária e pequena. MEDIUMTEXT String de tamanho comum e não binária. LONGTEXT String não binária de tamanho grande. Tabela 2: Tipos de Dados Alfanuméricos – Strings� 9 Tipos de Tempo DATE valor referente a uma data no formato ‘AAAA-MM-DD’. TIME Valor horário no forma- to ‘hh:mm:ss’(hora:minutos:segundos). TIMESTAMP “Timestamp” é uma sequência de caracte- res ou informação codificada que identifica uma marca temporal ou um dado momento em que um evento ocorreu� Tem o forma- to ‘AAAA-MM-DD hh:mm:ss’. YEAR Armazena um ano no formato ‘CCYY’ ou ‘YY’ Tabela 3: Tipos de Dados de tempo� Analisemos um exemplo de criação de colunas, con- forme ilustrado na figura 2. Figura 2: Exemplo de criação de colunas com tamanho e tipo de dados� Fonte: Elaboração própria� Temos três colunas (figura 2): uma é a Disciplina_ Codigo – numérico e inteiro de 10 posições; a se- gunda coluna, Disciplina_Nome, é alfanumérica com 40 posições; e a terceira coluna é numérica e inteira com dez posições� 10 Alteração de colunas Às vezes, surge a necessidade de aumentar o tama- nho ou o tipo de dados de uma coluna� Quando isso acontece, podemos fazer com o comando ALTER� 1. Criação de Tabelas CREATE TABLE ‘teste2’�‘produto’ ( ‘PRODUTOCODIGO’ INT NOT NULL, ‘PRODUTODESCRICAO’ VARCHAR(45) NULL); a) Alteração da coluna de Tabelas Na alteração vamos mudar o tamanho da coluna ‘PRODUTODESCRICAO’ VARCHAR(45) NULL) para ‘PRODUTODESCRICAO’ VARCHAR(50) NULL); para isso, vamos executar a seguinte sintaxe: ALTER TABLE ‘teste2’�‘produto’ CHANGE COLUMN ‘PRODUTODESCRICAO’ ‘PRODUTODESCRICAO’ VARCHAR(50) NULL DEFAULT NULL� Exclusão de colunas Às vezes, surge a necessidade de excluir uma coluna. Podemos fazer com o comando DROP. ALTER TABLE ‘teste2’. ‘produto’ DROP COLUMN ‘PRODUTODESCRICAO’. 11 Incluir uma coluna Para incluir uma coluna ou mais numa tabela já exis- tente, podemos fazer com o comando CHANGE� ALTER TABLE ‘teste2’�‘produto’ CHANGE COLUMN ‘PRODUTODESCRICAO’ VARCHAR(60) NULL� Podcast 1 Criação de Tabelas A tabela nasce com um nome e se compõe de linhas (Registro) e colunas (Campos); contém definições de tamanho e tipo de cada coluna� Contém nomes de colunas, definição de coluna chave, e definição de chaves estrangeiras� As linhas e as colunas ser- vem de referência para armazenar os conteúdos dos dados� Chave Primária (Primary Key) é uma ou mais colunas com valores que são únicos dentro da tabela e, por isso, podem ser usados para identificar as linhas dessa tabela� Há uma série de parâmetros que são considerados, no momento de criação de uma tabela: 12 https://famonline.instructure.com/files/168936/download?download_frd=1 1. UNSIGNED – Modificador que faz com que uma coluna aceite apenas números positivos (sem sinal). Somente para tipo “integer”� 2. ZEROFILL – Adiciona zeros de preenchimento à esquerda para completar um número; por exemplo, se o campo tem 5 posições e temos o conteúdo de 235, o resultado fica 00235. 3. AUTO_INCREMENT – Campo que soma automá- tica e sequencialmente mais 1 ao conteúdo anterior. 4. ON DELETE NO ACTION - Exclui somente o pai sem afetar o(s) registro(s) filhos ou relacionados em outras entidades� 5. ON DELETE CASCADE – É a exclusão com efeito realizado em cascata, quando se exclui o registro de uma tabela principal, também exclui todos os regis- tros de outras tabelas com conteúdo associado por chaves estrangeiras à tabela principal� 6. CASCADE: A opção CASCADE permite excluir ou atualizar os registros relacionados presentes na tabela filha automaticamente, quando um registro da tabela pai for atualizado (ON UPDATE) ou excluído (ON DELETE)� É a opção mais comumente aplicada� 7. RESTRICT: Impede que ocorra a exclusão ou a atualização de um registro da tabela principal, caso haja alguma tabela com conteúdo associado à tabela principal com chave estrangeira. A verificação de integridade referencial é realizada antes de tentar executar a instrução UPDATE ou DELETE� 13 8. SET NULL: Esta opção é usada para definir com o valor NULL o campo na tabela filha, quando um registro da tabela pai for atualizado ou excluído� 9. NO ACTION: Essa opção equivale à opção RESTRICT, porém a verificação de integridade re- ferencial é executada após a tentativa de alterar a tabela� É a opção padrão, aplicada caso nenhuma das opções seja definida na criação da chave estrangeira. 10. SET DEFAULT: “Configura Padrão” – Define um va- lor padrão na coluna na tabela filha, aplicado quando um registro da tabela pai for atualizado ou excluído� 11. PRIMARY KEY (PK): Esta restrição cria um índice único para um conjunto de colunas ou uma coluna para Chave Primária� 12. UNIQUE: Esta Contraint determina que uma coluna não poderá ter duas linhas com o mesmo valor� 13. FOREIGN KEY (FK ou Chave Estrangeira): Determina uma ou mais colunas, a qual se consi- dera o seu conteúdo como único dentro da tabela. 14. CHECK: Especifica uma condição lógica que con- diciona para efetivar o salvamento da tabela� 15. NOT NULL: Determina que a coluna tem preen- chimento obrigatório� 14 Um exemplo para criação de tabela (abaixo) explicado: CREATE TABLE ‘test’.‘alunoxdisciplina’ ( ‘test’ - corresponde ao nome do banco de dados ou “schema” ‘alunoxdisciplina’ - Corresponde o nome da tabela ‘Aluno_CPF’ INT(10) UNSIGNED NOT NULL, ‘Aluno_CPF’ – Nome da Coluna ou Campo. INT –Tipo da coluna (campo). UNSIGNED - Aceite apenas números positivos. NOT NULL – Coluna com conteúdo obrigatório. ‘Disciplina_Codigo’ INT(10) UNSIGNED NOT NULL, ‘Disciplina_Codigo’ – Nome da Coluna ou Campo. INT –Tipo da coluna (campo). UNSIGNED - Aceite apenas números positivos. NOT NULL – Coluna com conteúdo obrigatório. ‘Alunoxdisciplina_DataValidade’ DATE NULL DEFAULT NULL, ‘Alunoxdisciplina_DataValidade’ – Nome da Coluna ou Campo. DATE – Tipo da coluna que aceita somente con- teúdo no formato data (DD/MM/AAA). 15 NULL – Coluna com conteúdo opcional. DEFAULT NULL – Quando o campo não for infor- mado, assume o NULL, sem ser necessário ter um conteúdo obrigatório. PRIMARY KEY (‘Aluno_CPF’, ‘Disciplina_Codigo’), PRIMARY KEY – Chave primária com único con- teúdo em toda tabela. ‘Aluno_CPF’ – Nome da primeira coluna da chave primária. ‘Disciplina_Codigo’ – Nome da segunda coluna da chave primária. INDEX ‘AlunoXDisciplina_FKIndex1’ (‘Discipli- na_Codigo’ ASC) VISIBLE, INDEX – Primeiro índice da chave estrangeira (Foreign Key). ‘AlunoXDisciplina_FKIndex1’ – Nome do primeiro índice da chave estrangeira (Foreign Key). ASC – Classificação do índice de forma crescente do menor para o maior. VISIBLE – Significa que índice está sempre dispo- nível para usá-lo. INDEX ‘AlunoXDisciplina_FKIndex2’ (‘Aluno_CPF’ ASC) VISIBLE, Idem as mesmas configurações da primeira chave estrangeira. 16 CONSTRAINT ‘alunoxdisciplina_ibfk_1’ CONSTRAINT – Coluna com restrição que só per- mite a criação da tabela desde que conteúdo este- ja presente na tabela pai (principal). ‘alunoxdisciplina_ibfk_1’ – Nome de referência da primeira chave estrangeira. FOREIGN KEY (‘Disciplina_Codigo’) FOREIGN KEY – Comando de indicação de chave estrangeira. ‘Disciplina_Codigo’ – Nome da coluna da chave estrangeira referente a nome em outra tabela pai (Principal). REFERENCES ‘test’.‘disciplina’ (‘Disciplina_Codigo’) REFERENCES – Comando para referenciar a outra tabela pai (principal) onde tem a chave de origem. ‘test’ – Nome do Banco ou esquema onde exis- te a tabela com chave de origem para ser chave estrangeira. ‘disciplina’ – Nome tabela onde com chave de ori- gem para ser a chave estrangeira. ‘Disciplina_Codigo’ – Nome da coluna (campo) de origem da chave estrangeira. 17 ON DELETE NO ACTION ON DELETE – Quando houver exclusão de linha (registro). Exclui somente o pai sem afetar o(s) registro(s) filhos ou relacionados em outras entidades. ON UPDATE NO ACTION, ON UPDATE – Quando houver alteração de colu- na (campo) na linha (registro). Altera somente o pai sem afetar o(s) registro(s) filhos ou relacionados em outras entidades. CONSTRAINT ‘alunoxdisciplina_ibfk_2’ FOREIGN KEY (‘Aluno_CPF’) REFERENCES ‘test’.‘aluno’ (‘Aluno_CPF’) ON DELETE NO ACTION ON UPDATE NO ACTION); Após a execução do “script” acima, temoso resul- tado no Workbench do MySQL: 18 Figura 3: Exemplo de criação de tabela� Fonte: Elaboração própria� Na figura 3 é demonstrado como fica a criação da tabela no Workbench. Verificamos que está assinala- do “PK”, que significa chave primária para as colunas Aluno_CPF e Disciplina_Codigo. Também verificamos que está assinalado “NN”, que é “NOT NULL”, ou seja, que as colunas Aluno_CPF e Disciplina_Codigo de- vem ter, obrigatoriamente, conteúdos. Enquanto que a coluna Alunoxdisciplina tem conteúdo opcional, conforme a cláusula NULL� Concessão de acesso a tabelas – privilégios Quando se cria um “Schema”, é possível conceder acesso e privilégios por meio do seguinte comando: GRANT ALL PRIVILEGES ON test�* TO testecursoxdisciplina 19 Criação de chave estrangeira Toda que vez que temos uma relação terceira forma normal entre tabelas, temos de juntar as chaves de duas tabelas numa terceira tabela, chamada de en- tidade associativa, conforme ilustrado na figura 4. Figura 4: Exemplo de entidade associativa para criar chave estran- geira� Fonte: Elaboração própria� Observamos na figura 4, que a coluna chave Aluno_ CPF da tabela Aluno e também a coluna chave Disciplina_Codigo da tabela disciplina servem de chaves estrangeiras na tabela Alunoxdisciplina, pois são consideradas chaves estrangeiras, porque não são chaves próprias da tabela Alunoxdisciplina, foram criadas em outras tabelas� 20 Execução de Scripts de um modelo de dados para Criação de Tabelas A partir de um modelo de dados criado na ferramen- ta DBDesignerfork, foi gerado um script completo em linguagem DML� Esse script foi executado na ferramenta IDE Workbench do MySQL, o resultado foi a criação de todas as tabelas� A partir das tabe- las criadas, foi gerado um modelo lógico dentro da ferramenta Workbench, o que resultou numa figu- ra gráfica do DER, conforme apresentado no item anterior, semelhante ao modelo lógico da fermenta DBDesignerfork� CREATE TABLE ‘test’�‘aluno’ ( ‘Aluno_CPF’ INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, ‘Aluno_Nome’ VARCHAR(40) NULL DEFAULT NULL, PRIMARY KEY (‘Aluno_CPF’)); -- ----------------------------------------------------- CREATE TABLE ‘test’�‘disciplina’ ( ‘Disciplina_Codigo’ INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, ‘Disciplina_Nome’ VARCHAR(40) NULL DEFAULT NULL, ‘Disciplina_Creditos’ INT(10) UNSIGNED NULL DEFAULT NULL, 21 PRIMARY KEY (‘Disciplina_Codigo’)); -- ----------------------------------------------------- CREATE TABLE ‘test’�‘alunoxdisciplina’ ( ‘Aluno_CPF’ INT(10) UNSIGNED NOT NULL, ‘Disciplina_Codigo’ INT(10) UNSIGNED NOT NULL, ‘Alunoxdisciplina_DataValidade’ DATE NULL DEFAULT NULL, PRIMARY KEY (‘Aluno_CPF’, ‘Disciplina_Codigo’), INDEX ‘AlunoXDisciplina_FKIndex1’ (‘Disciplina_ Codigo’ ASC) VISIBLE, INDEX ‘AlunoXDisciplina_FKIndex2’ (‘Aluno_CPF’ ASC) VISIBLE, CONSTRAINT ‘alunoxdisciplina_ibfk_1’ FOREIGN KEY (‘Disciplina_Codigo’) REFERENCES ‘test’.‘disciplina’ (‘Disciplina_Codigo’) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT ‘alunoxdisciplina_ibfk_2’ FOREIGN KEY (‘Aluno_CPF’) REFERENCES ‘test’.’aluno’ (‘Aluno_CPF’) ON DELETE NO ACTION ON UPDATE NO ACTION); 22 -- ----------------------------------------------------- CREATE TABLE ‘test’�‘turma’ ( ‘Turma_Numero’ INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, ‘Turma_Sala’ INT(10) UNSIGNED NULL DEFAULT NULL, ‘Turma_Horario’ DATETIME NULL DEFAULT NULL, PRIMARY KEY (‘Turma_Numero’)); -- ----------------------------------------------------- CREATE TABLE ‘test’�‘alunoxturma’ ( ‘Turma_Numero’ INT(10) UNSIGNED NOT NULL, ‘Aluno_CPF’ INT(10) UNSIGNED NOT NULL, ‘Alunoxturma_DataValidade’ DATE NULL DEFAULT NULL, PRIMARY KEY (‘Turma_Numero’, ‘Aluno_CPF’), INDEX ‘AlunoXTurma_FKIndex1’ (‘Turma_Numero’ ASC) VISIBLE, INDEX ‘AlunoXTurma_FKIndex2’ (‘Aluno_CPF’ ASC) VISIBLE, CONSTRAINT ‘alunoxturma_ibfk_1’ FOREIGN KEY (‘Turma_Numero’) REFERENCES ‘test’.‘turma’ (‘Turma_Numero’) ON DELETE NO ACTION 23 ON UPDATE NO ACTION, CONSTRAINT ‘alunoxturma_ibfk_2’ FOREIGN KEY (‘Aluno_CPF’) REFERENCES ‘test’.‘aluno’ (‘Aluno_CPF’) ON DELETE NO ACTION ON UPDATE NO ACTION); ------------------------------------------------------- CREATE TABLE ‘test’�‘curso’ ( ‘Curso_Codigo’ INT(10) UNSIGNED NOT NULL, ‘Curso_Descricao’ VARCHAR(40) NOT NULL, PRIMARY KEY (‘Curso_Codigo’)); ------------------------------------------------------- CREATE TABLE ‘test’�‘cursoxdisciplina’ ( ‘Curso_Codigo’ INT(10) UNSIGNED NOT NULL, ‘Disciplina_Codigo’ INT(10) UNSIGNED NOT NULL, ‘Cursoxdisciplina_DataValidade’ DATE NULL DEFAULT NULL, PRIMARY KEY (‘Curso_Codigo’, ‘Disciplina_Codigo’), INDEX ‘CursoxDisciplina_FKIndex1’ (‘Disciplina_ Codigo’ ASC) VISIBLE, INDEX ‘CursoxDisciplina_FKIndex2’ (‘Curso_Codigo’ ASC) VISIBLE, 24 CONSTRAINT ‘cursoxdisciplina_ibfk_1’ FOREIGN KEY (‘Disciplina_Codigo’) REFERENCES ‘test’.‘disciplina’ (‘Disciplina_Codigo’) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT ‘cursoxdisciplina_ibfk_2’ FOREIGN KEY (‘Curso_Codigo’) REFERENCES ‘test’.‘curso’ (‘Curso_Codigo’) ON DELETE NO ACTION ON UPDATE NO ACTION); ------------------------------------------------------- -- Table ‘test’�‘professor’ ------------------------------------------------------- CREATE TABLE ‘test’�‘professor’ ( ‘Professor_CPF’ INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, ‘Professor_Nome’ VARCHAR(40) NULL DEFAULT NULL, ‘Professor_Endereco’ VARCHAR(40) NULL DEFAULT NULL, PRIMARY KEY (‘Professor_CPF’)); ------------------------------------------------------- 25 CREATE TABLE ‘test’�‘professorxaluno’ ( ‘Professor_CPF’ INT(10) UNSIGNED NOT NULL, ‘Aluno_CPF’ INT(10) UNSIGNED NOT NULL, ‘Professorxaluno_DataValidade’ DATE NULL DEFAULT NULL, PRIMARY KEY (‘Professor_CPF’, ‘Aluno_CPF’), INDEX ‘ProfessorXAluno_FKIndex1’ (‘Aluno_CPF’ ASC) VISIBLE, INDEX ‘ProfessorXAluno_FKIndex2’ (‘Professor_CPF’ ASC) VISIBLE, CONSTRAINT ‘professorxaluno_ibfk_1’ FOREIGN KEY (‘Aluno_CPF’) REFERENCES ‘test’.‘aluno’ (‘Aluno_CPF’) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT ‘professorxaluno_ibfk_2’ FOREIGN KEY (‘Professor_CPF’) REFERENCES ‘test’.‘professor’ (‘Professor_CPF’) ON DELETE NO ACTION ON UPDATE NO ACTION); ------------------------------------------------------- CREATE TABLE ‘test’�‘professorxcurso’ ( 26 ‘Professor_CPF’ INT(10) UNSIGNED NOT NULL, ‘Curso_Codigo’ INT(10) UNSIGNED NOT NULL, ‘Professorxcurso_DataValidade’ DATE NULL DEFAULT NULL, PRIMARY KEY (‘Professor_CPF’, ‘Curso_Codigo’), INDEX ‘ProfessorXCurso_FKIndex1’ (‘Professor_CPF’ ASC) VISIBLE, INDEX ‘ProfessorXCurso_FKIndex2’ (‘Curso_Codigo’ ASC) VISIBLE, CONSTRAINT ‘professorxcurso_ibfk_1’ FOREIGN KEY (‘Professor_CPF’) REFERENCES ‘test’.‘professor’ (‘Professor_CPF’) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT ‘professorxcurso_ibfk_2’ FOREIGN KEY (‘Curso_Codigo’) REFERENCES ‘test’.‘curso’ (‘Curso_Codigo’) ON DELETE NO ACTION ON UPDATE NO ACTION); ------------------------------------------------------- CREATE TABLE ‘test’�‘professorxturma’ ( ‘Professor_CPF’ INT(10) UNSIGNED NOT NULL, 27 ‘Turma_Numero’ INT(10) UNSIGNED NOT NULL, ‘Professorxturma_DataValidade’ DATE NULL DEFAULT NULL, PRIMARY KEY (‘Professor_CPF’, ‘Turma_Numero’), INDEX ‘ProfessorXTurma_FKIndex1’ (‘Professor_ CPF’ ASC) VISIBLE, INDEX ‘ProfessorXTurma_FKIndex2’ (‘Turma_ Numero’ ASC) VISIBLE, CONSTRAINT ‘professorxturma_ibfk_1’ FOREIGN KEY (‘Professor_CPF’) REFERENCES ‘test’.‘professor’ (‘Professor_CPF’) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT ‘professorxturma_ibfk_2’ FOREIGN KEY (‘Turma_Numero’) REFERENCES ‘test’.‘turma’ (‘Turma_Numero’) ON DELETE NO ACTION ON UPDATENO ACTION); ------------------------------------------------------- CREATE TABLE ‘test’�‘turmaxdisciplina’ ( ‘Turma_Numero’ INT(10) UNSIGNED NOT NULL, ‘Disciplina_Codigo’ INT(10) UNSIGNED NOT NULL, 28 ‘Turmaxdisciplina_DataValidade’ DATE NULL DEFAULT NULL, PRIMARY KEY (‘Turma_Numero’, ‘Disciplina_Codigo’), INDEX ‘TurmaXDisciplina_FKIndex1’ (‘Disciplina_ Codigo’ ASC) VISIBLE, INDEX ‘TurmaXDisciplina_FKIndex2’ (‘Turma_ Numero’ ASC) VISIBLE, CONSTRAINT ‘turmaxdisciplina_ibfk_1’ FOREIGN KEY (‘Disciplina_Codigo’) REFERENCES ‘test’.‘disciplina’ (‘Disciplina_Codigo’) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT ‘turmaxdisciplina_ibfk_2’ FOREIGN KEY (‘Turma_Numero’) REFERENCES ‘test’.‘turma’ (‘Turma_Numero’) ON DELETE NO ACTION ON UPDATE NO ACTION) 29 Modelo de dados após criação de tabelas no IDE Workbench MySQL Após a execução de todos os scripts e após a criação de todas as tabelas na ferramenta IDE Workbench MySQL, conforme ilustrado na figura 5, foi feito uma engenharia reversa por meio de uma opção no menu, conforme opção ilustrada na figura 6, e isso gerou o modelo lógico dentro da ferramenta Workbench, conforme ilustrado na figura 7, que está igual ao mo- delo lógico produzido na fermenta DBDesignerfork� Após a criação de todas as tabelas, o diretório no Workbench MySQL fica com a seguinte relação de tabelas: Figura 5: Criação das tabelas após execução do script. Fonte: Elaboração própria� 30 Figura 6: Engenharia reversa para gerar modelo lógico� Fonte: Elaboração própria� Para converter as tabelas criadas em um modelo lógico, por meio de uma opção do Workbench, clicar no seguinte caminho: Database/Reverse Engineer/ Connect to DBMS/Select Schemas� Após isso, será gerado o modelo lógico, conforme ilustrado na fi- gura 7. 31 Figura 7: Modelo lógico após engenharia reversa� Fonte: Elaboração própria� 32 MANIPULAÇÃO DE DADOS Para manipulação de dados é utilizada a linguagem DML (Data Manipulation Language), que é aplicada universalmente dentro de linguagens de aplicativos com compatibilidade de 100%. Os comandos DML estão dentro da linguagem SQL (Structured Query Language), servem para realizar a manipulação de dados, Consulta de dados (Select), Inclusão de dados (Insert), Exclusão de dados (Delete) e Alteração de dados (Update). A linguagem SQL é basicamente dividida em três tipos de comandos: SQL = DDL + DML + DCL: 1. DDL (definição de dados): Comandos: CREATE, DROP, ALTER 2. DML (manipulação de dados): Comandos: SELECT, INSERT, UPDATE e DELETE 3. DCL (controle de dados): Comandos: GRANT e REVOKE 4. Os comandos de manipulação de dados (DML) em SQL são representados por: ● INSERT: permite a inclusão de novas linhas nas tabelas� ● UPDATE: altera os valores de dados já cadastrados� ● DELETE: remove dados já cadastrados� 33 ● SELECT: usado para consultar o BD e retornar da- dos que satisfazem a determinada expressão em um comando� Inclusão de Dados Nos comandos DML da linguagem SQL (Structured Query Language) são executadas as inclusões (Insert) nas tabelas: Aluno, Disciplina e Alunoxdisciplina, conforme descrito anteriormente� Inclusão de Dados da tabela Aluno INSERT INTO ‘test’.‘aluno’ (‘Aluno_CPF’,‘Aluno_Nome’) VALUES (1,“João”); INSERT INTO ‘test’.‘aluno’ (‘Aluno_CPF’,‘Aluno_Nome’) VALUES (2,“Antonio”); INSERT INTO ‘test’.‘aluno’ (‘Aluno_CPF’,‘Aluno_Nome’) VALUES (3,“José”); Figura 8: Resultado das execuções na tabela aluno� Fonte: Elaboração própria� 34 Após a execução dos “Inserts” na tabela Aluno, o resultado com dados cadastrados na tabela Alunos fica conforme mostrado na figura 8� Inclusão de Dados da tabela disciplina INSERT INTO ‘test’.‘disciplina’ (‘Disciplina_Codigo’, ‘Disciplina_Nome’, ‘Disciplina_Creditos’) VALUES (1, “Engenharia”,10); INSERT INTO ‘test’.‘disciplina’ (‘Disciplina_Codigo’, ‘Disciplina_Nome’, ‘Disciplina_Creditos’) VALUES (2, “Direito”,30); INSERT INTO ‘test’.‘disciplina’ (‘Disciplina_Codigo’, ‘Disciplina_Nome’, ‘Disciplina_Creditos’) VALUES (3, “Matemática”,50); Figura 9: Resultado das execuções na tabela disciplina� Fonte: Elaboração própria� 35 Após a execução dos “Inserts” na tabela Disciplina, o resultado com dados cadastrados na tabela Disciplina fica conforme mostrado na figura 9� Inclusão de Dados da tabela Alunoxdisciplina INSERT INTO ‘test’.‘alunoxdisciplina’ (‘Aluno_CPF’, ‘Disciplina_Codigo’, ‘Alunoxdisciplina_DataValidade’) VALUES (1, 1, “2019/09/23”); INSERT INTO ‘test’.‘alunoxdisciplina’ (‘Aluno_CPF’, ‘Disciplina_Codigo’, ‘Alunoxdisciplina_DataValidade’) VALUES (1, 2, “2019/09/23”); INSERT INTO ‘test’.‘alunoxdisciplina’ (‘Aluno_CPF’, ‘Disciplina_Codigo’, ‘Alunoxdisciplina_DataValidade’) VALUES (1, 3, “2019/09/23”); INSERT INTO ‘test’.‘alunoxdisciplina’ (‘Aluno_CPF’, ‘Disciplina_Codigo’, ‘Alunoxdisciplina_DataValidade’) VALUES (2, 1, “2019/09/23”); INSERT INTO ‘test’.‘alunoxdisciplina’ (‘Aluno_CPF’, ‘Disciplina_Codigo’, ‘Alunoxdisciplina_DataValidade’) VALUES (2, 2, “2019/09/23”); INSERT INTO ‘test’.‘alunoxdisciplina’ (‘Aluno_CPF’, ‘Disciplina_Codigo’, ‘Alunoxdisciplina_DataValidade’) VALUES (2, 3, “2019/09/23”); 36 INSERT INTO ‘test’.‘alunoxdisciplina’ (‘Aluno_CPF’, ‘Disciplina_Codigo’, ‘Alunoxdisciplina_DataValidade’) VALUES (3, 2, “2019/09/23”); Figura 10: Resultado das execuções na tabela Alunoxdisciplina� Fonte: Elaboração própria� Após a execução dos “ Inserts” na tabela Alunoxdisciplina, o resultado com dados cadastrados na tabela Alunoxdisciplina fica conforme mostrado na figura 10� Devemos observar que, antes de fazer os “Inserts” dos dados chave da tabela Alunoxdisciplina, deve-se providenciar os “Inserts” dos conteúdos dos dados Aluno_CPF na tabela Aluno e Disciplina_Codigo na tabela Disciplina. Se isso não for feito, inviabiliza o insert da tabela Alunoxdisciplina. Abaixo vamos reexecutar um insert de conteúdo de dados na tabela Alunoxdisciplina. 37 INSERT INTO ‘test’.‘alunoxdisciplina’ (‘Aluno_CPF’, ‘Disciplina_Codigo’, ‘Alunoxdisciplina_DataValidade’) VALUES (1, 3, “2019/09/23”); Figura 11: Resultado na tentativa de reinserir dos dados na tabela Alunoxdisciplina� Fonte: Elaboração própria� Quando tentamos reinserir os mesmos dados já an- tes cadastrados na tabela Alunoxdisciplina, ocorre um erro “Duplicate entry for key PRIMARY”, por tratar- -se de dados da chave estrangeira (ocorrência de somente uma vez em toda tabela), sendo recusada a inclusão dos dados, conforme mostrado na figura 11. Exclusão de Dados Quando precisamos excluir uma linha (registro) da ta- bela, é necessário que os dados já existam na tabela� Vamos executar a instrução de exclusão de dados abaixo� Observamos que temos a cláusula Where para selecionar em qual linha devemos excluir� No Caso do Where comparamos os campos de chave primária, Aluno_CPF = 1 and Disciplina_Codigo = 3. DELETE FROM ‘test’.‘alunoxdisciplina’ WHERE Aluno_CPF = 1 and Disciplina_Codigo = 3; 38 Figura 12: Resultado após a exclusão de dados na tabela Alunoxdisciplina� Fonte: Elaboração própria� Quando tentamos fazer a mesma exclusão pela segunda vez, executamos a instrução abaixo� DELETE FROM ‘test’�‘alunoxdisciplina’ WHERE Aluno_ CPF = 1 and Disciplina_Codigo = 3; Figura 13: Resultado na tentativa de reexecutar a exclusão da linha na tabela Alunoxdisciplina� Fonte: Elaboração própria� Quando tentamos fazer a mesma exclusão pela se- gunda vez, ocorre de não concluir a exclusão, por- que os dados não existem mais, conforme figura 13, apresenta uma mensagem de “0 rows affected”� Alteração de Dados Quando precisamos alterar dados na tabela, é ne- cessário que os dados já existam nela� 39 Vamos executar a instrução de alteração de dados conforme abaixo� Observamos que temos a cláusula Where para selecionar em qual linha devemos alterar� No Caso do Where comparamos os campos de chave primária, ‘Aluno_CPF’ = 3 AND ‘Disciplina_Codigo’= 2. Ainda, a cláusula SET define qual a coluna deve ser alterada, e após o sinal de igual, coloca-se o conteúdo novo que deve ser alterado� UPDATE ‘test’.‘alunoxdisciplina’ SET ‘Alunoxdisciplina_ DataValidade’ = “2019/09/24” WHERE ‘Aluno_CPF’ = 3 AND ‘Disciplina_Codigo’ = 2; Figura 14: Resultado após a Alteração de dados na tabela Alunoxdisciplina� Fonte: Elaboração própria� Quando tentamos fazer a mesma alteração pela se- gunda vez, executamos a instrução abaixo� UPDATE ‘test’.‘alunoxdisciplina’ SET ‘Alunoxdisciplina_ DataValidade’ = “2019/09/24” WHERE ‘Aluno_CPF’ = 3 AND ‘Disciplina_Codigo’ = 4; 40 Figura 15: Resultado após a alteração de dados inexistentes na tabela Alunoxdisciplina� Fonte: Elaboração própria� Quando tentamos fazer a alteração de dados inexis- tente, ocorre de não concluir a alteração, porque os dados não existem, conforme figura 15, apresenta uma mensagem de “0 rows affected”� Consulta de Dados Um dos comandos mais utilizados é á consulta de dados, em que podemos ter inúmeras variações na forma de consulta; a mais simples é o que consta abaixo, pois o “*” é mostrado em todas as colunas da tabela que se deseja consultar� SELECT * FROM ‘test’�‘alunoxdisciplina’; Figura 16: Resultado após a consulta da tabela Alunoxdisciplina� Fonte: Elaboração própria� 41 O resultado do select é mostrar os conteúdos de to- das as colunas da tabela Alunoxdisciplina, conforme ilustrado na figura 15. Podemos selecionar a consulta conforme um critério; basta mencionarmos uma coluna com o conteúdo que desejamos� A coluna pode ser qualquer uma da tabela; se forem colunas da chave primária, o pro- cesso fica rápido, se forem colunas que não sejam chave primária, o processamento ficará demorado, principalmente se o volume de dados for além de 1�000 linhas, o que pode comprometer o tempo de resposta do resultado� Para realizarmos uma experi- ência, executamos a instrução abaixo, na qual sele- cionamos todas as linhas da coluna Aluno_CPF = 1. SELECT * FROM ‘test’�‘alunoxdisciplina’ where Aluno_CPF = 1; Figura 17: Resultado após a consulta da tabela Alunoxdisciplina com seleção de coluna� Fonte: Elaboração própria� O resultado do select é mostrar os conteú- dos de todas as colunas da tabela (*) da tabela Alunoxdisciplina, somente quando a coluna Aluno_ CPF = 1, conforme ilustrado na figura 16. 42 Pode ocorrer quando executamos uma seleção de a comparação da coluna não encontrar nenhuma linha que satisfaça a condição� Para essa situação, executamos a instrução a seguir� SELECT * FROM ‘test’.‘alunoxdisciplina’ where Aluno_ CPF = 4; Figura 18: Resultado após a consulta inexistente na tabela Alunoxdisciplina com seleção de coluna� Fonte: Elaboração própria� Uma vez que não há nenhum registro que atenda a condição da seleção, o resultado não mostra nenhum registro, conforme ilustrado na figura 18. Podcast 2 43 https://famonline.instructure.com/files/168937/download?download_frd=1 CONSIDERAÇÕES FINAIS O aprendizado contido neste módulo teve como ob- jetivo conscientizar você, estudante, sobre todo o fluxo que envolve o trabalho com banco de dados. Demonstramos, na prática, a utilidade do modelo de dados para gerar o script de criação de tabela� Essa conversão foi demonstrada como fazemos em uma ferramenta case de modelagem; no caso, a ferramen- ta de gerenciador de Banco de Dados, IDE Workbench do MySQL, em que exercitamos a criação de tabelas, e onde verificamos o resultado da criação. Estudamos os conceitos de chaves primárias, de chaves estrangeiras, relacionamentos entre tabelas, restrições de operações com manipulação de con- teúdos de dados, além das diferenças entre Modelo Lógico e Modelo Físico� Esperamos que você tenha aproveitado! 44 • Consulta de Dados (Select). • Exclusão de dados (Delete). • Alteração de dados (Update). • Inclusão de dados (Insert). • Manipulação de dados com comandos do SQL. • Execução de comandos DDL (Data Definition Language). • Construção de índices. • Linhas e colunas. • Tabelas. • Schema. O aprendizado de bancos se apoia na prática da manutenção de: • Dados que atendam os requisitos do usuário e as funções de inteligência do sistema. • Encaminhamento de solução intelectual. • Entendimento dos requisitos dos usuários. O Modelo Lógico abrange entender a regra de negócio, que envolve: • Formas de relacionamentos entre tabelas. • Formulação dos índices. • Composição de tabelas. A partir do Diagrama de Entidade e Relacionamento (DER), planeja-se: Após a criação do banco de dados e das tabelas, são executados comandos do SQL, na linguagem DDL (Data Definition Language) para praticar a inclusão, alteração, exclusão e consultas de dados. Banco de Dados DE ATH YST SÍNTESE Referências Bibliográficas & Consultadas ASCENCIO, A� F� G�; ARAÚJO, G� S� Estruturas de dados: algoritmos, análise da complexidade e implementações em JAVA e C/C++� São Paulo: Pearson Prentice Hall, 2010� [Biblioteca Virtual] BARBOZA, F� F� M� et al� Modelagem e desenvol- vimento de banco de dados. Porto Alegre: Sagah, 2018� [Minha Biblioteca] BOOCH, G�; RUMBAUGH, J�; JACOBSON, I� UML: guia do usuário. São Paulo: Campus, 2000� CALSARA, A�; MACHADO, C� A� F�; REINEHR, S� S�; BURNETT, R� C� Aderência do RUP à norma NBR ISO/IEC 12207. Dezembro/2002� Disponível em: https://docplayer.com.br/18795196-Aderencia-do- -rup-a-norma-nbr-iso-iec-12207.html� Acesso em: 03 out� 2019� DATE, C� J� Introdução a sistemas de bancos de dados. 7. ed. Rio Janeiro: Campus, 2000. DBDesignerfork� Software livre para modelagem de Dados� Disponível em: https://db-designer-fork. soft112.com. Acesso em: 05 set� 2019� ELMASRI, R�; NAVATHE, S� Sistemas de banco de dados. 4. ed. São Paulo: Pearson, 2005� [Biblioteca Virtual] GANE, C� Análise estruturada de sistemas. 7. ed. Rio Janeiro: LTC, 1993� HAY, D. C. Princípios de modelagem de dados. São Paulo: Makron, 1999� HEUSER, C� A� Projeto de banco de dados� 6�ed� Porto Alegre: Bookman, 2009� [Minha Biblioteca] KRUCHTEN, P. Introdução ao RUP Rational Unified Process. 2. ed. Rio de Janeiro: Ciência Moderna, 2003� MEDEIROS, L� F� Banco de dados: princípios e práti- ca� Curitiba: InterSaberes, 2013� [Biblioteca Virtual] PRESSMAN, R� S� Engenharia de software. 5. ed. São Paulo: Makron Books, 2002. PUGA, S�; FRANÇA, E�; GOYA, M� Banco de dados: implementação em SQL, PL/SQL e Oracle 11g� São Paulo: Pearson Education do Brasil, 2013� [Biblioteca Virtual] https://db-designer-fork.soft112.com/ https://db-designer-fork.soft112.com/ RAMAKRISHNAN, R�; GEHRKE, J� Sistemas de gerenciamento de banco de dados� 3� ed� Porto Alegre: AMGH, 2011� [Minha Biblioteca] RESENDE, D� A� Engenharia de software e sistemas de informação. 2. ed. Rio Janeiro: Brasport, 2003� SETZER, V� W� Banco de dados. 3� ed� Rio Janeiro: Edgard Blücher, 1989� SOMMERVILLE, I� Engenharia de software. 6� ed� São Paulo: Pearson, 1995� TELES, V� M� Extreme Programming. São Paulo: Novatec, 2004� WOLFGANG, P� A� T�; KOFFMAN, E� B� Objetos, abs- tração, estruturas de dados e projeto usando C++� Rio de Janeiro: LTC, 2008� [Minha Biblioteca] _GoBack Introdução Utilização de comandos SQL para criação e manutenção de Dados Criação de Bancos de Dados Criação de Schema Exclusão de Banco de Dados ou Schema Criação de colunas Alteração de colunas Exclusão de colunas Incluir uma coluna Criação de Tabelas Concessão de acesso a tabelas – privilégios Criação de chave estrangeira Execução de Scripts de um modelo de dados para Criação de Tabelas Modelo de dados após criação de tabelas no IDE Workbench MySQL Manipulação de Dados Inclusão de Dados Inclusão de Dados da tabela Aluno Inclusão de Dados da tabela disciplina Inclusão de Dados da tabela Alunoxdisciplina Exclusão de Dados Alteração de Dados Consulta de Dados Considerações finais Síntese
Compartilhar