Database Project: Designing and Implementing a Relational Database

This project involves the design and implementation of a database, starting with the creation of an Entity Relationship Diagram (ERD) based on provided business rules for an IO (presumably an institution) environment. The ERD is then used to derive 3NF (Third Normal Form) relations, ensuring data integrity and minimizing redundancy. A data dictionary is created to define the attributes and their properties for each relation. The database is then implemented using phpMyAdmin, with SQL queries provided for creating tables and defining relationships. The tables are populated with sample data, and the project also includes the implementation of WordPress forms and reports for data interaction. The database encompasses entities such as students, courses, faculties, schools, jobs, and facilities, reflecting the operational aspects of the IO environment. Desklib provides students with access to this project and numerous other solved assignments and past papers to aid in their studies.

COVER PAGE

Contents
1 Task 1........................................................................................................................................................3
1.1 ERD..............................................................................................................................................3
1.2 Business rules..............................................................................................................................3
1.3 3NF relations................................................................................................................................3
1.4 Data Dictionary............................................................................................................................6
Task 2..........................................................................................................................................................9
PhPMyAdmin implementation....................................................................................................................9
Populating the tables with data.............................................................................................................20
Wordpress forms and report implementation......................................................................................27

1 Task 1
1.1 ERD
Figure 1: Entity relationship diagram
1.2 Business rules
Business rule derived from IO background.
 IO is made of many faculties and each faculty consists of one or more schools.
 Every school in Io offers one or more courses.
 Every student enrolls for one and only one course and a student can either be a full-time
student or part-time student.
 IO has a lodge where students can meet for socializing. The lodge offers different facilities and
services to the students.
 The facilities and services offered at the lodge may result into one or more jobs.
 During enrollment a student can choose to volunteer for the jobs at the lodge or not.
Volunteering is optional for all students. Only interested students volunteer for the jobs at the
lodge.
 Some students require a special diet at the lodge. It’s not a must for a student to have a special
diet.
1.3 3NF relations
 Students (studentNO, firstName, familyName, phoneNO, streetNO, city, state, postcode, type)
The relation is in 3NF because;
1NF- All repeating groups have been eliminated.
2NF- All partial dependencies have been eliminated. StudentNO is the only candidate key and no
other combination of attributes can form a candidate key.

3NF- All transitive dependencies have been eliminated. StudentNO is the only key attribute
which determines all the other attributes.
 Roster ( rsID, taskID, studentNO, startDate, endDate, status)
The relation is in 3NF because;
1NF- All repeating groups have been eliminated.
2NF- All partial dependencies have been eliminated. rsID is the only candidate key and no other
combination of attributes can form a candidate key.
3NF- All transitive dependencies have been eliminated. rsID is the only key attribute which
determines all the other attributes.
 Volunteership (volunteershipID,rosterID,studentNO)
The relation is in 3NF because;
1NF- All repeating groups have been eliminated.
2NF- All partial dependencies have been eliminated. volunteershipID is the only candidate key
and no other combination of attributes can form a candidate key.
3NF- All transitive dependencies have been eliminated. volunteershipID is the only key attribute
which determines all the other attributes.
 Tasks (taskID, taskName, details, time, jobID)
The relation is in 3NF because;
1NF- All repeating groups have been eliminated.
2NF- All partial dependencies have been eliminated. taskID is the only candidate key and no
other combination of attributes can form a candidate key.
3NF- All transitive dependencies have been eliminated. taskID is the only key attribute which
determines all the other attributes.
 Special_diet (dietID, studentNO, details)
The relation is in 3NF because;
1NF- All repeating groups have been eliminated.
2NF- All partial dependencies have been eliminated. dietID is the only candidate key and no
other combination of attributes can form a candidate key.
3NF- All transitive dependencies have been eliminated. dietID is the only key attribute which
determines all the other attributes.
 Jobs (jobID, jobNname, details, fsID)
