/* Assignment 4
   Philip Bock */

use A4;

-- Delete old tables
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Address')
	drop table Address;

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'EmergencyContact')
	drop table EmergencyContact;

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ParentGuardian')
	drop table ParentGuardian;

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Student')
	drop table Student;

-- Create tables
create table Student (
	StudentID	int not null constraint PK_StudentID primary key,
	Password	varchar(20) not null,
	DateStamp	smalldatetime not null,
	IPAddress	varchar(15) null,
	Surname		varchar(50) not null,
	MiddleName	varchar(50) null,
	FirstName	varchar(50) not null,
	Gender		char not null
)

create table Address (
	StudentID	int not null constraint FK_Address_StudentID references Student (StudentID),
	AdrType		varchar(20) not null,
	ApartmentOrBoxNum varchar(10) null,
	StreetAddress	varchar(50) not null,
	City		varchar(20) not null,
	ProvinceOrState	varchar(20) not null,
	Country		varchar(20) not null,
	PostalCode	varchar(20) not null,
	constraint PK_StudentID_AdrType primary key (StudentID, AdrType)
)

create table EmergencyContact (
	StudentID	int not null constraint FK_EmergencyContact_StudentID references Student (StudentID),
	ContactPriority	tinyint not null,
	LastName	varchar(50) not null,
	FirstName	varchar(50) not null,
	HomePh		varchar(20) not null,
	WorkPh		varchar(20) null,
	MobilePh	varchar(20) null,
	constraint PK_StudentID_ContactPriority primary key (StudentID, ContactPriority)
)

create table ParentGuardian (
	StudentID	int not null constraint FK_ParentGuardian_StudentID references Student (StudentID),
	GuardianType	varchar(50) not null,
	LName		varchar(50) not null,
	FName		varchar(50) not null,
	HomePh		varchar(20) not null,
	WorkPh		varchar(20) null,
	MobilePh	varchar(20) null,
	constraint PK_StudentID_GuardianType primary key (StudentID, GuardianType)
)