Baixe o app para aproveitar ainda mais
Prévia do material em texto
Modelagem de Dados MODELAGEM DE DADOS MODELO FÍSICO Profa. Rosemary Melo Modelagem de Dados 2 Objetivo Elaborar Modelos Físicos de Dados Migrando do Modelo Lógico ou Criando as tabelas diretamente no SGBD 2 MODELO FÍSICO Modelagem de Dados 3 Na elaboração do Modelo Físico precisamos Definir as tabelas no SGBD escolhido Criar as Chaves Definir restrições Definir índices ... 3 MODELO FÍSICO Modelagem de Dados MODELO FÍSICO DEFINIÇÃO DE RESTRIÇÕES DO MODELO RELACIONAL Restrições podem ser definidas: No nível de coluna No nível de tabela (exceto NOT NULL) Restrições podem ser criadas: No momento em que a tabela for criada (CREATE TABLE) Depois que a tabela tiver sido criada (ALTER TABLE) Tipos de restrição: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK Modelagem de Dados MODELO FÍSICO DEFINIÇÃO DE ÍNDICES O que é um Índice? Mecanismos utilizados para acelerar o acesso aos dados. Um arquivo de índice consiste de registros (chamados de entradas de índice) na forma: Search Key (Chave de Busca) – Atributo ou conjunto de atributos usados para procurar registros em um arquivo. Ponteiro – Localização do registro no arquivo de dados Três tipos básicos de índices: •Índices Ordenados: as chaves de busca são armazenadas de forma ordenada. •Índices Hash: as chaves de busca são distribuídas uniformemente em “buckets” usando uma “função hash”. •Índices “Mapeados a Bit”: utiliza um único bit para representar a ocorrência de determinado valor Modelagem de Dados MODELO FÍSICO DEFINIÇÃO DE ÍNDICES Três tipos básicos de índices: Índices Ordenados: as chaves de busca são armazenadas de forma ordenada. Índices Hash: as chaves de busca são distribuídas uniformemente em “buckets” usando uma “função hash”. Índices “Mapeados a Bit”: utiliza um único bit para representar a ocorrência de determinado valor. Modelagem de Dados MODELO FÍSICO DEFINIÇÃO DE ÍNDICES Quando criar um Índice? São criados automaticamente quando definimos PRIMARY KEY ou UNIQUE KEY. Sempre criar para colunas Foreign Key. Coluna contiver um grande número de valores nulos. Colunas forem usadas com freqüência em uma cláusula WHERE . A tabela for grande e a expectativa for de que a maioria das consultas recuperará poucas linhas. Observação: Devem ser criados com moderação pois podem afetar o desempenho de comandos DML (Insert, Update e Delete). Modelagem de Dados INTRODUÇÃO À SQL LINGUAGENS DE DEFINIÇÃO E MANIPULAÇÃO DE DADOS Os SGBDs possuem duas linguagens: DDL (Linguagem de Definição de Dados): usada para definir os esquemas, atributos, regras de integridade, índices, etc. DML (Linguagem de Manipulação de Dados): usada para ter acesso aos dados armazenados no BD. Exemplo de linguagem de comercial que implementam DDL e DML no Modelo Relacional: QUEL QBE SQL ... Modelagem de Dados INTRODUÇÃO À SQL SQL – STRUCTURED QUERY LANGUAGE Considerada linguagem de consulta padrão para SGBDR’S. Inicialmente chamada de SEQUEL. Desenvolvida pela IBM na década de 1970. Aceita por quase todos os produtos (SQL server, Oracle, Interbase, etc) Utiliza os termos tabela, linha e coluna no lugar de relação, tupla e atributo, respectivamente. . Modelagem de Dados INTRODUÇÃO À SQL SQL – STRUTURED QUERY LANGUAGE Possui diversas partes: Linguagem de Definição de Dados (DDL): fornece comandos para definições de esquemas de relação, criação/remoção de tabelas, criação de índices e modificação de esquemas. Linguagem de Manipulação de Dados (DML): inclui uma linguagem de consulta baseada na álgebra relacional e cálculo relacional de tupla. Compreende comandos para inserir, consultar, remover e modificar tuplas num BD. Linguagem de Manipulação de Dados Embutido: designada para acessar o BD dentro de linguagem de programação de uso geral como Cobol, C, Pascal, PL-1, entre outros. Modelagem de Dados INTRODUÇÃO À SQL SQL – DDL Inclui operações de definição de dados: criação de tabelas Alteração de tabelas Eliminação de tabelas Os comandos para definições de dados são: CREATE TABLE: criar uma tabela. ALTER TABLE: altera a definição (esquema) de uma tabela. DROP TABLE: elimina uma tabela. Modelagem de Dados INTRODUÇÃO À SQL DDL – CRIAÇÃO DE TABELA CREATE TABLE: especifica uma nova relação, dando o seu nome e especificando os seus atributos (cada um com seu nome, tipo de dado e algumas restrições). Sintaxe: CREATE TABLE tabela_base (colunas tabela-base) Modelagem de Dados INTRODUÇÃO À SQL DDL – CRIAÇÃO DE TABELA As definições das colunas têm o seguinte formato: coluna tipo_de_dados [NOT NULL [UNIQUE]] Onde: coluna: nome do atributo que está sendo definido tipo_de_dado: domínio do atributo NOT NULL: expressa que o atributo não pode receber valores nulos. UNIQUE: indica que o atributo tem valor único na tabela. Qualquer tentativa de se introduzir uma linha na tabela contendo um valor igual ao do atributo será respeitado. Usamos o qualificador UNIQUE para indicar os atributos que fazem parte de chave primária. Uma outra forma de fazê-lo seria usar o qualificador primary key. Modelagem de Dados INTRODUÇÃO À SQL EXEMPLO DE CRIAÇÃO DE TABELA: Seja o seguinte BD de Peças e Fornecedores CREATE TABLE Fornecedor (CodForm char(5) NOT NULL, Nome char(20), Situação decimal(3), Cidade char(15), PRIMARY KEY (CodForn)) CREATE TABLE Peça (CodPeca char(6) NOT NULL, Nome char(10), Cor char(10), Peso decimal(3), Cidade char(15), UNIQUE(CodPeca)) CREATE TABLE Malote (CodigoF char(5) NOT NULL, CodigoP char(6) NOT NULL, Qtd decimal(5), PRIMARY KEY (CodigoF, CodigoP)) Modelagem de Dados INTRODUÇÃO À SQL DDL – DEFINIÇÃO DE TIPO DE DADOS Tipos de Dados A SQL ANSI suporta os seguintes tipos de dados: character numeric decimal integer smallint float real double precision Não ANSI varchar date time Modelagem de Dados INTRODUÇÃO À SQL DDL – CRIAÇÃO DE CHAVE ESTRANGEIRA Se quisermos criar chaves estrangeiras para que a integridade referencial seja validada, usa-se a cláusula FOREIGN KEY. Exemplo: CREATE TABLE Malote (codigof char(5) NOT NULL, codigo p char(6) NOT NULL, quantidade decimal(5), PRIMARY KEY (codigof, codigop)) FOREIGN KEY (codigof) REFEENCES Fornecedor FOREIGN KEY (codigop) REFEENCES Peca Modelagem de Dados INTRODUÇÃO À SQL DDL – CRIAÇÃO DE CHAVE ESTRANGEIRA Exemplo: CREATE TABLE Malote (codigof char(5) NOT NULL REFERENCES Fornecedor, codigop char(6) NOT NULL REFERENCES Peca, quantidade decimal(5), PRIMARY KEY (codigof, codigop)) Pode-se abreviar desta maneira se a chave estrangeira for composta de um único atributo. Modelagem de Dados INTRODUÇÃO À SQL DDL – ALTERAÇÃO DE TABELA ALTER TABLE: permite que se adicione novos atributos a uma determinada tabela. Os novos atributos terão valores nulos em todas as linhas. Ao incluir uma coluna deve-se especificar o tipo de dado. Sintaxe: ALTER TABLE tabela_base ADD atributo domínio ou ALTER TABLE tabela_base DROP atributo domínio : : Modelagem de Dados INTRODUÇÃO À SQL DDL – ALTERAÇÃO DE TABELA Exemplo: ALTER TABLE Peca ADD Espessura int ALTER TABLE Peca DROP Cidade ALTER TABLE Peca MODIFY Espessura float Modelagem de Dados INTRODUÇÃO À SQL DDL – ALTERAÇÃO DE TABELA Pode-se usar ALTER TABLE para definir chaves primárias e estrangeiras. Exemplo: ALTER TABLE Malote ADD PRIMARY Key (CodigoF) ALTER TABLE Malote ADD FOREIGN KEY (CodigoF) REFERENCES Fornecedor ALTER TABLE Malote DROP PRIMARY KEY ALTER TABLE Malote DROP FOREIGN KEY (CodigoF) Modelagem de Dados INTRODUÇÃO À SQL DDL – ALTERAÇÃODE TABELA Quando uma chave primária ou estrangeira é eliminada com ALTER TABLE, as colunas da chave e seus valores não são eliminados, mas sim a restrição. Modelagem de Dados INTRODUÇÃO À SQL DDL – REMOÇÃO DE TABELA DROP TABLE: exclui uma tabela-base do BD. Remove tanto os dados quanto a definição da tabela. Não permite apagar a tabela se esta estiver sendo referenciada por outra tabela (foreing key) Sintaxe: DROP TABLE tabela_base Modelagem de Dados INTRODUÇÃO À SQL DDL – REMOÇÃO DE TABELA Exemplo: DROP TABLE Malote DROP TABLE Peca DROP TABLE Fornecedor Modelagem de Dados Exercicios 24 • Considere uma loja virtual. A loja trabalha com diversos produtos. Cada produto contem um codigo, nome, descricao, preco, fornecedor e quantidade disponivel em estoque. Os produtos tambem são categorizado com codigo e categoria. Ex. produtos de informática, eletrodomesticos, livros, etc. Muitos fornecedores podem fornecer o mesmo produto. Assim, a especificação de um produto depende do fornecedor que o produz. • Os clientes compram via internet. Por isso precisam se cadastrar (cpf, nome, email, telefone e endereco). Eles colocam produtos em um carrinho e depois concluem ou desistem da compra. Quando uma compra é efetivada é gerada uma nota fiscal, calculado o valor total da compra e guardada a data da compra. A compra é paga sempre em cartao de credito. Deve ser guardado o número do cartão e o código da autorização obtida da operadora de cartão. • Para o cenário acima, construa: Modelo conceitual, Modelo lógico e Modelo Físico. Modelagem de Dados INTRODUÇÃO À SQL SQL – DML Após a definição do banco de dados inicia-se a manipulação dos dados através das seguintes operações: Consulta dados de tabelas Insere dados em tabela Altera dados em tabela Exclui dados em tabela As operações de manipulação são: SELECT: consulta dados em tabela. INSERT: insere dados em tabela. UPDATE: altera dados em tabela. DELETE: exclui dados em tabela. Modelagem de Dados INTRODUÇÃO À SQL SQL – DML Permite a realização de operações de restrição, projeção, junção, através das instruções SQL. Exemplo: Restrição Select Fcod, Pcod, Qtd From prod_fornec Where Qtd<150 Projeção Select Fcod, Cidade From Fornecedor Modelagem de Dados INTRODUÇÃO À SQL SQL – DML Exemplo: Junção Select Fornecedor.Fcod, Fnome, Status, Cidade, Pcod, Qtd From Fornecedor, Prod_fornec Where Fornecedor.Fcof = Prod_forncec.Fcof Modelagem de Dados INTRODUÇÃO À SQL SQL – DML Outros exemplos de manipulação de dados Select * from fornecedor Insert into fornecedor (Fcod, Fnome, Status, cidade) Values (1, 'bompreço’,2,’Salvador’) Insert into temp (Pcod, peso) Select Pcod, peso From produto Where cor=cor(‘vermelho’) Modelagem de Dados INTRODUÇÃO À SQL SQL – DML Outros exemplos de manipulação de dados Delete from prod_fornec where Pcod=‘P2’ Update fornecedor Set status = 2 * status, cidade = ‘Roma’ Where cidade=‘Paris’ Modelagem de Dados Sintaxe SQL - Insert • Utilizado para inserir dados em uma tabela – Não é obrigatório colocar todos os campos – A ordem dos campos não precisa ser a mesma ordem da tabela – Quando se deseja incluir valores para todos os campos, pode-se emitir a lista de campos. Neste caso é assumido a ordem dos campos da tabela INSERT INTO nome_da_tabela (campo1, ..., campon) VALUES (valor1,...,valorn) Ex.: jinto fornecedor (Fcod, Fnome, Status, cidade) Values (1, ‘bompreço’,2,’Salvador’) No SQLServer INSERT into cliente (cliente, nome, telefone) values (1001,'João','445-0988') INSERT into cliente (nome, cliente, telefone) values ('Carlos',1005,null) INSERT into cliente values (1006,'Viu só?','999-0000') Modelagem de Dados Sintaxe SQL - Update • Utilizado para alterar os dados já cadastrados nas tabelas – A condição é opcional, mas quando não especificada indica uma alteração em todos os registros da tabela! UPDATE nome_da_tabela SET nome_do_campo1 = novo_valor1, ..., nome_do_campon=novo_valorn [WHERE condição] Ex.: update fornecedor set status = 2 * status, cidade = ‘Roma’ where cidade=‘Paris’ No SQLServer UPDATE cliente SET telefone = ‘000-1111’ UPDATE cliente SET telefone = '111-0000' WHERE cliente = 1004 Modelagem de Dados Sintaxe SQL - Delete • Utilizado para excluir registros já cadastrados nas tabelas. – A condição é opcional, mas quando não especificada indica uma exclusão de todos os registros da tabela! DELETE FROM nome_da_tabela [WHERE condição] Ex.: Delete from prod_fornec where Pcod=‘P2’ No SQLServer DELETE cliente WHERE matr = 1006 DELETE cliente Modelagem de Dados Sintaxe SQL - Select • Utilizado para recuperar dados cadastrados no BD – Pode utilizar critérios para a recuperação SELECT lista_de_campos FROM lista_de _tabelas WHERE condições Ex.: Select * from fornecedor No SQLServer SELECT nome, telefone FROM cliente SELECT * FROM cliente SELECT * FROM cliente WHERE matr = 1500 Modelagem de Dados SQL - Exercícios • Criar o seguinte modelo no SGBD adotado Campo Tipo Chave Req Matricula Inteiro PK Sim Nome String(40) Sim Telefone String(11) Sim DataEntrada Date Não Aluno Campo Tipo Chave Req Registro Autoincremento PK Sim Nome String(40) Sim Telefone String(11) Sim Salario Valor Sim Professor Campo Tipo Chave Req CodDisc Autoincremento PK Sim Nome String(40) Sim CargaHor Int Sim Disciplina Turma Campo Tipo Chave Req CodTurma Autoincremento PK Sim Registro Int FK Sim Semestre Int Sim Ano int Sim CodDisc Int FK Sim Campo Tipo Chave Req CodTurma Int PK, FK Sim Matricula Int PK,FK Sim Media Valor Não Faltas Int Nao Aluno_turma Modelagem de Dados SQL - Exercícios 35 • Construir os comandos de inclusão para cada uma das tabelas criadas no exercício anterior: – Tabela Aluno • Mat:1 Nome: Maria Telefone: 2222-3344 – Tabela Professor • Registro 1 nome: Ana Telefone: 555-6677 Salario:1000 – Tabela Disciplina • Codigo 1 Nome:Banco1 CargaHor: 60 – Tabela Turma • CodTurma 1 Registro 1 Semestre 2 Ano 2012 CodDisc 1 – Tabela AlunoTurma • CodTurma 1 Matricula 1 Media 8 Faltas 5 Modelagem de Dados SQL - Exercícios 36 • Construir os comandos de alteração para cada uma das tabelas criadas no exercício anterior. Considerar alteração de um único registro por vez. – Tabela Aluno • Alterar o telefone de um aluno – Tabela Professor • Alterar o salario de todo os professores para 10000 – Tabela Disciplina • Altarar o nome e a carga horaria de uma disciplina – Tabela Turma • Alterar o ano da turma – Tabela AlunoTurma • Alterar a media e as faltas de um aluno Modelagem de Dados SQL - Exercício 37 • Construir os comandos de exclusão para cada uma das tabelas criadas no exercício anterior. Considerar alteração de um único registro por vez. – Tabela Aluno • Excluir um aluno – Tabela Professor • Excluir um professor – Tabela Disciplina • Excluir uma disciplina – Tabela Turma • Excluir uma turma – Tabela AlunoTurma • Excluir um aluno da turma – Delete AlunoTurma where codTurma=1 and Mat=1 Modelagem de Dados Para refletir • Considere as tabelas abaixo codTurma Matricula Media Faltas 1 1 10 0 1 2 7 6 1 3 5 2 2 1 9 0 2 3 8 2 2 4 7 0 O que aconteceria se os seguintes comandos fossem executados: Insert into TurmaAluno (1,5,3,8) Update turmaAluno set faltas=9 Delete turmaAluno where codTurma=2 and Matricula=3 Update turmaAluno set Media=10 where codTurma=2 and Matricula=5 Modelagem de Dados SQL – Exercício• Considere o banco Acadêmico do exercício anterior – Criar as seguintes sentenças: • Consultar todos os alunos • Consultar nome e telefone de todos os alunos • Consultar nome e salário de todos os professores • Consultar carga horária de uma disciplina qualquer (informando o codigo da disciplina) Modelagem de Dados SQL – Exercício Lembrete: - Se for usar o SGBD MySQL e quiser executar um arquivo com todos os scripts SQL, você deve primeiro salvar o script no bloco de notas com extensão SQL, no mesmo diretório do mysql: \mysql\bin\nomedoarquivo.sql. Em sequida você deve executar o mysql, depois executar no prompt do mysql os seguintes comandos: mysql> source nome do arquivo.sql. - Para executar o mysql, primeiro você deve executar o arquivo mysqld para em seguida executar o arquivo mysql, ambos estão dentro do diretório bin do mysql. Modelagem de Dados SQL – Exercício Comandos no MySql: - use NomedoBanco: seta o banco de dados que irá usar. - show Database: lista todos os banco de dados do SGBD. - show tables: lista as tabelas do banco em uso. - show columns from nometabela: mostra todas as colunas criadas para a tabela. (4.6.8.1. Recureparando Informações sobre Banco de Dados, Tabelas, Colunas e Índices – Manual MySql). Modelagem de Dados SQL – Exercício Comandos SQL no MySql: - Caso queira renomear uma coluna de uma tabela para outro nome especificando seu tipo. Ex.: ALTER TABLE t1 CHANGE a b INTEGER; - Caso queira mudar um tipo de coluna, mas não o nome. Usando o comando ALTER: Ex1.:ALTER TABLE t1 CHANGE b b BIGINT NOT NULL; Ex2.: ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
Compartilhar