Faculty
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.
- 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.
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 subsequent tasks |
VARCHAR(25) |
NOT NULL 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 |