Capstone Project Monitoring System Free Database Design Tutorial
Table of Contents
Introduction
In this tutorial, we will focus on the database design of a capstone project management platform. In particular, we will focus on the database design of a Capstone Project Monitoring System designed for use within department or college program. This system is used by teachers and student who are taking up capstone project to keep track of projects that they are working on. In this article, we will cover topics such as how to create tables necessary to establish the said project.
Let us now create the tables and their columns. Please watch the video for the complete tutorial.
Capstone Project Monitoring System Free Database Design Tutorial – List of Tables
tbl_adviser – this table will store the information of the adviser of the students during their capstone project undertaking.
- adviser_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- full_name – the complete name of the adviser
- credential- the adviser’s detailed credentials
- email_address – the email address of the adviser
- contact_number – the contact number of the adviser preferably cellphone or mobile number
- avatar – this will hold the profile image of the adviser
- username – the desired username of the adviser combined with the password needed to login to the system.
- password – the preferred password of the adviser, used also together with the username when logging in to the system.
- account_status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.
- user_id – user who encoded the record
Create SQL Statement – the statement below is used to create the tbl_adviser, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_adviser` ( `adviser_id` int(11) NOT NULL, `full_name` varchar(100) NOT NULL, `credential` text NOT NULL, `email_address` varchar(50) NOT NULL, `contact_number` varchar(15) NOT NULL, `avatar` blob NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `account_status` int(1) NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_group – the details of the groups undertaking capstone project will be stored in this table.
- group_id- primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- group_code – this is a unique code given to a specific group
- group_members – the name of the members of the group
- capstone_project_title – the title of the group’s capstone project
- abstract – the abstract of the capstone project topic
- school_year – the specific school the capstone project was conducted
Create SQL Statement – the statement below is used to create the tbl_group, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_group` ( `group_id` int(11) NOT NULL, `group_code` varchar(10) NOT NULL, `group_members` varchar(500) NOT NULL, `capstone_project_title` varchar(200) NOT NULL, `abstract` varchar(300) NOT NULL, `school_year` varchar(4) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_group_member_info – this table will hold the information of the group’s individual members.
- member_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- group_code – the unique code given to the group
- member_name – the complete name of the member
- role – the role of the member in the group
Create SQL Statement – the statement below is used to create the tbl_group_member_info, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_group_member_info` ( `member_id` int(11) NOT NULL, `group_code` varchar(10) NOT NULL, `member_name` varchar(100) NOT NULL, `role` varchar(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_milestone – the details of the student’s milestone in their capstone project undertaking will be stored in this table.
- milestone_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- milestone_name – the name of the milestone
- milestone_description – the description of the milestone
- deadline – the deadline of the milestone to accomplish
Create SQL Statement – the statement below is used to create the tbl_milestone, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_milestone` ( `milestone_id` int(11) NOT NULL, `milestone_name` varchar(50) NOT NULL, `milestone_description` varchar(200) NOT NULL, `deadline` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_milestone_details – the details of the milestone are held and stored in this table.
- detail_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- milestone_id – this is a foreign key that points out to the
- group_id – this is a foreign key that points out to the group
- file_uploaded – the file uploaded by the group
- remarks – comments and additional information about the milestone
- uploaded_date – the date the file was uploaded
- status – the status of the milestone
Create SQL Statement – the statement below is used to create the tbl_milestone_details, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_milestone_details` ( `detail_id` int(11) NOT NULL, `milestone_id` int(11) NOT NULL, `group_id` int(11) NOT NULL, `file_uploaded` blob NOT NULL, `remarks` varchar(100) NOT NULL, `uploaded_date` date NOT NULL, `status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_completed_capstone – this table will store the information of the completed capstone projects by students.
- record_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- capstone_project_title – the title of the completed capstone project.
- group_id – this is a foreign key that points out to the group
- year_completed – the year the capstone project was completed
- abstract – the abstract of the completed capstone project
- adviser_id – this is a foreign key that points out to the adviser of the group that completed the capstone project.
Create SQL Statement – the statement below is used to create the tbl_completed_capstone, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_completed_capstone` ( `record_id` int(11) NOT NULL, `capstone_project_title` varchar(200) NOT NULL, `group_id` int(11) NOT NULL, `year_completed` varchar(4) NOT NULL, `abstract` varchar(300) NOT NULL, `adviser_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_user – this table will store the information of personnel who can access the records of the system.
- user_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- username – username of the personnel used to login together with the password.
- password – password of the personnel used to login together with the username.
- avatar – this will hold the profile image of the user.
- fullname – the complete name of the personnel or user.
- contact – contact number of the personnel (mobile/cellphone number).
- email – email address of the personnel/user.
- user_category_id – the user group or category of the user.
- status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.
Create SQL Statement – the statement below is used to create the tbl_user, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_user` ( `user_id` int(11) NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `avatar` blob NOT NULL, `fullname` varchar(100) NOT NULL, `contact` varchar(15) NOT NULL, `email` varchar(50) NOT NULL, `user_category_id` int(11) NOT NULL, `status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_user_group – this table store the information of the user group which includes the functions they can and can’t access in the system.
- user_group_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- group_name – name of the category or user group.
- description – information on what the user group is all about.
- allow_add – this column is to allow or prevent user from adding a record.
- allow_edit – this column is to allow or prevent user from editing or updating a record.
- allow_delete – this column is to allow or prevent user from removing or deleting a record.
- allow_print – this column is to allow or prevent user from printing a report.
- allow_import – this column is to allow or prevent user from importing records to the system.
- allow_export – this column is to allow or prevent user from exporting records from the system.
Create SQL Statement – the statement below is used to create the tbl_user_group, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_user_group` ( `user_group_id` int(11) NOT NULL, `group_name` varchar(30) NOT NULL, `description` varchar(100) NOT NULL, `allow_add` int(1) NOT NULL, `allow_edit` int(1) NOT NULL, `allow_delete` int(1) NOT NULL, `allow_print` int(1) NOT NULL, `allow_import` int(1) NOT NULL, `allow_export` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_feature_access – this will store information of the features that can be access by the users.
- access_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- user_id- this is a foreign key that points out to the user
- access_adviser – this will allow access to the records of the advisers in the system
- access_group – this will allow access to the records of the group in the system.
- access_group_members – this will allow access to the group members information.
- access_milestone – this will allow access to the milestone or files uploaded in the system.
- access_completed_project – this will allow access to the details of the completed capstone project in the system.
- access_users – this will allow access to the records of the users of the system.
Create SQL Statement – the statement below is used to create the tbl_feature_access, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_feature_access` ( `access_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `access_adviser` int(1) NOT NULL, `access_group` int(1) NOT NULL, `access_group_members` int(1) NOT NULL, `access_milestone` int(1) NOT NULL, `access_completed_project` int(1) NOT NULL, `access_users` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Benefits of the project
- Assess the effectiveness of your thesis project.
- Gives you a record to refer to when you are unsure of what should be done next.
- Keeps you on the right track and progressing towards your objectives.
- Motivates to continue working on your thesis project when it starts becoming problematic.
- Supports any process improvement efforts aimed at improving the quality or efficiency of the thesis/capstone project
Indexes for dumped tables
-- -- Indexes for table `tbl_adviser` -- ALTER TABLE `tbl_adviser` ADD PRIMARY KEY (`adviser_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `tbl_completed_capstone` -- ALTER TABLE `tbl_completed_capstone` ADD PRIMARY KEY (`record_id`), ADD KEY `group_id` (`group_id`), ADD KEY `adviser_id` (`adviser_id`); -- -- Indexes for table `tbl_feature_access` -- ALTER TABLE `tbl_feature_access` ADD PRIMARY KEY (`access_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `tbl_group` -- ALTER TABLE `tbl_group` ADD PRIMARY KEY (`group_id`); -- -- Indexes for table `tbl_group_member_info` -- ALTER TABLE `tbl_group_member_info` ADD PRIMARY KEY (`member_id`); -- -- Indexes for table `tbl_milestone` -- ALTER TABLE `tbl_milestone` ADD PRIMARY KEY (`milestone_id`); -- -- Indexes for table `tbl_milestone_details` -- ALTER TABLE `tbl_milestone_details` ADD PRIMARY KEY (`detail_id`), ADD KEY `milestone_id` (`milestone_id`), ADD KEY `group_id` (`group_id`); -- -- Indexes for table `tbl_user` -- ALTER TABLE `tbl_user` ADD PRIMARY KEY (`user_id`), ADD KEY `user_category_id` (`user_category_id`); -- -- Indexes for table `tbl_user_group` -- ALTER TABLE `tbl_user_group` ADD PRIMARY KEY (`user_group_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `tbl_adviser` -- ALTER TABLE `tbl_adviser` MODIFY `adviser_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_completed_capstone` -- ALTER TABLE `tbl_completed_capstone` MODIFY `record_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_feature_access` -- ALTER TABLE `tbl_feature_access` MODIFY `access_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_group` -- ALTER TABLE `tbl_group` MODIFY `group_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_group_member_info` -- ALTER TABLE `tbl_group_member_info` MODIFY `member_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_milestone` -- ALTER TABLE `tbl_milestone` MODIFY `milestone_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_milestone_details` -- ALTER TABLE `tbl_milestone_details` MODIFY `detail_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_user` -- ALTER TABLE `tbl_user` MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_user_group` -- ALTER TABLE `tbl_user_group` MODIFY `user_group_id` int(11) NOT NULL AUTO_INCREMENT;
Summary
This project is the monitoring system for tracking the updates and deliverables of student’s capstone project. The project will consist of a MySQL database, web interface, and desktop/mobile applications. The database system will be accessible from any computer or mobile devices connected to the internet.
The contents presented earlier are the possible database tables which will be used as the storage of records for the capstone project entitled Capstone Project Monitoring System.
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.
Related links:
Project Management System Database Design
Billing System Database Design
