Great Southern Research Institute Database Management Report Analysis

This report details a database management solution for the Great Southern Research Institute (GSRI). It begins with an ER diagram and key assumptions, followed by an explanation of normalization and various relationships, including one-to-one and one-to-many. The report provides a comprehensive database schema, outlining attributes, data types, primary keys, and foreign key constraints for entities such as CLIENT, DOCUMENT, PROJECT, EXPENSE, STAFF, and AUTHOR. The report also includes references to relevant literature. The solution is designed to provide a robust and well-structured database system for GSRI, ensuring data integrity and efficient management. The report covers all aspects of the database design and implementation, with a focus on clarity and completeness. The database is designed to be in 3NF form.

Great Southern Research Institute – Database management report
[Name]
[Roll no]

Great Southern Research Institute (GSRI)
Table of Contents
ER diagram.......................................................................................................................3
Assumptions......................................................................................................................3
Normalization and relationships.......................................................................................3
One to One................................................................................................................................3
One to Many.............................................................................................................................4
Database schema...............................................................................................................5
References.........................................................................................................................8
2

Great Southern Research Institute (GSRI)
ER diagram
Assumptions
Following are key assumptions of the proposed database:
 A document can be written by one or more authors.
 An author can write one or more documents.
 A payment can only of one type but project can make one to many types of payment
Normalization and relationships
One to One
Relationship 1:
- CLIENT(uniqueID, cName, address, phone, email, contactName, clientType )
- CLIENT_TYPE (tName, description)
Relationship 2:
- DOCUMENT(uniqueID, dName, referDoc, projectID)
3

Great Southern Research Institute (GSRI)
- DOCUMENT_TYPE(typeName, description)
Relationship 3:
- DOCUMENT(uniqueID, dName, referDoc, projectID)
- DOCUMENT(uniqueID, dName, referDoc, projectID)
One to Many
Relationship 1:
- PROJECT(projectID, description, startDate, endDate, progress, totalProjectValue,
projLeaderName)
- PROJ_CLIENT(clientID, projectID, startDate, endDate)
Relationship 2:
- CLIENT(uniqueID, cName, address, email, contactName, clientType)
- PROJ_CLIENT(clientID, projectID, startDate, endDate)
Relationship 3:
- PROJECT(projectID, description, startDate, endDate, progress, totalProjectValue,
projLeaderName)
- PAYMENT(paymentID, amount, paymentDate, payemntType, projectID, clientID,
referenceNo, govtName, gContactName, donorName, donorAddr, donorEmail)
Relationship 4:
- PROJECT(projectID, description, startDate, endDate, progress, totalProjectValue,
projLeaderName)
- DOCUMENT(uniqueID, dName, referDoc, projectID)
Relationship 5:
- PROJECT(projectID, description, startDate, endDate, progress, totalProjectValue,
projLeaderName)
- EXPENSE(uniqueID, amount, expenseDate, payee, expenseDesc, projectID)
Relationship 6:
- DOCUMENT(uniqueID, dName, referDoc, projectID)
- DOC_AUTHOR(docID, authorID)
Relationship 7:
- AUTHOR(uniqueID, aName)
- DOC_AUTHOR(docID, authorID)
Relationship 8:
- PROJECT(projectID, description, startDate, endDate, progress, totalProjectValue,
projLeaderName)
- PROJ_STAFF(projectID, staffID, startDate, endDate)
Relationship 9:
- STAFF(uniqueID, firstName, lastName, address, email, taxNo, jobTitle,
weekRatePay, isProjectLeader)
- PROJ_STAFF(projectID, staffID, startDate, endDate)
4

