資料庫課程設計再續，資料庫課程設計

資料庫課程設計再續，資料庫課程設計
“茶咖啡”咖啡銷售管理系統，新增了 一張表bill2,用於解決一個使用者對應於多個商品的問題，不過寫完之後發現多了幾項冗餘項，還在繼續完善。。。。

代碼如下：

/****** Object:  Table [dbo].[Member]    Script Date: 12/26/2014 17:54:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOINSERT [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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOINSERT [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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOINSERT [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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGO/****** Object:  Table [dbo].[bill2]    Script Date: 12/26/2014 17:54:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOINSERT [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]='女'))GOALTER 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

如下：