Question

The Orlando Day of Service (ODS) organization will engage volunteers for one day only and focus on supporting over 20 service projects across Orlando focused on improving the quality of life in our community. ODS wants to track the projects, volunteers, and sponsors. ODS wants to track the unique project number, project name, project type (such as, cleaning or planting), project address, contact name, contact phone number. When volunteers sign up to participate in the ODS, they need to provide their full name, phone number, and t-shirt size (they get a free shirt for volunteering). A volunteer can sign up to participate one project at most because of the time conflict (It’s a one-day event). And a project couldn’t be implemented without any volunteer. Sponsors are businesses within the community that will provide funds to support various projects. A sponsor can fund many projects and a project could be funded by multiple sponsors. ODS wants to keep information on the sponsors such as the business name, address, donation type, and donation dollar amount.

Draw an ERD for the following cases. Be sure to include the entities, attributes, primary keys (identifiers), foreign keys, and relationships. Also, do not leave any many-to-many relationships in your data model (use associative entity if needed).

Answer 1

( ERD is given at the end )

ODS wants to track the data of :

1. Projects
2. Volunteers
3. Sponsors

For PROJECTS, ODS wants to track the following data :

• Unique project number
• Project name
• Project type
• Project address
• Contact name
• Contact phone number

For VOLUNTEERS, ODS wants to keep a track of the following :

• Full name
• Phone number
• T-shirt size

For SPONSORS, ODS wants to keep a track of the following data :

• Business name
• Address
• Donation type
• Donation dollar amount

From this information, we can have three entities in our ERD namely -  PROJECTS, VOLUNTEERS and SPONSORS.

PROJECTS entity will have the following attributes :

• UniqueProjectNo ( primary key )
• ProjectName
• ProjectType
• ProjectAddress
• ContactName
• ContactPhoneNo

VOLUNTEERS entity will have the following attributes :

• VolunteerID ( primary key )
• FirstName
• LastName
• PhoneNumber
• TShirtSize

SPONSORS entity will have the following attributes :

• SponsorID ( primary key )
• BusinessName
• Address
• DonationType
• DonationDollarAmount

Here in PROJECTS, UniqueProjectNo is the primary key.

In VOLUNTEERS, VolunteerID is the primary key which is added to uniquely identify each volunteer and the Full name for volunteers is broken down into two attributes FirstName and LastName.

In SPONSORS, SponsorID is the primary key which is added to identify each sponsor uniquely.

Relationships between entities :

As per the given information, one volunteer can participate in at most one project and a project must have at least one volunteer, it can have more than one volunteer too.

So, one project can have one or many volunteers and a volunteer can participate in one project only.

So, the relationship between PROJECTS and VOLUNTEERS is a one to many relationship.

For sponsors and projects, one sponsor can fund many projects and on project can be funded by many sponsors.

So, PROJECTS AND SPONSORS have a many to many relationship.

But we don't want a many to many relationship, so we will create an associative entity called SPONSORED_PROJECTS between PROJECTS AND SPONSORS entity.

SPONSORED_PROJECTS will contain the following attributes :

• UniqueProjectNo( foreign key taken from PROJECTS entity )
• SponsorID ( foreign key taken from SPONSORS entity )

In this entity, UniqueProjectNo and SponsorID will together act as a composite key. ( when two or more attributes act as a key, it is called a composite key )

UniqueProjectNo is taken as foreign key from PROJECTS entity and SponsorID is taken as foreign key from SPONSORS entity.

So now we can have a one to many relationship between PROJECTS and SPONSORED_PROJECTS , also there will exist a one to many relationship between SPONSORS and SPONSORED_PROJECTS .

So the ER diagram will be as follows :