if not exists (select * from sys.databases d where d.name = 'GemologyLab')
begin
create database GemologyLab
end
go

use GemologyLab
go

if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'ClarityGrade')
begin
drop table ClarityGrade
end
go


if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'ColorGrade')
begin
drop table ColorGrade
end
go


if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'WeightGrade')
begin
drop table WeightGrade
end
go


if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'ParcelStation')
begin
drop table ParcelStation
end
go


if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'Station')
begin
drop table Station
end
go

if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'Stone')
begin
drop table Stone
end
go


if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'Parcel')
begin
drop table Parcel
end
go


if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'Customer')
begin
drop table Customer
end
go


if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'Price')
begin
drop table Price
end
go

if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'StoneClarity')
begin
drop table StoneClarity
end
go


if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'StoneColor')
begin
drop table StoneColor
end
go


if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'StoneShape')
begin
drop table StoneShape
end
go


if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'StoneType')
begin
drop table StoneType
end
go


if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'Employee')
begin
drop table Employee
end
go


if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'Position')
begin
drop table Position
end
go


if exists( select * from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = 'Lab')
begin
drop table Lab
end
go


create table dbo.Lab(
iLabId int not null identity primary key,
vchLabName varchar(50) not null
constraint ck_Lab_Lab_Name_cant_be_blank check(vchLabName <>''),
chLabRegion char(2) not null
constraint ck_Lab_Lab_Region_cant_be_blank_and_Must_be_2_character check(chLabRegion <>'' And Len(chLabRegion)>=2),
constraint u_Lab_Lab_Name_and_Lab_Region_must_be_unique unique(vchLabName,chLabRegion)
)
go

create table dbo.Position(
iPositionId int not null identity primary key,
vchPositionName varchar(50) not null
constraint ck_Position_Position_Name_cant_be_blank check(vchPositionName <> '')
constraint u_Position_Position_Name_must_be_unique unique(vchPositionName),
bRankRequired bit not null default 0,
iMinRank int null,
iMaxRank int null,
constraint ck_Position_MaxRank_is_greater_than_Minrank check(iMaxRank > iMinRank),
constraint ck_Position_MaxRank_MinRank_are_ null_if_bRankRequired_is_0
check((bRankRequired = 0 And iMinRank is null and iMaxRank is Null) or (bRankRequired=1 And iMinRank is not null and IMaxRank is not null))
)
go

create table dbo.Employee(
iEmployeeId int not null identity primary key,
iLabId int not null
constraint fk_Lab_Employee foreign key references lab(iLabId),
iPositionId int not null
constraint fk_Position_Employee foreign key references position(iPositionId),
gEmployeeBadgeNumber uniqueidentifier default newid(),
vchFirstName varchar(30) not null
constraint ck_Employee_First_Name_cant_be_blank check(vchFirstName <> ''),
vchLastName varchar(30) not null
constraint ck_Employee_Last_Name_cant_be_blank check(vchLastName <> ''),
iRank int null
)
go

create table dbo.StoneType(
iStoneTypeId int not null identity primary key,
vchStoneTypeDesc varchar(15) not null
constraint ck_StoneType_Stone_Type_Desc_cant_be_blank check(vchStoneTypeDesc <> '')
constraint u_StoneType_Stone_Type_Desc_must_be_unique unique(vchStoneTypeDesc)
)
go

create table dbo.StoneShape(
iStoneShapeId int not null identity primary key,
vchStoneShapeDesc varchar(16) not null
constraint ck_StoneShape_Stone_Shape_Desc_cant_be_blank check(vchStoneShapeDesc <> '')
constraint u_StoneShape_Stone_Shape_Desc_must_be_unique unique(vchStoneShapeDesc)
)
go

create table dbo.StoneColor(
iStoneColorId int not null identity primary key,
vchStoneColorDesc varchar(5) not null
constraint ck_StoneColor_Stone_Color_Desc_cant_be_blank check(vchStoneColorDesc <> '')
constraint u_StoneColor_Stone_Color_Desc_must_be_unique unique(vchStoneColorDesc)
)
go

create table dbo.StoneClarity(
iStoneClarityId int not null identity primary key,
vchStoneClarityDesc varchar(5) not null
constraint ck_StoneClarity_Stone_Clarity_Desc_cant_be_blank check(vchStoneClarityDesc <> '')
constraint u_StoneClarity_Stone_Clarity_Desc_must_be_unique unique(vchStoneClarityDesc),
iStoneClarityRank int not null
constraint ck_StoneClarity_StoneClarityRank_is_a_positive_number check(iStoneClarityRank >= 0)
)
go

create table dbo.Price(
iPriceId int not null identity primary key,
iStoneClarityId int not null
constraint fk_StoneClarity_Price foreign key references StoneClarity(iStoneClarityId),
iStoneColorId int not null
constraint fk_StoneColor_Price foreign key references StoneColor(iStoneColorId),
dMinWeight decimal(5,2) not null,
dMaxWeight decimal(5,2) not null,
mPrice money not null,
constraint u_Price_StoneClarity_StoneColor_MinWeight_MaxWeight_Price_must_be_unique unique(iStoneClarityId,iStoneColorId,dMInWeight)
)
go

create table dbo.Customer(
iCustomerId int not null identity primary key,
vchCustomerFirstName varchar(30) not null
constraint ck_Customer_Customer_First_Name_cant_be_blank check (vchCustomerFirstName <> ''),
vchCustomerLastName varchar(30) not null
constraint ck_Customer_Customer_Last_Name_cant_be_blank check (vchCustomerLastName <> ''),
chCustomerPhoneNum char(10) not null
constraint ck_Customer_Phone_Number_Must_be_10_characters check(len(chCustomerPhoneNum)>=10),
constraint u_Customer_Customer_First_Name_Customer_Last_Name_Customer_Phone_Num_must_be_unique
unique(vchCustomerFirstName,vchCustomerLastName,chCustomerPhoneNum)
)
go

create table dbo.Parcel(
iParcelId int not null identity primary key,
iCustomerId int not null
constraint fk_Customer_Parcel foreign key references Customer(iCustomerId),
gParcelCode uniqueidentifier not null default newid(),
bJewerly bit not null default 0,
bPickedup bit not null default 0
)
go

create table dbo.Stone(
iStoneId int not null identity primary key,
iParcelId int not null
constraint fk_Parcel_Stone foreign key references parcel(IparcelId),
iStoneNum int not null
constraint ck_stone_StoneNum_is_greater_than_or_equal_to_1 check(iStoneNum >= 1),
iStoneShapeId int not null
constraint fk_StoneShape_Stone foreign key references StoneShape(iStoneShapeId),
iStoneTypeId int not null
constraint fk_StoneType_Stone foreign key references StoneType(iStoneTypeId),
constraint u_stone_Parcelid_and_StoneNum_must_be_unique unique(iParcelId,iStoneNum)
)
go

create table dbo.Station(
iStationId int not null identity primary key,
vchStationDesc varchar(50) not null
constraint ck_Station_Station_Desc_cant_be_blank check(vchStationDesc <> '')
constraint u_Station_Station_Desc_Must_be_unique unique(vchStationDesc),
iSequence int not null
constraint ck_Station_Sequence_must_be_greater_than_or_equal_to_1 check(iSequence >=1),
constraint u_Station_sequence_must_be_unique unique(iSequence)
)
go

create table dbo.ParcelStation(
iParcelStationId int not null identity primary key,
iEmployeeId int not null
constraint fk_Employee_ParcelStation foreign key references Employee(iEmployeeId),
iParcelId int not null
constraint fk_Parcel_ParcelStation foreign key references Parcel(iParcelId),
iStationId int not null
constraint fk_Station_ParcelStation foreign key references Station(iStationId),
dtInsertedParcelSation datetime not null default getdate(),
constraint u_parcelStation_parcelId_and_stationId_must_be_unique unique(iParcelId,iStationId)

)
go

create table dbo.WeightGrade(
iWeightGradeId int not null identity primary key,
iEmployeeId int not null
constraint fk_Employee_WeightGrade foreign key references Employee(iEmployeeId),
iStoneId int not null
constraint fk_Stone_WeightGrade foreign Key references Stone(iStoneid),
dcStoneWeight decimal (4,2)
constraint ck_WeightGrade_Stone_Weight_must_be_positive_number_less_than_or_equal_to_10
check(dcStoneWeight > 0 and dcStoneWeight <=10),
dtInsertedWeight datetime default getdate(),
)

create table dbo.ColorGrade(
iColorGradeId int not null identity primary key,
iEmployeeId int not null
constraint fk_Employee_ColorGrade foreign key references Employee(iEmployeeId),
iStoneId int not null
constraint fk_Stone_ColorGrade foreign Key references Stone(iStoneid),
iStoneColorId int not null
constraint fk_StoneColor_ColorGrade foreign Key references StoneColor(iStoneColorid),
dtInsertedColor datetime default getdate(),
)

create table dbo.ClarityGrade(
iClarityGradeId int not null identity primary key,
iEmployeeId int not null
constraint fk_Employee_ClarityGrade foreign key references Employee(iEmployeeId),
iStoneId int not null
constraint fk_Stone_ClarityGrade foreign Key references Stone(iStoneid),
iStoneClarityId int not null
constraint fk_StoneClarity_ClarityGrade foreign Key references StoneClarity(iStoneClarityid),
dtInsertedClarity datetime default getdate()


)
go;