ISQA 8410 US Congress ERD

Overview

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.

Tasks

Task 1. Create an E-R Diagram

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:

• Oracle has a tool called SQL Developer Data Modeler, available in the PKI labs. It supports the Information Engineering (crows foot) notation as well as two other less common notations, but not IDEF1X. It is very nicely integrated with Oracle. Make use of the online documentation under the Help menu. http://bit.ly/xqdC9o
• Download ERwin for use on your own computer. Go to http://bit.ly/ERwinCE . This link takes you to Computer Associates ERwin Community Edition web site where you can download ERwin 9 Community Edition. This version of ERwin is limited to 25 tables (fine for our class) and has a one-year license.
• Before using this tool for the assignment, you should go through the first section of a brief tutorial, entitled "Creation of a Basic Data Model found at http://www.isqa.unomaha.edu/WOLCOTT/Tutorials/ERwin/ERwin.html .
• http://gliffy.com Gliffy is an online diagramming application that allows you to create a wide variety of diagrams on-line. You will be creating an Entity Relationship diagram or a UML diagram.
• Use anything else you want
  • Microsoft Visio. Contact Joe Hrabak of the systems staff for instructions (chrabak@mail.unomaha.edu)
  • Use paper & pencil, and photograph & upload the final result. Ok too, as long as I can read the diagram easily.
  • Dont like any of these options? Take a look at all of the tools at http://www.databaseanswers.org/modelling_tools.htm

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).

Task 2. Capture the metadata

If you are using Oracles SQL Developer Data Modeler

• For each entity in the model, double-click on the entity to bring up the Entity Properties dialog box. Click on Comments on the left of the dialog box and type in the definition in the space provided.
• In the Entity Properties dialog box, click on Attributes on the left of the dialog box. Then, click on an attribute name in the Details tab (or, create the attribute first by clicking on the green +). Enter in a definition for the attribute in the Comments tab of the Attributes section of the Entity Properties dialog box.
• For attributes that have domains beyond a simple data type, create the domain using the Domain administration (click on Tools > Domain Administration). Once a domain has been created in this way, it can be used for one or more attributes. To associate a domain with an attribute, go back to the Entities Properties dialog box, click on Attributes, then click on the name of a particular attribute that is to be associated with the domain you created. Where it says Datatype:, click on the Domain radio button and then choose the desired domain in the Type: dropdown box. For other attributes that have just a simple data type, click on the Logical radio button and then choose the desired data type in the Type: dropdown box.
• When you are all done defining your model, generate a report that you will turn in as part of your assignment. Choose File > Reports to bring up a reports dialog box. In the Available Reports drop-down box, choose Entities. In the Output Format, choose PDF. Click on the Generate Report button to create the report.

If you are using ERwin:

• Use the ERwin capabilities to capture definitions and other metadata for Entities and Attributes.
• For each entity in the ER-diagram, right-click on the entity and choose Entity Properties In the Definition tab, type in a definition for the entity in the form A(n) entity name is
• The description communicates precisely what the entity represents and the role it plays in the organization. The definition should explain what the entity is and why it is important to the business. The definition should be concise. It should not just be a restatement of the name of the entity, a list of attributes, a description of process, or full of technical jargon.
• Bad definition: CUSTOMER: An entity holding information about the customer.
• Good definition: A CUSTOMER is an individual or organization who purchases products or services from the company. Customers are the principal source of revenue for the company.
• To add definitions about the attributes, right-click on the entity and choose Attribute properties On the Definition tab, type in a definition for the attribute. While many definitions may be obvious and simple, they are nonetheless useful. For example, Customer First Name may seem obvious to most, but in some countries, like in the Orient, the family name is the first name. Thus, the definition may be the given name of a customer, which clarifies that the name is the given name not the surname. The definition of Customer City may be the city portion of the mailing address for a customer. This definition distinguishes clarifies that this city is part of the mailing address, not necessarily the residential address. Of course, in this case one could argue that the attribute name should have been Customer mailing city
• In some cases, the attribute may have a domain that can be specified more precisely. On the Constraint tab, you may define a constraint to capture this domain. Click on the validation editor icon ( ) and define a new domain. Then create a new constraint and choose the validation rule you created with the validation editor . The same domain constraint may be used for multiple attributes (e.g. Customer state, Employee state, etc.). Alternatively, you can create a domain definition by right-clicking on Domains in the Model Navigator and clicking on New. This domain can be the associated with an attribute in the Attribute Properties dialog box by choosing it in the Parent Domain dropdown box.
• For attributes whose domain is sufficient specified by just a data type, indicate the data type in the Logical Data Type dropdown box.

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:

• Entity Name
• Description

The attribute table should, for each attribute, contain the following information

• Attribute Name
• Description
• Entity of which it is a part
• Type (i.e. primary key, if so designated; you should not include foreign keys in this table)
• Data type
• Additional Domain specification, e.g. enumerated list of permitted values, UNIQUE, NOT NULL, etc.

PLEASE NOTE: The tables I am talking about here ARE NOT DATABASE tables. You will not need to touch a DBMS for this assignment.

Appendix A: Maplight.org

[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?