/* 1.创建EmployeeInfo(雇员信息)表 */
use myshop
if exists(select * from sysobjects where name='EmployeeInfo')
return
create table EmployeeInfo
(
  Eid    int primary key not null,
  EName  nvarchar(50) not null,
  Sex    char(2) check (Sex in('男', '女')) default '女' not null,
  Age    tinyint check (Age > 18) not null,
  -- 唯一值
  Card   char(18) unique not null,
  Phone  nvarchar(11),
  Adress nvarchar(50),
  -- 默认值为系统日期
  ToDate datetime default(getdate()) not null,
  Job    nvarchar(8) not null
)

	/* 2.创建UserLogin(用户登录)表 */
if exists(select * from sysobjects where name='UserLogin')
return
create table UserLogin
(
  Uid int foreign key references EmployeeInfo(Eid) not null,
  UPassword nvarchar(50) not null
)


	/* 2.创建MemberInfo(会员信息)表 */
if exists(select * from sysobjects where name='MemberInfo')
return
create table Memberinfo
(
  Mid int primary key not null,
  MName nvarchar(30) not null,
  Sex char(2)check(Sex in('男','女')) default '女' not null,
  Age smallint check(Age > 18) not null,
  Card char(18) unique not null,
  Phone nvarchar(11),
  Adress nvarchar(50),
  OnDate datetime default(getdate()) not null,
  WMid char(8) unique
)


	/* 3.创建ProductInfo(产品信息)表 */
if exists(select * from sysobjects where name='ProductInfo')
return
create table ProductInfo
(
  Pid nvarchar(20) primary key not null,
  PName nvarchar(30) not null,
  Price real check(Price > 0) not null,
  JFen smallint check(JFen > 0) not null,
  Ptype nvarchar(30) not null
)

         /* 4.创建ProductToInfo（产品入库登记）表 */
if exists(select * from sysobjects where name='ProductToInfo')
return
create table ProductToInfo
(
  ToId int IDENTITY (1, 1) primary key not null,
  Pid nvarchar(20) foreign key references ProductInfo(Pid) not null,
  Num smallint check (Num > 0) not null,
  ToDate datetime default(getdate()) not null,
  ForWho nvarchar(50) not null
)


	/* 5.创建Stcok(库存信息)表 */
if exists(select * from sysobjects where name='Stcok')
return
create table Stcok
(
  Pid nvarchar(20) foreign key references ProductInfo(Pid) not null,
  -- 库存数量需要大于等于0
  Num smallint check (Num >= 0) not null
)
	/* 6.创建SellInfo(产品销售信息)表 */
if exists(select * from sysobjects where name='SellInfo')
return
create table SellInfo
(
  SIid int IDENTITY (1, 1) primary key not null,
  Pid nvarchar(20) foreign key references ProductInfo(Pid) not null,
  Num smallint check (Num > 0) not null,
  OutDate datetime default(getdate()) not null
)
	/* 7.创建temp表 */
if exists(select * from sysobjects where name='temp')
return
create table temp
(
  Pid nvarchar(20) foreign key references ProductInfo(Pid) not null,
  Pname nvarchar(30) not null,
  Price real check (Price > 0) not null,
  Num smallint check (Num > 0) not null
)