EmXtK4jr

1USE master
2GO
3
4IF EXISTS(
5    SELECT name
6		FROM sys.databases
7		WHERE name = N'AlexMullabaevFishery'
8)
9    DROP DATABASE AlexMullabaevFishery
10GO
11CREATE DATABASE AlexMullabaevFishery
12  GO
13
14USE AlexMullabaevFishery
15GO
16
17CREATE SCHEMA Fishery
18  GO
19
20CREATE TABLE Ships(
21  shipId int NOT NULL,
22  name VARCHAR(100) NOT NULL,
23  shipType VARCHAR(100) NOT NULL,
24  standardDisplacement FLOAT NOT NULL,
25  constructionDate date NOT NULL
26)
27  GO
28
29alter TABLE Ships ADD
30CONSTRAINT PK_Ships PRIMARY KEY (shipId)
31GO
32
33CREATE TABLE Passages (
34  passageId int NOT NULL,
35  shipId int NOT NULL,
36  dateFrom date NOT NULL,
37  dateTo date NOT NULL
38)
39GO
40
41ALTER TABLE Passages ADD
42CONSTRAINT PK_Passages PRIMARY KEY (passageId)
43GO
44
45CREATE TABLE Crews (
46  passageId int NOT NULL,
47  firstName VARCHAR(100) NOT NULL,
48  lastName VARCHAR(100) NOT NULL,
49  middleName VARCHAR(100) NULL,
50  address VARCHAR(200) NOT NULL,
51  position VARCHAR(100) NOT NULL
52)
53  GO
54
55ALTER TABLE Crews ADD
56CONSTRAINT FK_passageId FOREIGN KEY (passageId)
57  REFERENCES Passages (passageId)
58GO
59
60CREATE TABLE PassageHauls (
61  passageId int NOT NULL,
62  fishSort VARCHAR(100) NOT NULL,
63  haulWeight FLOAT NOT NULL /* суммарный вес отдельного сорта рыбы, кг */
64)
65  GO
66
67ALTER TABLE PassageHauls ADD
68CONSTRAINT PK_PassageHauls PRIMARY KEY (passageId, fishSort)
69GO
70
71ALTER TABLE PassageHauls ADD
72CONSTRAINT FK_PassageHauls FOREIGN KEY (passageId)
73  REFERENCES Passages(passageId)
74GO
75
76CREATE TABLE FishingPlaces (
77  placeId int NOT NULL,
78  placeInfo VARCHAR(500) NOT NULL
79)
80  GO
81
82ALTER TABLE FishingPlaces ADD
83CONSTRAINT PK_FishingPlaces PRIMARY KEY (placeId)
84GO
85
86CREATE TABLE PassagePoints (
87  stoppingId int NOT NULL,
88  passageId int NOT NULL,
89  placeId int NOT NULL,
90  arrivalDate date NOT NULL,
91  departureDate date NOT NULL,
92  fishQuality VARCHAR(100) NOT NULL
93)
94  GO
95
96ALTER TABLE PassagePoints ADD
97CONSTRAINT PK_PassagePoints PRIMARY KEY (stoppingId)
98GO
99
100ALTER TABLE PassagePoints ADD
101CONSTRAINT FK_PointPassageId FOREIGN KEY (passageId)
102  REFERENCES Passages(passageId)
103GO
104
105ALTER TABLE PassagePoints ADD
106CONSTRAINT FK_PointPlaceId FOREIGN KEY (placeId)
107  REFERENCES FishingPlaces(placeId)
108GO
109
110CREATE TABLE PlaceHauls(
111  stoppingId int NOT NULL,
112  fishSort VARCHAR(100) NOT NULL,
113  catchedCount int NOT NULL
114)
115  GO
116
117ALTER TABLE PlaceHauls ADD
118CONSTRAINT FK_PH_stoppingId FOREIGN KEY (stoppingId)
119  REFERENCES PassagePoints(stoppingId)
120GO
121
122/* ok, it works */
123
124CREATE TRIGGER PassageDateControl
125ON Passages
126FOR INSERT
127AS
128BEGIN
129  DECLARE @curPassageId INT
130  DECLARE @curShipId INT
131  DECLARE @curDateFrom DATE
132  DECLARE @curDateTo DATE
133
134  DECLARE insertedLine CURSOR FOR
135    SELECT *
136    FROM inserted
137
138  OPEN insertedLine
139  FETCH NEXT FROM insertedLine
140  INTO @curPassageId, @curShipId, @curDateFrom, @curDateTo
141  WHILE @@FETCH_STATUS = 0
142  BEGIN
143    if @curDateFrom > @curDateTo
144      BEGIN
145        ROLLBACK TRANSACTION
146        RAISERROR ('Некорректные даты рейса. Выход позже чем возвращение.', 16, 1)
147        BREAK
148      END
149
150    SELECT
151      passageId,
152      shipId,
153      dateFrom,
154      dateTo
155    FROM Passages
156    WHERE (
157      passageId != @curPassageId AND
158      shipId = @curShipId AND (
159      (@curDateFrom <= dateFrom AND dateFrom <= @curDateTo) OR
160      (@curDateFrom <= dateTo AND dateTo <= @curDateTo) OR
161      (dateFrom <= @curDateFrom AND @curDateTo <= dateTo)
162      )
163    )
164    if @@ROWCOUNT > 0
165      BEGIN
166        ROLLBACK TRANSACTION
167        RAISERROR ('Некорректные даты рейса. Пересечение по времени с другими рейсами.', 16, 1)
168        BREAK
169      END
170    FETCH NEXT FROM insertedLine
171    INTO @curPassageId, @curShipId, @curDateFrom, @curDateTo
172  END
173  CLOSE insertedLine
174  DEALLOCATE insertedLine
175END
176GO
177
178CREATE TRIGGER PlaceDateControl
179ON PassagePoints
180FOR INSERT
181AS
182BEGIN
183
184    DECLARE @curPassageId INT
185    DECLARE @curPlaceId INT
186    DECLARE @curStopId INT
187    DECLARE @curArrDate DATE
188    DECLARE @curDepDate DATE
189
190    DECLARE c CURSOR for
191      SELECT
192        stoppingId,
193        passageId,
194        placeId,
195        arrivalDate,
196        departureDate
197      FROM inserted
198
199    OPEN c
200    FETCH NEXT FROM c
201    INTO @curStopId, @curPassageId, @curPlaceId, @curArrDate, @curDepDate
202    WHILE @@FETCH_STATUS = 0
203    BEGIN
204
205      if @curArrDate > @curDepDate
206      BEGIN
207        ROLLBACK TRANSACTION
208        RAISERROR ('Некорректные даты стоянки. Приход позже чем уход.', 16, 1)
209        BREAK
210      END
211
212      SELECT *
213      FROM PassagePoints
214      WHERE (
215        @curStopId != stoppingId AND
216        @curPassageId = passageId AND
217        @curPlaceId = placeId AND (
218        (@curArrDate <= arrivalDate AND arrivalDate <= @curDepDate) OR
219        (@curArrDate <= departureDate AND departureDate <= @curDepDate) OR
220        (arrivalDate <= @curArrDate AND @curDepDate <= departureDate)
221        )
222      )
223      if @@ROWCOUNT > 0
224        BEGIN
225          ROLLBACK TRANSACTION
226          RAISERROR ('Некорректные даты стоянки. Приход позже чем уход.', 16, 1)
227          BREAK
228        END
229
230      SELECT *
231      FROM Passages
232      WHERE (
233        passageId = @curPassageId AND
234        dateFrom <= @curArrDate AND
235        @curDepDate <= dateTo
236      )
237      IF @@ROWCOUNT <> 1
238        BEGIN
239          ROLLBACK TRANSACTION
240          RAISERROR ('Некорректные даты стоянки.', 16, 1)
241          BREAK
242        END
243
244      FETCH NEXT FROM c
245      INTO @curStopId, @curPassageId, @curPlaceId, @curArrDate, @curDepDate
246    END
247    CLOSE c
248    DEALLOCATE c
249  END
250GO
251
252/* trigger testing */
253
254INSERT INTO Ships VALUES
255  (1, 'Kater1', 'motor', 123, '01.01.1980'),
256  (2, 'Kater2', 'motor', 123.5, '01.02.1980')
257GO
258
259INSERT INTO Passages VALUES
260  (1, 1, '01.01.2012', '01.01.2014')
261GO
262
263INSERT INTO Passages VALUES
264  (2, 1, '01.01.2010', '01.01.2011')
265GO
266
267/* testing data */
268/*
269INSERT INTO Passages VALUES
270  (2, 1, '01.01.2010', '01.01.2015')
271GO
272
273INSERT INTO Passages VALUES
274  (2, 1, '01.01.2010', '01.01.2013')
275GO
276
277INSERT INTO Passages VALUES
278  (2, 1, '01.01.2013', '01.01.2015')
279GO
280*/
281
282
283INSERT INTO FishingPlaces VALUES
284  (1, 'Peak'),
285  (2, 'Middle forest'),
286  (3, 'Sea')
287GO
288
289INSERT INTO PassagePoints VALUES
290  (1, 1, 1, '01.01.2012', '01.07.2012', 'Average'),
291  (2, 1, 2, '01.10.2012', '01.01.2013', 'Good'),
292  (3, 1, 3, '01.03.2013', '01.11.2013', 'Very well')
293GO
294
295INSERT INTO PassagePoints VALUES
296  (4, 2, 1, '01.05.2010', '01.07.2010', 'Best')
297GO
298
299SELECT *
300FROM Passages
301GO
302
303SELECT *
304FROM PassagePoints
305GO
306
307DECLARE @MinimalDateFrom DATE
308DECLARE @MaximalDateTo DATE
309DECLARE @requiredShipType VARCHAR(100)
310SET @MinimalDateFrom = '01.01.2000'
311SET @MaximalDateTo = '01.01.2020'
312SET @requiredShipType = 'motor'
313SELECT
314  Passages.passageId,
315  dateFrom,
316  dateTo,
317  sh.name,
318  sum(PETYA.haulWeight)
319FROM (
320    Passages
321    INNER JOIN (
322                 SELECT *
323                 FROM Ships
324                 WHERE shipType = @requiredShipType
325               ) AS sh
326      ON (Passages.shipId = sh.shipId)
327) INNER JOIN (
328  SELECT
329    haulWeight,
330    passageId
331  FROM PassageHauls
332) AS PETYA
333  ON Passages.passageId = PETYA.passageId
334
335WHERE @MinimalDateFrom <= dateFrom AND dateFrom <= @MaximalDateTo
336GROUP BY Passages.passageId, dateFrom, dateTo, sh.name
337GO
338
339
340SELECT sum(haulWeight)
341FROM PassageHauls
342WHERE passageId = 1