CREATE DATABASE Veterinaria;
USE Veterinaria;

Create table Veterinario(
   id int auto_increment,
   crv char(6) unique,
   nome char(40),
   salario decimal,
   dataadmissao date ,
   primary key (id));

CREATE TABLE Fabricante
(id int auto_increment,
 nome varchar(50) unique,
 PRIMARY KEY (id));

CREATE TABLE Vacina
( id int auto_increment,
  nome VARCHAR(50) unique ,
  dataFabricante date,
  datavencimento date,
  lote char(6) ,
   idfabricante int,
  PRIMARY KEY (id),
  FOREIGN KEY (idfabricante) REFERENCES Fabricante (id));

CREATE TABLE Medicamento
(id int auto_increment,
 nome VARCHAR(50) unique,
 dataFabricacao date,
 dataVencimento date,
 preco decimal,
 idFabricante int,
 PRIMARY KEY(id),
 FOREIGN KEY (idFabricante) REFERENCES Fabricante (id));

create table animal
(id int auto_increment,
registro char(5),
nome_animal varchar(50),
data_nascimento date,
primary key (id));

create table cliente
(id int auto_increment,
nome varchar(30),
CPF varchar(14) unique,
telefone varchar (9),
endereco varchar (25),
primary key (id));

insert into Fabricante values (null, 'x');
insert into Fabricante values (null, 'y');
insert into Medicamento values (null, 'Tylenol', '2000-12-01', '2006-06-05', 3.0, 1);
insert into Medicamento values (null, 'Benegripe', '2000-12-01', '2006-06-05', 5.0, 2);

INSERT INTO FABRICANTE VALUES ( NULL,  "Adjenane Vidal");

INSERT INTO FABRICANTE VALUES ( NULL,  "Camila Aparecida");


INSERT INTO VACINA VALUES ( NULL,  "Anti-carrapatica", '2006-04-01', '2008-04-01', "1b23cd", 1);

INSERT INTO VACINA VALUES ( NULL,  "Anti-rabica", '2006-04-01', '2008-04-01', "1b896e", 1);

INSERT INTO VACINA VALUES ( NULL,  "Anti-Alergica", '2005-06-01', '2012-08-01', "1edujt", 2);

INSERT INTO VACINA VALUES ( NULL,  "lesquistopirose", '2007-05-02', '2010-03-01', "1n83lj", 2);

insert into animal values (null, "00001","Shelly", "1998-09-02");
insert into animal values (null, "00002","Boris", "1994-08-27");  

insert into cliente values (null, "Ingrid Rodrigues","116.381.885.69", "2440-5849", "Estrada do Boiuna");
insert into cliente values (null, "Camila Aparecida","116.381.852.63", "3866-1590", "Higienópolis");
insert into cliente values (null, "Adjenane Vidal","223.663.587.14", "3325-9988", "Niterói");

insert into Veterinario values(null, "01","Danielle","1200","2006/03/12");

