Question

Assignment 7: Congressional Vote Tracking Database

Description

Design an Extended E-R schema diagram for keeping track of information about votes taken in the U.S. House of Representatives and Senate during the current two-year congressional session.  The database needs to keep track of each U.S. STATE's Name (e.g. Texas, New York, Pennsylvania, etc.) and include the Region of the state (whose domain is {North-east, Midwest, Southeast, West}).  Each CONGRESSPERSON in the House of Representatives is described by his or her Name, plus the District represented, the StartDate and EndDate for each term that the congressperson was elected, and the political Party to which he or she belonged when elected (whose domain is {Republican, Democrat, Independent, Other}). Each CONGRESSPERSON in the Senate is elected statewide, 2 senators per state, for six-year terms. The database should capture each CONGRESSPERSON's participation on committees and track committee votes, House votes, and Senate votes on bills made by each CONGRESSPERSON. For each vote taken on a bill, the database should capture whether or not the vote passed, the numbers of Yeas, Nays, Abstains, and Absences. The database should also record the President's decision to either pass the bill into law or veto the bill.

The database keeps track of each BILL (i.e., proposed law), including the BillName, the DateOfVote on the bill, whether the bill PassedOrFailed (whose domain is {Yes, No}), and the Sponsor(s) (the congressperson(s) who sponsored - that is, proposed - the bill).  The database keeps track of how each congressperson voted on each bill (domain of vote attribute is {Yes, No, Abstain, Absent}).  Draw an Extended ER schema diagram for this database application.  Express all constraints such as cardinality ratios, disjoint vs. overlapping specializations, and full vs. partial participation constraints.  State clearly any assumptions you make.

Tasks & Deliverables

1. Draw the Extended E-R Diagram
2. Map EER to Relational Schema using the 8-Step Mapping Algorithm

Answer 1

Many students added an arrow from the entity box Reprensentative to the relationship votes to make it a many-one relationship instead of a many-many relationship. Their argument was that a representative gets one vote on a given bill, not more than one. This many-one is the wrong logic, however! The Bill entity box represents the collection of all bills that come to the floor of the house. A representative votes on each bill, so he or she votes on many bills. And, of course, each bill is voted on by many representatives. So the relationship votes is properly many-many.

But what about the fact that we want to ensure that a representative can only vote once on a given bill? We have it already! That is why it was natural to model votes as a rel-ship. Any given pair of a particular representative and a particular bill can show up in the rel-ship at most once. This same pair showing up “again” is impossible by the definition of what a rel-ship is. A relationship is a set (not a multi-set, or bag) of these combinations (for votes, pairs of representatives and bills).

There is no easy way to represent via the E-R diagram that we are restricting the values of how a representative votes to a certain small set of legal values. However, in a relational schema, this would be simple to do. A representative can be uniquely identified by the state and the district (within the state) that he or she represents. So it makes since to make the entity Representative a weak entity on entity State. First, a representative cannot exist without a state to represent. Second, the natural key for representative is the state’s name plus the district.

A derived attribute is any piece of information that we could derive from the other information in the database. Generally, we like to avoid derived attribute because this is a redundancy and so can cause trouble. (What are we to think when the attribute’s value and the value that we could derive for it are different?) We have several in our design. The number of representatives for a state (num reps) can be computed by counting the number of Representative “records” associated with that state. The outcome of a bill can be deduced by how many votes in favor it received.

Relational purists will usually eliminate any derived attributes from the design. Realworld designers will sometimes allow a derived attribute in the design if they feel there is good reason for it. We just should be aware of them and weigh the advantages and disadvantages.