Database Management System: Case Study of GSRI Database Design

This report presents a comprehensive case study on the Database Management System (DBMS) for the Great Southern Research Institute (GSRI). It begins with an Entity-Relationship (ER) diagram illustrating the database structure, followed by a discussion of assumptions made during the design process. The report then delves into the normalization process, ensuring data integrity and minimizing redundancy, covering first, second, and third normal forms. Finally, it provides a detailed relational schema, outlining the attributes, data types, purposes, validation rules, and primary/foreign keys for each table in the database, including StaffMember, Client, Project, ProjectLead, Document, Payment, Government_Payment, and donation tables. The report utilizes examples to illustrate the design and implementation of a robust and efficient database system for GSRI.

Running head: DATABASE MANAGEMENT SYSTEM
DBMS - Database Management System: A Case study of Great Southern Research
Institute (GSRI)
Student Name: [Please Fill]
Student Number: [Please Fill]
Name of the Tutor: [Please Fill]

1DATABASE MANAGEMENT SYSTEM
Table of Contents
1. ER Diagram for GSRI Database..................................................................................................2
2. Assumptions................................................................................................................................3
3. Normalization..............................................................................................................................3
4. Relational Schema.....................................................................................................................10
Bibliography..................................................................................................................................16

2DATABASE MANAGEMENT SYSTEM
1. ER Diagram for GSRI Database
Figure 1: ER Diagram of GSRI Database

3DATABASE MANAGEMENT SYSTEM
(Source: Created by Author)
2. Assumptions
During the development of ERD the assumptions those have been made are as follows:
a) Between the start and end date of the project the payment should be done by the
payee.
b) The project could have different types of clients
c) Various payments are also available for one project
d) Non staff member can also be the lead of the project
3. Normalization
First Normal form: Each entity associated to the table must have an atomic value to be in
first normal form. The database does not have any multivalued attribute which reflects that it is
in first normal form.
Staff_Member
S_ID
S_FName
S_LName
S_PhNumber
S_Address
S_Email
S_TNumber

4DATABASE MANAGEMENT SYSTEM
S_JobTitle
S_WeeklyRateOfPay
StartDate
EndDate
Notes
Client
ClientID
FirstName
LastName
PhoneNumber
address
contact_name
ClientTypeID
Name
Description
ProjectID
StartDate
EndDate
Notes
ProjectLead
LeadID
Name
Email

5DATABASE MANAGEMENT SYSTEM
Document
DocID
DocuentTitle
AuthorName
TypeID
Type
TypeDescription
Project
ProjectID
Name
Description
StartDate
EndDate
Progress
TypicalProjectValue
ExpenseID
ExpenseAmount
ExpenseDate
ExpensePayee
ExpenseDescription
PaymentID
PaymentAmount
PaymentDate
ClientReferenceNumber
GovernmentDepartmentName

6DATABASE MANAGEMENT SYSTEM
GovernmentContactNumber
DonorName
DonorAddress
DonorEmial
Second Normal form: Following are the rules that must be possessed by the second
normal form:
a) The table must be in 1st normal form
b) No such nonprime attribute are to be dependent on the subset of any of the
candidate key placed in the tables
Staff_Member
FirstName
LastName
PhoneNumber
address
email
TaxNumber
JobTitle
WeeklyRateOfPay
Staff_Work
ProjectName
StartDate
EndDate
Notes

7DATABASE MANAGEMENT SYSTEM
The StartDate, EndDate and the notes are not dependent on the primary attributes but on
the project name. However, the project name is not the proper subset of the primary key.
Client
FirstName
LastName
PhoneNumber
address
contact_name
client_type
description
client_project
ProjectName
StartDate
EndDate
Notes
ProjectLead
name
email
Document
DocuentTitle
AuthorName

8DATABASE MANAGEMENT SYSTEM
type
TypeDescription
Project
name
description
StartDate
EndDate
progress
TypicalProjectValue
ExpenseAmount
ExpenseDate
ExpensePayee
ExpenseDescription
PaymentAmount
payment_date
ClientReferenceNumber
GovernmentDepartmentName
GovernmentContactNumber
DonorName
DonorAddress
DonorEmial
Third Normal form: The following are the rules to be in third normal form:
a) The table must have to be second normal form
b) In case of non-trivial functional dependency, X→A, where A is the primary
attribute and X is the superkey.

9DATABASE MANAGEMENT SYSTEM

10DATABASE MANAGEMENT SYSTEM
Document
DocID
TypeID
DocuentTitle
AuthorName
Document_Type
TypeID
Name
Description
Project
ProjectID
Name
Description
StartDate
EndDate
Progress
TypicalProjectValue
Expense
ExpenseID
Amount
Date
Payee
Description

11DATABASE MANAGEMENT SYSTEM
ClientPayment
PaymentID
ProjectID
ReferenceNumber
Amount
Date
Government_Payment
PaymentID
ProjectID
GovernmentDepartmentName
ContactNumber
Amount
Date
donation
PaymentID
ProjectID
Amount
Date
4. Relational Schema
STAFFMEMBER
Attribute Type Purpose Validation Key
S_ID Varchar Unique
identification of
rows in the
No two rows
can be same
Primary