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
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
| USE new48;
CREATE TABLE Tipi_gos_organov (Kod_tipa_organa INT NOT NULL,
Rasshifrovka_koda_tipa_organa VARCHAR (100) NULL,
CONSTRAINT prim_tipi PRIMARY KEY (Kod_tipa_organa));
INSERT Tipi_gos_organov
(Kod_tipa_organa, Rasshifrovka_koda_tipa_organa)
VALUES
(1, 'Загс');
INSERT Tipi_gos_organov
(Kod_tipa_organa, Rasshifrovka_koda_tipa_organa)
VALUES
(2, 'Нотариус');
INSERT Tipi_gos_organov
(Kod_tipa_organa, Rasshifrovka_koda_tipa_organa)
VALUES
(3, 'Суд');
INSERT Tipi_gos_organov
(Kod_tipa_organa, Rasshifrovka_koda_tipa_organa)
VALUES
(4, 'Администратор');
CREATE TABLE Tip_documenta (Kod_tipa_documenta INT IDENTITY(1,1) NOT NULL,
Kod_tipa_organa INT NULL,
Tip_documenta VARCHAR (100) NULL,
CONSTRAINT prim_tip PRIMARY KEY (Kod_tipa_documenta),
CONSTRAINT foreign_Tip_documenta FOREIGN KEY(Kod_tipa_organa)
REFERENCES Tipi_gos_organov (Kod_tipa_organa));
INSERT Tip_documenta
(Tip_documenta)
VALUES
('Паспорт');
CREATE TABLE Gos_organ (Kod_organa INT IDENTITY(1,1) NOT NULL,
Kod_tipa_organa INT NULL,
Naimenovanie_organa VARCHAR (100) NULL,
FIO_podpisanta VARCHAR (100) NULL,
Parol_dlia_vxoda_v_IS VARCHAR (8) NULL,
CONSTRAINT prim_Gos_organ PRIMARY KEY (Kod_organa),
CONSTRAINT foreign_Gos_organ FOREIGN KEY(Kod_tipa_organa)
REFERENCES Tipi_gos_organov (Kod_tipa_organa));
INSERT Gos_organ
(Naimenovanie_organa, FIO_podpisanta)
VALUES
('Гагаринский отдел ЗАГС', 'Васильева Мария Сергеевна');
update Gos_organ
set Gos_organ.Parol_dlia_vxoda_v_IS = '0000'
FROM Gos_organ
join Tipi_gos_organov on Gos_organ.Kod_organa=Tipi_gos_organov.Kod_tipa_organa
where Tipi_gos_organov.Kod_tipa_organa = 1 AND Tipi_gos_organov.Kod_tipa_organa = 2 AND Tipi_gos_organov.Kod_tipa_organa = 3;
update Gos_organ
set Gos_organ.Parol_dlia_vxoda_v_IS = '00000000'
FROM Gos_organ
join Tipi_gos_organov on Gos_organ.Kod_organa=Tipi_gos_organov.Kod_tipa_organa
where Tipi_gos_organov.Kod_tipa_organa = 4;
CREATE TABLE Documenti (Kod_documenta INT IDENTITY(1,1) NOT NULL,
Kod_organa INT NULL,
Kod_tipa_documenta INT NULL,
Naimenovanie_documenta VARCHAR (100) NULL,
Data_sozdania DATETIME NULL,
Seria VARCHAR (20) NULL,
Nomer INT NULL,
Tekstovoe_sodergimoe VARCHAR (100) NULL,
CONSTRAINT prim_Documenti PRIMARY KEY (Kod_documenta),
CONSTRAINT foreign1_Documenti FOREIGN KEY(Kod_organa)
REFERENCES Gos_organ (Kod_organa),
CONSTRAINT foreign2_Documenti FOREIGN KEY(Kod_tipa_documenta)
REFERENCES Tip_documenta (Kod_tipa_documenta));
INSERT Documenti
(Naimenovanie_documenta, Data_sozdania, Seria, Nomer, Tekstovoe_sodergimoe)
VALUES
('Свидетельство о бракосочетании Иванов Иван Иванович',Convert(DateTime,'20080813',112), 'СБ', 1, 'Свидетельство о бракосочетании: Иванов И.И.');
CREATE TABLE Fizicheskie_lica (Kod INT IDENTITY(1,1) NOT NULL,
FIO VARCHAR (100) NULL,
Data_rogdenia DATETIME NULL,
Data_smerti DATETIME NULL,
Pasportnie_dannie VARCHAR (100) NULL,
CONSTRAINT prim_fizich PRIMARY KEY (Kod));
INSERT Fizicheskie_lica
(FIO, Data_rogdenia, Data_smerti, Pasportnie_dannie)
VALUES
('Иванов Иван Иванович', 1952-03-14, 2013-09-01, 'Паспорт 47 00 456343 от 15/02/1966');
CREATE TABLE Dela_o_nasledstve (Kod_dela INT IDENTITY(1,1) NOT NULL,
Kod INT NULL,
Kod_organa INT NULL,
Data_dela DATETIME NULL,
Nomer_dela VARCHAR (20) NULL,
Elektronnaya_podpis_0_ili_1 INT NULL,
CONSTRAINT prim_Dela_o_nasledstve PRIMARY KEY (Kod_dela),
CONSTRAINT foreign1_Dela_o_nasledstve FOREIGN KEY(Kod)
REFERENCES Fizicheskie_lica (Kod),
CONSTRAINT foreign2_Dela_o_nasledstve FOREIGN KEY(Kod_organa)
REFERENCES Gos_organ (Kod_organa));
INSERT Dela_o_nasledstve
(Data_dela, Nomer_dela, Elektronnaya_podpis_0_ili_1)
VALUES
(2013-09-18, 'ДЛ-0001', 0);
INSERT Dela_o_nasledstve
(Elektronnaya_podpis_0_ili_1)
VALUES
(1);
CREATE TABLE Documenti_podtvergdaiuchie_nasledovanie (Kod_documenta_podtvergdaiuchego_nasledovanie INT IDENTITY(1,1) NOT NULL,
Kod_dela INT NULL,
Kod_documenta INT NULL,
Kod_organa INT NULL,
CONSTRAINT prim_Documenti_podtvergdaiuchie_nasledovanie PRIMARY KEY (Kod_documenta_podtvergdaiuchego_nasledovanie),
CONSTRAINT foreign1_Documenti_podtvergdaiuchie_nasledovanie FOREIGN KEY(Kod_dela)
REFERENCES Dela_o_nasledstve (Kod_dela),
CONSTRAINT foreign2_Documenti_podtvergdaiuchie_nasledovanie FOREIGN KEY(Kod_documenta)
REFERENCES Documenti (Kod_documenta),
CONSTRAINT foreign3_Documenti_podtvergdaiuchie_nasledovanie FOREIGN KEY(Kod_organa)
REFERENCES Gos_organ (Kod_organa));
CREATE TABLE Raspredelenie_nasledstva (Kod_raspredelenia_nasledstva INT IDENTITY(1,1) NOT NULL,
Kod_dela INT NULL,
Kod INT NULL,
Stepen_rodstva VARCHAR (100) NULL,
Naimenovanie_imuchestva VARCHAR (100) NULL,
Edinici VARCHAR (20) NULL,
Kolichestvo INT NULL,
Gosposhlina VARCHAR (50) NULL,
CONSTRAINT prim_Raspredelenie_nasledstva PRIMARY KEY (Kod_raspredelenia_nasledstva),
CONSTRAINT foreign1_Raspredelenie_nasledstva FOREIGN KEY(Kod_dela)
REFERENCES Dela_o_nasledstve (Kod_dela),
CONSTRAINT foreign2_Raspredelenie_nasledstva FOREIGN KEY(Kod)
REFERENCES Fizicheskie_lica (Kod));
INSERT Raspredelenie_nasledstva
(Stepen_rodstva, Naimenovanie_imuchestva, Edinici, Kolichestvo, Gosposhlina)
VALUES
('Дочь', 'квартира ул.Тверская,д.10,кв.36', 'кв.м', 80, '1000р.');
CREATE TABLE Delo_o_nasledovanii_i_raspredelenie_imuchestva (Kod_dela_o_nasledovanii INT IDENTITY(1,1) NOT NULL,
Kod INT NULL,
Kod_raspredelenia_nasledstva INT NULL,
CONSTRAINT prim_Delo_o_nasledovanii_i_raspredelenie_imuchestva PRIMARY KEY (Kod_dela_o_nasledovanii),
CONSTRAINT foreign1_Delo_o_nasledovanii_i_raspredelenie_imuchestva FOREIGN KEY(Kod)
REFERENCES Fizicheskie_lica (Kod),
CONSTRAINT foreign2_Delo_o_nasledovanii_i_raspredelenie_imuchestva FOREIGN KEY(Kod_raspredelenia_nasledstva)
REFERENCES Raspredelenie_nasledstva (Kod_raspredelenia_nasledstva));
CREATE TABLE Zavechania (Kod_zavechania INT IDENTITY(1,1) NOT NULL,
Kod_organa INT NULL,
Kod INT NULL,
Opisanie_zavechania VARCHAR (100) NULL,
Data_sozdania DATETIME NULL,
CONSTRAINT prim_zavech PRIMARY KEY (Kod_zavechania),
CONSTRAINT foreign1_Zavechania FOREIGN KEY(Kod_organa)
REFERENCES Gos_organ (Kod_organa),
CONSTRAINT foreign2_Zavechania FOREIGN KEY(Kod)
REFERENCES Fizicheskie_lica (Kod));
INSERT Zavechania
(Opisanie_zavechania, Data_sozdania)
VALUES
('Завещание Иванова И.И. на имущество', Convert(DateTime,'20130909',112));
CREATE TABLE Punkti_zavechania (Kod_punkta_zavechania INT IDENTITY(1,1) NOT NULL,
Kod_zavechania INT NULL,
Kod INT NULL,
Opisanie_imuchestva VARCHAR (100) NULL,
Edinici VARCHAR (20) NULL,
Kolichestvo INT NULL,
CONSTRAINT prim_Punkti PRIMARY KEY (Kod_punkta_zavechania),
CONSTRAINT foreign1_Punkti_zavechania FOREIGN KEY(Kod_zavechania)
REFERENCES Zavechania (Kod_zavechania),
CONSTRAINT foreign2_Punkti_zavechania FOREIGN KEY(Kod)
REFERENCES Fizicheskie_lica (Kod));
INSERT Punkti_zavechania
(Opisanie_imuchestva, Edinici, Kolichestvo)
VALUES
('квартира ул.Тверская,д.10,кв.36', 'кв.м', 80);
GO
CREATE VIEW Docview
AS
SELECT
td.Kod_tipa_documenta,
td.Tip_documenta,
doc.Kod_documenta,
doc.Naimenovanie_documenta,
doc.Data_sozdania,
doc.Seria,
doc.Nomer,
doc.Tekstovoe_sodergimoe,
gos.Kod_organa,
gos.Naimenovanie_organa,
gos.FIO_podpisanta,
tgos.Kod_tipa_organa,
tgos.Rasshifrovka_koda_tipa_organa
FROM Tip_documenta td JOIN Documenti doc ON td.Kod_tipa_documenta = doc.Kod_tipa_documenta
join Gos_organ gos on doc.Kod_organa=gos.Kod_organa
join Tipi_gos_organov tgos on gos.Kod_tipa_organa=tgos.Kod_tipa_organa
GO
CREATE VIEW OrganView
AS
SELECT
gos.Kod_organa,
gos.Naimenovanie_organa,
gos.FIO_podpisanta,
gos.Parol_dlia_vxoda_v_IS,
tgos.Kod_tipa_organa,
tgos.Rasshifrovka_koda_tipa_organa
FROM Gos_organ gos JOIN Tipi_gos_organov tgos ON gos.Kod_tipa_organa = tgos.Kod_tipa_organa
GO
CREATE VIEW WillView
AS
SELECT
Fizich.Kod,
Fizich.FIO,
Fizich.Data_rogdenia,
Fizich.Data_smerti,
Zavech.Kod_zavechania,
Zavech.Opisanie_zavechania,
Zavech.Data_sozdania,
gos.Kod_organa,
gos.Naimenovanie_organa
FROM Fizicheskie_lica Fizich JOIN Zavechania Zavech ON Fizich.Kod = Zavech.Kod
join Gos_organ gos on Zavech.Kod_organa=gos.Kod_organa
GO
CREATE VIEW WillItemsView
AS
SELECT
Fizich.Kod,
Fizich.FIO,
Punkti.Kod_punkta_zavechania,
Punkti.Opisanie_imuchestva,
Punkti.Edinici,
Punkti.Kolichestvo,
Zavech.Kod_zavechania
FROM Fizicheskie_lica Fizich JOIN Punkti_zavechania Punkti ON Fizich.Kod = Punkti.Kod
join Zavechania Zavech on Punkti.Kod_zavechania=Zavech.Kod_zavechania
GO
CREATE VIEW HeritageView
AS
SELECT
tgos.Kod_tipa_organa,
tgos.Rasshifrovka_koda_tipa_organa,
gos.Kod_organa,
gos.Naimenovanie_organa,
gos.FIO_podpisanta,
Dela.Kod_dela,
Dela.Data_dela,
Dela.Nomer_dela,
Dela.Elektronnaya_podpis_0_ili_1,
Fizich.Kod,
Fizich.FIO,
Fizich.Data_rogdenia,
Fizich.Data_smerti,
Fizich.Pasportnie_dannie
FROM Tipi_gos_organov tgos JOIN Gos_organ gos ON tgos.Kod_tipa_organa = gos.Kod_tipa_organa
join Dela_o_nasledstve Dela on gos.Kod_organa=Dela.Kod_organa
join Fizicheskie_lica Fizich on Dela.Kod=Fizich.Kod
GO
CREATE VIEW HeritageItemsView
AS
SELECT
raspred.Kod_raspredelenia_nasledstva,
raspred.Stepen_rodstva,
raspred.Naimenovanie_imuchestva,
raspred.Edinici,
raspred.Kolichestvo,
raspred.Gosposhlina,
Fizich.Kod,
Fizich.FIO,
Dela.Kod_dela,
Dela.Data_dela,
Dela.Nomer_dela
FROM Raspredelenie_nasledstva raspred JOIN Fizicheskie_lica fizich ON raspred.Kod = fizich.Kod
join Dela_o_nasledstve Dela on raspred.Kod_dela=Dela.Kod_dela
GO
CREATE VIEW HeritageDoc
AS
SELECT
Docview.Naimenovanie_organa,
Docview.Kod_tipa_organa,
Docview.Naimenovanie_documenta,
Docview.Data_sozdania,
Docview.Seria,
Docview.Nomer,
Docview.Tekstovoe_sodergimoe,
Docview.Tip_documenta,
Docview.FIO_podpisanta,
Docum.Kod_documenta_podtvergdaiuchego_nasledovanie,
Docum.Kod_documenta,
Docum.Kod_organa,
Dela.Kod_dela,
Dela.Data_dela,
Dela.Nomer_dela
FROM Docview JOIN Documenti_podtvergdaiuchie_nasledovanie Docum ON Docview.Kod_documenta = Docum.Kod_documenta
join Dela_o_nasledstve Dela on Docum.Kod_dela=Dela.Kod_dela
GO
CREATE VIEW PrintingformView
AS
SELECT
Fizich.Kod,
Fizich.FIO,
Fizich.Data_rogdenia,
Fizich.Pasportnie_dannie,
raspred.Kod_raspredelenia_nasledstva,
raspred.Kod_dela,
raspred.Stepen_rodstva,
raspred.Naimenovanie_imuchestva,
raspred.Edinici,
raspred.Kolichestvo,
raspred.Gosposhlina
FROM Fizicheskie_lica Fizich JOIN Raspredelenie_nasledstva raspred ON Fizich.Kod = raspred.Kod
GO |
(
Сообщение от ksk
