6wmgdYxC

1DROP SCHEMA IF EXISTS SN2016GR4;
2CREATE SCHEMA IF NOT EXISTS SN2016GR4;
3
4USE SN2016GR4;
5
6CREATE TABLE Kunde
7(
8Kunde_id INT(10) AUTO_INCREMENT,
9Fornavn CHAR(30) NOT NULL,
10Etternavn CHAR(20) NOT NULL,
11Telefonnummer INT(20) NOT NULL,
12Epost CHAR(50),
13Adresse CHAR(50),
14Post_nr INT(4),
15Fødselsdato DATE,
16PRIMARY KEY(Kunde_id)
17);
18
19
20
21CREATE TABLE Bestilling
22(
23Bestillings_id INT(10) AUTO_INCREMENT,
24Kunde_id INT(10) NOT NULL,
25Dato_fra DATE NOT NULL,
26Dato_til DATE NOT NULL,
27Ansatt_id INT(10) NOT NULL,
28Antall_voksne INT(1) NOT NULL,
29Antall_barn INT(1),
30StatusSjekk CHAR(1),
31PRIMARY KEY(Bestillings_id),
32CONSTRAINT Statusregel CHECK(StatusSjekk IN ('B','R','I','U'))
33);
34
35
36
37CREATE TABLE Talong
38(
39Talong_id INT(10)  AUTO_INCREMENT,
40Rom_Nr INT(3) NOT NULL,
41Pris DECIMAL(9, 2) NOT NULL,
42Kjøpssted VARCHAR(20) NOT NULL,
43Dato_kjøpt DATE,
44PRIMARY KEY (Talong_id)
45);
46
47CREATE TABLE Rom
48(
49Rom_Nr INT(3) NOT NULL,
50Romtype_id INT(10) NOT NULL,
51PRIMARY KEY(Rom_Nr)
52);
53
54
55CREATE TABLE Postkatalog
56(
57Post_nr INT(4) NOT NULL,
58PostSted CHAR(40) NOT NULL,
59PRIMARY KEY(Post_nr)
60);
61
62INSERT INTO Postkatalog VALUES ("3515", "Hønefoss");
63INSERT INTO Postkatalog VALUES ("1337", "Sandvika");
64
65CREATE TABLE RomLeie
66(
67RomLeie_id INT(10) NOT NULL AUTO_INCREMENT,
68Kunde_id INT(10) NOT NULL,
69Dato_Fra DATE NOT NULL,
70Dato_Til DATE NOT NULL,
71Rom_Nr INT(3) NOT NULL,
72PRIMARY KEY(RomLeie_id)
73);
74
75CREATE TABLE Romtype
76(
77Romtype_ID INT(10) NOT NULL AUTO_INCREMENT,
78Romtype CHAR(10) NOT NULL,
79Pris DECIMAL(9,2) NOT NULL,
80PRIMARY KEY (Romtype_id)
81);
82INSERT INTO Romtype VALUES("1","Dobbeltrom", "1290");
83INSERT INTO Romtype VALUES("2","Familierom", "1290");
84
85CREATE TABLE Ansatt
86(
87Ansatt_id INT(10) NOT NULL AUTO_INCREMENT,
88Fornavn CHAR(30) NOT NULL,
89Etternavn CHAR(133) NOT NULL,
90Fødselsdato DATE NOT NULL,
91TelefonNr INT(20) NOT NULL,
92Epost CHAR(50) NOT NULL,
93Adresse CHAR(50) NOT NULL,
94Post_nr INT(4) NOT NULL,
95Stillings_id INT(4) NOT NULL,
96Brukernavn CHAR(30),
97Passord CHAR(30),
98PRIMARY KEY (Ansatt_id)
99);
100
101
102CREATE TABLE Gammel_rompris
103(
104Dato_endret DATE NOT NULL,
105Gammel_Pris DECIMAL(3,0) NOT NULL,
106Romtype_id INT(10) NOT NULL,
107PRIMARY KEY(Dato_endret, Romtype_id)
108);
109
110CREATE TABLE Stillingstype
111(
112Stillings_id INT(10) NOT NULL,
113StillingsNavn CHAR(30) NOT NULL,
114PRIMARY KEY (Stillings_id)
115);
116
117INSERT INTO Stillingstype VALUES ("1", "Resepsjonist");
118INSERT INTO Stillingstype VALUES ("2", "Hotellsjef");
119
120CREATE TABLE Romgjester
121(
122 Bestillings_id INT(10) AUTO_INCREMENT,
123 Fornavn VARCHAR(30),
124 Etternavn VARCHAR(30),
125 Fødselsdato DATE,
126 Alder INT(3),
127 PRIMARY KEY (Bestillings_id, Fornavn, Fødselsdato)
128);
129
130ALTER TABLE Kunde ADD CONSTRAINT KundePostFK FOREIGN KEY (Post_nr) REFERENCES Postkatalog(Post_nr);
131ALTER TABLE Bestilling ADD CONSTRAINT BestillingKundeFK FOREIGN KEY (Kunde_id) REFERENCES Kunde(Kunde_id);
132ALTER TABLE Bestilling ADD CONSTRAINT BestillingAnsattFK FOREIGN KEY (Ansatt_id) REFERENCES Ansatt(Ansatt_id);
133ALTER TABLE Talong ADD CONSTRAINT TalongRomFK FOREIGN KEY (Rom_Nr) REFERENCES Rom(Rom_Nr);
134ALTER TABLE Rom ADD CONSTRAINT RomRTypeFK FOREIGN KEY (Romtype_id) REFERENCES Romtype(Romtype_id);
135ALTER TABLE Romleie ADD CONSTRAINT RomleieKundeFK FOREIGN KEY (Kunde_id) REFERENCES Kunde(Kunde_id);
136ALTER TABLE Romleie ADD CONSTRAINT RomleieRomNrFK FOREIGN KEY (Rom_nr) REFERENCES Rom(Rom_nr);
137ALTER TABLE Ansatt ADD CONSTRAINT AnsPostFK FOREIGN KEY (Post_Nr) REFERENCES Postkatalog(Post_Nr);
138ALTER TABLE Ansatt ADD CONSTRAINT AnsStillingFK FOREIGN KEY (Stillings_id) REFERENCES Stillingstype(Stillings_id);
139ALTER TABLE Gammel_rompris ADD CONSTRAINT GammelRomRTypeFK FOREIGN KEY (Romtype_id) REFERENCES Romtype(Romtype_id);
140ALTER TABLE Romgjester ADD CONSTRAINT KundeID FOREIGN KEY (Bestillings_id) REFERENCES Bestilling(Bestillings_id);
141
142
143INSERT INTO Ansatt VALUES ('1', 'Erik', 'Hellerslien', '1990-07-09', '92268956', 'erik_hellerslien@hotmail.com', 'Hengsleveien 147', '3515', '2', NULL, NULL);
144INSERT INTO Ansatt VALUES ('2', 'Sindre', 'Sluungard', '1996-07-09', '95854523', 'sindre_sluungard@hotmail.com', 'Hengsleveien 148', '1337', '1', NULL, NULL);
145INSERT INTO Kunde VALUES ('1', 'Erik', 'Hellerslien', '92268956', 'erik_hellerslien@hotmail.com', 'Hengsleveien 147', '3515', '1990-07-09');
146
147INSERT INTO Bestilling VALUES ('6','1','2016-04-04', '2016-05-05', '1','2', '2', 'K');
148
149INSERT INTO Rom VALUES ('1', '2');
150INSERT INTO Rom VALUES ("2", "2");
151INSERT INTO Rom VALUES ("3", "2");
152INSERT INTO Rom VALUES ("4", "2");
153INSERT INTO Rom VALUES ("5", "2");
154INSERT INTO Rom VALUES ("6", "2");
155INSERT INTO Rom VALUES ("7", "2");
156INSERT INTO Rom VALUES ("8", "2");
157INSERT INTO Rom VALUES ("9", "2");
158INSERT INTO Rom VALUES ("10", "2");
159INSERT INTO Rom VALUES ("11", "2");
160INSERT INTO Rom VALUES ("12", "2");
161INSERT INTO Rom VALUES ("13", "2");
162INSERT INTO Rom VALUES ("14", "2");
163INSERT INTO Rom VALUES ("15", "2");
164INSERT INTO Rom VALUES ("16", "2");
165INSERT INTO Rom VALUES ("17", "2");
166INSERT INTO Rom VALUES ("18", "2");
167INSERT INTO Rom VALUES ("19", "2");
168INSERT INTO Rom VALUES ("20", "2");
169INSERT INTO Rom VALUES ("21", "2");
170INSERT INTO Rom VALUES ("22", "2");
171INSERT INTO Rom VALUES ("23", "2");
172INSERT INTO Rom VALUES ("24", "2");
173INSERT INTO Rom VALUES ("25", "2");
174INSERT INTO Rom VALUES ("26", "2");
175INSERT INTO Rom VALUES ("27", "2");
176INSERT INTO Rom VALUES ("28", "2");
177INSERT INTO Rom VALUES ("29", "2");
178INSERT INTO Rom VALUES ("30", "2");
179INSERT INTO Rom VALUES ("31", "2");
180INSERT INTO Rom VALUES ("32", "2");
181INSERT INTO Rom VALUES ("33", "2");
182INSERT INTO Rom VALUES ("34", "2");
183INSERT INTO Rom VALUES ("35", "2");
184INSERT INTO Rom VALUES ("36", "1");
185INSERT INTO Rom VALUES ("37", "1");
186INSERT INTO Rom VALUES ("38", "1");
187INSERT INTO Rom VALUES ("39", "1");
188INSERT INTO Rom VALUES ("40", "1");
189INSERT INTO Rom VALUES ("41", "1");
190INSERT INTO Rom VALUES ("42", "1");
191INSERT INTO Rom VALUES ("43", "1");
192INSERT INTO Rom VALUES ("44", "1");
193INSERT INTO Rom VALUES ("45", "1");
194INSERT INTO Rom VALUES ("46", "1");
195INSERT INTO Rom VALUES ("47", "1");
196INSERT INTO Rom VALUES ("48", "1");
197INSERT INTO Rom VALUES ("49", "1");
198INSERT INTO Rom VALUES ("50", "1");
199INSERT INTO Rom VALUES ("51", "1");
200INSERT INTO Rom VALUES ("52", "1");
201INSERT INTO Rom VALUES ("53", "1");
202INSERT INTO Rom VALUES ("54", "1");
203INSERT INTO Rom VALUES ("55", "1");
204INSERT INTO Rom VALUES ("56", "1");
205INSERT INTO Rom VALUES ("57", "1");
206INSERT INTO Rom VALUES ("58", "1");
207INSERT INTO Rom VALUES ("59", "1");
208INSERT INTO Rom VALUES ("60", "1");
209INSERT INTO Rom VALUES ("61", "1");
210INSERT INTO Rom VALUES ("62", "1");
211INSERT INTO Rom VALUES ("63", "1");
212INSERT INTO Rom VALUES ("64", "1");
213INSERT INTO Rom VALUES ("65", "1");
214INSERT INTO Rom VALUES ("66", "1");
215INSERT INTO Rom VALUES ("67", "1");
216INSERT INTO Rom VALUES ("68", "1");
217INSERT INTO Rom VALUES ("69", "1");
218INSERT INTO Rom VALUES ("70", "1");
219INSERT INTO Rom VALUES ("71", "1");
220INSERT INTO Rom VALUES ("72", "1");
221INSERT INTO Rom VALUES ("73", "1");
222INSERT INTO Rom VALUES ("74", "1");
223INSERT INTO Rom VALUES ("75", "1");
224INSERT INTO Rom VALUES ("76", "1");
225INSERT INTO Rom VALUES ("77", "1");
226INSERT INTO Rom VALUES ("78", "1");
227INSERT INTO Rom VALUES ("79", "1");
228INSERT INTO Rom VALUES ("80", "1");
229INSERT INTO Rom VALUES ("81", "1");
230INSERT INTO Rom VALUES ("82", "1");
231INSERT INTO Rom VALUES ("83", "1");
232INSERT INTO Rom VALUES ("84", "1");
233INSERT INTO Rom VALUES ("85", "1");
234INSERT INTO Rom VALUES ("86", "1");
235INSERT INTO Rom VALUES ("87", "1");
236INSERT INTO Rom VALUES ("88", "1");
237INSERT INTO Rom VALUES ("89", "1");
238INSERT INTO Rom VALUES ("90", "1");
239INSERT INTO Rom VALUES ("91", "1");
240INSERT INTO Rom VALUES ("92", "1");
241INSERT INTO Rom VALUES ("93", "1");
242INSERT INTO Rom VALUES ("94", "1");
243INSERT INTO Rom VALUES ("95", "1");
244INSERT INTO Rom VALUES ("96", "1");
245INSERT INTO Rom VALUES ("97", "1");
246INSERT INTO Rom VALUES ("98", "1");
247INSERT INTO Rom VALUES ("99", "1");
248INSERT INTO Rom VALUES ("100", "1");
249INSERT INTO Rom VALUES ("101", "1");
250INSERT INTO Rom VALUES ("102", "1");
251INSERT INTO Rom VALUES ("103", "1");
252INSERT INTO Rom VALUES ("104", "1");
253INSERT INTO Rom VALUES ("105", "1");
254INSERT INTO Rom VALUES ("106", "1");
255INSERT INTO Rom VALUES ("107", "1");
256INSERT INTO Rom VALUES ("108", "1");
257INSERT INTO Rom VALUES ("109", "1");
258INSERT INTO Rom VALUES ("110", "1");
259INSERT INTO Rom VALUES ("111", "1");
260INSERT INTO Rom VALUES ("112", "1");
261INSERT INTO Rom VALUES ("113", "1");
262INSERT INTO Rom VALUES ("114", "1");
263INSERT INTO Rom VALUES ("115", "1");
264INSERT INTO Rom VALUES ("116", "1");
265INSERT INTO Rom VALUES ("117", "1");
266INSERT INTO Rom VALUES ("118", "1");
267INSERT INTO Rom VALUES ("119", "1");
268INSERT INTO Rom VALUES ("120", "1");
269INSERT INTO Rom VALUES ("121", "1");
270INSERT INTO Rom VALUES ("122", "1");
271INSERT INTO Rom VALUES ("123", "1");
272INSERT INTO Rom VALUES ("124", "1");
273INSERT INTO Rom VALUES ("125", "1");
274INSERT INTO Rom VALUES ("126", "1");
275INSERT INTO Rom VALUES ("127", "1");
276INSERT INTO Rom VALUES ("128", "1");
277INSERT INTO Rom VALUES ("129", "1");
278INSERT INTO Rom VALUES ("130", "1");
279INSERT INTO Rom VALUES ("131", "1");
280INSERT INTO Rom VALUES ("132", "1");
281INSERT INTO Rom VALUES ("133", "1");
282INSERT INTO Rom VALUES ("134", "1");
283INSERT INTO Rom VALUES ("135", "1");
284INSERT INTO Rom VALUES ("136", "1");
285INSERT INTO Rom VALUES ("137", "1");
286INSERT INTO Rom VALUES ("138", "1");
287INSERT INTO Rom VALUES ("139", "1");
288INSERT INTO Rom VALUES ("140", "1");
289INSERT INTO Rom VALUES ("141", "1");
290INSERT INTO Rom VALUES ("142", "1");
291INSERT INTO Rom VALUES ("143", "1");
292INSERT INTO Rom VALUES ("144", "1");
293INSERT INTO Rom VALUES ("145", "1");
294INSERT INTO Rom VALUES ("146", "1");
295INSERT INTO Rom VALUES ("147", "1");
296INSERT INTO Rom VALUES ("148", "1");
297INSERT INTO Rom VALUES ("149", "1");
298INSERT INTO Rom VALUES ("150", "1");
299INSERT INTO Rom VALUES ("151", "1");
300INSERT INTO Rom VALUES ("152", "1");
301INSERT INTO Rom VALUES ("153", "1");
302INSERT INTO Rom VALUES ("154", "1");
303INSERT INTO Rom VALUES ("155", "1");
304INSERT INTO Rom VALUES ("156", "1");
305INSERT INTO Rom VALUES ("157", "1");
306INSERT INTO Rom VALUES ("158", "1");
307INSERT INTO Rom VALUES ("159", "1");
308INSERT INTO Rom VALUES ("160", "1");
309INSERT INTO Rom VALUES ("161", "1");
310INSERT INTO Rom VALUES ("162", "1");
311INSERT INTO Rom VALUES ("163", "1");
312INSERT INTO Rom VALUES ("164", "1");
313INSERT INTO Rom VALUES ("165", "1");