When you are through with the exam, send your work by email (in plain text, please, with a subject line of "6.171 mid-term exam") to philg@mit.edu. At the beginning of the message, please include a statement affirming that you worked on the exam completely by yourself. Your mail must be received by 2:30 PM on Tuesday, May 2nd (before class begins).
Handed out: Thursday, April 27th, 2:00 am. Due: Tuesday, May 2nd, 2:30 pm.
Problems or questions? Email philg@mit.edu.
Expected length of answer: 1 page.
Note: In a previous semester, we published the best suggestions. Please state somewhere underneath your answer whether you are willing to let your answer be excerpted and published (and whether or not you want your name used).users table, keyed with
user_id or id. The data model should include appropriate
integrity constraints and indices to facilitate the queries that you
believe are necessary for the pages that you just experienced. The data
model should contain appropriate in-line comments.
Test your data model by feeding it to an RDBMS and making sure that the CREATE TABLE and index definition statements are accepted without errors. You will probably want to create a new database user so that your table definitions don't conflict with any tables that you might have defined for your team project.
Expected length of answer: if your data model contains more than 15 or 20 tables, you're probably handling too many special cases. The important thing is to get the structure of the data model right and the relations. Don't spend a lot of time making sure that you have every possible column in every table. For example it would be okay to say "-- ... address columns ..." to imply that there would be a way to store a member's street address, city, state, etc.
Using the data model you defined in Question 2 write the following computer programs:
Part a: a computer program that prepares a report showing the coders who were the lowest bidders on projects, ordered by descending count of how many projects on which they were the lowest bidder (so if Joe was the lowest bidder on 15 projects, he would appear above Mary, who was the lowest bidder on 12 projects). For each coder, the report should show the total amount of money actually paid to that coder for completed projects on which he or she was the low bidder, and the most recent date of a low bid.
Part b: a computer program that prepares a report showing total project completion by country of origin (number of projects and total dollars paid) and the average customer satisfaction for work done by coders from that country
Ideally each program will be a single SQL query. It is acceptable to use procedural language crutches, e.g., stored procedures.Test your queries by filling your data model tables with some sample data and then run the queries. Print out a transcript of a database session in which you use your queries.
Note that it is possible that you'll start doing these queries and discover that you want to make changes to your data model. That's a perfectly natural way to refine a system. See if a data model supports some queries then go back and modify it until it does.
Don't spend more than a few hours on this question. You can get partial credit if you show that you're thinking along the right lines, e.g., by coming up with some smaller queries that give you reports that could be useful in solving the larger problem.
Using the Levi Strauss data model defined in the "Real World Example" section of http://philip.greenspun.com/sql/data-warehousing, write SQL queries that answer the following questions:
waist_size column and why?
At the beginning of the message, please include a statement affirming that you worked on the exam completely by yourself.