The relation is in 3NF because;
1NF- All repeating groups have been eliminated.
2NF- All partial dependencies have been eliminated. jobID is the only candidate key and no
other combination of attributes can form a candidate key.
3NF- All transitive dependencies have been eliminated. jobID is the only key attribute which
determines all the other attributes.

 Course (courseID, courseName, duration, schoolID)
The relation is in 3NF because;
1NF- All repeating groups have been eliminated.
2NF- All partial dependencies have been eliminated. courseID is the only candidate key and no
other combination of attributes can form a candidate key.
3NF- All transitive dependencies have been eliminated. courseID is the only key attribute which
determines all the other attributes.
 Unit (unitID,unitName,courseID)
The relation is in 3NF because;
1NF- All repeating groups have been eliminated.
2NF- All partial dependencies have been eliminated. unitID is the only candidate key and no
other combination of attributes can form a candidate key.
3NF- All transitive dependencies have been eliminated. unitID is the only key attribute which
determines all the other attributes.
 Enrollment (enrollmentID, courseID, studentNO, date)
The relation is in 3NF because;
1NF- All repeating groups have been eliminated.
2NF- All partial dependencies have been eliminated. enrollmentID is the only candidate key and
no other combination of attributes can form a candidate key.
3NF- All transitive dependencies have been eliminated. enrollmentID is the only key attribute
which determines all the other attributes.
 Facilities_and_services (fsID, name, type, description)
The relation is in 3NF because;
1NF- All repeating groups have been eliminated.
2NF- All partial dependencies have been eliminated. fsID is the only candidate key and no other
combination of attributes can form a candidate key.
3NF- All transitive dependencies have been eliminated. fsID is the only key attribute which
determines all the other attributes.
 Faculty (facultyID, facultyName, dean)
The relation is in 3NF because;
1NF- All repeating groups have been eliminated.
2NF- All partial dependencies have been eliminated. facultyID is the only candidate key and no
other combination of attributes can form a candidate key.
3NF- All transitive dependencies have been eliminated. facultyID is the only key attribute which
determines all the other attributes.

 School (schoolID, schoolName, facultyID)
The relation is in 3NF because;
1NF- All repeating groups have been eliminated.
2NF- All partial dependencies have been eliminated. schoolID is the only candidate key and no
other combination of attributes can form a candidate key.
3NF- All transitive dependencies have been eliminated. schoolID is the only key attribute which
determines all the other attributes.
1.4 Data Dictionary
Relation Attributes Meaning Data type Other
faculty facultyID facultyID is unique for every
faculty and is used to identify
a faculty
INT Primary key
Auto-Increment
facultyName Name of the faculty e.g.
faculty of computing
VARCHAR(50) NOT NULL
eg “faculty of science”
dean Dean of the faculty VARCHAR(50) NOT NULL
units unitID The unique identifier of the
unit
INT Primary key
unitName The name of the unit VARCHAR(50) MANDATORY
courseID Identifier of the course the
unit belongs to
INT Foreign key references
course.courseID
course courseID CourseID is unique for evey
course and identifies a course
INT Primary key
Auto-Increment
courseName Name of the course VARCHAR(50) Not Null
duration Years the course will take INT NOT NULL
For example “4”
schoolID Identifier of the school the
course belongs to
INT Foreign key references
school .schoolID
School schoolID schoolID is unique for every
course and is used to identify
a school
INT primary key
Auto-Increment
schoolName Name of the school VARCHAR(50) Not Null
For example “School
of engineering”
facultyID Identifier of the faculty the
school belongs to
INT NOT NULL
foreign key
References
faculty.facultyID
task taskID The unique identifier of the
task
INT primary key
taskName The name of the task VARCHAR(50) NOT NULL
description The description of the task in
details
TEXT NULL
Time_interval The interval between two VARCHAR(25) NOT NULL

