ASAP PLEASE:
Electoral Process Management System (EPMS)

We want to design a data management application on the electoral process. We assumed

that the data covers only one election.

All eligible citizens must be identified and we want to be able to find for each citizen's

social insurance number (SIN), name (name) and telephone number (telephone). Each

citizen is identified by its social insurance number.

For every citizen, we want to retrieve the electoral pole where he has to exercise its

voting rights (one and only one per citizen). The electoral map divides the territory into

counties and each county is divided into poles. A county is identified by the county

(county_id) and we want to find the name of the county (county_name). A pole is

identified by the county (county_id) which is added to the identifier of the pole (pole_id)

generated to distinguish the different poles of the same county. The pole identifier can be

used in different counties. We want to find the address of each pole (pole_address).

Each citizen votes for a candidate for deputy in his county. A candidate can appear in one

county. We want to be able to find for each candidate, his social insurance number (SIN),

name (name), telephone number (telephone), age (age), gender (gender), his party and

the county where he reports to the deputy. We also want to preserve the following

information about recognized party: name of the party (party_name), address of the

official local (party_address) and telephone number (party_telephone).

For each party, we want to know who their leader should be a candidate officially

recognized. For the leader of the party, we want to preserve, in addition to the

information relating to the status of a candidate on deputy, the date of his election as

party leader (leader_date_elected).

Every citizen can make one or more monetary contributions to one or more parties. For

every citizen, we want to preserve, if applicable, the total amount (amount) that

contributed to each party.

QUESTIONS

1) Provide an entity-relationship conceptual diagram that models the above case. Specify

cardinalities and use a verb to represent relationships between entities (or tables). (20

points)

2) Provide the logical diagram of your conceptual diagram. Specify integrity constraints

by mentioning the primary keys and foreign keys for each table. (20 points)

3) Provide a relational diagram of your logical diagram. For each entity or relationship

set in the model (design), determine which attribute(s) form the key. (10 points)