Aparcamientos.sql

/*BORRADO DE LAS TABLLAS*/
DROP TABLE IF EXISTS HistorialVehiculos;
DROP TABLE IF EXISTS Vehiculo;
DROP TABLE IF EXISTS Gestion;
DROP TABLE IF EXISTS Gestor;
DROP TABLE IF EXISTS Feedback;
DROP TABLE IF EXISTS ResguardoSalida;
DROP TABLE IF EXISTS Cesion;
DROP TABLE IF EXISTS SolicitudCesion;
DROP TABLE IF EXISTS AbonoConReserva;
DROP TABLE IF EXISTS Abono;
DROP TABLE IF EXISTS SolicitudAbono;
DROP TABLE IF EXISTS Titular;
DROP TABLE IF EXISTS DomicilioSede;
DROP TABLE IF EXISTS Plaza;
DROP TABLE IF EXISTS Aparcamiento;

/*BORRADO DE LOS TIPOS*/
DROP TYPE IF EXISTS tipoGestion;
DROP TYPE IF EXISTS tipoNivel;
DROP TYPE IF EXISTS tipoPersona;
DROP TYPE IF EXISTS tipoColectivo;
DROP TYPE IF EXISTS tipoAbono;
DROP TYPE IF EXISTS serviciosAdicionales;
DROP TYPE IF EXISTS vehiculos;
DROP TYPE IF EXISTS naturalezaFeedback;
DROP TYPE IF EXISTS tipoIdentificacion;
DROP TYPE IF EXISTS tipoCategoria;
DROP TYPE IF EXISTS regimenPlaza;

/*CREACION DE TIPOS PREDEFINIDOS*/
CREATE TYPE tipoGestion AS ENUM('Directa', 'Indirecta');
CREATE TYPE tipoNivel AS ENUM('Superficie', 'Altura', 'Bajo_rasante');
CREATE TYPE tipoPersona AS ENUM('Fisica', 'Juridica');
CREATE TYPE tipoColectivo AS ENUM('Residente', 'ResidentePMR', 'Profesional');
CREATE TYPE tipoAbono AS ENUM('Residentes','24h','Mov_sostenible24h','Mov_sostenibleCortoPlazo', 'Diurno', 'Nocturno');
CREATE TYPE serviciosAdicionales AS ENUM('LavadoVehiculos', 'ReparacionLunas', 'LavadoOtros', 'Consigna', 'Otros');
CREATE TYPE vehiculos AS ENUM('Automovil', 'Motocicleta', 'Bicicleta', 'VMU', 'Autocaravana');
CREATE TYPE naturalezaFeedback AS ENUM('Sugerencia', 'Reclamacion');
CREATE TYPE tipoIdentificacion AS  ENUM('ITVM', 'Registro');
CREATE TYPE tipoCategoria AS ENUM('ECO', 'CERO', 'Normal');
CREATE TYPE regimenPlaza AS ENUM('GeneralRotacional','DisuasoriaRotacional', 'Residencial');

/*CREACION DE LAS TABLAS*/
CREATE TABLE Aparcamiento(
	nombre CHAR(50),
	direccion CHAR(50) NOT NULL,
	horaApertura TIME,
	horaCierre TIME,
	gestion tipoGestion NOT NULL,
	nivel tipoNivel NOT NULL,
	cubierto BIT NOT NULL,
	alturaMax FLOAT,
	anchoMax FLOAT,
	pesoMax FLOAT,
	tarifaMax FLOAT NOT NULL,
	tarifaMaxDisuasoria FLOAT,
	suplementoElectrica FLOAT,
	horaDiurno TIME,
	horaNocturno TIME,

	PRIMARY KEY (nombre),

	CHECK (alturaMax > 0),
	CHECK (anchoMax > 0),
	CHECK (pesoMax > 0),
	CHECK (tarifaMax >= 0),
	CHECK (tarifaMaxDisuasoria >= 0)
);

CREATE TABLE Plaza(
	numPlaza CHAR(5),
	nombreAparcamiento CHAR(50),
  electrica BIT NOT NULL,
	servicioAdicional	serviciosAdicionales,
	precioComplementaria FLOAT,
	accesible BIT NOT NULL,
	inutilizada BIT NOT NULL,
	tipoVehiculo vehiculos NOT NULL,
	gastosRepercutibles FLOAT,
	regimen regimenPlaza NOT NULL,

	PRIMARY KEY (numPlaza, nombreAparcamiento),
	FOREIGN KEY (nombreAparcamiento) REFERENCES Aparcamiento,

	CHECK (precioComplementaria >= 0),
	CHECK (gastosRepercutibles >= 0)/*

	*/
);

