固定资产数据库设计.sql

公司固定资产管理功能实现

use master
go

create database asset
go

use asset
go

--操作员表
create table operator
(
	name varchar(10) not null primary key ,
	pwd varchar(20)
)
go
--职员信息表
create table employee
(
	empno int primary key,
	ename varchar(10) not null,
	position varchar(40) not null,
	remarks varchar(255)
)
--资产类别表
create table category
(
	cateid int,
	catename varchar(40) not null,
	subcateid int primary key(cateid,subcateid),
	subcatename varchar(40) not null
)
go

--固定资产信息表
create table asset
(
	assetid int primary key,
	asset_name varchar(10) not null,
	cateid int not null ,
	subcateid int not null ,
	model varchar(40),
	price numeric(7,2) not null,
	purchase_date datetime,
	status char(1) check(status in(0,1,2)) default 0,--0正常 1维修 2报废
	useby varchar(10) ,--经手人
	remarks varchar(255)	
)
go
--借用和归还表
create table lend
(
	assetid int primary key foreign key references asset(assetid),
	empno int foreign key references employee(empno),
	usedate smalldatetime not null,
	retdate smalldatetime,
	isreturn char(1) check (isreturn in(0,1)) default 0,
	lender varchar(10) foreign key references operator(name),
	remarks varchar(255)
)
go

/*查看表*/
select * from operator
select * from employee
select * from category
select * from asset
select * from lend


/*插入测试数据*/
insert into operator values('admin','123')
go
insert into employee values(1,'杨明','总裁',null)
insert into employee values(2,'张三','经理',null)
insert into employee values(3,'李四','主任',null)
insert into employee values(4,'王五','秘书',null)
insert into employee values(5,'赵六','后勤',null)
go
insert into category values(1,'办公外设',1,'传真机')
insert into category values(1,'办公外设',2,'打印机')
insert into category values(1,'办公外设',3,'复印机')
insert into category values(1,'办公外设',4,'其他')

insert into category values(2,'数码产品',1,'数码相机')
insert into category values(2,'数码产品',2,'投影仪')

insert into category values(3,'计算机',1,'笔记本电脑')
insert into category values(3,'计算机',2,'台式机')
insert into category values(3,'计算机',3,'服务器')
go
insert into asset values(1,'HP2002',2,2,'惠普投影仪',9800,'2005-7-4',0,'杨明',null)
insert into asset values(2,'SY2002',2,2,'三洋投影仪',9000,'2005-7-4',0,'杨明',null)
insert into asset values(3,'EPSON2002',2,2,'爱普生投影仪',10800,'2005-7-4',0,'杨明',null)
insert into asset values(4,'HP1003',1,2,'惠普打印机',2000,'2005-7-4',0,'杨明',null)
insert into asset values(5,'EPSON0098',1,3,'爱普生复印机',3000,'2005-7-4',0,'杨明',null)
insert into asset values(6,'CZ2005',1,1,'传真机',500,'2005-7-4',0,'杨明',null)

insert into asset values(7,'SONYT9',2,1,'索尼数码相机T9',3800,'2006-7-4',0,'杨明',null)
insert into asset values(8,'SONYT7',2,1,'索尼数码相机T7',4000,'2005-7-4',2,'杨明',null)

insert into asset values(9,'IBM2002',3,1,'IBM便携笔记本',5999,'2005-7-4',0,'杨明',null)
insert into asset values(10,'HP2002',3,1,'HP便携笔记本',6900,'2006-7-4',0,'张三',null)
insert into asset values(11,'BLUESTAR',3,2,'蓝星台式机',3999,'2005-7-4',0,'张三',null)
insert into asset values(12,'JOY2005',3,2,'七喜台式机',4900,'2005-7-4',0,'张三',null)
insert into asset values(13,'联想2005',3,2,'联想2005台式机',5999,'2005-7-4',0,'张三',null)
insert into asset values(14,'BENQ2005',3,3,'明基台式机-服务器',6000,'2006-7-4',0,'张三',null)
go
insert into lend values( 1,3,'2005-9-9',null,default,'admin',null)
insert into lend values( 2,3,'2005-9-9',null,default,'admin',null)
insert into lend values( 9,1,'2005-7-9',null,default,'admin',null)
insert into lend values( 10,2,'2005-9-9',null,default,'admin',null)
go
/*查看表*/
select * from operator
select * from employee
select * from category

select * from lend