LCB/dbScript.sql at master · hmaciasc/LCB · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
/* DROP ALL CONSTRAINTS */
ALTER TABLE APP.RESERVATION DROP CONSTRAINT primary_key;
ALTER TABLE APP.SHOPPING DROP CONSTRAINT primary_key3;
ALTER TABLE APP.STARRED DROP CONSTRAINT primary_key4;

/* DROP ALL TABLES */
DROP TABLE BOOK;
DROP TABLE BOOK_VALUES;
DROP TABLE RESERVATION;
DROP TABLE DISCOUNT;
DROP TABLE SHOPPING;
DROP TABLE CLIENT;
DROP TABLE STARRED;


/*DISCOUNT*/

CREATE TABLE DISCOUNT
(
	ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
	DISCOUNTNAME VARCHAR(100) NOT NULL,
	DISCOUNT DOUBLE NOT NULL,
	PRIMARY KEY (ID));

INSERT INTO APP.DISCOUNT (DISCOUNTNAME, DISCOUNT)
	VALUES ('SIN DESCUENTO', 0);

/*BOOK*/

CREATE TABLE BOOK (
    TITLE VARCHAR(50) NOT NULL,
    AUTHOR VARCHAR(50) NOT NULL,
    PUBLISHER VARCHAR(50) NOT NULL,
    PRICE INTEGER NOT NULL,
    DISCOUNT_PRICE DOUBLE,
    COPY INTEGER NOT NULL,
    "VALUE" DOUBLE,
    ISBN INTEGER NOT NULL,
    CATEGORY VARCHAR(50) NOT NULL,
    PUBLISHYEAR INTEGER DEFAULT 1990  NOT NULL,
    USERSVALUE INTEGER,
    DISCOUNT_ID INTEGER NOT NULL,
    CONSTRAINT book_pk PRIMARY KEY (ISBN));

ALTER TABLE APP.BOOK ADD FOREIGN KEY (DISCOUNT_ID) REFERENCES APP.DISCOUNT (ID);


INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Harry Potter 1', 'JK Rowling', 'Salamandra', 12, 12.0, 0, 2.0, 123, 'Fantasia', 1991, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Harry Potter 2', 'JK Rowling', 'Salamandra', 12, 12.0, 1, NULL, 1234, 'Fantasia', 1991, 0, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Harry Potter 3', 'JK Rowling', 'Salamandra', 12, 12.0, 22, NULL, 12, 'Fantasia', 1991, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('El nombre del viento', 'Patrick Rothfuss', 'DEBOLSILLO', 20, 20.0, 11, NULL, 1131, 'Aventuras', 2007, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('El temor de un hombre sabio', 'Patrick Rothfuss', 'DEBOLSILLO', 20, 20.0, 21, NULL, 1132, 'Aventuras', 2011, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('El prisma negro', 'Brent Weeks', 'DEBOLSILLO', 20, 20.0, 21, NULL, 1121, 'Fantasia', 2010, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Juego de tronos', 'George RR Martin', 'GIGAMESH', 20, 20.0, 22, NULL, 1322, 'Fantasia', 1996, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Choque de reyes', 'George RR Martin', 'GIGAMESH', 20, 20.0, 20, NULL, 1323, 'Fantasia', 1998, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Tormenta de espadas', 'George RR Martin', 'GIGAMESH', 15, 15.0, 10, NULL, 1324, 'Fantasia', 2000, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Los juegos del hambre', 'Suzanne Collins', 'MOLINO', 20, 20.0, 32, NULL, 1123, 'Fantasia', 2008, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('El ultimo caton', '123', 'Salamandra', 12, 12.0, 22, NULL, 132123, 'Aventuras', 1994, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Cronicas vampiricas', '123', 'Salamandra', 12, 12.0, 22, NULL, 43321, 'Aventuras', 1994, NULL, 1);

INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Divergente', 'Veronica Roth', 'Man', 12, 12.0, 22, NULL, 0001, 'Juvenil', 1994, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Insurgente', 'Veronica Roth', 'Man', 12, 12.0, 22, NULL, 0002, 'Juvenil', 1994, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Leal', 'Veronica Roth', 'Man', 12, 12.0, 22, NULL, 0003, 'Juvenil', 1994, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Omnia', 'Laura Gallego', 'Man', 12, 12.0, 22, NULL, 0004, 'Ciencia Ficcion', 1994, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Metro 2033', 'Laura Gallego', 'Man', 12, 12.0, 22, NULL, 0005, 'Ciencia Ficcion', 1994, NULL, 1);

INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Napa en produccion', 'OReilly', 'OReilly', 12, 12.0, 22, NULL, 0011, 'Informatica', 1994, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Clean code', 'OReilly', 'OReilly', 12, 12.0, 22, NULL, 0012, 'Informatica', 1994, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Running lean', 'OReilly', 'OReilly', 12, 12.0, 22, NULL, 0013, 'Informatica', 1994, NULL, 1);

INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Friday night lights', 'Bissinger', 'Bissinger', 12, 12.0, 22, NULL, 0021, 'Deportes', 1994, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Sports gene', 'Bissinger', 'Bissinger', 12, 12.0, 22, NULL, 0022, 'Deportes', 1994, NULL, 1);

INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Diccionario cambridge', 'Cambridge', 'Cambridge', 12, 12.0, 22, NULL, 0031, 'Idiomas', 1994, NULL, 1);

INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Using technology to sell', 'Anonimo', 'Anonimo', 12, 12.0, 22, NULL, 0041, 'Tecnologia', 1994, NULL, 1);
INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Technology that will change the world', 'Anonimo', 'Anonimo', 12, 12.0, 22, NULL, 0041, 'Tecnologia', 1994, NULL, 1);

INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Policia nacional', 'Anonimo', 'Anonimo', 12, 12.0, 22, NULL, 0051, 'Oposiciones', 1994, NULL, 1);

INSERT INTO APP.BOOK (TITLE, AUTHOR, PUBLISHER, PRICE, DISCOUNT_PRICE, COPY, "VALUE", ISBN, CATEGORY, PUBLISHYEAR, USERSVALUE, DISCOUNT_ID)
	VALUES ('Manolito gafotas yo y el imbecil', 'Elvira lindo', 'Elvira lindo', 12, 12.0, 22, NULL, 0061, 'Infantil', 1994, NULL, 1);


/*CLIENT*/

CREATE TABLE CLIENT (
    "NAME" VARCHAR(50),
    LASTNAME VARCHAR(50),
    ADDRESS1 VARCHAR(200),
    ADDRESS2 VARCHAR(200),
    MAIL VARCHAR(200) NOT NULL,
    PASSWORD VARCHAR(18) NOT NULL,
    ISADMIN INTEGER DEFAULT 0 ,
    PRIMARY KEY (MAIL));

INSERT INTO APP.CLIENT ("NAME", LASTNAME, ADDRESS1, ADDRESS2, MAIL, PASSWORD, ISADMIN)
	VALUES ('admin', 'admin', '123', '123', 'admin@lcb.com', '321', 1);
INSERT INTO APP.CLIENT ("NAME", LASTNAME, ADDRESS1, ADDRESS2, MAIL, PASSWORD, ISADMIN)
	VALUES ('123', '123', '123', '123', '123@123.com', '123', 0);
INSERT INTO APP.CLIENT ("NAME", LASTNAME, ADDRESS1, ADDRESS2, MAIL, PASSWORD, ISADMIN)
	VALUES ('asd', 'asd', 'asd', 'asd', 'asd@asd.com', '123', 0);

/*RESERVATION*/

CREATE TABLE RESERVATION
(
    RESERVATIONID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    USERNAME VARCHAR(60) NOT NULL,
    BOOK VARCHAR(50) NOT NULL,
    RESERVATIONDATE DATE NOT NULL,
    CONSTRAINT primary_key PRIMARY KEY (RESERVATIONID)
);

/*
INSERT INTO APP.RESERVATION (USERNAME, BOOK, RESERVATIONDATE)
	VALUES ('asd@asd.com', 'Harry Potter 1', '2016-03-28');
*/

/*SHOPPING*/

CREATE TABLE SHOPPING
(
	MAIL VARCHAR(200) NOT NULL,
	ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
	"DATE" DATE not null,
	SHOPPING BLOB(2147483647) not null,
    CONSTRAINT primary_key3 PRIMARY KEY (ID)
);

ALTER TABLE APP.SHOPPING ADD FOREIGN KEY (MAIL) REFERENCES APP.CLIENT (MAIL);

/*STARRED*/
CREATE TABLE STARRED
(
    MAIL VARCHAR(200) NOT NULL,
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    STARRED BLOB(2147483647) not null,
    CONSTRAINT primary_key4 PRIMARY KEY (ID)
);

ALTER TABLE APP.STARRED ADD FOREIGN KEY (MAIL) REFERENCES APP.CLIENT (MAIL);


/* Book values */
CREATE TABLE BOOK_VALUES (
    CLIENTMAIL VARCHAR(200) NOT NULL,
    BOOKISBN INTEGER NOT NULL,
    "VALUE" INTEGER,
    PRIMARY KEY (CLIENTMAIL, BOOKISBN));