DB SECURITY:
https://www.uvm.edu/ets/mysql/?Page=04_solutions.html

Place your mySQL particulars in a small, separate file. If your mySQL database name is DOGCOW, username is dogcow and password is woofmoo, then 

    <?
    $usr = "dogcow";
    $db = "DOGCOW";
    $pswd = "woofmoo";
    $dbh=mysql_pconnect("webdb.uvm.edu",$usr,$pswd);
    mysql_select_db($db, $dbh);
    ?>

Send this file to the ETS Systems Administration and Architecture at saa@uvm.edu4 , and ask them to name it as yourNetId.inc (so if yourNetId is also dogcow, that would be dogcow.inc). Now, whenever you need to connect to your database in a script, use this code

    include("/usr/local/uvm-inc/yourNetId.inc");


***************************************************
***************************************************

////////////
DB STRUCTURE
////////////


[NOT IN DB: emailAdvisor, fnameAdvisor, lnameAdvisor]

***

tblstudent:

($)pkNetidStudent [KEY]
fnameStudent
lnameStudent
address1Student
address2Student
townStudent
zipStudent
phoneStudent
emailStudent
timestamp


***

tblstatus:

[KEY: fkNetidstudent + ($)pkConfyear]
status
college
program
graddate
rsrcharea
affiliation
rsrchindependent
rsrchother

***

tblproject:

[KEY: pkProjectID + fkNetidStudent]
netidAdvisor
advisorPermission
projtitle
abstract
projformat
projoldnew
schedule


//////////////////////////////////////

//////////////////////////////////////

//////////////////////////////////////


tblStudent:

pkNetidStudent
fnameStudent
lnameStudent
address1Student
address2Student
townStudent
zipStudent
phoneStudent
emailStudent
timestamp


CREATE TABLE 'UVMSRC'.'tblStudent' (
'pkNetidStudent' VARCHAR( 10 ) NOT NULL ,
'fnameStudent' VARCHAR( 30 ) NOT NULL ,
'lnameStudent' VARCHAR( 30 ) NOT NULL ,
'address1Student' VARCHAR( 80 ) NOT NULL ,
'address2Student' VARCHAR( 80 ) NULL ,
'townStudent' VARCHAR( 50 ) NOT NULL ,
'zipStudent' VARCHAR( 10 ) NOT NULL ,
'phoneStudent' VARCHAR( 20 ) NOT NULL ,
'emailStudent' VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( 'pkNetidStudent' )
) ENGINE = InnoDB 


ALTER TABLE 'tblStudent' ADD 'timestamp' TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;


/////////////


tblStatus:

fkNetidstudent + pkConfyear
status
college
program
graddate
rsrcharea
affiliation
rsrchindependent
rsrchother
timestamp


 CREATE TABLE 'UVMSRC'.'tblStatus' (
'fkNetidstudent' VARCHAR( 15 ) NOT NULL ,
'pkConfyear' YEAR( 4 ) NOT NULL ,
'status' VARCHAR( 15 ) NOT NULL ,
'college' VARCHAR( 80 ) NOT NULL ,
'program' VARCHAR( 80 ) NOT NULL ,
'graddate' VARCHAR( 15 ) NOT NULL ,
'rsrcharea' VARCHAR( 80 ) NOT NULL ,
'affiliation' VARCHAR( 400 ) NULL ,
'rsrchindependent' VARCHAR( 20 ) NULL ,
'rsrchother' VARCHAR( 150 ) NULL ,
PRIMARY KEY ( 'fkNetidstudent' , 'pkConfyear' )
) ENGINE = InnoDB 


ALTER TABLE 'tblStatus' ADD FOREIGN KEY ( 'fkNetidstudent' ) REFERENCES 'UVMSRC'.'tblStudent' (
'pkNetidStudent'
) ON DELETE CASCADE ON UPDATE CASCADE ;


ALTER TABLE 'tblStatus' ADD 'timestamp' TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;


/////////////


tblProject:

pkProjectID + fkNetidStudent
confyear
netidAdvisor
advisorPermission
projTitle
projAbstract
projFormat
projOld
schedulePref
session
time
location
presentation
timestamp


 CREATE TABLE 'UVMSRC'.'tblProject' (
'pkProjectID' SMALLINT NOT NULL AUTO_INCREMENT ,
'fkNetidStudent' VARCHAR( 15 ) NOT NULL ,
'netidAdvisor' VARCHAR( 15 ) NOT NULL ,
'advisorPermission' CHAR( 1 ) NOT NULL ,
'projTitle' VARCHAR( 255 ) NOT NULL ,
'projAbstract' VARCHAR( 50 ) NULL ,
'projFormat' VARCHAR( 10 ) NOT NULL ,
'projOld' VARCHAR( 3 ) NOT NULL ,
'schedule' VARCHAR( 10 ) NOT NULL ,
PRIMARY KEY ( 'pkProjectID' , 'fkNetidStudent' )
) ENGINE = InnoDB


ALTER TABLE 'tblProject' ADD FOREIGN KEY ( 'fkNetidStudent' ) REFERENCES 'UVMSRC'.'tblStudent' (
'pkNetidStudent'
) ON DELETE CASCADE ON UPDATE CASCADE ;


ALTER TABLE 'tblProject' ADD 'timestamp' TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;


ALTER TABLE `tblProject` ADD `session` CHAR( 2 ) NULL AFTER `schedule` ,
ADD `time` TIME NULL AFTER `session` ,
ADD `location` VARCHAR( 50 ) NULL AFTER `time` ,
ADD `presentation` VARCHAR( 50 ) NULL AFTER `location` ;


ALTER TABLE `tblProject` ADD `abstractApproved` CHAR( 1 ) NOT NULL DEFAULT 'N' AFTER `schedulePref` ;


ALTER TABLE `tblProject` CHANGE `session` `session` CHAR( 2 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'TK' 


ALTER TABLE `tblProject` CHANGE `time` `time` VARCHAR( 10 ) NOT NULL DEFAULT 'TK' 


ALTER TABLE `tblProject` CHANGE `location` `location` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'TK' 


ALTER TABLE `tblProject` CHANGE `presentation` `presentation` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'TK' 


ALTER TABLE `tblProject` ADD `confyear` YEAR( 4 ) NOT NULL AFTER `fkNetidStudent` ;


ALTER TABLE `tblProject` ADD `projMultiple` CHAR( 1 ) NOT NULL DEFAULT 'N' AFTER `confyear` ;


ALTER TABLE `tblProject` DROP `projOld`  



/////////////