-
SQL Query - Vote System
Hii! I have the following project:
Code:
DROP TABLE IF EXISTS PARTY, MUNICIPALITY, STANDFOR, POLLING_STATION, ELECTORAL_BOARD, CENSUS, COUNT CASCADE;
CREATE TABLE IF NOT EXISTS PARTY (
acronym VARCHAR(20) PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
address VARCHAR(50) NOT NULL);
CREATE TABLE IF NOT EXISTS MUNICIPALITY (
name VARCHAR(50) PRIMARY KEY,
locality VARCHAR(50) NOT NULL,
total_votes INTEGER);
CREATE TABLE IF NOT EXISTS STANDFOR (
acronym VARCHAR(20) REFERENCES PARTY(acronym) ON DELETE CASCADE ON UPDATE CASCADE,
name VARCHAR(50) REFERENCES MUNICIPALITY(name) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (acronym,name));
CREATE TABLE IF NOT EXISTS POLLING_STATION (
ndistrict INTEGER PRIMARY KEY,
name VARCHAR(50) NOT NULL REFERENCES MUNICIPALITY(name) ON DELETE CASCADE ON UPDATE CASCADE,
presides VARCHAR(9) );
CREATE TABLE IF NOT EXISTS ELECTORAL_BOARD (
ndistrict INTEGER REFERENCES POLLING_STATION(ndistrict) ON DELETE CASCADE ON UPDATE CASCADE,
number INTEGER,
null_votes INTEGER,
blank_votes INTEGER,
presides VARCHAR(9),
PRIMARY KEY (ndistrict,number));
CREATE TABLE IF NOT EXISTS CENSUS (
idcard VARCHAR(9) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
votes_ndist INTEGER NOT NULL,
votes_num INTEGER NOT NULL,
member_ndist INTEGER,
member_num INTEGER,
FOREIGN KEY (votes_ndist,votes_num) REFERENCES ELECTORAL_BOARD(ndistrict,number),
FOREIGN KEY (member_ndist,member_num) REFERENCES ELECTORAL_BOARD(ndistrict,number) ON DELETE SET NULL ON UPDATE SET NULL);
ALTER TABLE POLLING_STATION
ADD FOREIGN KEY (presides) REFERENCES CENSUS(idcard) ON DELETE SET NULL ON UPDATE SET NULL;
ALTER TABLE ELECTORAL_BOARD
ADD FOREIGN KEY (presides) REFERENCES CENSUS(idcard) ON DELETE SET NULL ON UPDATE SET NULL;
CREATE TABLE IF NOT EXISTS COUNT (
ndistrict INTEGER,
number INTEGER,
FOREIGN KEY (ndistrict,number) REFERENCES ELECTORAL_BOARD(ndistrict,number) ON DELETE CASCADE ON UPDATE CASCADE,
acronym VARCHAR(20) REFERENCES PARTY(acronym) ON DELETE CASCADE ON UPDATE CASCADE,
nvotes INTEGER NOT NULL,
PRIMARY KEY(ndistrict,number,acronym));
I need a query that returns votes and acronyms of the parties whose number of votes in the municipality m1 is equal to the number of invalid votes plus the number of blank votes in that same municipality. I have thought of a meeting between POLLING STATION and ELECTORAL BOARD, and a correlated subquery with a grouping that would give the total of adding all the null and blank votes, but I'm a bit lost. Thanks for the help!
-
Running those SQL in Access had issues. The IF NOT EXISTS and ON DELETE clauses errored - I had to remove. Also, using number as a field name errored - had to enclose in [] everywhere. Should avoid using reserved words as names - name and number are reserved words. What is purpose of number field? Sample data would be nice. Could go to Advanced editor and just attach Access db file or Excel workbook or build tables in post.
Really need more explanation of data each table would hold. For instance, why would multiple tables have count data? What is presides field for?
Should make it easier for members to help. I have already done more work than I should have and now have to figure out data that would make sense. I am ready to abandon.
Last edited by June7; 06-11-2022 at 01:28 PM.
-
Should avoid using reserved words
-
The project you shared here needs to be fixed for me. Please provide a working code for testing. Your objective should be achieved once we get some testing demos. We can't give you the direction when we can't see the live results of this project. Thanks!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|