Database Modelling for International Office of Victoria University

This report discusses the design and the development of the International Office of Victoria University. The report presents the steps that are followed to design the database from coming up with an entity relationship diagram and then deriving entities to perform normalization. The entities achieved in 3NF are then used to create a data dictionary which is used in the implementation of the database. The database is then used to create forms and reports using wordpress.

Student details

Contents
1 Task 1 Database modelling.......................................................................................................................3
1.1 Entity relationship diagram..........................................................................................................3
1.2 Business rules..............................................................................................................................3
1.3 3NF relations................................................................................................................................3
1.4 Data Dictionary............................................................................................................................4
Bibliography................................................................................................................................................7
Appendix.....................................................................................................................................................7
Wordpress implementation...................................................................................................................10

Introduction
This report discusses the design and the development of the International Office of Victoria University.
The report presents the steps that are followed to design the database from coming up with an entity
relationship diagram and then deriving entities to perform normalization. The entities achieved in 3NF
are then used to create a data dictionary which is used in the implementation of the database. The
database is then used to create forms and reports using wordpress.

1 Task 1 Database modelling
1.1 Entity relationship diagram
Figure 1: ER Diagram
1.2 Business rules
 A student has one or more special diets although its not a must for a student to have a special
diet.
 A student is only supposed to enroll for one course
 A faculty has one or more schools
 A school offers one or more courses.
 The lodge offers one or more facilities and services to the students
 A facility or service at the lodge results to one or more jobs for the volunteering students.
 A student can volunteer for a job at the lodge. Its not a must for every student to volunteer for a
job at the lodge.
 Jobs in the lodge results to a roaster of the work to be done by the volunteering students
1.3 3NF relations
 Students (studentNO,firstName,familyName,phoneNO,streetNO,city,state,postcode,type)
 Volunteership (volunteershipID, rosterID, studentNO)
 Special_diet (dietID,studentNO,details)
 Course (courseID,courseName,duration,schoolID)

 Unit (unitID, unitName, courseID)
 School (schoolID,schoolName,facultyID)
 Faculty (facultyID,facultyName,dean)
 Enrollment (enrollmentID,courseID,studentNO,date)
 Facilities_and_services (fsID,name,type,description)
 Jobs (jobID,jobNname,details,fsID)
 Tasks (taskID,taskName,details,time,jobID)
 Roster ( rsID,taskID,studentNO,startDate,endDate,status)
All the relations are in 3NF because the following steps have been followed
 Normalization to 1NF- All the relations have been normalized to 1NF by removing any repeating
group in all the relations.
 Normalization to 2NF- All the relations have been normalized to 2NF by removing any partial
dependency existing in any of the relations. By eliminating the partial dependencies all relations
are left with only one candidate key and no combination of other attributes can form an
additional candidate key in any of the relations.
 Normalization to 3NF- All the relations are in 3NF because normalization to 3NF has been
achieved by removing all the transitive dependencies to leave all the relations with only key
attribute which determines all the other attributes in every relation.
1.4 Data Dictionary
Relation Attributes Meaning Data type Other
course courseID Course unique identifier INT Primary key
Unique
Auto-Increment
courseName Name identifying the course VARCHAR(50) Not Null
duration Duration of the course in
years
INT NOT NULL
For example “3’
schoolID Foreign key pointing to the
school that the course is in
INT Foreign key references
school (schoolID)
School schoolID School unique identifier INT primary key
Unique
Auto-Increment
schoolName Name identifying the school VARCHAR(50) Not Null
For example “School
of Engineering”

