IO Student Registration And Job Management

NIT5130: Database Analysis and Design

Assignment Report

1|P a g e
IO Student Registration And Job Management

TABLE OF CONTENTS
Database Modelling................................................................................................................................................ 3
Entity Relationship Diagram (ERD) ............................................................................................................................3
Business Rules ...........................................................................................................................................................4
Mapped Relations .....................................................................................................................................................4
DATA DICTIONARY .....................................................................................................................................................6
Database Design and Associated Tasks ................................................................................................................ 10
Design ......................................................................................................................................................................10
System capabilities .............................................................................................................................................. 11
Student Registration Form ......................................................................................................................................11
Job Listing and Assignment .....................................................................................................................................15
Member Details Modification .................................................................................................................................18
Report – Student Address Details ...........................................................................................................................23
Dietary Requirements .............................................................................................................................................24
Associated Materials ........................................................................................................................................... 25
References ........................................................................................................................................................... 31
Appendix A .......................................................................................................................................................... 32
Declaration Form .....................................................................................................................................................32
Students’ Declaration ..............................................................................................................................................32

2|P a g e
IO Student Registration And Job Management

DATABASE MODELLING

ENTITY RELATIONSHIP DIAGRAM (ERD)

This task involved creating an ERD from the description specified in the assignment. To achieve this task
we had to create several entities that were directly required. Some other entities had to be created that
would help relate the direct entities. After careful consideration of each task to be achieved, the following
ERD was generated. The relation between any two given entities has been labelled with the connectors.
The connectors depict how each entity or relation is in relation to other entities or relations. The primary
keys have been underlined and are the first attribute in each section and references have been denoted
as italics.

From the ERD, we can see how all the important are correlated. The relation between each entity is
defined on the connector. It specifies how each entity is in relation with the connected entity. The
connectors also suggested a “ONE TO MANY”,”ONE TO ONE”, and “MANY TO ONE” relations.

In the following section we have defined the business rules that will help you translate the ER Diagram
into business rules.

3|P a g e
IO Student Registration And Job Management

BUSINESS RULES

I. One Faculty houses many schools

II. Many or no Schools are housed under a Faculty
III. One School houses many Students
IV. Many Students belong to a School
V. Every Student is a User
VI. Many or no Students are classified under a Nutrition Category
VII. Every Nutrient Category classifies zero or more Students
VIII. Every User performs zero or more Tasks (recorded as TaskRecord)
IX. Every Task performed is recorded as TaskRecord
X. Every Job is divided as Tasks
XI. Every Task is a sub-division of a Job
XII. Every Job performed is recorded as a JobRecord
XIII. Every JobRecord records a Job
XIV. Every JobRecord has a JobStatus
XV. Every Job falls under a Facility
XVI. Every Facility has zero or more Jobs

MAPPED RELATIONS

 Facility(FacilityNumber,FacilityName,FacilityDescription) – All single value attributes depend

directly and wholly on the primary key - FacililtyNumber
 Task(TaskID,TaskName,TaskDesc,TaskFrequency,TaskDuration,JobID) - All single value attributes
depend directly and wholly on the primary key - TaskID
 Faculty(FacultyID,FacultyName) - All single value attributes depend directly and wholly on the
primary key - FacultyID
 School(SchoolID,SchoolName,FacultyID) - All single value attributes depend directly and wholly
on the primary key - SchoolID
 TaskRecord(TaskRecordID,TaskStartTime,TaskEndTime,IsTaskComplete,UserID,TaskID) - All single
value attributes depend directly and wholly on the primary key - TaskRecordID
 Job(JobID,JobName,JobDesc,FacilityNumber) - All single value attributes depend directly and
wholly on the primary key - JobID
 User(UserID,GivenName,FamilyName,PhoneNo,HouseNum,StreetName,City,State,PostCode,IsSt
udent,Email) - All single value attributes depend directly and wholly on the primary key - UserID
 JobStatus(JobStatusID,JobStatusDesc) - All single value attributes depend directly and wholly on
the primary key - JobStatusID
 NutritionCategory(NutrientCatID,CategoryName,IsVegan,HasAllergies,HasDiabetes) - All single
value attributes depend directly and wholly on the primary key - NutrientCatID

4|P a g e
IO Student Registration And Job Management

 Student(StudentNum,WantVolunteer,UserID,SchoolID,NutrientCatID) - All single value attributes

depend directly and wholly on the primary key - StudentID
 JobRecord(JobRecordID,JobStartTime,JobEndTime,JobID,JobStatusID) - All single value attributes
depend directly and wholly on the primary key - JobRecordID

Following are the rules for any relation to be classified as 3NF :

o None of the non-key attributes should be dependent on any other non-key attributes, i.e. any
attribute should be identified uniquely only through the primary key of the table
o No repeating attributes
o No multi-value attributes

All the relations specified above satisfy these conditions and hence are all in 3NF

5|P a g e
IO Student Registration And Job Management

