2008-04-14
1 固定资产管理系统,刚开始做这个项目,今天刚设计好的数据库,不知道是否符合三大范式,反正自己以为是挺好的了,希望大家指点.
2
3
4
5 use master
6 go
7
8 if exists(select * from sysdatabases where name = 'fixationAssetManageSystem')
9 drop database fixationAssetManageSystem
10 go
11
12 exec xp_cmdshell 'mkdir F:\s1\Jave\FixationAssetManageSystem\FixationAssetManageSystem_database'
13
14 --创建数据库
15 create database fixationAssetManageSystem
16 on primary
17 (
18 name = 'fixationAssetManageSystem_data',
19 filename = 'F:\s1\Jave\FixationAssetManageSystem\FixationAssetManageSystem_database\FixationAssetManageSystem_data.mdf',
20 size = 5mb,
21 filegrowth = 15%
22 )
23 log on
24 (
25 name = 'fixationAssetManageSystem_log',
26 filename = 'F:\s1\Jave\FixationAssetManageSystem\FixationAssetManageSystem_database\FixationAssetManageSystem_log.ldf',
27 size = 3mb,
28 filegrowth = 5%
29 )
30 go
31
32 --打开数据库
33 use fixationAssetManageSystem
34 go
35
36 if exists (select * from sysobjects where name = 'admin')
37 drop table admin
38 go
39 --管理员表
40 create table admin
41 (
42 adminId int identity(1,1) not null,
43 adminName varchar(10) not null,
44 passWord varchar(16) not null
45 )
46
47 go
48
49 --添加约束
50 alter table admin
51 add constraint pk_adminId primary key(adminId)--设置主键
52 alter table admin
53 add constraint ck_passWord check(len(passWord) >= 6 and len(passWord)<=16) --密码长度大于等于6位,小于等于16位
54 go
55
56 if exists (select * from sysobjects where name = 'kind')
57 drop table kind
58 go
59
60 --大类别表
61 create table bigKind
62 (
63 bigKindId varchar(5) primary key not null ,
64 bigKindIdName varchar(20) not null
65 )
66 go
67
68 if exists (select * from sysobjects where name = 'officEquipment')
69 drop table officEquipment
70 go
71
72 --小类别表
73 create table smallKind
74 (
75 smallKindId varchar(5) primary key not null,
76 bigKindId varchar(5) not null,
77 smallKindName varchar(20) not null
78 )
79 go
80
81 alter table smallKind
82 add constraint fk_smallKindName foreign key(bigKindId) references bigKind(bigKindId)
83 go
84
85 /*--办公外设表
86
87 create table officEquipment
88 (
89 officEquipmentId varchar(5) primary key not null,
90 officEquipmentName varchar(20) not null,
91
92 )
93 go
94
95
96 if exists (select * from sysobjects where name = 'numberEquipment')
97 drop table numberEquipment
98 go
99 --数码产品表
100
101 create table numberEquipment
102 (
103 numberEquipmentId varchar(5) primary key not null,
104 numberEquipmentName varchar(20) not null,
105
106 )
107 go
108
109 if exists (select * from sysobjects where name = 'computerEquipment')
110 drop table computerEquipment
111 go
112 --计算机表
113 create table computerEquipment
114 (
115 computerEquipmentId varchar(5) primary key not null,
116 computerEquipmentName varchar(20) not null,
117 )
118 go
119 */
120 if exists (select * from sysobjects where name = 'state')
121 drop table state
122 go
123 --状态表
124 create table state
125 (
126 stateId int primary key not null,
127 stateName char(4) not null --状态
128 )
129
130 go
131 --添加约束
132 alter table state
133 add constraint ck_stateName check(stateName in ('正常','维修','报废'))
134 go
135
136
137 if exists (select * from sysobjects where name = 'duty')
138 drop table duty
139 go
140
141
142 --职务表
143 create table duty
144 (
145 dutyId varchar(5) primary key not null,
146 dutyName varchar(20) not null,
147 )
148 go
149
150 if exists (select * from sysobjects where name = 'personnel')
151 drop table personnel
152 go
153
154 --人员表
155 create table personnel
156 (
157 personnelId int identity(1,1) primary key not null, --人员号
158 personnelName varchar(10) not null, --人员名
159 dutyId varchar(5) not null, --职务号,引用职务表duty中的dutyId
160 reMarks varchar(50) --备注
161 )
162 go
163 --添加约束
164
165 alter table personnel --外键约束,引用职务表duty中的dutyId
166 add constraint fk_dutyId foreign key(dutyId) references duty(dutyId)
167 go
168
169
170 if exists (select * from sysobjects where name = 'equipment')
171 drop table equipment
172 go
173 --固定资产信息表
174
175 create table equipment
176 (
177 equipmentId int identity(1,1) primary key not null,
178 equipmentName varchar(20) not null, --设备名称
179 equipmentType varchar(10) not null,--设备型号
180 equipmentValues money not null,--设备价值
181 buyDate datetime not null, --购买日期
182 stateId int not null,--设备状态号 外键 ,引用状态表中的stateId
183 userId int not null, --使用者ID 外键,引用人员表的personnelID(当没有使用时,UserId为0)
184 smallKindId varchar(5) not null --小类型号 外键,引用三张设备表中的ID
185 )
186 go
187 --添加约束
188
189 alter table equipment --添加外键,引用大类别表的KindId
190 add constraint fk_kindId foreign key(smallKindId) references smallKind(smallKindId)
191
192
193 alter table equipment --外键,引用人员表的personnelID
194 add constraint fk_userId foreign key(userId) references personnel(personnelId)
195 alter table equipment --外键 ,引用状态表中的stateId
196 add constraint fk_stateId foreign key(stateId) references state(stateId)
197 alter table equipment --为使用者添加默认约束
198 add constraint df_userId default(0) for userId
199 go
200
201
202 if exists (select * from sysobjects where name = 'borrow')
203 drop table borrow
204 go
205
206 --资产领用表
207 create table borrow
208 (
209 borrowId int identity(1,1) primary key not null,
210 personnelId int not null,--领用者编号
211 equipmentId int not null,--设置编号
212 date datetime not null,--领用日期
213 adminId int not null,--管理员号
214 purpose varchar(50) not null,--用途
215 remarks varchar(50)--备注
216
217 )
218 go
219
220 alter table borrow
221 add constraint fk_personnelId foreign key(personnelId) references personnel(personnelId)
222 alter table borrow
223 add constraint fk_equipmentId foreign key(equipmentId) references equipment(equipmentId)
224 alter table borrow
225 add constraint ck_date check(date <= getdate())
226 alter table borrow
227 add constraint fk_borrowAdminId foreign key(adminId) references admin(adminId)
228 go
229
230 if exists (select * from sysobjects where name = 'returnEquiment')
231 drop table returnEquiment
232 go
233 --归还表
234 create table returnEquiment
235 (
236 returnId int identity(1,1) primary key not null,
237 equipmentId int not null,--设置编号
238 returnDate datetime not null, --归还日期
239 adminId int not null --管理员
240 )
241
242 alter table returnEquiment
243 add constraint fk_returnEquimentAdminId foreign key (adminId) references admin(adminId)
244 alter table returnEquiment
245 add constraint fk_returnEquipmentId foreign key (equipmentId) references equipment(equipmentId)
246 alter table returnEquiment
247 add constraint ck_returnDate check(returnDate <= getdate())
248
2
3
4
5 use master
6 go
7
8 if exists(select * from sysdatabases where name = 'fixationAssetManageSystem')
9 drop database fixationAssetManageSystem
10 go
11
12 exec xp_cmdshell 'mkdir F:\s1\Jave\FixationAssetManageSystem\FixationAssetManageSystem_database'
13
14 --创建数据库
15 create database fixationAssetManageSystem
16 on primary
17 (
18 name = 'fixationAssetManageSystem_data',
19 filename = 'F:\s1\Jave\FixationAssetManageSystem\FixationAssetManageSystem_database\FixationAssetManageSystem_data.mdf',
20 size = 5mb,
21 filegrowth = 15%
22 )
23 log on
24 (
25 name = 'fixationAssetManageSystem_log',
26 filename = 'F:\s1\Jave\FixationAssetManageSystem\FixationAssetManageSystem_database\FixationAssetManageSystem_log.ldf',
27 size = 3mb,
28 filegrowth = 5%
29 )
30 go
31
32 --打开数据库
33 use fixationAssetManageSystem
34 go
35
36 if exists (select * from sysobjects where name = 'admin')
37 drop table admin
38 go
39 --管理员表
40 create table admin
41 (
42 adminId int identity(1,1) not null,
43 adminName varchar(10) not null,
44 passWord varchar(16) not null
45 )
46
47 go
48
49 --添加约束
50 alter table admin
51 add constraint pk_adminId primary key(adminId)--设置主键
52 alter table admin
53 add constraint ck_passWord check(len(passWord) >= 6 and len(passWord)<=16) --密码长度大于等于6位,小于等于16位
54 go
55
56 if exists (select * from sysobjects where name = 'kind')
57 drop table kind
58 go
59
60 --大类别表
61 create table bigKind
62 (
63 bigKindId varchar(5) primary key not null ,
64 bigKindIdName varchar(20) not null
65 )
66 go
67
68 if exists (select * from sysobjects where name = 'officEquipment')
69 drop table officEquipment
70 go
71
72 --小类别表
73 create table smallKind
74 (
75 smallKindId varchar(5) primary key not null,
76 bigKindId varchar(5) not null,
77 smallKindName varchar(20) not null
78 )
79 go
80
81 alter table smallKind
82 add constraint fk_smallKindName foreign key(bigKindId) references bigKind(bigKindId)
83 go
84
85 /*--办公外设表
86
87 create table officEquipment
88 (
89 officEquipmentId varchar(5) primary key not null,
90 officEquipmentName varchar(20) not null,
91
92 )
93 go
94
95
96 if exists (select * from sysobjects where name = 'numberEquipment')
97 drop table numberEquipment
98 go
99 --数码产品表
100
101 create table numberEquipment
102 (
103 numberEquipmentId varchar(5) primary key not null,
104 numberEquipmentName varchar(20) not null,
105
106 )
107 go
108
109 if exists (select * from sysobjects where name = 'computerEquipment')
110 drop table computerEquipment
111 go
112 --计算机表
113 create table computerEquipment
114 (
115 computerEquipmentId varchar(5) primary key not null,
116 computerEquipmentName varchar(20) not null,
117 )
118 go
119 */
120 if exists (select * from sysobjects where name = 'state')
121 drop table state
122 go
123 --状态表
124 create table state
125 (
126 stateId int primary key not null,
127 stateName char(4) not null --状态
128 )
129
130 go
131 --添加约束
132 alter table state
133 add constraint ck_stateName check(stateName in ('正常','维修','报废'))
134 go
135
136
137 if exists (select * from sysobjects where name = 'duty')
138 drop table duty
139 go
140
141
142 --职务表
143 create table duty
144 (
145 dutyId varchar(5) primary key not null,
146 dutyName varchar(20) not null,
147 )
148 go
149
150 if exists (select * from sysobjects where name = 'personnel')
151 drop table personnel
152 go
153
154 --人员表
155 create table personnel
156 (
157 personnelId int identity(1,1) primary key not null, --人员号
158 personnelName varchar(10) not null, --人员名
159 dutyId varchar(5) not null, --职务号,引用职务表duty中的dutyId
160 reMarks varchar(50) --备注
161 )
162 go
163 --添加约束
164
165 alter table personnel --外键约束,引用职务表duty中的dutyId
166 add constraint fk_dutyId foreign key(dutyId) references duty(dutyId)
167 go
168
169
170 if exists (select * from sysobjects where name = 'equipment')
171 drop table equipment
172 go
173 --固定资产信息表
174
175 create table equipment
176 (
177 equipmentId int identity(1,1) primary key not null,
178 equipmentName varchar(20) not null, --设备名称
179 equipmentType varchar(10) not null,--设备型号
180 equipmentValues money not null,--设备价值
181 buyDate datetime not null, --购买日期
182 stateId int not null,--设备状态号 外键 ,引用状态表中的stateId
183 userId int not null, --使用者ID 外键,引用人员表的personnelID(当没有使用时,UserId为0)
184 smallKindId varchar(5) not null --小类型号 外键,引用三张设备表中的ID
185 )
186 go
187 --添加约束
188
189 alter table equipment --添加外键,引用大类别表的KindId
190 add constraint fk_kindId foreign key(smallKindId) references smallKind(smallKindId)
191
192
193 alter table equipment --外键,引用人员表的personnelID
194 add constraint fk_userId foreign key(userId) references personnel(personnelId)
195 alter table equipment --外键 ,引用状态表中的stateId
196 add constraint fk_stateId foreign key(stateId) references state(stateId)
197 alter table equipment --为使用者添加默认约束
198 add constraint df_userId default(0) for userId
199 go
200
201
202 if exists (select * from sysobjects where name = 'borrow')
203 drop table borrow
204 go
205
206 --资产领用表
207 create table borrow
208 (
209 borrowId int identity(1,1) primary key not null,
210 personnelId int not null,--领用者编号
211 equipmentId int not null,--设置编号
212 date datetime not null,--领用日期
213 adminId int not null,--管理员号
214 purpose varchar(50) not null,--用途
215 remarks varchar(50)--备注
216
217 )
218 go
219
220 alter table borrow
221 add constraint fk_personnelId foreign key(personnelId) references personnel(personnelId)
222 alter table borrow
223 add constraint fk_equipmentId foreign key(equipmentId) references equipment(equipmentId)
224 alter table borrow
225 add constraint ck_date check(date <= getdate())
226 alter table borrow
227 add constraint fk_borrowAdminId foreign key(adminId) references admin(adminId)
228 go
229
230 if exists (select * from sysobjects where name = 'returnEquiment')
231 drop table returnEquiment
232 go
233 --归还表
234 create table returnEquiment
235 (
236 returnId int identity(1,1) primary key not null,
237 equipmentId int not null,--设置编号
238 returnDate datetime not null, --归还日期
239 adminId int not null --管理员
240 )
241
242 alter table returnEquiment
243 add constraint fk_returnEquimentAdminId foreign key (adminId) references admin(adminId)
244 alter table returnEquiment
245 add constraint fk_returnEquipmentId foreign key (equipmentId) references equipment(equipmentId)
246 alter table returnEquiment
247 add constraint ck_returnDate check(returnDate <= getdate())
248
