· 9 years ago · Jan 05, 2017, 10:10 PM
1/*==============================================================*/
2/* DBMS name: Microsoft SQL Server 2008 */
3/* Created on: 05/01/2017 19:54:57 */
4/*==============================================================*/
5
6
7if exists (select 1
8 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
9 where r.fkeyid = object_id('CustomerMeeting') and o.name = 'FK_CUSTOMER_ASSOCIATI_CUSTOMER')
10alter table CustomerMeeting
11 drop constraint FK_CUSTOMER_ASSOCIATI_CUSTOMER
12go
13
14if exists (select 1
15 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
16 where r.fkeyid = object_id('Design') and o.name = 'FK_DESIGN_ASSOCIATI_CUSTOMER')
17alter table Design
18 drop constraint FK_DESIGN_ASSOCIATI_CUSTOMER
19go
20
21if exists (select 1
22 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
23 where r.fkeyid = object_id('RawMaterialInOrder') and o.name = 'FK_RAWMATER_RAWMATERI_RAWMATER')
24alter table RawMaterialInOrder
25 drop constraint FK_RAWMATER_RAWMATERI_RAWMATER
26go
27
28if exists (select 1
29 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
30 where r.fkeyid = object_id('RawMaterialInOrder') and o.name = 'FK_RAWMATER_SUPPLIERO_SUPPLIER')
31alter table RawMaterialInOrder
32 drop constraint FK_RAWMATER_SUPPLIERO_SUPPLIER
33go
34
35if exists (select 1
36 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
37 where r.fkeyid = object_id('SupplierOrder') and o.name = 'FK_SUPPLIER_ASSOCIATI_SUPPLIER')
38alter table SupplierOrder
39 drop constraint FK_SUPPLIER_ASSOCIATI_SUPPLIER
40go
41
42if exists (select 1
43 from sysobjects
44 where id = object_id('Customer')
45 and type = 'U')
46 drop table Customer
47go
48
49if exists (select 1
50 from sysindexes
51 where id = object_id('CustomerMeeting')
52 and name = 'association1_FK'
53 and indid > 0
54 and indid < 255)
55 drop index CustomerMeeting.association1_FK
56go
57
58if exists (select 1
59 from sysobjects
60 where id = object_id('CustomerMeeting')
61 and type = 'U')
62 drop table CustomerMeeting
63go
64
65if exists (select 1
66 from sysindexes
67 where id = object_id('Design')
68 and name = 'Association2_FK'
69 and indid > 0
70 and indid < 255)
71 drop index Design.Association2_FK
72go
73
74if exists (select 1
75 from sysobjects
76 where id = object_id('Design')
77 and type = 'U')
78 drop table Design
79go
80
81if exists (select 1
82 from sysobjects
83 where id = object_id('RawMaterial')
84 and type = 'U')
85 drop table RawMaterial
86go
87
88if exists (select 1
89 from sysindexes
90 where id = object_id('RawMaterialInOrder')
91 and name = 'supplierOrderSOSO_FK'
92 and indid > 0
93 and indid < 255)
94 drop index RawMaterialInOrder.supplierOrderSOSO_FK
95go
96
97if exists (select 1
98 from sysobjects
99 where id = object_id('RawMaterialInOrder')
100 and type = 'U')
101 drop table RawMaterialInOrder
102go
103
104if exists (select 1
105 from sysobjects
106 where id = object_id('Supplier')
107 and type = 'U')
108 drop table Supplier
109go
110
111if exists (select 1
112 from sysindexes
113 where id = object_id('SupplierOrder')
114 and name = 'Association3_FK'
115 and indid > 0
116 and indid < 255)
117 drop index SupplierOrder.Association3_FK
118go
119
120if exists (select 1
121 from sysobjects
122 where id = object_id('SupplierOrder')
123 and type = 'U')
124 drop table SupplierOrder
125go
126
127if exists(select 1 from systypes where name='City')
128 execute sp_unbindrule City
129go
130
131if exists(select 1 from systypes where name='City')
132 drop type City
133go
134
135if exists(select 1 from systypes where name='OrderStatus')
136 execute sp_unbindrule OrderStatus
137go
138
139if exists(select 1 from systypes where name='OrderStatus')
140 drop type OrderStatus
141go
142
143if exists (select 1 from sysobjects where id=object_id('R_City') and type='R')
144 drop rule R_City
145go
146
147if exists (select 1 from sysobjects where id=object_id('R_Status') and type='R')
148 drop rule R_Status
149go
150
151create rule R_City as
152 @column in ('telAviv','ramatGan','hodHasharon','petahTikva')
153go
154
155create rule R_Status as
156 @column in ('yetToBeSent','sent','finished')
157go
158
159/*==============================================================*/
160/* Domain: City */
161/*==============================================================*/
162create type dbo.City
163 from char(11)
164go
165
166execute sp_bindrule R_City, City
167go
168
169/*==============================================================*/
170/* Domain: Status */
171/*==============================================================*/
172create type dbo.OrderStatus
173 from char(11)
174go
175
176execute sp_bindrule R_Status, OrderStatus
177go
178
179/*==============================================================*/
180/* Table: Customer */
181/*==============================================================*/
182create table dbo.Customers (
183 customerID varchar(254) not null,
184 customerEmail varchar(254) null,
185 customerFirstName varchar(254) null,
186 customerLastName varchar(254) null,
187 customerPhone varchar(254) null,
188 customerDateOfBirth datetime null,
189 customerCity City null,
190 constraint PK_CUSTOMER primary key (customerID)
191)
192go
193
194/*==============================================================*/
195/* Table: CustomerMeeting */
196/*==============================================================*/
197create table dbo.CustomerMeetings (
198 customerMeetingDate datetime not null,
199 customerID varchar(254) not null,
200 initialCost int null,
201 constraint PK_CUSTOMERMEETING primary key (customerMeetingDate,customerID)
202)
203go
204drop table dbo.CustomerMeetings
205/*==============================================================*/
206/* Index: association1_FK */
207/*==============================================================*/
208create index association1_FK on dbo.CustomerMeetings (
209customerID ASC
210)
211go
212
213/*==============================================================*/
214/* Table: Design */
215/*==============================================================*/
216create table dbo.Designs (
217 customerID varchar(254) not null,
218 designCreationDate datetime not null,
219 designDescription varchar(254) null,
220 constraint PK_DESIGN primary key (customerID, designCreationDate)
221)
222go
223
224/*==============================================================*/
225/* Index: Association2_FK */
226/*==============================================================*/
227create index Association2_FK on dbo.Designs (
228customerID ASC
229)
230go
231
232/*==============================================================*/
233/* Table: RawMaterial */
234/*==============================================================*/
235create table dbo.RawMaterials (
236 rawMaterialID varchar(254) not null,
237 cost int null,
238 rawMaterialDescription varchar(254) null,
239 constraint PK_RAWMATERIAL primary key (rawMaterialID)
240)
241go
242
243/*==============================================================*/
244/* Table: RawMaterialInOrder */
245/*==============================================================*/
246create table dbo.RawMaterialInOrders (
247 supplierID varchar(254) not null,
248 supplierOrderID int not null,
249 rawMaterialID varchar(254) not null,
250 quantity int null,
251 constraint PK_RAWMATERIALINORDER primary key (supplierID, supplierOrderID, rawMaterialID)
252)
253go
254
255/*==============================================================*/
256/* Index: supplierOrderSOSO_FK */
257/*==============================================================*/
258create index supplierOrderSOSO_FK on dbo.RawMaterialInOrders (
259supplierID ASC,
260supplierOrderID ASC
261)
262go
263
264/*==============================================================*/
265/* Table: Supplier */
266/*==============================================================*/
267create table dbo.Suppliers (
268 supplierID varchar(254) not null,
269 supplierFirstName varchar(254) null,
270 supplierLastName varchar(254) null,
271 supplierEmail varchar(254) null,
272 supplierFaxNumber varchar(254) null,
273 supplierCity City null,
274 constraint PK_SUPPLIER primary key (supplierID)
275)
276go
277
278/*==============================================================*/
279/* Table: SupplierOrder */
280/*==============================================================*/
281create table dbo.SupplierOrders (
282 supplierID varchar(254) not null,
283 supplierOrderID int not null,
284 supplierOrderDate datetime null,
285 supplierOrderStatus OrderStatus null,
286 constraint PK_SUPPLIERORDER primary key (supplierID, supplierOrderID)
287)
288go
289
290/*==============================================================*/
291/* Index: Association3_FK */
292/*==============================================================*/
293create index Association3_FK on dbo.SupplierOrders (
294supplierID ASC
295)
296go
297
298alter table dbo.CustomerMeetings
299 add constraint FK_CUSTOMER_ASSOCIATI_CUSTOMER foreign key (customerID)
300 references dbo.Customers (customerID)
301go
302
303alter table dbo.Designs
304 add constraint FK_DESIGN_ASSOCIATI_CUSTOMER foreign key (customerID)
305 references dbo.Customers (customerID)
306go
307
308alter table dbo.RawMaterialInOrders
309 add constraint FK_RAWMATER_RAWMATERI_RAWMATER foreign key (rawMaterialID)
310 references dbo.RawMaterials (rawMaterialID)
311go
312
313alter table dbo.RawMaterialInOrders
314 add constraint FK_RAWMATER_SUPPLIERO_SUPPLIER foreign key (supplierID, supplierOrderID)
315 references dbo.SupplierOrders (supplierID, supplierOrderID)
316go
317
318alter table dbo.SupplierOrders
319 add constraint FK_SUPPLIER_ASSOCIATI_SUPPLIER foreign key (supplierID)
320 references dbo.Suppliers (supplierID)
321go
322
323
324
325drop database SAD_02
326drop table dbo.CustomerMeetings
327drop table dbo.Customers
328drop table
329insert into dbo.Customers values ('12345','Elor@gmail.com','Elor','Azaria','0000000',1.90,'telAviv')