Database Systems for Desklib Online Library

This article discusses the benefits of database systems and the implementation of a database for Desklib online library. It includes details on tables, relationships, normalization, and use of PhpMyAdmin and Wordpress. The article also covers the use of forms and reports to input and display data.

DATA BASE SYSTEMS

Table of Contents
1. Introduction..........................................................................................................................................................2
2. Task 1....................................................................................................................................................................2
3. Task 2 : Database Implementation.....................................................................................................................6
The SQL Dump file is attached...................................................................................................................................9
Wordpress....................................................................................................................................................................22
4. Conclusion...........................................................................................................................................................25
5. References...........................................................................................................................................................26
1

1. Introduction
The database is a set of information. And this database system provides the interface between the
data storage and retrieval (SILBERSCHATZ, 2018). The followings are benefits of this system,
1. Efficient retrieval mechanisms
2. Capacity for large amount of data
3. Multi user support
4. Concurrency and transaction control
5. Security management
6. An easy to use interface languages
7. Persistence storage with backup and recovery for reliability.
The objective of the IO database will be to collect the students details , collect the positions and
facilities deails and alotting the facilities and positions to the suitable students. The database that
satisfies all the objectives will be created. PhpMyAdmin and Wordpress will be used for
database operations and input/output operations using forms/reports.
2. Task 1
2

Five tables are developed
The database is IOdatabase (International Office Database). It got tblStudents table to collect all
the informations about the student. The Faculty and Foodstyle details also collected. Other 4
tables are tblPositionsAllottment , tblFacilityTypeAllottment, tblFacilities and
tblPositions. tblPositionsAllottment is used for alotting the positions for the students.
tblFacilityTypeAllottment are for allotting facilities to the students. tblFacilities stores the details
about the Facilities and tblPositions stores the details about the Positions.
Suitable entities are made
tblStudents(StudentNo, GivenName, FamilyName, Phone No, StreetNo, City, State, PostCode,
Faculty, FoodStyle)
tblPositionsAllottment(Date,PositionsID,StudentID)
tblFacilityTypeAllottment(Data,FacilityID,StudentID)
tblFacilities(FacilityID,Date,FacilityType)
tblPositions(PositionID,Date,PositionsType)
Relationships between entities
StudentNo  GivenName, FamilyName, Phone No, StreetNo, City, State, PostCode, Faculty,
FoodStyle
FacilityID  ,Date,FacilityType
PositionID  Date,PositionsType
3

Suitable attributes are selected. Primary keys are underlined above. The links between tables are
shown. 1:n relationships between entities are shown in the ER Diagram below (Al-Masree, 2015)
(SILBERSCHATZ, 2018).
Business Rules are explained in the diagrams (A Web Based Approach towards the
Automated Generation of ER-Diagram, 2015) (SILBERSCHATZ, 2018)
 Students details should be collected
 Facilities details should be collected
 Positions details should be collected
 Facilities should be assigned to the students
 Positions should be assigned to the students
 Student details can be inputed from the form
4

 Few queries should be run and the results should be displayed in the form of
reports
 School and associated faculties should be linked.
Normalization of database done (Shsu.edu, 2018) (Ischool.utexas.edu, 2018) (Ict.griffith.edu.au,
2018)
The 3NF format is shown below
tblStudents(StudentNo,GivenName,FamilyName,Phone No,Street
No,City,State,PostCode,Faculty,FoodStyle)
tblPositionsAllottment(Date,PositionsID,StudentID)
tblFacilityTypeAllottment(Data,FacilityID,StudentID)
tblFacilities(FacilityID,Date,FacilityType)
tblPositions(PositionID,Date,PositionsType)
tblStudents
StudentNo GivenNam
e
FamilyName PhoneNo StreetNo City State PostCode Faculty FoodStyle
tblPositionsAllottment
Date PositionsID StudentID
tblFacilityTypeAllottment
Data FacilityID StudentID
5

tblFacilities
FacilityID Date FacilityType
tblPositions
PositionID Date PositionsType
There is no multiple values in each table. No records are repeating and they are unique. Hence
the tables are following 1NF rules.
The tables are already got primary keys in a single column. Hence they follow 2NF formats too.
There is no transitive functional references present. Hence the table got 3NF format too.
6

