DB Schema
Each database is handwritten using constraints, foreign keys, function, and stored procedures to produce a robust and secure system
US Gov
The US Gov Database has all our 45 presidents,50 states, US Departments,US Budget and more
create table dbo.party(
iPartyID int not null identity primary key,
vchPartyName varchar(50) not null constraint u_party_name_must_be_unique unique
)
go
create table dbo.president(
iPresidentId int not null identity (1000,1) primary key,
iNum int not null constraint u_president_num_must_be_unique unique,
vchFirstName varchar(100) not null constraint ck_president_firstname_cannot_be_blank check(vchFirstName <> ''),
vchLastName varchar(100) not null constraint ck_president_lasttname_cannot_be_blank check(vchLastName <> ''),
iYearBorn int not null,
iYearDied int null,
iTermStart int not null,
iTermEnd int,
iPartyId int not null foreign key references party(iPartyId),
iYearsServed as iTermEnd - iTermStart,
iTermStartAge as iTermStart - iYearBorn,
iTermEndAge as iTermEnd - iYearBorn,
iDeathAge as iYearDied - iYearBorn,
constraint ck_president_yearborn_must_be_before_yeardied check(iYearBorn < iYearDied),
constraint ck_president_termstart_must_be_before_termend check(iTermStart <= iTermEnd)
)
create table executiveOrder(
iExecutiveOrderId int not null identity primary key,
iPresidentId int not null constraint f_executiveOrder_president foreign key references president(ipresidentId),
iOrderNumber int not null constraint u_executiveOrder_order_number_must_be_unique unique,
vchOrderName varchar(500) not null constraint ck_executiveOrder_order_name_cannot_be_blank check(vchOrderName <> ''),
iPageNumber int not null constraint ck_executiveOrder_page_number_must_be_greater_than_zero check(iPageNumber > 0),
iYear int not null constraint ck_executiveOrder_year_must_be_greater_than_1776 check(iYear >= 1776),
bUpheldByCourts bit not null,
dtInserted datetime not null default getdate(),
vchOfficialFormat as
'Exec. Order No. ' + convert(varchar, iOrderNumber)
+ ', 3 C.F.R. ' + convert(varchar, iPageNumber) + ' ' + convert(varchar,iYear)
+ '. ' + vchOrderName + '.'
)
go
create table dbo.medal(
iMedalId int not null identity primary key,
vchMedalName varchar(50) not null constraint u_Medal_name_must_be_unique unique
)
go
create table dbo.PresidentMedal(
iPresidentMedalId int not null identity primary key,
iPresidentId int constraint f_president_president_medal foreign key references president(iPresidentId),
iMedalId int constraint f_medal_president_medal foreign key references medal(iMedalId)
)
go ;
