Banco de dados para cadastro de livro (Diagrama do Schema e Script de criação do banco de dados)

Link do gitHub para fazer download : https://github.com/LaboratorioDeInformatica/sql

Diagrama do Schema Books.

Script para gerar as tabelas e popular as tabelas.

+CREATE DATABASE IF NOT EXISTS books;
+
+USE books;
+
+DROP TABLE IF EXISTS authorISBN;
+DROP TABLE IF EXISTS titles;
+DROP TABLE IF EXISTS authors;
+DROP TABLE IF EXISTS publishers;
+
+CREATE TABLE publishers (
+ publisherID INT NOT NULL AUTO_INCREMENT,
+ publisherName varchar (30) NOT NULL,
+ PRIMARY KEY (publisherID)
+) ENGINE = INNODB
+;
+
+CREATE TABLE authors (
+ authorID INT NOT NULL AUTO_INCREMENT,
+ firstName varchar (20) NOT NULL,
+ lastName varchar (30) NOT NULL,
+ PRIMARY KEY (authorID)
+) ENGINE = INNODB
+;
+
+CREATE TABLE titles (
+ isbn varchar (20) NOT NULL,
+ title varchar (100) NOT NULL,
+ editionNumber INT NOT NULL,
+ copyright varchar (4) NOT NULL,
+ publisherID INT NOT NULL,
+ imageFile varchar (20) NOT NULL,
+ price REAL NOT NULL,
+ PRIMARY KEY (isbn),
+ INDEX (publisherID),
+ FOREIGN KEY (publisherID) REFERENCES publishers(publisherID)
+) ENGINE = INNODB;
+;
+CREATE TABLE authorISBN (
+ authorID INT NOT NULL,
+ isbn varchar (20) NOT NULL,
+ INDEX (authorID),
+ FOREIGN KEY (authorID) REFERENCES authors (authorID), 
+ INDEX (isbn),
+ FOREIGN KEY (isbn) REFERENCES titles (isbn)
+) ENGINE = INNODB;
+;
+
+insert into publishers (publisherName) values ('Prentice Hall')
+;
+insert into publishers (publisherName) values ('Prentice Hall PTG')
+;
+
+insert into authors (firstName,lastName) values ('Harvey','Deitel')
+;
+insert into authors (firstName,lastName) values ('Paul','Deitel')
+;
+insert into authors (firstName,lastName) values ('Tem','Nieto')
+;
+insert into authors (firstName,lastName) values ('Sean','Santry')
+;
+
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0131426443','C How to Program',4,'2004',1,'chtp4.jpg',85.00)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130895725','C How to Program',3,'2001',1,'chtp3.jpg',69.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0132261197','C How to Program',2,'1994',1,'chtp2.jpg',49.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130384747','C++ How to Program',4,'2003',1,'cpphtp4.jpg',85.00)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130895717','C++ How to Program',3,'2001',1,'cpphtp3.jpg',69.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID, imageFile,price) values ('0135289106','C++ How to Program',2,'1998',1,'cpphtp2.jpg',49.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('013100252X','The Complete C++ Training Course',4,'2003',2,'cppctc4.jpg',109.99)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0139163050','The Complete C++ Training Course',3,'2001',2,'cppctc3.jpg',109.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('013028419x','e-Business and e-Commerce How to Program',1,'2001',1,'ebechtp1.jpg',69.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0131450913','Internet and World Wide Web How to Program',3,'2004',1,'iw3htp3.jpg',85.00)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130308978','Internet and World Wide Web How to Program',2,'2002',1,'iw3htp2.jpg',69.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130161438','Internet and World Wide Web How to Program',1,'2000',1,'iw3htp1.jpg',69.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130856118','The Complete Internet and World Wide Web Programming Training Course',1,'2000',2,'iw3ctc1.jpg',109.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0131483986','Java How to Program',6,'2005',1,'jhtp6.jpg',85.00)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0131016210','Java How to Program',5,'2003',1,'jhtp5.jpg',85.00)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130341517','Java How to Program',4,'2002',1,'jhtp4.jpg',85.00)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130125075','Java How to Program (Java 2)',3,'2000',1,'jhtp3.jpg',69.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0138993947','Java How to Program (Java 1.1)',2,'1998',1,'jhtp2.jpg',49.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130852473','The Complete Java 2 Training Course',3,'2000',2,'javactc3.jpg',109.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130829277','The Complete Java Training Course (Java 1.1)',2,'1998',2,'javactc2.jpg',99.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0134569555','Visual Basic 6 How to Program',1,'1999',1,'vbhtp1.jpg',69.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130829293','The Complete Visual Basic 6 Training Course',1,'1999',2,'vbctc1.jpg',109.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130284173','XML How to Program',1,'2001',1,'xmlhtp1.jpg',69.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130284181','Perl How to Program',1,'2001',1,'perlhtp1.jpg',69.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130895601','Advanced Java 2 Platform How to Program',1,'2002',1,'advjhtp1.jpg',69.95)
+;
+insert into titles (isbn,title,editionNumber,copyright,publisherID,imageFile,price) values ('0130461342','Java Web Services for Experienced Programmers',1,'2003',1,'jwsfep1.jpg',54.99)
+;
+
+insert into authorISBN (authorID,isbn) values (1,'0130895725')
+;
+insert into authorISBN (authorID,isbn) values (1,'0132261197')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130895717')
+;
+insert into authorISBN (authorID,isbn) values (1,'0135289106')
+;
+insert into authorISBN (authorID,isbn) values (1,'0139163050')
+;
+insert into authorISBN (authorID,isbn) values (1,'013028419x')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130161438')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130856118')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130125075')
+;
+insert into authorISBN (authorID,isbn) values (1,'0138993947')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130852473')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130829277')
+;
+insert into authorISBN (authorID,isbn) values (1,'0134569555')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130829293')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130284173')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130284181')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130895601')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130384747')
+;
+insert into authorISBN (authorID,isbn) values (1,'0131426443')
+;
+insert into authorISBN (authorID,isbn) values (1,'013100252X')
+;
+insert into authorISBN (authorID,isbn) values (1,'0131450913')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130308978')
+;
+insert into authorISBN (authorID,isbn) values (1,'0131483986')
+;
+insert into authorISBN (authorID,isbn) values (1,'0131016210')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130341517')
+;
+insert into authorISBN (authorID,isbn) values (1,'0131450913')
+;
+insert into authorISBN (authorID,isbn) values (1,'0130308978')
+;
+
+insert into authorISBN (authorID,isbn) values (2,'0130895725')
+;
+insert into authorISBN (authorID,isbn) values (2,'0132261197')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130895717')
+;
+insert into authorISBN (authorID,isbn) values (2,'0135289106')
+;
+insert into authorISBN (authorID,isbn) values (2,'0139163050')
+;
+insert into authorISBN (authorID,isbn) values (2,'013028419x')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130161438')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130856118')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130125075')
+;
+insert into authorISBN (authorID,isbn) values (2,'0138993947')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130852473')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130829277')
+;
+insert into authorISBN (authorID,isbn) values (2,'0134569555')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130829293')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130284173')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130284181')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130895601')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130384747')
+;
+insert into authorISBN (authorID,isbn) values (2,'0131426443')
+;
+insert into authorISBN (authorID,isbn) values (2,'013100252X')
+;
+insert into authorISBN (authorID,isbn) values (2,'0131450913')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130308978')
+;
+insert into authorISBN (authorID,isbn) values (2,'0131483986')
+;
+insert into authorISBN (authorID,isbn) values (2,'0131016210')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130341517')
+;
+insert into authorISBN (authorID,isbn) values (2,'0131450913')
+;
+insert into authorISBN (authorID,isbn) values (2,'0130308978')
+;
+
+insert into authorISBN (authorID,isbn) values (3,'013028419x')
+;
+insert into authorISBN (authorID,isbn) values (3,'0130161438')
+;
+insert into authorISBN (authorID,isbn) values (3,'0130856118')
+;
+insert into authorISBN (authorID,isbn) values (3,'0134569555')
+;
+insert into authorISBN (authorID,isbn) values (3,'0130829293')
+;
+insert into authorISBN (authorID,isbn) values (3,'0130284173')
+;
+insert into authorISBN (authorID,isbn) values (3,'0130284181')
+;
+
+
+insert into authorISBN (authorID,isbn) values (4,'0130895601')
+;

