Online Year Book Management Database Project

Online Year Book Management Database Project

About the System

Year Book captures memories depicting events and lives of people involved with a school during a given year. The researcher of the proposed system entitled Online Platform for Year Book Compilations provide platform that enables compilation of year book convenient. Through this system accessing year book will be easier. Through the proposed system, the students and staff can be able to see the tasks and achievements done in previous years. This system will eradicate the manual way of accessing year book for it will have an online platform wherein in just a click you could reminisce what had happen during the previous years. The system should also allow for the printing of yearbooks. The printing functionality should allow for the selection of a yearbook layout, and should allow for the insertion of images and text into the yearbook layout. The system should also allow for the export of yearbooks in PDF format.

Database Tables

Let’s start by making the tables and columns. For a thorough tutorial, please watch the video.

tbl_admin – this table will store the information of the admin registered in the system and it will contain 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).
• admin_user – the desired username of the admin, combined with the password to login to the system.
• admin_password – the desired password of the admin for his/her account.
• complete_name – complete name of the administrator
• email_address – the email address of the administrator
• contact_info – the contact information of the admin preferably mobile number
• account_status – active or inactive

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,
`admin_user` varchar(30) NOT NULL,
`admin_password` text NOT NULL,
`complete_name` varchar(100) NOT NULL,
`email_address` varchar(50) NOT NULL,
`contact_info` varchar(15) NOT NULL,
`account_status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_course – the details of the courses are stored in this table. Course table has three database 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 particular code
• 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(10) NOT NULL,
`course_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_course_checker – this table will store the information of the course checker in the system. Course checker refers to the assigned personnel that will check, verify and make sure that the information of the graduates is correct. The said table has seven fields.

• checker_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).
• checker_name – the name of the checker
• course_id – this is a foreign key that points out to the course of the student
• checker_user – the username of the checker combined with the password to login to the system.
• checker_password – the preferred password of the password of the user
• email_address – email address of the course checker
• account_status – active or inactive

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

CREATE TABLE `tbl_course_checker` (
`checker_id` int(11) NOT NULL,
`checker_name` varchar(100) NOT NULL,
`course_id` int(11) NOT NULL,
`checker_user` varchar(30) NOT NULL,
`checker_password` text NOT NULL,
`email_address` varchar(50) NOT NULL,
`account_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_year – this table will store the information of the years that already have a year book and it has three columns.

• year_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).
• graduation_year – the year of the graduation
• remarks – additional information of the years

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

CREATE TABLE `tbl_year` (
`year_id` int(11) NOT NULL,
`graduation_year` varchar(10) NOT NULL,
`remarks` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_yearbook_content – this table store the information of the yearbook content. This table is very important to the batch of graduates since it will store their batch theme song and same day edit video which covers the graduation event. Yearbook content table has six columns.

• content_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).
• year_id – this is a foreign key that points out to the year
• cover_photo – cover photo of the yearbook
• messages – messages of important personnel in the yearbook
• graduation_song – graduation song in the yearbook
• same_day_edit_video – the video edited for the graduation

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

CREATE TABLE `tbl_yearbook_content` (
`content_id` int(11) NOT NULL,
`year_id` int(11) NOT NULL,
`cover_photo` varchar(100) NOT NULL,
`messages` text NOT NULL,
`graduation_song` varchar(100) NOT NULL,
`same_day_edit_video` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_memories – this table stores the memory details in the yearbook. The graduates may submit a photo of their classmates and other memorable event during their college days. The memories table contains seven columns.

• memory_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).
• year_id – this is a foreign key that points out to the year
• course_id – this is a foreign key that points out to the course
• checker_id – this is a foreign key that points out to the checker
• upload_photo – this is used to upload photo for memories
• description – additional information about memories
• type – public, private

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

CREATE TABLE `tbl_memories` (
`memory_id` int(11) NOT NULL,
`year_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`checker_id` int(11) NOT NULL,
`upload_photo` varchar(100) NOT NULL,
`description` varchar(100) NOT NULL,
`type` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_student_info – this table stores the information of the students registered in the system. The system will allow the students to check their profile and input their motto. The student info table has 14 columns.

• student_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).
• id_number – unique id number of the student
• last_name – the last name of the student
• first_name – the first name of the student
• middle_name – the middle name of the student
• motto -the student’s motto
• awards – the awards of the student
• thesis_capstone_project – the thesis capstone project of the student
• course_id – this is a foreign key that points out to the course of the student
• year_id – this is a foreign key that points out to the year
• formal_attire_photo – this is used to upload formal attire photo of the student
• toga_photo – this is used to upload toga photo of the student
• username – the desired username of the student
• password – the desired password of the student

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

CREATE TABLE `tbl_student_info` (
`student_id` int(11) NOT NULL,
`id_number` varchar(10) NOT NULL,
`last_name` varchar(30) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`motto` varchar(100) NOT NULL,
`awards` text NOT NULL,
`thesis_capstone_project` varchar(150) NOT NULL,
`course_id` int(11) NOT NULL,
`year_id` int(11) NOT NULL,
`formal_attire_photo` varchar(100) NOT NULL,
`toga_photo` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

FREE DOWNLOAD SQL FILE

Summary

The online yearbook management database system is a system that can help to streamline the process of creating and managing a yearbook. This system can help to reduce the amount of time and effort that is required to create and manage a yearbook. The online year book management database system can help to reduce the amount of paper that is used to create and manage a yearbook. The online year book management database system can also help to reduce the amount of time that is required to search for information about people and events in a yearbook.

With this article, we believe that you could also create and design the database schema for your school’s online year book management system.

Readers are also interested in:

Mobile Based Year Book Gallery App Capstone Project

Top 35 Free School Related IT Capstone Project

Online Platform for Year Book Compilations

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.