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)
- 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)
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
|
|
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
|
|
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
|
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.
