Contents1 Task 1 Database modelling.......................................................................................................................31.1Entity relationship diagram..........................................................................................................31.2Discovered business rules............................................................................................................31.33NF relations................................................................................................................................41.3.1Students relation.................................................................................................................41.3.2Course relation....................................................................................................................41.3.3School relation.....................................................................................................................41.3.4Faculty relation....................................................................................................................51.3.5 Enrollment relation.....................................................................................................................51.3.6 Facilities_services.......................................................................................................................51.3.7 Jobs relation................................................................................................................................61.3.8 Tasks relation..............................................................................................................................61.3.9 Roster.........................................................................................................................................61.4Data Dictionary............................................................................................................................72Task 2: Database implementation.......................................................................................................9C) Forms and reports...............................................................................................................................93. References.............................................................................................................................................12
1 Task 1 Database modelling1.1Entity relationship diagramFigure 1: ERD1.2Discovered business rulesWhile 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.33NF relationsThe following set of relations were obtained from the entity relationship diagram after normalization was done up to 3NF.1.3.1Students relationThe 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. Thisis 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.2Course relationThe 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. Thisis condition is true because all the non key attributes are determined by the primary key courseID.1.3.3School relationThe 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.
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Database Modelling and Implementation for Deskliblg...
|14
|2041
|343
Database Modelling for International Office of Victoria Universitylg...
|13
|1540
|390
Database Design Process for Victoria University Accommodation Departmentlg...
|15
|1568
|291
ERD, Business Rules, 3NF Relations and Data Dictionary | Deskliblg...