Database Systems and Administration

The report reflects the database designing and database implementation considerations. The entity relationship diagram presented in the report mainly concentrates on the relationships among the entities. Many-to-Many relationship is present in the design which can be removed using bridge table while implementing the database. Considering the MySQL database management system as the best solution made the database development process easy. The backup/recovery, user management and many other features will enhance the database quality.

Running head: DATABASE SYSTEMS AND ADMINISTRATION
Database Systems and Administration
Name of the Student
Name of the University
Author’s note:

1DATABASE SYSTEMS AND ADMINISTRATION
Executive Summary
The report reflects the database designing and database implementation considerations. The
entity relationship diagram presented in the report mainly concentrates on the relationships
among the entities. Many-to-Many relationship is present in the design which can be removed
using bridge table while implementing the database. Considering the MySQL database
management system as the best solution made the database development process easy. The
backup/recovery, user management and many other features will enhance the database quality.

2DATABASE SYSTEMS AND ADMINISTRATION
Table of Contents
Introduction:....................................................................................................................................3
Task 1:.............................................................................................................................................3
Task 2:.............................................................................................................................................4
Task 3:.............................................................................................................................................7
Task 4:...........................................................................................................................................13
Task 5:...........................................................................................................................................15
Conclusion:....................................................................................................................................16
References:....................................................................................................................................17

3DATABASE SYSTEMS AND ADMINISTRATION
Introduction:
The database is the technology that stores data in a specific place. The database
management system is an application that can be used for manipulating the database. Each
database management system has different features and drawbacks. Evaluation of mainstream
database management application is essential before selecting one. The database modeling the
process through which conceptual, logical and physical design of database can be created. The
database also requires various other technical supports like back up, administrative and many
more for meeting fulfilling its purpose.
The report includes Justification of the proposed database along with the logical database
design. The logical design is presented through the entity relationship model. The report also
provides detailed business rules, integrity constraint and database structure.
Task 1:
The first thing done in the report is designing the database of Gulf Institute of Advanced
Technology. This database is completely based on the database requirement of the organization.
Each of the attributes included in the database is mentioned in the scenario of Gulf Institute of
Advanced Technology. The second task is describing the policies and structure of the database.
Each decision taken in the database design has been justified with supported information. The
design supports the structure and business rules perfectly.
The report in the Part B describes the database management system actions along with
the designing consideration of database. The concerns of database implementation is provided in
detail in that report. Various diagrams and table are provided in the report to support the
Justifications. The information is collected mainly from the google scholar database.

4DATABASE SYSTEMS AND ADMINISTRATION
Task 2:
Figure 1: Entity Relationship Diagram
(Source: Created by Author)
Assumptions: During the ERD design it was assumed that student name and age will be
stored in the database. Another assumption is that a set of teaching staff will assigned to only one
module.

5DATABASE SYSTEMS AND ADMINISTRATION
Table: Employee
Attribute Justification
employee_id The unique value that defined every row individually
department_id The id of department in which the employee works
employee_name The name of the employee including first name, middle name
and last name
position The designation of the employee such as Principle, receptionist
or any other except teaching staff
Table: Department
Attribute Justification
department_id The unique value that defined every row individually
department_name The name of the department such as administrative and many
more
Table: Program
Attribute Justification
program_id The unique value that defined every row individually
department_id The id of department which handles the program
program_name The name of the program
Table: Courses

6DATABASE SYSTEMS AND ADMINISTRATION
Attribute Justification
course_id The unique value that defined every row individually
program_id The id of program which includes this course
course_name The name of the course
number_of_credit_hours The count of credit hours
Table: Module
Attribute Justification
module_id The unique value that defined every row individually
course_id The id of course which is associated with the module
module_name The name of the module
Table: TeachingStaff
Attribute Justification
staff_id The unique value that defined every row individually
staff_name The name of the staff including first, middle and last
name
teaching_load Credit load of teaching staff
Table: Student
Attribute Justification
student_id The unique value that defined every row individually

7DATABASE SYSTEMS AND ADMINISTRATION
course_id The id of the course in which the student has got
admission
student_name Name of the student including first, middle and last
name
age Age of the student
Task 3:
Table: Employee
Attribute Data Type Length Null
employee_id INT 10 No
department_id INT 10 No
employee_name Varchar 100 No
position Varchar 100 No
Table: Department
Attribute Data Type Length Null
department_id INT 10 No
department_name Varchar 100 No
Table: Program
Attribute Data Type Length Null
program_id INT 10 No