JavaScript Validação e Mascara de placas AAA-9999 e AAAA-999

Link para os downloads do arquivo no GitHub :https://github.com/LaboratorioDeInformatica/jsPlacas


Este é o javaScript que faz a validação e a mascara da placa

function mascaraPlaca(placa, event){
var pl = placa.value; // recebe o valor de placa
regexNumericos = /\d/; // regex para todos valores numericos
regexLetras = /\D/; // regex para todos valores letras
regexNumLetra =/[a-zA-Z0-9]/; // regex para numeros e letras
var resultado = pl;
var posicao = pl.length-1 ;
var key = event.keyCode; // pegar codigo da tecla digitada
 //AAA-9999 AAAA-999
 // apaga tudo que for digitado caso seja algo diferente de numero ou letra
 if(!regexNumLetra.test(pl)){
 resultado=pl.substr(0,pl.length-1);
 }
 //verifico se o digito atual é numerico e se a string digitada é menor que 4 , 
 //pois os 3 primeiros digitos da placa precisam ser numeros
if(regexNumericos.test(pl.substr(posicao)) && pl.length < 4){
 resultado=pl.replace(regexNumericos,"");
}
//verifico o digito atual se é letra e se a string é maior que 5 pois os
//digitos a partir do 6 caracter precisam ser numeros 
if(regexLetras.test(pl.substr(posicao)) && pl.length > 5){
 resultado=pl.substr(0,pl.length-1);
}
//verifico se o 4 digito é Numerico e se o atual é uma letra para este caso de placa AAA-9999 
//entao todas as teclas digitadas a partir do 4 caracter serão numeros
if( regexNumericos.test(pl.substr(4)) && regexLetras.test(pl.substr(posicao))){
 resultado=pl.substr(0,pl.length-1);
}
//verifico se o 4 caracter é letra e se a string tem o tamanho 5 pra o caso de AAAA-999
if(regexLetras.test(pl.substr(4)) && pl.length == 5){
 resultado = pl.replace("-","");//apagar o "-" da placa do tipos AAA-9999
 resultado=resultado.substr(0,pl.length-1);
 resultado = resultado.concat("-");//inserir o "-" para a 4 posição de string 
}
//reposicionr o "-" para quando se apaga digitos 
if(regexLetras.test(pl.substr(2)) && pl.length == 3 && key!=8){
 resultado = pl.concat("-");
}

//quando o tamanho da string for 5 ou 4  e o ultimo digito for numero inserir  "-"
if((pl.length == 5 || pl.length == 4)&& regexNumericos.test(pl.substr(pl.length-1))){
 pl = pl.replace("-","");
 resultado = pl.substr(0,pl.length-1).concat("-").concat(pl.substr(pl.length-1));
}
//quando se cola um valor de placa
if(key==86){
 exp = /[A-Z]{3}\d{4}/ ; //AAA-9999
 exp1 = /[A-Z]{4}\d{3}/ ; //AAAA-999
 pl = pl.replace("-",""); // apaga todos os "-"
  var executou = false;
  //verifica o tipo de placa e insere o "-"
  if(exp.test(pl)){  
   resultado =pl.substr(0,3)+"-"+pl.substr(3,4);
   executou = true;
  //verifica o tipo de placa e insere o "-" 
  }else if( exp1.test(placa.value)){
   resultado =pl.substr(0,4)+"-"+pl.substr(4,5);
   executou = true;
  }else if(executou == false){
  resultado ="";
   
 }
}
 document.getElementById('placa').value=resultado ;
}

