Group members:

·         Jose (David) Rodriguez

david.rodriguez@vr.fldoe.org

·         Jeremy Dieringer

germey04@gmail.com

·         Ricardo A Altman

classiciron@hotmail.com

 

Title of Group Project

• MISRequest.com  / Group 17

 

Description of Group Project

This is a combine Group Project collaboration for both CET 4429 and CET 4584. This application will be a client/server web system that will allow the end-users to utilize a centralized location to submit and/or initiate a trouble call work order request. Support Staff will be able to access the database backend to retrieve and input information to process and close out the work order request forms. Management will also be able to gather data to provide the necessary information to complete the necessary monthly reports and evaluations.

 

Duties of Group Project Members

 

•  Data Dictionary: 

  • Primary - David Rodriguez
  • Secondary – Jeremy Dieringer

Table Name	Table Description	Column Order	Column Name	Column Type	Column Description	Constraint Name	Primary_Key_Column	Primary_Key_Table	Data Life Cycle	Restricted Access
userrequests	This table stores information about different person i.e customers, employees and parties like companies, vendors, haulers.	1	TicketID	int(11)	Unique identifier for userrequests - set as a primary key for the table and serves as a Ticket Id for follow-up.				Data is auto generated and will have a life cycle of 2 years.	Supporting Staff - Full Access Users - Read Access
userrequests		2	UserID	varchar(255)	User's personal company ID.	NULL			Data is generated by the requesting user and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - Read & Write Access
userrequests		3	FirstName	varchar(255)	First name of the user submitting request.	NULL			Data is generated by the requesting user and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - Read & Write Access
userrequests		4	LastName	varchar(255)	Last name of the user submitting request.	NULL			Data is generated by the requesting user and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - Read & Write Access
userrequests		5	Unit	varchar(255)	Unit name of the office location.	FK_Unit_UnitID	UnitID	UnitOffice	Data is generated by the supporting staff and requesting user and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - Read & Write Access
userrequests		6	ContactNumber	int(11)	Contact # of the user submitting request.	NULL			Data is generated by the requesting user and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - Read & Write Access
userrequests		7	Email	varchar(255)	Email of the user submitting request.				Data is generated by the requesting user and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - Read & Write Access
userrequests		8	RequestID	int(11)	Specifies the request type ID assigned to the requesttype table.	FK_RequestID_RequestID	RequestID	RequestTYpe	Data is generated by the supporting staff and requesting user and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - Read & Write Access
userrequests		9	Severity	varchar(255)	Specifies the degree of emergency with the request.	NULL			Data is generated by the supporting staff and requesting user and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - Read & Write Access
userrequests		10	Status	varchar(255)	Specifies the current status of the request.	NULL			Data is generated by the supporting staff and requesting user and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - Read & Write Access
userrequests		11	AssignedTo	int(11)	Specifies the staff ID assigned to the misstaff table.	Fk_AssignedTo_StaffID	StaffID	MISStaff	Data is generated by the supporting staff and and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - None
userrequests		12	Description	varchar(255)	Description of the user request problem.	NULL			Data is generated by the requesting user and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - Read & Write Access
userrequests		13	Resolution	varchar(255)	Resolution of the user request.	NULL			Data is generated by the supporting staff and and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - None
userrequests		14	EstimatedTime	datetime	Estimated time to complete and resolve the user request.	NULL			Data is generated by the supporting staff and and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - Read & Write Access
userrequests		15	CallStarted	timestamp	Date/time user submit request.	NULL			Data is generated by the system and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - Read Access
userrequests		16	CallEnded	datetime	Date/time supporting staff complete request.	NULL			Data is generated by the supporting staff and and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - Read Access
misstaff	This table list the supporting staff that will be providing support for the office users.	1	StaffID	int(11)	Unique identifier for staff supporting members - set as a primary key for the table.				Data is auto generated and will have a life cycle of 2 years.	Supporting Staff - Full Access Users - None
misstaff		2	StaffName	varchar(255)	Specifies the full name of the supporting staff.	NULL			Data is generated by the supporting staff and and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - None
requestype	This table list the types of request that users are able to submit to the supporting staff.	1	RequestID	int(11)	Unique identifier for the types of requests that users are able to submit - set as a primary key for the table.				Data is generated by the supporting staff and and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - None
requestype		2	RequestType	varchar(255)	Specifies the types of requests that are available.	Null			Data is generated by the supporting staff and and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - None
requestype		3	RequestDescription	longtext	Description of the request type to further clarify in detail as to what the request type entails.	Null			Data is generated by the supporting staff and and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - None
unitoffice	This table identifies the unit ID by providing the full address, phone #, and organizational code.	1	UnitID	varchar(255)	This is a unit ID name and it is unique and set as the primary key for the table.				Data is auto generated and will have a life cycle of 2 years.	Supporting Staff - Full Access Users - None
unitoffice		2	UnitAddress	varchar(255)	Specifies the unit's address.	NULL			Data is generated by the supporting staff and and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - None
unitoffice		3	UnitPhone	int(11)	Specifies the unit's office phone number.	NULL			Data is generated by the supporting staff and and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - None
unitoffice		4	OrgCode	varchar(255)	This is the unit organizational code and also identifies the unit address.	NULL			Data is generated by the supporting staff and and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - None
users		1	ID	int(4)	Specifies the ID assigned to the misstaff table.	FK_ID_StaffID	ID	MISStaff	Data is auto generated and will have a life cycle of 2 years.	Supporting Staff - Full Access Users - None
users		2	UserName	varchar(32)	This will store the user names that will be supporting the MISRequest application.				Data is generated by the supporting staff and and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - None
users		3	Password	varchar(32)	This will store the password and athenticate the users that will be supporting the application.				Data is generated by the supporting staff and and it has a life cycle of 2 years.	Supporting Staff - Full Access Users - None

 

 

