0

I want to express in relational calculus and SQL the following functional dependency associated with Vote. This is the first time I try to implement it in actual SQL and relational calculus.

Vote: #PollID, #ElectorID → VoteID

From the following relational scheme:

  • Candidate(CandidateID, Name, PoliticalGroup)

  • Poll(PollID, Date, Round)

  • Elector(ElectorID, Name, #PollingStationID)

  • Vote(VoteID,#PollID, #CandidateID, #ElectorID)

My attempt

1. In relational calculus

dependance

2. In SQL

CREATE ASSERTION Vote
    BEFORE COMMIT t.voteID FROM Vote t
        CHECK u.CandidateID FROM Candidate u
             WHERE EXISTS v.PollID FROM Poll v
                 WHERE u.ElectorID=t.ElectorID AND t.PollID=v.PollID
  • 2
    Is there a special reason not to implement in SQL with primary, unique, and foreign keys? No current system implements assertions. – Paul White May 9 '16 at 2:16
  • 1
    What @PaulWhite said ^^. Plus, the FD you mention (#PollID, #ElectorID → VoteID) has ElectionID, yet your attempts do not use ElectorID at all but use CandidateID? Why? (the ASSERTION code looks totally broken by the way.) – ypercubeᵀᴹ May 9 '16 at 7:00
  • @ypercubeᵀᴹ Indeed! It was a mistake actually! Such a shame! That being corrected, is the ASSERTION fixed? – ThePassenger May 9 '16 at 16:23
1

Not knowing your full situation, I recommend something like this. The foreign keys should create the dependencies you need. If your SQL structure is already in a tabular format, you might consider putting this information into temporary tables--like the ones below. But it looks like your tables are there according to your assertion comment.

From what you mentioned you'll need to connect the Candidate, Poll, and Elector tables to the Vote table with a foreign key (with the specific requirements you need--you can easily do this in the SQL Server Studio GUI as well. You'll need to do the same thing with the Elector table and the Poll table via a foreign key within Elector that points to the Poll table. Use the following below as a template/base--add/subtract as needed...

create table Candidate
(
CandidateID int primary key,
Name varchar(255) null,
PoliticalGroup varchar(255) null
)
create table Poll
(
PollID int primary key,
Date datetime null,
Round float null
)
create table Elector
(
ElectorID int primary key, 
Name varchar(255) null,
FK_PollingStationID int null
)

create table Vote
(
VoteID int primary key,
FK_PollID int null,
FK_CandidateID int null,
FK_ElectorID int null
)


ALTER TABLE dbo.Elector ADD CONSTRAINT
    FK_Elector_Polling FOREIGN KEY
    (
    FK_PollingStationID
    ) REFERENCES dbo.Poll
    (
    PollID
    ) 

GO

ALTER TABLE dbo.Vote ADD CONSTRAINT
    FK_Vote_Candidate FOREIGN KEY
    (
    FK_CandidateID
    ) REFERENCES dbo.Candidate
    (
    CandidateID
    ) 

GO
ALTER TABLE dbo.Vote ADD CONSTRAINT
    FK_Vote_Poll FOREIGN KEY
    (
    FK_PollID
    ) REFERENCES dbo.Poll
    (
    PollID
    ) 

GO
ALTER TABLE dbo.Vote ADD CONSTRAINT
    FK_Vote_Elector FOREIGN KEY
    (
    FK_ElectorID
    ) REFERENCES dbo.Elector
    (
    ElectorID
    ) 

GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.