Banco de Dados Structured Query Language – SQL/DDL Prof. MS Wagner Siqueira Cavalcante O sistema de uma publicadora cadastra editoras que editam livros (ou mesmo que ainda não os tenham publicado, ou mesmo que não cheguem a ser publicados), cada qual podendo ter sido escrito por um ou mais autores, os quais serão cadastrados assim que escreverem seu primeiro de muitos livros, sendo que cada um está associado a um único assunto, previamente cadastrado, mesmo que ainda não haja livros associados. Criação das estruturas das tabelas: Tabela Coluna Tipo de Dados Editora Código Numérico inteiro com valor máximo inferior a 100 Nome Cadeia de caracteres de tamanho variável, com tamanho máximo igual a 80 caracteres Assunto Sigla Cadeia de caracteres de tamanho fixo igual a 1 caractere Descrição Cadeia de caracteres de tamanho variável, com tamanho máximo igual a 50 caracteres Autor Matrícula Numérico inteiro com valor máximo inferior a 1000 Nome Cadeia de caracteres de tamanho variável, com tamanho máximo igual a 80 caracteres CPF Cadeia de caracteres de tamanho fixo igual a 11 caracteres Endereço Cadeia de caracteres de tamanho variável, com tamanho máximo igual a 120 caracteres Data nascimento Data Nacionalidade Cadeia de caracteres de tamanho variável, com tamanho máximo igual a 30 caracteres (nome do País) Livro Código Numérico inteiro com valor máximo inferior a 1000 Título Cadeia de caracteres de tamanho variável, com tamanho máximo igual a 80 caracteres Preço Número real Lançamento Data Assunto 0 mesmo tipo de dados da coluna SIGLA da tabela ASSUNTO Editora 0 mesmo tipo de dados da coluna CÓDIGO da tabela EDITORA Autor_Livro Código_livro 0 mesmo tipo de dados da coluna CÓDIGO da tabela LIVRO Matrícula 0 mesmo tipo de dados da coluna MATRICULA da tabela AUTOR Req Sim Sim Sim Sim Sim Sim Sim Sim Sim Sim Sim Sim Não Não Sim Não Sim Sim Banco de Dados Structured Query Language – SQL/DDL Prof. MS Wagner Siqueira Cavalcante 1. Criar Tabela Editora: CREATE TABLE Editora ( Codigo SMALLINT Nome VARCHAR (50) → OU ← CREATE TABLE Editora ( Codigo SMALLINT Nome VARCHAR (50) CONSTRAINT NOT NULL, NOT NULL); -- PK será criada em “ALTER TABLE”… ← Execute e Commit NOT NULL, NOT NULL, PK_Editora PRIMARY KEY (Codigo)); → OU ← CREATE TABLE Editora ( Codigo SMALLINT CONSTRAINT Nome VARCHAR (50) NOT NULL PK_Livro PRIMARY KEY, NOT NULL); → OU ← CREATE TABLE Editora ( Codigo SMALLINT Nome VARCHAR (50) ALTER TABLE Editora ADD CONSTRAINT PRIMARY KEY 2. Criar Tabela Livro: CREATE TABLE Livro ( Codigo SMALLINT Titulo VARCHAR(80) Preco NUMERIC(10,2), Lancamento DATE, Assunto CHAR (01) Editora SMALLINT, CONSTRAINT PK_Livro CONSTRAINT FK_Assunto CONSTRAINT FK_Editora NOT NULL, NOT NULL); PK_Editora (Codigo); NOT NULL, NOT NULL, NOT NULL, PRIMARY KEY (Codigo), FOREIGN KEY (Assunto) REFERENCES Assunto(Sigla), FOREIGN KEY (Editora) REFERENCES Editora (Codigo)); ← Execute e Commit ERRO: Esta tabela só pode ser criada após Assunto e Editora, de onde vêm ambas as chaves estrangeiras! 3. 4. 5. Criar Tabela Assunto: CREATE TABLE Assunto ( Sigla CHAR (01) Descrição VARCHAR (50) CONSTRAINT PK_Assunto NOT NULL, NOT NULL, PRIMARY KEY (Sigla)); ← Execute e Commit Criar Tabela Autor: CREATE TABLE Autor ( Matricula SMALLINT Nome VARCHAR(80) CPF CHAR (11) Endereco VARCHAR (120) Nascim DATE Nacional VARCHAR (30) CONSTRAINT PK_Autor NOT NULL, NOT NULL, NOT NULL UNIQUE, NOT NULL, NOT NULL, NOT NULL, PRIMARY KEY (Matricula)); ← Execute e Commit NOT NULL, NOT NULL, FOREIGN KEY (Codigo_Livro) REFERENCES Livro (Codigo), FOREIGN KEY (Matricula) REFERENCES Autor); ← Execute e Commit Criar Tabela Autor-Livro: CREATE TABLE Autor_Livro ( Codigo_Livro SMALLINT Matricula SMALLINT CONSTRAINT FK_Livro CONSTRAINT FK_Autor Banco de Dados Structured Query Language – SQL/DDL Prof. MS Wagner Siqueira Cavalcante 6. Alterar estruturas de tabelas: 6.1. Definir a chave primária da tabela Editora: ALTER TABLE Editora ADD CONSTRAINT PK_Editora PRIMARY KEY (Codigo); ← Execute e Commit 6.2. Inserir o atributo "Sexo" à tabela Autor, com "F" como padrão: ALTER TABLE Autor ADD Sexo CHAR DEFAULT 'F'; -- isto faz com que o atributo “sexo” de tupla vazia já venha com “F” 6.3. Adicionando a restrição de sexo (= 'F' ou 'M'): ALTER TABLE Autor ADD CONSTRAINT CK_Autor CHECK (Sexo in ('F', 'M')); → Experimente inserir dados para um Autor, com sexo diferente de “F” ou “M” (mesmo “f” ou “m” minúscula). 6.4. Removendo a restrição de sexo = 'F' ou 'M' ALTER TABLE Autor DROP CONSTRAINT CK_Autor; -- não checa mais se sexo é “F” ou “M”… 6.5. Excluindo o atributo "Sexo" da tabela autor (página 36) ALTER TABLE Autor DROP Sexo; -- retira o atributo “sexo” da tabela, mesmo que com dados… 7. Remover estruturas de tabelas: 7.1. Remover a estrutura da tabela Blablabla: DROP TABLE Blablabla /* Destrói a tabela “Blablabla” e todos os seus dados, caso haja */