Assignment #2 gives you an opportunity to apply what you are learning about data modeling in a number of different ways. First, you are to develop a conceptual data model based on the Maplight.org website. This web site tracks contributions made by organizations and individuals to members of Congress. Second, you will carry out one element of a data model quality assurance program: the data model review. Here you will review another student's data model and, in turn, have yours reviewed. You will incorporate the results of the review of your model into your final deliverable for this assignment.
Read through the problem description in Appendix A and examine the screen shots in the accompanying PowerPoint file. Based on this information, you should create a logical data model using entity-relationship diagramming. The data model should be able to accommodate all of the data found in the description and may include other information found in the screen shots.1 You should do your best to conform to the standards and guidelines we discuss in class.
If you have questions about the requirements for the data model, you may post them in the Blackboard discussion board created for this purpose. Answers will be posted there for all to see. I will also collate the questions and answers in a Google doc (http://bit.ly/Assign2QnA) for ease of access.
I encourage (but do not require) you to use a data modeling tool to develop your Entity-Relationship Diagram. There are (at least) five possibilities here:
I am not requiring you to use any of these tools, but strongly suggest it. If you do not use one of these tools (or some other comparable), you will need to draw the E-R diagrams in some alternative way (PowerPoint is a fairly good tool for drawing lines and boxes, but it is completely manual).
If you are using Oracles SQL Developer Data Modeler
If you are using ERwin:
If you are not using ERwin or Oracle SQL Data Modeler:
Along with the E-R diagram, you should provide a Word or Excel table displaying details about your entities and another table displaying details about your attributes. The entity table should, for each entity type, contain the following information:
The attribute table should, for each attribute, contain the following information
PLEASE NOTE: The tables I am talking about here ARE NOT DATABASE tables. You will not need to touch a DBMS for this assignment.
[a] You are to design a database to support (parts of) a web site tracking money and politics in the U.S. Congress. This assignment and is based on the web site http://maplight.org (Slide A). However, the assignment will differ from that site. When it does, the assignment & Blackboard discussion board are considered authoritative.
[b] Each election season, enormous amounts of money are contributed to candidates. To make the role of money in the democratic processes more transparent, you are to design the database for a website that will track contributions by various contributors to legislators. Each legislator is elected to a particular Chamber in Congress (House of Representatives (House) or the Senate) in a particular year. [simplifying assumption: we wont track terms, or the possibility that a given individual can serve different terms in different houses of Congress.] The legislator has a party affiliation (Democrat, Republican, Independent) and is elected from a particular state. Members of the House of Representatives are elected from a particular district within the state (e.g. 1, 2, 3,) and the district number is stored (Slide B). The name, telephone number, and e-mail for the legislator are also to be stored (Slide C, D).
[c] A legislator receives contributions. Each contribution is made on a specific date in a particular amount, usually $100-1000, to a specific legislator (Slide E). The database is to record this contribution information, as well as the name of the contributor. Contributors are classified into interest groups (e.g. veterinarians, milk and dairy producers, greeting card publishing, farm bureaus, etc.). Each contributor is classified into a single interest group. An interest group is classified into an interest and a category. For example, the veterinarians interest group is part of the Agricultural Services & Products interest, which is classified under the Agribusiness category (Slide F).
[d] An interest group may have a position (support, oppose) on a particular bill (Slide G). A bill is a piece of proposed legislation (Slide H). It has a unique identifying number (e.g. S.3 747 (114th)4, H.R. 1528 (113th)) and an official title (e.g. American Innovation Act, Veterinary Medicine Mobility Act of 2014). A bill has a legistator who is the bills sponsor and zero or more co-sponsors, has a date on which it is introduced, and has a current status (e.g. The bill has become law, a veto override was attempted, etc.) (Slide I). Over time, the bill passes through a number of actions. Each action has a date (Slide J, M). An action may have an action name (if it is a Major Action) and may have a description, but these are optional. This system will not keep track of amendments to bills, or of alternative titles, nor will it store additional information about bills (e.g., Learn More) (Slide I).
[e] Multiple votes are taken on a bill during its lifetime (Slides K, L). A vote takes place on a particular day, in a particular chamber (Senate, House) or Committee, is taken on the basis of a motion and has a result. Legislators may cast their individual votes. A legislator may have a vote of yes, no, present, or not voting (Slide N). Based on a tally of the individual votes, the vote may have a result of passed, not passed. The number of yes votes and the number of no votes are stored.
[f] You may assume, here, that the legislators emails and of contributor names are unique (are identifiers). You may also assume that a contributor gives at most one contribution to a given legislator on a given day. A contributor can give multiple contributions to the same legislator, as long as the contributions are made on different days. A contributor can also give contributions to multiple legislators on the same day. A legislator can cast only one vote for each vote event.
[g] With the data described above stored in a database, the website can determine the total number of money contributed by particular organizations, to individual legislators. It can break down the contributions by the category or interest. It can determine the amount of money contributed by contributors who oppose or who support a particular bill. It can also show the volume of contributions by organizations who support and oppose a bill over time. Note, the website does not directly link contributions to specific legislator votes on particular bills. That would be illegal Outside of the scope of this assignment, but interesting to think about is to what extent contributions are correlated in time or result with specific legislative action To what extent does money influence decisions in Congress?