Graduate Tracer System Database Project

Graduate Tracer System Database Project

About the System

The academic institutions have never had an easy time tracking down and keeping tracks on their graduates. The graduates must provide more information in order for the school to keep track their employment status and records. The Graduate Tracer System will be able to track graduates to determine whether they are employed or not. The information acquired by the system aids the school administrators in determining whether curriculum adjustments are necessary as well as the percentage of unemployed people and/or job mismatches

The system can be used to track the alumni’s whereabouts so that the school can keep in touch with them and know how they are doing. The system can also be used by employers to find potential employees who have graduated from the school. The system is beneficial to the alumni because it gives them a way to stay connected with their alma mater and to know what is happening there. It is also beneficial to the school because it helps keep track of alumni and makes it easier for them to find new students.

Database Tables

This article will provide you with an idea about the Graduate Tracer system database design.

tbl_admin – this table stores the information of the admin in the system and it contains seven columns.

• admin_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 – the desired username of the admin combined with the password to login to his/her account.
• password – the preferred password of the admin for his/her account.
• complete_name- the complete name of the administrator.
• designation – the designation of the administrator.
• email_address – the email address of the administrator
• contact – the contact info of the administrator, preferably mobile number.

Create SQL Statement – the statement below is used to create the tbl_admin, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_admin` (
`admin_id` int(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`complete_name` varchar(100) NOT NULL,
`designation` varchar(30) NOT NULL,
`email_address` varchar(50) NOT NULL,
`contact` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_graduate_profile – this table stores the personal data of the graduates registered in the system. Profile of graduates table has 19 columns.

• profile_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).
• lastname – last name of the graduate
• firstname – the graduate’s first name
• middlename – the graduate’s middle name
• course_id – this is a foreign key that points out to the course of the student
• gender – the gender of the graduate student
• civil_status – the civil status of the graduate student
• email_address – the email address of the student
• contact_info – the contact information of the student
• profile_image – the profile image of the graduate student
• facebook_id – this is a foreign key that points out to the student’s facebook account
• twitter_id – this is a foreign key that points out to the student’s twitter account
• street_address – the address of the graduate student
• city – the city where the student live
• state_province_region – the state, province or region where the student live in
• zip_postal_code – postal code of the address
• country- the country where the graduate student live in
• username – the desired username of the graduate student
• password -the desired password of the graduate student

Create SQL Statement – the statement below is used to create the tbl_graduate_profile, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_graduate_profile` (
`profile_id` int(11) NOT NULL,
`lastname` varchar(30) NOT NULL,
`firstname` varchar(30) NOT NULL,
`middlename` varchar(30) NOT NULL,
`course_id` int(11) NOT NULL,
`gender` int(1) NOT NULL,
`civil_status` int(1) NOT NULL,
`email_address` varchar(50) NOT NULL,
`contact_info` varchar(50) NOT NULL,
`profile_image` text NOT NULL,
`facebook_id` varchar(50) NOT NULL,
`twitter_id` varchar(50) NOT NULL,
`street_address` varchar(50) NOT NULL,
`city` varchar(50) NOT NULL,
`state_province_region` varchar(100) NOT NULL,
`zip_postal_code` varchar(15) NOT NULL,
`country` varchar(50) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_course – the details of the courses offered are stored in this table and it has three fields.

• course_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).
• course_code – unique code given to a specific course
• course_name – the name of the course

Create SQL Statement – the statement below is used to create the tbl_course, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_course` (
`course_id` int(11) NOT NULL,
`course_code` varchar(15) NOT NULL,
`course_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_employment_profile – this table stores the employment profile data in the system. This table is very important since this is the source of the statistics module wherein it will display the percentage of graduates who have landed a work or job. The said table has nine database columns.

• employment_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).
• graduate_id – this is a foreign key that points out to the graduate
• job_name – the name of the job
• description – description of the job employment
• upload_proof_of_employment – id, certificate of employment, etc
• orgranization_type – private, public, ngo, self_employment
• status – permanent, contractual, casual
• is_related_to_course – indicate if the job is related to the course
• remarks – additional information about the employment

Create SQL Statement – the statement below is used to create the tbl_employment_profile, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_employment_profile` (
`employee_id` int(11) NOT NULL,
`graduate_id` int(11) NOT NULL,
`job_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL,
`upload_proof_of_employment` text NOT NULL,
`organization_type` int(1) NOT NULL,
`status` int(1) NOT NULL,
`is_related_to_course` int(1) NOT NULL,
`remarks` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_front_end_pages – this table stores the information of the front end pages of the site and it has four columns.

• front_end_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).
• home_page – this stores the information of the home page of the site.
• contact_page – the information in the contact page
• about_page – the information in the about page

Create SQL Statement – the statement below is used to create the tbl_front_end_pages, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_front_end_pages` (
`front_end_id` int(11) NOT NULL,
`home_page` text NOT NULL,
`contact_page` text NOT NULL,
`about_page` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_events – the information about the events will be stored in this table. Events table of the project has six columns.

• event_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).
• event_name – the name of the event
• description – description of the event
• date_of_event – the date when the event will be held
• time_of_event – the time when the event will be held
• banner_image – banner image for the event

Create SQL Statement – the statement below is used to create the tbl_events, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_events` (
`event_id` int(11) NOT NULL,
`event_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL,
`date_of_event` date NOT NULL,
`time_of_event` time NOT NULL,
`banner_image` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_job_opportunities – this table stores the information of the job opportunities in the system. Job opportunities will allow the graduates to browse and apply for a job that is related to their course. The table has eight columns.

• job_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).
• job_name – the name of the job
• description – description of the job
• requirements – requirements for the job
• location – location of the job
• starting_salary – starting salary for the job
• contact_info – contact information for the employer
• status – active, inactive

Create SQL Statement – the statement below is used to create the tbl_job_opportunities, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_job_opportunities` (
`job_id` int(11) NOT NULL,
`job_name` varchar(30) NOT NULL,
`description` text NOT NULL,
`requirements` text NOT NULL,
`location` varchar(100) NOT NULL,
`starting_salary` varchar(10) NOT NULL,
`contact_info` varchar(15) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

FREE DOWNLOAD SQL FILE

Summary

The project entitled Graduate Tracer Study Application is an online platform that will be used to gather and archive the information of the graduates, this study is somewhat similar or related to alumni information system. The said project was designed and developed in PHP, MySQL and Bootstrap.

The Graduate Tracer System (GTS) is a tool used to track the employment outcomes of university graduates. It is used by universities to collect data on the employment status and job satisfaction of their alumni. In this article, we have presented to you the idea about graduate tracer system and provided an example on how to design the database of the said project.

Please watch the video tutorial that will be posted on our YouTube Channel.

Readers are also interested in:

Online Alumni Tracer and Job Portal System with SMS Notification

Top 94 Capstone Project Ideas with Related Literature

Finding Job System with Algorithm Search Job using PHP

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.