• Database Schema Diagrams:
  • Primary - David Rodriguez
  • Secondary – Ricardo Altman

         

 

• Database Schema Scripts:
  • Primary - David Rodriguez
  • Secondary – Ricardo Altman

              

-- phpMyAdmin SQL Dump

-- version 3.1.1

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Apr 23, 2009 at 09:09 PM

-- Server version: 5.1.30

-- PHP Version: 5.2.8

 

 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--

-- Database: `misrequest`

--

-- --------------------------------------------------------

--

-- Table structure for table `misstaff`

--

CREATE TABLE IF NOT EXISTS `misstaff` (

  `StaffID` int(11) NOT NULL AUTO_INCREMENT,

  `StaffName` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`StaffID`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--

-- Dumping data for table `misstaff`

--

-- --------------------------------------------------------

--

-- Table structure for table `requesttype`

--

CREATE TABLE IF NOT EXISTS `requesttype` (

  `RequestID` int(11) NOT NULL AUTO_INCREMENT,

  `RequestType` varchar(255) DEFAULT NULL,

  `RequestDescription` longtext,

  PRIMARY KEY (`RequestID`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--

-- Dumping data for table `requesttype`

--

-- --------------------------------------------------------

--

-- Table structure for table `unitoffice`

--

CREATE TABLE IF NOT EXISTS `unitoffice` (

  `UnitID` varchar(255) NOT NULL,

  `UnitAddress` varchar(255) DEFAULT NULL,

  `UnitPhone` int(11) DEFAULT NULL,

  `OrgCode` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`UnitID`),

  KEY `Org Code` (`OrgCode`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

-- Dumping data for table `unitoffice`

--

-- --------------------------------------------------------

--

-- Table structure for table `userrequests`

--

CREATE TABLE IF NOT EXISTS `userrequests` (

  `TicketID` int(11) NOT NULL AUTO_INCREMENT,

  `UserID` varchar(255) DEFAULT NULL,

  `FirstName` varchar(255) DEFAULT NULL,

  `LastName` varchar(255) DEFAULT NULL,

  `Unit` varchar(255) DEFAULT NULL,

  `ContactNumber` int(11) DEFAULT NULL,

  `Email` varchar(255) DEFAULT NULL,

  `RequestID` int(11) DEFAULT NULL,

  `Severity` varchar(255) DEFAULT NULL,

  `Status` varchar(255) DEFAULT 'Pending',

  `AssignedTo` int(11) DEFAULT NULL,

  `Description` varchar(255) DEFAULT NULL,

  `Resolution` varchar(255) DEFAULT NULL,

  `EstimatedTime` datetime DEFAULT NULL,

  `CallStarted` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

  `CallEnded` datetime DEFAULT NULL,

  PRIMARY KEY (`TicketID`),

  KEY `CallOutcomeCode` (`FirstName`),

  KEY `Category ID` (`RequestID`),

  KEY `CustomerID` (`UserID`),

  KEY `MIS StaffCustomer Requests` (`AssignedTo`),

  KEY `Unit OfficeCustomer Requests` (`Unit`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--

-- Dumping data for table `userrequests`

--

-- --------------------------------------------------------

--

-- Table structure for table `users`

--

CREATE TABLE IF NOT EXISTS `users` (

  `ID` int(4) NOT NULL,

  `UserName` varchar(32) NOT NULL,

  `Password` varchar(32) NOT NULL,

  PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--

-- Dumping data for table `users`

--

--

-- Constraints for dumped tables

--

--

-- Constraints for table `userrequests`

--

ALTER TABLE `userrequests`

  ADD CONSTRAINT `MIS StaffCustomer Requests` FOREIGN KEY (`AssignedTo`) REFERENCES `misstaff` (`StaffID`) ON DELETE NO ACTION ON UPDATE NO ACTION,

  ADD CONSTRAINT `Request TypeCustomer Requests` FOREIGN KEY (`RequestID`) REFERENCES `requesttype` (`RequestID`) ON DELETE NO ACTION ON UPDATE NO ACTION,

  ADD CONSTRAINT `Unit OfficeCustomer Requests` FOREIGN KEY (`Unit`) REFERENCES `unitoffice` (`UnitID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

 

• Schema of all views (and diagrams): 
  • Primary – Ricardo Altman
  • Secondary - Jeremy Dieringer

 

 

 

• Security plan (roles and acess levels to tables, views, stored procedures):
  • Primary - Ricardo Altman
  • Secondary - Jeremy Dieringer
    • The support staff have full administrative access to the database, table, and hosting site.
    • Users submitting request calls only have add/view access to the userrequest table. 
    • The database and all web pages can only be accessed by the appropriate support personnel. All other users will be redirected to the main page.

 

• Indexing plan and maintenance plan:
  • Primary - Jeremy Dieringer
  • Secondary - Ricardo Altman
    • The hosting site has built in performance utilities that the client will be able to support from the back-end. The client will also be the support manager and will be responsible for the continaul maintenance of the system.

 

• Proof of deployment in approved database product:
  • Primary - David Rodriguez / Jeremy Dieringer / Ricardo Altman