script.sql

-- DROP DATABASE IF EXISTS DB_BOOKWIZ;
CREATE DATABASE DB_BOOKWIZ;

USE DB_BOOKWIZ;

CREATE TABLE TB_USER (
    USU_ID int NOT NULL AUTO_INCREMENT,
    USU_NOME varchar(200) NOT NULL UNIQUE,
    USU_PREF int NOT NULL,
    USU_IDADE int NOT NULL,
    USU_EMAIL varchar(200) NOT NULL,
    USU_ATIVO int DEFAULT 1,
    USU_SENHA varchar(200) NOT NULL,
    TP_ID int NOT NULL,
    SEX_ID int NOT NULL,
    PRIMARY KEY (USU_ID),
    KEY TP_ID (TP_ID),
    KEY SEX_ID (SEX_ID)
);

CREATE TABLE TIPO_USU (
    TP_ID int NOT NULL AUTO_INCREMENT,
    TP_DESC varchar(200) NOT NULL,
    PRIMARY KEY (TP_ID)
);

CREATE TABLE SEX_USU(
    SEX_ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    SEX_DESC varchar(200) not null
);

CREATE TABLE TB_GEN (
    GEN_ID int NOT NULL AUTO_INCREMENT,
    GEN_DESC varchar(200) NOT NULL,
    PRIMARY KEY (GEN_ID)
);

CREATE TABLE TB_LIVRO (
    LV_ID int NOT NULL AUTO_INCREMENT,
    LV_TITULO varchar(200) NOT NULL,
    LV_AUTOR varchar(200) NOT NULL,
    LV_GEN varchar(200) NOT NULL,
    LV_DTCAD datetime NOT NULL,
    LV_DESC varchar(1000) NOT NULL,
    PRIMARY KEY (LV_ID)
);

CREATE TABLE TB_AVA (
    AVA_ID int NOT NULL AUTO_INCREMENT,
    AVA_NOTA int NOT NULL,
    USU_ID int NOT NULL,
    LV_ID int NOT NULL,
    PRIMARY KEY (AVA_ID),
    KEY USU_ID (USU_ID),
    KEY LV_ID (LV_ID),
    FOREIGN KEY (USU_ID) REFERENCES TB_USER(USU_ID),
    FOREIGN KEY (LV_ID) REFERENCES TB_LIVRO(LV_ID)
);

CREATE TABLE TB_ESCOLHER (
    PREF_ID int NOT NULL AUTO_INCREMENT,
    USU_ID int NOT NULL,
    GEN_ID int NOT NULL,
    PRIMARY KEY (PREF_ID),
    KEY USU_ID (USU_ID),
    KEY GEN_ID (GEN_ID),
    FOREIGN KEY (USU_ID) REFERENCES TB_USER (USU_ID),
    FOREIGN KEY (GEN_ID) REFERENCES TB_GEN (GEN_ID)
);

ALTER TABLE TB_USER ADD FOREIGN KEY (TP_ID) REFERENCES TIPO_USU (TP_ID);
ALTER TABLE TB_USER ADD FOREIGN KEY (SEX_ID) REFERENCES SEX_USU (SEX_ID);

INSERT INTO TIPO_USU (TP_DESC) VALUES ("Administrador"), ("Comum");
INSERT INTO SEX_USU (SEX_DESC) VALUES ("Masculino"), ("Feminino"), ("Prefiro não responder");
INSERT INTO TB_USER (USU_NOME, USU_PREF, USU_IDADE, USU_EMAIL, USU_SENHA, TP_ID, SEX_ID) 
             VALUES ('admin',         0,         0,        '',   'admin',     1,      1);