NIT5130: Database Project - Victoria University International Office

Desklib provides past papers and solved assignments. This project details database design and implementation for Victoria University.

NIT5130: Database Analysis and
Design
1

Table of Contents
Introduction................................................................................................................................2
Task 1:........................................................................................................................................3
1.1 Entity Relationship Diagram............................................................................................3
1.2 Business Rules..................................................................................................................4
1.3 Normalization Form.........................................................................................................4
Data Dictionary:.........................................................................................................................6
Task 2:......................................................................................................................................10
Implemented and populated the database.............................................................................10
Design the following three forms and two reports, requiring a separate query to assure
every of the following information needs:...........................................................................15
References................................................................................................................................21
Table of Figures
Figure 1 Entity Relationship Diagram.....................................................................................................3
Figure 2 database tables......................................................................................................................10
Figure 3 student table.........................................................................................................................11
Figure 4 volunteers table.....................................................................................................................11
Figure 5course table............................................................................................................................12
Figure 6 jobs table..............................................................................................................................12
Figure 7 Job_tasks table......................................................................................................................13
Figure 8 Department table..................................................................................................................13
Figure 9 faculty table...........................................................................................................................14
Figure 10 School table.........................................................................................................................14
Figure 11 Event Table..........................................................................................................................14
Figure 12 Event_attend table..............................................................................................................15
Figure 13 Home page...........................................................................................................................15
Figure 14: Registration Form...............................................................................................................16
Figure 15 Update details screenshot...................................................................................................17
Figure 16 update detail screenshot.....................................................................................................18
2

Introduction
In this assignment, the database is to be developed for the Victoria University International
office. The database is to be designed as per the given requirements or case scenario. The
entities and the attributes are to be introduced to full fill the requirement. The Entity
relationship diagram is also to be created. The tables or entities are going to be created in 3rd
normalization form.
The database is going to be created on the PHPMyAdmin. The website is going to be created
in the WordPress. The WordPress website will contain the form which facilitates the user to
enter the student information and also shows the information related to the student.
3

Task 1:
1.1 Entity Relationship Diagram
An ERD (Entity Relationship Diagram) is used to show the graphical representation of the
database structure on the basis of the gathered information. It uses to organize all the
information. It includes the entities or tables with their respective attributes or columns. It
also represents the relationships among the entities or tables. It is the first initial step in order
to create a database. It provides the visualization of the starting point in order to design a
database.
The following entities are introduced with their attributes on the basis of the provided
information in order to design the database:
Figure 1 Entity Relationship Diagram
4

5

1.2 Business Rules
 A student can take admission to one college or school only.
 A student can select one course at a time.
 A student can enroll in one department only.
 There can be more than one students who can be volunteers.
 A volunteer can do only one job.
 There can be more than one task which can be done by the volunteers.
 A faculty can belong to only one department.
 A faculty can belong to one course only.
 A student can attend more than one events.
 An event can include many students.
1.3 Normalization Form
 Student (stdnt_id, stdnt_fname, stdnt_lname, contact_no, address, stdnt_diet,
stdnt_carb, colleg_id, dept_id, course_id)
This student table doesn’t include partial and transitive dependencies and also
include the unique values in each row. So it is in 3rd Normalization form.
 Volunteers (stdnt_id, job_id, salary, working_hours)
The volunteer's table has stdnt_id as primary key and job_id as foreign key and it
also doesn’t include the partial and transitive dependencies.
 Course (course_id, course_name, department_id)
The course table has course_id as primary key and department_id as a foreign key.
It doesn’t include the transitive and partial dependencies.
 Jobs (job_id, job_name, description, total_working_hrs, vacancy)
The jobs table has job_id as primary key which uniquely identifies each row and
also doesn’t has a transitive and primary key.
6

 Job_tasks (task_id, job_name, job_id, stdnt_id, task_date, task_status)
The job_tasks has task_id as primary key and school_id as a foreign key. It
doesn’t have partial and transitive dependencies.
 Department (dept_id, dept_name, dept_head, school_id)
Department table has dept_id as primary key and school_id as a foreign key. It
doesn’t have partial and transitive dependencies.
 Faculty (faculty_id,faculty_name, dept_id, course_id, contact_no, salary)
