Database Specification

The department has decided to build a database to handle tracking of personnel's professional development and percent effort. The design of the database and its tables is shown below.

Questions

• Where is the database? What type of database is it?

A mySql database is used to store the data on the biostat server.

• Can I access the database?

Not directly; a front-end to the database is provided through PHP (see below for more details). You can access it here.

• My VUNETID & password does not grant me access.

Authentication cannot be provided through Vanderbilt (at least for now). Contact ColeBeck to acquire a password.

Table Design

Personnel Table Field Description Relationship vunetid primary key, assigned by VU ->careerDev, professionalDev, %effort num employee ID, assigned by VU none firstname first name none lastname last name none middle middle initial/name none dob date of birth none social social security number none addr1 home address, line 1 none addr2 home address, line 2 none city city none state state none zip zip code none homephone home phone number none cellphone cell phone number none officephone office phone number none fax office fax number none email email address none rank degrees & certifications none department Vanderbilt department none location office location none note additional notes none modified date of last modification none   Career Development Table Field Description Relationship id primary key, autonumber none vunetid VU's vunetid <-personnel pos position ID <-position begin start date of position held none end end date of position held none

Professional Development Table Field Description Relationship number primary key, determined by form none vunetid VU's vunetid <-personnel date date of purchase none vendor vendor none payable payable to whom none description description of purchase none formtype form ID <-form amount amount of purchase none account account number none center center number none type category ID <-category   Form List Table Field Description Relationship id primary key, autonumber ->professionalDev formtype type of form, determined by form none   Category List Table Field Description Relationship id primary key, autonumber ->professionalDev category type of purchase none   Position List Table Field Description Relationship id primary key, autonumber ->career position full name of position none allocation yearly allocation to professional development none

Project Table Field Description Relationship grantnum primary key, determined by grant      ->%effort, event       head principal investigator none department department ID <-department contact contact's name none phone contact's phone number none title project title none agency funding agency ID <-agency center center number none shortname shorter, memorable project name none direct direct costs for life over project none indirect indirect costs over life of project none start start date of project none end end date of project none   Department List Table Field Description Relationship id primary key, autonumber ->project dept department name none   Funding Agency List Table Field Description Relationship agencyID primary key, autonumber ->project fundagency funding agency name none

Event Table Field Description Relationship id primary key, autonumber                         none grantnum project number <-project status status of grant none priority priority score none percentile percentile score none date date of event none   Percent Effort Table Field Description Relationship id primary key, autonumber none grantnum project number <-project vunetid VU's vunetid <-personnel percent personnel's percent effort none start start date none end end date none role role ID <-role   Project Role List Table Field Description Relationship id primary key, autonumber ->%effort role project role none

Validation Table Field Description Relationship id primary key, autonumber                         none field field to validate none                         tbl table to validate none chartype character type none min minimum character length none max maximum character length none valmin minimum value none valmax maximum value none required required or not none

User Name Table Field Description Relationship vunetid primary key, user name ->session pass password none   Session List Table Field Description Relationship id primary key, autonumber                         none vunetid      username <-username created time stamp of session creation none modified time stamp of session modification none

PHP Design

Initially, the PHP and HMTL were written as one. To increase maintainability the code has been separated into templates and classes. A normal user can only access information about themself and information about projects. Administrative users have the ability to add/edit/delete all information about personnel, purchases, projects, drop-down choices, and validation. The following shows the tables associated with each page.

• Personnel: Personnel, Percent Effort, Project, Project Role, Career Development, Position List, Professional Development, Form List, & Category List
• Projects: Project, Percent Effort, Personnel, Project Role, Event, Department List, Funding Agency List
• Purchases: Professional Development, Form List, Category List, & Personnel
• Drop-down Choices: Funding Agency List, Department List, & Project Role
• Validation: Validation