作业三

物应1705 付超 2017270396

题目

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

一．根据上述语义画出ER图。

image.png

二．将ER模型转化成数据表。

image.png

三．根据建立的数据表，在SQL Sever 2008中建表。

1.收银员

USE [FUCHAO]
GO
/****** Object: Table [dbo].[收银员] Script Date: 10/30/2018 22:27:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[收银员](
[工号] varchar NOT NULL primary key,
[姓名] varchar NOT NULL,
[密码] varchar NOT NULL,
[电话] varchar NOT NULL
) ON [PRIMARY]
GO

2.商品销售清单

SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[商品销售清单] Script Date: 10/30/2018 22:27:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[商品销售清单](
[流水号] varchar NOT NULL primary key,
[收银员工号] varchar NULL,
[商品号] varchar NOT NULL,
[销售时间] varchar NOT NULL
) ON [PRIMARY]
GO

3.商品录入清单

SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[商品录入清单] Script Date: 10/30/2018 22:27:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[商品录入清单](
[流水号] varchar NOT NULL primary key,
[收银员工号] varchar NOT NULL,
[商品号] varchar NOT NULL,
[录入时间] varchar NOT NULL
) ON [PRIMARY]
GO

4.商品

SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[商品] Script Date: 10/30/2018 22:27:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[商品](
[商品号] varchar NOT NULL primary key,
[商品名称] varchar NOT NULL,
[价格] varchar NOT NULL,
[供应商] varchar NOT NULL
) ON [PRIMARY]
GO

5.库管员

SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[库管员] Script Date: 10/30/2018 22:27:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[库管员](
[工号] varchar NOT NULL primary key,
[姓名] varchar NOT NULL,
[密码] varchar NOT NULL,
[电话] varchar NOT NULL
) ON [PRIMARY]
GO

6.供应商

SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[供应商] Script Date: 10/30/2018 22:27:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[供应商](
[代码] varchar NOT NULL primary ke ,
[名称] varchar NOT NULL,
[地点] varchar NOT NULL,
[联系人] varchar NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO