IO Lounge Database Design and Implementation

This article discusses the database design and implementation for IO Lounge, an online library for study material with solved assignments, essays, dissertation etc. It covers ERD of the database, business rules, normalization, data dictionary, and PhpMyAdmin based database development.

Running head: DATABASE DESIGN AND IMPLEMENTATION
IO Lounge Database Design and Implementation
Name of the Student
Name of the University

1DATABASE DESIGN AND IMPLEMENTATION
Table of Contents
1. Database Designing:....................................................................................................................2
1.1 ERD of the Database:............................................................................................................2
1.2 Business Rules:......................................................................................................................3
1.3 The Normalization:....................................................................................................................3
2. Database Implementation:...........................................................................................................4
2.1 Data Dictionary:.....................................................................................................................4
2.2 PhpMyAdmin Based Database Development:......................................................................5
Bibliography:.................................................................................................................................10
Appendices:...................................................................................................................................11
Appendix 1: Student Information Entry Form Coding..............................................................11
Appendix 2: Job Update Form Coding......................................................................................14
Appendix 3: Student Address Report Coding...........................................................................18
Appendix 4: Job Assigning Form Coding.................................................................................19
Appendix 5: Nutrition Related Data Report Coding.................................................................23

2DATABASE DESIGN AND IMPLEMENTATION
1. Database Designing:
1.1 ERD of the Database:
1.2 Business Rules:
Entity Rule Entity

3DATABASE DESIGN AND IMPLEMENTATION
Student One-to-One Volunteer
Student Many-to-One Course
Volunteer One-to-Many Volunteer_Job
Job Many-to-One Volunteer_Job
Job One-to-Many Task
Volunteer_Job One-to-Many Roster
1.3 The Normalization:
All the tables are already in third normal form.

4DATABASE DESIGN AND IMPLEMENTATION
2. Database Implementation:
2.1 Data Dictionary:
The PHPMyAdmin has been sued for installing the database.

5DATABASE DESIGN AND IMPLEMENTATION
2.2 PhpMyAdmin Based Database Development:
Student Information Entry Form:
Through this form, the students are registered into the system.
Suppose a student selects course Bachelor of Arts, her name is Lauren Wood and fills
other data then click submit.

6DATABASE DESIGN AND IMPLEMENTATION
The data is also inserted in the student entity of the database.
Student Information Entry Form:
This form is for updating the job details. The admin will enter the student ID and the job
details of the student will appear on the screen.

7DATABASE DESIGN AND IMPLEMENTATION
Suppose director inputs 3. Then the details of job of the student with id 3 will appear in
the screen. If the director changes any data then clicks submit. Then the job and roster will be
updated. Suppose the student wants to be waiter on 13th Feb.
The data is also inserted in the student entity of the database.
Student Address Report:
The address of all the students will appear at screen when the page will be opened.

8DATABASE DESIGN AND IMPLEMENTATION
See the last entered student address is also available in the page.
Fill Job:
Here a student either new of already worked as volunteer will be assigned to job for a
particular day at a particular time. The volunteer batch_number is also provided here.
After submitting all the data the job will be assigned to the volunteer.
Dietary Requirements:
The nutrition related data will be shown in this page.

9DATABASE DESIGN AND IMPLEMENTATION

10DATABASE DESIGN AND IMPLEMENTATION
Bibliography:
Islam, M., 2018. WordPress Theme Development and Customization.
Solichin, A., 2016. Pemrograman web dengan PHP dan MySQL. Penerbit Budi Luhur.

