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 topics such as 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:...................................................................................................................................12
Appendix 1: Student Information Entry Form Coding..............................................................12
Appendix 2: Job Update Form Coding......................................................................................15
Appendix 3: Student Address Report Coding...........................................................................19
Appendix 4: Job Assigning Form Coding.................................................................................20
Appendix 5: Nutrition Related Data Report Coding.................................................................24

2DATABASE DESIGN AND IMPLEMENTATION
1. Database Designing:
1.1 ERD of the Database:

3DATABASE DESIGN AND IMPLEMENTATION
1.2 Business Rules:
Entity Rule Entity
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:
The tables are already in the third normal form as all the attributes are atomic, there is no
partial dependency and no transitive dependency.

4DATABASE DESIGN AND IMPLEMENTATION
2. Database Implementation:
2.1 Data Dictionary:
The whole database is being installed in the phpMyAdmin. The wordpress site has been
also installed in the localhost of phpMyAdmin.

5DATABASE DESIGN AND IMPLEMENTATION
2.2 PhpMyAdmin Based Database Development:
Student Information Entry Form:
The first form is student information entry form. In this form the data will be provide by
the student.

6DATABASE DESIGN AND IMPLEMENTATION
Suppose a student selects course Bachelor of Arts, her name is Lauren Wood and fills
other data then click submit.
The data is also inserted in the student entity of the database.
Student Information Entry Form:
The second form is job update form. In this form the job, roster and batch_number of the
volunteer can be changed. The director will first enter the id of the student whose job needs to be
updated.

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.

8DATABASE DESIGN AND IMPLEMENTATION
Student Address Report:
The address of all the students will appear at screen when the page will be opened.
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.

9DATABASE DESIGN AND IMPLEMENTATION
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.

10DATABASE DESIGN AND IMPLEMENTATION
Bibliography:
Albuquerque, L.C., 2017. 2D Vector Map and Database design for Indoor Assisted Navigation.
Bennett, R.J., Smith, H.J., van Lieshout, C. and Newton, G.H., 2017. Drivers of
Entrepreneurship and Small Businesses: Project overview and database design.
Echeverria, V., Martinez-Maldonado, R., Chiluiza, K. and Shum, S.B., 2017. Dbcollab:
Automated Feedback for Face-to-Face Group Database Design. In Proceedings of 25th
International Conference on Computers in Education (ICCE 2017).
Groom, C.R., Bruno, I.J., Lightfoot, M.P. and Ward, S.C., 2016. The Cambridge structural
database. Acta Crystallographica Section B: Structural Science, Crystal Engineering and
Materials, 72(2), pp.171-179.
Letkowski, J., 2015. Doing database design with MySQL. Journal of Technology Research, 6,
p.1.
Richter, K.S., Mottla, G.L., Kaplan, B., Hayward, B. and Mahony, M.C., 2016. Evaluating
pregnancy and live birth outcomes in a real-world analysis of a US database of 66,051 transfers
over 6.5 years. Fertility and Sterility, 106(3), pp.e53-e54.
Stonebraker, M., Deng, D. and Brodie, M.L., 2017. Application-Database Co-Evolution: A New
Design and Development Paradigm. New England Database Day, pp.1-3.
Youssef, M.W. and Asfour, A., 2015. A Proposed Design of Logistics Information Systems
Database to Enhance the Effectiveness of Supply Chain Manager Decisions. International
Journal of Advance Research in Education, Technology & Management.

11DATABASE DESIGN AND IMPLEMENTATION
Zhou, B., Lapedriza, A., Xiao, J., Torralba, A. and Oliva, A., 2014. Learning deep features for
scene recognition using places database. In Advances in neural information processing
systems (pp. 487-495).

12DATABASE 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;

13DATABASE 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,

14DATABASE 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>

15DATABASE 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');

16DATABASE 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>

17DATABASE 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'];

18DATABASE 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>';

19DATABASE 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');

20DATABASE 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 */ ?>

21DATABASE 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'];

22DATABASE 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

23DATABASE 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" />

24DATABASE 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>

25DATABASE 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>