Assignment Objective: The goal of this assignment is to develop database model and database design that will assist in understanding the relational database. Before we start the coding we need to create a model that represents the database then convert it into database design.

Introduction: Remember, from the first project assignment you were hired as a consultant to assist in the construction of an efficient database for a non-profit foundation. As a consultant you were able to identify four major entities in the organization system, Donor, Donation, Events and Projects. You also identified each entities attributes and functional dependencies as shown below.

Donations

DonationID

Amount

Date

TransactionNum

DonorID

ProjectID

EventID

Donors

DonorID

FirstName

LastName

StreetAddress

City

State

ZipCode

PhoneNumber

Email

Projects

ProjectID

Type

Name

Location

Duration

DateOfFunding

AmountOfFunding

CompletionDate

Events

EventID

Description

Location

Date

Sponsor

EventFundingGoal

CollectedFunds

Functional dependencies:

Donor

(DonorID) --> (FirstName, LastName, StreetAdd, City, State, ZipCode, Phone, Email)

(ZipCode) --> (City, State)

(Email) --> (FirstName, LastName, StreetAdd, City, State, ZipCode, Phone)

(Phone) --> (FirstName, LastName, StreetAdd, City, State, ZipCode, email)

Donations

(DonationID) --> (DonationAmt, DonationType, DonationCCNum, DonatCCExpDate, ProjectID, DonorID)

Projects

(ProjectID) --> (ProjectType, ProjectName, ProjectLocation, ProjectDuration, FundingDate, FundingAmt, CompleteDate)

Events

(EventID) --> (ProjectID, EventDescription, EventLocation, EventDate, Sponsor, EventFundGoal, CollectedFunding)

In addition, you had an interview with the nonprofit organization director to understand more about the database and entity relationships. The director said: "The fundraising or donations can be through donors or through events. One donor can make recurrent donations or one time donation, which means that a donation must have a donor but a donor does not have to donate always. If donations are not enough an event will be initiated. Usually the collected donation will support one project or several projects, and sometimes we might have a huge project that requires the collection of donations through several events. So a project must be supported by a donation, and donations have to be used for projects."

Note: this interview will assist in defining the maximum and minimum cardinalities (relationships).

Requirement:

Based on your interview with the foundation director create a data model and a database design that will represent the four entities, including their attributes and relationships. Watch the video lectures related to data model and database design and the difference between the model and the design. Also watch the lecture that will assist you in using MS Visio to develop the model and design. You will submit a MS Word Document that has snap shots from MS Visio representing your assignment.

In the design remember to include the data type, data size and the null/not null constraints. You should specify which attribute will be used as the primary key and which one will be a foreign key.