Skip to content

Latest commit

 

History

History
76 lines (38 loc) · 2.48 KB

File metadata and controls

76 lines (38 loc) · 2.48 KB

Canadian federal election 2015 Database

Description

SQl Server database used to store data relating to the 2015 federal election.

Database Schema

TABLE SCHEMA:

  • Province = {provinceName, provincePopulation, provinceGDP}

  • Riding = {ridingName, ridingPopulation, provinceName}

  • Party = {partyID, partyName, leaderFirstName, leaderLastName, partyWeb}

  • Ballots = {ridingName, candidateFirstName, candidateLastName, partyID, votes}

Bolded attributes = primary key

Italicized attributes = foreign attributes

Bolded + italicized attributes = foreign keys

Constraints

THE BEHAVIOUR OF THE FOREIGN KEYS IS GIVEN BELOW:

  • provinceName in Riding: do not allow deletion or update of referenced attributes

  • ridingName in Ballots: do not allow deletion of referenced attributes, cascade changes to referenced attributes

  • partyID in Ballots: do not allow deletion of referenced attributes, cascade changes to referenced attributes

IN ADDITION TO THE FOREIGN KEY AND PRIMARY KEY CONTRAINTS WE HAVE THE FOLLOWING CONSTRAINTS:

  • The attribute set {candidateFirstName, candidateLastName, partyID} should be specified as a candidate key in the Ballots table (no pun intended)

  • The partyID attribute in Ballots should not be null

  • The partyWeb attribute should be specified as a candidate key in the Party table

  • The leaderFirstName and leaderLastName attributes in the Party table should not be null

  • The votes attribute in the Ballots table should not be null, and should be greater or equal to zero

  • The sum of the votes for a riding should be less than the population of that riding

View

THE VIEW CONTAINS THE FOLLOWING ATTRIBUTES:

  • ridingName

  • mpFirstName (the first name of the candidate with the most votes in the riding)

  • mpLastName (the last name of the candidate with the most votes in the riding)

  • mpParty (the party name of the candidate with the most votes in the riding)

  • mpVotes (the votes cast for the candidate with the most votes in the riding)

  • totalVotes (the total number of votes cast in the riding)

Data

THE DATA WAS TAKEN FROM THE FOLLOWING WEBSITES:

Political parties

Province population

Province GDP

Riding population