How to express functional dependency in relational calculus and SQL

dependenciesrelational-calculusrelational-theory

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

Best Answer

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
Related Question