Design a database for recording information about the activities (specifically votes) taken in the US House of Representatives, including:
Each Congress has a unique number (such as 115) and range of dates when it meets (such as January 3, 2017 to January 3, 2019).
Each US state has a name and region (Northeast, Midwest, Southeast, Southwest, West).
Each Representative is described by his or her name, represents a particular district in their state, and belongs to a political party (Republican, Democrat, Independent, Other). Note that a Representative's district and/or party affiliation may be different for different Congresses, but assume that it won't change during a single Congress.
A bill has a unique bill number, a title, at least one sponsor, the Congress it was introduced in, and the date on which it was introduced.
For each bill that has been voted on, the date of the vote, the number of "ayes" and "nos", and whether the bill passed or failed is recorded. Assume there can only be one vote on a given bill. In addition, how each Representative voted on the bill (yes, no, abstain, absent) is also recorded.
Some common problems:
Having party and district be attributes of REPRESENTATIVE. While these are aspects of a representative, it does not allow for the representative to represent different districts and/or have different party affiliations in different Congresses. Making the attributes multivalued allows for multiple values, but there is then no way to know which district and which party go with which Congress.
Having district be an attribute of a binary SERVES IN relationship between REPRESENTATIVE and CONGRESS (in addition to party) allows the representative to represent different districts in different Congresses but doesn't reflect the idea that a district is in a state.
A sponsor attribute on BILL instead of a SPONSORS relationship between REPRESENTATIVE and BILL. This would need to be multivalued and NOT NULL to capture "at least one sponsor", but misses the connection that sponsors are representatives.
Incorrect or missing cardinality values.
Not making the number of ayes and the number of nos that a bill receives derived attributes. These can be computed from how each representative votes on the bill.
Total participation of BILL in VOTES ON means that every bill must have at least one vote - which isn't the case for a bill that hasn't been voted on yet.
Total participation should be reserved for cases where an instance of the relationship must exist in the database. For example, there must be an IN for every district in the database. (This is reasonable because a district name is not meaningful - and is not unique - without the state the district is in.) In other cases, an instance of the relationship may always exist - for example, every representative serves in at least one Congress - but requiring total participation means that it would not be possible to have a representative in the database without also recording at least one Congress the representative is in.