I was asked to design an ER diagram for the following elections system :
election (edate,eno)
party (pname,symbol)
running (edate, pname, chid, totalvotes)
city (cid, cname, region)
votes (cid, pname, edate, nofvotes)
Elections (election) will take place on a certain date (edate) for the n-th time (eno).
A party (party) that participates in an election has a name (pname) and a symbol (psymbol).
Different parties are running (running) in different elections and we would like to know who is the president of the parties (chid) in each election and what is the total number of votes each party received (totalvotes).
Every party in the system has to participate in some election.
We also keep data about different cities (city), such that each city has an id (cid), name (cname) and region (region).
We would like also to know how many votes (nofvotes) each party received in the different cities where different elections took place, this information is stored in the relation\table named votes.
If a certain party didn't get any votes in a certain city then that city wouldn't appear in the relation votes along with that specific party. (Therefore the attribute nofvotes can't be null).
Note also that in each election there were voters that voted in them.
I came with the following ER diagram :
My question is if I want to enforce the constraint that a certain party in a certain election could get votes in some city only if that party indeed ran in that election (i.e. participated in that election), this constraint is easily achievable if I define edate and pname to be foreign keys in votes referencing running, but is it possible to model this constraint in the ER diagram? Thanks