11DATABASE DESIGN AND IMPLEMENTATION
Appendices:
Appendix 1: Student Information Entry Form Coding
<?php /* Template Name: student entry form */ ?>
<?php get_header(); ?>
<?php
/*
Plugin Name: Contact Form
Plugin URI: http://wpgeeks.net/
Version: 1.0
Author: Adam
Description: A simple contact form for testing
*/
include('/../../../wp-load.php');
if(isset($_POST['submit'])){
// use global variable for database opertation
global $wpdb;
$num_rows = $wpdb->get_var( "SELECT COUNT(*) FROM student" ); //PHP count()
$num_rows=$num_rows+1;

12DATABASE DESIGN AND IMPLEMENTATION
// assign value that pass from form
$sid = $num_rows;
$course = $_POST['course'];
$ename= $_POST['ename'];
$address = $_POST['address'];
$phone= $_POST['contact'];
$email= $_POST['email'];
$sex=$_POST['sex'];
// call insert method of wpdb class
if ($wpdb->insert(
'student',
array(
'StudentID' => $sid,
'Course' => $course,
'Name' => $ename,
'Address' => $address,
'Contact' => $phone,
'Email' => $email,

13DATABASE DESIGN AND IMPLEMENTATION
'Sex' => $sex
)
) == false) wp_die('Database insertion failed');
else
echo '<p>Successfully inserted</p>';
echo "<p>Student Name: {$ename}</p>";
}
else {
?>
<form action="" method="post">
<label>Course: <br><input type="radio" name="course" value="1" checked> Diploma
of Accounting<br>
<input type="radio" name="course" value="2"> Bachelor of Arts<br>
<input type="radio" name="course" value="3"> Bachelor of Biomedical<br>
<input type="radio" name="course" value="4"> Bachelor of
Biomedicine<br><br></label>
<label>Name: <input type="text" name="ename" /><br><br></label>
<label>Address: <input type="text" name="address" /><br><br> </label>
<label>Contact: <input type="text" name="contact" /><br><br></label>

14DATABASE DESIGN AND IMPLEMENTATION
<label>Email: <input type="text" name="email" /><br><br></label>
<label>Sex: <br><input type="radio" name="sex" value="male" checked>
Male<br>
<input type="radio" name="sex" value="femail"> Female<br><br></label>
<input type="submit" name="submit" id="addsubmit" value="submit" />
</form>
<?php } ?>
Appendix 2: Job Update Form Coding
<?php /* Template Name: Update */ ?>
<?php get_header(); ?>
<?php
/*
Plugin Name: Contact Form
Plugin URI: http://wpgeeks.net/
Version: 1.0
Author: Adam
Description: A simple contact form for testing
*/
include('/../../../wp-load.php');