Faculty table has faculty_id as primary key and dept_id, course_id as foreign
keys.
It doesn’t conbtains partial and transitive dependencies.
 School (cllg_id, cllg_name)
School table has cllg_id as primary key which identifies the unique values.
 Event (event_id, event_name, event_date, timing)
Event tables have event_id as the primary key.
 Event_attend (stdnt_id, event_id)
Here stdnt_id and event_id are the composite primary key and also are foreign
keys.
7

Data Dictionary:
Student Table
8
Attributes Meaning Data Type Other
stdnt_id Uniquely
identify the
student.
Varchar(10) Primary key
stdnt_fname Records the
first name of
the student.
Char(40) Not Null
stdnt_lname Records the last
name of the
student.
Char(40) Not Null
, contact_no Phone number
of the student.
Int(11)
colleg_id Student’s
college or
school id.
Varchar(10) Foreign key
course_id Course opt by
the student
Varchar(10) Foreign key
dept_id Department of
the student.
Varchar(10) Foreign key
address Address of the
student.
Varchar(300) Not Null
stdnt_diet Type of diet of
the student.
Char(30) Not Null
stdnt_carb Carb taken by
the student.
Char(30) Not Null

Volunteers table
Attributes Meaning Data Type Other
stdnt_id Uniquely identify
the volunteers.
Varchar(10) Primary key,
Foreign key
job_id Job id for the
volunteers
Varchar(10) Foreign key
salary Salary offered to the
volunteers.
Float Not Null
working_hours Job hours Varchar(30) Not Null
Course table
Attributes Meaning Data Type Other
Course_id Uniqueli identify the
course.
Varchar(10) Primary key
Course_name Name of the course Char(100) Not Null
Department_id Department id which
includes a particular
course.
Varchar(10) Foreign key
Jobs table
Attributes Meaning Data Type Other
Job_id Uniquely identify
the job.
Varchar(10) Primary key
Job_name Name of the job. Varchar(40) Not Null
Description Description of the Varchar(200) Not Null
9

job.
Total_working_hrs Job hour. Int(11) Not Null
Vacancy Number of vacancy Int Not Null
Job_tasks Table
Attributes Meaning Data Type Other
Task_id Uniquely identify
the task.
Varchar(10) Primary key
job_name Name of the task. Varchar(10) Not Null
Stdnt_id Student id of the
volunteer.
Varchar(10) Foreign key
Job_id Job id of the relative
job.
Varchar(10) Foreign key
Task_date Task date. Date Not Null
Task_status Status of the task. Char(20) Not Null
Department table
Attributes Meaning Data Type Other
Dept_id Uniquely identify
the department.
Varchar(10) Primary key
Dept_name Name of the
department.
Char(100) Not Null
Dept_head Head of the
department.
Varchar(10) Not Null
School_id Job id of the relative
job.
Varchar(10) Foreign key
10

Faculty table
Attributes Meaning Data Type Other
faculty_id Uniquely identify
the faculty.
Varchar(10) Primary key
faculty_name Name of the faculty. Char(100) Not Null
Dept_id The ID of the
department.
Varchar(10) Foreign key
Course_id Course id of the
faculty.
Varchar(10) Foreign key
iContact_no Phone number of the
faculty.
Int(11) Not Null
Salary Salary of the faculty. float Not Null
School table
Attributes Meaning Data Type Other
Cllg_id Uniquely identify
the college.
Varchar(10) Primary key
Cllg_name Name of the college
or school.
Varchar(200) Not Null
Event table
Attributes Meaning Data Type Other
Event_id Uniquely identify
the event.
Varchar(10) Primary key
Event_name Name of the event. Varchar(100) Not Null
11

Event_date Date of the event. Date Not Null
Timing Event timing. Varchar(101) Not Null
Event_attend table
Attributes Meaning Data Type Other
Stdnt_id Uniquely identify
the student.
Varchar(10) Composite Primary
key, foreign key
Evemt_id Uniquely identify
the event.
Varchar(10) Composite Primary
key, foreign key
Task 2:
Implemented and populated the database
Database
Figure 2 database tables
12