GIAT: Database Systems and Administration - Design & Implementation

This report details the design and implementation considerations for a database system at the Gulf Institute of Advanced Technology (GIAT). It includes an entity-relationship diagram (ERD) emphasizing entity relationships, noting the presence of many-to-many relationships that require bridge tables during database implementation. The report justifies the selection of MySQL as the database management system, highlighting its ease of development, backup/recovery features, and user management capabilities. It covers database policies, structures, business rules, integrity constraints, and includes justifications for design choices supported by information from Google Scholar. Furthermore, it compares MySQL, Oracle SQL, and MS SQL, ultimately favoring MySQL for its suitability and compatibility. The report concludes by emphasizing the importance of backup/recovery, user management, and storage management techniques for enhancing database functionality and availability.

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