cliente produto contem (1,1) realizacao venda (1,n) (0,n) (1,1)

Laboratório de Banco de Dados
Exercicios
Modelo conceitual segundo notação de Peter Chen
cliente
(1,1)
realizacao
(1,n)
venda
(0,n)
produto
(1,1)
contem
Modelo conceitual gerado pela CASE Dezign for Database da Datanamic. Esta ferramenta CASE
gera um modelo segundo a notação de Martin(pé-de-galinha)
Modelo lógico gerado pela CASE Dezign for Database da Datanamic
Obs:
As CASE para Modelagem de Dados não diferenciam os modelos conceitual e lógico.
Elas geram um unico modelo de apresenta caracteristicas do modelo conceitual e logico. No caso da
Datanamic ao gerarmos um modelo sem a definição de chaves primárias podemos nos referir a ele
como modelo conceitual. Ao definirmos as chaves primárias em cada tabela podemos nos referir ao
modelo como modelo lógico.
Para atender a uma demanda, não especificada no modelo conceitual foi criada a tabela
histórico de vendas, no modelo fisico, com a finalidade de reter dados da venda quando esta for
excuida da tabela vendas. O campo usuario tem a finalidade de reter a identificação do usuario que
excluiu a referida venda.
Modelo físico gerado pela CASE Dezign for Database da Datanamic
Foram gerados 4 modelos físicos, para banco dde dados distintos. Observe que apesar
da essencia ser a mesma cada um tem sua particularidade.
Modelo físico para o Postgresql
--------------------------------------------------------------------------------------
/*
/*
/*
/*
/*
/*
/*
File generated
Create-date
Create-time
project-name
project-author
by "DeZign for databases"
:23/5/2006
:18:59:58
:Vendas
:Sidney Vieira da Silva
*/
*/
*/
*/
*/
*/
*/
CREATE SEQUENCE num_venda increment 10 start 10;
CREATE TABLE historico_vendas(
data date DEFAULT 'today',
valor decimal(10,1),
usuario varchar(10));
CREATE TABLE cliente(
codigo serial NOT NULL PRIMARY KEY,
nome varchar(20) NOT NULL,
sexo varchar(1) CHECK (sexo in('f','m')));
CREATE TABLE venda(
numero integer NOT NULL PRIMARY KEY,
data date,
valor decimal(10,2) NOT NULL,
codigo int4 NOT NULL,
tipo varchar(2) DEFAULT 'di' NOT NULL CHECK (tipo in ('di','ch','ca')),
FOREIGN KEY (codigo) REFERENCES cliente (codigo) on delete set null);
CREATE TABLE produto(
codigo int4 NOT NULL PRIMARY KEY,
descricao varchar(20),
quant_disponivel int4);
CREATE TABLE contem(
codigo int4,
numero int4 NOT NULL,
FOREIGN KEY (codigo) REFERENCES produto (codigo) on delete cascade,
FOREIGN KEY (numero) REFERENCES venda (numero) on delete cascade,
PRIMARY KEY (codigo,numero));
--------------------------------------------------------------------------------------------------
Modelo Físico gerado para o SGBD Interbase
--------------------------------------------------------------/*
/*
/*
/*
/*
/*
/*
*/
File generated by "DeZign for databases"
Create-date
Create-time
project-name
project-author
:23/5/2006
:18:59:58
:Vendas
:Sidney Vieira da Silva
CREATE DOMAIN tsexo AS VARCHAR(1)
CHECK (VALUEs in('f','m'));
CREATE DOMAIN tvenda AS VARCHAR(2) DEFAULT 'di'
CHECK (VALUE in ('di','ch','ca'));
*/
*/
*/
*/
*/
*/
CREATE TABLE historico_vendas(
data DATE DEFAULT 'today',
valor DECIMAL(10,1),
usuario VARCHAR(10));
CREATE TABLE cliente(
codigo NUMERIC(18,0) NOT NULL,
nome VARCHAR(20) NOT NULL,
sexo tsexo,
CONSTRAINT PK_cliente1 PRIMARY KEY (codigo));
CREATE GENERATOR cliente_codigo_INC;
SET GENERATOR cliente_codigo_INC TO 0;
SET TERM !! ;
CREATE TRIGGER SET_cliente_codigo FOR cliente
BEFORE INSERT AS
BEGIN
IF (NEW.codigo IS NULL) THEN NEW.codigo = GEN_ID(cliente_codigo_INC, 1);
END !!
SET TERM ; !!
CREATE TABLE venda(
numero INTEGER NOT NULL,
data DATE,
valor DECIMAL(10,2) NOT NULL,
codigo NUMERIC(18,0) NOT NULL,
tipo tvenda NOT NULL,
CONSTRAINT FK_venda_1 FOREIGN KEY (codigo) REFERENCES cliente (codigo),
CONSTRAINT PK_venda1 PRIMARY KEY (numero));
CREATE TABLE produto(
codigo INTEGER NOT NULL,
descricao VARCHAR(20),
quant_disponivel INTEGER,
CONSTRAINT PK_produto1 PRIMARY KEY (codigo));
CREATE TABLE contem(
codigo INTEGER,
numero INTEGER NOT NULL,
CONSTRAINT FK_contem_1 FOREIGN KEY (codigo) REFERENCES produto (codigo),
CONSTRAINT FK_contem_2 FOREIGN KEY (numero) REFERENCES venda (numero),
CONSTRAINT PK_contem1 PRIMARY KEY (codigo,numero));
------------------------------------------------------
Modelo Físico gerado para o SGBD Mysql
----------------------------------------------------------------------------------------------CREATE TABLE historico_vendas(
data DATE DEFAULT 'today',
valor DECIMAL(10,1),
usuario VARCHAR(10));
CREATE TABLE cliente(
codigo INT NOT NULL AUTO_INCREMENT,
nome VARCHAR(20) NOT NULL,
sexo VARCHAR(1),
PRIMARY KEY (codigo),
CHECK (sexos in('f','m')));
CREATE TABLE venda(
numero INT NOT NULL,
data DATE,
valor DECIMAL(10,2) NOT NULL,
codigo INT NOT NULL,
tipo VARCHAR(2) DEFAULT di NOT NULL,
FOREIGN KEY (codigo) REFERENCES cliente (codigo),
PRIMARY KEY (numero),
CHECK (tipo in ('di','ch','ca')));
CREATE TABLE produto(
codigo INT NOT NULL,
descricao VARCHAR(20),
quant_disponivel INT,
PRIMARY KEY (codigo));
CREATE TABLE contem(
codigo INT,
numero INT NOT NULL,
FOREIGN KEY (codigo) REFERENCES produto (codigo),
FOREIGN KEY (numero) REFERENCES venda (numero),
PRIMARY KEY (codigo,numero));
---------------------------------------------------------------------------------------------------
Modelo Físico gerado para o SGBD MS SQL-Server
---------------------------------------------------------------------------------------------------/*
/*
/*
/*
/*
/*
/*
File generated
Create-date
Create-time
project-name
project-author
by "DeZign for databases"
:23/5/2006
:18:59:58
:Vendas
:Sidney Vieira da Silva
CREATE TABLE historico_vendas(
data datetime DEFAULT 'today' NULL,
valor decimal(10,1) NULL,
usuario varchar(10) NULL)
go
*/
*/
*/
*/
*/
*/
*/
CREATE TABLE cliente(
codigo int identity(1,1) NOT NULL CONSTRAINT PK_cliente1 PRIMARY KEY,
nome varchar(20) NOT NULL,
sexo varchar(1) NULL,
CONSTRAINT CK_cliente1 CHECK (sexos in('f','m')))
go
CREATE TABLE venda(
numero int NOT NULL CONSTRAINT PK_venda1 PRIMARY KEY,
data datetime NULL,
valor decimal(10,2) NOT NULL,
codigo int NOT NULL,
tipo varchar(2) DEFAULT di NOT NULL,
CONSTRAINT CK_venda1 CHECK (tipo in ('di','ch','ca')))
go
CREATE TABLE produto(
codigo int NOT NULL CONSTRAINT PK_produto1 PRIMARY KEY,
descricao varchar(20) NULL,
quant_disponivel int NULL)
go
CREATE TABLE contem(
codigo int NULL,
numero int NOT NULL,
CONSTRAINT PK_contem1 PRIMARY KEY (codigo,numero))
go
ALTER TABLE venda
ADD CONSTRAINT FK_venda_1
FOREIGN KEY (codigo) REFERENCES cliente (codigo)
go
ALTER TABLE contem
ADD CONSTRAINT FK_contem_1
FOREIGN KEY (codigo) REFERENCES produto (codigo)
go
ALTER TABLE contem
ADD CONSTRAINT FK_contem_2
FOREIGN KEY (numero) REFERENCES venda (numero)
go
----------------------------------------------------------------
Considerações sobre os modelos gerados por meio do dezign for database
1. cada modelo físico é considerado um scritp para criação do banco de dados em questão
2. O programa datanamic pode estar gerando um script para uma versão do SGBD que não seja
a ultima versão, ou a versão que voce irá utilizar
3. A sequencia do script para criação do Banco de Dados no Postgresql, não foi gerada pelo
programa Datanamic, ele foi inserida manualmente apois a geração do script pelo referido
programa, não constando assim nos demais scritp
EXERCICIOS:
1- Determine a quantidade de comandos SQL em cada script do modelo físico
2 – utilize o script create.sql para gerar o Banco de Dados vendas no Postgresql
3- utilize o script inserir.sql para inserir dados no banco de dados vendas
4 – Efetue consultas para:
a) Exibir vendas(numero, data, valor e tipo) e o nome dos respectivos produtos contidos nas vendas
b) Exibir o nome dos clientes que efetuaram compras entre 20 de janeiro de 2006 e 20 de junho de
2006
c) Exibir o nome do cliente que efetuaram compras em cheque, ou seja compras do tipo ch
d) Exibir a venda que contem o produto mais caro
e) Exibir a venda que possui o produto mais barato
f) Exibir o nome do produto mais caro
g) Exibir o nome do produto mais barato
h) Exibir os nomes dos clientes que realizaram compras de maior valor
i) Exibir os nomes dos clientes que realizaram compras de menor valor
j) Exibir os nomes dos clientes que compraram arroz
k) Exibir os nome de cliente que compraram arroz em 20 de janeiro de 2005
l) Exibir os nomes dos produtos comprados pelo cliente ze
m) Exibir os nomes dos produtos e dos clientes que realizou a compra 01