USE [master]
GO
IF EXISTS(select * from sys.databases where name='銷售管理系統')
   DROP DATABASE [銷售管理系統]
GO
CREATE DATABASE [銷售管理系統]
GO
IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = 'Mary')
BEGIN
    CREATE LOGIN [Mary] WITH PASSWORD = N'12345678'
END
GO

USE [銷售管理系統]
GO
IF NOT EXISTS
    (SELECT name
     FROM sys.database_principals
     WHERE name = 'Mary')
BEGIN
    CREATE USER [Mary] FOR LOGIN [Mary] WITH DEFAULT_SCHEMA=[dbo]
    ALTER ROLE [db_owner] ADD MEMBER [Mary]
END
GO
/****** Object:  Table [dbo].[客戶]    Script Date: 2021/7/29 下午 05:00:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[客戶](
	[客戶編號] [char](5) NOT NULL,
	[客戶名稱] [nvarchar](50) NOT NULL,
	[客戶地址] [nvarchar](80) NULL,
	[電話號碼] [varchar](16) NOT NULL,
	[傳真號碼] [varchar](16) NULL,
	[聯絡人姓名] [nvarchar](20) NOT NULL,
	[分機號碼] [varchar](6) NULL,
	[電郵地址] [varchar](30) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[訂單]    Script Date: 2021/7/29 下午 05:00:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[訂單](
	[訂單編號] [char](5) NOT NULL,
	[客戶編號] [char](5) NOT NULL,
	[員工編號] [char](5) NOT NULL,
	[訂單日期] [datetime] NOT NULL,
	[送貨日期] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[訂單明細]    Script Date: 2021/7/29 下午 05:00:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[訂單明細](
	[訂單編號] [char](5) NOT NULL,
	[產品編號] [char](5) NOT NULL,
	[數量] [int] NOT NULL,
	[折扣價] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[員工]    Script Date: 2021/7/29 下午 05:00:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[員工](
	[員工編號] [char](5) NOT NULL,
	[員工姓名] [nvarchar](16) NOT NULL,
	[部門名稱] [nvarchar](16) NOT NULL,
	[員工職稱] [nvarchar](16) NOT NULL,
	[分機號碼] [varchar](6) NULL,
	[電郵地址] [varchar](30) NULL,
	[住家地址] [nvarchar](50) NULL,
	[住家電話] [varchar](16) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[員工加班]    Script Date: 2021/7/29 下午 05:00:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[員工加班](
	[員工編號] [char](5) NOT NULL,
	[加班日期] [datetime] NOT NULL,
	[延長工時] [float] NOT NULL,
	[再延長工時] [float] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[員工薪資]    Script Date: 2021/7/29 下午 05:00:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[員工薪資](
	[員工編號] [char](5) NOT NULL,
	[員工年薪] [int] NOT NULL,
	[年終獎金] [int] NULL,
	[業績獎金] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[產品]    Script Date: 2021/7/29 下午 05:00:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[產品](
	[產品代碼] [char](5) NOT NULL,
	[產品名稱] [nvarchar](50) NOT NULL,
	[產品說明] [nvarchar](100) NULL,
	[庫存量] [int] NOT NULL,
	[安全庫存] [int] NOT NULL,
	[定價] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[產品]  WITH CHECK ADD  CONSTRAINT [CK_產品] CHECK  (([安全庫存]>=(10)))
GO
ALTER TABLE [dbo].[產品] CHECK CONSTRAINT [CK_產品]
GO