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
;