第五章 任务2.4 系统数据库设计

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

image.png

二．ER模型转换成数据表，并指出每个数据表的主键
1，收银员

image.png

2，商品销售清单

image.png
3，商品
image.png
4，库管员
image.png

5，商品录入清单

image.png

6，供应商

image.png

三.每个表格的sql脚本
1.收银员
USE [wy]
GO
/****** Object: Table [dbo].[收银员] Script Date: 11/04/2018 14:55:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[收银员](
[工号] [int] NOT NULL,
[姓名] varchar NULL,
[密码] varchar NULL,
[电话] varchar NULL,
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

2.商品销售清单
/****** Object: Table [dbo].[商品销售清单] Script Date: 11/04/2018 14:55:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[商品销售清单](
[流水号] [int] NOT NULL,
[收银员] varchar NOT NULL,
[商品号] varcharNOT NULL,
[销售时间] varchar NULL,
PRIMARY KEY CLUSTERED
(
[流水号] ASC
[收银员] ASC
[商品号] 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

3.商品
/****** Object: Table [dbo].[商品] Script Date: 11/04/2018 14:55:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[商品](
[商品号] [int] NOT NULL,
[商品] varchar NULL,
[名称] varchar NULL,
[价格] varchar NULL,
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

4.库管员
/****** Object: Table [dbo].[库管员] Script Date: 11/04/2018 14:55:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[库管员](
[工号] [int]NOT NULL,
[姓名] varchar NULL,
[密码] varchar NULL,
[电话] varchar NULL,
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

5.商品录入清单
/****** Object: Table [dbo].[商品录入清单] Script Date: 11/04/2018 14:55:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[商品录入清单](
[流水号] [int] NOT NULL,
[库管员工号] varcharNOT NULL,
[商品号] varchar NOT NULL,
[录入时间] varcharNULL,
PRIMARY KEY CLUSTERED
(
[流水号] ASC
[库管员工号] ASC
[商品号] 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

6.供应商
/****** Object: Table [dbo].[供应商] Script Date: 11/04/2018 14:55:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[供应商](
[代码] [int] NOT NULL,
[名称] varchar NULL,
[地点] varchar NULL,
[联系人] varchar NULL,
[电话] varchar NULL,
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