SQL Query Development for Database Election
Consider a problem of developing a database for an on-line election system that will be utilized to conduct election of new leaders of a non-profit organization. Some members of the organization hold leadership positions (president, vice president, treasurer, newsletter editor, annual meeting coordinator, secretary, etc.). It is assumed that they have been nominated to run for the positions and that they have already accepted their nominations, thus becoming official candidates for the available positions. The organization’s statute states that each candidate may only run for one office and each member may cast no more than one vote for each of the offices. The database should facilitate the voting process and record all votes assigned to the candidates but it should not tell which member has voted for which candidate. A typical voting process is expected to involve the following steps:
- A member logs into the system, using her/his user name and password.
- The system authenticates the member and, upon success, it provides ballot forms for positions the member has not yet voted for. Each form shows one position and the candidates who run for this position.
- On each form, the member selects one candidate and submits the form.
- The system stores the ballot, including information about the member, the position, and the pickup time (when the form was submitted). Separately, the system records a vote record, including its sequential number (a unique electronic signature) and the selected candidate's ID (mid).
It is important to note that the separation of the vote record, containing the selected candidate, from the ballot (member + position) is necessary in order to maintain voting privacy.
The following enhanced entity relationship diagram (EERD) shows all relavant entities and their attributes as well as relationships between the entities. A detail instuction dedicated to the deveopment of this diagram is availble at: Online Election Design.
ERD
Download SQL script for this [MySQL] database. The script contains the database schema and sample records. Use this script to build the Election database in MySQL. If necessary follow the instruction for Building MySQL Databases, Using an SQL Script. When done, the Election database will be open so you will be able to develop your queries. You may wish to compare your queries with those shown by the How links.
Single-table queries:
Query S01: Retrieve all columns and all rows from table Member. How?
SELECT * FROM Member;
+-----+-----------+-----------+--------+----------------------+
| mid | firstName | lastName  | pass   | email                |
+-----+-----------+-----------+--------+----------------------+
|   1 | Ann       | Bee       | ab1234 | abee@misor.org       |
|   2 | Ben       | Wolf      | bw4321 | bwolf@misor.org      |
|   3 | Sue       | Butterfly | sb1234 | sbutterfly@misor.org |
|   4 | Don       | Eagle     | de4321 | deagle@misor.org     |
|   5 | Eve       | Hawk      | eh1234 | ehawk@misor.org      |
|   6 | Tom       | Dolphin   | td4321 | tdolphin@misor.org   |
|   7 | Gin       | Whale     | gw1234 | gwhale@misor.org     |
|   8 | Hal       | Fox       | hf4321 | hfox@misor.org       |
|   9 | Ian       | Cat       | ic1234 | icat@misor.org       |
|  10 | Ron       | Dog       | rd4321 | rdog@misor.org       |
+-----+-----------+-----------+--------+----------------------+
Query S02: Show table Office (all columns and rows). How?
SELECT * FROM Office;
+-----+----------------+
| oid | title          |
+-----+----------------+
|   1 | President      |
|   2 | Vice-President |
|   3 | Treasurer      |
+-----+----------------+
Query S03: Show all votes cast for a given candidate (e.g. for mid=1). How?
SELECT * FROM Vote WHERE mid=1;
+-----+-----+---------------------+
| vid | mid | voteTime            |
+-----+-----+---------------------+
|   3 |   1 | 2009-10-02 20:10:00 |
|   5 |   1 | 2009-10-02 23:07:00 |
|   7 |   1 | 2009-10-02 23:43:00 |
|  14 |   1 | 2009-10-03 04:05:00 |
|  17 |   1 | 2009-10-03 06:33:00 |
|  24 |   1 | 2009-10-03 14:54:00 |
|  25 |   1 | 2009-10-03 18:35:00 |
+-----+-----+---------------------+
Query S04: Show all ballots submitted for a given office (e.g. for oid=1). How?
SELECT * FROM Ballot WHERE oid=1;
+-----+-----+---------------------+
| mid | oid | ballotPickupTime    |
+-----+-----+---------------------+
|   1 |   1 | 2009-10-03 18:32:00 |
|   2 |   1 | 2009-10-02 23:41:00 |
|   3 |   1 | 2009-10-03 14:53:00 |
|   4 |   1 | 2009-10-03 06:32:00 |
|   6 |   1 | 2009-10-02 23:45:00 |
|   7 |   1 | 2009-10-03 04:02:00 |
|   8 |   1 | 2009-10-02 23:05:00 |
|   9 |   1 | 2009-10-02 20:08:00 |
|  10 |   1 | 2009-10-03 08:39:00 |
+-----+-----+---------------------+
Query S05: Show all votes cast after a specific time (e.g. after 10-01-2009 10:00 p.m.How?
SELECT * FROM Vote WHERE voteTime > '2009-10-03 22:00:00';
+-----+-----+---------------------+
| vid | mid | voteTime            |
+-----+-----+---------------------+
|  22 |  10 | 2009-10-03 14:56:00 |
|  23 |   5 | 2009-10-03 14:55:00 |
|  24 |   1 | 2009-10-03 14:54:00 |
|  25 |   1 | 2009-10-03 18:35:00 |
|  26 |   9 | 2009-10-03 18:34:00 |
|  27 |   8 | 2009-10-03 18:35:00 |
+-----+-----+---------------------+
 
Multi-table queries:
Query
M01:		 Show members (IDs and names) who are running for an office (ordered by lastName). How?
SELECT Member.mid, Member.firstName, Member.lastName
FROM Member JOIN Candidate USING(mid)
ORDER BY Member.lastName;
Query Outcome:
+-----+-----------+-----------+
| mid | firstName | lastName  |
+-----+-----------+-----------+
|   1 | Ann       | Bee       |
|   3 | Sue       | Butterfly |
|   8 | Hal       | Fox       |
|   4 | Don       | Eagle     |
|  10 | Ron       | Dog       |
|   5 | Eve       | Hawk      |
|   9 | Ian       | Cat       |
+-----+-----------+-----------+
In other words we want to see members whose IDs overlap with those of the candidates. Alternative solutions:
SELECT Member.mid, Member.firstName, Member.lastName
FROM Member JOIN Candidate ON Member.mid=Candidate.mid;
 
SELECT Member.mid, Member.firstName, Member.lastName
FROM Member,Candidate
WHERE Member.mid = Candidate.mid;
 
Query
M02:		 Show members (IDs and names) who are not running for any office (ordered by lastName). How?
SELECT Member.mid, Member.firstName, Member.lastName
FROM Member
WHERE NOT Member.mid IN (SELECT Candidate.mid FROM Candidate)
ORDER BY Member.lastName;
Query Outcome:
+-----+-----------+----------+
| mid | firstName | lastName |
+-----+-----------+----------+
|   2 | Ben       | Wolf     |
|   6 | Tom       | Dolphin  |
|   7 | Gin       | Whale    |
+-----+-----------+----------+
In other words we want to see members who are not candidates. Here is an alternative solution:
SELECT Member.mid, Member.firstName, Member.lastName
FROM Member
WHERE NOT EXISTS
  (Select mid FROM Candidate WHERE Member.mid = Candidate.mid);
 
Query
M03:		 Display information about the candidates and office they are running for, all sorted by the last name. How?
SELECT Member.mid, Member.firstName, Member.lastName, Office.title AS 'runningFor'
FROM Member, Candidate, Office
WHERE Member.mid=Candidate.mid AND Candidate.oid=Office.oid
ORDER BY Member.lastName;
This query joins fields mid, firstName, lastName from table Member with field title from table Office, making sure that Member fields are connected with the Office by matching them along the relationships. Notice that table Member is not directly associated with table Office but there exists associations between Member and Candidate and between Candidate and Office. Thus the primary to foreign key matching conditions, Member.mid=Candidate.mid AND Candidate.oid=Office.oid, must be included in order to enforce the chain of associations Member - Candidate - Office.
Query Outcome:
+-----+-----------+-----------+----------------+
| mid | firstName | lastName  | runningFor     |
+-----+-----------+-----------+----------------+
|   1 | Ann       | Bee       | President      |
|   3 | Sue       | Butterfly | President      |
|   9 | Ian       | Cat       | Treasurer      |
|  10 | Ron       | Dog       | Vice-President |
|   4 | Don       | Eagle     | Vice-President |
|   8 | Hal       | Fox       | President      |
|   5 | Eve       | Hawk      | Treasurer      |
+-----+-----------+-----------+----------------+
 
Query
M04:		 How many candidates run for each office? How?
SELECT oid, title, Count(*) AS candidateCount
FROM Office NATURAL JOIN Candidate
GROUP BY oid;
This is a subtotal query. It joins tables Candidate and Office and it counts the number of times each office is associated with candidates.
Query Outcome:
+-----+----------------+----------------+
| oid | title          | candidateCount |
+-----+----------------+----------------+
|   1 | President      |              3 |
|   2 | Vice-President |              2 |
|   3 | Treasurer      |              2 |
+-----+----------------+----------------+
There is a hidden connection between the COUNT() function and the GROUP BY clause. The latter specifies a column by which the Count() function counts the records. This counting breaks when the value of the GROUP BY column changes. It then shows the count value and continues counting the next group.
Consider the following natural join of tables Candidate and Office, sorted by the office ID (oid):
SELECT oid, title, mid
FROM Office NATURAL JOIN Candidate
ORDER BY oid;
+-----+----------------+-----+
| oid | title          | mid |
+-----+----------------+-----+
|   1 | President      |   1 |
|   1 | President      |   3 |
|   1 | President      |   8 |
|   2 | Vice-President |   4 |
|   2 | Vice-President |  10 |
|   3 | Treasurer      |   5 |
|   3 | Treasurer      |   9 |
+-----+----------------+-----+
With the query output arranged by oid, one can see how the counting proceeds. It starts with the first record having oid = 1 and it continues counting the records until the value of column oid changes (oid = 2). It takes care of the other groups in the same way. Such an algorithm is referred to as "Control-break Processing" (aggregate until it breaks).
Compare this type of processing with the Excel's Subtotal tool: CountByOffice.xlsx.
BTW, if we did not care about the office title, the query could be based just on the Candidate table.
 
Query
M05:		 Show the office that have more than two candidates running for. How?
SELECT oid, title, Count(*) AS candidateCount
FROM Office NATURAL JOIN Candidate
GROUP BY oid
HAVING candidateCount > 2;
This is a filtered-subtotal query. It is an extension of the previous query (M04).
Query Outcome:
+-----+----------------+----------------+
| oid | title          | candidateCount |
+-----+----------------+----------------+
|   1 | President      |              3 |
+-----+----------------+----------------+
An important point to remembers is that conditions imposed on aggregates must by defined in the HAVING clause (not in the WHERE clause).
 
Copyright © 2018 biiat.com