DATA DICTIONARY

Now we arrive to probably the most important part of this report. The data dictionary here enlists all the
attributes that are used in our database. The four key columns that are specified here are used to
elaborate on each and every attribute.

Note – The datatype here are not specific to any particular database but are generic terms for better
readability.

Attributes Meaning Data Example /

Type Information
Facility
FacilityNumber Unique Identifier for Integer(3) Primary Key, Auto-
Facilities Increment
FacilityName Name of the facility Text(20) “Swimming Pool”
FacilityDescription Description of the facility Text(250) “Place to swim”
Task
TaskID Unique Identifier for Tasks Integer(5) Primary Key, Auto-
Increment
TaskName Name of task Text(30) “Clean pool side”
TaskDesc Description of task Text(250) “This task involves cleaning
the borders of the swimming
pool”
TaskFrequency How often the task needs Text(7) Weekly, Daily, Biweekly
to be performed
TaskDuration Estimated time taken in Integer(3) 30,60
minutes to perform a task
JobID Reference to which Job is Integer(5) References (Job)
this task part of
Faculty
FacultyID Unique Identifier for each Integer(2) Primary Key, Auto-
Faculty Increment
FacultyName Name of the Faculty Text(30) “Faculty of Technology”
School
SchoolID Unique Identifier for each Integer(2) Primary Key, Auto-
School Increment
SchoolName Name of school Text(30) “School of Arts”

6|P a g e
IO Student Registration And Job Management

FacultyID Reference of Faculty Integer(2) References (Faculty)

TaskRecord
TaskRecordID Unique Identifier to record Integer(10) Primary Key, Auto-
each task done Increment
TaskStartTime Time when a task was DateTime DDMMYY-HH:MM:SS
started
TaskEndTime Time when a task was DateTime DDMMYY-HH:MM:SS
completed
IsTaskComplete Flag to denote task Boolean True or False
completion
UserID Reference to the user Integer (10) xxxxxxxxxx
assigned this task
TaskID Reference to the task that Integer (5) xxxxx
is to be done and that will
be recorded here
Job
JobID Unique Identifier for each Integer(5) Primary Key, Auto-
Job Increment
JobName Name of the job Text (30) “Pool Maintenance”
JobDesc Description of the job Text (150) “Job involves all
maintenance activity of the
swimming pool”
FacilityNumber Reference to the facility Integer (3) xxx
for which this job is
created
User
UserID Unique Identifier for every Integer(10) Primary Key, Auto-
User Increment
GivenName First Name of the user Text (20) “John”
FamilyName Last Name of the user Text (20) “Smart”
PhoneNo Contact number Integer(10) xxxxxxxxx
Email Email ID Text (50) “xyz@abc.com”
HouseNum Part of address Integer(4) 11
StreetName Part of address Text (20) “South Street”
City Part of address Text (20) “Adelaide”
State Part of address Text (20) “South Australia”
PostCode Part of address Integer(4) 5000

7|P a g e
IO Student Registration And Job Management

IsStudent Flag to denote if the user is Boolean True or False

a student or director or
any other user
JobStatus
JobStatusID Unique Identifier for every Integer (2) Primary Key, Auto-
JobStatus Increment
JobStatusDesc Description for each Job Text (50) “Ongoing”,”Delayed”, etc
status
NutritionCategory
NutrientCatID Unique Identifier for Integer (2) Primary Key, Auto-
Nutrient Category Increment
CategoryName Name of the nutrient Text (20) “Pure Vegan”, ”Allergic”
category
IsVegan Flag to indicate vegan or Boolean True or False
not
HasAllergies Flag to Indicate if there is Boolean True or False
any chance of allergy
HasDiabetes Flag to indicate if diabetic Boolean True or False
Student
StudentNum Unique Identifier for every Integer (10) Primary Key, Auto-
Student Increment
WantVolunteer Flag to indicate if the Boolean True or False
student wants to
volunteer
UserID Reference to the user id Integer (10) xxxxxxxxxx
SchoolID Reference to the school to Integer (2) xx
which the student belongs
NutrientCatID Reference to the nutrient Integer (2) xx
requirements of the
student
JobRecord
JobRecordID Unique Identifier to record Integer (10) Primary Key, Auto-
every Job Increment
JobStartTime Time when a job was DateTime DDMMYY-HH:MM:SS
started
JobEndTime Time when a job was ateTime DDMMYY-HH:MM:SS
completed
JobID Reference to the job being Integer (5) xxxxx
recorded here

8|P a g e
IO Student Registration And Job Management

JobStatusID Current Status of the job Integer (2) xx

9|P a g e
IO Student Registration And Job Management

DATABASE DESIGN AND ASSOCIATED TASKS

DESIGN

Based on the Data Dictionary in the previous section, the following helped create the corresponding
database in phpMyAdmin. Here’s presenting a screen capture of the database created in phpMyAdmin

Each table listed here contains the description that is mentioned in the data dictionary.

