Customer Satisfaction System Database Design

Customer Satisfaction System Database Design

The purpose of the study is to improve, develop, and implement a system that will help the organization in the processing and evaluation of Customer Satisfaction. It presents a new aspect of developing the study including on the different services.  Through using Tablets to survey, it can make the school effective and efficient in terms of technology.

Technical Background

Customer Satisfaction System is developed using the rapid application development model.  It is one of the process models in System Development Life Cycle (SDLC) that has a series of steps which involves the number of phases or procedures that gave the complete software. All possible requirements were gathered and defined in a manner that is understandable by both user and developer. This study is in a form of research in which you ask the students, faculty and staff for their views on issues that indicate how well or how badly the services is performing with the system.

The proponents will install the XAMMP Server 1.7.3 an Apache distribution containing MySQL, PHP and Perl in which will provide a support for creating and manipulating the Database.  The relevant to the demand of the system is to have a software program which is important, the Hypertext Mark-up Language (HTML) that will be used for the design of system, PHP: Hypertext Pre-processor (PHP) is a server-side scripting language that used for web development and also used as a general-purpose programming language.  For the query, we used MySQL Front a client for Windows that allows for the management, to manage a local or remote Database through a client interface.  Since we consider PHP-Nuke to be more appropriate and flexible tool for this task, we would like to focus our attention a little on a functionality of MySQLFront.  That is the ability to load, import and export Databases of great dimensions without losing data or getting errors.  Also we used Google Chrome as the browser and Intranet for the connection only within the organization.

Database Design/Schema

tblusers (id, fullname, designation, contact, picture, username, password)

Description: the users table will store the information of the users such as the name, designation, username and password

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblusers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(100) NOT NULL,
  `designation` varchar(50) NOT NULL,
  `contact` varchar(11) NOT NULL,
  `picture` blob NOT NULL,
  `username` varchar(30) NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblregistration (regid, schoolid, fullname, contact, departmentid, type(student, faculty, staff, visitor), status (active, inactive),username, password, approvedby)

Description: registration table is where the personal information of the students, faculty, staff and stakeholder are stored. The table will also store the username and password of the stakeholders that is used to access the system and take the survey. Registration will be approved first by the admin before the account can be activated and used.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblregistration` (
  `regid` int(11) NOT NULL AUTO_INCREMENT,
  `schoolid` varchar(10) NOT NULL,
  `fullname` varchar(10) NOT NULL,
  `contact` varchar(11) NOT NULL,
  `departmentid` int(11) NOT NULL,
  `type` varchar(15) NOT NULL,
  `status` varchar(10) NOT NULL,
  `username` varchar(30) NOT NULL,
  `password` varchar(30) NOT NULL,
  `approvedby` int(11) NOT NULL,
  PRIMARY KEY (`regid`),
  KEY `departmentid` (`departmentid`,`approvedby`),
  KEY `approvedby` (`approvedby`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblquestioncategory (catid, categoryname(Library, Instruction, etc))

Description: question category table is used to encode and store the different areas that will be evaluated by the stakeholders.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblquestioncategory` (
  `catid` int(11) NOT NULL AUTO_INCREMENT,
  `categoryname` varchar(30) NOT NULL,
  PRIMARY KEY (`catid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblsurveyquestion (surveyid, categoryid, question)

Description: survey question table is where the questions and the different criteria will be encoded and stored. This table is connected or linked with the tblquestioncategory.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblsurveyquestion` (
  `surveyid` int(11) NOT NULL AUTO_INCREMENT,
  `categoryid` int(11) NOT NULL,
  `question` varchar(100) NOT NULL,
  PRIMARY KEY (`surveyid`),
  KEY `categoryid` (`categoryid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbldepartment (deptid, departmentname)

Description: tbldepartment is the table where the courses and other office information will be encoded and stored.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbldepartment` (
  `deptid` int(11) NOT NULL AUTO_INCREMENT,
  `departmentname` varchar(30) NOT NULL,
  PRIMARY KEY (`deptid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblsurvey (id, surveyquestionid, rating, regid, departmentid)

Description: survey table is responsible for storing the answers of the stakeholders and as well as the result are fetch from this table. The table is connection or linked to the tbldepartment, tblsurveyquestion and tblregistration

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblsurvey` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `surveyquestionid` int(11) NOT NULL,
  `rating` int(11) NOT NULL,
  `regid` int(11) NOT NULL,
  `departmentid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `surveyquestionid` (`surveyquestionid`,`regid`,`departmentid`),
  KEY `regid` (`regid`),
  KEY `departmentid` (`departmentid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Constraints for dumped tables

 — Constraints for table `tblregistration`

 ALTER TABLE `tblregistration`

ADD CONSTRAINT `tblregistration_ibfk_2` FOREIGN KEY (`departmentid`) REFERENCES `tbldepartment` (`deptid`) ON DELETE CASCADE ON UPDATE CASCADE,

ADD CONSTRAINT `tblregistration_ibfk_1` FOREIGN KEY (`approvedby`) REFERENCES `tblusers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

— Constraints for table `tblsurvey`

ALTER TABLE `tblsurvey`

ADD CONSTRAINT `tblsurvey_ibfk_3` FOREIGN KEY (`departmentid`) REFERENCES `tbldepartment` (`deptid`) ON DELETE CASCADE ON UPDATE CASCADE,

ADD CONSTRAINT `tblsurvey_ibfk_1` FOREIGN KEY (`surveyquestionid`) REFERENCES `tblsurveyquestion` (`surveyid`) ON DELETE CASCADE ON UPDATE CASCADE,

ADD CONSTRAINT `tblsurvey_ibfk_2` FOREIGN KEY (`regid`) REFERENCES `tblregistration` (`regid`) ON DELETE CASCADE ON UPDATE CASCADE;

— Constraints for table `tblsurveyquestion`

ALTER TABLE `tblsurveyquestion`

ADD CONSTRAINT `tblsurveyquestion_ibfk_1` FOREIGN KEY (`categoryid`) REFERENCES `tblquestioncategory` (`catid`) ON DELETE CASCADE ON UPDATE CASCADE;

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive free capstone projects resources and computer programming tutorials.

Hire our team to do the project.