CREATE TABLE DomicilioSede(
	direccion CHAR(50),

	PRIMARY KEY (direccion)
);

CREATE TABLE Titular(
	nombreCompleto CHAR(50) NOT NULL,
	nif CHAR(9),
	direccion CHAR(50) NOT NULL,
	persona tipoPersona NOT NULL,
	numModificacionesAnuales INTEGER DEFAULT 0,
	colectivo tipoColectivo NOT NULL,

	PRIMARY KEY (nif),
	FOREIGN KEY (direccion) REFERENCES DomicilioSede
);

CREATE TABLE SolicitudAbono(
	idSolAbono INTEGER,
	nombreAparcamiento CHAR(50),
	fechaSolicitud DATE NOT NULL,
	horaSolicitud TIME NOT NULL,
	precio FLOAT NOT NULL,
	concedida BIT NOT NULL,
	nif CHAR(9) NOT NULL,
	tipo tipoAbono,
	numHorasAccesibles TIME,
	renovacion BIT NOT NULL,

	PRIMARY KEY (idSolAbono, nombreAparcamiento),
	FOREIGN KEY (nombreAparcamiento) REFERENCES Aparcamiento,
	FOREIGN KEY (nif) REFERENCES Titular,

	CHECK (precio >= 0)
);

CREATE TABLE Abono(
	direccion CHAR(50) NOT NULL,
	fechaCaducidad DATE NOT NULL,
	horaCaducidad TIME NOT NULL,
	numAbono INTEGER,
	nombreAparcamiento CHAR(50),
	mesesMorosidad INTEGER DEFAULT 0,
	idSolAbono INTEGER NOT NULL,

	PRIMARY KEY (numAbono, nombreAparcamiento),
	FOREIGN KEY (idSolAbono, nombreAparcamiento) REFERENCES SolicitudAbono,
	FOREIGN KEY (direccion) REFERENCES DomicilioSede,

	CHECK (numAbono >= 0)/*,
	CONSTRAINT solicitudAbonoConcedida CHECK ((SELECT i.concedida
	 						FROM SolicitudAbono i
							WHERE idSolAbono = i.idSolAbono) = 1) ,
	CONSTRAINT dosAbonosDomicilio CHECK ((SELECT COUNT(*)
			FROM Abono NATURAL JOIN DomicilioSede
		) <= 2 )*/
);

CREATE TABLE AbonoConReserva(
	nombreAparcamiento CHAR(50),
	numAbono INTEGER,
	numPlaza CHAR(5) NOT NULL,

	PRIMARY KEY (numAbono, nombreAparcamiento),
	FOREIGN KEY (numAbono, nombreAparcamiento) REFERENCES Abono,
	FOREIGN KEY (numPlaza, nombreAparcamiento) REFERENCES Plaza/*,

	CONSTRAINT abonoTipoReserva CHECK ((SELECT c.tipo FROM AbonoConReserva a, Abono b , SolicitudAbono c
		WHERE a.nombreAparcamiento = b.nombreAparcamiento AND
					a.numAbono = b.numAbono AND
					b.idSolAbono = c.idSolAbono) IN ('Residentes','24h','Mov_sostenible24h')),

	CONSTRAINT plazaResidencial CHECK ((SELECT regimen FROM Plaza NATURAL JOIN AbonoConReserva) LIKE '%Residencial')
	*/


);

CREATE TABLE SolicitudCesion(
	idSolCesion INTEGER,
	nombreAparcamiento CHAR(50),
	fechaSolicitud DATE NOT NULL,
	horaSolicitud TIME NOT NULL,
	precio FLOAT NOT NULL,
	concedida BIT NOT NULL,
	nif CHAR(9) NOT NULL,

	PRIMARY KEY (idSolCesion, nombreAparcamiento),
	FOREIGN KEY (nombreAparcamiento) REFERENCES Aparcamiento,
	FOREIGN KEY (nif) REFERENCES Titular,

	CHECK (precio >= 0)

);

