Assignment 4: Design a Database

Setting

Design an ER schema for keeping track of the information about votes taken in the U.S. House of Representatives during the current two-year congressional session.

• The database needs to keep track of each U.S. STATE’s Name (eg. Texas, New York etc) and include the Region of the state (whose domain is {Northeast, Midest, Southeast, Southwest, West}).
• Each CONGRESS_PERSON in the House of Representatives is described by his or her Name, plus the District represented, the Start_date when the congressperson was first elected, and the political Party to which he or she belongs (whose domain is {Republican, Democrat, Independent, Other}).
• The database keeps track of each BILL (i.e., proposed law), including the Bill_name, the Date _of_vote on the bill, whether the bill Passed_or_failed (whose domain is {Yes, No}), and the Sponsor ( the congressperson(s) who sponsored – that is, proposed – the bill).
• The database also keeps track of how each congressperson voted on each bill (domain of the Vote attribute is {Yes, No, Abstain, Absent}).

Your Tasks/Questions.

Q1. ER diagram (50 pts.) Give an ER diagram for the database. Be sure to write down all the the assumptions you have made.

Q2. Functional Dependencies (20 pts.) List all the FDs that hold for the database.

Q3. Relational Schema in Data Definition Language (20 pts.) Translate your ER diagram into a relational schema in the form of SQL DDLs. Choose appropriate data types for each attribute and include primary key and foreign key constraints.

Q4. Normal Forms (10 pts.) For each relation in your relational schema, write down whether the relation is in BCNF or 3NF, and if not give a violating FD.

Submission instructions

• Only electronic submissions via Laulima will be accepted.
• This is an individual assignment.
• Every student must make a submission to receive credit. However, you may discuss the problem with at most two other fellow students, but you must write the answers up yourself. List all fellow students that you had discussions with in your PDF submission.
• Label the answers to the four questions clearly.
• Type and/or draw the answers to all four questions using a word processor (eg Microsoft Word).
• Use the diagramming features of the word processor to draw the ER diagram.
• Export the document as a PDF file.
• In addition to the PDF file, submit a text file (named with a .sql suffix) containing all the DDL(s) that is needed for the creation of the database. This DDL file should be executable as a script using the Oracle DBMS command line processor.
• You should name your submission files -a4.{pdf,sql}. For example, if your UH email address is john@hawaii.edu, your submission should contain the following two files: (1) john-a4.pdf contains answers to all 4 questions of the assignment. (2) john-a4.sql contains the DDL from question 3.
• Make sure that the PDF submission also contain your name and list of collaborators.