/*
	SQL Statements to build up Tables for Student Registration System
	
	Use SQLExec.exe to build up the tables

	** Coverted all memo fields to text fields
	** Increased CTitle from 35->60 in TCourse
	
*/

//********** Drop any previous Tables  ********************
drop table TWaved;
drop table TCoRequisite;
drop table TSubstitution;
drop table TPreRequisite;
drop table TSugOffering;
drop table TEnrollment;
drop table TOffering;
drop table TCourse;
drop table TStudent;
drop table TAdministrator;



//*********************************************************
create table TAdministrator(CUserName varchar(20) not null
	,CFirstName varchar(14) not null,CLastName varchar(20) not null
	,CPassword varchar(15) not null,CBirthDate datetime);

alter table TAdministrator add constraint TAdministratorPK primary key(CUserName);


//*************** Student *********************************
create table TStudent(CStudentID char(9) not null,CLastName varchar(20) not null
	,CFirstName varchar(14) not null,CMI char(1)
	,CWorkPhone varchar(10),CHomePhone varchar(10)
	,CStreet varchar(25),CCity varchar(15),CState char(2)
	,CZip char(5),CEMail varchar(40),CSemAdm char(4),CYearAdm char(4)
	,CBlocked char(1),CPassword varchar(15),CDOB datetime
	,CLoggedIn char(1)); 


alter table TStudent add constraint TStudentPK primary key (CStudentID);


//*************** Course *******************************
create table TCourse(CCourseNo char(4) not null,CDept char(5) not null,
	CType char(1),CCredits integer,CTitle varchar(60),
	CDescription text);   


alter table TCourse add constraint TCoursePK primary key (CCourseNo,CDept);


// ****************  Offering ***************************
create table TOffering(CCourseNo char(4) not null,CDept char(5) not null
	,CSection char(2) not null,CSemester char(4) not null
	,CYear char(4) not null,CDay char(4),CStartTime integer
	,CEndTime integer,CComment varchar(250),CCapacity integer
	,CInstructor char(20));


alter table TOffering add constraint TOfferingPK primary key 
	( CCourseNo,CDept,CSection,CSemester,CYear);


alter table TOffering add constraint TOfferingFKTCourse foreign key 
	(CCourseNo,CDept) references TCourse(CCourseNo,CDept);


// *************** Enrollment ***************************
create table TEnrollment(CStudentID char(9) not null,CCourseNo char(4) not null,
	CDept char(5) not null,CSection char(2) not null,CSemester char(4) not null
	,CYear char(4) not null, CLetterGrade char(2),CEnrollDate DateTime);


alter table TEnrollment add constraint TEnrollmentPK primary key 
	(CStudentID,CCourseNo,CDept,CSection,CSemester,CYear);

alter table TEnrollment add constraint TEnrollmentFKTStudent 
	foreign key (CStudentID) references TStudent(CStudentID);

alter table TEnrollment add constraint TEnrollmentFKTOffering 
	foreign key ( CCourseNo,CDept,CSection,CSemester,CYear)
	references TOffering (CCourseNo, CDept, CSection,CSemester,CYear);


// ********************* SugOffering *************************
create table TSugOffering (CStudentID char(9) not null, 
	CCourseNo char(4) not null, CDept char(5) not null,
	CSemester char(4) not null, CYear char(4) not null,
	CComment varchar(250),CSugDate DateTime);


alter table TSugOffering add constraint TSugOfferingPK 
	primary key (CStudentID,CCourseNo,CDept,CSemester,CYear);


alter table TSugOffering add constraint TSugOfferingFKTStudent
	foreign key (CStudentID) references TStudent(CStudentID);


//********************** TPreRequisite ***********************
create table TPreRequisite(CCourseNo char(4) not null,CDept char(5) not null
	,CPreCourseNo char(4) not null,CPreDept char(5) not null);


alter table TPreRequisite add constraint TPreRequistePK primary key 
	(CCourseNo,CDept,CPreCourseNo,CPreDept);

alter table TPreRequisite add constraint TPreRequisteFK1
	foreign key (CCourseNo,CDept) references TCourse(CCourseNo,CDept);


alter table TPreRequisite add constraint TPreRequisteFK2 
	foreign key (CPreCourseNo,CPreDept) references TCourse(CCourseNo,CDept);



//************************ TCoRequisite **************************
create table TCoRequisite(CCourseNo char(4) not null,CDept char(5) not null
	,CCoCourseNo char(4) not null,CCoDept char(5) not null);

alter table TCoRequisite add constraint TCoRequistePK primary key 
	(CCourseNo,CDept,CCoCourseNo,CCoDept);

alter table TCoRequisite add constraint TCoRequisteFK1 foreign key 
	(CCourseNo,CDept) references TCourse(CCourseNo,CDept);

alter table TCoRequisite add constraint TCoRequisiteFK2 foreign key 
	(CCoCourseNo,CCoDept) references TCourse(CCourseNo,CDept);



//***************  TSubstitution **********************************
create table TSubstitution (CStudentID char(9) not null
	,CCoreCourseNo char(4) not null,CCoreDept char(5) not null
	,CSubstCourseNo char(4) not null,CSubstDept char(5) not null
	);


alter table TSubstitution add constraint 
	TSubstitutionPK primary key (CStudentID,CCoreCourseNo,CCoreDept
	,CSubstCourseNo,CSubstDept);

alter table TSubstitution add constraint TSubstitutionFK1 foreign key 
	(CCoreCourseNo,CCoreDept) references TCourse(CCourseNo,CDept);

alter table TSubstitution add constraint TSubstitutionFK2 foreign key 
	(CSubstCourseNo,CSubstDept) references TCourse(CCourseNo,CDept);

alter table TSubstitution add constraint TSubstitutionFK3
	foreign key (CStudentID) references TStudent(CStudentID);


//****************** TWaved *****************************************
create table TWaved
	(
	CStudentID char(9) not null  // StudentID who is getting waved
	,CCourseNo char(4) not null // Course which is waved
	,CDept char(5)  not null
	,CReason text	     // Reason for waving course
	,CAdminUserName varchar(20)  // Administrator you authorized waved course
	,CDate  DateTime            // Date when course was waved
	); 

// There is no foreign key set for CAdminUserName against
// TAdministrator(CUserName). This column is just for signing
// the waved course.

alter table TWaved add constraint
	TWavedPK primary key (CStudentID,CCourseNo,CDept);

alter table TWaved add constraint
	TWaveFK1 foreign key(CStudentID) references TStudent(CStudentID);

alter table TWaved add constraint
	TWaveFK2 foreign key(CCourseNo,CDept) references TCourse(CCourseNo,CDept);