Great Southern Research Institute (GSRI)
All the above mentioned entities are in 3NF relationship. This can be explained using
following relation:
PROJECT(projectID, description, startDate, endDate, progress, totalProjectValue,
projLeaderName)
This entity is in 1st NF because it removes all the duplicate columns of table. The projectID is
the candidate key of the table because more than one project can have same description.
Similarly, start and end date can be same for two projects. Same holds for progress, total
project value and project leader name. This shows that projectID can functionality determine
rest of the attributes. Also, there is no partial dependency in the PROJECT relation (George,
2004).
The entity is also in the 2nd NF because it removes data subsets which can be applied to more
than one row and put them in separate tables. As mentioned above, description, start date, end
date, progress, total project value and project leader name are not unique hence cannot be
used to functionally determine any other attribute. Since there is not transitive dependency
that is it removed columns which are independent of primary key, hence the relation is in
transitive dependency (Hoffer, George, & Valacich, 1999).
Database schema
CLIENT
Attribute Data Type Primary Key Foreign Key Constraint
uniqueID NUMBER Yes NOT NULL
cName CHAR NOT NULL
address VARCHAR NOT NULL
phone NUMBER NOT NULL
Email VARCHAR NOT NULL
contactName CHAR NOT NULL
clientType CHAR Yes NOT NULL
CLIENT_TYPE
Attribute Data Type Primary Key Foreign Key Constraint
tName CHAR Yes NOT NULL
description VARCHAR NOT NULL
DOCUMENT
Attribute Data Type Primary Key Foreign Key Constraint
uniqueID NUMBER Yes NOT NULL
dName CHAR NOT NULL
referDoc VARCHAR Yes NOT NULL
projectID NUMBER Yes NOT NULL
DOCUMENT_TYPE
Attribute Data Type Primary Key Foreign Key Constraint
5

Great Southern Research Institute (GSRI)
typeName CHAR Yes NOT NULL
description VARCHAR NOT NULL
PROJECT
Attribute Data Type Primary Key Foreign Key Constraint
projectID NUMBER Yes NOT NULL
description VARCHAR NOT NULL
startDate DATE NOT NULL
endDate DATE NOT NULL
Progress VARCHAR NOT NULL
totalProjectValue NUMBER NOT NULL
projLeaderName CHAR NOT NULL
PROJ_CLIENT
Attribute Data Type Primary Key Foreign Key Constraint
clientID NUMBER Yes Yes NOT NULL
projectID NUMBER Yes Yes NOT NULL
startDate DATE NOT NULL
endDate DATE NOT NULL
EXPENSE
Attribute Data Type Primary Key Foreign Key Constraint
uniqueID NUMBER Yes NOT NULL
Amount NUMBER NOT NULL
expenseDate DATE NOT NULL
Payee CHAR NOT NULL
expenseDesc VARCHAR NOT NULL
projectID NUMBER NOT NULL
PROJ_STAFF
Attribute Data Type Primary Key Foreign Key Constraint
staffID NUMBER Yes Yes NOT NULL
projectID NUMBER Yes Yes NOT NULL
startDate DATE NOT NULL
endDate DATE NOT NULL
DOC_AUTHOR
Attribute Data Type Primary Key Foreign Key Constraint
docID NUMBER Yes Yes NOT NULL
authorID NUMBER Yes Yes NOT NULL
STAFF
Attribute Data Type Primary Key Foreign Key Constraint
uniqueID NUMBER Yes Yes NOT NULL
firstName CHAR NOT NULL
lastName CHAR NOT NULL
Address VARCHAR NOT NULL
Email VARCHAR NOT NULL
6

Great Southern Research Institute (GSRI)
taxNo NUMBER NOT NULL
jobTitle CHAR NOT NULL
weekRatePay DECIMAL(4,1) NOT NULL
isPRojectLeader BOOLEAN NOT NULL
AUTHOR
Attribute Data Type Primary Key Foreign Key Constraint
uniqueID CHAR Yes NOT NULL
aName CHAR NOT NULL
7

Great Southern Research Institute (GSRI)
References
George, J. (2004). Object-oriented systems analysis and design. Upper Saddle River, N.J.:
Pearson Prentice Hall.
Hoffer, J., George, J., & Valacich, J. (1999). Modern systems analysis and design. Reading,
Mass.: Addison-Wesley.
8