курсач БД

  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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
USE MASTER;
GO
IF db_id (N'Civilizations') IS NOT NULL
DROP DATABASE Civilizations;
GO
CREATE DATABASE Civilizations
ON (NAME = Civ_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Civ.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = Civ_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Civ.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE Civilizations;
GO
------------------------------------------------------ Базовые таблицы-----------------------------
CREATE TABLE technology (
technologyid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
namet nvarchar(30) NOT NULL UNIQUE,
cost int NOT NULL DEFAULT 0,
generation int NOT NULL DEFAULT 0
);
GO
CREATE TABLE unit (
unitid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
nameu nvarchar(30) UNIQUE,
poweru int NOT NULL DEFAULT 0,
speed int NOT NULL DEFAULT 0,
prise int NOT NULL DEFAULT 0,
unit_technology int FOREIGN KEY REFERENCES technology(technologyid) ON DELETE SET NULL ON UPDATE NO ACTION
);
GO
CREATE TABLE building (
buildingid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
nameb nvarchar(30) UNIQUE,
discription text NOT NULL DEFAULT 'no',
prise int NOT NULL DEFAULT 0,
building_technology int FOREIGN KEY REFERENCES technology(technologyid) ON DELETE CASCADE ON UPDATE CASCADE
);
GO
CREATE TABLE country (
countryid int IDENTITY(1,1) Primary key NOT NULL,
namec nvarchar(30) NOT NULL UNIQUE,
discription text NOT NULL DEFAULT 'no',
technology_country int FOREIGN KEY REFERENCES technology(technologyid) ON DELETE NO ACTION ON UPDATE NO ACTION
);
GO
CREATE TABLE ruler (
rulerid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
namer nvarchar(30) NOT NULL UNIQUE,
Feature_I nvarchar(30) NOT NULL DEFAULT 'no1',
Feature_II nvarchar(30) NOT NULL DEFAULT 'no2',
ruler_country int FOREIGN KEY REFERENCES country(countryid) ON DELETE CASCADE ON UPDATE CASCADE
);
GO
CREATE TABLE uniq (
unitid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
nameu2 nvarchar(30) NOT NULL UNIQUE,
feature nvarchar(30) NOT NULL DEFAULT 'no1',
unit_country int FOREIGN KEY REFERENCES country(countryid) ON DELETE CASCADE ON UPDATE CASCADE,
uniq_unit int FOREIGN KEY REFERENCES unit(unitid) ON DELETE NO ACTION
);
GO
CREATE TABLE TT(
technologyFK int NOT NULL FOREIGN KEY REFERENCES technology(technologyid) ON UPDATE NO ACTION,
technologyFKnext int NOT NULL FOREIGN KEY REFERENCES technology(technologyid) ON UPDATE NO ACTION,
PRIMARY KEY (technologyFK, technologyFKnext),
);
GO
------------------------------------------------------ Представление на цивилизацию-----------------------------
IF OBJECT_ID ('Civilization', 'view') IS NOT NULL
DROP VIEW Civilization;
GO
CREATE VIEW Civilization WITH SCHEMABINDING AS
SELECT a.namec, a.discription, b.namer, b.Feature_I, b.Feature_II, c.nameu2, c.feature, d.nameu, e.namet FROM
dbo.country a INNER JOIN dbo.ruler b ON a.countryid = b.ruler_country
INNER JOIN dbo.uniq c ON c.unit_country = a.countryid
INNER JOIN dbo.unit d ON c.uniq_unit = d.unitid
INNER JOIN dbo.technology e ON e.technologyid = a.technology_country
GO
--Вставка цивилизации (Удаление цивилизации происходит каскадно после удаления страны)--
IF OBJECT_ID ('insert_Civilization','TR') IS NOT NULL
DROP TRIGGER insert_Civilization;
GO
CREATE TRIGGER insert_Civilization ON Civilization INSTEAD OF INSERT
AS
BEGIN
INSERT technology(namet) SELECT DISTINCT namet FROM inserted WHERE namet NOT IN (SELECT namet FROM technology)
INSERT unit(nameu) SELECT DISTINCT nameu FROM inserted WHERE nameu NOT IN (SELECT nameu FROM unit)
INSERT country(namec, technology_country) SELECT DISTINCT b.namec, a.technologyid FROM dbo.technology a INNER JOIN inserted b ON a.namet = b.namet WHERE b.namec NOT IN (SELECT namec FROM country)
UPDATE country SET discription =a.discription FROM inserted a WHERE (country.namec = a.namec AND a.discription IS NOT NULL)
INSERT ruler(ruler_country, namer) SELECT DISTINCT a.countryid, b.namer FROM dbo.country a INNER JOIN inserted b ON a.namec = b.namec WHERE b.namer NOT IN (SELECT namer FROM ruler) AND b.namec NOT IN (SELECT a.namec FROM dbo.country a INNER JOIN dbo.ruler d ON a.countryid = d.ruler_country ) ;
UPDATE ruler SET Feature_I = a.Feature_I FROM inserted a WHERE (ruler.namer = a.namer AND a.Feature_I IS NOT NULL);
UPDATE ruler SET Feature_II = a.Feature_II FROM inserted a WHERE (ruler.namer = a.namer AND a.Feature_II IS NOT NULL);
INSERT uniq(nameu2, unit_country, uniq_unit) SELECT DISTINCT b.nameu2, a.countryid, c.unitid FROM dbo.country a INNER JOIN inserted b ON a.namec = b.namec INNER JOIN dbo.unit c ON c.nameu = b.nameu WHERE b.nameu2 NOT IN (SELECT nameu2 FROM uniq) AND b.namec NOT IN (SELECT a.namec FROM dbo.country a INNER JOIN dbo.uniq d ON a.countryid = d.unit_country )
UPDATE uniq SET feature = a.feature FROM inserted a WHERE (uniq.nameu2 = a.nameu2 AND a.feature IS NOT NULL);
END;
GO
------------------------------------------------------ Представление на правителя-----------------------------
IF OBJECT_ID ('rulerview', 'view') IS NOT NULL
DROP VIEW rulerview;
GO
CREATE VIEW rulerview WITH SCHEMABINDING AS
SELECT a.namec, b.namer, b.Feature_I, b.Feature_II FROM
dbo.country a INNER JOIN dbo.ruler b ON a.countryid = b.ruler_country
GO
--Вставка правителя--
IF OBJECT_ID ('insert_ruler','TR') IS NOT NULL
DROP TRIGGER insert_ruler;
GO
CREATE TRIGGER insert_ruler ON rulerview INSTEAD OF INSERT
AS
BEGIN
INSERT ruler(ruler_country, namer)SELECT DISTINCT a.countryid, b.namer FROM dbo.country a INNER JOIN inserted b ON a.namec = b.namec WHERE b.namer NOT IN (SELECT namer FROM ruler);
UPDATE ruler SET Feature_I = a.Feature_I FROM inserted a WHERE (ruler.namer = a.namer AND a.Feature_I IS NOT NULL);
UPDATE ruler SET Feature_II = a.Feature_II FROM inserted a WHERE (ruler.namer = a.namer AND a.Feature_II IS NOT NULL);
END;
GO
--Удаление правителя--
IF OBJECT_ID ('delete_ruler','TR') IS NOT NULL
DROP TRIGGER delete_ruler;
GO
CREATE TRIGGER delete_ruler ON rulerview INSTEAD OF DELETE
AS
DELETE FROM ruler WHERE namer IN (SELECT namer FROM deleted);
DELETE FROM country WHERE countryid NOT IN (SELECT ruler_country FROM ruler);
GO
------------------------------------------------------ Представление на УУ-----------------------------
IF OBJECT_ID ('uniqview', 'view') IS NOT NULL
DROP VIEW uniqview;
GO
CREATE VIEW uniqview WITH SCHEMABINDING AS
SELECT a.namec, c.nameu2, c.feature, d.nameu FROM
dbo.country a INNER JOIN dbo.uniq c ON c.unit_country = a.countryid
INNER JOIN dbo.unit d ON c.uniq_unit = d.unitid
GO
--Вставка УУ--
IF OBJECT_ID ('insert_uniq','TR') IS NOT NULL
DROP TRIGGER insert_uniq;
GO
CREATE TRIGGER insert_uniq ON uniqview INSTEAD OF INSERT
AS
BEGIN
INSERT unit(nameu) SELECT DISTINCT nameu FROM inserted WHERE nameu NOT IN (SELECT nameu FROM unit)
INSERT uniq(nameu2, unit_country, uniq_unit) SELECT DISTINCT b.nameu2, a.countryid, c.unitid FROM dbo.country a INNER JOIN inserted b ON a.namec = b.namec INNER JOIN dbo.unit c ON c.nameu = b.nameu WHERE b.nameu2 NOT IN (SELECT nameu2 FROM uniq);
UPDATE uniq SET feature = a.feature FROM inserted a WHERE (uniq.nameu2 = a.nameu2 AND a.feature IS NOT NULL);
END;
GO
--Удаление УУ--
IF OBJECT_ID ('delete_uniq','TR') IS NOT NULL
DROP TRIGGER delete_uniq;
GO
CREATE TRIGGER delete_uniq ON uniqview INSTEAD OF DELETE
AS
DELETE FROM uniq WHERE nameu2 IN (SELECT nameu2 FROM deleted);
DELETE FROM country WHERE countryid NOT IN (SELECT unit_country FROM uniq);
GO
------------------------------------------------------ Представление для дерева технологий -----------------------------
CREATE VIEW treeview WITH SCHEMABINDING AS
SELECT a.namet AS tecprev, c.namet AS tecnext FROM
dbo.technology a LEFT JOIN dbo.TT b ON a.technologyid = b.technologyFK
LEFT JOIN dbo.technology c ON c.technologyid = b.technologyFKnext
GO
--Вставка связей в представление
IF OBJECT_ID ('insert_tree','TR') IS NOT NULL
DROP TRIGGER insert_tree;
GO
CREATE TRIGGER insert_tree ON treeview INSTEAD OF INSERT
AS
BEGIN
INSERT TT(technologyFK, technologyFKnext) SELECT DISTINCT a.technologyid, c.technologyid FROM dbo.technology a INNER JOIN inserted b ON a.namet = b.tecprev INNER JOIN dbo.technology c ON b.tecnext = c.namet;
END;
GO
--Вставка связей в таблицу--
IF OBJECT_ID ('insert_TT', 'TR') IS NOT NULL
DROP TRIGGER insert_TT;
GO
CREATE TRIGGER insert_TT ON TT INSTEAD OF INSERT
AS
BEGIN
INSERT TT (technologyFK, technologyFKnext) SELECT DISTINCT a.technologyFK, a.technologyFKnext FROM inserted a WHERE
( SELECT b.generation FROM inserted INNER JOIN technology b ON a.technologyFK = b.technologyid) < (SELECT c.generation FROM inserted INNER JOIN technology c ON a.technologyFKnext = c.technologyid)
END;
GO
--Удаление связей из таблицы--
IF OBJECT_ID ('delete_tree','TR') IS NOT NULL
DROP TRIGGER delete_tree;
GO
CREATE TRIGGER delete_tree ON treeview INSTEAD OF DELETE
AS
DELETE FROM TT WHERE technologyFK IN (SELECT DISTINCT a.technologyid FROM dbo.technology a INNER JOIN deleted b ON a.namet = b.tecprev) AND
technologyFKnext IN (SELECT DISTINCT a.technologyid FROM dbo.technology a INNER JOIN deleted b ON a.namet = b.tecnext)
GO
------------------------------------------------------ Представление на технологии-----------------------------
IF OBJECT_ID ('tecview', 'view') IS NOT NULL
DROP VIEW tecview;
GO
CREATE VIEW tecview WITH SCHEMABINDING AS
SELECT a.namet, a.cost, a.generation, b.nameu, c.nameb, d.tecprev, e.tecnext FROM
dbo.technology a LEFT JOIN dbo.unit b ON a.technologyid = b.unit_technology
LEFT JOIN dbo.building c ON a.technologyid = c.building_technology
Left Join dbo.treeview d ON a.namet = d.tecnext
Left Join dbo.treeview e ON a.namet = e.tecprev
GO
--Вставка технологии (Удаление технологии DELETE FROM technology WHERE namet = 'имя' юнит сбросится как не требующий техи, если к ней привязана страна удаления не будет--
IF OBJECT_ID ('insert_technology','TR') IS NOT NULL
DROP TRIGGER insert_technology;
GO
CREATE TRIGGER insert_technology ON tecview INSTEAD OF INSERT
AS
BEGIN
INSERT technology(namet, cost, generation) SELECT DISTINCT namet, cost, generation FROM inserted WHERE namet NOT IN (Select namet FROM technology);
INSERT unit(unit_technology, nameu) SELECT DISTINCT a.technologyid, b.nameu FROM dbo.technology a INNER JOIN inserted b ON a.namet = b.namet WHERE b.nameu NOT IN (SELECT nameu FROM unit) AND b.namet NOT IN (SELECT a.namet FROM dbo.technology a INNER JOIN dbo.unit d ON a.technologyid = d.unit_technology) AND b.nameu IS NOT NULL;
INSERT building(building_technology, nameb) SELECT DISTINCT a.technologyid, b.nameb FROM dbo.technology a INNER JOIN inserted b ON a.namet = b.namet WHERE b.nameb NOT IN (SELECT nameb FROM building) AND b.namet NOT IN (SELECT a.namet FROM dbo.technology a INNER JOIN dbo.building d ON a.technologyid = d.building_technology) AND b.nameb IS NOT NULL;
END;
GO
/*
Вставка юнита происходит вместе с технологией которая его добавляет или вместе в с УУ, который его добавляет
Параметры атаки, скорости и цены юнита задаются через апдейт таблицы юнитов по имени
Удаление юнита возможно если у нему не привязан УУ
*/
------------------------------------------------------ Тесты-----------------------------
--Тестирование цивилизаций--
INSERT Civilization(namec, namer, nameu, nameu2, namet) VALUES ('Egypt', 'Ramses', 'archer', 'arsher', 'astronomy');
INSERT Civilization(namec, namer, nameu, nameu2, namet) VALUES ('Egypt', 'Ramses II','archer', 'arsher', 'phisics');
INSERT ruler(namer, ruler_country) VALUES ('Ramses II', 1);
INSERT unit(nameu) VALUES ('archer2');
INSERT uniq(nameu2, unit_country, uniq_unit) VALUES ('archer3', 1, 2);
DELETE FROM uniqview WHERE nameu = 'archer'
DELETE FROM rulerview WHERE namer = 'Ramses'
DELETE FROM technology WHERE namet = 'phisics'
SELECT namec, namet, nameu2, namer, nameu FROM Civilization
SELECT namet FROM technology;
SELECT namec FROM country;
SELECT namer FROM ruler;
SELECT nameu2 FROM uniq;
SELECT nameu FROM unit;
GO
--Тестирование технологий--
INSERT tecview(namet, generation, cost, nameu, nameb) VALUES ('рыболовство', 0, 0, '1', 'plfy');
INSERT tecview(namet, generation, cost, nameu, nameb) VALUES ('мореплавание', 1, 0, '2', 'sdf');
INSERT tecview(namet, generation, cost, nameu) VALUES ('компас', 1, 0, '3');
INSERT tecview(namet, generation, cost, nameu) VALUES ('оптика', 2, 0, '4');
UPDATE building SET building_technology = technologyid FROM technology WHERE (namet = 'оптика' AND nameb = 'plfy');
INSERT TT(technologyFK,technologyFKnext) Values (1,1);
INSERT TT(technologyFK,technologyFKnext) Values (1,2);
INSERT TT(technologyFK,technologyFKnext) Values (2,1);
INSERT TT(technologyFK,technologyFKnext) Values (2,3);
INSERT TT(technologyFK,technologyFKnext) Values (3,4);
INSERT treeview(tecprev,tecnext) Values ('рыболовство','рыболовство');
INSERT treeview(tecprev,tecnext) Values ('рыболовство','мореплавание');
INSERT treeview(tecprev,tecnext) Values ('мореплавание','рыболовство');
INSERT treeview(tecprev,tecnext) Values ('мореплавание','компас');
INSERT treeview(tecprev,tecnext) Values ('компас','оптика');
SELECT namet, generation FROM technology;
SELECT *FROM tecview
SELECT *FROM unit
SELECT *FROM building
SELECT *FROM TT
SELECT*FROM treeview
SELECT*from tecview
GO