智慧社区商超管理系统作业

在智慧社区商超管理系统中，通常有多种不同角色的人员使用该系统，超市里面有各种各样不同的商品，商品都有不同的供应商。

1.根据上述语义画出ER图

-1dfa84b8e3356e16.png

2.将ER模型转换成数据表，并指出每个数据表的主键。

6bde8f2ec92bcf47.jpg -30e615955c281c0e.jpg -538a66b45c53386a.jpg -4835e88ac62f4120.jpg 7549f56d7bc59104.jpg -ce2a4486d04ea0f.jpg

3.根据建立的数据表，在SQL Server 2008中建表

建表命令如下：
USE [duanyaqi]
GO
/****** Object: Table [dbo].[收银员] Script Date: 11/04/2018 13:14:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[收银员](
[工号] varchar NOT NULL,
[姓名] varchar NOT NULL,
[密码] varchar NOT NULL,
[电话] varchar NOT NULL,
CONSTRAINT [PK_收银员] PRIMARY KEY CLUSTERED
(
[工号] 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].[商品销售清单] Script Date: 11/04/2018 13:14:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[商品销售清单](
[流水号] varchar NOT NULL,
[收银员工号] varchar NOT NULL,
[商品号] varchar NOT NULL,
[销售时间] [datetime] NOT NULL,
CONSTRAINT [PK_商品销售清单] PRIMARY KEY CLUSTERED
(
[流水号] 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].[商品录入清单] Script Date: 11/04/2018 13:14:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[商品录入清单](
[流水号] varchar NOT NULL,
[库管员工号] varchar NOT NULL,
[商品号] varchar NOT NULL,
[录入时间] [datetime] NOT NULL,
CONSTRAINT [PK_商品录入清单] PRIMARY KEY CLUSTERED
(
[流水号] 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].[商品] Script Date: 11/04/2018 13:14:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[商品](
[商品号] varchar NOT NULL,
[商品名称] varchar NOT NULL,
[价格] [decimal](10, 2) NOT NULL,
[供应商] varchar NOT NULL,
CONSTRAINT [PK_商品] PRIMARY KEY CLUSTERED
(
[商品号] 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].[库管员] Script Date: 11/04/2018 13:14:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[库管员](
[工号] varchar NOT NULL,
[姓名] varchar NOT NULL,
[密码] varchar NOT NULL,
[电话] varchar NULL,
CONSTRAINT [PK_库管员] PRIMARY KEY CLUSTERED
(
[工号] 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].[供应商] Script Date: 11/04/2018 13:14:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[供应商](
[代码] varchar NOT NULL,
[名称] varchar NOT NULL,
[地点] varchar NOT NULL,
[联系人] varchar NOT NULL,
[电话] varchar NOT NULL,
[备注] varchar NULL,
CONSTRAINT [PK_供应商] PRIMARY KEY CLUSTERED
(
[代码] 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: ForeignKey [FK_商品供应商] Script Date: 11/04/2018 13:14:48 ******/
ALTER TABLE [dbo].[商品] WITH CHECK ADD CONSTRAINT [FK商品供应商] FOREIGN KEY([供应商])
REFERENCES [dbo].[商品] ([商品号])
GO
ALTER TABLE [dbo].[商品] CHECK CONSTRAINT [FK商品供应商]
GO
/****** Object: ForeignKey [FK商品录入清单商品录入清单] Script Date: 11/04/2018 13:14:48 ******/
ALTER TABLE [dbo].[商品录入清单] WITH CHECK ADD CONSTRAINT [FK商品录入清单商品录入清单] FOREIGN KEY([库管员工号])
REFERENCES [dbo].[商品录入清单] ([流水号])
GO
ALTER TABLE [dbo].[商品录入清单] CHECK CONSTRAINT [FK商品录入清单商品录入清单]
GO
/****** Object: ForeignKey [FK商品录入清单商品录入清单1] Script Date: 11/04/2018 13:14:48 ******/
ALTER TABLE [dbo].[商品录入清单] WITH CHECK ADD CONSTRAINT [FK商品录入清单商品录入清单1] FOREIGN KEY([商品号])
REFERENCES [dbo].[商品录入清单] ([流水号])
GO
ALTER TABLE [dbo].[商品录入清单] CHECK CONSTRAINT [FK商品录入清单商品录入清单1]
GO
/****** Object: ForeignKey [FK商品销售清单商品销售清单] Script Date: 11/04/2018 13:14:48 ******/
ALTER TABLE [dbo].[商品销售清单] WITH CHECK ADD CONSTRAINT [FK商品销售清单商品销售清单] FOREIGN KEY([收银员工号])
REFERENCES [dbo].[商品销售清单] ([流水号])
GO
ALTER TABLE [dbo].[商品销售清单] CHECK CONSTRAINT [FK商品销售清单商品销售清单]
GO
/****** Object: ForeignKey [FK商品销售清单商品销售清单1] Script Date: 11/04/2018 13:14:48 ******/
ALTER TABLE [dbo].[商品销售清单] WITH CHECK ADD CONSTRAINT [FK商品销售清单商品销售清单1] FOREIGN KEY([商品号])
REFERENCES [dbo].[商品销售清单] ([流水号])
GO
ALTER TABLE [dbo].[商品销售清单] CHECK CONSTRAINT [FK商品销售清单_商品销售清单1]
GO