DB Schema
Each database is handwritten using constraints, foreign keys, function, and stored procedures to produce a robust and secure system
Gemolomagy Lab
The Gemology Lab database is a relational schema created to support a gemology lab that receives and grades precious stones
use master
go
if not exists(select * from sys.databases d where d.name = 'gemologyLab')
begin
create database gemologyLab
end
go
use gemologyLab
go
create table dbo.lab(
iLabId int not null identity primary key,
vchLabName varchar(100)
constraint c_lab_labName_cannot_be_blank check(vchLabName <> '')
constraint u_lab_labName_must_be_unique unique
)
go
create table dbo.customer(
iCustomerId int not null identity primary key,
iLabId int not null
constraint f_lab_customer foreign key references lab(iLabId),
vchCustomerFirstName varchar(25) not null
constraint c_customer_customerFirstName_cannot_be_blank check(vchCustomerFirstName <> ''),
vchCustomerLastName varchar(25) not null
constraint c_customer_customerLastName_cannot_be_blank check(vchCustomerLastName <> ''),
gCustomerCode uniqueidentifier not null default newid()
constraint u_customer_customerCode_must_be_unique unique
)
go
create table dbo.station(
iStationId int not null identity primary key,
vchStation varchar(30) not null
constraint c_station_station_cannot_be_blank check(vchStation <> ''),
iStationSequence int not null
constraint c_station_stationNum_must_be_greater_than_zero check(iStationSequence > 0)
constraint u_iStationSequence_must_be_unique unique,
bInLab bit not null
)
go
create table dbo.employeeType(
iEmployeeTypeId int not null identity primary key,
vchEmployeeType varchar(30) not null
constraint c_employeeType_employeeType_cannot_be_blank check(vchEmployeeType <> '')
constraint u_employeeType_employeeType_must_be_unique unique,
bEmployeeRankValid bit not null default 0,
bCanGiveGrade bit not null default 0
)
go
create table dbo.employee(
iEmployeeId int not null identity primary key,
iEmployeeTypeId int not null
constraint f_employeeType_employee foreign key references employeeType(iEmployeeTypeId),
iLabId int not null
constraint f_lab_employee foreign key references lab(iLabId),
vchEmployeeFirstName varchar(30) not null
constraint c_employeeType_employeeFirstName_cannot_be_blank check(vchEmployeeFirstName <> ''),
vchEmployeeLastName varchar(30) not null
constraint c_employeeType_employeeLastName_cannot_be_blank check(vchEmployeeLastName <> ''),
iEmployeeRank int
constraint c_employee_employeeRank_must_be_between_1_and_4 check(iEmployeeRank between 1 and 4),
gEmployeeCode uniqueidentifier not null default newid()
constraint u_employee_employeeCode_must_be_unique unique
)
go
create table dbo.stoneType(
iStoneTypeId int not null identity primary key,
vchStoneType varchar(50) not null
constraint c_stoneType_stoneType_cannot_be_blank check(vchStoneType <> '')
constraint u_stoneType_stoneType_must_be_unique unique
)
go
create table dbo.shape(
iShapeId int not null identity primary key,
vchShape varchar(50) not null
constraint c_shape_shape_cannot_be_blank check(vchShape <> '')
constraint u_shape_shape_must_be_unique unique
)
go
create table dbo.color(
iColorId int not null identity primary key,
vchColor varchar(5) not null
constraint c_color_color_cannot_be_blank check(vchColor <> '')
constraint u_color_color_must_be_unique unique,
iColorValue int not null constraint u_color_colorvalue_must_be_unique unique
)
go
create table dbo.clarity(
iClarityId int not null identity primary key,
vchClarity varchar(10) not null
constraint c_clarity_clarity_cannot_be_blank check(vchClarity <> '')
constraint u_clarity_clarity_must_be_unique unique,
iClarityValue int not null
constraint u_clarity_clarityvalue_must_be_unique unique
)
go
create table dbo.rapPrice(
iRapPriceId int not null identity primary key,
iColorId int not null
constraint f_color_rapPrice foreign key references color(iColorId),
iClarityId int not null
constraint f_clarity_rapPrice foreign key references clarity(iClarityId),
dcWeightFrom dec(4,2)
constraint f_rapPrice_weight_from_cannot_be_negative check(dcWeightFrom >= 0) ,
dcWeightTo dec(4,2),
iPrice int
constraint f_rapPrice_price_cannot_be_negative check(iPrice >= 0),
constraint f_rapPrice_weight_to_cannot_be_negative check(dcWeightTo >= dcWeightFrom)
)
go
create table dbo.parcel(
iParcelId int not null identity primary key,
iCustomerId int not null
constraint f_customer_parcel foreign key references customer(iCustomerId),
bJewelry bit not null default 0,
gParcelCode uniqueidentifier not null default newid()
constraint u_parcel_parcelCode_must_be_unique unique
)
go
create table dbo.stone(
iStoneId int not null identity primary key,
iParcelId int not null
constraint f_stone_parcel foreign key references parcel(iParcelId),
iStoneTypeId int
constraint f_stone_stonetype foreign key references stoneType(iStoneTypeId),
iShapeId int
constraint f_stone_shape foreign key references shape(iShapeId),
iStoneNum int not null default 1
constraint c_stone_stoneNum_must_be_greater_than_zero check(iStoneNum > 0),
constraint u_stone_parcelId_stoneNum_must_be_unique unique(iParcelId, iStoneNum)
)
go
create table dbo.parcelStation(
iParcelStationId int not null identity primary key,
iParcelId int not null
constraint f_parcel_parcelStation foreign key references parcel(iParcelId),
iStationId int not null
constraint f_station_parcelStation foreign key references station(iStationId),
iEmployeeId int not null
constraint f_employee_parcelStation foreign key references employee(iEmployeeId),
dtInserted datetime not null default getdate()
)
go
create table dbo.cert(
iCertId int not null identity primary key,
iStoneId int not null
constraint f_stone_cert foreign key references stone(iStoneId),
dcStonePrice dec(10,2) null,
bCertificateValid bit not null default 1,
gCertificateCode uniqueidentifier not null default newid()
constraint u_certificate_certificateCode_must_be_unique unique
)
go
create table dbo.gradeWeight(
iGradeWeightId int not null identity primary key,
iCertId int not null
constraint f_certificate_gradeWeight foreign key references cert(iCertId),
iEmployeeId int not null
constraint f_employee_gradeWeight foreign key references employee(iEmployeeId),
dcWeight dec(4,2) not null
constraint c_dcWeight_must_be_greater_than_zero check(dcWeight > 0),
dtInserted datetime not null default getdate(),
bFinalGradeWeight bit not null default 0
)
go
create table dbo.gradeColor(
iGradeColorId int not null identity primary key,
iCertId int not null
constraint f_certificate_gradeColor foreign key references cert(iCertId),
iEmployeeId int not null
constraint f_employee_gradeColor foreign key references employee(iEmployeeId),
iColorId int not null
constraint f_color_gradeColor foreign key references color(iColorId),
dtInserted datetime not null default getdate(),
bFinalGradeColor bit not null default 0
)
go
create table dbo.gradeClarity(
iGradeClarityId int not null identity primary key,
iCertId int not null
constraint f_certificate_gradeClarity foreign key references cert(iCertId),
iEmployeeId int not null
constraint f_employee_gradeClarity foreign key references employee(iEmployeeId),
iClarityId int not null
constraint f_clarity_gradeClarity foreign key references clarity(iClarityId),
dtInserted datetime not null default getdate(),
bFinalGradeClarity bit not null default 0
)
go ;
