Midterm Exam

The take-home exam is due Monday, April 13 (at midnight). Please submit via moodle and include:

  • A zip file containing your .db file, the code you used to generate the schema, and the code you used to populate the data
  • The questions you wrote that can be answered by querying your database
  • Responses to the reflection questions below.

Queries

Please include some queries that I can try to write for your database! These should include:

  • A case where COUNT can be used
  • A case where SUM can be used
  • A case where MIN or MAX might be useful
  • A case where GROUP BY can be used
  • One with HAVING
  • Equijoin and/or Theta join
  • Natural join

Data Generation

Two options:

  • https://smith.campusgenai.org/c/new?agent_id=agent_wQzkghtFdbEIRJv6YYIlc

Midterm Reflection Questions

Please answer the following questions, writing around one paragraph per question. Your responses should focus on the mini-project we have been working on in class, in which you worked in groups to design a database related to a shared group interest. You will be graded on your ability to reflect on your design process and on how different design decisions affected the way your final database represented the topic you were modeling.

  1. How did your plan for the database change over time, from your ER diagram to your final schema? What limitations or design challenges became apparent as you moved through the process?

  2. What kinds of real-world things did your database successfully capture? Identify 2–3 aspects of the domain that your schema handled well.

  3. What was harder to represent than you expected? Describe at least one idea, relationship, or kind of data that was more difficult to model than you initially thought.

  4. What assumptions did your schema make about the world? For example, did it assume that certain values were unique, always present, limited in number, or easy to categorize?

  5. What broke, or almost broke, when you tried to generate, insert, or work with data? How did those problems differ from your initial expectations?

  6. If you had to revise your schema, what would you change first, and why?

  7. What information is missing, constrained, or not encoded the way you would prefer because of SQLite’s storage model or type system? How did that affect your ability to encode the information relevant to your project?