Design And Development Of International Office Database – 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.

1.1 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.2 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.3 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 the task	VARCHAR(25)	NOT NULL
	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)