Database Analysis and Design Assignment

Database Analysis and Design

Table of Contents
INTRODUCTION...........................................................................................................................................3
TASK 1..........................................................................................................................................................3
Entity relationship Diagram-..............................................................................................................3
TASK 2..........................................................................................................................................................8
1. Implement your data dictionary to create a relational database..................................................8
2. The queries, view(s), and report of database relational tables...................................................11
Design Forms and Report..........................................................................................................................13
4. A form for the director to fill in the jobs for the up-coming week with the volunteers available15
5. Report that lists all the people with particular dietary requirements of student information....16
6. A report that prints the students address details........................................................................17
CONCLUSION.............................................................................................................................................18
REFERENCES..............................................................................................................................................19

LIST OF FIGURES
Illustration Index
Illustration 1: figure 1................................................................................................................................10
Illustration 2: Figure:2................................................................................................................................11
Illustration 3: Figure:3................................................................................................................................11
Illustration 4: Figure: 4...............................................................................................................................12
Illustration 5: Figure: 5...............................................................................................................................12
Illustration 6: Figure: 6...............................................................................................................................13
Illustration 7: Figure: 7...............................................................................................................................14
Illustration 8: Figure:8................................................................................................................................14
Illustration 9: Figure :9...............................................................................................................................15
Illustration 10: Figure: 10...........................................................................................................................16
Illustration 11: Figure:11............................................................................................................................16
Illustration 12: Figure:12............................................................................................................................17
Illustration 13: Figure: 13...........................................................................................................................17
Illustration 14: Figure : 14..........................................................................................................................18
Illustration 15: Figure: 15...........................................................................................................................18
Illustration 16: Figure: 16...........................................................................................................................19
Illustration 17: Figure: 17...........................................................................................................................20
Illustration 18: Figure: 18...........................................................................................................................20
Illustration 19: Figure :19...........................................................................................................................21
Illustration 20: Figure :20...........................................................................................................................21
Illustration 21: Figure:21............................................................................................................................22
Illustration 22: Figure :22...........................................................................................................................22
Illustration 23: Figure: 23...........................................................................................................................23

INTRODUCTION
Database management system is a technique for managing the business process which
help for identifying the need and requirement of organization this report will describe the
database of Victoria university. A database of Victoria university is been developed and
implemented. It will store and manage data of student and various other facilities provided by
college. Apart from this, information related to upcoming events and activities are also entered in
database (McMinn and et.al., 2017). This database system is designed for student to access the
system for checking all information. It is a type of relational database system that is all
relationship defined in between entities. Database table used in the organization and used third
normal form that is no data dependencies and data redundancy. This system is designed
according to the need and requirement of system.
The website is developed on Word-press and database is created on PHP MyAdmin panel. It
contains all information in the website. This website provides the opportunity for student to fill
the form and update their information. Plugin is used for designing form and it is useful for
designing an effective database system.

TASK 1
Entity relationship Diagram-
Fig: 1
Entity relationship diagram is a graphical representation which establishes connection between
different entities and attributes (Tocco and et.al., 2018). Entity relationship diagram is represent the
structure of database therefore, it also represents the relationship between tables. This diagram is enclosed
with the entities and attributes.
There are important components applicable in this diagram such as entities and attributes. These
attributes are helpful for creating an effective relationship in database system (Crowson and et.al.,
2018 ).

Entities:
The important entities used in this ER diagram for database as following:
Director_table
Roaster_table
Student_table
Job_table
parent_table
Department_table
volunteer_table
Attributes:
All the above attributes of above entities are following:
1. Student_table (Student_id, student_name, student_email, student_contact no,
student_city, student_state)
2. Volunteer_table (volunteer_id, volunteer_name, volunteer_position, volunteer_roaster,
student_id)
3. Roaster_table (Roaster_schedule, roaster_date, roaster_time, director_is, student_id)
4. Parent_table (parent_id, parent_name, parent_contact, parent_email, student_id)
5. Job_table (Job_id, Job_name, Job_type, student_id)
6. Department_table (department-id, department_name, student_id, volunteer_id)
7. Director_table (director_id, director_name, director_email, director_contact no, Job_id)
Relationship:
1. One or more student can be connected with volunteers.
2. One or more associated with parents.
3. Many departments associated with many volunteers.
4. One director associated with many departments.
5. One director maintain many jobs.

6. One or more student associated with one roaster schedule.
Foreign and primary key constraints applicable in database relational tables.
1. Student_table
Primary key: student_id
Foreign key: Parent_id
2. Volunteer_table
Primary key : volunteer_id
Foreign key: Student_id
3. Roaster_table
Primary key : roaster_schedule
Foreign key: director_id, student_id
4. Parent_table
Primary key : parent_id
Foreign key: student_id
5. Job_table
Primary key : Job_id
Foreign key: Student_id
6. Department_table
Primary key : department_id
Foreign key: student_id, volunteer_id
7. Director_table
Primary key : Director_id
Foreign key: Job_id

Data dictionary :
Data dictionary contain much information and detail of database system. It can contain all record
of data that store in database system. Data dictionary is the important concept for containing
attributes, entities (Deschaseaux and et.al., 2017). It also using key constraints such as foreign
key and primary key. It also useful or creating relationship between tables in database system.
For Example-
Student_table
Attributes Meaning Other
Student_id It uniquely identifies the
table.
Duplication is not allowed i.e
PRIMARY KEY
Student_name This attribute represents the
first name of the student
Ex: “Tyler”
student_email, This attribute represents the
last name of the student
Ex: “Samuel”
student_contact
student_state
This attribute represents the
Student contact number
Ex: 2357
student_city, It represents the student’s
city name
For Example, “greek creek”

