if not exists (select * From master.dbo.sysdatabases where name='圖書銷售系統') create database [圖書銷售系統]



use [圖書銷售系統]

--圖書基本資料

if not exists (select * from sysobjects where id = 
object_id(N'[dbo].[BookRecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
begin
	CREATE TABLE [BookRecord] (
		[BookNo] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[BookName] [char] (200) COLLATE Chinese_PRC_CI_AS NULL ,
		[Publisher] [char] (100) COLLATE Chinese_PRC_CI_AS NULL ,
		[Author] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
		[SalesPrice] [money] NULL ,
		[Quantity] [int] NULL ,
		[Discount] [decimal](18, 0) NULL ,
		CONSTRAINT [PK_BookRecord] PRIMARY KEY  CLUSTERED 
		(
			[BookNo]
		)  ON [PRIMARY] 
	) ON [PRIMARY]
end 


--操作員帳號表
if not exists (select * from sysobjects where id = 
object_id(N'[dbo].[Operator]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
begin
	CREATE TABLE [Operator] (
		[Account] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[Name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
		[Password] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
		[Isdeleted] [bit] NULL ,
		CONSTRAINT [PK_Operator] PRIMARY KEY  CLUSTERED 
		(
			[Account]
		)  ON [PRIMARY] 
	) ON [PRIMARY]
end 

--圖書銷售表

if not exists (select * from sysobjects where id = 
object_id(N'[dbo].[BookSale]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
begin
	CREATE TABLE [BookSale] (
		[Sn] [int] NOT NULL ,
		[Account] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
		[SaleDate] [datetime] NULL ,
		[SumMoney] [money] NULL ,
		CONSTRAINT [PK_BookSale] PRIMARY KEY  CLUSTERED 
		(
			[Sn]
		)  ON [PRIMARY] ,
		CONSTRAINT [FK_BookSale_Operator] FOREIGN KEY 
		(
			[Account]
		) REFERENCES [Operator] (
			[Account]
		) ON DELETE CASCADE  ON UPDATE CASCADE 
	) ON [PRIMARY]
end 

--圖書入庫表
if not exists (select * from sysobjects where id = 
object_id(N'[dbo].[InRecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
begin
	CREATE TABLE [InRecord] (
		[sn] [int] NOT NULL ,
		[account] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
		[BookNo] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
		[BookName] [char] (200) COLLATE Chinese_PRC_CI_AS NULL ,
		[Quantity] [int] NULL ,
		[Inprice] [money] NULL ,
		[SalesPrice] [money] NULL ,
		[InDate] [datetime] NULL ,
		CONSTRAINT [PK_InRecord] PRIMARY KEY  CLUSTERED 
		(
			[sn]
		)  ON [PRIMARY] ,
		CONSTRAINT [FK_InRecord_BookRecord] FOREIGN KEY 
		(
			[BookNo]
		) REFERENCES [BookRecord] (
			[BookNo]
		) ON DELETE CASCADE  ON UPDATE CASCADE ,
		CONSTRAINT [FK_InRecord_Operator] FOREIGN KEY 
		(
			[account]
		) REFERENCES [Operator] (
			[Account]
		) ON DELETE CASCADE  ON UPDATE CASCADE 
	) ON [PRIMARY]
end 
use [圖書銷售系統]
--價格維護表
if not exists (select * from sysobjects where id = 
object_id(N'[dbo].[MantainRecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
begin
	CREATE TABLE [MantainRecord] (
		[sn] [int] NOT NULL ,
		[MantainDate] [datetime] NOT NULL ,
		[BookNo] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[Account] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
		[Operation] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
		[OriginalData] [decimal](18, 0) NULL ,
		[NewDate] [decimal](18, 0) NULL ,
		CONSTRAINT [PK_MantainRecord] PRIMARY KEY  CLUSTERED 
		(
			[sn]
		)  ON [PRIMARY] ,
		CONSTRAINT [FK_MantainRecord_BookRecord] FOREIGN KEY 
		(
			[BookNo]
		) REFERENCES [BookRecord] (
			[BookNo]
		) ON DELETE CASCADE  ON UPDATE CASCADE ,
		CONSTRAINT [FK_MantainRecord_Operator] FOREIGN KEY 
		(
			[Account]
		) REFERENCES [Operator] (
			[Account]
		) ON DELETE CASCADE  ON UPDATE CASCADE 
	) ON [PRIMARY]
end 


--銷售詳細信息表
if not exists (select * from sysobjects where id = 
object_id(N'[dbo].[SalesDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
begin
CREATE TABLE [SalesDetail] (
	[Sn] [int] NOT NULL ,
	[Account] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[BookNo] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[BookName] [char] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[SalePrice] [money] NULL ,
	[Quantity] [int] NULL ,
	[Discount] [decimal](18, 0) NULL ,
	CONSTRAINT [PK_SalesDetail] PRIMARY KEY  CLUSTERED 
	(
		[Sn]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_SalesDetail_BookRecord] FOREIGN KEY 
	(
		[BookNo]
	) REFERENCES [BookRecord] (
		[BookNo]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_SalesDetail_Operator] FOREIGN KEY 
	(
		[Account]
	) REFERENCES [Operator] (
		[Account]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
) ON [PRIMARY]
end