if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__titleauth__au_id__0519C6AF]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[titleauthor] DROP CONSTRAINT FK__titleauth__au_id__0519C6AF
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__employee__job_id__1BFD2C07]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[employee] DROP CONSTRAINT FK__employee__job_id__1BFD2C07
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[employee_insupd]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[employee_insupd]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[authors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[authors]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[discounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[discounts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[employee]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jobs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[jobs]
GO

CREATE TABLE [dbo].[authors] (
	[au_id] [id] NOT NULL ,
	[au_lname] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[au_fname] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[phone] [char] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[address] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[city] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[state] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[zip] [char] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[contract] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[discounts] (
	[discounttype] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[stor_id] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[lowqty] [smallint] NULL ,
	[highqty] [smallint] NULL ,
	[discount] [decimal](4, 2) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[employee] (
	[emp_id] [empid] NOT NULL ,
	[fname] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[minit] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
	[lname] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[job_id] [smallint] NOT NULL ,
	[job_lvl] [tinyint] NULL ,
	[pub_id] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[hire_date] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[jobs] (
	[job_id] [smallint] IDENTITY (1, 1) NOT NULL ,
	[job_desc] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[min_lvl] [tinyint] NOT NULL ,
	[max_lvl] [tinyint] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[authors] WITH NOCHECK ADD 
	CONSTRAINT [UPKCL_auidind] PRIMARY KEY  CLUSTERED 
	(
		[au_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[jobs] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[job_id]
	)  ON [PRIMARY] 
GO

 CREATE  CLUSTERED  INDEX [employee_ind] ON [dbo].[employee]([lname], [fname], [minit]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[authors] ADD 
	CONSTRAINT [DF__authors__phone__78B3EFCA] DEFAULT ('UNKNOWN') FOR [phone],
	 CHECK ([au_id] like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'),
	 CHECK ([zip] like '[0-9][0-9][0-9][0-9][0-9]')
GO

 CREATE  INDEX [aunmind] ON [dbo].[authors]([au_lname], [au_fname]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[employee] ADD 
	CONSTRAINT [DF__employee__job_id__1B0907CE] DEFAULT (1) FOR [job_id],
	CONSTRAINT [DF__employee__job_lv__1CF15040] DEFAULT (10) FOR [job_lvl],
	CONSTRAINT [DF__employee__pub_id__1DE57479] DEFAULT ('9952') FOR [pub_id],
	CONSTRAINT [DF__employee__hire_d__1FCDBCEB] DEFAULT (getdate()) FOR [hire_date],
	CONSTRAINT [PK_emp_id] PRIMARY KEY  NONCLUSTERED 
	(
		[emp_id]
	)  ON [PRIMARY] ,
	CONSTRAINT [CK_emp_id] CHECK ([emp_id] like '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or [emp_id] like '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')
GO

ALTER TABLE [dbo].[jobs] ADD 
	CONSTRAINT [DF__jobs__job_desc__1273C1CD] DEFAULT ('New Position - title not formalized yet') FOR [job_desc],
	 CHECK ([max_lvl] <= 250),
	 CHECK ([min_lvl] >= 10)
GO

ALTER TABLE [dbo].[discounts] ADD 
	 FOREIGN KEY 
	(
		[stor_id]
	) REFERENCES [dbo].[stores] (
		[stor_id]
	)
GO

ALTER TABLE [dbo].[employee] ADD 
	 FOREIGN KEY 
	(
		[job_id]
	) REFERENCES [dbo].[jobs] (
		[job_id]
	),
	 FOREIGN KEY 
	(
		[pub_id]
	) REFERENCES [dbo].[publishers] (
		[pub_id]
	)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE TRIGGER employee_insupd
ON employee
FOR insert, UPDATE
AS
--Get the range of level for this job type from the jobs table.
declare @min_lvl tinyint,
   @max_lvl tinyint,
   @emp_lvl tinyint,
   @job_id smallint
select @min_lvl = min_lvl,
   @max_lvl = max_lvl,
   @emp_lvl = i.job_lvl,
   @job_id = i.job_id
from employee e, jobs j, inserted i
where e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
begin
   raiserror ('Job id 1 expects the default level of 10.',16,1)
   ROLLBACK TRANSACTION
end
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
begin
   raiserror ('The level for job_id:%d should be between %d and %d.',
      16, 1, @job_id, @min_lvl, @max_lvl)
   ROLLBACK TRANSACTION
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

