6qdmiJ5C

1DROP TABLE BanksAttendance;
2DROP TABLE Sailings;
3DROP TABLE Catches;
4DROP TABLE Crews;
5DROP TABLE Persons;
6DROP TABLE Boats;
7
8/* Schema */
9
10CREATE TABLE Boats (
11  ID      INT           NOT NULL PRIMARY KEY IDENTITY (0, 1),
12
13  Name    NVARCHAR(255) NOT NULL,
14  Type    NVARCHAR(255) NOT NULL,
15  Draught NVARCHAR(255) NOT NULL,
16  Builded DATE          NOT NULL
17);
18
19CREATE TABLE Persons (
20  ID      INT           NOT NULL PRIMARY KEY IDENTITY (0, 1),
21
22  Name    NVARCHAR(255) NOT NULL,
23  Address NVARCHAR(255) NOT NULL
24);
25
26CREATE TABLE Crews (
27  ID        INT NOT NULL PRIMARY KEY IDENTITY (0, 1),
28
29  Capitan   INT NOT NULL FOREIGN KEY REFERENCES Persons (ID),
30  Boatswain INT NOT NULL FOREIGN KEY REFERENCES Persons (ID),
31  Jung      INT NOT NULL FOREIGN KEY REFERENCES Persons (ID),
32  XO        INT NOT NULL FOREIGN KEY REFERENCES Persons (ID)
33);
34
35CREATE TABLE Catches (
36  ID     INT NOT NULL PRIMARY KEY  IDENTITY (0, 1),
37
38  Cod    INT NOT NULL,
39  Trout  INT NOT NULL,
40  Salmon INT NOT NULL
41);
42
43CREATE TABLE Sailings (
44  ID         INT      NOT NULL PRIMARY KEY IDENTITY (0, 1),
45
46  Boat       INT      NOT NULL FOREIGN KEY REFERENCES Boats (ID),
47  Crew       INT      NOT NULL FOREIGN KEY REFERENCES Crews (ID),
48
49  Departured DATETIME NOT NULL,
50  Arrived    DATETIME NOT NULL,
51
52  Catch      INT      NOT NULL FOREIGN KEY REFERENCES Catches (ID)
53);
54
55CREATE TABLE Banks (
56  ID     INT NOT NULL PRIMARY KEY IDENTITY (0, 1),
57  Number INT NOT NULL
58);
59
60CREATE TABLE BanksAttendance (
61  ID         INT      NOT NULL PRIMARY KEY IDENTITY (0, 1),
62
63  Sailing    INT      NOT NULL FOREIGN KEY REFERENCES Sailings (ID),
64
65  Bank       INT      NOT NULL FOREIGN KEY REFERENCES Banks (ID),
66
67  Arrived    DATETIME NOT NULL,
68  Departured DATETIME NOT NULL,
69
70  Quality    INT      NOT NULL
71);
72
73/* Insertion */
74
75INSERT INTO Boats VALUES
76  ('Pobeda', 'Vessel', 20, '12.10.2015');
77
78INSERT INTO Persons VALUES
79  ('Ivan Ivanov', 'Moskow, Pushkina, 4'),
80  ('Pyotr Petrov', 'St. Petersburg, Yasnaya, 101A'),
81  ('Fedor Fedorov', 'St. Petersburg, Severnaya, 14'),
82  ('Sidor Sidorov', 'Moskow, Okhotniy Ryad, 1');
83
84INSERT INTO Crews VALUES
85  (0, 1, 2, 3);
86
87INSERT INTO Catches VALUES
88  (10, 20, 30);
89
90INSERT INTO Sailings VALUES
91  (0, 0, '12.10.2015 12:50', '12.10.2016 15:11', 0);
92
93INSERT INTO Banks VALUES
94  (0),
95  (1);
96
97INSERT INTO BanksAttendance VALUES
98  (0, 0, '12.10.2015 13:20', '12.10.2015 13:51', 1);
99/* Valid */
100INSERT INTO BanksAttendance VALUES
101  (0, 1, '12.10.2015 14:00', '12.10.2015 14:15', 2);
102/* Invalid */
103INSERT INTO BanksAttendance VALUES
104  (0, 1, '12.10.2015 13:30', '12.10.2015 14:00', 2);
105
106/* Trigger */
107
108CREATE TRIGGER already_on_another_bank
109  ON BanksAttendance
110FOR INSERT
111AS IF EXISTS(
112    SELECT *
113    FROM inserted
114    WHERE EXISTS
115    (SELECT *
116     FROM BanksAttendance
117     WHERE BanksAttendance.Bank != inserted.Bank AND
118           BanksAttendance.Departured > inserted.Arrived)
119)
120  BEGIN
121    RAISERROR ('Boat is already on another bank', 16, 1);
122    ROLLBACK TRANSACTION;
123    RETURN;
124  END;
125
126/* Queries */
127
128SELECT
129  Boats.Name          AS 'Boat',
130  Sailings.Departured AS 'Departured',
131  Sailings.Arrived    AS 'Arrived',
132  Catches.Cod         AS 'Cod',
133  Catches.Salmon      AS 'Salmon',
134  Catches.Trout       AS 'Trout'
135FROM Sailings
136  INNER JOIN Boats ON Sailings.Boat = Boats.ID
137  INNER JOIN Catches ON Sailings.Catch = Catches.ID
138WHERE
139  Boats.Type = 'Vessel' AND
140  Sailings.Departured > CONVERT(DATETIME, '10.10.2015') AND
141  Sailings.Arrived < CONVERT(DATETIME, '20.10.2016')
142ORDER BY
143  Boats.Name,
144  Sailings.Departured, Sailings.Arrived,
145  Catches.Cod, Catches.Salmon, Catches.Trout;