10 | P a g e
IO Student Registration And Job Management

SYSTEM CAPABILITIES

The following section shows the various capabilities of the system. We have designed very basic forms
which depict the functionalities of the system. The idea is inspired from a US Navy design principle KISS –
“Keep it simple, stupid” which suggests that maximum things should be done without complicating the
design more than it is required.

STUDENT REGISTRATION FORM

There is a lot of data to be entered and the structure maintained in the database for these entries is
complex. However, we have tried to design the form to be as simple as possible so that the user
experiencing these can enter all the data in a single flow without having to navigate through different
forms and pages.

This is the screenshot of the registration form created wherein we take the information required for
student and his dietary requirements. This data is stored across multiple tables which is then used for
various purposes.

11 | P a g e
IO Student Registration And Job Management

12 | P a g e
IO Student Registration And Job Management

Screenshot with information filled in

13 | P a g e
IO Student Registration And Job Management

This is the success message you will get when you successfully enter all details

14 | P a g e
IO Student Registration And Job Management

JOB LISTING AND ASSIGNMENT

The next task was to provide the director with a listing of jobs and the capability to modify and update
the information. We came up with the following solution

Here is a screenshot of what you can see on the job listing page. It is a simple dropdown wherein the
director can choose the job for which assignment needs to be done

This is a listing of the jobs that would be populated from the database.

15 | P a g e
IO Student Registration And Job Management

Once the selection is done for a particular job, the next list box would fetch all the students (members)
who would’ve opted Yes for the volunteering position. This list control is extremely easy to use. The
director can assign multiple volunteers to a job and the corresponding information would be updated in
the database

16 | P a g e
IO Student Registration And Job Management

The director can now select multiple volunteers from this list and Assign to the job that is selected. Once
can see the selections made through a blue highlight

17 | P a g e
IO Student Registration And Job Management

MEMBER DETAILS MODIFICATION

There might be a requirement to update member details. The following form helps achieve that. The only
thing to be known for this is the student number which can be considered like a student ID that is unique
to each student

18 | P a g e
IO Student Registration And Job Management

Once that value is entered, the system shall fetch the corresponding details from the database and would
populate controls on the form in an editable control. This enables the director to make necessary changes

19 | P a g e
IO Student Registration And Job Management

The director can now update the necessary details. Let us assume we want to update the pincode from
5885 to 5886 and change the volunteering from No to Yes. Then the form appears as below

Once the update is done, the following success message is displayed. The form can be used again to
update details. Corresponding details would be updated in the database as well.

20 | P a g e
IO Student Registration And Job Management

Now if you see the available job list. You shall see the person above added as we changed the Volunteering
from “No” to “Yes”

21 | P a g e
IO Student Registration And Job Management

22 | P a g e
IO Student Registration And Job Management

REPORT – STUDENT ADDRESS DETAILS

Another task was to display student address details. This is a simple listing of GivenName and FamilyName
and addresses which is a combination of various data fields which were entered during the registration
process in the format HouseNumber,StreetName,City,State,Pincode

23 | P a g e
IO Student Registration And Job Management

DIETARY REQUIREMENTS

The last but very important requirement is the listing of students’ dietary requirements. This is a list of
GivenName and FamilyName alongwith the description of the corresponding dietary requirement. This
information was entered during the registration itself. The report displays the values along with a legend
of all nutrition categories which help the director or any other user of this information decide and use the
information on an as needed basis

24 | P a g e
IO Student Registration And Job Management

ASSOCIATED MATERIALS

Following are some of the screenshots from the database

All tables for the IO database

25 | P a g e
IO Student Registration And Job Management

Facility Table

26 | P a g e
IO Student Registration And Job Management

Faculty Table

27 | P a g e
IO Student Registration And Job Management

Job Table

Nutrition Table

28 | P a g e
IO Student Registration And Job Management

School Table

29 | P a g e
IO Student Registration And Job Management

User/Student Table

30 | P a g e
IO Student Registration And Job Management

REFERENCES
LucidChart – To create database designs

https://homepage.net/name_generator/ - To generate names that can be used for data entry

https://www.phpmyadmin.net/ - For phpMyAdmin related tools and documentation

https://developer.wordpress.org/ - For all wordpress development

https://en.wikipedia.org/wiki/KISS_principle - For design decisions

https://www.sisense.com/blog/two-approaches-scalable-database-design/ - For design decisions

31 | P a g e
IO Student Registration And Job Management

APPENDIX A

DECLARATION FORM

Subject Name:
Subject Code:
Campus:
Lecturer:
Student Id ______________ Name _________________________________
Student Id ______________ Name__________________________________
Student Id ______________ Name__________________________________
Student Id ______________ Name _________________________________

STUDENTS’ DECLARATION

I hereby certify that I am an author of the submitted work bearing my name and student
identification number.
Signature ___________________ Date __________
Signature ___________________ Date __________
Signature ___________________ Date __________
Signature ___________________ Date __________

32 | P a g e