Implementación DDL de las BD

Biblioteca Digital

 

CREATE DATABASE BibliotecaDigital;

 

USE BibliotecaDigital;

 

CREATE TABLE libro(

id_libro INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_autor INT NOT NULL,

id_categoria INT NOT NULL,

id_URL INT NOT NULL,

id_evalua INT NOT NULL,

Titulo VARCHAR(50),

Edición INT,

Editorial VARCHAR(30),

FOREIGN KEY(id_autor) REFERENCES autor(id_autor) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_categoria) REFERENCES categoria(id_categoria) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_URL) REFERENCES URL(id_URL) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_evalua) REFERENCES evalua(id_evalua) ON DELETE RESTRICT ON UPDATE CASCADE);

CREATE TABLE usuario(

id_usuario INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_email INT NOT NULL,

Nombre VARCHAR(30),

Apellido1 VARCHAR(30),

Apellido2 VARCHAR(30),

Fecha_nacimiento DATE,

FOREIGN KEY(id_email) REFERENCES e-mail(id_email) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE autor(

id_autor INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

Nombre VARCHAR(30),

Apellido1 VARCHAR(30),

Apellido2 VARCHAR(30)

Nacionalidad VARCHAR(30));

 

CREATE TABLE evalua(

id_evalua INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_usuario INT NOT NULL,

id_libro INT NOT NULL,

Calificación INT,

FOREIGN KEY(id_usuario) REFERENCES usuario(id_usuario) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_libro) REFERENCES libro(id_libro) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE URL(

id_URL INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_libro INT NOT NULL,

URL VARCHAR(50),

FOREIGN KEY(id_libro) REFERENCES libro(id_libro) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE e-mail(

id_email INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_usuario INT NOT NULL,

E-mail VARCHAR(30),

FOREIGN KEY(id_usuario) REFERENCES usuario(id_usuario) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE escribio(

id_escribio INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_libro INT NOT NULL,

id_autor INT NOT NULL,

FOREIGN KEY(id_libro) REFERENCES libro(id_libro) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_autor) REFERENCES autor(id_autor) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE categoria(

id_categoria INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_libro INT NOT NULL,

Categoría VARCHAR(50),

FOREIGN KEY(id_libro) REFERENCES libro(id_libro) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE comenta(

id_comenta INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_libro INT NOT NULL,

id_usuario INT NOT NULL,

Categoría VARCHAR(50),

FOREIGN KEY(id_libro) REFERENCES libro(id_libro) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_usuario) REFERENCES usuario(id_usuario) ON DELETE RESTRICT ON UPDATE CASCADE);

 

Gestión de nóminas de una empresa

 

CREATE DATABASE GestionNominas;

USE GestionNominas;

CREATE TABLE empleados(

id_empleado INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_col INT NOT NULL,

id_ciudad INT NOT NULL,

id_email INT NOT NULL,

id_tel INT NOT NULL,

Num_cuenta INT NOT NULL,

Nombre VARCHAR(30),

Apellido1 VARCHAR(30),

Apellido2 VARCHAR(30),

Genero CHAR(1),

Calle_y_num VARCHAR(50),

CURP VARCHAR(15),

RFC VARCHAR(15),

FOREIGN KEY(id_col) REFERENCES colonia(id_col) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_cd) REFERENCES ciudad(id_cd) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_email) REFERENCES e-mail(id_email) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_tel) REFERENCES teléfono(id_tel) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE contrato(

id_contrato INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

Fecha_alta DATE,

Fecha_baja DATE);

CREATE TABLE firma(

id_firma INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_empleado INT NOT NULL,

id_contrato INT NOT NULL,

FOREIGN KEY(id_empleado) REFERENCES empleado(id_empleado) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_contrato) REFERENCES contrato(id_contrato) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE categoria(

id_categoria INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

Nombre VARCHAR(30),

Sueldo INT,

Trienio INT);

 

CREATE TABLE puesto(

id_puesto INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

Nombre VARCHAR(30),

Complementos INT);

 

CREATE TABLE destinado(

id_destino INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_puesto INT NOT NULL,

id_contrato INT NOT NULL,

FOREIGN KEY(id_puesto) REFERENCES puesto(id_puesto) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_contrato) REFERENCES contrato(id_contrato) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE perteneceCC(

id_perteneceCC INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_categoria INT NOT NULL,

id_contrato INT NOT NULL,

FOREIGN KEY(id_categoria) REFERENCES categoria(id_categoria) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_contrato) REFERENCES contrato(id_contrato) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE perteneceEP(

id_perteneceEP INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_empleado INT NOT NULL,

id_puesto INT NOT NULL,

FOREIGN KEY(id_empleado) REFERENCES empleado(id_empleado) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_puesto) REFERENCES puesto(id_puesto) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE colonia(

id_col INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

Colonia VARCHAR(30),

C.P. INT);

 

CREATE TABLE ciudad(

id_ciudad INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

Ciudad VARCHAR(30));

 

CREATE TABLE e-mail(

id_email INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_empleado INT NOT NULL,

E-mail VARCHAR(30),

FOREIGN KEY(id_empleado) REFERENCES empleado(id_empleado) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE telefono(

id_tel INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_empleado INT NOT NULL,

Teléfono VARCHAR(30),

FOREIGN KEY(id_empleado) REFERENCES empleado(id_empleado) ON DELETE RESTRICT ON UPDATE CASCADE);

Galería de Arte

CREATE DATABASE GaleriaArte;

USE GaleriaArte;

 

CREATE TABLE clientes(

id_cliente INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_col INT NOT NULL,

id_ciudad INT NOT NULL,

id_edo INT NOT NULL,

id_pais INT NOT NULL,

id_email INT NOT NULL,

id_tel INT NOT NULL,

Nombre VARCHAR(30),

Apellido1 VARCHAR(30),

Apellido2 VARCHAR(30),

Calle_y_num VARCHAR(50),

FOREIGN KEY(id_col) REFERENCES colonia(id_col) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_cd) REFERENCES ciudad(id_cd) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_edo) REFERENCES estado(id_edo) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_pais) REFERENCES pais(id_pais) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_email) REFERENCES e-mail(id_email) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_tel) REFERENCES teléfono(id_tel) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE exposiciones(

id_exposicion INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

Titulo VARCHAR(50);

Descripción VARCHAR(80);

Fecha_inauguracion DATE;

Fecha_clausura DATE);

 

