Database Modelling and Implementation for Desklib

Student details

Contents
1 Task 1 Database modelling.......................................................................................................................3
1.1 Entity relationship diagram..........................................................................................................3
1.2 Discovered business rules............................................................................................................3
1.3 3NF relations................................................................................................................................4
1.3.1 Students relation.................................................................................................................4
1.3.2 Course relation....................................................................................................................4
1.3.3 School relation.....................................................................................................................4
1.3.4 Faculty relation....................................................................................................................5
1.3.5 Enrollment relation.....................................................................................................................5
1.3.6 Facilities_services.......................................................................................................................5
1.3.7 Jobs relation................................................................................................................................6
1.3.8 Tasks relation..............................................................................................................................6
1.3.9 Roster.........................................................................................................................................6
1.4 Data Dictionary............................................................................................................................7
2 Task 2: Database implementation.......................................................................................................9
C) Forms and reports...............................................................................................................................9
3. References.............................................................................................................................................12

1 Task 1 Database modelling
1.1 Entity relationship diagram
Figure 1: ERD
1.2 Discovered business rules
While modelling the entity relationship diagram, the following business rules were discovered;
 A student enrolls for only one course at any given time thus the student cannot be taking two
courses at the time.
 A school has many courses for example school of computing can have courses like computer
science and data science.
 A faculty has many schools for example faculty of science can have school of computing and
school of engineering.
 Every faculty is headed by a dean whose name is recorded in the faculty relation.
 The lodge has different facilities and services which generate different jobs that are done by
volunteer students.

 A job can have more than one task required to be done and the task can be done by different
students.
1.3 3NF relations
The following set of relations were obtained from the entity relationship diagram after normalization
was done up to 3NF.
1.3.1 Students relation
The students relation is used to hold details of a student.
Students (studentNO, firstName, familyName, phoneNO, streetNO, city, state, postcode, type,
volunteer)
The relation is in 3NF because the following three conditions hold;
 1NF- No repeating groups in the relation. The primary key identified for the student’s relation is
student NO.
 2NF- No partial dependencies exist among any of it attributes. All the non key attributes are
dependent on the key attribute studentNO.
 3NF - the relation does not contain any transitive dependency between any of its attributes. This
is condition is true because all the non key attributes are determined by the primary key
studentNO so for example to get the first name of a student the studentNO is used to identify
that student.
1.3.2 Course relation
The course relation is sued to hold details of courses belonging to a certain school.
Course (courseID, name, duration, schoolID)
The course relation is in 3NF because the following conditions are true;
 1NF- No repeating groups in the relation. The primary key identified for the student’s relation is
courseID.
 2NF- No partial dependencies exist among any of it attributes. All the non key attributes are
dependent on the key attribute courseID.
 3NF - the relation does not contain any transitive dependency between any of its attributes. This
is condition is true because all the non key attributes are determined by the primary key
courseID.
1.3.3 School relation
The school relation is used to hold details of schools that belong to a certain faculty.
School (schoolID, name, facultyID)
The school relation is in 3NF because the following conditions hold;
 1NF- No repeating groups in the relation. The primary key identified for the student’s relation is
schoolID.