CREATE TABLE Cesion(
	numCesion INTEGER,
	fechaFin DATE NOT NULL,
	horaFin TIME NOT NULL,
	nombreAparcamiento CHAR(50),
	numPlaza CHAR(5),
	nif CHAR(9),
	idSolCesion INT NOT NULL,

	PRIMARY KEY (numCesion, nombreAparcamiento),
	FOREIGN KEY (nif) REFERENCES Titular,
	FOREIGN KEY (idSolCesion, nombreAparcamiento) REFERENCES SolicitudCesion,
	FOREIGN KEY (numPlaza, nombreAparcamiento) REFERENCES Plaza,

	CHECK (numCesion >= 0)/*,
	CONSTRAINT solicitudCesionConcedida CHECK ((SELECT i.concedida
							FROM SolicitudCesion i
							WHERE idSolCesion = i.idSolCesion) = 1),
	CONSTRAINT plazaResidencial CHECK ((SELECT regimen FROM Plaza NATURAL JOIN Cesion) LIKE '%Residencial')
							*/
);

CREATE TABLE ResguardoSalida(
	idResguardo INTEGER,
	nombreAparcamiento CHAR(50),
	fechaEntrada DATE NOT NULL,
	horaEntrada TIME NOT NULL,
	fechaEmision DATE NOT NULL,
	horaEmision TIME NOT NULL,
	fechaSalida DATE NOT NULL,
	horaSalida TIME NOT NULL,
	matricula CHAR(8) NOT NULL,
	movilidadReducida BIT NOT NULL,
	usoPlazaElectrica BIT NOT NULL,
	precio FLOAT,

	PRIMARY KEY (idResguardo, nombreAparcamiento),
	FOREIGN KEY (nombreAparcamiento) REFERENCES Aparcamiento,

	CHECK (precio>=0),
	CHECK (fechaEntrada<=fechaEmision),
	CHECK (fechaEmision<=fechaSalida),
	CHECK (horaEntrada<horaEmision),
	CHECK (horaEmision<horaSalida)
);

CREATE TABLE Feedback(
	idFeedback INTEGER,
	nombreAparcamiento CHAR(50),
	fechaEmision DATE,
	horaEmision TIME,
	texto CHAR(500),
	naturaleza naturalezaFeedback NOT NULL,
	email CHAR(100) NOT NULL,
	nif CHAR(9),

	PRIMARY KEY(idFeedback, nombreAparcamiento),
	FOREIGN KEY (nombreAparcamiento) REFERENCES Aparcamiento,
	FOREIGN KEY (nif) REFERENCES Titular,

	CHECK (naturaleza <> 'Reclamacion' OR nif IS NOT NULL)
);

CREATE TABLE Gestor(
	nif CHAR(9),
	nombreCompleto CHAR(50) NOT NULL,
	fechaInicioSolicitudes DATE,

	PRIMARY KEY (nif)
);

CREATE TABLE Gestion(
	fechaFin DATE,
	fechaInicio DATE,
	nif CHAR(9),
	nombreAparcamiento CHAR(50),

	PRIMARY KEY (nombreAparcamiento, fechaInicio),
	FOREIGN KEY (nif) REFERENCES Gestor,
	FOREIGN KEY (nombreAparcamiento) REFERENCES Aparcamiento,

	CHECK (fechaFin >= fechaInicio)

);

CREATE TABLE Vehiculo(
	matricula CHAR(8),
	direccion CHAR(50),
	modelo CHAR(50),
	identificacion tipoIdentificacion NOT NULL,
	categoria tipoCategoria,
	tpmr BIT NOT NULL,

	PRIMARY KEY (matricula),
	FOREIGN KEY (direccion) REFERENCES DomicilioSede/*,

	CONSTRAINT cincoVehiculosDomicilio CHECK ((
			SELECT COUNT(*)
			FROM Vehiculo NATURAL JOIN DomicilioSede
		) <= 5)*/

);

CREATE TABLE HistorialVehiculos(
	numAbono INTEGER,
	nombreAparcamiento CHAR(50),
	matricula CHAR(8) NOT NULL,
	fechaInicio DATE,
	fechaFin DATE,

	PRIMARY KEY (numAbono, nombreAparcamiento, fechaInicio),
	FOREIGN KEY (numAbono, nombreAparcamiento) REFERENCES Abono,
	FOREIGN KEY (matricula) REFERENCES Vehiculo,

	CHECK (fechaFin >= fechaInicio)

);

