FrEypsB5

1DROP TABLE IF EXISTS SOCIO;
2DROP TABLE IF EXISTS RESERVA;
3DROP TABLE IF EXISTS INSTALACION;
4DROP TABLE IF EXISTS RECIBO;
5
6CREATE TABLE socio (
7	socioID varchar(32) NOT NULL,
8	PRIMARY KEY (socioID)
9);
10
11CREATE TABLE instalacion (
12	instalacionID integer NOT NULL,
13	instalacion_nombre varchar(32) NOT NULL,
14	PRIMARY KEY (instalacionID)
15);
16
17CREATE TABLE reserva (
18	socioID varchar(32) NOT NULL,
19	instalacionID integer NOT NULL,
20	reservaID integer NOT NULL,
21	horaComienzo  TIMESTAMP NOT NULL,
22	horaFinal TIMESTAMP NOT NULL,
23	horaEntrada TIMESTAMP,
24	horaSalida TIMESTAMP,
25	modoPago varchar(32),
26	pagado BOOLEAN,
27	precio integer,
28	CONSTRAINT FK_SOCIO FOREIGN KEY (socioID) REFERENCES SOCIO(socioID),
29	CONSTRAINT FK_INSTALACION FOREIGN KEY (instalacionID) REFERENCES INSTALACION(instalacionID),
30        CONSTRAINT chk_pago CHECK (modoPago IN ('Efectivo', 'Cuota')),
31	PRIMARY KEY (socioID, instalacionID, horaComienzo)
32);
33
34CREATE TABLE recibo (
35    reciboID INTEGER NOT NULL,
36    socioID VARCHAR(32) NOT NULL,
37    importe INTEGER NOT NULL,
38    fecha TIMESTAMP NOT NULL,
39    PRIMARY KEY (reciboID)
40);
41
42INSERT INTO SOCIO VALUES ('adri');
43INSERT INTO SOCIO VALUES ('david');
44INSERT INTO SOCIO VALUES ('joni');
45INSERT INTO SOCIO VALUES ('jose');
46INSERT INTO SOCIO VALUES ('admin');
47INSERT INTO INSTALACION VALUES (1, 'Piscina');
48INSERT INTO INSTALACION VALUES (2, 'Cancha futbol');
49INSERT INTO INSTALACION VALUES (3, 'Pista tenis');
50INSERT INTO RESERVA VALUES ('adri', 1, 1,  PARSEDATETIME('13-11-2016 10:00:00', 'dd-MM-yyyy hh:mm:ss'), PARSEDATETIME('13-11-2016 12:00:00', 'dd-MM-yyyy hh:mm:ss'), null, null, 'Efectivo' , TRUE, 2);
51INSERT INTO RESERVA VALUES ('joni', 2, 2,  PARSEDATETIME('13-11-2016 10:00:00', 'dd-MM-yyyy hh:mm:ss'), PARSEDATETIME('3-11-2016 11:00:00', 'dd-MM-yyyy hh:mm:ss'), null, null, 'Efectivo' , TRUE, 2);
52INSERT INTO RESERVA VALUES ('admin', 3, 3,  PARSEDATETIME('13-11-2016 14:00:00', 'dd-MM-yyyy hh:mm:ss'), PARSEDATETIME('13-11-2016 15:00:00', 'dd-MM-yyyy hh:mm:ss'), null, null, 'Efectivo' , TRUE, 2);