Database Systems and Administration

The report reflects the database design of Gulf Institute of Advance Technology and the backup/recovery strategy for data loss. It discusses the selection of suitable DBMS and provides information on user management and storage management techniques. The report concludes that MS SQL is the best solution for the organization.

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 design of Gulf Institute of Advance Technology. The database is
designed with the nature of properly managing the data of the organization. The structure of the
database states that there will be no many-to-many relation in the database. The database will
track every action taken by the user using the primary key of the user as foreign keys. The
database can experience many issues and data loss is on of those. The Backup/recovery strategy,
capable of managing data loss, is described in the final sections of the report. Total 4.2 percent of
GDP of the local region is contributed by MENA. GCC is going to be the first 5G network
launcher. Amazon bought Souq.com with $580 million. Oman launched a $200 million
investment in 2016. The cloud is the future of business because it can promote remote access,
scalable resources and easy to upgrade.

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

3DATABASE SYSTEMS AND ADMINISTRATION
Introduction:
The database management system can be considered as the application that can be used
for generating and handling databases. The DBMS has inbuilt programs that provides organized
way of creating, retrieving, updating and deleting data. The database is a collection of data. Two
most popular database types are relational database and big-data. The concentration of this report
is entirely on the relational database.
The report is created based on the data requirements of Gulf Institute. The report consist
of logical database in the form of entity relational model. It also describes the reasons for
choosing the entity and attributes.
Task 1:
The design of the database is done initially in the assignment. The created database
design is entirely based on the organization requirement on data storage and retrieval. In the
second task, the structure and working of the database has been described. The third task entails
about the selection of suitable DBMS. In the end, the administration of the database is described.
The Part B is completely about best practices in developing database system. A literature
review has been provided to get an idea about database systems. After understanding the topic,
analysis on various findings has been provided to conclude to an acceptable solution.

4DATABASE SYSTEMS AND ADMINISTRATION
Task 2:
Figure 1: ERD of Gulf Institute of Advanced Technology
(Source: Created by Author)

5DATABASE SYSTEMS AND ADMINISTRATION
Assumptions: At the time of creating the database it was assumed that no many-to-many
relation will be included. Moreover, it was assumed that having guardian data is essential for an
institute to contact someone when needed. The address details will be stored in a different table
so that data redundancy can be reduced as much as possible.
Table: Employee – This entity is selected for storing the details of the employee
Attribute Justification
employeeNumber This will be the primary key of the entity
departmentNumber It refers to the department in which the employee works
employeeName Full name of the employee
designation Working position of the employee
salary Salary of the employee
joinDate The data of joining of the employee
lastDateAtOffice Resignation date
staffPostalAaddress Current Address
staffResidentialAaddress Permanent Home address
Table: Department – Storing the department details in the database
Attribute Justification
departmentNumber This will be the primary key of the entity
departmentName Name of the department
departmentLocation The location of the department in the Institute

6DATABASE SYSTEMS AND ADMINISTRATION
Table: Program – Storing the program details in the database
Attribute Justification
program_id This will be the primary key of the entity
departmentNumber It refers to the department in which the program resides
program_name The name of the program
Table: Courses – Storing the course details in the database
Attribute Justification
course_id This will be the primary key of the entity
program_id It refers to the program in which the program resides
course_name The name of the course
number_of_credit_hours Credit hours count
Table: Module – Storing the module details in the database
Attribute Justification
module_id This will be the primary key of the entity
teachingStaffNumber It refers to the staff who will teach the module
course_id It refers to the course in which the module resides
module_name The name of the module
Table: TeachingStaff – Storing the module details in the database
Attribute Justification

7DATABASE SYSTEMS AND ADMINISTRATION
teachingStaffNumber This will be the primary key of the entity
employeeNumber It refers to the employee who is a teaching staff
teachingLoad Credit load of teacher
Table: Student – Storing the student details in the database
Attribute Justification
studentNumber This will be the primary key of the entity
courseNumber It refers to the course to which the student has taken
admission
studentAaddress It refers to the current address of the student
GuardianNumber It refers to the guardian of the student
guirdianRelation The relation of the guardian with the student
student_name Full name of student
Age Age of the student
studentEmail Email address of the student
Table: Guardian – Storing the student’s guardian details in the database
Attribute Justification
GuardianNumber This will be the primary key of the entity
name Full name of guardian
age Age of guardian
contactNumber Contact number

