Database Design Process for Victoria University Accommodation Department

This report presents a discussion on the database design process for the proposed database system intended for Victoria University accommodation department. The database system is supposed to help the university to keep track and maintain data in an efficient way. The design process follows the standard top-down approach of designing the database.

COVER PAGE

Contents
Introduction.................................................................................................................................................3
1 Task 1........................................................................................................................................................3
1.1 ERD..............................................................................................................................................3
1.2 Business rules..............................................................................................................................3
1.3 3NF relations................................................................................................................................4
1.4 Data Dictionary............................................................................................................................7
Task 2........................................................................................................................................................10
PhPMyAdmin implementation..................................................................................................................10
Wordpress forms and reports...............................................................................................................13
Conclusion.................................................................................................................................................15

Introduction
This report presents a discussion on the database design process for the proposed database system
intended for Victoria University accommodation department. The database system is supposed to help
the university to keep track and maintain data in an efficient way. The design process follows the
standard top-down approach of designing the database.
1 Task 1
1.1 ERD
Figure 1: Entity relationship diagram
1.2 Business rules
 The university consists of many faculties

 A faculty consists of one or many schools
 A school offers one or more courses
 A student enrolls for only one course at a time thus for very enrollment is associated with one
and only one course.
 A student enrollment is either full-time or part-time
 There is only one lodge in the university
 The lodge offers different facilities and services
 The services or facilities in the university results to many jobs
 Student can volunteer for a job. Volunteering by a student is not mandatory
 One or more students can have a special diet
1.3 3NF relations
Students (studentNO,firstName,familyName,phoneNO,streetNO,city,state,postcode,type)
Justification for normalization up to 3NF;
1NF
No repeating groups
2NF
No partial dependencies thus only one candidate key on which all the other attributes depend
on.
3NF
No transitive dependencies thus only one key attribute determines all the other attributes.
Roster ( rsID,taskID,studentNO,startDate,endDate,status)
Justification for normalization up to 3NF;
1NF
No repeating groups
2NF
No partial dependencies thus only one candidate key on which all the other attributes depend
on.
3NF
No transitive dependencies thus only one key attribute determines all the other attributes.
Volunteership (volunteershipID,rosterID,studentNO)
Justification for normalization up to 3NF;
1NF
No repeating groups
2NF
No partial dependencies thus only one candidate key on which all the other attributes depend
on.
3NF
No transitive dependencies thus only one key attribute determines all the other attributes.
Tasks (taskID,taskName,details,time,jobID)
Justification for normalization up to 3NF;
1NF

No repeating groups
2NF
No partial dependencies thus only one candidate key on which all the other attributes depend
on.
3NF
No transitive dependencies thus only one key attribute determines all the other attributes.
Special_diet (dietID,studentNO,details)
Justification for normalization up to 3NF;
1NF
No repeating groups
2NF
No partial dependencies thus only one candidate key on which all the other attributes depend
on.
3NF
No transitive dependencies thus only one key attribute determines all the other attributes.
Jobs (jobID,jobNname,details,fsID)
Justification for normalization up to 3NF;
1NF
No repeating groups
2NF
No partial dependencies thus only one candidate key on which all the other attributes depend
on.
3NF
No transitive dependencies thus only one key attribute determines all the other attributes.
Course (courseID,courseName,duration,schoolID)
Justification for normalization up to 3NF;
1NF
No repeating groups
2NF
No partial dependencies thus only one candidate key on which all the other attributes depend
on.
3NF
No transitive dependencies thus only one key attribute determines all the other attributes.
Unit (unitID,unitName,courseID)
Justification for normalization up to 3NF;
1NF
No repeating groups
2NF

No partial dependencies thus only one candidate key on which all the other attributes depend
on.
3NF
No transitive dependencies thus only one key attribute determines all the other attributes.
Enrollment (enrollmentID, courseID, studentNO, date)
Justification for normalization up to 3NF;
1NF
No repeating groups
2NF
No partial dependencies thus only one candidate key on which all the other attributes depend
on.
3NF
No transitive dependencies thus only one key attribute determines all the other attributes.
Facilities_and_services (fsID, name, type, description)
Justification for normalization up to 3NF;
1NF
No repeating groups
2NF
No partial dependencies thus only one candidate key on which all the other attributes depend
on.
3NF
No transitive dependencies thus only one key attribute determines all the other attributes.
Faculty (facultyID, facultyName, dean)
Justification for normalization up to 3NF;
1NF
No repeating groups
2NF
No partial dependencies thus only one candidate key on which all the other attributes depend
on.
3NF
No transitive dependencies thus only one key attribute determines all the other attributes.
School (schoolID, schoolName, facultyID)
Justification for normalization up to 3NF;
1NF
No repeating groups
2NF
No partial dependencies thus only one candidate key on which all the other attributes depend
on.
3NF
No transitive dependencies thus only one key attribute determines all the other attributes.

