· 5 years ago · Jun 16, 2020, 12:34 PM
1IF EXISTS (SELECT * FROM sysobjects WHERE name= 'PHONE_NUMBERS' and xtype='U')
2 DROP TABLE [PHONE_NUMBERS]
3GO
4
5IF EXISTS (SELECT * FROM sysobjects WHERE name='PERSONS' and xtype='U')
6 DROP TABLE PERSONS
7GO
8
9IF EXISTS (SELECT * FROM sysobjects WHERE name= 'CITIES' and xtype='U')
10 DROP TABLE [CITIES]
11GO
12
13IF EXISTS (SELECT * FROM sysobjects WHERE name='PHONE_TYPES' and xtype='U')
14 DROP TABLE [PHONE_TYPES]
15GO
16
17--Table CITIES
18 CREATE TABLE [CITIES](
19 [CHECKSUM] [int] NOT NULL,
20 [STATUS] [BINARY](4) NOT NULL,
21 [TRCODE] [int] NOT NULL,
22 [ID] [int] NOT NULL,
23 [UPDATE_COUNTER] [int] Default(0) ,
24 [NAME] [char](256) NOT NULL,
25 [REGION] [char](256) NOT NULL,
26
27 CONSTRAINT PK_CITIES_ID PRIMARY KEY (ID)
28 )
29GO
30
31-- INDEX FOR CITIES TABLE
32CREATE INDEX IX_CITIES_NAME
33On CITIES(NAME)
34GO
35
36--Table PHONE_TYPES
37IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='PHONE_TYPES' and xtype='U')
38 CREATE TABLE [PHONE_TYPES](
39 [CHECKSUM] [int] NOT NULL,
40 [STATUS] [BINARY](4) NOT NULL,
41 [TRCODE] [int] NOT NULL,
42 [ID] [int] NOT NULL,
43 [UPDATE_COUNTER] [int] Default(0),
44 [PHONE_TYPE] [char](8) NOT NULL
45
46
47 CONSTRAINT PK_PHONE_TYPES_ID PRIMARY KEY (ID)
48 )
49GO
50
51--INDEX FOR PHONE_TYPES
52CREATE UNIQUE INDEX UX_PHONE_TYPES_NAME
53On PHONE_TYPES(PHONE_TYPE)
54GO
55
56-- Table PERSONS
57IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='PERSONS' and xtype='U')
58 CREATE TABLE [PERSONS](
59 [CHECKSUM] [int] NOT NULL,
60 [STATUS] [BINARY](4) NOT NULL,
61 [TRCODE] [int] NOT NULL,
62 [ID] [int] NOT NULL,
63 [UPDATE_COUNTER] [int] Default(0),
64 [FIRST_NAME] [char](256) NOT NULL,
65 [MIDDLE_NAME] [char](256) NOT NULL,
66 [LAST_NAME] [char](256) NOT NULL,
67 [UCN] [char](32) NOT NULL,
68 [CITY_ID] [int] NOT NULL,
69 [STREET_ADDRESS] [varchar](128) NOT NULL,
70
71 CONSTRAINT PK_PERSONS_ID PRIMARY KEY (ID),
72 CONSTRAINT FK_PERSONS_CITY_ID FOREIGN KEY (CITY_ID) REFERENCES CITIES (ID)
73 )
74GO
75
76-- INDEXES FOR PERSONS TABLE
77CREATE INDEX IX_PERSONS_CITY_ID
78On PERSONS(CITY_ID)
79GO
80
81CREATE UNIQUE INDEX UX_PERSONS_UCN
82On PERSONS(UCN)
83GO
84
85-- Table PHONE_NUMBERS
86IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='PHONE_NUMBERS' and xtype='U')
87 CREATE TABLE [PHONE_NUMBERS](
88 [CHECKSUM] [int] NOT NULL,
89 [STATUS] [BINARY](4) NOT NULL,
90 [TRCODE] [int] NOT NULL,
91 [ID] [int] NOT NULL,
92 [UPDATE_COUNTER] [int] Default(0) ,
93 [PERSON_ID] [int] NOT NULL,
94 [PHONE_TYPE_ID] [int] NOT NULL,
95 [PHONE_NUMBER] [char](16) NOT NULL,
96
97
98 CONSTRAINT PK_PHONE_NUMBERS_ID PRIMARY KEY (ID)
99
100 )
101GO
102
103--ALTER TABLE PHONE_NUMBERS: CREATE FOREIGN KEYS
104ALTER TABLE [PHONE_NUMBERS]
105ADD CONSTRAINT FK_PHONE_NUMBERS_PERSON_ID FOREIGN KEY (PERSON_ID) REFERENCES PERSONS(ID)
106
107ALTER TABLE [PHONE_NUMBERS]
108ADD CONSTRAINT FK_PHONE_NUMBERS_PHONE_TYPE_ID FOREIGN KEY (PHONE_TYPE_ID) REFERENCES PHONE_TYPES(ID)
109
110-- CREATE INDEXES FOR PHONE_NUMBERS TABLE
111CREATE INDEX IX_PHONE_NUMBERS_PERSON_ID
112On PHONE_NUMBERS(PERSON_ID)
113GO
114
115CREATE INDEX IX_PHONE_NUMBERS_PHONE_TYPE_ID
116On PHONE_NUMBERS(PHONE_TYPE_ID)
117GO
118
119CREATE INDEX IX_PHONE_NUMBERS_PHONE_NUMBER
120On PHONE_NUMBERS(PHONE_NUMBER)
121GO