8DATABASE SYSTEMS AND ADMINISTRATION
Task 3:
Table: Employee
Attribute Data Type Length Null
employeeNumber NUMBER 11 No
departmentNumber NUMBER 11 No
employeeName Varchar2 100 No
designation Varchar2 100 No
salary Decimal 12,2 No
joinDate Date No
lastDateAtOffice Date No
staffPostalAaddress NUMBER 11 No
staffResidentialAaddress NUMBER 11 No
Table: Department
Attribute Data Type Length Null
departmentNumber NUMBER 11 No
departmentName Varchar2 100 No
departmentLocation Varchar2 100 No
Table: Program
Attribute Data Type Length Null

9DATABASE SYSTEMS AND ADMINISTRATION
program_id NUMBER 11 No
departmentNumber NUMBER 11 No
program_name Varchar2 100 No
Table: Courses
Attribute Data Type Length Null
course_id NUMBER 11 No
program_id NUMBER 11 No
course_name Varchar2 100 No
number_of_credit_hours Varchar2 100 No
Table: Module
Attribute Data Type Length Null
module_id NUMBER 11 No
teachingStaffNumber NUMBER 11 No
course_id NUMBER 11 No
module_name Varchar2 100 No
Table: TeachingStaff
Attribute Data Type Length Null
teachingStaffNumber NUMBER 11 No
employeeNumber NUMBER 11 No

10DATABASE SYSTEMS AND ADMINISTRATION
teachingLoad Varchar2 100 No
Table: Student
Attribute Data Type Length Null
studentNumber NUMBER 11 No
courseNumber NUMBER 11 No
studentAaddress NUMBER 11 No
GuardianNumber NUMBER 11 No
guirdianRelation Varchar2 100 No
student_name Varchar2 100 No
Age NUMBER 11 No
studentEmail Varchar2 100 No
Table: Student
Attribute Data Type Length Null
GuardianNumber NUMBER 11 No
name Varchar2 100 No
age NUMBER 11 No
contactNumber NUMBER 10 No
Integrity Rules: The integrity rules are as following.
i. No primary key will be null

11DATABASE SYSTEMS AND ADMINISTRATION
ii. The foreign keys must have correct value
iii. The foreign key must have reference to primary key of other table
Table: Employee
Attribute Integrity Constraint References Table
employeeNumber Primary Key None
departmentNumber Foreign Key Department
Table: Department
Attribute Integrity Constraint References Table
departmentNumber Primary Key None
Table: Program
Attribute Integrity Constraint References Table
program_id Primary Key None
departmentNumber Foreign Key Department
Table: Courses
Attribute Integrity Constraint References Table
course_id Primary Key None
program_id Foreign Key Program
Table: Module

12DATABASE SYSTEMS AND ADMINISTRATION
Attribute Data Type Length
module_id Primary Key None
teachingStaffNumber Foreign Key TeachingStaff
course_id Foreign Key Course
Table: TeachingStaff
Attribute Integrity Constraint References Table
teachingStaffNumber Primary Key None
employeeNumber Foreign Key Employee
Table: Student
Attribute Integrity Constraint References
Table
studentNumber Primary Key None
courseNumber Foreign Key Course
studentAaddress Foreign Key Address
GuardianNumber Foreign Key Guardian
Business Rules: The business rules of the database are as following.
i. One employee can work in one department only
ii. One department can handle one or many programs

13DATABASE SYSTEMS AND ADMINISTRATION
iii. One program has many courses
iv. Not every program must have at least one course
v. Teaching staff will work on modules
vi. Many student can enroll into many courses
Task 4:
The major three database management system that are most popular among the small and
medium sized organization are MySQL, MS SQL and Oracle SQL. Only the relational database
related functionality database management systems are considered in this analysis.
Database Application Technical Feasibility Financial Feasibility
MS SQL It is the product of Microsoft
and it is supported in all the
technologies developed by
Microsoft. The ASP.NET is
by default is most suitable
with MS SQL.
The MS SQL is a free
RDBMS.
MySQL The functionality and security
of MySQL is not as rich as
other two RDBMS.
MySQL is also a free to use
tool.
Oracle SQL Oracle is the most powerful
RDBMS among all. This
database is capable of many
in-depth analysis of data. This
database is supported by most
Oracle is a not a free product
every time. Specifically while
using the ORACLE for
business, many functional
and features of ORACLE is