15DATABASE DESIGN AND IMPLEMENTATION
if(isset($_POST['submit2'])){
$sid = $_POST['student'];
$all_students=$wpdb->get_results("SELECT * FROM Volunteer v inner join
volunteer_job vj ON v.StudentID = vj.VolunteerID inner join Job j ON vj.JobID = j.JobID inner
join roster ro ON vj.RosterID = ro.RosterID WHERE StudentID = '{$sid}'", OBJECT);
foreach ($all_students as $single_student){
?>
<form action="" method="post">
<label>Student ID: <input type="text" name="student2" value="<?php echo
$sid ?>" /><br><br></label>
<label>Volunteer Batch: <input type="text" name="batch" value="<?php echo
$single_student->Batch_Number ?>" /><br><br></label>
<label>Roster: <input type="date" name="Rdate"/ value="<?php echo
$single_student->Date ?>"><br><br> </label>
<label>Current Job: <?php echo $single_student->Name ?><br></label>
<label>Assign Job: <br><input type="radio" name="job" value="1" checked>
Waiter<br>
<input type="radio" name="job" value="2"> Cook<br>
<input type="radio" name="job" value="3"> Cashier<br>
<input type="radio" name="job" value="4"> Janitor<br><br></label>

16DATABASE DESIGN AND IMPLEMENTATION
<label>Start Time: <input type="time" name="stime" value="<?php echo
$single_student->Start_Time ?>" /><br><br></label>
<label>End Time: <input type="time" name="etime" value="<?php echo
$single_student->End_Time ?>" /><br><br></label>
<input type="submit" name="submit" id="addsubmit" value="submit" />
</form>
<?php }
}
else {
if(isset($_POST['submit'])){
// use global variable for database opertation
global $wpdb;
// assign value that pass from form
$sid2 = $_POST['student2'];
$vbatch= $_POST['batch'];
$rosterdate = $_POST['Rdate'];
$job= $_POST['job'];
$starttime= $_POST['stime'];

17DATABASE DESIGN AND IMPLEMENTATION
$endtime=$_POST['etime'];
$rosdate = $wpdb->get_col("SELECT * FROM roster where date =
'{$rosterdate}'");
// call insert method of wpdb class
if ((($wpdb->update(
'volunteer',
array('Batch_Number' => $vbatch),
array('StudentID' => $sid2),
array('%s')
)) && ($wpdb->update(
'volunteer_job',
array(
'JobID' => $job,
'RosterID' => $rosdate[0]),
array('VolunteerID' => $sid2),
array('%d', '%d')
))) == false) wp_die('Update failed');
else
echo '<p>Updated successfully</p>';

18DATABASE DESIGN AND IMPLEMENTATION
} else {
?>
<form action="" method="post">
<label>Student ID: <input type="text" name="student" /><br><br></label>
<input type="submit" name="submit2" id="addsubmit" value="submit2" />
<?php } } ?>
Appendix 3: Student Address Report Coding
<?php /* Template Name: Student Address Report */ ?>
<?php get_header(); ?>
<?php
/*
Plugin Name: Contact Form
Plugin URI: http://wpgeeks.net/
Version: 1.0
Author: Adam
Description: A simple contact form for testing
*/
include('/../../../wp-load.php');

19DATABASE DESIGN AND IMPLEMENTATION
global $wpdb;
?>
<table>
<tr>
<th>Student Name:</th>
<th>Student Address:</th>
</tr>
<?php
$all_students=$wpdb->get_results("SELECT * FROM Student",
OBJECT);
foreach ($all_students as $single_student){
?>
<tr>
<td><?php echo $single_student->Name ?></td>
<td><?php echo $single_student->Address ?></td>
</tr>
<?php } ?></table>
Appendix 4: Job Assigning Form Coding
<?php /* Template Name: Fill Job */ ?>

20DATABASE DESIGN AND IMPLEMENTATION
<?php get_header(); ?>
<?php
/*
Plugin Name: Contact Form
Plugin URI: http://wpgeeks.net/
Version: 1.0
Author: Adam
Description: A simple contact form for testing
*/
include('/../../../wp-load.php');
if(isset($_POST['submit'])){
// use global variable for database opertation
global $wpdb;
// assign value that pass from form
$sid = $_POST['student'];
$vbatch= $_POST['batch'];
$rosterdate = $_POST['Rdate'];
$job= $_POST['job'];

21DATABASE DESIGN AND IMPLEMENTATION
$starttime= $_POST['stime'];
$endtime=$_POST['etime'];
$rosdate = $wpdb->get_col("SELECT * FROM roster where date =
'{$rosterdate}'");
// call insert method of wpdb class
if ((($wpdb->insert(
'volunteer',
array(
'StudentID' => $sid,
'Batch_Number' => $vbatch
)
)) && ($wpdb->insert(
'volunteer_job',
array(
'VolunteerID' => $sid,
'JobID' => $job,
'RosterID' => $rosdate[0],
'Start_Time' => $starttime,
'End_Time' => $endtime

22DATABASE DESIGN AND IMPLEMENTATION
)
))) == false) wp_die('Database insertion failed');
else
echo '<p>Job Successfully Assigned</p>';
}
else {
?>
<form action="" method="post">
<label>Student ID: <input type="text" name="student" /><br><br></label>
<label>Volunteer Batch: <input type="text" name="batch" /><br><br></label>
<label>Roster: <input type="date" name="Rdate" /><br><br> </label>
<label>Job: <br><input type="radio" name="job" value="1" checked> Waiter<br>
<input type="radio" name="job" value="2"> Cook<br>
<input type="radio" name="job" value="3"> Cashier<br>
<input type="radio" name="job" value="4"> Janitor<br><br></label>
<label>Start Time: <input type="time" name="stime" /><br><br></label>
<label>End Time: <input type="time" name="etime" /><br><br></label>
<input type="submit" name="submit" id="addsubmit" value="submit" />

23DATABASE DESIGN AND IMPLEMENTATION
</form>
<?php } ?>
Appendix 5: Nutrition Related Data Report Coding
<?php /* Template Name: Nutrition */ ?>
<?php get_header(); ?>
<?php
/*
Plugin Name: Contact Form
Plugin URI: http://wpgeeks.net/
Version: 1.0
Author: Adam
Description: A simple contact form for testing
*/
include('/../../../wp-load.php');
global $wpdb;
?>
<table>
<tr>

24DATABASE DESIGN AND IMPLEMENTATION
<th>Student Name:</th>
<th>Criteria:</th>
</tr>
<?php
$all_students=$wpdb->get_results("SELECT s.Name, Details FROM
nutrition n inner join student s ON n.StudentID = s.StudentID", OBJECT);
//var_dump($students);
foreach ($all_students as $single_student){
?>
<tr>
<td><?php echo $single_student->Name ?></td>
<td><?php echo $single_student->Details ?></td>
</tr>
<?php } ?></table>