DDL

CREATE TABLE CLIENTE
( ID_CLIENT CHAR(15) NOT NULL,
PNOM VARCHAR(15) NOT NULL,
SNOM VARCHAR(15),
PAPE VARCHAR(15) NOT NULL,
SAPE VARCHAR(15) ,
DIRECCION VARCHAR(100),
PRIMARY KEY (ID_CLIENT)
)ENGINE=INNODB;

CREATE TABLE AREA
( ID_MAESTRO CHAR(15) NOT NULL,
AREA VARCHAR(15) NOT NULL,
PRIMARY KEY (ID_MAESTRO,AREA),
FOREIGN KEY (ID_MAESTRO) REFERENCES CLIENTE (ID_CLIENT) ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE PERSONAL_ADMON
( ID_PERSONAL CHAR(15) NOT NULL,
CARGO VARCHAR(15) NOT NULL,
PRIMARY KEY (ID_PERSONAL),
FOREIGN KEY (ID_PERSONAL) REFERENCES CLIENTE (ID_CLIENT) ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE ALUMNO
( ID_ALUMNO CHAR(15) NOT NULL,
CURSO VARCHAR(40) NOT NULL,
PRIMARY KEY (ID_ALUMNO),
FOREIGN KEY (ID_ALUMNO) REFERENCES CLIENTE (ID_CLIENT) ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE BIBLIOTECARIO
( ID_BIBLIOTECARIO CHAR(15) NOT NULL,
PNOM VARCHAR(15) NOT NULL,
SNOM VARCHAR(15),
PAPE VARCHAR(15) NOT NULL,
SAPE VARCHAR(15) ,
PRIMARY KEY (ID_BIBLIOTECARIO)
)ENGINE=INNODB;

CREATE TABLE PROVEEDOR
( ID_PROVEEDOR VARCHAR(3) NOT NULL,
NOMBRE VARCHAR(20) NOT NULL,
PRIMARY KEY (ID_PROVEEDOR)
)ENGINE=INNODB;

CREATE TABLE LIBRO
( ID_LIBRO VARCHAR(6) NOT NULL,
NOMBRE VARCHAR(100) NOT NULL,
N_EDICION INT NOT NULL,
OBSERVACION VARCHAR(200),
ISBN VARCHAR(17),
IDPROVE_DONA VARCHAR(3),
IDPROVE_VENDE VARCHAR(3),
PRIMARY KEY (ID_LIBRO),
FOREIGN KEY(IDPROVE_DONA) REFERENCES PROVEEDOR(ID_PROVEEDOR) ON UPDATE CASCADE ,
FOREIGN KEY(IDPROVE_VENDE) REFERENCES PROVEEDOR(ID_PROVEEDOR) ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE EDITORIAL
( ID_EDITORIAL VARCHAR(4) NOT NULL,
PAIS VARCHAR(20) NOT NULL,
NOMBRE VARCHAR (50) NOT NULL,
PRIMARY KEY (ID_EDITORIAL)
)ENGINE=INNODB;

CREATE TABLE AUTOR
( ID_AUTOR VARCHAR(3) NOT NULL,
NOMBRE VARCHAR(30) NOT NULL,
PAIS_ORIGEN VARCHAR(20) NOT NULL,
FECHA_NAC DATE,
PRIMARY KEY (ID_AUTOR)
)ENGINE=INNODB;

CREATE TABLE MODULO
( ID_MODULO VARCHAR(2) NOT NULL,
DESCRIPCCION VARCHAR(200) NOT NULL,
PRIMARY KEY (ID_MODULO)
)ENGINE=INNODB;

CREATE TABLE USUARIO
( ID_USUARIO VARCHAR(2) NOT NULL,
PASSWORD VARCHAR(15) NOT NULL,
NOMBRE_USUARIO VARCHAR(15) NOT NULL,
ID_BIBLIOTECARIO CHAR(15) NOT NULL,
PRIMARY KEY (ID_USUARIO),
FOREIGN KEY(ID_BIBLIOTECARIO) REFERENCES BIBLIOTECARIO(ID_BIBLIOTECARIO) ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE PUBLICA
( ID_EDITORIAL VARCHAR(4) NOT NULL,
ID_LIBRO VARCHAR(6) NOT NULL,
PRIMARY KEY (ID_EDITORIAL, ID_LIBRO),
FOREIGN KEY(ID_EDITORIAL) REFERENCES EDITORIAL(ID_EDITORIAL) ON UPDATE CASCADE,
FOREIGN KEY(ID_LIBRO) REFERENCES LIBRO(ID_LIBRO) ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE ESCRIBE
( ID_AUTOR VARCHAR(3) NOT NULL,
ID_LIBRO VARCHAR(6) NOT NULL,
PRIMARY KEY (ID_AUTOR , ID_LIBRO),
FOREIGN KEY(ID_AUTOR) REFERENCES AUTOR(ID_AUTOR) ON UPDATE CASCADE,
FOREIGN KEY(ID_LIBRO) REFERENCES LIBRO(ID_LIBRO) ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE CATEGORIA
( ID_LIBRO VARCHAR(6) NOT NULL,
CATEGORIA CHAR(20) NOT NULL,
PRIMARY KEY (ID_LIBRO, CATEGORIA),
FOREIGN KEY (ID_LIBRO) REFERENCES LIBRO(ID_LIBRO) ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE TELEFONO
( ID_CLIENT CHAR(15) NOT NULL,
TELEFONO CHAR(9) NOT NULL,
PRIMARY KEY (ID_CLIENT, TELEFONO),
FOREIGN KEY (ID_CLIENT) REFERENCES CLIENTE (ID_CLIENT) ON UPDATE CASCADE
)ENGINE=INNODB;
CREATE TABLE PRESTAMO
( N_PRESTAMO VARCHAR(5) NOT NULL,
FECHA_PRESTAMO DATE,
FECHA_DEV DATE,
ID_CLIENT CHAR(15) NOT NULL,
ID_BIBLIOTECARIO CHAR(15) NOT NULL,
PRIMARY KEY (N_PRESTAMO),
FOREIGN KEY (ID_CLIENT) REFERENCES CLIENTE (ID_CLIENT) ON UPDATE CASCADE,
FOREIGN KEY(ID_BIBLIOTECARIO) REFERENCES BIBLIOTECARIO(ID_BIBLIOTECARIO) ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE EJEMPLAR
( ID_EJEMPLAR VARCHAR(2) NOT NULL,
ID_LIBRO VARCHAR(6) NOT NULL,
ESTADO BOOLEAN,
N_EJEMPLAR VARCHAR(2) NOT NULL,
N_PRESTAMO VARCHAR(5) NOT NULL,
PRIMARY KEY (ID_EJEMPLAR , ID_LIBRO),
FOREIGN KEY(ID_LIBRO) REFERENCES LIBRO(ID_LIBRO) ON UPDATE CASCADE ,
FOREIGN KEY(N_PRESTAMO) REFERENCES PRESTAMO(N_PRESTAMO) ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE ACCEDER
( ID_MODULO VARCHAR(2) NOT NULL,
ID_USUARIO VARCHAR(2) NOT NULL,
PRIMARY KEY (ID_MODULO, ID_USUARIO),
FOREIGN KEY(ID_MODULO) REFERENCES MODULO(ID_MODULO) ON UPDATE CASCADE ,
FOREIGN KEY(ID_USUARIO) REFERENCES USUARIO(ID_USUARIO) ON UPDATE CASCADE
)ENGINE=INNODB;