ERD, Business Rules, 3NF Relations and Data Dictionary | Desklib

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