2kr7bAXp

1USE [Phonebook_Db];
2IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[CITIES]') AND TYPE IN (N'U')) 
3CREATE TABLE [CITIES]
4(
5	[ID] INT NOT NULL IDENTITY(1,1),
6	[UPDATE_COUNTER] INT NOT NULL DEFAULT 0,
7	[NAME] VARCHAR(128) NOT NULL,
8	[PROVINCE_NAME] VARCHAR(128) NOT NULL,
9	CONSTRAINT PK_CITIES_ID PRIMARY KEY(ID)
10)
11
12IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[PERSONS]') AND TYPE IN (N'U')) 
13CREATE TABLE [PERSONS]
14(
15	[ID] INT NOT NULL IDENTITY(1,1),
16	[UPDATE_COUNTER] INT NOT NULL DEFAULT 0,
17	[FIRST_NAME] VARCHAR(128) NOT NULL,	
18	[MIDDLE_NAME] VARCHAR(128) NOT NULL,
19	[LAST_NAME] VARCHAR(128) NOT NULL,
20	[UCN] VARCHAR(32) NOT NULL,
21	[CITY_ID] INT NOT NULL,
22	[ADDRESS] VARCHAR(128) NOT NULL,
23	CONSTRAINT PK_PERSONS_ID PRIMARY KEY(ID),
24	CONSTRAINT UX_PERSONS_UCN UNIQUE (UCN),  
25	CONSTRAINT FK_PERSONS_CITY_ID 
26	FOREIGN KEY (CITY_ID)
27	REFERENCES [CITIES](ID)
28);
29
30IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[PHONE_TYPES]') AND TYPE IN (N'U')) 
31CREATE TABLE [PHONE_TYPES]
32(
33	[ID] INT NOT NULL IDENTITY(1,1),
34	[UPDATE_COUNTER] INT NOT NULL DEFAULT 0,
35	[TYPE] VARCHAR(38) NOT NULL,
36	CONSTRAINT PK_PHONE_TYPES_ID PRIMARY KEY(ID)
37);
38
39
40IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[PHONE_NUMBERS]') AND TYPE IN (N'U')) 
41CREATE TABLE [PHONE_NUMBERS]
42(
43	[ID] INT  NOT NULL IDENTITY(1,1),
44	[UPDATE_COUNTER] INT NOT NULL DEFAULT 0,
45	[PERSON_ID] INT NOT NULL,
46	[PHONE_TYPE_ID] INT NOT NULL,
47	[PHONE] VARCHAR(15) NOT NULL,
48	CONSTRAINT PK_PHONE_NUMBERS_ID PRIMARY KEY(ID),
49	CONSTRAINT FK_PHONE_NUMBERS_PERSON_ID
50	FOREIGN KEY (PERSON_ID)
51	REFERENCES [PERSONS](ID),
52	CONSTRAINT FK_PHONE_NUMBERS_PHONE_TYPE_ID
53	FOREIGN KEY (PHONE_TYPE_ID)
54	REFERENCES [PHONE_TYPES](ID)
55);
56
57	-- Index --
58
59DROP INDEX IF EXISTS [IX_PHONE_NUMBERS_PHONE] ON [dbo].[PHONE_NUMBERS];
60CREATE INDEX IX_PHONE_NUMBERS_PHONE ON [PHONE_NUMBERS] (PHONE);
61
62
63DROP INDEX IF EXISTS IX_PHONE_TYPES_TYPE ON [dbo].[PHONE_TYPES];
64CREATE INDEX IX_PHONE_TYPES_TYPE ON [PHONE_TYPES] ([TYPE]);
65
66
67DROP INDEX IF EXISTS IX_CITIES_NAME ON [dbo].[CITIES];
68CREATE INDEX IX_CITIES_NAME ON [CITIES] ([NAME]);
69