sql.txt

固定资产管理系统包括一个基本的部分

create database Mythsoft




CREATE TABLE asset_class1 (
	asset_class1_id		int				IDENTITY		PRIMARY KEY,
	asset_class1_name	varchar(20)		NOT NULL		UNIQUE,
	asset_class1_memo	varchar(200)		NULL,
	tag					bit				NOT NULL		DEFAULT 1
) 



CREATE TABLE asset_class2 (
	asset_class2_id		int				IDENTITY		PRIMARY KEY,
	asset_class2_name	varchar(20)		NOT NULL		UNIQUE,
	asset_class1_id		int				NOT NULL,
	asset_class2_memo	varchar(200)		NULL,
	tag					bit				NOT NULL		DEFAULT 1,	
	CONSTRAINT FK_asset_class2_asset_class1 FOREIGN KEY (asset_class1_id)
		REFERENCES asset_class1 (asset_class1_id)
)



CREATE TABLE employee (
	emp_id				int				IDENTITY		PRIMARY KEY,
	emp_name			varchar(20)		NOT NULL,
	emp_duty			varchar(20)		NOT NULL,
	emp_memo			varchar(200)		NULL,
	emp_state			bit				NOT NULL		DEFAULT 1
)


CREATE TABLE admin (
	admin_name			varchar(20)		NOT NULL		PRIMARY KEY,
	password			varchar(22)		NOT NULL
)



create table asset_state (
	asset_state_id		int				IDENTITY		PRIMARY KEY,
	asset_state_name		varchar(10)		NOT NULL
)



CREATE TABLE assets (
	asset_id				int				IDENTITY		PRIMARY KEY,
	asset_class2_id		int				NOT NULL,
	asset_name			varchar(40)		NOT NULL,
	asset_type			varchar(40)		NOT NULL,
	asset_value			decimal(9,2)		NOT NULL
		CHECK (asset_value>2000.00),
	asset_purchase_date	datetime			NOT NULL,
	asset_state_id		int				NOT NULL,
	asset_emp_id		int				NULL,
	asset_memo			varchar(200)		NULL,
	tag					bit				NOT NULL		DEFAULT 1,
	CONSTRAINT FK_assets_asset_class2 FOREIGN KEY (asset_class2_id)
		REFERENCES asset_class2 (asset_class2_id),
	CONSTRAINT FK_assets_asset_state FOREIGN KEY (asset_state_id)
		REFERENCES asset_state (asset_state_id),
	CONSTRAINT FK_assets_employee FOREIGN KEY (asset_emp_id)
		REFERENCES employee (emp_id)
)



CREATE TABLE asset_using (
	id					int				IDENTITY		PRIMARY KEY,
	asset_id				int				NOT NULL,
	emp_id				int				NOT NULL,
	borrow_date			datetime			NOT NULL,
	borrow_admin_name	varchar(20)		NOT NULL,
	return_date			datetime			NULL,
	return_admin_name	varchar(20)		NULL,
	asset_purpose		varchar(100)		NOT NULL,
	use_memo			varchar(200)		NULL
	CONSTRAINT FK_asset_using_assets FOREIGN KEY (asset_id)
		REFERENCES assets (asset_id),
	CONSTRAINT FK_asset_using_employee FOREIGN KEY (emp_id)
		REFERENCES employee (emp_id),
	CONSTRAINT FK_asset_using_admin FOREIGN KEY (borrow_admin_name)
		REFERENCES admin (admin_name),
	CONSTRAINT FK_asset_using_admin1 FOREIGN KEY (return_admin_name)
		REFERENCES admin (admin_name)
)


CREATE VIEW dbo.asset_class_view
AS
SELECT asset_class1_id AS asset_class_id, '----' AS parent_name,
		asset_class1_name AS asset_class_name, asset_class1_memo AS asset_class_memo
	FROM asset_class1
UNION ALL
SELECT a.asset_class2_id, b.asset_class1_name, a.asset_class2_name, a.asset_class2_memo
	FROM asset_class2 a
INNER JOIN asset_class1 b ON a.asset_class1_id = b.asset_class1_id




insert into asset_class1(asset_class1_name)
values ('办公外设')
insert into asset_class1(asset_class1_name)
values ('数码产品')
insert into asset_class1(asset_class1_name)
values ('计算机')

insert into asset_class2(asset_class2_name,asset_class1_id)
values ('传真机',1)
insert into asset_class2(asset_class2_name,asset_class1_id)
values ('复印机',1)
insert into asset_class2(asset_class2_name,asset_class1_id)
values ('打印机',1)
insert into asset_class2(asset_class2_name,asset_class1_id)
values ('其它',1)


insert into asset_class2(asset_class2_name,asset_class1_id)
values ('数码相机',2)
insert into asset_class2(asset_class2_name,asset_class1_id)
values ('投影仪',2)


insert into asset_class2(asset_class2_name,asset_class1_id)
values ('笔记本电脑',3)
insert into asset_class2(asset_class2_name,asset_class1_id)
values ('台式机',3)
insert into asset_class2(asset_class2_name,asset_class1_id)
values ('服务器',3)

insert into asset_state values('正常')
insert into asset_state values('维修')
insert into asset_state values('报废')