Database ER Diagram and Relational Model

This assignment requires the creation of an Entity-Relationship (ER) diagram and its corresponding relational model for a donation database. The ER diagram should include at least four entities: Organisation, Donor, Donation, and DonationAmount. Each entity has several attributes defined, including unique identifiers (primary keys), and relationships between entities are clearly shown. The relational model then translates this ER diagram into a set of tables, specifying attribute names, data types, primary keys, foreign keys, and constraints like NOT NULL and default values for each attribute. The assignment concludes with an explanation of the key differences between the ER diagram (a high-level conceptual model) and the relational model (a more detailed, implementation-ready model). The website provides past papers and solved assignments to help students understand the concepts better.

Show the ER diagram for your database. There should be at least 4 entities with their
relations. Clearly describe and explain all the symbols you use for your ER diagram.
Organisation
OrganisationId - Unique Id for each organisation
OrganisationName - Name of the organisation
OrganisationAddress - Address of the organisation
OrganisationContact - Contact Number of organisation
OrganisationDescription - Organisation description
OrganisationStatus - Status of the organisation like active or non-active to take donation further
Donor

DonorId - Unique Id for each donor
DonorName - Name of the donor
DonorEmail - Email of the donor to send email
DonorContact - Contact number of donor
DonorAddress - Address of the donor
Donation
DonationId - Unique Id for each donation made
DonorId - Donor Id who made the donation
OrganisationId - Organisation Id for which donation is made
DonationAmount - Donation amount donated by donor
DonationDate - Donation date on which donation is made
IsAnonymous - Yes, if donor wants to be anonymous else no
ThankyouLetterStatus - Sent, if mail has been sent to donor else penidng
DonationAmount
OrganisationId - Organisation Id for which data is stored
TotalAmountReceived - Total donation received by the organisation
TotalAmountTransferred - Total donation transferred by DonationDollar by now
TotalAmountPending - Total donation pending to be transferred by DonationDollar
DonationDollarFees - Fees earned by DonationDollar till now
Show the corresponding Relational Model
Organisation

Attribute Name Attribute Type Attribute Key Null
OrganisationId int Primary key, auto
increment
NOT NULL
OrganisationName varchar(100) NOT NULL
OrganisationAddress varchar(500) NOT NULL
OrganisationContact varchar(20) NOT NULL
OrganisationStatus Enum (‘active’,’non-
active’’)
Default - active NOT NULL
OrganisationDescripti
on
text NOT NULL
Donor
Attribute Name Attribute Type Attribute Key Null
DonorId int Primary key, auto
increment
NOT NULL
DonorName varchar(100) NOT NULL
DonorEmail varchar(100) NOT NULL
DonorContact varchar(20) NOT NULL
DonorAddress varchar(500) NOT NULL
Donation
Attribute Name Attribute Type Attribute Key Null

DonationId int Primary key, auto
increment
NOT NULL
DonorId int Foreign Key (Donor) NOT NULL
OrganisationId int Foreign Key
(Organisation)
NOT NULL
DonationAmount decimal(10,2) NOT NULL
DonationDate date Default - CURRENT
DATE
NOT NULL
IsAnonymous enum(‘yes’, ‘no’) Default - no NOT NULL
ThankyouLetterStatu
s
Enum (‘sent’,
‘pending’)
Default - pending NOT NULL
DonationAmount
Attribute Name Attribute Type Attribute Key Null
OrganisationId int Foreign Key
(Organisation)
NOT NULL
TotalAmountReceive
d
decimal(10,2) NOT NULL
TotalAmountTransferr
ed
decimal(10,2) NOT NULL
TotalAmountPending decimal(10,2) NOT NULL
DonationDollarFees decimal(10,2) NOT NULL
Explain the difference between an ER diagram and your Relational Model showing the
tables.
The difference between ER Diagram and Relational Model is that ER diagram shows the
relationship between the entities while Relational Model shows the attributes type, primary key,
foreign key and default values.