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

12DATABASE MANAGEMENT SYSTEM
table
S_FName Varchar The first name
of the staff
None
S_LName Varchar The last name
of the staff
None
S_PhNumber Number The phone
number of the
staff
Must be of ten
numbers
None
S_Address Varchar The address of
the staff
None
S_Email Varchar The email of
the staff
Should have a
valid mail id
None
S_TNumber Number The tax number
of the staff
None
S_JobTitle Varchar Staff
designation
None
S_WeeklyRateOfPa
y
Numeric (4,2) Payment done
to staff per
week
None
CLIENT
Attribute Type Purpose Validation Key
C_ID Varchar Unique
identification of
rows in the
table
No two rows
can be same
Primary
C_TypeID Varchar Indicates to the
primary key of
CLIENT_TYPE
entity
Must be same
with one of the
primary keys of
CLIENT_TYPE
entity
Foreign
C_FName Varchar The first name
of the client
None
C_LName Varchar The last name
of the client
None
C_PhNumber Number The phone Must be of ten None

13DATABASE MANAGEMENT SYSTEM
number of the
client
numbers
C_Address Varchar The address of
the client
None
C_ContctName Varchar The name of the
(only for
contact) of the
client
None
PROJECT
Attribute Type Purpose Validation Key
Proj_ID Varchar Unique
identification of
rows in the
table
No two rows
can be same
Primary
P_Name Varchar The name of
the project
None
P_Description Varchar Description of
the project
None
Start_Date Date Start date of the
project
Must be of ten
numbers
None
End_Date Date End date of the
project
Should be
bigger than
StartDate
None
P_Progress Varchar The name of
the (only for
contact) of the
client
None
TypicalProjectValu
e
Varchar The typical
value of the
project
None
PROJECTLEAD
Attribute Type Purpose Validation Key
Lead_ID Varchar Unique No two rows Primary

14DATABASE MANAGEMENT SYSTEM
identification of
rows in the
table
can be same
Proj_ID Varchar Indicates to the
primary key of
PROJECT
entity
Must be same
with one of the
primary keys of
PROJECT
entity
Foreign
Name Varchar Name of the
project lead
None
Email Varchar Email of the
project lead
Should have a
valid mail id
None
DOCUMENT
Attribute Type Purpose Validation Key
Doc_ID Varchar Unique identification
of rows in the table
No two rows can be
same
Primary
TypeID Varchar Indicates to the
primary key of
DOCUMENT_TYP
E entity
Must be same with
one of the primary
keys of
DOCUMENT_TYP
E entity
Foreign
DocuentTitle Varchar Name of the
document
None
AuthorName Varchar The name of the
author of document
Should have a valid
mail id
None
PAYMENT
Attribute Type Purpose Validation Key
Pay_ID Varchar Unique identification
of rows in the table
No two rows can be
same
Primary
ProjID Varchar Indicates to the
primary key of
PROJECT entity
Must be same with
one of the primary
keys of PROJECT
entity
Foreign