CREATE DATABASE businesssampleDB
GO

USE businesssampleDB

ALTER TABLE [dbo].[伝票情報] DROP CONSTRAINT FK_伝票情報_顧客情報
GO

ALTER TABLE [dbo].[在庫情報] DROP CONSTRAINT FK_在庫情報_製品情報
GO

ALTER TABLE [dbo].[明細情報] DROP CONSTRAINT FK_明細情報_製品情報
GO

ALTER TABLE [dbo].[伝票情報] DROP CONSTRAINT FK_伝票情報_請求書情報
GO

ALTER TABLE [dbo].[在庫情報] DROP CONSTRAINT FK_在庫情報_伝票情報1
GO

ALTER TABLE [dbo].[伝票追加情報] DROP CONSTRAINT FK_伝票追加情報_伝票情報
GO

ALTER TABLE [dbo].[明細情報] DROP CONSTRAINT FK_明細情報_伝票情報
GO

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

if exists (select * from sysobjects where id = object_id(N'[dbo].[伝票追加情報]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[伝票追加情報]
GO

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

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

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

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

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

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

CREATE TABLE [dbo].[顧客情報] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[NAME] [nvarchar] (64) NOT NULL ,
	[YOMIGANA] [nvarchar] (80) NULL ,
	[ZIP] [nvarchar] (10) NULL ,
	[ADDRESS] [nvarchar] (255) NULL ,
	[TELEPHONE] [nvarchar] (32) NULL ,
	[FAX] [nvarchar] (32) NULL ,
	[MEMO] [nvarchar] (80) NULL ,
	[BILLDAY] [int] NULL ,
	[MADEUSER] [nvarchar] (256) NOT NULL ,
	[MADEDATE] [datetime] NOT NULL ,
	[LASTUSER] [nvarchar] (256) NOT NULL ,
	[LASTDATE] [datetime] NOT NULL ,
	[DELETEDFLAG] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[製品情報] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[PRODUCTNAME] [nvarchar] (64) NOT NULL ,
	[YOMIGANA] [nvarchar] (80) NULL ,
	[PRICE] [money] NOT NULL ,
	[STOCK] [int] NOT NULL ,
	[MEMO] [nvarchar] (80) NULL ,
	[BACKORDER] [int] NOT NULL ,
	[MADEUSER] [nvarchar] (256) NOT NULL ,
	[MADEDATE] [datetime] NOT NULL ,
	[LASTUSER] [nvarchar] (256) NOT NULL ,
	[LASTDATE] [datetime] NOT NULL ,
	[DELETEDFLAG] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[請求書情報] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[CUSTOMERID] [int] NOT NULL ,
	[STARTDATE] [datetime] NOT NULL ,
	[ENDDATE] [datetime] NOT NULL ,
	[MADEDATE] [datetime] NOT NULL ,
	[SUBMITDATE] [datetime] NULL ,
	[PAIDFLAG] [bit] NOT NULL ,
	[SENDBILLFLAG] [bit] NOT NULL ,
	[SUBTOTAL] [money] NOT NULL ,
	[TAX] [money] NOT NULL ,
	[TOTAL] [money] NOT NULL ,
	[MEMO] [nchar] (80) NULL ,
	[SUBMITUSER] [nchar] (256) NULL ,
	[MADEUSER] [nvarchar] (256) NOT NULL ,
	[LASTUSER] [nchar] (256) NOT NULL ,
	[LASTDATE] [datetime] NOT NULL ,
	[DELETEDFLAG] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[履歴] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[DATE] [datetime] NOT NULL ,
	[TABLENAME] [nvarchar] (32) NOT NULL ,
	[FIELDNAME] [nvarchar] (32) NOT NULL ,
	[OLDDATA] [nvarchar] (256) NULL ,
	[NEWDATA] [nvarchar] (256) NULL ,
	[UPDATEUSER] [nvarchar] (256) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[伝票情報] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[CUSTOMERID] [int] NOT NULL ,
	[MADEDATE] [datetime] NOT NULL ,
	[SUBTOTAL] [money] NOT NULL ,
	[TAX] [money] NOT NULL ,
	[TOTAL] [money] NOT NULL ,
	[BILLID] [int] NULL ,
	[BILLDATE] [datetime] NULL ,
	[MADEBILLFLAG] [bit] NOT NULL ,
	[ONEBILLFLAG] [bit] NOT NULL ,
	[REQ_CONSENTFLAG] [bit] NOT NULL ,
	[REQ_CONSENTDATE] [datetime] NULL ,
	[REQ_CONSENTCOMMENT] [nvarchar] (80) NULL ,
	[CONSENTEDFLAG] [bit] NOT NULL ,
	[CONSENTEDDATE] [datetime] NULL ,
	[CONSENTEDCOMMENT] [nvarchar] (80) NULL ,
	[REJECTEDFLAG] [bit] NOT NULL ,
	[REJECTEDDATE] [datetime] NULL ,
	[REJECTEDCOMMENT] [nvarchar] (80) NULL ,
	[SENDFLAG] [bit] NOT NULL ,
	[SENDDATE] [datetime] NULL ,
	[SENDCOMMENT] [nvarchar] (80) NULL ,
	[ACCOUNTINGFLAG] [bit] NOT NULL ,
	[ACCOUNTINGDATE] [datetime] NULL ,
	[ACCOUNTINGCOMMENT] [nvarchar] (80) NULL ,
	[MADEUSER] [nvarchar] (256) NOT NULL ,
	[REQ_CONSENTUSER] [nvarchar] (256) NULL ,
	[CONSENTEDUSER] [nvarchar] (256) NULL ,
	[REJECTEDUSER] [nvarchar] (256) NULL ,
	[SENDUSER] [nvarchar] (256) NULL ,
	[ACCOUNTINGUSER] [nvarchar] (256) NULL ,
	[LASTUSER] [nvarchar] (256) NOT NULL ,
	[LASTDATE] [datetime] NOT NULL ,
	[DELETEDFLAG] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[在庫情報] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[DATE] [datetime] NOT NULL ,
	[DUEDATE] [datetime] NULL ,
	[CONFIRMEDFLAG] [bit] NOT NULL ,
	[PRODUCTID] [int] NOT NULL ,
	[NUMBER] [int] NOT NULL ,
	[MEMO] [nvarchar] (80) NULL ,
	[SLIPID] [int] NULL ,
	[MADEUSER] [nvarchar] (256) NOT NULL ,
	[MADEDATE] [datetime] NOT NULL ,
	[LASTUSER] [nvarchar] (256) NOT NULL ,
	[LASTDATE] [datetime] NOT NULL ,
	[DELETEDFLAG] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[伝票追加情報] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[SLIPID] [int] NOT NULL ,
	[DIVISION] [nvarchar] (64) NULL ,
	[PERSON] [nvarchar] (64) NULL ,
	[DELIVERDATE] [datetime] NOT NULL ,
	[SENT_ADDR] [nvarchar] (255) NULL ,
	[SEND_TEL] [nvarchar] (32) NULL ,
	[MEMO] [nvarchar] (80) NULL ,
	[MADEDATE] [datetime] NOT NULL ,
	[MADEUSER] [nvarchar] (256) NOT NULL ,
	[LASTDATE] [datetime] NOT NULL ,
	[LASTUSER] [nvarchar] (256) NOT NULL ,
	[DELETEDFLAG] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[明細情報] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[PRODUCTID] [int] NOT NULL ,
	[NUMBER] [int] NOT NULL ,
	[UNITPRICE] [money] NOT NULL ,
	[PRICE] [money] NOT NULL ,
	[MEMO] [nvarchar] (80) NULL ,
	[SLIPID] [int] NOT NULL ,
	[MADEUSER] [nvarchar] (256) NOT NULL ,
	[MADEDATE] [datetime] NOT NULL ,
	[LASTUSER] [nvarchar] (256) NOT NULL ,
	[LASTDATE] [datetime] NOT NULL ,
	[DELETEDFLAG] [bit] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[顧客情報] WITH NOCHECK ADD 
	CONSTRAINT [PK_顧客情報] PRIMARY KEY  NONCLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[製品情報] WITH NOCHECK ADD 
	CONSTRAINT [PK_製品情報] PRIMARY KEY  NONCLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] ,
	CONSTRAINT [CK_製品情報] CHECK ([STOCK] >= 0)
GO

ALTER TABLE [dbo].[請求書情報] WITH NOCHECK ADD 
	CONSTRAINT [PK_請求書] PRIMARY KEY  NONCLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[履歴] WITH NOCHECK ADD 
	CONSTRAINT [PK_履歴] PRIMARY KEY  NONCLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[伝票情報] WITH NOCHECK ADD 
	CONSTRAINT [PK_伝票情報] PRIMARY KEY  NONCLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[在庫情報] WITH NOCHECK ADD 
	CONSTRAINT [PK_在庫情報] PRIMARY KEY  NONCLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[伝票追加情報] WITH NOCHECK ADD 
	CONSTRAINT [PK_伝票付加情報] PRIMARY KEY  NONCLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[明細情報] WITH NOCHECK ADD 
	CONSTRAINT [PK_詳細情報] PRIMARY KEY  NONCLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[伝票情報] ADD 
	CONSTRAINT [FK_伝票情報_顧客情報] FOREIGN KEY 
	(
		[CUSTOMERID]
	) REFERENCES [dbo].[顧客情報] (
		[ID]
	),
	CONSTRAINT [FK_伝票情報_請求書情報] FOREIGN KEY 
	(
		[BILLID]
	) REFERENCES [dbo].[請求書情報] (
		[ID]
	)
GO

ALTER TABLE [dbo].[在庫情報] ADD 
	CONSTRAINT [FK_在庫情報_製品情報] FOREIGN KEY 
	(
		[PRODUCTID]
	) REFERENCES [dbo].[製品情報] (
		[ID]
	),
	CONSTRAINT [FK_在庫情報_伝票情報1] FOREIGN KEY 
	(
		[SLIPID]
	) REFERENCES [dbo].[伝票情報] (
		[ID]
	)
GO

ALTER TABLE [dbo].[伝票追加情報] ADD 
	CONSTRAINT [FK_伝票追加情報_伝票情報] FOREIGN KEY 
	(
		[SLIPID]
	) REFERENCES [dbo].[伝票情報] (
		[ID]
	)
GO

ALTER TABLE [dbo].[明細情報] ADD 
	CONSTRAINT [FK_明細情報_製品情報] FOREIGN KEY 
	(
		[PRODUCTID]
	) REFERENCES [dbo].[製品情報] (
		[ID]
	),
	CONSTRAINT [FK_明細情報_伝票情報] FOREIGN KEY 
	(
		[SLIPID]
	) REFERENCES [dbo].[伝票情報] (
		[ID]
	)
GO

