在智慧社區(qū)商超管理系統(tǒng)中，通常有多種不同角色的人員使用該系統(tǒng)，超市里面有各種各樣不同的商品，商品都有不同的供應(yīng)商。
1.根據(jù)上述語義畫出ER圖
2.將ER模型轉(zhuǎn)換成數(shù)據(jù)表，并指出每個數(shù)據(jù)表的主鍵。
3.根據(jù)建立的數(shù)據(jù)表，在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,
[供應(yīng)商] 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].[供應(yīng)商] 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].[供應(yīng)商](
[代碼] varchar NOT NULL,
[名稱] varchar NOT NULL,
[地點(diǎn)] varchar NOT NULL,
[聯(lián)系人] varchar NOT NULL,
[電話] varchar NOT NULL,
[備注] varchar NULL,
CONSTRAINT [PK_供應(yīng)商] 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_商品供應(yīng)商] Script Date: 11/04/2018 13:14:48 ******/
ALTER TABLE [dbo].[商品] WITH CHECK ADD CONSTRAINT [FK商品供應(yīng)商] FOREIGN KEY([供應(yīng)商])
REFERENCES [dbo].[商品] ([商品號])
GO
ALTER TABLE [dbo].[商品] CHECK CONSTRAINT [FK商品供應(yīng)商]
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
