· 8 years ago · Apr 16, 2018, 07:42 AM
1CREATE SCHEMA spbtest05;
2
3CREATE TABLE spbtest05.HOTEL
4(
5idhot numeric(8) IDENTITY NOT NULL,
6adresa nvarchar(60),
7naziv nvarchar(30) NOT NULL,
8kat numeric(1) NOT NULL,
9brziror nvarchar(20),
10tel nvarchar(18),
11CONSTRAINT PK_Hotel PRIMARY KEY (idhot),
12CONSTRAINT CHK_Hotel_Kat CHECK (kat between 0 and 6)
13)
14
15CREATE TABLE spbtest05.RECEP
16(
17idhot numeric(8) NOT NULL,
18brrec numeric(2) NOT NULL,
19lok numeric(4) NOT NULL,
20mestor nvarchar(60),
21CONSTRAINT PK_Recep PRIMARY KEY (idhot, brrec),
22CONSTRAINT FK_Recep_Hotel FOREIGN KEY (idhot)
23REFERENCES spbtest05.HOTEL(idhot)
24)
25
26CREATE TABLE spbtest05.SOBA
27(
28idhot numeric(8) NOT NULL,
29brsob numeric(8) NOT NULL,
30tipsob nvarchar(25) NOT NULL,
31opis nvarchar(60),
32napom nvarchar(60),
33CONSTRAINT PK_Soba PRIMARY KEY (idhot, brsob),
34CONSTRAINT FK_Soba_Hotel FOREIGN KEY (idhot)
35REFERENCES spbtest05.HOTEL (idhot)
36)
37
38CREATE TABLE spbtest05.RADNIK
39(
40jmbg numeric(13) NOT NULL,
41ime nvarchar(20) NOT NULL,
42prz nvarchar(50) NOT NULL,
43email nvarchar(60),
44uloga char(1) NOT NULL,
45CONSTRAINT PK_Radnik PRIMARY KEY (jmbg),
46CONSTRAINT CHK_Radnik_Uloga CHECK (uloga In ('R','C','M'))
47)
48
49CREATE TABLE spbtest05.SMENA
50(
51idsme numeric(8) NOT NULL,
52nazsme nvarchar(60) NOT NULL,
53vremeod numeric(2) NOT NULL,
54vremedo numeric(2) NOT NULL,
55napome nvarchar(300),
56CONSTRAINT PK_Smena PRIMARY KEY (idsme)
57)
58
59CREATE TABLE spbtest05.GOST
60(
61mbr numeric(8) NOT NULL,
62imeg nvarchar(60) NOT NULL,
63prezg nvarchar(100) NOT NULL,
64adresag nvarchar(400) NOT NULL,
65dokid nvarchar(80) NOT NULL,
66CONSTRAINT PK_Gost PRIMARY KEY (mbr)
67)
68
69CREATE TABLE spbtest05.RADI
70(
71jmbg numeric(13) NOT NULL,
72idsme numeric(8) NOT NULL,
73datum date NOT NULL,
74idhot numeric(8),
75brrec numeric(2),
76CONSTRAINT PK_Radi PRIMARY KEY (jmbg,idsme),
77CONSTRAINT FK_Radi_Radnik FOREIGN KEY (jmbg)
78REFERENCES spbtest05.Radnik (jmbg),
79CONSTRAINT FK_Radi_Smena FOREIGN KEY (idsme)
80REFERENCES spbtest05.Smena (idsme),
81CONSTRAINT FK_Radi_Hotel FOREIGN KEY (idhot)
82REFERENCES spbtest05.Hotel (idhot)
83)
84
85ALTER TABLE spbtest05.RADI
86add constraint FK_Radi_Recep FOREIGN KEY (idhot, brrec)
87REFERENCES spbtest05.RECEP (idhot, brrec)
88
89CREATE TABLE spbtest05.PRIJAVA
90(
91idprij numeric(8) NOT NULL,
92datprij date NOT NULL,
93mbr numeric(8) NOT NULL,
94jmbg numeric(13) NOT NULL,
95CONSTRAINT PK_Prijava PRIMARY KEY (idprij),
96CONSTRAINT FK_Prijava_Gost FOREIGN KEY (mbr)
97REFERENCES spbtest05.GOST (mbr),
98CONSTRAINT FK_Prijava_Radnik FOREIGN KEY (jmbg)
99REFERENCES spbtest05.RADNIK (jmbg)
100)
101
102CREATE TABLE spbtest05.SMESTA
103(
104idprij numeric(8) NOT NULL,
105idhot numeric(8) NOT NULL,
106brsob numeric(8) NOT NULL,
107brdana numeric(3) NOT NULL,
108CONSTRAINT PK_Smesta PRIMARY KEY (idprij, idhot, brsob),
109CONSTRAINT FK_Smesta_Prijava FOREIGN KEY (idprij)
110REFERENCES spbtest05.PRIJAVA (idprij),
111CONSTRAINT FK_Smesta_Soba FOREIGN KEY (idhot, brsob)
112REFERENCES spbtest05.SOBA (idhot, brsob)
113)
114
115USE TestSBP
116GO
117
118CREATE PROCEDURE spbtest05.sp_prijave_nadan @Datum date
119AS
120SELECT g.imeg, g.prezg, g.mbr
121FROM spbtest05.PRIJAVA p join spbtest05.GOST g on p.mbr = g.mbr
122WHERE p.datprij = @Datum
123GROUP BY g.imeg, g.prezg, g.mbr
124GO
125
126
127CREATE TRIGGER proveri_dostupnost ON spbtest05.SMESTA
128INSTEAD OF INSERT
129AS
130
131IF EXISTS ( SELECT *
132FROM spbtest05.SMESTA A
133INNER JOIN inserted B ON B.idhot=A.idhot and A.brsob=B.brsob join PRIJAVA p on A.idprij = p.idprij
134WHERE DATEADD(day,b.brdana, GETDATE()) between p.datprij and DATEADD(day,a.brdana ,p.datprij)
135)
136BEGIN
137 RAISERROR ('Soba je zauzeta', 16, 1);
138END
139GO