quintoSQL.sql

DROP DATABASE IF EXISTS aeropuerto;

CREATE DATABASE IF NOT EXISTS aeropuerto CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci;

USE aeropuerto;

CREATE TABLE persona (
	nss VARCHAR(15) PRIMARY KEY, 
	nombre VARCHAR(20) NOT NULL,
	dirección VARCHAR(50) NOT NULL
) 
CREATE TABLE tipo (
	Modelo VARCHAR(30) PRIMARY KEY,
	Capacidad INT UNSIGNED NOT NULL,
	Peso INT UNSIGNED NOT NULL 
) 
CREATE TABLE piloto (
	nss VARCHAR(15) PRIMARY KEY,
	n_lic INT UNSIGNED UNIQUE NOT NULL, 
	FOREIGN KEY (nss) REFERENCES persona(nss) ON DELETE CASCADE ON UPDATE CASCADE
) 
CREATE TABLE pilota (
	nss VARCHAR(15),
	Modelo VARCHAR(30),
	PRIMARY KEY (nss, Modelo),
	FOREIGN KEY (nss) REFERENCES persona(nss) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (Modelo) REFERENCES tipo(Modelo) ON DELETE CASCADE ON UPDATE CASCADE
) 
CREATE TABLE mecanico (
	nss VARCHAR(15) PRIMARY KEY,
	salario INT UNSIGNED NOT NULL,
	turno ENUM('Mañana' 'Tarde' 'Noche') NOT NULL,
	FOREIGN KEY (nss) REFERENCES persona(nss) ON DELETE CASCADE ON UPDATE CASCADE,
) 
CREATE TABLE propietario (
	nss VARCHAR(15) PRIMARY KEY,
	telf VARCHAR(15) NOT NULL
	FOREIGN KEY (nss) REFERENCES persona(nss) ON DELETE CASCADE ON UPDATE CASCADE,
) 
CREATE TABLE cualifica (
	nss VARCHAR(15),
	Modelo VARCHAR(30),
	PRIMARY KEY (nss, Modelo),
	FOREIGN KEY (nss) REFERENCES mecanico(nss) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (Modelo) REFERENCES tipo(Modelo) ON DELETE CASCADE ON UPDATE CASCADE

) 
CREATE TABLE hangar (
	cod_hangar int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	Capacidad INT UNSIGNED NOT NULL,
	localizacion VARCHAR(50) NOT NULL
) 
CREATE TABLE avion (
	n_registro INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
	matricula VARCHAR(10) UNIQUE NOT NULL,
	Modelo VARCHAR(30) NOT NULL,
	cod_hangar int UNSIGNED NOT NULL,
	fecha_reg DATE NOT NULL,
	fecha_constr DATE NOT NULL,
	FOREIGN KEY (Modelo) REFERENCES tipo(Modelo) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (cod_hangar) REFERENCES hangar(cod_hangar) ON DELETE CASCADE ON UPDATE CASCADE
) 
CREATE TABLE compra (
	nss VARCHAR(15),
	n_registro INT UNSIGNED,
	fecha_compra DATE NOT NULL
	PRIMARY KEY (nss, n_registro)
	FOREIGN KEY (nss) REFERENCES propietario(nss) ON DELETE CASCADE ON UPDATE CASCADE,	
	FOREIGN KEY (n_registro) REFERENCES avion(n_registro) ON DELETE CASCADE ON UPDATE CASCADE,
) 
CREATE TABLE mantiene (
	cod_trab INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
	nss VARCHAR(15) NOT NULL,
	n_registro INT UNSIGNED NOT NULL,
	fecha DATE NOT NULL,
	n_horas TIME NOT NULL,
	FOREIGN KEY (nss) REFERENCES mecanico(nss) ON DELETE CASCADE ON UPDATE CASCADE,	
	FOREIGN KEY (n_registro) REFERENCES avion(n_registro) ON DELETE CASCADE ON UPDATE CASCADE,
)