ECM38IS Spring 2019: Database Design for Gulf Institute Report

This report details the database design for the Gulf Institute of Advanced Technology (GIAT), addressing the institute's data management needs. It begins with an executive summary and introduction to database management systems, focusing on relational databases. The report includes an Entity-Relationship Diagram (ERD) illustrating the database structure, along with assumptions and justifications for the chosen entities and attributes, such as Employee, Department, Program, Courses, Module, TeachingStaff, Student, and Guardian. The report specifies data types, lengths, and null constraints for each attribute, along with integrity and business rules. Furthermore, it analyzes three popular database management systems (MS SQL, MySQL, and Oracle SQL), recommending MS SQL as the most suitable solution for GIAT. The report also covers user management and outlines a backup/recovery strategy and storage management techniques to ensure data availability and integrity. The conclusion emphasizes the importance of a well-designed database system for GIAT's operations.

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