facultyID A foreign key used to identify
the faculty a school belongs
INT NOT NULL
foreign key
References faculty
(facultyID)
faculty facultyID Unique faculty identifier INT Primary key
Unique
Auto-Increment
facultyName The name identifying the
faculty for example faculty of
technology
VARCHAR(50) NOT NULL
For example “faculty
of education”
dean Name identifying the dean of
the department
VARCHAR(50) NOT NULL
Students studentNO Unique student identifier INT(8) Primary Key
Unique
firstName Student’s first name VARCHAR(50) Not Null
familyName Student’s second name VARCHAR(50) Not Null
phoneNO A student’s phone number VARCHAR(25) NOT NULL
streetNO Student’s street number VARCHAR(50) NOT NULL
City Student’s city or origin VARCHAR(50) NOT NULL
For example “Victoria”
State Student’s state of orin VARCHAR(50) NOT NULL
postcode Student’s postcode INT(8) Not Null
type Type of student whether full-
time or part-time
VARCHAR(25) NOT NULL
For xample “part-
time”
Special_diet dietID Unique diet identifier INT primary key
Unique
studnetNO Foreign key identifying the
specific student in need of the
special diet
INT foreign key references
students (studentID)
description Details of the special diet
task taskID Task unique identifier INT primary key
Unique
taskName Name identifying the task VARCHAR(50) NOT NULL
description Details of the task TEXT NULL
Time_interval The time between two tasks
i.e end of one task and end of
the other task
VARCHAR(25) NOT NULL
For example “daily”
roster rID Unique roster identifier INT primary key Unique
Auto-increment
taskID Foreign key pointing to the
specific task
INT Foreign key references
task (taskID)
studentNO Foreign key identifying the
student doing the task
INT(8) foreign key references
students (studentID)
startDate Start date and time of the task VARCHAR(25) NOT NULL
endDate Date and time of ending for VARCHAR(25) NOT NULL

the task
status Status of completion of the
task
VARCHAR(10) NOT NULL
For example
“complete”
Enrollment enrollmentID Enrollment unique identifier INT Primary key
Unique
Auto-Increment
courseID Foreign key identifying the
specific course a student is
enrolling for.
INT Foreign key references
course (courseID)
StudentNO Foreign key identifying the
specific student eenrolling for
the course
INT Foreign key references
students (studentNO)
date Date of enrollment Timestamp NOT NULL
Current_timestamp
Facility_and_services facOrSerID Facility or service uniue
identifier
INT primary key
Unique
name Name identifying the facility
or service at the lodge
VARCHAR(50) NOT NULL
type Type; facility or service VARCHAR(25) NOT NULL
Example “service”
description Details of the service or
facility
TEXT NULL
Jobs JobID Unique job identifier INT Primary key
Unique
Auto-Increment
name Name identifying the job VARCHAR(50) NOT NULL
description Details of the job TEXT NULL
facOrSerID Foreign key identifying the
facility or service in the lodge
INT NOT NULL
Constraint foreign key
references
facility_and_services
(facOrSerID)
volunteership volunteershipID Unique volunteer ship
indetifier
INT Primary key
rosterID Foreign key identifying the
specific roster
INT Constraint foreign key
references roster
(rosterID)
studentNO Foreign key identifying the
specific student
INT Constraint foreign key
references roster
(rosterID)
units unitID Unique unit identifier INT Primary key
unitName Name identifying the unit VARCHAR(50) MANDATORY
courseID Foreign key identifying the
specific course
INT Foreign key references
course (courseID)

Bibliography
Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems (3rd Edition ed.). New York:
McGraw-Hill Education.
Appendix
Tables in PhpMyAdmin
 Course table
The following screenshot shows the implementation of the course table in PhpMyAdmin
 Enrollment table
The following screenshot shows the implementation of the enrollment table in PhpMyAdmin
 Facilities_services table
The following screenshot shows the implementation of the facilities_services table in
PhpMyAdmin
 faculty table
The following screenshot shows the implementation of the facullty table in PhpMyAdmin

 Jobs table
The following screenshot shows the implementation of the jobs table in PhpMyAdmin
 roster table
The following screenshot shows the implementation of the roster table in PhpMyAdmin
 school table
The following screenshot shows the implementation of the school table in PhpMyAdmin

 special_diet table
The following screenshot shows the implementation of the special_diet table in PhpMyAdmin
 Students table
The following screenshot shows the implementation of the students table in PhpMyAdmin
 tasks table
The following screenshot shows the implementation of the tasks table in PhpMyAdmin

Wordpress implementation
C. Design the following three forms and two reports, requiring a separate query to assure every of the
following information needs:
1. A Student information entry form
2. A form to allow the director to change Member details and update the job and roster information
without losing data integrity
3. A report that prints the students address details

4. A form for the director to fill in the jobs for the up-coming week with the volunteers available
5. A report that lists all of the people with particular dietary requirements so that this can be given to
the chef before social functions