cZQ3Pmjg

1use master 
2GO 
3
4IF EXISTS ( 
5SELECT name 
6FROM sys.databases 
7WHERE name = N'[ AMitrofanova]' 
8) 
9ALTER DATABASE AMitrofanova set single_user with rollback immediate 
10GO 
11
12IF EXISTS ( 
13SELECT name 
14FROM sys.databases 
15WHERE name = N'AMitrofanova' 
16) 
17DROP DATABASE AMitrofanova 
18GO 
19
20IF OBJECT_ID('AMitrofanova') IS NOT NULL 
21DROP DATABASE AMitrofanova 
22GO 
23
24CREATE DATABASE AMitrofanova 
25GO 
26
27USE AMitrofanova 
28GO 
29
30
31CREATE TABLE Posts 
32( 
33Id_Post int NOT NULL, 
34Name_Post nvarchar(80) NULL, 
35CONSTRAINT Id_Post PRIMARY KEY (Id_Post) 
36) 
37GO 
38
39
40CREATE TABLE NameRegion 
41( 
42Number_Region nvarchar(3) NOT NULL, 
43Name_Region nvarchar(80) NOT NULL, 
44CONSTRAINT Number_Region PRIMARY KEY (Number_Region) 
45) 
46GO 
47
48CREATE TABLE Owners 
49( 
50Surname nvarchar(80) NOT NULL, 
51Penalty int, 
52CONSTRAINT S_name PRIMARY KEY (Surname), 
53) 
54GO 
55
56CREATE TABLE NumbersRegion 
57( 
58Main_Number nvarchar(3) NOT NULL, 
59Number nvarchar(3) NOT NULL, 
60CONSTRAINT NumberReg PRIMARY KEY (Number), 
61CONSTRAINT Region FOREIGN KEY (Main_Number) REFERENCES NameRegion(Number_Region), 
62) 
63GO 
64
65/*CREATE TABLE Cars 
66( 
67Numb_Car nvarchar(6) NOT NULL, 
68Numb_Region int NOT NULL, 
69Mark nvarchar(20) NOT NULL, 
70Color nvarchar(40) NOT NULL, 
71CONSTRAINT CarNumber PRIMARY KEY (Numb_Car, Numb_Region), 
72CONSTRAINT Number_Reg FOREIGN KEY (Numb_Region) REFERENCES NumbersRegion(Number) 
73) 
74GO */ 
75
76
77CREATE TABLE Journal 
78( 
79Id_Post int NOT NULL, 
80Number_Car nvarchar(6) NOT NULL/* CHECK (Number_Car LIKE 
81N'[АВЕКМНОРСТУХавекмнорстух][0-9][0-9][0-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух]' and 
82Number_Car != N'[АВЕКМНОРСТУХавекмнорстух]000[АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух]')*/, 
83Number_Region nvarchar(3) NOT NULL /*CHECK (Number_Region LIKE N'[0-9][0-9]' or Number_Region LIKE N'[127][0-9][0-9]')*/, 
84Owner_Car nvarchar(80) NOT NULL, 
85Time_transit datetime NOT NULL, 
86Direction nvarchar(40) NOT NULL, 
87CONSTRAINT Post FOREIGN KEY (Id_Post) REFERENCES Posts, 
88CONSTRAINT Num_Reg FOREIGN KEY (Number_Region) REFERENCES NumbersRegion, /* 
89CONSTRAINT Car_Number FOREIGN KEY (Number_Car, Number_Region) REFERENCES Cars(Numb_Car, Numb_Region), */ 
90CONSTRAINT OwnerС FOREIGN KEY (Owner_Car) REFERENCES Owners (Surname), 
91) 
92GO 
93
94
95CREATE TRIGGER checkNumber 
96ON Journal 
97FOR INSERT
98AS IF EXISTS (SELECT * FROM inserted 
99WHERE (((inserted.Number_Car not LIKE N'[АВЕКМНОРСТУХавекмнорстух][0-9][0-9][0-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух]')) 
100 or (inserted.Number_Car like N'[АВЕКМНОРСТУХавекмнорстух]000[АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух]')))
101	BEGIN 
102	PRINT 'Ошибка в номере' 
103	ROLLBACK TRANSACTION 
104	END
105
106	SELECT * FROM inserted 
107		WHERE NOT  (inserted.Number_Region  LIKE N'[0-9][0-9]' and
108		inserted.Number_Region  LIKE N'[127][0-9][0-9]')
109	/*WHERE (0 < inserted.Number_Region  AND inserted.Number_Region < 100 or
110		100 < inserted.Number_Region AND inserted.Number_Region< 200 or
111		200 < inserted.Number_Region AND inserted.Number_Region < 300 or
112		700 < inserted.Number_Region AND inserted.Number_Region < 800))*/
113	  
114		PRINT 'Ошибка в номере региона' 
115		ROLLBACK TRANSACTION 
116	 
117	
118
119ALTER TABLE Journal ENABLE TRIGGER checkNumber 
120GO 
121
122INSERT INTO Posts 
123VALUES 
124(1, N'Северный') 
125,(2, N'Южный') 
126,(3, N'Западный') 
127,(4, N'Восточный') 
128GO 
129
130INSERT INTO NameRegion 
131VALUES 
132(N'11', N'Республика Коми') 
133,(N'12', N'Республика Марий Эл') 
134,(N'13', N'Республика Мордовия') 
135,(N'14', N'Республика Саха') 
136,(N'15', N'Республика Северная Осетия — Алания') 
137,(N'16', N'Республика Татарстан') 
138,(N'17', N'Республика Тыва') 
139,(N'18', N'Удмуртская Республика') 
140,(N'19', N'Республика Хакасия') 
141,(N'21', N'Чувашская Республика') 
142,(N'22', N'Алтайский край') 
143,(N'23', N'Краснодарский край') 
144,(N'24', N'Красноярский край') 
145,(N'25', N'Приморский край') 
146GO 
147
148INSERT INTO NumbersRegion 
149VALUES 
150(N'13', N'13') 
151,(N'13', N'113') 
152,(N'16', N'16') 
153,(N'16', N'116') 
154,(N'21', N'21') 
155,(N'21', N'121') 
156,(N'23', N'23') 
157,(N'23', N'93') 
158,(N'23', N'123') 
159,(N'24', N'24') 
160,(N'24', N'84') 
161,(N'24', N'88') 
162,(N'24', N'124') 
163,(N'25', N'25') 
164,(N'25', N'125') 
165GO 
166
167INSERT INTO Owners 
168values 
169(N'Иванов', 100) 
170,(N'Петров', 0) 
171go 
172
173/*insert into Cars 
174values 
175(N'А152АС', 125, N'Lada', N'черный') 
176,(N'А152СС', 125, N'Lada', N'белый') 
177,(N'А931АС', 93, N'opel', N'pink') 
178go */ 
179
180INSERT INTO Journal 
181VALUES 
182(2, N'А152АС', N'805', N'Иванов', N'20161020 10:10:10.000', N'out') 
183,(2, N'А652АС', N'25', N'Иванов', N'20161020 10:10:10.000', N'out') 
184
185GO 
186
187
188/* 
189SELECT dbo.Journal.Number_Car, dbo.Posts.Name_Post, dbo.NameRegion.Name_Region, dbo.Owners.Surname 
190FROM dbo.Cars INNER JOIN 
191dbo.Journal ON dbo.Cars.Numb_Car = dbo.Journal.Number_Car AND dbo.Cars.Numb_Region = dbo.Journal.Number_Region INNER JOIN 
192dbo.NameRegion ON dbo.Journal.Number_Region = dbo.NameRegion.Number_Region INNER JOIN 
193dbo.NumbersRegion ON dbo.Cars.Numb_Region = dbo.NumbersRegion.Number AND dbo.NameRegion.Number_Region = dbo.NumbersRegion.Main_Number INNER JOIN 
194dbo.Owners ON dbo.Journal.Owner_Car = dbo.Owners.Surname INNER JOIN 
195dbo.Posts ON dbo.Journal.Id_Post = dbo.Posts.Id_Post/* 
196where (dbo.Journal.Direction LIKE N'out' and dbo.Journal.Direction LIKE N'in')*/ 
197group by dbo.Journal.Number_Car, dbo.Posts.Name_Post, dbo.NameRegion.Name_Region, dbo.Owners.Surname*/