NIT5130 Database Analysis and Design Project: VU IO Database

This project outlines the development of a database system for Victoria University's International Office (IO), designed to streamline data entry and reporting. The database, built using phpMyAdmin, focuses on collecting student details, positions, and facilities information, and allotting facilities and positions to students. The implementation includes five tables (tblStudents, tblPositionsAllottment, tblFacilityTypeAllottment, tblFacilities, and tblPositions) with defined relationships and normalization to 3NF. WordPress is integrated for creating user-friendly forms for data input and generating reports, utilizing plugins like Ninja Forms and Exports and Reports. The project includes SQL dump files for database creation, password creation steps, and sample forms and reports, demonstrating the practical application of database concepts to meet the IO's needs for efficient data management and reporting.

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