CREATE TABLE Individuals individual_id int IDENTITY NOT NULL first_nam

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
CREATE TABLE Individuals(
individual_id int IDENTITY(1,1) NOT NULL,
first_name nchar(100) NOT NULL,
surname nchar(100) NOT NULL,
middle_name nchar(100) NULL,
adds nchar(100) NULL,
phone nchar(12) NOT NULL,
birth_date date NULL,
CONSTRAINT PK_Individuals PRIMARY KEY CLUSTERED (individual_id)
)
create table Company(
company_id int identity(1,1) not null,
name nchar(100) not null,
adds nchar(100) null,
phone nchar(12) not null,
constraint PK_Company primary key clustered (company_id)
)
CREATE TABLE Sellers(
seller_id int IDENTITY(1,1) NOT NULL,
name nchar(100) NOT NULL,
surname nchar(100) NOT NULL,
middle_name nchar(100) NOT NULL,
phone nchar(12) NOT NULL,
adds nchar(100) NOT NULL,
CONSTRAINT PK_Sellers PRIMARY KEY CLUSTERED (seller_id)
)
CREATE TABLE CustDiscounts(
cust_discount_id int IDENTITY(1,1) NOT NULL,
cust_discount_name nchar(100) NOT NULL,
discount float NOT NULL,
CONSTRAINT PK_CustDiscounts PRIMARY KEY CLUSTERED (cust_discount_id)
)
create table Customers(
cust_id int IDENTITY(1, 1) NOT NULL,
cust_foreign_id int not null,
cust_discount_id int NOT NULL default (0),
cust_type_id int not null default(0),
constraint PK_Customers primary key clustered (cust_id),
constraint FK_Customers_CustDiscounts foreign key ( cust_discount_id ) references CustDiscounts (cust_discount_id)
ON DELETE CASCADE
ON UPDATE CASCADE
)
create table Categories(
category_id int identity(1, 1) not null,
name nchar(100) not null,
constraint PK_Categories primary key clustered(category_id)
)
CREATE TABLE Gifts(
gift_id int IDENTITY(1,1) NOT NULL,
name nchar(100) NOT NULL,
category_id int NOT NULL,
purchase_price float NOT NULL,
sell_price float NOT NULL,
CONSTRAINT PK_Gifts PRIMARY KEY CLUSTERED (gift_id),
constraint FK_Gifts_Categories foreign key ( category_id ) references Categories (category_id)
ON DELETE CASCADE
ON UPDATE CASCADE
)
CREATE TABLE Orders(
order_id int IDENTITY(1,1) NOT NULL,
customer_id int NOT NULL,
seller_id int NOT NULL,
order_date date NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (order_id),
constraint FK_Orders_Cusomers foreign key ( customer_id ) references Customers (cust_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
constraint FK_Orders_Sellers foreign key ( seller_id ) references Sellers (seller_id)
ON DELETE CASCADE
ON UPDATE CASCADE
)
CREATE TABLE OrdersInfo(
order_info_id int identity(1, 1) not null,
order_id int NOT NULL,
gift_id int NOT NULL,
final_price money NOT NULL,
amount int NOT NULL
constraint PK_OrdersInfo primary key clustered(order_info_id),
constraint FK_OrdersInfo_Orders foreign key ( order_id ) references Orders (order_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
constraint FK_OrdersInfo_Gifts foreign key ( gift_id ) references Gifts (gift_id)
ON DELETE CASCADE
ON UPDATE CASCADE
)
create trigger