Database Design and Implementation

This assignment delves into the world of database design and implementation. Students will learn about relational database concepts like normalization and create a database schema to manage student information, job assignments, and special diets. The task also involves designing forms for data entry and retrieval, as well as reports to analyze student details and dietary requirements. This hands-on project provides a practical understanding of database principles and their application in real-world scenarios.

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.

 2NF- No partial dependencies exist among any of it attributes. All the non key attributes are
dependent on the key attribute schoolID.
 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
schoolID.
1.3.4 Faculty relation
The faculty relation is sued to hold details of various faculties in the university.
Faculty (facultyID, name , dean)
The faculty 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
facultyID.
 2NF- No partial dependencies exist among any of it attributes. All the non key attributes are
dependent on the key attribute facultyID.
 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
facultyID.
1.3.5 Enrollment relation
The enrollment relation is used to hold details of an enrollment to a course by a student.
Enrollment (enrollmentID, courseID, studentNO, date)
The enrollment 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
enrollmenID.
 2NF- No partial dependencies exist among any of it attributes. All the non key attributes are
dependent on the key attribute enrollmentID.
 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
enrollmentID.
1.3.6 Facilities_services
The facilities_services relation is used to store details of the different facilities and services offered at
the lodge for the students.
Facilities_services (fsID, name, type, description)
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
fsID.
 2NF- No partial dependencies exist among any of it attributes. All the non key attributes are
dependent on the key attribute fsID.
 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 fsID.
1.3.7 Jobs relation
The jobs relation is used to hold details of jobs that result from various facilities and services offered at
the lodge.
Jobs (jobID, name, description, fsID)
The 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
jobID.
 2NF- No partial dependencies exist among any of it attributes. All the non key attributes are
dependent on the key attribute jobID.
 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 jobID.
1.3.8 Tasks relation
The tasks relation is sued to hold different tasks that can result from a certain job.
Tasks (taskID, name, description, time, jobID)
The tasks 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
taskID.
 2NF- No partial dependencies exist among any of it attributes. All the non key attributes are
dependent on the key attribute taskID.
 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 taskID.
1.3.9 Roster
The roster relation is used to hold details of a roster for various tasks to be perfomed.
Roster ( rsID, taskID, studentNO, date_time_from, date_time_to, status)
The roster 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
rsID.

 2NF- No partial dependencies exist among any of it attributes. All the non key attributes are
dependent on the key attribute rsID.
 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 rsID.
1.4 Data Dictionary
Relation Attributes Meaning Data type Other
Students studentNO A unique number used to identify
a student
INT(8) Constraint Primary Key
Unique
firstName The first name of a student VARCHAR(50) Not Null
familyName The family name of a student VARCHAR(50) Not Null
phoneNO A student’s mobile phone number VARCHAR(25) NOT NULL
streetNO The street number the student
comes from
VARCHAR(50) NOT NULL
City The city a student comes from VARCHAR(50) NOT NULL
For example “queens”
State The state a student comes from VARCHAR(50) NOT NULL
postcode The postcode of a student INT(8) Not Null
type The type of a student VARCHAR(25) NOT NULL
For xample “part-time”
volunteer The status of a student whether
he is willing to volunteer or not
VARCHAR(5) NOT NULL
DEFAULT “NO”
For example “YES”
course courseID Unique identifier used to identify a
course
INT Constraint Primary key
Unique
Auto-Increment
name The name of a course VARCHAR(50) Not Null
duration The duration a course takes in
years
INT NOT NULL
For example “4’
schoolID A foreign key used to identify the
school a course belongs to
INT Constraint Foreign key
references school
(schoolID)
School schoolID Unique identifier used to identify a
school
INT Cosntraint primary key
Unique
Auto-Increment
name The name of a school VARCHAR(50) Not Null
For example “School of
computing”
facultyID A foreign key used to identify the
faculty a school belongs
INT NOT NULL
Constraint foreign key
References faculty
(facultyID)
faculty facultyID Unique identifier used to identify a
faculty
INT Constraint Primary key
Unique
Auto-Increment

name The name of the faculty VARCHAR(50) NOT NULL
For example “faculty
of science”
dean The name of the dean in charge of
that department
VARCHAR(50) NOT NULL
Enrollment enrollmentID Unique identifier used to identify
an enrollment by a student
INT Constraint Primary key
Unique
Auto-Increment
courseID Foreign key used to identify the
course a student is enrolling in
INT Constraint Foreign key
references course
(courseID)
StudentNO Foreign key used to identify which
student has done that enrollment
INT Constraint Foreign key
references students
(studentNO)
date The date that the enrollment was
done
Timestamp NOT NULL
Current_timestamp
Facility_service
s
fsID Unique identifier used to identify a
facility or a service
INT Constraint primary key
Unique
name The name of the service or the
facility
VARCHAR(50) NOT NULL
type The type which is either service or
facility
VARCHAR(25) NOT NULL
Example “service”
description The description of the facility of
the service
TEXT NULL
Jobs JobID Unique identifier used to identify a
job
INT Constraint Primary key
Unique
Auto-Increment
name The name of the job VARCHAR(50) NOT NULL
description THe description of the job TEXT NULL
fsID Foreign key identifying the service
or facility the job belongs to
INT NOT NULL
Constraint foreign key
references
facility_services (fsID)
task taskID Unique identifier used to identify a
task resulting from a job
INT Constraint primary key
Unique
name The name of the task VARCHAR(50) NOT NULL
description The description of the task TEXT NULL
Time_interval The time interval between the
subsequent task
VARCHAR(25) NOT NULL
For example “Twice a
week”
roster rID Unique identifier used to identify
every record
INT Constraint primary key
Unique
Auto-increment
taskID Foreign key used to identify the
task
INT Foreign key references
task (taskID)
studentNO Foreign key used to identify INT(8) Constraint foreign key

student that will perform the duty references students
(studentID)
Date_time_from The date and time the task is
supposed to start
DATETIME NOT NULL
Date_time_to The time the task is expected to
end
DATETIME NOT NULL
status The status of the task i.e.
completed or not
VARCHAR(10) NOT NULL
For example
“complete”
Special_diet dietID Unique identifier used to identify
the diet
INT Constraint primary key
Unique
studnetNO Foreign key used to identify the
student
INT Constraint foreign key
references students
(studentID)
description Description of the special diet
2 Task 2: Database implementation
C) Forms and reports
1. A student entry form

2. A form to allow director to change member details and update the job and roster information
without losing data integrity
3. A report that prints the students address details
4. A form for the director to fill in the jobs for the up-coming week with volunteers available

5. A report that lists all the people with particular dietary requirements so that this can be given to
the chef before social functions.

3. References
Baral, S. (2014, August 21). Solved MCQ on Database Normalization set-1. Retrieved May 2, 2017, from
http://www.siteforinfotech.com/2014/08/solved-mcq-on-database-normalization.html
Silberschatz, A., Korth, H. F., & Sudarshan, S. (1986). Database System Concepts. McGraw-Hill Education.
Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems (3rd Edition ed.). New York:
McGraw-Hill Education.