1.4 Data Dictionary
Relation Attributes Meaning Data type Other
Students studentNO Primary key of a student INT(8) Primary Key
firstName First name identifying a
student
VARCHAR(50) Not Null
familyName Family name identifying a
student
VARCHAR(50) Not Null
phoneNO Phone number of a student VARCHAR(25) NOT NULL
streetNO Street number of student
address
VARCHAR(50) NOT NULL
City City a student is located VARCHAR(50) NOT NULL
eg “Melbourne”
State The state of student address VARCHAR(50) NOT NULL
postcode Postcode of student address INT(8) Not Null
type Type of student; part time or
full time
VARCHAR(25) NOT NULL eg “full-
time”
Enrollment enrollmentID Primary key of an enrollment INT Primary key
courseID Foreign key referencing the
course table
INT Foreign key references
course.courseID
StudentNO Foreign key referencing the
student table
INT Foreign key references
students.studentNO
date The actual date and time of
the enrollment
Timestamp NOT NULL
Current_timestamp
faculty facultyID Primary key of a faculty INT Primary key
facultyName Name identifying the faculty VARCHAR(50) NOT NULL
eg “faculty of
business”
dean Dean heading the faculty VARCHAR(50) NOT NULL
units unitID Primary key of a unit INT Primary key
unitName Name given to a unit VARCHAR(50) MANDATORY
courseID Foreign key identifier of the
course
INT Foreign key references
course.courseID
course courseID Unique key of a course INT Primary key
courseName Name given to a course VARCHAR(50) Not Null
duration Duration in years a course will
take
INT NOT NULL
For example “4”
schoolID Foreign key identifier of the
school
INT Foreign key references
school .schoolID
School schoolID Primary key of a school INT primary key
schoolName Name given to a school VARCHAR(50) Not Null
For example “School

of engineering”
facultyID Foreign key identifier of a
faculty
INT NOT NULL
foreign key
References
faculty.facultyID
task taskID Primary key of a task INT primary key
taskName Name given to a taks VARCHAR(50) NOT NULL
description Description of a task TEXT NULL
Time_interval Time between two tasks VARCHAR(25) NOT NULL
eg “daily”
Special_diet dietID Unique key of a student INT primary key
studnetNO Foreign key identifier of a
student
INT foreign key references
students.studentID
description Description of the special diet TEXT NOT NULL
Jobs JobID Unique key of a student INT Primary key
name Name given to a student VARCHAR(50) NOT NULL
description Description of a job TEXT NULL
facOrSerID Foreign key identifying the
lodge facility or service
INT NOT NULL
foreign key references
facility_and_services.
facOrSerID
Lodge_services facOrSerID Unique key of a service or
facility
INT primary key
name Name given to a lodge facility
or service
VARCHAR(50) NOT NULL
type Facility or service VARCHAR(25) NOT NULL
Eg “service”
description Description given to service or
facility
TEXT NULL
roster rID Unique key of a roster INT primary key
taskID Foreign key identifier of a task INT Foreign key references
task.taskID
studentNO Foreign key identifier of the
student
INT(8) foreign key references
students.studentID
startDate The date the roster starts VARCHAR(25) NOT NULL
endDate The date the roster ends VARCHAR(25) NOT NULL
status Status of the roster; can be
complete or incomplete
VARCHAR(10) NOT NULL
Eg “complete”
volunteership volunteershipID Unique key of a volunteership INT Primary key
rosterID Foreign key identifier of the
rsoter
INT foreign key references
roster.rosterID
studentNO Foreign key identifier of the
roster
INT foreign key references
roster.rosterID

Task 2
PhPMyAdmin implementation
Data

Course
Enrollment
Facilities_services
Faculty
Jobs

Roster
School
Special diet
Students
Tasks

Wordpress forms and reports
Special diets
Student address details

Add student form

Fill a job form
Update roaster form
Conclusion
The database achieved from the design process described in the sections above will help the university
to record and manage data in an efficient way thus providing an alternative method to the manual
methods which are inefficient and difficult to analyze.