3. Task 2 : Database Implementation
PhpMyAdmin
Phpmyadmin (contributors, 2018) tool is a free software tool. This tool is used to written
in php code. This tool is work under MySQL. We have to done the mysql operations by using
this tool . These operations are managing columns, relations, tables, index users and permissions.
This operations are directly run in any type of SQL statements. And then this tool is used to
update our wiki pages and documentation. Easy to handle this tool. This tool is supports both
RTL and LTR languages. This tool is one member of software freedom conservancy
(WordPress.org, 2018).
The following are benefits of phpmyadmin:
1. Creating critical queries using query by example
2. Administrating multiple services
3. Search8ing globally in a database
4. Import data from CSV and SQL
5. Export data to various formats.
1. First we have to open the table page. And to type a table name in text field.
2. Next to type the number of fields for your database. After enter the number of fields
to click go button. Then to save the table.
3. To type the data for the first row.
(i) Type student no in the field column
(ii) Next to select the data type in type column
(iii) Select index type for the index column.
(iv) Next to select check box
4. Next to click the save button .The is saved.
5. Next to repeat these steps for remaining rows.
7

6. To give the database name in the left sidebar.
7. We want to one more tables you have to follow the above 1-6 steps.
Phone number is a number and others can be short texts.
8

The SQL Dump file is attached
Manually created database “movedb” using phpmyadmin tool. So create database
command is commented out in the following script.
#
# CREATE DATABASE IF NOT EXISTS `movedb`;
# USE `movedb`;
#
# Table structure for table 'tblFacilities'
#
DROP TABLE IF EXISTS `tblFacilities`;
CREATE TABLE `tblFacilities` (
`FacilitiesID` VARCHAR(255) NOT NULL,
`Date` DATETIME,
`FacilitiesType` VARCHAR(255),
PRIMARY KEY (`FacilitiesID`)
) ENGINE=myisam DEFAULT CHARSET=utf8;
SET autocommit=1;
#
9

# Dumping data for table 'tblFacilities'
#
INSERT INTO `tblFacilities` (`FacilitiesID`, `Date`, `FacilitiesType`) VALUES
('Fac1', '2018-06-08 00:00:00', 'PoolTable');
# 1 records
#
# Table structure for table 'tblFacilityTypeAllottment'
#
DROP TABLE IF EXISTS `tblFacilityTypeAllottment`;
CREATE TABLE `tblFacilityTypeAllottment` (
`Date` DATETIME,
`FacilityID` VARCHAR(255),
`StudentID` VARCHAR(255),
INDEX (`StudentID`)
) ENGINE=myisam DEFAULT CHARSET=utf8;
SET autocommit=1;
#
# Dumping data for table 'tblFacilityTypeAllottment'
#
INSERT INTO `tblFacilityTypeAllottment` (`Date`, `FacilityID`, `StudentID`)
VALUES ('2018-06-09 00:00:00', 'Fac1', '1');
# 1 records
#
# Table structure for table 'tblPositions'
#
DROP TABLE IF EXISTS `tblPositions`;
CREATE TABLE `tblPositions` (
`PositionID` VARCHAR(255) NOT NULL,
`Date` DATETIME,
`PositionType` VARCHAR(255),
10

PRIMARY KEY (`PositionID`)
) ENGINE=myisam DEFAULT CHARSET=utf8;
SET autocommit=1;
#
# Dumping data for table 'tblPositions'
#
INSERT INTO `tblPositions` (`PositionID`, `Date`, `PositionType`) VALUES
('Pos1', '2018-06-08 00:00:00', 'Mentering');
# 1 records
#
# Table structure for table 'tblPositionsAllottment'
#
DROP TABLE IF EXISTS `tblPositionsAllottment`;
CREATE TABLE `tblPositionsAllottment` (
`Date` INTEGER AUTO_INCREMENT,
`PositionsID` VARCHAR(255),
`StudentID` VARCHAR(255),
INDEX (`PositionsID`),
INDEX (`StudentID`),
INDEX (`Date`)
) ENGINE=myisam DEFAULT CHARSET=utf8;
SET autocommit=1;
#
# Dumping data for table 'tblPositionsAllottment'
#
# 0 records
#
# Table structure for table 'tblStudents'
#
11

DROP TABLE IF EXISTS `tblStudents`;
CREATE TABLE `tblStudents` (
`StudentNo` VARCHAR(255) NOT NULL,
`GivenName` VARCHAR(255),
`FamilyName` VARCHAR(255),
`Phone No` INTEGER DEFAULT 0,
`Street No` VARCHAR(255),
`City` VARCHAR(255),
`State` VARCHAR(255),
`PostCode` VARCHAR(255),
`Faculty` VARCHAR(255),
`FoodStyle` VARCHAR(255),
INDEX (`PostCode`),
PRIMARY KEY (`StudentNo`)
) ENGINE=myisam DEFAULT CHARSET=utf8;
SET autocommit=1;
#
# Dumping data for table 'tblStudents'
#
INSERT INTO `tblStudents` (`StudentNo`, `GivenName`, `FamilyName`, `Phone
No`, `Street No`, `City`, `State`, `PostCode`, `Faculty`, `FoodStyle`) VALUES ('1',
'Peter', 'John', 887778, '12', 'MiddleSydney', 'Sydney', '343654', NULL, NULL);
# 1 records
The results are given below.
12

