Database Design Project for Victoria University's International Office

Desklib provides past papers and solved assignments; this project details database design for Victoria University.

NIT5130: Database Analysis and
Design Assignment

Table of Contents
Introduction......................................................................................................................................3
Task 1: Database Modelling............................................................................................................4
ER diagram..................................................................................................................................4
Business Rules.............................................................................................................................6
Normalization Form.....................................................................................................................6
Data Dictionary:...........................................................................................................................8
Task 2.............................................................................................................................................13
Implement and populate the database........................................................................................13
C. Design the following three forms and two reports, requiring a separate query to assure every
of the following information needs:..............................................................................................16
References......................................................................................................................................18
Table of tables
Table 1: course table........................................................................................................................8
Table 2: departments table...............................................................................................................8
Table 3: employees table.................................................................................................................9
Table 4: events table......................................................................................................................10
Table 5: HOD table........................................................................................................................10
Table 6: jobs table..........................................................................................................................10

Table 7: job_task table...................................................................................................................11
Table 8: School table.....................................................................................................................12
Table 9: Student table....................................................................................................................12
Table of figure
Figure 1 ERD...................................................................................................................................4
Figure 2: Database Structure.........................................................................................................13
Figure 3: Course table....................................................................................................................13
Figure 4: Departments Table.........................................................................................................14
Figure 5: Employees Table............................................................................................................14
Figure 6: Events Table...................................................................................................................14
Figure 7: HOD table......................................................................................................................15
Figure 8: Jobs Table.......................................................................................................................15
Figure 9: Job_task Table................................................................................................................15
Figure 10: School table..................................................................................................................16
Figure 11: Student Table...............................................................................................................16
Figure 12: Student Registration Form...........................................................................................17
Figure 13 Updating formtcv..........................................................................................................18
Figure 14: Student Address Report................................................................................................18

Figure 15 vacancy form.................................................................................................................19
Figure 16: Student Diet Report......................................................................................................19

Introduction
This assignment is developed for the International Office of Victoria University. In this
assignment, a study is given on which an Entity Relationship diagram is developed which further
use to develop a well-structured data base. For the development of database, phpMyAmin was
used while for development of front end forms wordpress was used. Word press web service
used to create a website in which forms are designed to enter in the database. The database must
use third normalization form while the course of development.

Task 1: Database Modelling
ER diagram
The Entity Relationship diagram abbreviated as ER diagram is a complex level conceptual
diagram representing data modelling technique of the system. ER diagrams are used to represent
the graphical representations of the database and its structure. One can easily say ER diagram is
the kind of blue print of the database. Through ER diagram one can easily represent the
relationship between the various entities. ER diagram can be considered as the root step for setup
a database system. ER diagram reduces the work and time required in developing complete
database according to the scenario.
ER diagram provides the image of the database. On which circumstances whole database is
going to be designed.
Figure 1 ERD

The above figure one can see the Entity relationship diagram of the given scenario. In this
diagram, all the required points are covered.

Business Rules
 One student can only take admission into one school only.
 A student can select only one course from available courses at the decided branch or
school.
 A student can only register himself/herself in one department.
 Volunteers are selected from the available students if they wish to be a volunteer.
 One volunteer can assign one job at a time.
 A single job might contain multiple tasks which is performed by the volunteers.
 Faculty also belong to a unique department.
 Also, faculty can teach at most one course.
 Events can be attended by multiple students.
 Students can also be registered for multiple events in a period of time.
Normalization Form
 Course (c_id, c_name, sch_id, duration)
The above table in a database has c_id as the primary key and sch_id is the foreign key
related to the school table. Each row of the above table do not show any dependencies
neither transitive nor partial. Hence it is satisfied the third Normalization.
 Departments (d_id, d_name,hod_id)
The above department's table has d_id as a primary key while hod_id is the foreign key
references to the hod table. Its columns are all unique and also fulfilling the need of third
normalisation.
 Employees (emp_id, sch_id, emp_name, address, phone, salary, dept_id, doj, experience)
The above Employees table has emp_id as a primary key while sch_id is referential
relation with the school table dept_id is also linked with the department's table as a
foreign key relation remaining all columns of the table provide third normalisation form
as they do not have partial and transitive dependencies.

 Events (ev_id, ev_name, date, venue)
Events table has an ev_id act as a primary key while other columns are not containing
transitive and partial dependencies thus has a valid third normalisation.
 Hod (hod_id, emp_id)
This table contains hod_id as a primary key while emp_id as a foreign key relation thus
not consisting of any kind of dependencies.
 Jobs (job_id, job_name, job_description, job_duration, student_req)
Jobs table has a unique primary key job_id while remaining columns are not showing any
transitive and partial dependencies.
 Job_task (task_id, task_name, job_id, task_duration, status, s_id)
Job_task table has a unique primary key task_id while job_id and s_id are they foreign
key relation with jobs and student respectively.
 School (sch_id, sch_name)
In the above table, we have sch_id as the primary one. This table does not show the
dependencies thus follow the third normalization form.
 Student (s_id, name, address, sch_id, phone, c_id, d_id)
Student table has a s_id as a unique primary key while sch_id, c_id, d_id are the foreign
key relationship with school, course, department respectively.

Data Dictionary:
Course table
Table 1: course table
Attribute name Meaning Type Other
c_id For the unique
identification of the
course
int(10) Primary key
c_name Name of the course is
given to identify the
course
Varchar(20) Not null
sch_id This column contains
the school id which is
used to find the
course in which
department
Int(10) Foreign key
Duration0 This column is used
to store the time
required to complete
the course in years
varchar(5) Not null
Departments table
Table 2: departments table
Attribute name Meaning Type Other
d_id It is used for the
unique identification
of the particular row
of a table
Int(10) Primary key
d_name It stores the name of
the department
Varchar(20) Not null
hod_id In this column id of
hod is given which
assigns the head of
the department of the
particular table.
Int(10) Foreign key

Employees table
Table 3: employees table
Attribute name Meaning Type Other
Emp_id This column
represents the unique
row of a table. It
stores the id of the
employees by which
they identify.
Int(10) Primary key
Sch_id This column stores
the id of the school to
relate the school table
by it and representing
in which school
employee works.
Int(10) Foreign key
Emp_name This column stores
the name of the
employee.
Varchar(20) Not null
Address It stores the address
of the employee.
Varchar(50) Not null
Phone In this column
contact detail of
employees are stored
Varchar(15) Not null
Salary In this column, the
salary of the
employee is stored.
Int(10) Not null
Dept_id This column is stored
in the department
table primary key
dept_id as a foreign
key.
Int(10) Not null
Doj In this table joining
date of the employee
is stored.
Date Not null
Experience In this column
experience of the
column is noted.
Int(10) Not null

Events Table
Table 4: events table
Attribute name Meaning Type Other
ev_id This column stores
the id od event to
identify them
uniquely.
Int(10) Primary key
ev_name In this column, we
store the name of the
event.
Varchar(20) Not null
Date Date of the event is
stored in this column
Date Not null
Venue Event venue is stored
here
Varchar(20) Not null
HOD Table
Table 5: HOD table
Attribute name Meaning Type Other
hod_id It stores the id
assigned to the head
of the departments
Int(10) Primary key
Emp_id It stores the emp_id
to link employee
table with it.
Int(10) Foreign key
Jobs Table
Table 6: jobs table
Attribute name Meaning Type Other
Job_id It is used to uniquely
identify the job
Int(10) Primary key