/*LLENADO DE LAS TABLAS*/
INSERT INTO Aparcamiento(nombre, direccion, horaApertura, horaCierre, gestion, nivel, cubierto, alturaMax, anchoMax, pesoMax, tarifaMax, tarifaMaxDisuasoria, suplementoElectrica, horaDiurno, horaNocturno)
	VALUES('AparcamientoEjemplo', 'Calle ejemplo 1', '09:00:00', '23:00:00', 'Directa', 'Superficie', '1', 3.0, 3.0, 3000.0, 5.0, 3.5, 1.5, '09:00:00', '21:00:00');

INSERT INTO Plaza(numPlaza, nombreAparcamiento, electrica, servicioAdicional, precioComplementaria, accesible, inutilizada, tipoVehiculo, gastosRepercutibles, regimen)
	VALUES('1', 'AparcamientoEjemplo', '0', 'Otros', 4.0, '1', '0', 'Automovil', 1.0, 'Residencial');

INSERT INTO Plaza(numPlaza, nombreAparcamiento, electrica, servicioAdicional, precioComplementaria, accesible, inutilizada, tipoVehiculo, gastosRepercutibles, regimen)
	VALUES('2', 'AparcamientoEjemplo', '0', 'Otros', 4.0, '1', '0', 'Automovil', 1.0, 'Residencial');

INSERT INTO DomicilioSede(direccion)
	VALUES('Calle Domicilio');

INSERT INTO Titular(nombreCompleto, nif, direccion, persona, numModificacionesAnuales, colectivo)
	VALUES('Persona Ejemplo', '11111111T', 'Calle Domicilio', 'Fisica', 0, 'Residente');

INSERT INTO solicitudAbono(idSolAbono, nombreAparcamiento, fechaSolicitud, horaSolicitud, precio, concedida, nif, tipo, numHorasAccesibles, renovacion)
	VALUES(1, 'AparcamientoEjemplo', '11-01-2019', '19:43:00', '40.0', '1', '11111111T', 'Residentes', '08:00:00', '0');

INSERT INTO Abono(direccion, fechaCaducidad, horaCaducidad,  numAbono, nombreAparcamiento, mesesMorosidad, idSolAbono)
	VALUES('Calle Domicilio', '01-01-2020', '12:00:00', 1, 'AparcamientoEjemplo', 0, 1);

INSERT INTO AbonoConReserva(nombreAparcamiento, numAbono, numPlaza)
	VALUES('AparcamientoEjemplo', 1, '1');

INSERT INTO SolicitudCesion(idSolCesion, nombreAparcamiento, fechaSolicitud, horaSolicitud, precio, concedida, nif)
	VALUES(1, 'AparcamientoEjemplo', '01-01-2020', '12:00:00', 150.0, '1', '11111111T');

INSERT INTO Cesion(numCesion, fechaFin, horaFin, nombreAparcamiento, numPlaza, nif, idSolCesion)
	VALUES(1, '01-01-2021', '12:00:00', 'AparcamientoEjemplo', '2', '11111111T', 1);

INSERT INTO ResguardoSalida(idResguardo, nombreAparcamiento, fechaEntrada, horaEntrada, fechaEmision, horaEmision, fechaSalida, horaSalida, matricula, movilidadReducida, usoPlazaElectrica, precio)
	VALUES(1, 'AparcamientoEjemplo', '01-01-2020', '12:00:00', '01-01-2020', '12:30:00', '01-01-2020', '12:35:00', '1111 JKL', '0', '1', 0.4);

INSERT INTO Feedback(idFeedback, nombreAparcamiento, fechaEmision, horaEmision, texto, naturaleza, email, nif)
	VALUES(1, 'AparcamientoEjemplo', '01-01-2020', '12:00:00', 'Mal trato.', 'Reclamacion', 'ejemplo@ejemplo.com', '11111111T');

INSERT INTO Gestor(nif, nombreCompleto, fechaInicioSolicitudes)
	VALUES('11111111T', 'Gestor Ejemplo', '01-01-2019');

INSERT INTO Gestion(nif, nombreAparcamiento, fechaFin, fechaInicio)
	VALUES('11111111T', 'AparcamientoEjemplo', '01-01-2021', '01-01-2019');

INSERT INTO Vehiculo(matricula, direccion, modelo, identificacion, categoria, tpmr)
	VALUES('1111 JKL', 'Calle Domicilio', 'Renault', 'Registro', 'ECO', '0');

INSERT INTO HistorialVehiculos(numAbono, nombreAparcamiento,matricula, fechaInicio, fechaFin)
	VALUES(1, 'AparcamientoEjemplo', '1111 JKL', '01-01-2019', '01-01-2021');