Define the database structure, including tables, relationships, and any constraints.$$

$-$Provide an Entity$-$Relationship Diagram $($ERD$)$ for the database design.

$-$ context Diagram$$

the data that i got

Users

Stores information about all users, including voters, administrators, and candidates.

user$\_$id $($Primary Key, Auto Increment$)$

national$\_$id $($Unique$,$ Required$)$

full$\_$name

email $($Unique$)$

password $($Hashed$)$

role $($e$.$g$.,$ "voter", "admin", "candidate"$)$

photo$\_$id $($Stores path to uploaded ID photo$)$

is$\_$eligible $($Boolean$,$ Indicates voter eligibility$)$

Elections

Stores details about elections.

election$\_$id $($Primary Key, Auto Increment$)$

name

start$\_$date

end$\_$date

status $($e$.$g$.,$ "ongoing", "completed"$)$

Candidates

Stores candidate information.

candidate$\_$id $($Primary Key, Auto Increment$)$

election$\_$id $($Foreign Key to Elections$)$

user$\_$id $($Foreign Key to Users, references the candidate$)$

bio

votes $($Default $0,$ Updated dynamically during vote tallying$)$

Votes

Stores cast votes.

vote$\_$id $($Primary Key, Auto Increment$)$

election$\_$id $($Foreign Key to Elections$)$

voter$\_$id $($Foreign Key to Users, references the voter$)$

candidate$\_$id $($Foreign Key to Candidates$)$

cast$\_$at $($Timestamp$)$

is$\_$provisional $($Boolean$)$

Results

Stores finalized election results.

result$\_$id $($Primary Key, Auto Increment$)$

election$\_$id $($Foreign Key to Elections$)$

candidate$\_$id $($Foreign Key to Candidates$)$

total$\_$votes

Feedback

For app rating and feedback.

feedback$\_$id $($Primary Key, Auto Increment$)$

user$\_$id $($Foreign Key to Users$)$

rating $($Integer$,$ e$.$g$.,15)$

comments

Relationships

Users $$ Votes: One$-$to$-$Many $($A voter can vote only once in each election$).$

Elections $$ Candidates: One$-$to$-$Many $($Each election can have multiple candidates$).$

Elections $$ Votes: One$-$to$-$Many $($Votes are linked to specific elections$).$

Candidates $$ Votes: One$-$to$-$Many $($Votes are cast for candidates$).$

Elections $$ Results: One$-$to$-$One $($Each election has one result entry$).$

Constraints

Voter Registration

Unique constraint on national$\_$id to avoid duplicates.

Validation of is$\_$eligible before allowing registration completion.

Ballot Casting

A unique composite key on voter$\_$id and election$\_$id in the Votes table to ensure one vote per voter per election.

Vote Tallying

The system automatically calculates votes from the Votes table, grouping by candidate$\_$id and election$\_$id$.$

Results Reporting

Only authorized users $($e$.$g$.,$ admins$)$ can insert into or update the Results table.

Rating the App

Ensure rating is between $1$ and $5.$