· 9 years ago · Nov 07, 2016, 10:46 AM
1USE master
2GO
3
4IF EXISTS (
5 SELECT name
6 FROM sys.databases
7 WHERE name = N'UlianarKvashnina2'
8)
9ALTER DATABASE [UlianaKvashnina2] set single_user with rollback immediate
10GO
11
12IF EXISTS (
13 SELECT name
14 FROM sys.databases
15 WHERE name = N'UlianarKvashnina2'
16)
17DROP DATABASE [UlianarKvashnina2]
18GO
19
20CREATE DATABASE [UlianarKvashnina2]
21GO
22
23USE [UlianarKvashnina2]
24GO
25
26IF OBJECT_ID('Passages', 'U') IS NOT NULL
27 DROP TABLE Passages
28GO
29IF OBJECT_ID('Posts', 'U') IS NOT NULL
30 DROP TABLE Posts
31GO
32IF OBJECT_ID('Plate_Regions', 'U') IS NOT NULL
33 DROP TABLE Plate_Regions
34GO
35IF OBJECT_ID('Region', 'U') IS NOT NULL
36 DROP TABLE Region
37GO
38IF OBJECT_ID('Cars', 'U') IS NOT NULL
39 DROP TABLE Cars
40GO
41IF OBJECT_ID('Colors', 'U') IS NOT NULL
42 DROP TABLE Colors
43GO
44IF OBJECT_ID('Car_Models', 'U') IS NOT NULL
45 DROP TABLE Car_Models
46GO
47
48create table Colors (
49 Color_ID int NOT NULL,
50 Color nvarchar(100) NOT NULL,
51 check (Color_ID > 0),
52 constraint pk_Color_ID primary key (Color_ID)
53)
54go
55
56insert into Colors values
57 (1, N'КраÑный'),
58 (2, N'Желтый'),
59 (3, N'Зеленый')
60go
61
62SELECT * From Colors
63go
64
65create table Region (
66 Region_Code int NOT NULL,
67 Region_Name nvarchar(100) NOT NULL,
68 constraint pk_Region_Code primary key (Region_Code)
69)
70go
71
72insert into Region values
73 (1, N'РеÑпублика ДагеÑтан'),
74 (2, N'РеÑпублика ИнгушетиÑ'),
75 (3, N'Кабардино-БалкарÑÐºÐ°Ñ Ð ÐµÑпублика'),
76 (4, N'РеÑпублика КалмыкиÑ'),
77 (5, N'Карачаево-ЧеркеÑÑÐºÐ°Ñ Ð ÐµÑпублика')
78go
79
80SELECT * From Region
81go
82
83
84create table Plate_Regions (
85 Region_Code int NOT NULL,
86 Plate_Region int NOT NULL,
87 constraint pk_Plate_Region primary key (Plate_Region),
88 constraint fk_Region_Code foreign key (Region_Code)
89 REFERENCES Region(Region_Code)
90 ON UPDATE CASCADE
91)
92
93insert into Plate_Regions values
94 (1, 1),
95 (2, 2),
96 (2, 102),
97 (3, 3)
98go
99
100
101SELECT * From Plate_Regions
102go
103
104
105create table Car_Models(
106 Model_ID int NOT NULL,
107 Model nvarchar(100) NOT NULL,
108 constraint pk_Model_ID primary key (Model_ID)
109)
110go
111
112insert into Car_Models values
113 (1, N'Мазда'),
114 (2, N'Ðуди'),
115 (3, N'Хьюндай')
116go
117
118SELECT * From Car_Models
119go
120
121create table Cars (
122 Model_ID int NOT NULL,
123 Color_ID int NOT NULL,
124 Region int NOT NULL,
125 Number nvarchar(100) NULL,
126 constraint fk_Region foreign key (Region)
127 REFERENCES Plate_Regions(Plate_Region)
128 ON UPDATE CASCADE,
129 /*check (Number like N'[ÐВЕКМÐОРСТУХ][0-9][0-9][0-9][ÐВЕКМÐОРСТУХ][ÐВЕКМÐОРСТУХ]'),*/
130 constraint fk_Color_ID foreign key (Color_ID)
131 REFERENCES Colors(Color_ID)
132 ON UPDATE CASCADE,
133 constraint fk_Model_ID foreign key (Model_ID)
134 REFERENCES Car_Models(Model_ID)
135 ON UPDATE CASCADE,
136
137)
138
139insert into Cars values
140 (1, 1, 1, N'Ð226ЩД'),
141 (1, 2, 3, N'Ð546ОГ'),
142 (1, 2, 102, N'Л984ГО'),
143 (2, 3, 1, N'Ð 765ОÐ'),
144 (3, 2, 102, N'В453ПП'),
145 (2, 2, 2, N'Ð892ÐЧ'),
146 (1, 3, 1, N'У564КВ'),
147 (3, 3, 3, N'У673РП'),
148 (3, 2, 3, N'Ш985ЛЛ'),
149 (2, 2, 3, N'К453ÐУ'),
150 (2, 1, 3, N'Ð 978ÐÐ'),
151 (2, 3, 3, N'О278ОÐ'),
152 (1, 1, 1, N'Ð764КÐ')
153go
154
155SELECT * From Cars
156go
157
158create table Passages (
159 Post_ID int NOT NULL,
160 Number_ nvarchar(100) NULL,
161)
162go
163
164
165insert into Passages values
166 (1, N'Ð226ЩД'),
167 (2, N'Ð 765ОÐ')
168go
169
170SELECT * From Passages
171go