Assignment 7: Congressional Vote TrackingDatabase

Description

Design an Extended E-R schema diagram for keeping track ofinformation about votes taken in the U.S. House of Representativesand Senate during the current two-year congressionalsession.  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 CONGRESSPERSONin the House of Representatives is described by his or her Name,plus the District represented, the StartDate and EndDate for eachterm that the congressperson was elected, and the political Partyto which he or she belonged when elected (whose domain is{Republican, Democrat, Independent, Other}). Each CONGRESSPERSON inthe Senate is elected statewide, 2 senators per state, for six-yearterms. The database should capture each CONGRESSPERSON'sparticipation on committees and track committee votes, House votes,and Senate votes on bills made by each CONGRESSPERSON. For eachvote taken on a bill, the database should capture whether or notthe vote passed, the numbers of Yeas, Nays, Abstains, and Absences.The database should also record the President's decision to eitherpass the bill into law or veto the bill.

The database keeps track of each BILL (i.e., proposedlaw), including the BillName, the DateOfVote on the bill, whetherthe bill PassedOrFailed (whose domain is {Yes, No}), and theSponsor(s) (the congressperson(s) who sponsored - that is, proposed- the bill).  The database keeps track of how eachcongressperson voted on each bill (domain of vote attribute is{Yes, No, Abstain, Absent}).  Draw an Extended ER schemadiagram for this database application.  Express allconstraints such as cardinality ratios, disjoint vs. overlappingspecializations, and full vs. partial participationconstraints.  State clearly any assumptions youmake.

Tasks & Deliverables

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