1.1_创建studydb1数据库中的表.sql

这是我在学习《SQL Server数据库系统结构基础》时所用到的一个脚本文件

/* 1.1_创建StudyDB1数据库中的表.sql */

USE StudyDB1
go

raiserror('创建表 RecruitmentUsers....',0,1)
create table Recruitmentusers
(
	cUserName char(10),
	cPassword char(10)
)
raiserror('创建表  Department....',0,1)
create table Department
(
	cDepartmentCode	char(4) constraint dt_pk primary key,
	vDepartmentName	varchar(25) ,
	vDepartmentHead varchar(25),
	vLocation	varchar(20)
)
raiserror('创建表  Position....',0,1)
create table Position
(
	cPositionCode	char(4) constraint ptv_pk primary key,
	vDescription	varchar(35),
	iBudgetedStrength	int,
	siYear		smallint,
	iCurrentStrength int
)
go

raiserror('创建表  Country....',0,1)
create table Country
(
	cCountryCode	char(3) constraint c_pk primary key,
	cCountry		char(35) not null,
)
go

raiserror('创建表  Newspaper....',0,1)
create table Newspaper
(
	cNewspaperCode	char(4) constraint np_pk primary key,
	cNewspaperName	char(20) not null,
	vRegion 	varchar(20),
	vTypeOfNewspaper varchar(20),
	vContactPerson 	varchar(35),
	vHOAddress	varchar(35),
	cCity		char(20),
	cState		char(20),
	cZip		char(10) constraint nzp_ck check(cZip like('[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')),
	cCountryCode	char(3) references Country(cCountryCode),
	cFax		char(15)constraint npf_ck check(cFax like('([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')),
	cPhone 		char(15) constraint npp_ck check(cPhone like('([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')),
)
go 

raiserror('创建表  NewsAd....',0,1)
create table NewsAd
(
	cNewsAdNo	char(4) constraint nat_pk primary key,
	cNewspaperCode	char(4) references Newspaper(cNewspaperCode),
	dAdStartDate	datetime,
	dDeadline	datetime
)
go

raiserror('创建表  JobFair....',0,1)
create table JobFair
(
	cJobFairCode	char(4) constraint jft_pk primary key ,
	vLocation	varchar(35),
	vJobFairCompany	varchar(40),
	mFee		money,
	dFairDate	datetime
)
go

raiserror('创建表  ContractRecruiter....',0,1)
create table ContractRecruiter
(
	cContractRecruiterCode	char(4) constraint crtp_pk primary key,
	cName		char(35),
	vAddress	varchar(35),
	cCity		char(20),
	cState		char(15),
	cZip		char(10) constraint crzp_ck check(cZip like('[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')) ,

	cFax		char(15)constraint crf_ck check(cFax like('([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')),
	cPhone 		char(15) constraint crp_ck check(cPhone like('([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')),
	siPercentageCharge	smallint,
	mTotalPaid	money
)
go
raiserror('创建表  RecruitmentAgencies....',0,1)

create table RecruitmentAgencies
(
	cAgencyCode	char(4) constraint rat_pk primary key,
	cName		char(20),
	vAddress	varchar(35),
	cCity		char(15),
	cState 		char(15),
	cZip		char(10),
	cPhone 		char(15) constraint rap_ck check(cPhone like('([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')),
	cFax		char(15)constraint raf_ck check(cFax like('([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')),
	siPercentageCharge	smallint,
	mTotalPaid	money,
)
go 

raiserror('创建表  College....',0,1)
create table College
(
	cCollegeCode	char(4) constraint ct_pk primary key,
	cCollegeName	char(30) not null,
	vCollegeAddress varchar(35),
	cCity 		char(20),
	cState		char(20),
	cZip		char(10) constraint czp_ck check(cZip like('[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')),
	cPhone		char(15) constraint cph_ck check(cPhone like('([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'))
)

raiserror('创建表  CampusRecruitment....',0,1)
create table CampusRecruitment
(
	cCampusRecruitmentCode	char(4) constraint cr_pk primary key,
	cCollegeCode	char(4) references College(cCollegeCode),
	dRecruitmentStartDate	datetime,
	dRecruitmentEndDate 	datetime
)
go

raiserror('创建表  InternalJobPosting....',0,1)
create table InternalJobPosting
(
	cInternalJobPostingCode	char(4) constraint ijp_pk primary key,
	cPositionCode	char(4), /* references,  Position(cPositionCode), */
	siNoOfVacancies	smallint not null,
	vRegion		varchar(20),
	dNoticeReleaseDate 	datetime not null,
	dDeadline	datetime
)
go

raiserror('创建表  InternalCandidate....',0,1)
create table InternalCandidate
(
	cCandidateCode 	char(6) ,
	cEmployeeCode	char(6), 
	cInternalJobPostingCode	char(4) references InternalJobPosting(cInternalJobPostingCode),
	cPositionCodeAppliedFor	char(4) references Position(cPositionCode) ,
	dDateOfApplication	datetime,
	dTestDate	datetime,
	siTestScore	smallint,
	dInterviewDate	datetime,
	cInterviewer	char(25),
	vInterviewComments	varchar(256),
	cRating		char,
	cStatus		char,
	constraint ict_pk primary key(cCandidateCode,cEmployeeCode,cInternalJobPostingCode)
)
go

raiserror('创建表  Employees....',0,1)
create table Employee
(
	cEmployeeCode	char(6) constraint etv_pk primary key,
	vFirstName	varchar(20),
	vLastName	varchar(20),
	cCandidateCode	char(6),
	vAddress	varchar(35),
	cCity		char(20),
	cState		char(20),
	cZip		char(10) constraint ezp_ck check(cZip like('[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')),
	cCountryCode	char(3) references Country(cCountryCode),
	cPhone		char(15) constraint eph_ck check(cPhone like('([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')),
	vQualification	varchar(20),
	dBirthDate	datetime,
	cSex		char,
	cCurrentPosition	char(20),
	cDesignation	char(20),
	cEmailId	char(20),
	cDepartmentCode	char(4) references Department(cDepartmentCode),
	cRegion		char(20),
	imPhoto		image,
	dJoiningDate	datetime,
	dResignationDate	datetime,
	cSocialSecurityNo	char(15) unique,
	cSupervisorCode char(6)

)
go

raiserror('创建表  MonthlySalary....',0,1)
create table MonthlySalary
(
	cEmployeeCode	char(6)references Employee(cEmployeeCode),
	mMonthlySalary	money,
	dPayDate	datetime,
	mReferralBonus	money,
	constraint mst_pk primary key(cEmployeeCode,dPayDate)
)
go
raiserror('创建表  AnnualSalary....',0,1)
create table AnnualSalary