sql server商品进销存管理系统设计及实现的数据库源代码_sql server数据库设计:商品库存管理系统

use master
if exists(select * from sysdatabases where name='NorthwindSQL')  --判断NorthwindSQL库是否存在，若存在则先删除
   drop database NorthwindSQL

create database NorthwindSQL                          --创建数据库NorthwindSQL
on primary
(
name='NorthwindSQL',
filename='D:\sqldata\NorthwindSQL.mdf',
size=5,
filegrowth=10%
)
log on
(
name='NorthwindSQL_log',
filename='D:\sqldata\NorthwindSQL_log.ldf',
size=3,
filegrowth=10%
)
 
use NorthwindSQL
create table Staff(St_ID int identity not null,            --创建员工表
St_number char(10) not null,
Name varchar(10) null,
sex bit not null,
St_address varchar(42) null,
St_zipcode char(5) null,
St_telephone char(10) null,
St_birthday datetime null,
St_marriage bit not null,
Employ_date datetime null,
Start_money money null,
Now_money money null,
Addmoney_date datetime null,
Department varchar(10) null,
Exe_director varchar(10) null
)


create table Customer(Cu_ID nvarchar(5) not null,          --创建客户表
corporate_name nvarchar(40) not null,
Contact_person nvarchar(30) null,
Contact_n nvarchar(30) null,
address nvarchar(60) null,
City nvarchar(15) null,
Ad_region nvarchar(15) null,
Ad_number nvarchar(10) null,
Country nvarchar(15) null,
Telephone nvarchar(24) null,
Fax nvarchar(24) null
 )
 
create table Order_file(Order_number int identity not null,   --创建订货主档表
Cu_ID nvarchar(5) null,
St_ID int null,
Order_date datetime null,
Require_date datetime null,
Delivery_date datetime null,
Delivery_mode int null,
Trans_money money null,
Get_people nvarchar(40) null,
Delivery_address nvarchar(60) null,
Delivery_city nvarchar(15) null,
Delivery_Ad nvarchar(15) null,
Delivery_Adnumber nvarchar(10) null,
Delivery_country nvarchar(15) null
 )

 
 create table Order_schedule(Order_ID int not null,     --创建订货明细表
 Product_ID int not null,
 O_money money not null,
 Counts smallint not null,
 Discount real not null,
 )
 
 create table Product_info(Product_ID int identity not null,  --创建产品资料表
 Product nvarchar(40) not null,
 Supplier_number int null,
 Category_ID int null,
 DW_count nvarchar(20) null,
 O_money money not null,
 Inventory smallint null,
 YD_count smallint null,
 Safety_stock smallint null,
 No_sale bit not null
 )

 
 create table Product_category( Category_ID int identity not null, --创建产品类别表
 Category_name nvarchar(15) not null,
 Instruc ntext null,
 Picture image null
 )
 
 --创建约束
 
 alter table Staff add constraint pK_St_ID primary key (St_ID)  --添加主键约束
 alter table Staff add constraint uq_St_number unique (St_number) --添加唯一约束
 
 alter table Customer add constraint pk_Cu_ID primary key(Cu_ID)
 
 alter table Order_file add constraint pk_Order_number primary key(Order_number)
 alter table Order_file add constraint df_Trans_money default(0) for Trans_money  --添加默认值为0
 
 alter table Order_schedule add constraint pk_Order_ID primary key (Order_ID)
 alter table Order_schedule add constraint df_O_money default(0) for O_money
 alter table Order_schedule add constraint df_Counts default(1) for Counts
 alter table Order_schedule add constraint df_Discount default(0) for Discount
  
 alter table Product_info add constraint pk_Product_ID primary key (Product_ID)
 alter table Product_info add constraint df_O_money default(0) for O_money
 alter table Product_info add constraint df_Inventory default(0) for Inventory
 alter table Product_info add constraint df_YD_count default(0) for YD_count
 alter table Product_info add constraint df_Safety_stock default(0) for Safety_stock
 alter table Product_info add constraint df_No_sale default(0) for No_sale
 
 
 alter table Product_category add constraint pk_Category_ID primary key(Category_ID)
 

 alter table Order_file add constraint fk_St_ID foreign key(St_ID) references Staff(St_ID)--添加主外键约束
 alter table Order_file add constraint fk_Cu_ID foreign key(Cu_ID) references Customer(Cu_ID)
 alter table Order_file add constraint fk_Order_number foreign key(Order_number) references Order_schedule(Order_ID)
 alter table Order_schedule add constraint fk_Product_ID foreign key(Product_ID) references Product_info(Product_ID)
 alter table Order_schedule add constraint fk_O_money foreign key(O_money) references Product_info(O_money)
 alter table Product_info add constraint fk_Category_ID foreign key(Category_ID)references Product_category(Category_ID)
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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127

因为没有得到数据，只能先建好数据库，等得到数据后，再慢慢完善吧