加油站管理系统数据库.txt

只是老师布置的一个作业而已。VC下编写的加油站系统

-- 创建数据库

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'GasStation')
	DROP DATABASE [GasStation]
GO

CREATE DATABASE [GasStation]  
ON (
	NAME = N'GasStation_Data', 
	FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL\data\GasStation_Data.MDF' , 
	SIZE = 1, 
	FILEGROWTH = 10%) 
LOG ON (
	NAME = N'GasStation_Log', 
	FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL\data\GasStation_Log.LDF' , 
	SIZE = 1, 
	FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
GO

exec sp_dboption N'GasStation', N'autoclose', N'true'
GO

exec sp_dboption N'GasStation', N'bulkcopy', N'false'
GO

exec sp_dboption N'GasStation', N'trunc. log', N'true'
GO

exec sp_dboption N'GasStation', N'torn page detection', N'true'
GO

exec sp_dboption N'GasStation', N'read only', N'false'
GO

exec sp_dboption N'GasStation', N'dbo use', N'false'
GO

exec sp_dboption N'GasStation', N'single', N'false'
GO

exec sp_dboption N'GasStation', N'autoshrink', N'true'
GO

exec sp_dboption N'GasStation', N'ANSI null default', N'false'
GO

exec sp_dboption N'GasStation', N'recursive triggers', N'false'
GO

exec sp_dboption N'GasStation', N'ANSI nulls', N'false'
GO

exec sp_dboption N'GasStation', N'concat null yields null', N'false'
GO

exec sp_dboption N'GasStation', N'cursor close on commit', N'false'
GO

exec sp_dboption N'GasStation', N'default to local cursor', N'false'
GO

exec sp_dboption N'GasStation', N'quoted identifier', N'false'
GO

exec sp_dboption N'GasStation', N'ANSI warnings', N'false'
GO

exec sp_dboption N'GasStation', N'auto create statistics', N'true'
GO

exec sp_dboption N'GasStation', N'auto update statistics', N'true'
GO

-- 创建表
use [GasStation]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Worker]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Worker]
GO

CREATE TABLE [dbo].[Worker] (
	number 		smallint PRIMARY KEY,
	name 		varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	id 		varchar(18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	type		tinyint,
	password	varchar(12)
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Depot]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Depot]
GO

CREATE TABLE [dbo].[Depot] (
	number 		smallint PRIMARY KEY,
	name	 	varchar(25) COLLATE Chinese_PRC_CI_AS ,
	location 	varchar(25) COLLATE Chinese_PRC_CI_AS ,
	capacity 	float
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GasType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GasType]
GO

CREATE TABLE [dbo].[GasType] (
	number 		smallint PRIMARY KEY,
	name	 	varchar(25) COLLATE Chinese_PRC_CI_AS ,
	price		float
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Storage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Storage]
GO

CREATE TABLE [dbo].[Storage] (
	gas_type	smallint NOT NULL,
	depot		smallint NOT NULL,
	amount		float
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MemberLevel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MemberLevel]
GO

CREATE TABLE [dbo].[MemberLevel] (
	number 		smallint PRIMARY KEY,
	name	 	varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	agio 		float,
	expenditure	float
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Member]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Member]
GO

CREATE TABLE [dbo].[Member] (
	number 		smallint PRIMARY KEY,
	name	 	varchar(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	id	 	varchar(18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	car_number 	varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	expenditure		float
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StockRecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StockRecord]
GO

CREATE TABLE [dbo].[StockRecord] (
	time 		datetime PRIMARY KEY ,
	gas_type	smallint ,
	depot	 	smallint ,
	amount		float ,
	price		float ,
	worker		smallint
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SellRecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SellRecord]
GO

CREATE TABLE [dbo].[SellRecord] (
	time 		datetime PRIMARY KEY ,
	gas_type	smallint ,
	depot	 	smallint ,
	amount		float ,
	price		float ,
	worker		smallint ,
	client		smallint ,
	agio		float	
) ON [PRIMARY]
GO

-- 创建触发器
use [GasStation]

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'ReserveNormalClient')
   DROP TRIGGER ReserveNormalClient
GO

Create Trigger ReserveNormalClient
On MemberLevel
After Delete, Update
As
if not exists(select number from MemberLevel where number = 1)
Begin
	Insert InTo MemberLevel Values(1, '普通会员', 1.0, 0)
End
Else
Begin
	Update MemberLevel set name = '', agio = 1.0, expenditure = 0 where number = 1
End


IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AutoInsertStorage')
   DROP TRIGGER AutoInsertStorage
GO

Create Trigger AutoInsertStorage
On StockRecord
After Insert
As
Declare @gas_type as smallint
Declare	@depot as smallint
Declare	@amount	as float
Declare	@cur_amount as float
select @gas_type = gas_type from inserted
select @depot = depot from inserted
select @amount = amount from inserted
if not exists(select amount from Storage where gas_type = @gas_type and depot = @depot)
Begin
	Insert into Storage values(@gas_type, @depot, @amount)
End
Else
Begin
	select @cur_amount = amount from Storage where gas_type = @gas_type and depot = @depot
	Update Storage set amount = (@amount+@cur_amount) where gas_type = @gas_type and depot = @depot 
End


IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AutoProcessSell')
   DROP TRIGGER AutoProcessSell
GO

Create Trigger AutoProcessSell
On SellRecord
After Insert
As

Declare @gas_type as smallint
Declare	@depot as smallint
Declare	@amount	as float
Declare @client as smallint
Declare	@cur_amount as float
Declare @cur_exp as float
Declare @price as float
Declare @agio as float

select @gas_type = gas_type from inserted
select @depot = depot from inserted
select @amount = amount from inserted

select @cur_amount = amount from Storage where gas_type = @gas_type and depot = @depot
Update Storage set amount = (@cur_amount-@amount) where gas_type = @gas_type and depot = @depot

select @price = price from inserted
select @agio = agio from inserted
select @client = client from inserted
if exists(select number from Member where number = @client)
Begin
	Select @cur_exp = expenditure from Member where number = @client
	Update Member set expenditure = (@cur_exp+@amount*@price*@agio) where number = @client
End
GO

-- 创建用户

INSERT INTO Worker Values(1, 'SuperManager', '000000000000000', 3, '999')