CREATE TABLE obras(

num_registro INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_artista INT NOT NULL,

Titulo VARCHAR(30),

Estilo VARCHAR(10),

Propietario VARCHAR(30),

Precio INT,

FOREIGN KEY(id_artista) REFERENCES artista(id_artista) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE artista(

id_artista INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

Nombre VARCHAR(30);

Apellido1 VARCHAR(30),

Apellido2 VARCHAR(30));

 

CREATE TABLE exhibida(

id_exhibe INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

num_registro INT NOT NULL,

id_exposicion INT NOT NULL,

FOREIGN KEY(num_registro) REFERENCES obras(num_registro) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_exposicion) REFERENCES exposiciones(id_exposicion) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE creada(

id_exhibe INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

num_registro INT NOT NULL,

id_artista INT NOT NULL,

FOREIGN KEY(num_registro) REFERENCES obras(num_registro) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_artista) REFERENCES artista(id_artista) ON DELETE RESTRICT ON UPDATE CASCADE);

CREATE TABLE oferta(

id_oferta INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

num_registro INT NOT NULL,

id_cliente INT NOT NULL,

FOREIGN KEY(num_registro) REFERENCES obras(num_registro) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY(id_cliente) REFERENCES cliente(id_cliente) ON DELETE RESTRICT ON UPDATE CASCADE);

 

CREATE TABLE colonia(

id_col INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

Colonia VARCHAR(30),

C.P. INT);

CREATE TABLE ciudad(

id_ciudad INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

Ciudad VARCHAR(30));

 

CREATE TABLE estado(

id_estado INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

Estado VARCHAR(30));

CREATE TABLE pais(

id_pais INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

País VARCHAR(30));

 

CREATE TABLE e-mail(

id_email INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_cliente INT NOT NULL,

E-mail VARCHAR(30),

FOREIGN KEY(id_cliente) REFERENCES cliente(id_cliente) ON DELETE RESTRICT ON UPDATE CASCADE);

CREATE TABLE telefono(

id_tel INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

id_cliente INT NOT NULL,

Teléfono VARCHAR(30),

FOREIGN KEY(id_cliente) REFERENCES cliente(id_cliente) ON DELETE RESTRICT ON UPDATE CASCADE);