subsequent tasks eg “daily”
Students studentNO studentNO is the unique
identifier of the student
INT(8) Primary Key
firstName First name of the student VARCHAR(50) Not Null
familyName Family name given to the
student
VARCHAR(50) Not Null
phoneNO The personal phone number
of the student
VARCHAR(25) NOT NULL
streetNO The street that the student
stays
VARCHAR(50) NOT NULL
City The city the student comes
from
VARCHAR(50) NOT NULL
eg “Melbourne”
State The state the student comes
from
VARCHAR(50) NOT NULL
postcode The postcode of the student INT(8) Not Null
type The type of the student i.e.
part-time enrollment student
or full-time enrollment
student
VARCHAR(25) NOT NULL eg “full-
time”
Special_diet dietID The unique identifier of the
special diet
INT primary key
studnetNO Identifier of the student
taking the special diet
INT foreign key references
students.studentID
description The description of the special
diet in details
TEXT NOT NULL
Jobs JobID The unique identifier of a job INT Primary key
Auto-Increment
name The name of a job VARCHAR(50) NOT NULL
description The description in details of a
job
TEXT NULL
facOrSerID Identifier of the facility or
service
INT NOT NULL
foreign key references
facility_and_services.
facOrSerID
roster rID The unique identifier of the
roster
INT primary key
Auto-increment
taskID Identifier of the task INT Foreign key references
task.taskID
studentNO Identifier of the student INT(8) foreign key references
students.studentID
startDate The starting date of the task VARCHAR(25) NOT NULL
endDate The ending date of the task VARCHAR(25) NOT NULL
status The status of the task whether
complete or not
VARCHAR(10) NOT NULL
Eg “complete”
Enrollment enrollmentID The unique identifier of the
enrollment
INT Primary key
Auto-Increment

courseID Identifier of the course being
enrolled for
INT Foreign key references
course.courseID
StudentNO Identifier of the student
enrolling for the course
INT Foreign key references
students.studentNO
date The date the enrollment was
done.
Timestamp NOT NULL
Current_timestamp
Facility_and_services facOrSerID The unique identifier of the
facility or service
INT primary key
name The name of the facility or
service
VARCHAR(50) NOT NULL
type The type; either facility or
servce
VARCHAR(25) NOT NULL
Eg “service”
description The description in details of
the facility or service
TEXT NULL
volunteership volunteershipID The unique identifier of the
volunteership
INT Primary key
rosterID Identifier of the roster for
which the volunteership is
taking place
INT foreign key references
roster.rosterID
studentNO Identifier of the student
volunteering
INT foreign key references
roster.rosterID
Task 2
PhPMyAdmin implementation
The SQL queries used to create the database;

-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Jun 09, 2018 at 07:28 AM
-- Server version: 5.6.21
-- PHP Version: 5.6.3
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `iodb`
--
CREATE DATABASE IF NOT EXISTS `iodb` DEFAULT CHARACTER SET latin1
COLLATE latin1_swedish_ci;
USE `iodb`;
-- --------------------------------------------------------
--
-- Table structure for table `course`
--

CREATE TABLE IF NOT EXISTS `course` (
`coureID` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`duration` int(11) NOT NULL,
`schoolID` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `enrollment`
--
CREATE TABLE IF NOT EXISTS `enrollment` (
`enrollmentID` int(11) NOT NULL,
`courseID` int(11) NOT NULL,
`studentNO` int(8) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `facilities_services`
--
CREATE TABLE IF NOT EXISTS `facilities_services` (
`fsID` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`type` varchar(25) NOT NULL,

`descrption` text
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `faculty`
--
CREATE TABLE IF NOT EXISTS `faculty` (
`facultyID` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`dean` varchar(50) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `jobs`
--
CREATE TABLE IF NOT EXISTS `jobs` (
`jobID` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`description` text NOT NULL,
`fsID` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
-- --------------------------------------------------------

--
-- Table structure for table `roster`
--
CREATE TABLE IF NOT EXISTS `roster` (
`rsID` int(11) NOT NULL,
`taskID` int(11) NOT NULL,
`studentNO` int(8) NOT NULL,
`date_time_from` varchar(50) NOT NULL,
`date_time_to` varchar(50) NOT NULL,
`status` varchar(25) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `school`
--
CREATE TABLE IF NOT EXISTS `school` (
`schoolID` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`facultyID` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `special_diet`
--