14DATABASE SYSTEMS AND ADMINISTRATION
of the technologies out there. not available in the free
version.
Selection of Database Management System: The MS SQL is the most suitable solution
for the organization because it can allows the organization to develop a mist effective ASP.NET
solution. Instead of using PHP and MySQL, the organization can use ASP.NET and MS SQL
which is much more secure and robust in nature. On the other hand, using complex platforms and
ORACLE database will increase the budget of the project. Therefore, maintaining security along
with budget can be most suitable by using MS SQL.
Task 5:
User Management: The administrator of the database is a user and responsible for
managing the users who are given permission to access the database. The administrator decides
how the different users will access the database and to which information they have authorization
to access. The user management includes the roles like adding new user, deleting user, managing
access of users to the database, editing user permission, configuring user connection privilege,
changing password and many more.
Backup/Recovery Strategy: It is a common concern of every organization that a database
can become useless if hardware or software failure occurs. The storage issues, recovery issues,
power interruptions and many more problems have to be encountered by any organization at
some point. All this issues can be handled through one solution named data backup/recovery. In
order to implement the data backup/recovery strategy, the organization must be concerned about
the facts such as is the recovery process sufficient, how fast the recovery must be completed,

15DATABASE SYSTEMS AND ADMINISTRATION
how much gap must be provided between two backup operation and many more. The
backup/recovery strategy should ensure that every data in the database is available when an
issues occurs.
Storage Management/Techniques: The database storage management can be
implemented through software approach or hardware approach. Various database management
system like Oracle provides cost effective storage management solution. The storage
management will allow Gulf Institute to handle the growth in data, maximizing resource
utilization and many more.
Conclusion:
From the above study, it can be concluded that database management system is a very
scalable and important piece of technology in modern days. Gulf Institute needs a fully
functional database management system for storing, retrieving and managing the data. The MS
SQL is the best solution for the organization because it can provide a level of security that no
other database system can for free. The database design of the Gulf Institute has many
information about the organization will handle the business and user data. The justification and
description of the database is also given in a very good manner.

16DATABASE SYSTEMS AND ADMINISTRATION
References:
Arasu, A., Babcock, B., Babu, S., Cieslewicz, J., Datar, M., Ito, K., Motwani, R., Srivastava, U.
and Widom, J., 2016. Stream: The stanford data stream management system. In Data Stream
Management (pp. 317-336). Springer, Berlin, Heidelberg.
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.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Deelman, E., Vahi, K., Juve, G., Rynge, M., Callaghan, S., Maechling, P.J., Mayani, R., Chen,
W., Da Silva, R.F., Livny, M. and Wenger, K., 2015. Pegasus, a workflow management system
for science automation. Future Generation Computer Systems, 46, pp.17-35.
Gubanov, M., Jermaine, C., Gao, Z. and Luo, S., 2016. Hybrid: A large-scale linear-relational
database management system. In MIT Annual DB Conference.
Hababeh, I., Khalil, I. and Khreishah, A., 2015. Designing high performance web-based
computing services to promote telemedicine database management system. IEEE transactions on
services computing, 8(1), pp.47-64.
Laudon, K.C. and Laudon, J.P., 2016. Management information system. Pearson Education
India.

17DATABASE SYSTEMS AND ADMINISTRATION
Lee, H., Chapiro, J., Schernthaner, R., Duran, R., Wang, Z., Gorodetski, B., Geschwind, J.F. and
Lin, M., 2015. How I do it: a practical database management system to assist clinical research
teams with data collection, organization, and reporting. Academic radiology, 22(4), pp.527-533.
Lewis, K.A., Tzilivakis, J., Warner, D.J. and Green, A., 2016. An international database for
pesticide risk assessments and management. Human and Ecological Risk Assessment: An
International Journal, 22(4), pp.1050-1064.
Shindo, K., Matsuzaka, T., Mizuguchi, K., Koshizuka, N. and Ishikawa, Y., 2016. Data Model
and Method of Database Federation for Expressway Infrastructure Management. In Proc. in
International Conference on Computing in Civil and Building Engineering (ICCCBE), Jul. 6–8,
Osaka, Japan (pp. 1148-1154).
Thomas, A., Reddy, T.B.K., Isbandi, M., Mallajosyula, J., Stamatis, D. and Bertsch, J., 2015.
Genomes OnLine Database (GOLD) v. 5: an improved metadata management system based on a
four level (meta) genome project classification. In Genomic Standards Consortium (GSC) 17th
Workshop.
Zheng, Y. and Bai, Q., 2018, February. Design and Implementation of Database Centralized
Access Control and Audit System. In 2018 International Conference on Computer Science,
Electronics and Communication Engineering (CSECE 2018). Atlantis Press.