University:
Massachusetts Institute of Technology
Course:
15.992 | S-Lab: Laboratory for Sustainable Business
Academic year:

2023
Views:

463

Pages:

3
Author:

einnauxgu

Information Technology I Problem Set 2 Database Queries & Design What to turn in: For this problem set, turn in a WinZip archive file containing the following: 1. Database files nwind.mdb and alumni.mdb 2. A Word (or plain text) document containing the text of your answers to Problem 2. PROBLEM 1: QUERIES (40%) In this problem, you will be using Microsoft Access to perform queries on the Northwind Traders database that was used in class. Since there are several different versions of this database around (each successive version of Microsoft Access came with a slightly modified version of the database), to make sure that we grade you correctly, we ask that you use the version, which is available from the class server (see folder Assignments). To do this assignment, you must download this file and make a private copy in one of your directories. In order to perform the queries follow these steps: 1. Go to the class web site and download file nwind.mdb (available in compressed format on the class server) 2. Go to the directory where you saved your personal copy of file nwind.mdb (or make a personal copy if you don’t have one already) 3. Double-click on file nwind.mdb to start Microsoft Access and load the Northwind Traders database 4. Build all queries required for this problem and save each query using names which obey the following pattern: "PS2 Task X", where X is the number of the task corresponding to the query (e.g. "PS2 Task 3"). Feel free to use any of the queries already existing in the database as intermediate steps in your solutions. If you need to create intermediate queries for some of your solutions, save them using names of the form: "PS2 " 5. By saving your queries, you have modified file nwind.mdb. For this reason it is very important that you work in your own personal copy of the file. Task 1: Write a query to display the names of all employees who are Sales Representatives. Task 2: Write a query to display the names of all the customers in Brazil. Task 3: Write a query to display the names of all customers who placed at least one order in 1994. Each name should appear once. Task 4: Write a query to rank all customers who placed orders in 1994 by the total value of their orders. Task 5: Write a query, which lists the names of the 10 most valuable products in 1994 (i.e. the ten products that generated the highest total order revenues in 1994). PROBLEM 2: DATABASE DESIGN (60%) Note: In this problem, some information has deliberately been left unspecified. Please use your common sense and make your own assumptions where you feel is necessary. Make sure that you clearly state your assumptions in your write-up. You will get full credit if your assumptions are reasonable and if your database design is consistent with your assumptions. Like in real life, there is no single correct answer to this problem ☺ Recent reductions in government funding of educational institutions have forced your alma mater to rely increasingly on the contributions of its alumni to keep its budget balanced. Up until now, the Alumni Association of the school has kept a rudimentary database of donors and donations. They have recently decided to make their Alumni Association a more efficient place. Among other things, the AA administration would like to improve their donor database, so that they can keep better track of who is contributing what (and who ought to be contributing more...). Unfortunately, given the school’s budget deficit, they cannot afford a database consultant. An undergraduate IT student, who is working part-time for the Alumni Association, has surfed the net and found the home page you created for 15.564. Since you have not contributed any money to the school since you graduated and since, by taking 15.564, you are now a database expert, the school has kindly asked you to provide some volunteer consulting for them. The current Donors database consists of a single table with the following structure: Donors Last Name First Name Middle Street City State Zip/Country Phone Class Date Amount Purpose Notes <-- Middle initial <-- Year of graduation <-- Donation date <-- Donation amount <-- A code describing the purpose of the donation <-- Additional notes about the person or the donation Currently there are no keys defined in this database. Task 1: Briefly critique the present form of the donors database. In your answer describe the problems that you see with the current design. Task 2: Propose a new design for the database that solves the problems you identified above. Prepare a short text description of your design. Then, enter your table design into Microsoft Access. Save the new database you created under filename alumni.mdb. (You might want to enter some test data in your tables, but you are not required to do so for this problem. However, you must specify all table fields, their data types, as well as any primary or foreign keys you need in your design). Now that you have finalized your database table design, you need to write some SQL queries to extract the information from your new, improved database. Feel free to create intermediate queries whenever necessary. Save your queries with names "PS2 Task X", where X is the number of the corresponding task. Task 3: Write a query that lists all donors together with the total amount of their contributions in the last 5 years (i.e from Jan 1, 1998 to Dec. 31, 2002). Order your output so that the biggest donors are listed first. Your final output should have the form: Last Name First Name Total Amount Task 4: Write a query that calculates the total amount of donations that were raised for the 2002 Alumni Ball. Ball donations have the code "AB" stored in field Purpose. The same code is used for every year’s Ball. Realizing that you are indeed an expert on relational databases, the chief of the Alumni Association is now asking you to extend the functionality of the original database. More specifically, she would like to be able to keep information about their various fundraising efforts. A fundraising effort involves mailing an invitation to a specially selected list of donors. Donations in the context of a specific fundraising effort can be distinguished from other donations by using a special code in the field "Purpose" which uniquely identifies the fundraising effort. For every fundraising effort, a different code is used. The chief of the AA would like to keep sufficient data in the donors database in order to get information, such as: i) What was the response rate to fundraising effort X (i.e. what percentage of alumni contacted have actually made any contributions)? ii) How many times did we send mailings to Mr. Y in the past two years? (We don’t want to target him too often, or else he might become annoyed with us). Task 5: Extend the design of the database that you designed in Task 2 so that the Alumni Association can keep information about their fundraising efforts. In your answer, show any new tables you might be adding to the database together with their fields. Also show the primary keys and possible foreign keys in each table. Write a short paragraph explaining your extension and modify your database schema in alumni.mdb Finally, and while you beginning to feel sorry you graduated from that particular institution, the AA chief is asking you one last favor: The AA has a Board of Trustees, which meets four times a year. Some of the Trustees are not very good about attending those meetings and AA would like to keep better track of who attends when. Task 6: Extend your database design so that the Alumni Association can keep information about their Board of Trustees meetings. The goal of the extension is to be able to tell how many meetings were attended by a given Trustee during a given time period. As in the previous tasks, briefly describe your extensions and incorporate them in alumni.mdb. You can assume that members of the Board of Trustees are also donors. Task 7: Write a query that outputs a list of all members of the Board of Trustees, together with the total number of their attendances in 2002. Your output should have the form: Last Name First Name Total attendances in 1997 After you complete all tasks, add the final version of your alumni.mdb database to the WinZip archive you created for Problem 1.