8DATABASE SYSTEMS AND ADMINISTRATION
department_id INT 10 No
program_name Varchar 100 No
Table: Courses
Attribute Data Type Length Null
course_id INT 10 No
program_id INT 10 No
course_name Varchar 100 No
number_of_credit_hours Varchar 100 No
Table: Module
Attribute Data Type Length Null
module_id INT 10 No
course_id INT 10 No
module_name Varchar 100 No
Table: TeachingStaff
Attribute Data Type Length Null
staff_id INT 10 No
staff_name Varchar 100 No
teaching_load Varchar 100 No

9DATABASE SYSTEMS AND ADMINISTRATION
Table: Student
Attribute Data Type Length Null
student_id INT 10 No
course_id INT 10 No
student_name Varchar 100 No
age INT 10 No
Integrity Rules: The integrity rules are as following.
i. No primary key will be null
ii. The foreign key must have reference to primary key of other table
iii. The foreign keys must have correct value
Table: Employee
Attribute Integrity Constraint References Table
employee_id Primary Key None
department_id Foreign Key Department
employee_name None None
position None None
Table: Department
Attribute Integrity Constraint References Table
department_id Primary Key None

10DATABASE SYSTEMS AND ADMINISTRATION
department_name None None
Table: Program
Attribute Integrity Constraint References Table
program_id Primary Key None
department_id Foreign Key Department
program_name None None
Table: Courses
Attribute Integrity Constraint References Table
course_id Primary Key None
program_id Foreign Key Program
course_name None None
number_of_credit_hours None None
Table: Module
Attribute Data Type Length
module_id Primary Key None
course_id Foreign Key Course
module_name None None

11DATABASE SYSTEMS AND ADMINISTRATION
Table: TeachingStaff
Attribute Integrity Constraint References Table
staff_id Primary Key None
staff_name None None
teaching_load None None
Table: Student
Attribute Integrity Constraint References
Table
student_id Primary Key None
course_id Foreign Key Course
student_name None None
age None None
Business Rules: The business rules of the database are as following.
i. One employee can work in one department only
ii. One department may not have any employee working in
iii. One department can handle one or many programs
iv. Not all department must handle programs
v. One program has many courses
vi. Not every program must have at least one course
vii. Many module can be associated with many courses

12DATABASE SYSTEMS AND ADMINISTRATION
viii. Teaching staff will work on modules
ix. Not every teaching staff will actively work on a module
x. Many student can enroll into many courses
Entity Relation Entity Business Rule
Employee One optional to one
mandatory
Department One employee can
work in one
department only
One department may
not have any
employee working in
Department One mandatory to
many optional
Program One department can
handle one or many
programs
Not all department
must handle
programs
Program One mandatory to
many optional
Courses One program has
many courses
Not every program
must have at least one
course
Courses Many optional to
many mandatory
Module Many module can be
associated with many

13DATABASE SYSTEMS AND ADMINISTRATION
courses
Module One optional to many
mandatory
TeachingStaff Teaching staff will
work on modules
Not every teaching
staff will actively
work on a module
Courses Many optional to
many mandatory
Student Many student can
enroll into many
courses
Task 4:
There are mainly three mainstream databases that can be used for implementing the
database of Gulf Institute of Advanced Technology. These databases are MySQL, Oracle SQL
and MS SQL. These three databases have distinctive features and challenges. The analysis of
these three databases are provided in the following section.
Database Application Technical Feasibility Financial Feasibility
MySQL The MySQL is very simple
and easy to understand. It can
be easily implemented and
managed using applications
like Workbench and PHP My
Admin.
The MySQL is complete free
SQL platform. The only cost
associated with MySQL is
hosting.

14DATABASE SYSTEMS AND ADMINISTRATION
Oracle SQL Oracle is comparatively hard
to implement and is a very
complex database
management application.
Oracle is also very flexible
and secure. It provides a lot
more support to the user than
MySQL or MS SQL. Most of
the programming languages
are more compatible with
Oracle SQL. AWS and MS
Azure has easy to integrate
options for Oracle Database.
The Oracle does not support
all the functionalities in free
version. Hosting latest
versions of Oracle is not
supported by all the vendors.
MS SQL MS SQL is a product of
Microsoft and is most
preferred for Microsoft
Programs and projects. MS
SQL is suitable for ASP.Net
Programs. It is more complex
than MySQL and often
invoke connection error in
local server.
The MS SQL is a free to use
application. MS SQL may
need additional support based
on the project it is being used
in. Regarding this, additional
costs may be required

