Database Course Design recontinued, database Course Design
The coffee sales management system adds a table bill2 to solve the problem that a user corresponds to multiple products. However, after writing the table, several redundant items are found, still improving ....
The Code is as follows:
/***** 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 '000000', N 'Li yi', N 'male ', n' 15670276163 ') INSERT [dbo]. [Member] ([MemberID], [MemberNAme], [MemberSex], [Memberphone]) VALUES (N '000000', N 'Li 2', N 'female ', N '000000')/****** 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, rows = 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'lanlan', n'20', 10) INSERT [dbo]. [Goods] ([Goods_num], [Goods_name], [Goods_danjia], [Goods_left]) VALUES (n'2', n'cappuccino ', n'25', 10) INSERT [dbo]. [Goods] ([Goods_num], [Goods_name], [Goods_danjia], [Goods_left]) VALUES (n'3', n'latte ', 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 '000000', N '000000', 2010010101, N '000000 ') INSERT [dbo]. [BILL] ([BILL_num], [BILL_time], [BILL_paymoney], [MemberID]) VALUES (N '000000', N '000000', 2010010102, N '000000 ') /***** Object: Table [dbo]. [MERs] Script Date: 12/26/2014 17:54:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ongocreate table [dbo]. [MERs] ([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, rows = OFF, IGNORE_DUP_KEY = OFF, rows = 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 '000000', 1) INSERT [dbo]. [bill2] ([Goods_num], [BILL_num], [bill_sum]) VALUES (N '1', N '000000', 3) INSERT [dbo]. [bill2] ([Goods_num], [BILL_num], [bill_sum]) VALUES (n'2', n'000000', 2) INSERT [dbo]. [bill2] ([Goods_num], [BILL_num], [bill_sum]) VALUES (N '3', N '000000', 4)/***** Object: check [MemberSex] Script Date: 12/26/2014 17:54:54 *****/alter table [dbo]. [Member] with check add constraint [MemberSex] CHECK ([MemberSex] = 'male' OR [MemberSex] = 'female ') goalter table [dbo]. [Member] check constraint [MemberSex] GO/***** Object: ForeignKey [fk1_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 _ 1_3c1cd] 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 _ MERs _ bill1__0ad2a005] Script Date: 12/26/2014 17:54:54 ******/alter table [dbo]. [MERs] with check add foreign key ([BILL_num]) REFERENCES [dbo]. [BILL] ([BILL_num]) GO
As follows: