数据库课程设计再续

“茶咖啡”咖啡销售管理系统，新增了 一张表bill2,用于解决一个用户对应于多个商品的问题，不过写完之后发现多了几项冗余项，还在继续完善。。 代码如下： /****** Object: Table [dbo].[Member] Script Date: 12/26/2014 17:54:54 ******/SET ANSI_NULLS

代码如下：

/****** Object: Table [dbo].[Member] Script Date: 12/26/2014 17:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Member](
	[MemberID] [char](11) NOT NULL,
	[MemberNAme] [char](10) NOT NULL,
	[MemberSex] [char](2) NOT NULL,
	[Memberphone] [varchar](12) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[MemberID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Member] ([MemberID], [MemberNAme], [MemberSex], [Memberphone]) VALUES (N'2000010101 ', N'李一 ', N'男', N'15670276163')
INSERT [dbo].[Member] ([MemberID], [MemberNAme], [MemberSex], [Memberphone]) VALUES (N'2000010202 ', N'李二 ', N'女', N'15670276162')
/****** Object: Table [dbo].[Goods] Script Date: 12/26/2014 17:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Goods](
	[Goods_num] [char](4) NOT NULL,
	[Goods_name] [char](10) NULL,
	[Goods_danjia] [char](10) NULL,
	[Goods_left] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[Goods_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Goods] ([Goods_num], [Goods_name], [Goods_danjia], [Goods_left]) VALUES (N'1 ', N'蓝山 ', N'20 ', 10)
INSERT [dbo].[Goods] ([Goods_num], [Goods_name], [Goods_danjia], [Goods_left]) VALUES (N'2 ', N'卡布奇诺 ', N'25 ', 10)
INSERT [dbo].[Goods] ([Goods_num], [Goods_name], [Goods_danjia], [Goods_left]) VALUES (N'3 ', N'拿铁 ', N'30 ', 10)
/****** Object: Table [dbo].[BILL] Script Date: 12/26/2014 17:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BILL](
	[BILL_num] [char](10) NOT NULL,
	[BILL_time] [char](8) NOT NULL,
	[BILL_paymoney] [money] NOT NULL,
	[MemberID] [char](11) NULL,
PRIMARY KEY CLUSTERED 
(
	[BILL_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[BILL] ([BILL_num], [BILL_time], [BILL_paymoney], [MemberID]) VALUES (N'2010010101', N'20100101', 100.0000, N'2000010101 ')
INSERT [dbo].[BILL] ([BILL_num], [BILL_time], [BILL_paymoney], [MemberID]) VALUES (N'2010010102', N'20000102', 200.0000, N'2000010202 ')
/****** Object: Table [dbo].[Customers] Script Date: 12/26/2014 17:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customers](
	[Customers_num] [char](10) NOT NULL,
	[BILL_num] [char](10) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Customers_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[bill2] Script Date: 12/26/2014 17:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[bill2](
	[Goods_num] [char](4) NOT NULL,
	[BILL_num] [char](10) NOT NULL,
	[bill_sum] [int] NULL,
 CONSTRAINT [fk_goodsinfor1] PRIMARY KEY CLUSTERED 
(
	[Goods_num] ASC,
	[BILL_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PA【本文来自鸿网互联 (http://www.68idc.cn)】DDING OFF
GO
INSERT [dbo].[bill2] ([Goods_num], [BILL_num], [bill_sum]) VALUES (N'1 ', N'2010010101', 1)
INSERT [dbo].[bill2] ([Goods_num], [BILL_num], [bill_sum]) VALUES (N'1 ', N'2010010102', 3)
INSERT [dbo].[bill2] ([Goods_num], [BILL_num], [bill_sum]) VALUES (N'2 ', N'2010010101', 2)
INSERT [dbo].[bill2] ([Goods_num], [BILL_num], [bill_sum]) VALUES (N'3 ', N'2010010102', 4)
/****** Object: Check [MemberSex] Script Date: 12/26/2014 17:54:54 ******/
ALTER TABLE [dbo].[Member] WITH CHECK ADD CONSTRAINT [MemberSex] CHECK (([MemberSex]='男' OR [MemberSex]='女'))
GO
ALTER TABLE [dbo].[Member] CHECK CONSTRAINT [MemberSex]
GO
/****** Object: ForeignKey [FK__BILL__MemberID__060DEAE8] Script Date: 12/26/2014 17:54:54 ******/
ALTER TABLE [dbo].[BILL] WITH CHECK ADD FOREIGN KEY([MemberID])
REFERENCES [dbo].[Member] ([MemberID])
GO
/****** Object: ForeignKey [FK__bill2__BILL_num__1273C1CD] Script Date: 12/26/2014 17:54:54 ******/
ALTER TABLE [dbo].[bill2] WITH CHECK ADD FOREIGN KEY([BILL_num])
REFERENCES [dbo].[BILL] ([BILL_num])
GO
/****** Object: ForeignKey [FK__bill2__Goods_num__117F9D94] Script Date: 12/26/2014 17:54:54 ******/
ALTER TABLE [dbo].[bill2] WITH CHECK ADD FOREIGN KEY([Goods_num])
REFERENCES [dbo].[Goods] ([Goods_num])
GO
/****** Object: ForeignKey [FK__Customers__BILL___0AD2A005] Script Date: 12/26/2014 17:54:54 ******/
ALTER TABLE [dbo].[Customers] WITH CHECK ADD FOREIGN KEY([BILL_num])
REFERENCES [dbo].[BILL] ([BILL_num])
GO