Volunteer table
Attributes Meaning Other
volunteer_id, It uniquely identifies the
table.
Duplication is not allowed i.e PRIMARY
KEY
volunteer_name This attribute represents the
first name of the student
Ex: “Tyler”
volunteer_position This attribute represents the
last name of the student
Ex: “Samuel”
volunteer_roaster, This attribute represents the
Student contact number
Ex: 2357
student_id It represents the student’s
city name
For Example, “greek creek”

Department table:
Attributes Meaning Data Type Other
department_id It is the unique
identifier to the
rows.
Int (11) Duplication is not
allowed i.e
PRIMARY KEY
department_name The name of the
department.
Text Ex: “Computer
Science”

This table shows different attributes and their specific meaning in database table. It also
identifies the primary key in particular table. Data dictionary is a process for collecting
information and data in proper manner.
Normalization:
Database normalization is a process for reconstructing the relational database in accordance with
the organizational need and requirement. Normal form is useful in order to reduce the data redundancy.
This normalization form is helpful for improving the data integrity. The main objective of normalization
as discuss in followings:
 To minimizing the need of reconstructing the different collection of relations while new data are
introduced and increase the life of application programs.
 To increase the efficiency of relational database table and more informative to user.
 It is free for user to insert the information in database that would be managed by administrator
which control the entire functionality.
Map the Entity Relationship Diagram (ERD) into a set of relations in at least Third Normal Form (3NF).
You should show that each relation satisfies the rules for 1NF, 2NF, and 3NF.
3NF:
Fig: 1

This table show data dependencies and reduce redundancy in database. Student table can reduce into two
tables that is:
1st student table
2nd parents table
Both tables have data dependency and primary key that show as a foreign key.
Fig: 1 shows data 1 normalization form therefore, 1st Normal form contain the unique value in table. They
do not have data dependencies.
1NF: 1st normal form is process that is unique value in table and doesn't have any data
dependencies.
In student table-
 All the information contain unique value and cannot match to another attributes.
 Each table cell contain single value.
 Each record need to be unique.
2NF: this normal form contain single column primary key in database table (Whited and et.al.,
2018).

TASK 2
Database Implementation:-
Complete database:
Illustration 1: figure 1

Illustration 2: Figure:2
Illustration 3: Figure:3

Illustration 4: Figure: 4
Illustration 5: Figure: 5

Illustration 6: Figure: 6

Il
lustration 7: Figure: 7

Queries of database tables:
Illustration 9: Figure: 10
Ill
ustration 10: Figure:11

Ill
ustration 11: Figure:12
Illustration 12: Figure: 13

Ill
ustration 13: Figure : 14
Illustration 14: Figure: 15

Student entry form is store the details of each and very students. This form will help for student
to enter their details and also store information in the database system.
Il
lustration 15: Figure: 16

A form to allow the director to change Member details and update the job and roster information
without losing data integrity
I
llustration 16: Figure: 17
Illustration 17: Figure: 18

A form for the director to fill in the jobs for the up-coming week with the volunteers available
I
llustration 18: Figure :19
Illustration 19: Figure :20

Report that lists all the people with particular dietary requirements of student information
Illustration 20: Figure:21
Illustration 21: Figure :22

6. A report that prints the students address details
Illustration 22: Figure: 23

CONCLUSION
As above report, A successful design an effectively and efficiently relationship database
system that addressed all the information regarding the departments and people. Database
management is conceptual part for managing the information and data in the form of tables. This
report also gives a brief description about how a form a developed by using wordpress.
Moreover, tables in relational database is developed by using the PHP software.

REFERENCES
Books and journals:
Crowson, M.G and et.al., 2018. Payer database and geospatial analysis to evaluate practice
patterns in treating allergy in North Carolina. American journal of otolaryngology, 39(1),
pp.20-24.
Deschaseaux, C and et.al., 2017. Real-world hsCRP Testing, With a Potential to Evaluate
Residual Inflammatory Cardiovascular Risk, Among Patients With History of Myocardial
Infarction in the United States: A Retrospective Database Analysis. Circulation:
Cardiovascular Quality and Outcomes, 10(suppl_3), pp.A229-A229.
Korn, J and et.al., 2017. Persistence with multiple sclerosis disease-modifying therapies is
highest when switching to fingolimod compared with other oral and injectable therapies:
a retrospective US claims database analysis (P2. 397). Neurology, 88(16 Supplement),
pp.P2-397.
McMinn, P.S and et.al., 2017. Automatic detection and removal of ineffective mutants for the
mutation analysis of relational database schemas. IEEE Transactions on Software
Engineering.
Tocco, M and et.al., 2018. T5. LURASIDONE AND RISK FOR METABOLIC SYNDROME
IN PATIENTS WITH SCHIZOPHRENIA: A COMPREHENSIVE DATABASE
ANALYSIS. Schizophrenia Bulletin, 44(Suppl 1), p.S114.
Whited, W.M and et.al., 2018. Optimal Therapy in Locally Advanced Esophageal Cancer: a
National Cancer Database Analysis. Journal of Gastrointestinal Surgery, 22(2), pp.187-
193.