13

Relating tables
After creating the database to insert the related tables.
 To open your database and to select tools->relationships from your menu.
 Next to select the tables and click add button.
 After add the rows to click close button.
 Next to provide the one to many relation ship for your table. And save your table.
 Finally to close the relationship window.
14

15

Create a password:
1. First to click privileges tab. To view your list of student accounts.
2. Next to click the edit privilege icon.
3. To click change password section. Click go button.
4. To give your password.
16

Student Entry Form
17

2. Form to Director to Change the student details and jobs
18

3. Report to print student address details
The following query is created in SQL
SELECT tblStudents.GivenName, tblStudents.FamilyName, tblStudents.[Phone
No], tblStudents.[Street No], tblStudents.City, tblStudents.PostCode
FROM tblStudents;
The same query is shown in picture below.
19

20

4. Form to Director to Change the student details and Facilities
21

Wordpress
Rented a webspace and created wordpress (Wordpress.org, 2018)
Checked the wordpress config.php file
Got the following settings
// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'fastlabi_wo1');
22

/** MySQL database username */
define('DB_USER', 'fastlabi_wo1');
/** MySQL database password */
define('DB_PASSWORD', 'Z.isXQ3lBBuqeRCwEZt75');
/** MySQL hostname */
define('DB_HOST', 'localhost');
/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8');
/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');
The database name is : 'fastlabi_wo1'
The exisiting DB structure of the wordpress
23

Now database got IO details
24

Forms are created
Added NinjaForms plugin
Student details are inserted using NINJA Forms (WordPress.org, 2018)
Installed Exports and Reports plugin for showing reports in the wordpress (Sauter and Is, 2018).
SELECT queries are used to diplay the results.
4. Conclusion
25

The objective of the IO database is to collect the students details , collect the positions and
facilities deails and alotting the facilities and positions to the suitable students. The database that
satisfies all the objectives are created. PhpMyAdmin and Wordpress are used for database
operations and input/output operations using forms/reports. Suitable tables are selected and lined
with input forms. Suitable quieries are created to get the filtered results of the tables. The results
are displayed in the reports.
5. References
A Web Based Approach towards the Automated Generation of ER-Diagram.
(2015). International Journal of Science and Research (IJSR), 4(11), pp.1964-1966.
Al-Masree, H. (2015). Extracting Entity Relationship Diagram (ERD) From Relational Database
Schem. International Journal of Database Theory and Application, 8(3), pp.15-26.
contributors, p. (2018). phpMyAdmin. [online] phpMyAdmin. Available at:
https://www.phpmyadmin.net/ [Accessed 10 Jun. 2018].
Ict.griffith.edu.au. (2018). Home. [online] Available at:
http://www.ict.griffith.edu.au/~jw/normalization/ind.php [Accessed 10 Jun. 2018].
Ischool.utexas.edu. (2018). Overview of Normalization: Database-Management Principles and
Applications, LIS 384K.11, School of Information, The University of Texas at Austin.. [online]
Available at: https://www.ischool.utexas.edu/~wyllys/DMPAMaterials/normover.html
[Accessed 10 Jun. 2018].
Sauter, M. and Is, L. (2018). Exports and Reports. [online] WordPress.org. Available at:
https://wordpress.org/plugins/exports-and-reports/ [Accessed 10 Jun. 2018].
Shsu.edu. (2018). Normalization of Database Tables. [online] Available at:
http://www.shsu.edu/~csc_tjm/summer2000/cs334/Chapter05/Chapter5.html [Accessed 10 Jun.
2018].
SILBERSCHATZ, A. (2018). DATABASE SYSTEM CONCEPTS. [S.l.]: MCGRAW-HILL
EDUCATION.
Wordpress.org. (2018). Blog Tool, Publishing Platform, and CMS — WordPress. [online]
Available at: https://wordpress.org/ [Accessed 10 Jun. 2018].
26

WordPress.org. (2018). Ninja Forms – The Easy and Powerful Forms Builder. [online] Available
at: https://wordpress.org/plugins/ninja-forms/ [Accessed 10 Jun. 2018].
27