Baixe o app para aproveitar ainda mais
Prévia do material em texto
Curso Preparatório Certificação MTA Database Fundamentals Olá! Sou Luiz Santana seu Professor. Você pode me encontrar no LinkeIn através do link abaixo: https://www.linkedin.com/in/luizfsantana/ 2 Luiz Santana ▪ Certificações Microsoft ▪ MCP: Microsoft Certified Professional ▪ MTA: Microsoft Technology Associate - Database Fundamentals ▪ MCE: Microsoft Certified Educator ▪ CMIE: Certified Microsoft Innovative Educator ▪ Microsoft Certified: Azure Fundamentals ▪ MCT: Microsoft Certified Trainer 2020-2021 ▪ MCSA SQL 2016 Database Development: 70761+70762 ▪ 70-461: Querying Microsoft SQL Server 2012/2014 ▪ Microsoft Certified: Azure Data Fundamentals ▪ Microsoft Innovative Educator Expert 2020-2021 ▪ Certificações Scrum ▪ SFC: Scrum Fundamentals Certified ▪ SFPC: Scrum Foundation Professional Certificate 33 ▪ Formação ▫ ADS ▫ Pós-graduação em BD com ênfase em BI ▪ TI ▫ 8 Anos da área de Analytics ▫ Analista de Sistemas, DBA, Programador T-SQL, Engenheiro de Dados ▫ Professor de BD, NoSQL, T-SQL ▫ Instrutor de Certificação Microsoft ▪ Blog ▫ Consulta BD ▪ Empregos Atuais ▫ Engenheiro de dados Sênior ▫ Professor de banco de dados Minhas Bagdes 44 Dicas de estudo – Rotina de estudo ▪ Gerenciar tempo de estudo diário ▫ Tempo: 30 minutos a 1 hora por dia. ▫ Praticar é essencial para fixação do conteúdo. ▪ Perguntar sempre que tiver dúvida ▪ Trazer duvidas do trabalho ou sobre os assuntos abordados para a aula. 55 ▪ Sites / blogs ▫ https://www.w3schools.com/sql/def ault.asp ▫ https://consultabd.wordpress.com/ ▫ https://www.dirceuresende.com/ ▫ https://blogfabiano.com/ ▫ https://www.fabriciolima.net/ ▫ https://docs.microsoft.com/pt- br/learn/ Dicas de estudo - Livros ▪ Microsoft Sql Server 2012 – Passo A Passo – Desenvolvedor ▪ SQL Server 2014 Para Iniciantes ▪ Microsoft – SQL Server 2014 Express – Guia Prático e Interativo 66 ▪ Banco de Dados: princípios e prática ▪ T- SQL Com Microsoft SQL Server 2012 Express na Prática ▪ Microsoft Sql Server 2008: Fundamentos Em T-sql Dicas de estudo - Aplicativos ▪ Manual SQL: https://play.google.com/store/apps/details?id =com.felipe.manualsql ▪ Learn SQL: https://play.google.com/store/apps/details?id =com.sololearn.sql 77 ▪ Duolingo https://play.google.com/store/apps/details?id =com.duolingo&hl=pt_BR ▪ Trello https://play.google.com/store/apps/details?id =com.trello “ Tente uma, duas, três vezes e se possível tente a quarta, a quinta e quantas vezes for necessário. Só não desista nas primeiras tentativas, a persistência é amiga da conquista. Se você quer chegar a onde a maioria não chega, faça o que a maioria não faz. Bill Gates 8 Conteúdo do Curso Conteúdo do curso ▪ Conceitos de bancos de dados ▪ Modelos de dados ▪ Modelo Relacional ▪ Entidade-Relacionamento ▪ Entidades ▪ Atributos ▪ Cardinalidades ▪ Normalização ▪ Instalando e acessando o SQL Server 1010 Conteúdo do curso ▪ Entender os tipos de dados ▪ SQL ▪ Comandos SQL (DDL,DML,DQL,DTL,DQL) ▪ Índices e Schemas ▪ Desenvolvimento de consultas ▪ Funções SQL (texto, número, agregação e data) ▪ Programação T-SQL (Views, Procedures, Functions e Triggers) ▪ Conceitos básicos de administração de banco de dados ▪ Conceitos básicos de segurança de dados ▪ Agendando a prova ▪ Dicas da prova ▪ Simulados da prova 1111 Certificação MTA 12 ▪ Microsoft Technology Associate ▪ É um excelente ponto de partida para quem esta iniciando no ramo da tecnologia ou quer se certificar. As certificações MTA abrangem conceitos teóricos e técnicos fundamentais de uma determinada tecnologia, avaliando melhorar sua credibilidade técnica. Observação: Os exames MTA não o qualificam o candidato a receber o título MCP e também não são pré- requisito para as certificações MCSA ou MCSE. MTA Database Fundamentals 13 ▪ Areas: ▫ Banco de dados ▫ Desenvolvimento ▫ Infraestrutura de TI ▪ Outras provas no link abaixo: ▪ https://www.microsoft.com/pt- br/learning/mta-summary- certification.aspx ▪ Teste 98-364 (entre 30 e 40 questões) ▪ Database Fundamentals ▪ Avalia os conhecimentos básicos e habilidades com bancos de dados, conceitos de modelagem, linguagem SQL e sobre o SGBD Microsoft SQL Server. ▪ Idiomas: Inglês, chinês (simplificado), chinês (tradicional), francês, alemão, italiano, japonês, coreano, português (Brasil), russo, espanhol, espanhol (América Latina) ▪ Preço: 77 dólares = 350 reais ▫ taxas de cartão (internacional) ▫ cotação do dólar MTA Database Fundamentals 14 ▪ Você é estudante ou instrutor? ▫ Inscreva-se para seu exame na Certiport. ▪ Não é mais estudante, mas tem interesse em tecnologia? ▫ Registre-se com a Pearson VUE ▪ A prova pode ser feita online ou presencial (verificar se existe um centro de aplicação no seu estado) ▪ Site Microsoft sobre a certificação ▫ https://www.microsoft.com/pt- BR/learning/exam-98-364.aspx ▪ Aprovação a partir de 70% de acertos. ▪ Habilidades medidas ▪ Este exame mede sua habilidade em executar as tarefas técnicas listadas ao lado. ▪ As porcentagens indicam o peso relativo de cada tópico principal no exame. Quanto mais alta a porcentagem, mais perguntas você verá nessa área. ▪ As perguntas podem testar, mas não estarão limitadas aos tópicos descritos. MTA Database Fundamentals 15 ▪ O que vai ser contemplado na prova ▫ Compreendendo os principais conceitos de bancos de dados (20- 25%) ▫ Criar objetos do banco de dados (20- 25%) ▫ Manipular dados (25-30%) ▫ Entender armazenamento de dados (15-20%) ▫ Administrar um Banco de dados (10- 15%) ▪ Compreendendo os principais conceitos de bancos de dados (20-25%) ▫ Compreender como os dados são armazenados em tabelas ▫ Compreender o que é uma tabela e como ela se relaciona com os dados que serão armazenados no banco de dados, colunas/campos, linhas/registros ▫ Compreender os conceitos de banco de dados relacional ▫ Compreender o que é um banco de dados relacional, a necessidade de sistemas de gestão de banco de dados relacional (RDBMS ou SGBDs) e como as relações são estabelecidas ▫ Compreender a linguagem de manipulação de dados (DML) ▫ Compreender o que é DML e qual a sua função em bancos de dados ▫ Compreender a linguagem de definição de dados (DDL) ▫ Compreender como o T-SQL pode ser usado para criar objetos de bancos de dados, como tabelas e exibições MTA Database Fundamentals 16 ▪ Criar objetos do banco de dados (20-25%) ▫ Escolher tipos de dados ▫ Compreender o que são tipos de dados, por que eles são importantes e como eles afetam os requerimentos de armazenamento ▫ Compreender tabelas e como criá-las ▫ Propósito das tabelas; criar tabelas em um banco de dados usando a sintaxe ANSI SQL apropriada ▫ Criar exibições ▫ Compreender como usar as exibições e como criar exibições usando T-SQL ou um designer gráfico ▫ Criar criar procedimentos e funções armazenados ▫ Selecionar, inserir, atualizar ou excluir dados MTA Database Fundamentals 17 ▪ Manipular dados (25-30%) ▫ Selecionar dados ▫ Utilizar consultas SELECT para extrair dados de uma tabela, extrair dados usando junções, combinar conjuntos de resultados usando UNION e INTERSECT ▫ Inserir dados ▫ Compreender como os dados são inseridos em um banco de dados, como usar afirmações INSERT ▫ Atualizar dados ▫ Compreender como os dados são atualizados em um banco de dados e como escrever os dados atualizados usando as afirmações UPDATE apropriadas, atualizar usando uma tabela ▫ Excluir tabela ▫ Excluir dados de uma ou várias tabelas, garantir a integridade dos dados e integridade referencial usando transações MTA Database Fundamentals 18 ▪ Entender armazenamento de dados (15-20%) ▫ Entender a normalização ▫ Entender as razões para normalização, os cinco níveis mais comuns de normalização, como normalizar um banco de dados para a terceira forma normal ▫ Entender chaves primárias, estrangeiras e compostas ▫ Entender a razão para chaves em um banco de dados, escolher as chaves primárias apropriadas, selecionaro tipo de dado apropriado para as chaves, selecionar os campos apropriados para chaves compostas, entender a relação entre chaves primárias e estrangeiras ▫ Entender índices ▫ Entender índices clusterizados e não clusterizados e seus fins em um banco de dados MTA Database Fundamentals 19 ▪ Administrar um Banco de dados (10-15%) ▫ Entender os conceitos de segurança de banco de dados ▫ Entender a necessidade de proteger um banco de dados, quais objetos podem ser protegidos, quais objetos devem ser protegidos, contas de usuários e funções ▫ Entender os backups e restauração de bancos de dados ▫ Entender os diversos tipos de backup, como completo e incremental, a importância dos backups, como restaurar um banco de dados MTA Database Fundamentals 20 Conceitos de banco de dados 21 Banco de Dados: é um conjunto organizado de dados, tipicamente armazenado em formato eletrônico. Ele nos permite dar entrada, organizar e recuperar dados rapidamente. Bancos de dados tradicionais são organizados por campos, registros e arquivos. Simplificando, um banco de dados é um local de armazenamento de diversas informações. Banco de dados Ex.: Para entender melhor o que é um banco de dados, considere lista de contatos do celular como um exemplo simples. Dentro da lista telefônica, você teria uma lista de registros - cada registro um contendo um nome, número de telefone, tipo de contato e etc. Estas informações únicas (nome, tipo de contato, número de telefone). Tudo isso é armazenado em formato de um arquivo dentro do celular. 22 Existem três tipos de bancos de dados com os quais você deve estar familiarizado para fazer a escolha apropriada ao desenvolver suas próprias tabelas de banco de dados: • Bancos de dados de tipo plano (flat-type databases) • Bancos de dados hierárquicos (Hierarchical databases) • Bancos de dados relacionais (Relational databases) Cada tipo de banco de dados possui seus próprios recursos de design importantes. Tipos de banco de dados 23 Entendendo Bancos de dados de tipo plano Bancos de dados de tipo plano são simplistas em design. Eles são mais comumente usados em formatos de texto simples. Como o propósito deles é manter um registro por linha, eles tornam o acesso, o desempenho e as consultas muito rápidos. Um exemplo desse tipo de banco de dados seria o que você encontraria em um arquivo .txt, .csv ou .ini. Flat-type databases 24 Bancos de dados desse tipo são considerados “planos” porque são tabelas bidimensionais consistindo de linhas e colunas. Cada coluna pode ser referida como um campo (como o sobrenome de uma pessoa ou o número de identificação de um produto) e cada linha pode ser referida como um registro (como as informações de uma pessoa ou de um produto). Um banco de dados hierárquico é semelhante a uma estrutura em árvore (como uma árvore genealógica). Nesse banco de dados, cada tabela “pai” pode ter vários filhos, mas cada filho pode ter apenas um pai. Se várias dessas tabelas estiverem vinculadas, as tabelas do banco de dados começarão a formar uma estrutura de árvore na qual cada pai pode ter múltiplas tabelas filho e cada tabela filho, por sua vez, podem ter seus próprios filhos, mas nenhuma tabela filho única terá mais de um pai. Hierarchical databases 25 Um banco de dados relacional é semelhante a um banco de dados hierárquico. Diferentemente dos bancos de dados hierárquicos, no banco relacional, uma tabela em um banco de dados relacional pode ter vários pais. Um exemplo de banco de dados relacional é mostrado na imagem ao lado. A primeira tabela pai mostra os vendedores dentro de uma empresa e a segunda tabela pai lista quais modelos de produtos são vendidos pela empresa. Enquanto isso, a tabela filho lista os clientes que compraram modelos da empresa; essa tabela filho é vinculada à primeira tabela pai pelo SalesNum e à segunda tabela pai pelo Model. Essa tabalea mostra os produtos que o cliente comprou e o vendedor que realizou a venda. Relational databases 26 Modelo de Dados ▪ Um modelo de dados mostra a estrutura lógica de um banco de dados, incluindo as relações e restrições que determinam como os dados podem ser armazenados e acessados. 2727 Modelo Relacional 28 O modelo relacional é um modelo de dados representativos que se baseia no princípio em que todos os dados estão guardados em tabelas. Toda sua definição e teoria é baseada na lógica de predicados e na teoria dos conjuntos. O conceito foi criado por Edgar Frank Codd em 1970, sendo descrito no artigo "Modelo relacional de dados para grandes bancos de dados compartilhados". Na verdade, o modelo relacional foi o primeiro modelo de dados descrito teoricamente. Visão histórica do modelo de dados Relacional O modelo relacional foi mantido e aprimorado por Chris Date e Hugh Darwen como um modelo geral de dados. No Terceiro Manifesto (1995) eles mostraram como o modelo relacional pode ser estendido com características de orientação a objeto sem comprometer os seus princípios fundamentais. 29 Este modelo se preocupa com a descrição das tabelas, linhas e colunas. ▪ Tabela – Local onde serão armazenados todos os dados informados ao banco. Dentro da tabela estão contidos as linhas e as colunas. As tabelas se relacionam entre si, pode haver dezenas de relações dependendo de como o banco foi modelado. Estrutura do modelo de dados Relacional ▪ Coluna - Também chamado de atributo, é a característica única da tabela. Cada coluna deve ter um tipo de dado que será o formato do dado ela armazenará as informações (texto, valor numérico, imagem, etc.). ▪ Linha - São os registros que foram armazenados na tabela. Também pode ser chamada de tupla ou dado. 30 Estrutura modelo de dados relacional 31 32 Modelo Entidade - Relacionamento ▪ Foi definido por Peter Chen em 1976; ▪ É chamado de “meta-modelo”; ▪ Permite obter resultados e esquemas puramente conceituais sobre o negócio a ser modelado; ▪ Permite retratar a realidade compreendendo o negócio através de seus dados. Entidade Relacionamento ▪ Os objetos são classificados em dois grupos: ▫ Entidades – Representadas por um retângulo ▫ Relacionamentos – Representados por um losango 33 Estrutura modelo de ER 34 35 Entidade ▪ Entidade: Representação abstrata dos objetos do mundo real ▫ São objetos que existem no negócio; ▫ Descrevem o negócio em si; ▫ Possui um significado próprio; ▫ É a representação de uma Classe de dados do negócio; Entidade 36 ▫ Conjunto de objetos sobre os quais se deseja manter informações no banco de dados. ▫ Sua representação gráfica é feita através de um retângulo com seu nome no interior. O nome deve ficar no singular. ▫ Uma entidade pode ser um objeto concreto, um fato, ou ainda um evento que desejamos registrar e que possui características próprias. Ex.: Pessoa, Funcionário, Venda, Lista de produtos. Entidades 37 ▪ Sua existência independe da entidade relacionada. Entidade Fortes 38 ▪ Sua existência depende da entidade relacionada. Entidade Fraca 39 ▪ Quando uma entidade pode ser subdividida ou agrupada em virtude da proximidade de suas características (atributos ou colunas). ▪ Existem dois tipos de Generalização/Especialização: ▪ Total (representada pela letra t) ▪ Parcial (representada pela letra p). Generalização / Especialização 40 ▪ Generalização: Quando duas entidades podem ser agrupadas, em virtude de suas semelhanças (atributos em comum), gerando uma superentidade. ▪ Ex.: As duas entidades tem em comum os atributos email, endereco, telefone,midias sociais e etc… Generalização 41 Especialização ▪ Especialização: Quando uma entidade pode ser subdividida, herdando atributos da entidade genérica e, juntamente com seus atributos específicos, compor a sua lista de atributos. 42 ▪ Relacionamentos NxN entre duas ou mais tabelas pode ser chamado informalmente de Tabela associativa. ▪ Uma tabela associativapode conter duas ou mais tabelas referenciando os atributos identificadores de cada tabela relacionada. ▪ Sua representação é feita a partir de um retângulo colocado ao redor do losango do relacionamento. Entidade Associativa 43 44 Relacionamento Relacionamento: É o fato que liga dois objetos (entidades) no mundo real. Exemplos: 1. As Pessoas Moram em Apartamentos; 2. Os Apartamentos Formam Condomínios; 3. Os Condomínios Localizam-se em logradouros (Ruas ou Avenidas); 4. Os logradouros Estão em uma Cidade. Relacionamento ▪ Através de um losango com o verbo no interior e arestas que ligam as entidades relacionadas. 45 ▪ Relacionamento Binário ▪ São aqueles que envolvem duas ocorrências entidades. Classificação dos Relacionamentos 46 ▪ Relacionamento Ternário. ▫ São aqueles que envolvem três ocorrências de entidades. ▫ Alguns relacionamentos ternários podem ser convertidos em dois relacionamentos binários. ▫ Relacionamento ternário ou de maior grau não são frequentes. ▫ Relacionamento Ternário PODE estar ocultando uma ENTIDADE não percebida, concentrando sobre si relacionamento com os demais. O fato (ou ação) que liga as três entidades tende a tornar-se uma tabela contendo referência das tabelas relacionadas. Esta tabela pode ser chamada de entidade associativa. Classificação dos Relacionamentos 47 ▪ Denominamos de auto-relacionamento quando uma entidade está associada a uma ou mais ocorrências da mesma entidade, ou seja, quando a entidade se relaciona com ela mesma. ▪ Ex: Funcionário (gerente) chefia Funcionário (analista). Auto relacionamento 48 49 Atributos São propriedades da entidade. Os atributos podem ser classificados como: ▪ Simples; ▪ Composto; ▪ Opcional; ▪ Multivalorado; ▪ Determinante; Atributos 67 ▪ São atributos que podem assumir apenas um valor por ocorrência. ▪ Também são chamados de Monovalorados. ▪ Ex.: Atributo nome somente receberá um valor por Cliente. Atributos Simples 51 ▪ São atributos que podem ser divididos em outros atributos. O atributo origem contém atributos embutidos nele, ou seja, este atributo é composto por mais informações. Na modelagem ele é decomposto em atributos da própria entidade. Atributos Compostos 52 ▪ São atributos que não possuem a obrigatoriedade de ocorrência (preenchimento). ▪ Ex.: Para ser um funcionário não é obrigatório o preenchimento da CNH, supondo que existam funcionários sem carteira de habilitação. Atributos Opcionais 53 ▪ São atributos que podem assumir mais de um valor por ocorrência. ▪ Quando um atributo começa a ter características de uma entidade, ou seja, começa a ter atributos que o caracterizam como tabela. ▪ Os atributos multivalorados são convertidos em uma nova entidade e contém um atributo que faz relação com sua tabela de origem. Atributos Multivalorados 54 ▪ É um atributo ou conjunto de atributos que têm a capacidade de identificar unicamente uma ocorrência em uma entidade. ▪ Também são conhecidos como identificadores ou chaves primárias. ▪ Os atributos determinantes ainda podem ser classificados como simples ou compostos. Atributos Determinantes 55 56 Cardinalidade É o número de ocorrências (vezes) que uma entidade A que está associado com ocorrências de uma entidade B. ▪ Há três tipos: ▫ Um-para-Um; ▫ Um-para-Muitos ou Muitos-para-Um; ▫ Muitos-para-Muitos; Cardinalidade ▪ Cardinalidade Máxima: Número máximo de vezes que uma entidade A pode ocorrer em B. Podendo assumir o valor de 1 ou N. ▪ Cardinalidade Mínima: Número mínimo de vezes que uma entidade A pode ocorrer em B. Podendo assumir o valor de 0 ou 1. ▪ As cardinalidades são informadas inversamente. 57 Cardinalidade 58 ▪ Relacionamento de Um-para-Um (1 X 1): Cada elemento de uma entidade A relaciona-se com um e somente um elemento da entidade B e vice-versa. ▪ Nesse tipo de relacionamento a cardinalidade mínima influência na modelagem. Cardinalidade ▪ Relacionamento de Um-para-Muitos (1 X N) ou Muitos-para-Um (N X 1): Um elemento de uma entidade A pode se relacionar com mais de um elemento da entidade B e B somente com 1 de A. ▫ OBS: Durante este tipo de normalização é definido em que tabela receberá a chave estrangeira (referência de chave primária de outra tabela). Normalmente na tabela que recebe a cardinalidade máxima N. 59 ▪ Relacionamento de Muitos-para-Muitos (N x N): Vários elementos de uma entidade A podem se relacionar com vários elementos de uma entidade B e vice-versa. ▫ Nesse tipo de relacionamento uma nova entidade é criada para armazenar dados das entidades que estavam se relacionando inicialmente. Cardinalidade ▪ Na nova entidade criada deve haver as chaves primárias das tabelas de origem, ou seja, criando uma associação entres as entidades originais. Na nova tabela elas podem ser tanto chaves primárias quanto chaves estrangeiras. ▪ Boa prática: criar na nova tabela um campo para tornar-se chave primária, deixando os campos de referência apenas como chave estrangeira. 60 Cardinalidade 61 Relacionamento 1 x 1 Cardinalidade 62 Relacionamento 1 X N ou N X 1 Cardinalidade 63 Relacionamento N X N 1. Perguntar quantas vezes a entidade A se relaciona com a entidade B e vice versa. 2. Em todo relacionamento 1 x N ou N x 1, a chave estrangeira sempre é adicionada na entidade que possuir cardinalidade máxima N. Cardinalidade - Dicas 3. No relacionamento N x N as chaves estrangeiras caem na nova entidade criada. Essas chaves estrangeiras podem ser ou não chaves primarias nessa nova entidade. 4. No relacionamento 1 x 1 as entidades podem ser unificadas em uma só ou virar um relacionamento parecido com o relacionamento 1 x N. Verificar as cardinalidades mínimas elas indicaram como ficará a tabela. 64 Cardinalidade em Relacionamentos Binários 58 Cardinalidade em Relacionamentos Ternários 59 67 68 Normalização Processo de eliminação de dados redundantes de determinado domínio (ou negocio), para obter maior eficiência, confiabilidade e a integridade dos dados. A normalização por definição, é o processo de organizar dados para reduzir a redundância, dividindo um banco de dados em duas ou mais tabelas e em seguida definindo as relações entre elas. O objetivo dessa operação é isolar os dados de modo que adições, exclusões e modificações que ocorrem em cada campo possam ser feitas em uma tabela e depois propagadas em todo o restante do banco de dados usando esses relacionamentos definidos. Normalização Na normalização aplica-se as formas normais (ou seja regras de normalização), para as melhor modelagem do domínio. Existe atualmente 7 formas normais: 1ª FN, 2ª FN, 3ª FN, Boyce-Codd FN, 4ª FN, 5ª FN, 6ª FN ou chave-dominio. Modelando as entidades até a 3ª FN, pode-se obter um banco bem normalizado e consistente. 69 1. Tradução inicial de entidades e respectivos atributos 2. Tradução de relacionamentos e respectivos atributos 3. Tradução de generalizações e especializações Passos para Transformação 70 ▫ Cada entidade é traduzida para uma tabela. ▫ Cada atributo da entidade constituirá um campo da tabela criada. ▫ O atributo identificador da cada entidade corresponde à chave primária da tabela criada. Passo 1: Tradução inicial de entidades e respectivos atributos 71 ▪ Atributo multivalorado ▫ Cria-se uma tabela para cada atributo multivalorado. ▫ A tabela contém um campo para cada atributo multivalorado ▫ A tabela deve conter um campo correspondente à chave primária da entidade a que o atributo pertencia (chave estrangeira). Passo 1: Tradução inicial de entidades e respectivos atributos 72 ▪ Atributo composto ▫ Incluir cada atributo como campo da própria tabela. Transformação do modelo conceitual em fisico 73 ▪ Relacionamento ▫ A regra de tradução a ser adotada é determinada pelas cardinalidades mínima e máxima das entidades que participam do relacionamento. ▪ Tipos de tradução: ▫ Tabela associativa▫ Atributo referência em outra entidade ▫ Fusão de tabelas de entidades Passo 2: Tradução de relacionamentos e respectivos atributos 74 ▪ Tabela Associativa ▫ Quando as entidades associadas tem cardinalidade NxN. ▫ O relacionamento é traduzido em uma tabela (tabela associativa). ▫ A tabela criada deve conter os atributos identificadores das entidades relacionadas (tabelas de origem) e os atributos do relacionamento. ▪ Tabela Associativa. Perceba que após a nova tabela ser criada, as cardinalidades com as tabelas de origem passam a ser 1 x N e N x 1. Passo 2: Tradução de relacionamentos e respectivos atributos 75 ▪ Atributo referência em outra entidade. ▪ Quando uma das entidades associadas tem cardinalidade 1xN ou Nx1. ▫ Ocorre a inclusão da chave primária da tabela que possui cardinalidade máxima 1 na tabela que possui cardinalidade máxima N. Assim fazendo referencia a sua tabela de origem. ▫ Esse atributo adicionado na outra tabela é denominado chave estrangeira. Passo 2: Tradução de relacionamentos e respectivos atributos 76 ▪ Fusão de tabelas de entidades ▪ Quando o relacionamento é do tipo (1,1) x (1,1), ou seja, máximas e mínimas iguais, as entidades envolvidas no relacionamento são fundidas em uma única tabela. ▪ Quando as cardinalidades são 1 x 1 porém as cardinalidades mínimas são diferentes (ex: 1,1 x 0,1), uma chave estrangeira é adicionada na entidade fraca. Como no exemplo ao lado. Passo 2: Tradução de relacionamentos e respectivos atributos 77 ▪ Relacionamento Ternário ▪ O relacionamento é transformado em uma tabela, relacionada às outras tabelas através de relacionamentos binários. ▪ São empregadas as regras de transformação aplicadas a relacionamentos binários. ▪ O fato ou ação que une as três entidades, tende a tornar-se uma tabela de ligação (ou associação), contendo atributos chaves das demais. Passo 2: Tradução de relacionamentos e respectivos atributos 78 Passo 2: Tradução de relacionamentos e respectivos atributos 79 ▪ Tipos de tradução 1. Criar uma única tabela para toda hierarquia de generalização/especialização. 2. Criar uma tabela para a entidade genérica e uma tabela para cada entidade especializada. 3. Criar uma tabela para cada entidade especializada Passo 3: Tradução de generalizações e especializações 80 ▪ Criar uma única tabela para toda hierarquia de entidades generalização/especialização Passo 3: Tradução de generalizações e especializações 81 ▪ Criar uma tabela para a entidade genérica e uma tabela para cada entidade especializada Passo 3: Tradução de generalizações e especializações 82 ▪ Criar uma tabela para cada entidade especializada, colocando em cada tabela os atributos da entidade genérica. Passo 3: Tradução de generalizações e especializações 83 Formas Normais 1ª FN – Tem como objetivo: 1. Identificar as entidades; 2. Adicionar atributos identificadores; 3. Eliminar conjunto de dados repetidos: 1. Eliminar dos atributos multivalorado 2. Atributos compostos. 4. Não possuir tabelas aninhadas. 2ª FN – Tem como objetivo: 1. Encontrar-se na 1FN. 2. Verificar a existência de dependência Funcional 1. Dependência funcional total. 2. Dependência funcional parcial. 3ª FN – Tem como objetivo: 1. Encontrar-se na 2FN. 2. Eliminar dependências transitiva. 84 ▪ A tela ao lado contém os registros dos alunos e os cursos em que ele se matriculou. ▪ VL. TABELA é o valor padrão do curso na data da matricula. ▪ VL. PAGO é o valor efetivamente pago pelo aluno decorrente de negociação de desconto. Aplicando engenharia reversa 85 ▪ Esquema descrevendo os dados do aluno em uma tabela não normalizada: ▪ A tabela é considerada não normalizada porque apresenta redundância de dados , conforme observado no tabela com dados abaixo, outras situações que contrariam as regras das formas normais. ▪ Tabela com os registros dos alunos: Aplicando engenharia reversa 86 ▪ Constrói-se uma tabela para cada atributo multivalorado e para cada tabela aninhada. ▪ Cada atributo composto é decomposto em atributos na própria tabela. Os atributos da tabela aninhada também podem ser representados como atributos multivalorados Primeira Forma Normal (1FN) 87 ▪ Constrói-se uma tabela para cada atributo multivalorado e para cada tabela aninhada. Primeira Forma Normal (1FN) 88 ▪ Cada atributo composto é decomposto em atributos na própria tabela. Primeira Forma Normal (1FN) 89 ▪ Tabelas normalizadas na 1FN. Primeira Forma Normal (1FN) 90 ▪ Dependência Funcional ▪ Estabelece uma relação de atributos dentro da tabela. A dependência mais comum é a funcional. A dependência funcional acontece sempre quando um atributo determina outro. ▫ Ex: Código do cliente > Nome do cliente ▫ Lê - se: ▫ Para descobrirmos o nome do cliente (dentro de um conjunto de clientes), primeiramente precisamos saber qual é o código dele. Assim, o campo/atributo nome é dependente do campo/atributo código. Segunda Forma Normal (2FN) – Dependência Funcional 91 Segunda Forma Normal (2FN) – Dependência Funcional 92 ▪ Ocorre quando um ou mais atributos de uma entidade dependem totalmente da chave primária composta (2 ou mais chaves primárias). Na tabela com esta dependência deve apenas conter os atributos que dependem das 2 PKs. Segunda Forma Normal (2FN) - Dependência Funcional Total 93 ▪ Quando os atributos apenas dependem de sua chave primária. Constrói-se uma tabela para acomodar as dependências funcionais parciais. Segunda Forma Normal (2FN) - Dependência Funcional Parcial 94 ▪ Tabelas normalizadas na 2FN. Segunda Forma Normal (2FN) 95 ▪ Ocorre quando um coluna não chave primária depende funcionalmente de outra coluna (ou colunas) não chave primária. Constrói-se uma nova tabela para a dependência transitiva. Terceira Forma Normal (3FN) - Dependência Transitiva 96 ▪ Tabelas normalizadas na 3FN. Terceira Forma Normal (3FN) 97 Tabelas Normalizadas 98 Tabelas Normalizadas 99 Exercício 01 – Marque as entidades na lista abaixo. 100 Exercício 02 – Identifique as possíveis entidades para as seguintes organizações: ▪ Escola: aluno, professor, pessoa, disciplina, classe, turma, curso, sala... ▪ Aeroporto: pessoa, pf, pj, destino, origem, voo, funcionarios , check-in, despaço... ▪ Hospital: pessoa, paciente, funcionarios, consulta, acompanhante, consultorio, função... ▪ Hotel: pessoa, cliente , funcionarios, quartos, reserva, serviços, planos de reserva... ▪ Time: pessoas, tecnico, jogadores, posição, funcionários, funções, jogos... 101 Exercício 03 – Represente as entidades e os relacionamentos do seguinte cenário: Os livros são escritos por pelo menos um autor. Um autor pode escrever vários livros. Do livro deverá ser armazenado o título, o código ISBN, o número e o ano da edição. Dos autores deve-se armazenar: um código, nome, data de nascimento, país de origem, titulação Um livro é publicado por apenas uma editora, desta deve ser armazenado: nome, cidade, UF, e-mail e todos os telefones de contato. 102 Exercício 04 – Represente as entidades e os relacionamentos do seguinte caso: Um funcionário deve ter somente uma profissão. Um funcionário é designado para trabalhar em apenas um departamento. Um departamento pode estar em diversas Filiais. Cada filial é situada em alguma unidade da federativa do Brasil, podendo ter uma unidade da federativa com mais de uma filial. Do Funcionário deve-se armazenar: Matricula, nome e salário. Da profissão deve-se armazenar: código e descrição Do departamento deve-se armazenar o nome e a sigla do departamento. Da filial deve-se armazenar o endereço e os telefones. Da unidade da federativa deve-se armazenar a sigla e o nome. 103 Exercício 05 – Construir um projeto de banco de dados Construa um diagrama ER (apenas entidades e relacionamentos com cardinalidades máximas e mínimas) para a Locadora de vídeos descrita abaixo. A locadora trabalha com a locação de vídeos nacionaise internacionais. Em uma entrevista com o gerente da locadora resultou nas seguintes informações: Os filmes devem ser locados por clientes previamente cadastrados. O cliente pode locar quantos filmes quiser. O filme pertence a uma determinada categoria (Drama, Ação, Aventura, etc.) Os filmes são gravados em mídias que possuem um código de identificação. Um filme pode ser gravado em várias mídias, mas uma mídia só pode conter um filme. Cada mídia pode estar locada para no máximo um cliente em um determinado tempo, mas um cliente pode alugar diversas mídias. Em uma locação pode haver várias mídias assim como ,uma mídia pode estar em várias locações. 104 Exercício 06 – Construir um projeto de banco de dados Utilizando o BRModelo modele o negócio. Aplicar as 3 FN (Formas Normais). Uma floricultura deseja informatizar suas operações. Inicialmente, deseja manter um cadastro de todos os seus clientes, armazenando informações como: RG, CPF, nome, telefone, e-mail e endereço. Dos produtos vendidos deve-se armazenar: nome do produto, código da categoria, descrição da categoria, preço unitário, preço de venda, quantidade em estoque (ex: 10 unidades), quantidade de itens (que estão sendo comprados). Também é necessário um cadastro dos vendedores contendo os atributos: matricula, nome, rg, cpf, endereço, salário, e-mail, telefone, data de admissão, data de demissão e cargo. Durante a conversa com o dono da floricultura, ele informou que deseja flexibilizar para o cliente ter várias opções de pagamento, da forma de pagamento deve-se armazenar os seguintes campos: código, sigla, descrição. Quando um pedido é realizado alguns dados são armazenados como: código da nota, data do pedido, além de outros que podem surgir a partir da relação entre as entidades. Continuação das regras no próximo slides. 105 Exercício 06 – Construir um projeto de banco de dados Lembrar que: a) Um cliente pode fazer vários pedidos, porém um pedido somente pode pertencer a um cliente b) Um produto pode estar em vários pedidos, e este pedido pode conter vários produtos c) Um vendedor pode liberar vários pedidos. Um determinado pedido somente pode ser liberado por um vendedor d) Um pedido pode ter várias formas de pagamento. Uma forma de pagamento pode ser utilizada em vários pedidos; e) Um produto pode ter apenas uma categoria. Uma categoria pode estar em diversos produtos. 106 Exercício 07 – Construir um projeto de banco de dados Uma empresa deseja contratar os seus serviços para desenvolver um banco de dados com o objetivo de controlar os contratos de fornecimento de músicos para festividades. Sendo o controle atual feito na planilha eletrônica ao lado, aplique as três formas normais. Leve em consideração se é um musico solo ou uma banda. 107 Nr_Contrato Número do contrato Dt_Contrato Data do contrato Hr_Duracao Duração em horas da festividade CNPJ_Contratante CNPJ do contratante Nm_Contratante Nome do contratante End_Contratante Endereço do contratante Tl_Contratante Telefone fixo do contratante Cd_Musico Códigos dos músicos contratados Nm_Musico Nomes dos músicos contratados Cd_Funcao_Mus Códigos das funções do músico no contrato Nm_Funcao_Mus Nomes das funções do músico no contrato Vl_Musico Valor do músico no contrato) Exercício 08 – Construir um projeto de banco de dados Uma prefeitura deseja controlar os eventos de shows que realiza. Os eventos são identificados por um código e um nome. Cada evento é realizado em apenas um local e uma data especifica. Do local deve armazenar nome do local, cidade, estado e bairro. Cada evento é controlado por apenas um organizador. Os organizadores de eventos serão previamente cadastros pela prefeitura os seguintes dados: matricula, nome e telefone. De um evento podem participar vários artistas, que deverão ser cadastrados na prefeitura, onde deverá constar um código para cada artista, seu nome e cpf. A prefeitura deseja registrar o cachê pago a cada artista por evento. Aonde existe um vinculo entre artista e evento. Cada artista tem apenas um estilo musical. 108 Exercício 09 – Construir um projeto de banco de dados Uma instituição deseja desenvolver um aplicativo para otimizar as orientações que os professores dão aos alunos para elaboração do TCC. Elabore um DER que atenda a seguintes regras estabelecidas: Cada aluno está vinculado a um único curso e só tem um professor orientador. A orientação são os encontros realizados entre o aluno e o professor. Cada encontro da orientação so pode conter um professor e um aluno em uma determinada hora e data. O objetivo desta tabela é registrar todos os encontros feitos. Em cada orientação realizada pode ser recomendado a leitura de vários artigos. Dos artigos deve-se armazenar o título, o ano de publicação e os autores. Das orientações deseja-se armazenar a data da orientação, hora de inicio , hora de fim , orientando e o orientador. Dos orientandos deve-se armazenar o número da matricula, o nome, os telefones de contato e o e-mail. Do orientador será armazenado o CPF, nome, titulação, telefones de contato e o e-mail. Dos cursos deve-se registrar o nome do curso e a portaria de funcionamento do curso (número, data da portaria e data da publicação no DOU). Obs.: Utilizar generalização / especialização. Aluno e Professor se relacionam mais de uma vez neste modelo. 109 Microsoft SQL Server 110 SGBD ou Sistema de Gerenciamento de Banco de Dados: São softwares que tem como principal finalidade a manipulação e gerenciamento de informações armazenadas no banco de dados. Entre seus principais objetivos esta o armazenamento e segurança dos dados, geração e recuperação de arquivos, prevenção de anormalidades, entre outras funcionalidades. Atualmente no ranking do DB Engines existem cerca de 358 SGBD's. Sistema de Gerenciamento de Banco de Dados As empresas levam em consideração alguns requisitos na hora de escolher seus SGBD's, sendo alguns deles: ▪ Tempo de resposta; ▪ Segurança; ▪ Preço; ▪ Suporte; ▪ Compatibilidade com a nuvem; ▪ Quantidade de profissionais disponíveis no mercado. ▪ Entre outras funcionalidades. 111 Principais SGBD's do mercado – db-engines (https://db-engines.com/en/ranking): 1. Oracle 2. MySQL 3. Microsoft SQL Server 4. PostgreSQL 5. MongoDB 6. IBM Db2 7. Redis 8. ElasticSearch 9. SQLite TOP 21 – DB Engines 10. Cassandra 11. Microsoft Access 12. MariaDB 13. Splunk 14. Teradata 15. Hive 16. Amazon Dynamo 17. Microsoft Azure SQL Database 18. SAP Adaptive Server 19. SAP HANA 20. Solr 21. Neo4j 112 É um sistema gerenciador de Banco de dados relacional (SGBD) desenvolvido e mantido pela Microsoft. Suas linguagens de consulta primárias são Transact-SQL (T-SQL) e ANSI SQL. Distribuído em diferentes edições e com várias ferramentas integradas, esse banco é capaz de atender às demandas desde os mais simples negócios até os mais complexos cenários que lidam com grande volume de dados. Microsoft SQL Server Antigamente o SQL Server era apenas utilizado para armazenamento de dados relacionais. Essencialmente nesse modelo você armazena dados estruturados no formato tabular (linhas e colunas). Atualmente o SQL Server passou a ser uma PLATAFORMA DE SERVIÇOS DE DADOS. Seus serviços foram ampliados para atender as mais variadas áreas que utilizam dados como Banco de dados Relacionais, Business Intelligence, Data Mining, Machine Learning, ETL/ELT, Big Data, Data Science. 113 É disponibilizado nas versões: ▪ Enterprise ▪ Standard ▪ Developer ▪ Express ▪ Azure Microsoft SQL Server Os links abaixo mostram as versões disponíveis e uma comparação entre as mesmas. https://www.microsoft.com/pt-br/sql- server/sql-server-2017-editions https://www.microsoft.com/pt-br/sql- server/sql-server-2017-comparison 114 ▪ Enterprise É a versão completa com recursos datacenter, virtualização ilimitada e BI. Esta versão não é gratuita e o seu licenciamento é feito por núcleo. ▪Standard Permite gerenciamento de bancos de dados (relacionais e BI), oferece suporte a ferramentas de desenvolvimento comuns para rede local e em nuvem, assim, permitindo o gerenciamento eficiente de bancos de dados com mínimos recursos de TI. Esta versão não é gratuita e o seu licenciamento é feito por núcleo. Microsoft SQL Server - Versões ▪ Developer Esta edição é idêntica a edição Enterprise. Utilizada por desenvolvedores para criar, testar e demonstrar de maneira econômica os aplicativos baseados no SQL Server. Esta edição é gratuita. Não se pode utilizar para versão comercial. ▪ Express Banco de dados de nível básico gratuito ideal para aprendizado, além da criação de aplicativos que utilizam até 10 GB de dados. Esta edição também é gratuita. ▪ Azure SQL Database Versão do banco de dados na nuvem da Microsoft. 115 Ao instalarmos o SQL Server temos a opção de escolher quais ferramentas queremos utilizar. Alguns conceitos devem ser compreendidos sobre uma instalação do SQL Server. Instância é uma instalação de um SGBD em uma máquina. Uma máquina pode conter várias instancias de um SGBD. O Microsoft SQL Server - Features ▪ Database Engine ▪ Management Studio ▪ Analisys Service ▪ Reporting Services ▪ Integration Services ▪ Data Quality Services ▪ SQL Agent ▪ Browser ▪ Full-Text Search ▪ Docs, help, entre outros. 116 Instalando e acessando o SQL Server 117 Para instalar o SQL Server deve-se acessar o site da Microsoft e realizar o download da versão desejada. A versão Express With Tools do SQL Server 2014 idioma em inglês contém o Management Studio( ferramenta gráfica do SQL Server) integrada no instalador. Obs.: A partir da versão 2016 a ferramenta gráfica Management Studio não vem no mesmo instalador do banco de dados. O download deve ser feito separadamente. Instalando o Microsoft SQL Server ▪ Algumas Features disponíveis para download no site da Microsoft ao baixar o SQL Server ▪ LocalDB (SqlLocalDB) ▪ Express (SQLEXPR) ▪ Express with Tools (SQLEXPRWT) ▪ SQL Server Management Studio (SQLManagementStudio) ▪ Express with Advanced Services (SQLEXPRADV) 118 Sistema operacional compatível Windows 10 ; Windows 7 Service Pack 1; Windows 8.1; Windows Server 2008 R2; Windows Server 2008 R2 SP1; Windows Server 2012; Windows Server 2012 R2 Processador Intel - processador compatível com uma velocidade mínima de 1 GHz ou mais rápido RAM Mínimo de 512 MB para SQL Server Express with Tools e SQL Server Express with Advanced Services, e 4 GB para o Reporting Services que é instalado com o SQL Server Express with Advanced Services Requisitos do sistema – SQL Express Espaço em disco rígido 4,2 GB de espaço em disco Limitações Microsoft SQL Server Express supports 1 physical processor, 1 GB memory, and 10 GB storage Softwares necessários Habilitar Microsoft .Net Framework 3.5 SP1. e Baixar e instalar Microsoft .Net Framework 4.0, instalar o JDK 1.7 ou superior. 119 Baixando o SQL Server 120 Link para download: https://www.microsoft.com/en-us/download/details.aspx?id=57473 Baixando o SQL Server 121 Selecionar a verão desejada, podendo ser 32 ou 64 bits Instalando o SQL Server 122 Clicar no arquivo que foi baixado pelo site da Microsoft e executa-lo como administrador. 123 Instalando o SQL Server Para uma nova instalação do SQL Server clicar em New SQL Server stand-alone installation or add features on na existing installation. 124 Instalando o SQL Server Aceitar os termos de licença e clicar em Next. 125 Instalando o SQL Server Na tela de Global Rules o SQL Server ira verificar se todos os requisitos do sistema estão OK para prosseguir com a instalação. Caso algum item contenha falha, corrigi-lo e executar o Re-run novamente. Caso os itens estejam ok clicar em Next. 126 Instalando o SQL Server Na tela Product Updates o SQL Server checará se há alguma feature do sistema necessitando de download, caso sim, ele solicita permissão para instalação. Após permissão concedida, a tela de Install Setup Files demonstra como esta a instalação dos itens. Uma vez finalizado o download o botão Next fica habilitado. 127 Instalando o SQL Server Na tela Install Rules, o SQL Server mostra quais regras foram aprovadas para que a instalação continue com sucesso. Em caso de erro, tentar corrigir e clicar no botão Re-run (re-checar), caso não haja erro clicar no botão Next. 128 Instalando o SQL Server Na tela Feature Selection, devemos selecionar os itens que iremos instalar no SQL Server. Uma vez selecionados, clicar em Next. 129 Instalando o SQL Server Na tela Instance Configuration o SQL Server lhe dará 2 opções para o nome da instancia. Primeiro a padrão que geralmente vem com o nome MSSQL ou instância nomeada, onde é possível informar no nome desejado. Ele também lista outras instâncias já instaladas na máquina através da lista Installed Instances. Instalando o SQL Server 130 Na tela Server Configuration, aba Service Accounts o SQL Server lista quais serviços serão instalados, nome da conta e tipo de inicialização. Há opção de colocar um serviço como desativado, manual e automático. Após configurar os serviços clicar na aba Collation. 131 Instalando o SQL Server Na tela Server Configuration, aba Collation, há opção de configurar as características de escrita do SQL Server. Por padrão o collate principal é o Latin_General_CI_AS. 132 Instalando o SQL Server Na tela Database Engine Configuration, vamos configurar a aba Server Configuration. Na opção Authentication Mode existem 2 possibilidades, a primeira é o Windows authentication mode, forma de login com as credenciais do Windows. A segunda forma é Mixed Mode, há possibilidade de logar tanto com credenciais do Windows quanto com o usuário master do SQL Server. Lembrar da senha utilizada para se logar futuramente. Na opção Specify SQL Server administrators, caso venha vazio clicar no botão Add Current User. Clicar na aba FILESTREAM. 133 Instalando o SQL Server Na tela Database Engine Configuration, vamos configurar a aba FILESTREAM. O Filestream nos permite salvar arquivos e documentos dentro da base de dados. Ex: PDF’s, Docx, Xlsx, áudio, vídeo, entre outros. Habilitar as três caixas de seleção que estão na tela. Após caixas habilitadas clicar Next. 134 Instalando o SQL Server Na tela Ready to Install mostrará todos os itens que serão instalados. Após checar clicar em Install. 135 Instalando o SQL Server Na tela Installation Progress mostrará o processo de instalação. Aguardar em torno de 30 a 40 minutos para que o processo seja finalizado. 136 Instalando o SQL Server Na tela Complete mostrará se todos os itens foram instalados com sucesso ou erro. Após conferência dos itens clicar em Close. 137 Acessando o SQL Server Acessar o menu iniciar do SO e clicar no ícone do SQL Server 2014 Management Studio. 138 Acessando o SQL Server Duplo clique no ícone. 139 Acessando o SQL Server O SQL Server começará a ser carregado. 140 Acessando o SQL Server Na tela de conexão do SQL Server vemos algumas opções disponíveis, no Server name será exibido o nome instância configurada no anteriormente na instalação. No Authentication selecionar a opção SQL Server Authentication, no login informar sa (minúsculo) e a senha configurada no momento da configuração da instalação. O usuário sa é o usuário administrador do SQL Server; Clicar em Connect. 141 Acessando o SQL Server Uma nova conexão é aberta com a instância instalada. Os únicos bancos de dados disponíveis após a instalação são os bancos de dados de sistemas. Databases > System Databases. Após instalação de cada instância, o SQL Server possui uma estrutura de banco de dados de sistemas. Banco de dados do sistema ▪ Master: Guarda informações da instância instalada, possui as configurações do servidor, possui as informaçõessobre contas de login, guarda a localização dos arquivos de base de dados. ▪ NUNCA APAGA-LO. ▪ Model: Banco modelo, serve para todos os bancos de dados de usuário. Todas as bases de dados utilizam as configurações do model. Banco de dados de sistema ▪ TempDB: Banco de dados temporário, é recriado sempre que a instância é reiniciada. Mantém os objetos temporários do banco de dados (cursores, variáveis e etc.). O banco de dados TempDB utiliza o model no momento que é recriado, quando há uma inicialização ou reinicialiazação do sistema. ▪ MSDB: Da suporte ao SQL Server Agent, guardando todas as informações de tarefas automatizadas o SQL Server, também armazena pacotes de ETL. 142 Quando um banco de dados é criado no SQL Server dois tipos de arquivos são gerados. Os arquivos MDF e LDF. Pode-se ter os arquivos de dados secundários chamados de NDF. ▪ MDF: Master data file. Armazenam tudo que os usuários criam, como dados, índices, procedures, usuários, roles, views e etc. ▪ NDF: Secondary data file. Não é obrigatório. ▫ Este São arquivos adicionais de dados e ajudam a distribuir a quantidade de dados do banco de dados. Estrutura de arquivos – Arquivos MDF, LDF e NDF ▪ LDF: Log data file. Protegem o SQL Server de falhas, como erros de usuários, falhas de disco e etc. Armazenam as transações. Uma transação primeiro é salva no arquivo de log e posteriormente no arquivo de dados. Wal-Write ahead log. 143 Tipos de dados do SQL Server 144 No SQL Server, cada coluna, variável, expressão e parâmetro tem um tipo de dados relacionado. O tipo de dado especifica o que pode ser colocado no objeto. A integridade do banco de dados depende decisivamente dos tipos de dados com escopo apropriado. Os tipos de dados que o objeto pode assumir são: dados inteiros, dados de caractere, dados monetários, data e hora, cadeias de caracteres binárias etc. Categoria dos tipos de dados do SQL Server ▪ Os tipos de dados em SQL Server são organizados nas seguintes categorias: ▫ Numéricos ▫ Numéricos exatos ▫ Numéricos aproximados ▫ String (Texto) ▫ Cadeias de caracteres ▫ Cadeias de caracteres Unicode ▫ Cadeia de caracteres binária ▫ Data e hora ▫ Outros tipos de dados 145 ▪ Numéricos exatos ▫ bigint ▫ numeric ▫ bit ▫ smallint ▫ decimal ▫ smallmoney ▫ int ▫ tinyint ▫ money Tipos de dados ▪ Numéricos aproximados ▫ float ▫ Real ▪ Data e hora ▫ date ▫ datetimeoffset ▫ datetime2 ▫ smalldatetime ▫ datetime ▫ time 146 ▪ Cadeias de caracteres ASCII ▫ Char ▫ Varchar ▫ Text (sendo substituído pelo varchar) ▪ Cadeias de caracteres Unicode ▫ Nchar ▫ Nvarchar ▫ Ntext (sendo substituído pelo nvarchar) ▪ Cadeia de caracteres binária ▫ binary ▫ varbinary ▫ imagem Tipos de dados ▪ Outros tipos de dados ▫ cursor ▫ rowversion ▫ hierarchyid ▫ uniqueidentifier ▫ sql_variant ▫ XML ▫ Tipos de geometria espacial ▫ Tipos de geografia espacial ▫ Table https://docs.microsoft.com/pt-br/sql/t-sql/data- types/data-types-transact-sql?view=sql-server-2017 147 148 Tipos Numéricos Tipo de Dado Descrição Tamanho (bytes) bit Número Inteiro que pode ser 0, 1 ou NULL 1 byte tinyint Permite números inteiros de 0 a 255 1 byte smallint Permite números inteiros entre -32,768 e 32,767 2 bytes int Permite números inteiros entre -2,147,483,648 e 2,147,483,647 4 bytes bigint Permite números inteiros entre -9,223,372,036,854,775,808 e 9,223,372,036,854,775,807 8 bytes decimal(p,s) Precisão de número flutuante e número de escala. Permite número de -10^38 +1 a 10^38 –1. O parâmetro p indica o número total máximo de dígitos que podem ser armazenados (ambos à esquerda e à direita do ponto decimal). p deve ser um valor de 1 a 38. O padrão é 18. O parâmetro s indica o número máximo de dígitos armazenados à direita do ponto decimal. s deve ser um valor de 0 a p. O valor padrão é 0. 5-17 bytes 149 Tipos Numéricos Tipo de Dado Descrição Tamanho (bytes) numeric(p,s) Precisão de número flutuante e número de escala. Permite número de -10^38 +1 a 10^38 –1. O parâmetro p indica o número total máximo de dígitos que podem ser armazenados (ambos à esquerda e à direita do ponto decimal). p deve ser um valor de 1 a 38. O padrão é 18. O parâmetro s indica o número máximo de dígitos armazenados à direita do ponto decimal. s deve ser um valor de 0 a p. O valor padrão é 0 5-17 bytes smallmoney Tipo de "Moeda" de -214,748.3648 a 214,748.3647 4 bytes money Tipo de "Moeda" de -922,337,203,685,477.5808 a 922,337,203,685,477.5807 8 bytes float(n) Precisão de número flutuante de -1.79E + 308 a 1.79E + 308.O parâmetro n indica se o campo deve conter 4 ou 8 bytes. float (24) contém um campo de 4 bytes e o float(53) mantém um campo de 8 bytes. O valor padrão de n é 53. 4 ou 8 bytes real Precisão de número flutuante de -3,40E + 38 a 3,40E + 38 4 bytes 150 Tipos String Texto Tipo de Dados Descrição Tamanho Máximo Tamanho (bytes) char(n) Tamanho fixo, completado com espaços em branco o restante dos bytes. 8,000 caracteres Tamanho Definido 1 byte varchar(n) Tamanho variável com limite 8,000 caracteres 2 bytes + número de caracteres varchar(max) Tamanho variável com limite 1,073,741,824 caracteres 2 bytes + número de caracteres TEXT (substituir por varchar(max)) Tamanho variável 2GB de dados (texto) 4 bytes + número de caracteres nchar Tamanho fixo com espaços em bracos 2GB Tamanho definido x 2 nvarchar Tamanho variável 2GB Tamanho definido x 2 nvarchar(max) Tamanho variável 536,870,912 caracteres NTEXT (substituir por nvarchar(max)) Tamanho variável 2GB de texto binary(n) Tamanho fixo (binário) 8,000 bytes varbinary Tamanho variável (binário) 8,000 bytes varbinary(max) Tamanho variável (binário) 2GB image Tamanho variável (binário) 2GB 151 Tipos Data e Hora Tipo de Dado Descrição Tamanho (bytes) datetime (utilizar o datetime2 ao invés do datetime) De 1 de janeiro de 1753 a 31 de dezembro de 9999 com uma precisão de 3,33 milisegundos 8 bytes datetime2 De 1º de janeiro de 0001 a 31 de dezembro de 9999 com precisão de 100 nanossegundos 6-8 bytes smalldatetime De 1 de janeiro de 1900 a 6 de junho de 2079 com precisão de 1 minuto 4 bytes date Armazena apenas uma data. De 1 de janeiro de 0001 a 31 de dezembro de 9999 3 bytes time Armazena um tempo apenas para uma precisão de 100 nanosegundos 3-5 bytes datetimeoffset O mesmo que datetime2 com a adição de um deslocamento de fuso horário 8-10 bytes 152 Outros Tipos Tipo de Dado Descrição sql_variant Armazena até 8.000 bytes de dados de vários tipos de dados, exceto text, ntext e timestamp uniqueidentifier Armazena um identificador globalmente exclusivo (GUID) xml Armazena dados formatados em XML. Máximo de 2GB cursor Armazena uma referência a um cursor usado para operações de banco de dados table Armazena um conjunto de resultados para processamento posterior ▪ Analisar quantas informações serão armazenadas no campo da tabela. Se perguntar até aonde aquela informação pode crescer. ▪ É recomendável usar os tipos de dados String de comprimento fixo (char, nchar, binary), quando os valores que estão sendo armazenados tiverem um tamanho consistente. Quando o tamanho não for consistente utilizar os tipos String de comprimento variável (varchar,nvarchar, varbanary) Dicas sobre os tipos de dados ▪ É mais vantajoso usar VARCHAR ao invés de um CHAR. Mas Por quê? ▫ Ex.: Um campo é do tipo CHAR (5), mesmo que você o preencha com apenas 2 caracteres, ele ocupa em memoria 5 bytes. ▫ Já o campo VARCHAR vai ocupar em bytes apenas o que esta sendo inserido no campo. 153 ▪ Para os tipos numéricos, é necessário verificar até quando a informação vai crescer. ▫ Ex.: É melhor uma coluna Smallmoney ou Money em uma coluna de preço de produto? Eu terei algum preço que vai ultrapassar 200 mil de valor? ▫ Quero armazenar o valor de 1 a 10 em uma coluna. Pode-se utilizar TINYINT ao invés de INT, pois seu range de dados vai de 0 a 255 e seu consumo é apenas de 1 byte. Dicas sobre os tiposde dados ▪ Para os tipos Data, verificar se deseja armazenar apenas a data, data e hora ou apenas a hora. A Microsoft recomenda usar DATETIME2 ao invés de DATETIME. 154 SQL: Linguagem de Programação para banco de dados 155 Structured Query Language ou Linguagem de Consulta Estruturada ou SQL, é a linguagem de pesquisa declarativa padrão para banco de dados relacional (base de dados relacional). Muitas das características originais do SQL foram inspiradas na álgebra relacional. O SQL foi desenvolvido originalmente no início dos anos 70 nos laboratórios da IBM em San Jose, dentro do projeto System R, que tinha por objetivo demonstrar a viabilidade da implementação do modelo relacional proposto por E. F. Codd. SQL O nome original da linguagem era SEQUEL, acrônimo para "Structured English Query Language" (Linguagem de Consulta Estruturada, em Inglês), vindo daí o facto de, até hoje, a sigla, em inglês, ser comumente pronunciada "síquel" ao invés de "és-kiú-él", letra a letra. No entanto, em português, a pronúncia mais corrente é letra a letra: "ésse-quê-éle". [Wikipedia] 156 Embora o SQL tenha sido originalmente criado pela IBM, rapidamente surgiram vários "dialetos" desenvolvidos por outras empresas. Essa expansão levou à necessidade de ser criado e adaptado um padrão para a linguagem. Esta tarefa foi realizada pela American National Standards Institute (ANSI) em 1986 e ISO em 1987. A primeira versão surgiu em 1986. Foi revisto na primeira vez em 89 com nome SQL-89, Novamente m 1992 e a esta versão foi dado o nome de SQL-92. Foi revisto novamente deste então. O SQL:2019 foi a ultima versão lançada da linguagem. https://en.wikipedia.org/wiki/SQL SQL ▪ SQL-92 surgimento da clausula JOIN. ▪ SQL:1999 usa expressões regulares de emparelhamento, queries recursivas e gatilhos (triggers). ▪ SQL:2003 introduz características relacionadas a XML, sequências padronizadas e colunas com valores de auto-generalização (inclusive colunas-identidade). ▪ SQL:2006 inclusão do XQuery e melhorias na utilização de XML. ▪ SQL:2008: inclusão das Triggers INSTEAD OF, TRUNCATE e Cláusula FETCH. ▪ SQL:2011 Inclusão de dados temporais e melhorias na cláusula FETCH. ▪ SQL:2016 introduz a leitura e gravação de JSON’s e a utilização de linguagens como R e Python. ▪ SQL:2019 inclusão arrays multidimensionais (tipo e operadores MDarray). 157 Tal como dito anteriormente, embora padronizado pela ANSI e ISO, a linguagem possui muitas variações e extensões produzidos pelos diferentes fabricantes de sistemas gerenciadores de bases de dados. Tipicamente a linguagem SQL ANSI pode ser migrada de plataforma para plataforma sem mudanças estruturais principais. SQL ▪ Variações do SQL ANSI ▫ SQL Server: Transact-SQL ▫ Oracle: PL/SQL ▫ PostgreSQL: PL/pgSQL ▫ MySQL: SQL/PSM ▫ Interbase/Firebird: PSQL ▫ IBM DB2: SQL PL ▫ SAP HANA: SQLScript ▫ Teradata: SPL [Wikipedia] 158 A linguagem SQL se subdivide em 5 grupos de comandos. Alguns autores apenas consideram 3 ou 4. Cada grupo tem seus comandos específicos, tendo cada um uma finalidade especifica. ▪ Obs.: Esta subdivisão pode ser diferente dependendo do treinamento e da concepção de cada profissional de banco de dados. Grupo de comandos SQL 159 DDL CREATE ALTER DROP TRUNCATE USE DML INSERT UPDATE DELETE MERGE DQL SELECT DCL GRANT REVOKE DENY DTL BEGIN TRAN COMMIT ROLLBACK O DDL é usado para criar , alterar e deletar objetos do banco de dados. Podendo ser banco de dados, tabelas, views, procedures, functions, indexes, usuário e etc. Seus Principais comandos são: ▪ USE – Muda o context (BD) da janela de consulta. ▪ CREATE – Cria objetos no banco de dados. ▪ ALTER – Altera estrutura objetos no banco. ▪ DROP – Apaga objetos do Banco. ▪ TRUNCATE – Apaga todos os registros de uma tabela, reiniciando o índice das colunas auto incrementais. DDL: DATA DEFINITION LANGUAGE 160 ▪ Diferenças entre os comandos SQL Server DELETE e TRUNCATE. ▪ Truncar redefine os valores de identidade, enquanto a exclusão não. ▪ Truncar remove todos os registros e não aciona triggers. ▪ Truncar é mais rápido comparado a excluir, pois faz menos uso do log de transações. ▪ Truncar não é possível quando uma tabela é referenciada por uma Chave Estrangeira ou tabelas são usadas na replicação ou com views indexadas. O DML é usado para a manipulação os registros no banco de dados. Seus comandos são: ▪ INSERT – Insere registros na tabela do banco. ▪ UPDATE – Atualiza registros na tabela do banco. ▪ DELETE – Deleta /Apaga registros na tabela do banco. ▪ MERGE - Executa operações de inserção, atualização ou exclusão em uma tabela de destino com base nos resultados de uma junção com uma tabela de origem. DML: DATA MANIPULATION LANGUAGE 161 ▪ Obs.: Para a certificação MTA Database Fundamentals o comando SELECT é categorizado como um comando DML. (Verificar o livro oficial) O DQL é usado para a consultar dados no banco. Seu comando é: ▪ SELECT – Usado para consultar informação aos registro das tabelas/views ou Function_table_valued do banco de dados. DQL: DATA QUERY LANGUAGE 162 ▪ Obs.: O comando SELECT é categorizado como um comando DML para a prova. É importante criar um padrão de codificação e nomenclatura dos objetos do banco de dados. Esse processo facilita a leitura do código e futuras manutenções. Dicas: ▪ Os tipos de dados string (texto) e data são delimitados por aspas simples. ▫ Ex: 'Joao' ▫ Ex: '2019-01-01' ▪ Comentários no SQL Server ▫ Linha: -- ▫ Bloco: /* */ Dicas sobre os comandos SQL 163 ▪ O SQL utiliza o padrão ISO para datas, ou seja, o formato ANOMESDIA. Porém pode-se adicionar datas no formato ANO-MÊS-DIA ou DIA/MÊS/ANO. ▪ Virgula na linguagem SQL é separador de campos. ▫ Ex.: Campo1, campo2, campo3, .... ▪ Para dados números utilizar o ponto para separar os valores. ▫ Ex.: 149.99 ▪ No comando SELECT o asterisco (*) significa que todos os campos da tabela serão exibidos. O DCL controla a segurança dos dados, definido quem tem acesso a determinado tipo de operação. Seus comandos são: ▪ GRANT – Usado para dar permissão a um determinado usuário para executar um procedimento ou acessar um determinado objeto. DCL: DATA CONTROL LANGUAGE 164 ▪ DENY – Negar permissões de acesso a objetos do banco de dados. ▪ REVOKE – Desfaz uma permissão, seja uma GRANT ou DENY. O DTL é usado para a manipulação de transações (create, alter, drop, truncate, inserção, alteração, remoção e consulta) no banco de dados. Seus comandos são: ▪ BEGIN TRAN ou (BEGIN TRANSACTION) – Utilizado para iniciar uma transação. ▪ COMMIT – Utilizado para gravar as transações no banco. ▪ ROLLBACK – Utilizado para desfazer as alterações de uma transação no banco. DTL: DATA TRANSACTION LANGUAGE 165 ▪ Obs.1: Alguns SGBDs são auto-commit, ou seja, uma vez executado comandos do tipo DML os dados são gravados automaticamente no banco de dados. ▪ Obs.2: Ao realizar a abertura de uma transação com o BEGIN TRAN, a transação deve ser finalizada com os comandos COMMIT ou ROLLBACK. ▪ Alguns sistemas ERPs travam quando uma transação é aberta e não é finalizada. ▪ CREATE ▫ Sintaxe: ▫ CREATE OBJETO NOME_OBJETO; ▫ Criando banco de dados (on-premise - local) CREATE DATABASE BD_DDL; ▫ Criando banco de dados no Azure. CREATE DATABASE BD_DDL (EDITION = 'basic') ; Comandos DDL 166 ▪ USE ▫ Carregando o banco de dados no contexto da janela de consulta do SQL Server USE BD_DDL; ▪ Criando uma Tabela CREATE TABLE TB_DDL( MATRICULA TINYINT PRIMARY KEY, NOME VARCHAR(100) , SEXO CHAR(1)) ▪ ALTER ▫ Sintaxe: ▫ ALTER OBJETO NOME_OBJETO; ▪ Alterando a tabela, adicionando um novo campo ALTER TABLE TB_DDL ADD DTNASC DATETIME, CPF CHAR( 11) ▪ Alterando a tabela, alterando o tipo de dados campo SEXO de CHAR(1) para VARCHAR(15) ALTER TABLE TB_DDL ALTER COLUMN DTNASC DATE Comandos DDL 167 ▪ Alterando a tabela, excluindo o campo SEXO. ALTER TABLE TB_DDL DROPCOLUMN SEXO; ▪ TRUNCATE ▫ Sintaxe ▫ TRUNCATE TABLE NOMETABELA; ▪ Apagando todos os registros da tabela TRUNCATE TABLE TB_DDL; Obs.: Se existir ligação de chave estrangeira na tabela que esta sendo executado o comando, o TRUNCATE não será realizado. Comandos DDL 168 ▪ DROP ▫ Sintaxe ▫ DROP OBJETO NOME_OBJETO; ▪ Apagando tabela do banco de dados DROP TABLE TB_DDL; ▪ Obs.: Se existir ligação de chave estrangeira não sera possível deletar os dados da tabela. Comandos DDL 169 ▪ Apagando o banco de dados ▫ DROP DATABASE BD_DDL; ▪ Apagando Objetos do banco de dados ▫ DROP DATABASE TESTE; ▫ DROP VIEW VW_TESTE ▫ DROP FUNCTION FN_TESTE ▫ DROP PROCEDURE USP_TESTE ▫ DROP TRIGGER TR_TESTE CREATE DATABASE BD_DML USE BD_DML CREATE TABLE TB_DML( MATRICULA TINYINT PRIMARY KEY, NOME VARCHAR(100) , SEXO CHAR(1)) ALTER TABLE TB_DML ADD DTNASC DATETIME, CPF CHAR( 11) ALTER TABLE TB_DML ALTER COLUMN DTNASC DATE ALTER TABLE TB_DML DROP COLUMN CPF; SCRIPT DML, DCL, DQL, DTL 170 ▪ INSERT ▪ Sintaxe: ▫ MANEIRA 1 INSERT INTO TABELA (CAMPOS) VALUES (VALORES); ▫ MANEIRA 2 INSERT INTO NOMETABELA VALUES (VALORES,VALORES) ▫ Obs.: Quando não se especifica os campos no momento do insert, deve-se assumir a ordem de criação dos campos na tabela. Comandos DML 171 ▪ Inserindo um registro na tabela especificando os campos INSERT INTO TB_DML (MATRICULA, NOME, DTNASC, SEXO) VALUES (1, 'PESSOA_A', '2000-12-10','M') ▪ Utilizando o mesmo insert para inserir vários registros INSERT INTO TB_DML (MATRICULA, NOME, DTNASC, SEXO) VALUES (2, 'PESSOA_B', '2000-12-10','M'), (3,'PESSOA_C','2000-01-01','F') Comandos DML 172 ▪ Inserindo um registro na tabela sem especificar os campos. Lembrar da ordem das colunas no momento da criação da tabela. INSERT TB_DML VALUES (4, 'PESSOA_D', '2002-01-10','M') ▪ Inserindo dados numa tabela que foi resultado de um relacionamento n x n. INSERT INTO LISTA_COMPRAS (CODPRODUTO, CODNF, QTD) VALUES ('PRD0001', 'NF000001', 3) ▪ UPDATE ▫ Sintaxe: UPDATE TABELA SET CAMPO = NOVO_VALOR WHERE CONDICAO = VALOR ▫ Atenção: Sempre utilizar filtros para executar este comando Comandos DML 173 ▪ Atualizando o registro de um campo da tabela funcionário UPDATE TB_DML SET SEXO = 'F' WHERE NOME = 'PESSOA_B' ▪ Atualizando dois campos de um registro da tabela funcionário. UPDATE TB_DML SET SEXO = 'F', DTNASC = '1997-07-01' WHERE NOME = 'PESSOA_D' ▪ DELETE ▫ Sintaxe DELETE FROM TABELA WHERE CONDICAO = VALOR OU DELETE TABELA WHERE CONDICAO = VALOR ▫ Atenção: Sempre utilizar filtros para executar este comando ▪ Obs.: Se existir ligação de chave estrangeira na tabela que esta sendo executado o comando, o DELETE não será realizado. Comandos DML 174 ▪ Apagando o registro da tabela funcionário. DELETE FROM TB_DML WHERE MATRICULA = 3 Ou DELETE TB_DML WHERE MATRICULA = 3 ▪ SELECT ▫ Sintaxe SELECT <valor a ser exibido> OPCIONAL: FROM TABELA ou VIEW ou FUNCTION OU outros objetos. ▪ É possível executar um SELECT sem uma tabela. Podemos executa-lo para mostrar um valor fixo ou exibir o nome de uma variável. ▪ Listando todas as colunas da tabela com todos os registros. SELECT * FROM TB_DML Comando DQL 175 ▪ Selecionando apenas algumas colunas da tabela. SELECT NOME, DTNASC FROM TB_DML WHERE SEXO = 'F' ▪ Selecionando todas as colunas da tabela e filtrando por várias informações. SELECT * FROM TB_DML WHERE DTNASC > '1999-10-25' AND SEXO = 'F' ▪ BEGIN TRANSANCTION / COMMIT / ROLLBACK ▫ Sintaxe BEGIN TRAN / BEGIN TRANSACTION COMANDO SQL (DML,DCL, DDL) COMMIT OU ROLLBACK Comandos DTL 176 ▪ Abrindo uma transação no banco de dados para execução do comando UPDATE BEGIN TRAN UPDATE TB_DML SET SEXO = 'M' WHERE NOME = 'PESSOA_D' COMMIT: Salvará a operação realizada na base de dados e fechará a transação. ROLLBACK: Irá desfazer a operação realizada e fechará a transação. ▪ GRANT / REVOKE / DENY ▫ Sintaxe GRANT / REVOKE /DENY <Privilegio> ON OBJETO_BD TO <Usuario>. ▪ Dando permissão para o usuário executar o comando SELECT na tabela. CREATE USER USER_DCL WITHOUT LOGIN; GRANT SELECT, INSERT ON DBO.TB_DML TO USER_DCL Comandos DCL 177 ▪ Desfazendo permissão dada do comando GRANT REVOKE INSERT ON DBO.TB_DML TO USER_DCL ▪ Negando permissão do usuário dar SELECT na tabela DENY SELECT ON DBO.TB_DML TO USER_DCL ▪ Executando consulta como usuário criado EXECUTE AS USER = 'USER_DCL' SELECT * FROM TB_DML REVERT; Permissões para os comandos DCL 178 Privilégio Descrição SELECT Capacidade de executar instruções SELECT na tabela. INSERT Capacidade de executar instruções INSERT na tabela. UPDATE Capacidade de executar instruções UPDATE na tabela. DELETE Capacidade de executar instruções DELETE na tabela. REFERENCES Capacidade de criar uma restrição que se refere à tabela. ALTER Capacidade de executar instruções ALTER TABLE para alterar a definição da tabela. ALL ALL não concede todas as permissões para a tabela. Em vez disso, concede as permissões ANSI-92 que são SELECT, INSERT, UPDATE, DELETE e REFERENCES. ▪ Lista de privilégios Restrições de Domínio (Constraints) 179 ▪ A principal finalidade das restrições de domínio é fazer com que os campos (ou atributos) da tabela não recebam qualquer tipo de dado e tenham um certo nível de validação no momento dos comandos DML. ▪ As restrições também são chamadas de constraints. ▪ Obs.: Deve-se ponderar a utilização de restrições de domínio na tabela, pois muitas restrições podem tornar a utilização da tabela inviável. Qual a finalidade? 180 ▪ Lista das restrições ▫ Primary Key (Chave Primária) ▫ Not Null (Não nulo) ▫ Default (Padrão) ▫ Foreign Key (Chave Estrangeira) ▫ Unique (Valor único na coluna) ▫ Check (Checagem do valor na coluna) ▪ Faz com que o atributo selecionado seja incrementado automaticamente, ou seja, ele ira gerar uma numeração automatica sem que seja necessário informar o valor no Insert. ▪ É utilizado com campos do tipo Inteiro. ▪ Não é necessário informar o campo que recebeu a clausula IDENTITY no momento do INSERT. Caso seja informado, um erro será exibido. ▪ No identity você pode informar qual o inicio da numeração e quanto será o incremento. Identity - Extra 181 ▪ Criando uma tabela com um campo auto incremental. CREATE TABLE tb_identity( Cod INT IDENTITY, NOME VARCHAR(50) ) ▪ Criando uma tabela começando a numeração a partir do número 1000 e incrementando de 1 em 1. CREATE TABLE tb_identity2 ( Cod INT IDENTITY (1000,1), NOME VARCHAR (20) ) ▪ Restrição que identifica o atributo como identificador da tabela. ▪ Quando uma tabela possui mais de uma chave primária, é chamada de chave composta. ▪ Ao definir uma campo como PK, o SQL Server cria um índice clusterizado na tabela. ▪ Boa pratica! ▫ Ao criar uma tabela sempre definir um campo como chave primária. Primary Key - PK 182 ▪ Criando chave primária no inicio da tabela com constraint. CREATE TABLE tb_pk ( Cod INT CONSTRAINT pk_test PRIMARY KEY, Nome VARCHAR(50) ) ▪ Criando chave primária no final do comando. Deve-se referenciar o campo no parênteses. CREATE TABLE tb_pk2 ( Cod INT, Nome VARCHAR(50) , PRIMARY KEY (Cod) ) ▪ Criando tabela com duas chaves primarias CREATE TABLE tb_pk3 ( Cod INT, Cod2 INT, nome VARCHAR(50) , PRIMARY KEY (Cod, Cod2) ) ▪ Adicionando chave primária, após criação de uma tabela sem pk. Obs.: O que vai receber a constraint PK, deve ter sido criado como NOT NULL. ALTER TABLE tb_pk ADD PRIMARY KEY(Cod); Primary Key - PK 183 ▪ Adicionando chave primária, após criação de uma tabela sem pk, informando o nome da constraint ALTER TABLE tb_pk ADD CONSTRAINT pk_test PRIMARY KEY (Cod) ▪ Apagando uma constraint PK ALTER TABLE tb_pk DROP CONSTRAINT pk_teste; ▪ Restrição que define que um atributo A faz referência ao atributo B que é chave de outra tabela. ▪ A chave estrangeira é definida no momento da normalização. ▪ O atributo deve ser do mesmo tipo do campo referência de suatabela de origem. ▪ O atributo (FK) deve ser referenciado a qual atributo da tabela de origem ele faz correlação. ▪ Para a chave estrangeira funcionar, a tabela que contém o atributo chave deve ser criada primeiro. ▪ Para um registro ser referenciado na tabela com a FK, o registro tem que existir na tabela de origem. Foreign Key - FK 184 ▪ Criando chave estrangeira na criação da tabela com constraint. CREATE TABLE tb_fk_marca ( Cod INT PRIMARY KEY IDENTITY, Nome VARCHAR(50) ) CREATE TABLE tb_fk_carro( Placa varchar(6) PRIMARY KEY, modelo VARCHAR(50), fk_marca INT FOREIGN KEY REFERENCES tb_fk_marca(Cod) ) ▪ Adicionando uma chave estrangeira após criação de tabela. Supondo que o campo não foi referenciado como fk. ALTER TABLE tb_fk_carro ADD FOREIGN KEY (fk_marca) REFERENCES tb_fk_marca (COD); Foreign Key - FK 185 ▪ Adicionando uma chave estrangeira com constraint após criação de tabela. Supondo que o campo não foi referenciado como fk. ALTER TABLE tb_fk_carro ADD CONSTRAINT fk_marca_carro FOREIGN KEY (fk_marca) REFERENCES tb_fk_marca (COD); ▪ Apagando constraint ALTER TABLE tb_fk_carro DROP CONSTRAINT fk_marca_carro ▪ Criando foreing key em um relacionamento N x N. CREATE TABLE ALUNO( MATRICULA INT PRIMARY KEY, NOME VARCHAR(20)) CREATE TABLE DISCIPLINA( CODDISC CHAR(3) PRIMARY KEY, DESCRICAO VARCHAR(20)) INSERT INTO ALUNO (MATRICULA, NOME) VALUES (100, 'ALUNO A'), (150, 'ALUNO B') INSERT INTO DISCIPLINA (CODDISC,DESCRICAO) VALUES ('PBD','PROG. EM BD'), ('POO','PROG. ORIENT. A OBJ') Foreign Key - FK 186 -- DISCIPLINA N x N ALUNO CREATE TABLE ALUNO_DISCIPLINA ( PK_ALUNO_DISCIPLINA INT PRIMARY KEY IDENTITY, FK_MATRICULA INT FOREIGN KEY REFERENCES ALUNO(MATRICULA), FK_CODDISC CHAR(3) FOREIGN KEY REFERENCES DISCIPLINA(CODDISC), ) INSERT INTO ALUNO_DISCIPLINA (FK_CODDISC,FK_MATRICULA) VALUES ('PBD',100), ('POO',100), ('POO',150) SELECT * FROM ALUNO SELECT * FROM DISCIPLINA SELECT * FROM ALUNO_DISCIPLINA ▪ Restrição que garante que o valor de um determinado campo não se repita dentro de uma tabela, ou seja, os valores serão únicos. ▪ A restrição PRIMARY KEY possui uma restrição UNIQUE implícita. ▪ Em uma tabela pode haver várias restrições Unique, porém somente uma restrição chave de PK por tabela. ▪ Ele cria um índice não clusterizado na tabela. ▪ A coluna com essa restrição pode aceitar apenas um valor nulo (NULL). Unique 187 ▪ Criando tabela com a restrição UNIQUE CREATE TABLE tb_unique( Cod INT PRIMARY KEY, Nome VARCHAR(50), CPF CHAR(11) CONSTRAINT UQ_CPF UNIQUE ) ▪ Adicionando unique em um campo já criado ALTER TABLE tb_unique ADD UNIQUE (cpf); ▪ Adicionando unique em um campo já criado. especificando o nome da constraint. ALTER TABLE tb_unique ADD CONSTRAINT UQ_CPF UNIQUE (cpf); Unique 188 ▪ Apagando uma constrait UNIQUE ALTER TABLE tb_unique DROP CONSTRAINT UQ_CPF; ▪ Not Null é a restrição que impede a coluna de receber valores nulos no momento da inserção ou atualização. ▪ O SQL Server coloca como padrão NULL quando não é informado se o campo não pode receber valores nulos. ▪ Quando uma coluna recebe a restrição Primary Key, ele é automaticamente NOT NULL. ▪ Boa prática: Sempre explicitar se o campo é NULL ou NOT NULL na criação da tabela. Not Null 189 ▪ Criando tabela com campos não nulos e campos nulos CREATE TABLE tb_notnull( Cod INT PRIMARY KEY, NOME VARCHAR(50) NOT NULL, DT DATE NULL ) ▪ Colocando not null em uma campo já criado ALTER TABLE tb_notnull ALTER COLUMN DT DATE NOT NULL ▪ Removendo o not null em uma campo já criado ALTER TABLE tb_notnull ALTER COLUMN DT DATE NULL ▪ Restrição que coloca um valor como padrão em uma determinada coluna. ▪ Ao realizar um comando de Insert, e o valor de um determinado campo não é informado, a restrição Default ira preencher o campo não informado com o valor padrão definido pela Constraint. ▪ Obs.: Não é obrigatório informar o campo no momento no INSERT. Default 190 ▪ Criando tabela com a restrição default CREATE TABLE tb_default( Cod INT PRIMARY KEY IDENTITY, Nome VARCHAR(50), dtInsert DATE CONSTRAINT DF_DATA DEFAULT (GETDATE()) , VALOR SMALLMONEY DEFAULT(1000) ) ▪ Adicionando default em uma coluna já criada ALTER TABLE tb_default ADD CONSTRAINT df_dt_insert DEFAULT getdate() FOR dtInsert ▪ Adicionando uma nova coluna com a restrição default. ALTER TABLE tb_default ADD NUMERO TINYINT DEFAULT (0) Default 191 ▪ Apagando constraint DEFAULT ALTER TABLE tb_default ALTER COLUMN dtInsert DROP DEFAULT ▪ A restrição CHECK é usada para limitar os valores que pode ser inseridos em uma coluna. ▪ Se você definir uma restrição CHECK em uma coluna, ela permitirá apenas inserir determinados valores que atendam a lógica da checagem. Check 192 ▪ Criando tabela com o check CREATE TABLE tb_check( Cod INT PRIMARY KEY, NOME VARCHAR(50) NOT NULL, VALOR NUMERIC (6,2) CONSTRAINT CK_VAL CHECK (VALOR > 0), LETRA CHAR(1) CHECK (LETRA IN ('A', 'B', 'C')) ) Check 193 ▪ Adicionando check a um campo já criado. ALTER TABLE tb_check ADD CHECK (valor > 0) ▪ Adicionando check atribuindo nome a constraint ALTER TABLE tb_check ADD CONSTRAINT CHK_VALOR (valor > 0) ▪ Apagando uma constraint check ALTER TABLE tb_check DROP CONSTRAINT CHK_VALOR Desenvolvendo Consultas 194 Objetivo: ▪ Entender aonde cada informação se encaixa dentro de uma consulta para melhor otimiza-la e que faça retornar o resultado esperado. ▪ Abordaremos ▫ Cláusulas de um comando SELECT ▫ Operadores lógicos ▫ Operadores Relacionais ▫ Operadores Aritméticos (ou matemáticos) ▫ Operadores Auxiliares ▫ Operadores Extras O que será abordado sobre consultas 195 CREATE DATABASE BD_CONSULTAS; USE BD_CONSULTAS; CREATE TABLE TB_FUNC ( MATRICULA INT PRIMARY KEY IDENTITY, NOME VARCHAR (255) NOT NULL, DATANASCIMENTO DATE NOT NULL, SALARIO MONEY NULL, DATAADMISSAO DATE DEFAULT (GETDATE()), DATADEMISSAO DATE NULL, INICIOFERIAS DATE NULL, FIMFERIAS DATE NULL, STATUS_FUNC VARCHAR (20) CHECK (STATUS_FUNC IN ('ATIVO','INATIVO','FERIAS','LICENÇA','INSS')), VALOR INT) Script para trabalhar o desenvolvimento de consultas 196 INSERT INTO TB_FUNC (NOME, DATANASCIMENTO,SALARIO,DATAADMISSAO,DATADEMISSAO,INICIOFERIAS,FIMFERIAS, STATUS_FUNC, VALOR) VALUES ('ANA MARIA', '2000-01-01', 1200.55, '2018-05-01',NULL,NULL,NULL, 'ATIVO', 1), ('JOSE HENRIQUE', '1998-11-20', 2575.55, '2005-09-01','2017-12-01',NULL,NULL, 'INATIVO', 7), ('ANA MARIA', '2002-08-21', 950.00, '2019-01-01',NULL,NULL,NULL, 'ATIVO', 6), ('LUAN FELIX', '1991-09-28', 3500.00, '2013-04-01',NULL,NULL,NULL, 'ATIVO', 1), ('FELIPE JOSE DOS SANTOS', '1996-01-11', 4000, '2011-05-01','2015-01-29',NULL,NULL, 'INATIVO', 2), ('MARCELO JOSE', '1980-10-05', 7000, '2000-05-01',NULL,'2019-05-01','2019-06-01', 'ATIVO', 2), ('MARIANA MARIA', '1987-02-08', 4500, '2010-01-01',NULL,NULL,NULL, 'INSS', 3), ('JULIANA MARIA DOS SANTOS', '2002-01-01', 2000, '2017-05-01',NULL,NULL,NULL, 'LICENÇA', 5), ('MARIA ALICIA', '2001-01-01', 950, '2018-05-01',NULL,NULL,NULL, 'ATIVO', 4) INSERT INTO TB_FUNC (NOME, DATANASCIMENTO,SALARIO,STATUS_FUNC, VALOR) VALUES ('MARIA ALICIA', '2003-09-18', 950, 'ATIVO', 3) Script para trabalhar o desenvolvimento de consultas 197 ▪ O comando SELECT possui uma estrutura, e é executado em uma ordem lógica para retornar as informações. ▪ Cláusulas ▫ SELECT – Selecionar a lista de colunas ▫ FROM – Aonde vou buscar meus dados ▫ WHERE – O que eu quero filtras dos dados ▫ GROUP BY – Agrupa os dados ▫ HAVING – Filtra o valor do agrupamento ▫ ORDER BY – Ordena o resultado Entendendo a estrutura de um comando SELECT 198 ▪ Como escrever uma consulta simples SELECT <* / campos_da_tabela> FROM <tabela/view/functions_tablevalue> WHERE <Filtros> ORDER BY <campos> ASC/DESC ▪ Uma consulta é executada logicamente na ordem como é mostrada na imagem ao lado. Entendendo a estrutura de um comando SELECT 199 ▪ ORDER BY: Maneira em que os dados são ordenados após
Compartilhar