15DATABASE SYSTEMS AND ADMINISTRATION
Selection of Database Management System: As per the comparison made between three
database management software, the MySQL is the most suitable DBMS for the organization. The
organization does not need to have extremely complex processing that is handled by Oracle.
Moreover, MySQL is supported by many programming language like PHP, which can be
sundered as most appropriate web programming language.
Task 5:
User Management: The user management is the process of managing access of the users
to the database governed by database administrator. The user management includes including
new users, selecting user roles and allowing general privileges for the database users.
Backup/Recovery Strategy: The backup is the process through which a database’s data is
stored in a different location for reducing the effect of data loss. The backup can be categorized
into logical backups and physical backups. The recovery is the process of making the backup
data available again in the system. The incremental backup is a special form of backup that
copies only the changed data blocks. Based on the Gulf Institute of Advanced Technology
database requirements, the backup will be done once in a month as considerable amount of data
change will happen in a month. 64 bit operating system is required for implementing the
backup/recovery system. The backup data storage medium size would be three times bigger than
the actual database.
Storage Management/Techniques: As the advancement in the technology is making data
storage mechanisms easier for small and medium sized organizations. Gulf Institute of Advanced
Technology will implement the storage management technology as a software. The organization

16DATABASE SYSTEMS AND ADMINISTRATION
can also integrate this technology with the hardware. However, the software approach would be
better for the organization to consider because of technical and financial feasibility.
Conclusion:
From the above study, it can be concluded that proposed database can handle all the
operations of Gulf Institute of Advanced Technology after some modifications in the structure.
The database is designed using crow’s foot notation which allowed to have a concise
presentation. The MS SQL could be the best solution for the proposed database. However, the
compatibility of MySQL with most of the programming languages made it the most suitable
choice. Oracle was a strong contender but many of its services required for database
administrative and security purpose needs to be bought from Oracle. The implementation of
backup/recovery, user management and data storage approach will enhance the database
functionality and availability.

17DATABASE SYSTEMS AND ADMINISTRATION
References:
Al-Masree, H.K., 2015. Extracting Entity Relationship Diagram (ERD) from relational database
schema. International Journal of Database Theory and Application, 8(3), pp.15-26.
Arulraj, J. and Pavlo, A., 2017, May. How to build a non-volatile memory database management
system. In Proceedings of the 2017 ACM International Conference on Management of Data (pp.
1753-1758). ACM.
Dennis, A. and Wixom, B.H., 2018. Systems analysis and design. Wiley.
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems. Pearson.
Kadivar, M., 2015. Entity relationship diagram approach to defining cyber-attacks (Doctoral
dissertation, Carleton University).
Nidzwetzki, J.K. and Güting, R.H., 2016. DISTRIBUTED SECONDO: An extensible highly
available and scalable database management system. FernUniversität, Fakultät für Mathematik
und Informatik.
Roy-Hubara, N., Rokach, L., Shapira, B. and Shoval, P., 2017. Modeling graph database
schema. IT Professional, 19(6), pp.34-43.
Yang, L. and Cao, L., 2016. The Effect of MySQL Workbench in Teaching Entity-Relationship
Diagram (ERD) to Relational Schema Mapping. International Journal of Modern Education and
Computer Science, 8(7), p.1.
Yu, X., Xia, Y., Pavlo, A., Sanchez, D., Rudolph, L. and Devadas, S., 2018. Sundial:
harmonizing concurrency control and caching in a distributed OLTP database management
system. Proceedings of the VLDB Endowment, 11(10), pp.1289-1302.

18DATABASE SYSTEMS AND ADMINISTRATION
Zhang, L., Shi, L., Zhang, B., Zhao, L., Dong, Y., Liu, J., Lian, Z., Liang, L., Chen, W., Luo, X.
and Pei, S., 2017. Probabilistic Entity-Relationship Diagram: A correlation between functional
connectivity and spontaneous brain activity during resting state in major depressive
disorder. PloS one, 12(6), p.e0178386.