function validaPlaca(placa, event){
 var regex = /[a-zA-Z]{3}[a-zA-Z0-9]{1}\d{3}/;
}
// mascara para placas sem o - formatos AAA9999 e AAAA999
function maskPlaca(placa, event){
var pl = placa.value;
var regexNumericos = /\d/;
var regexLetras = /\D/;
var regexNumLetra =/[a-zA-Z0-9]/;
var resultado = pl;
var posicao = pl.length-1 ;
var key = event.keyCode;
 
 //verifica se existe caracter diferente de numero ou letra
 if(!regexNumLetra.test(pl)){
 resultado=pl.substr(0,pl.length-1);
 }
//verifica os 3 primeiros digitos são numericos 
if(regexNumericos.test(pl.substr(posicao)) && pl.length < 4){
 resultado=pl.replace(regexNumericos,"");
}
//verifica se os 4 primeiros digitos sao letras
if(regexLetras.test(pl.substr(posicao)) && pl.length > 4){
 resultado=pl.substr(0,pl.length-1);
  
}
//verifica se o quarto digito é numerico e se o ultimo digitado é letra
if( regexNumericos.test(pl.substr(4)) && regexLetras.test(pl.substr(posicao))){
 resultado=pl.substr(0,pl.length-1);
}

//verifica se o quarto digito é letra e se o tamanho da string é 5 
if(regexLetras.test(pl.substr(4)) && pl.length == 5){
 resultado=resultado.substr(0,pl.length-1);
}


if(key==86){
 exp = /[A-Z]{3}\d{4}/ ;
  exp1 = /[A-Z]{4}\d{3}/ ;
  pl = pl.replace("-","");
  var executou = false;
  if(exp.test(pl)){
   
   resultado =pl.substr(0,3)+"-"+pl.substr(3,4);
    executou = true;
  }else if( exp1.test(placa.value)){
   resultado =pl.substr(0,4)+"-"+pl.substr(4,5);
   executou = true;
  }else if(executou == false){
  resultado ="";
   
 }
}

 document.getElementById('placa').value=resultado ;
}

Página HTML que utilizamos o JavaScript de validação e Mascara de placas

 






PLACA:

Vídeo Aula 30 - PL/SQL Oracle

Nesta vídeo aula mostramos como criar e executar uma trigger em PL/SQL, também, vemos sua funcionalidade  em um banco de dados Oracle.

Vídeo Aula 29 - PL/SQL Oracle

Nesta vídeo aula trabalhamos com pacotes e tiramos o proeveito de toda funcionalidade de se trabalhar com pacotes , vemos o ganho que  ele nos tras vemos algumas de suas peculiaridades.

Vídeo Aula 28 - PL/SQL Oracle

Nesta vídeo aula mostramos como se utiliza as views e pra que serve e quais os tipos de views que temos no Oracle 10g express edition para nosso usuario.

Vídeo Aula 27 - PL/SQL Oracle

Funçoes PL/SQL , nesta vídeo aula mostramos como criar funçoes , como executa-la e também comentamos algumas de suas características.

Vídeo Aula 26 - PL/SQL Oracle

Vídeo Aula 26 - PL/SQL Oracle

Nesta vídeo aula mostramos como utilizar procedures e para que serve , também mostramos os tipos de procedures .