MCS 275 Spring 2024 Homework 11¶

• Course Instructor: Emily Dumas

Deadline¶

This homework assignment must be submitted in Gradescope by Noon central time on Tuesday April 2, 2024.

Collaboration¶

Collaboration is prohibited, and while working on this you should only consult the resources (books, online, etc.) listed below.

Content¶

This assignment corresponds to Worksheet 11 and is about SQLite databases.

Resources you may consult¶

The materials you may refer to for this homework are:

• Most likely useful

  • Worksheet 11
  • Worksheet 11 Solutions - This link will work once the solutions are posted
  • Lecture 26 - databases (and a little matplotlib)
  • Lecture 27 - SQL queries
  • Lecture 28 - SQL CRUD example
  • Course sample code repo
    • Especially /databases/
• Allowed, but less likely to be useful:
  • Any of the MCS 275 lecture materials, worksheets, previous homework assignments, or solutions to these, if posted on the course Blackboard site.
  • Slides from any lecture of my MCS 260 course from Fall 2021.
  • Any lecture video posted on the MCS 275 Blackboard site
  • Any textbook listed on the course blackboard site under "Textbooks"

Point distribution¶

This homework assignment has two problems. The grading breakdown is:

What to do if you're stuck¶

Ask your instructor or TA a question by email, in office hours, or on discord.

Make sure you have the data¶

You will need the power plants database for problem 2. If you don't

• powerplants.sqlite

The only table in this database is called powerplants, and the columns present in that table are described at

• Lecture 27 Slide 10

2. More powerplants queries¶

Helpful tip: First, here's an SQL feature that you should know about. If you have a query that selects a bunch of rows, e.g.

SELECT * FROM available_beverages WHERE caffeine_mg_per_liter > 400;

then you might want to know how many rows it returns. Instead of fetching them all and counting them, it is better to have SQLite do the counting. You can do that by replacing the * or the list of requested columns with COUNT(*). For example

SELECT COUNT(*) FROM available_beverages WHERE caffeine_mg_per_liter > 400;

is a query that returns a single row, which has a single column, which contains an integer. That integer is the number of rows matching the SELECT statement.

Your task: Write a python script hwk11prob2.py that connects to the power plants sqlite database (powerplants.sqlite) and performs appropriate SQL queries in order to print answers to the following questions:

• A. What are the five oldest power plants in the database, in terms of the year they were commissioned? For each, list the name, the country, and the year it was commisioned (oldest first). Power plants for which no commissioning date is included should be excluded from consideration.
• B. How many nuclear power plants in the database are located in the southern hemisphere?
• C. What percentage of the power plants in the United Kingdom are wind power plants?
• D. What percentage of the total power generation capacity in the United Kingdom comes from wind power plants?

Also, please paste the output of your program at the bottom of the code as a sequence of comments.

Note: In this problem terms "nuclear power plant" and "wind power plant" always refer to the primary fuel used by a facility.

3. I always have at least 10 things to do¶

Remember the first task list sample application we wrote, which works in the terminal? (Don't confuse it with the more complex web-based one we're working on now.) It can be found at:

• tasks.py

It uses a database tasks.db containing a single table called tasks with this schema:

CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
desc TEXT,
complete INTEGER DEFAULT 0 );

Write a python script hwk11prob3.py that opens and modifies the database tasks.db as follows:

• Every task in the table is marked as not complete
• If there are fewer than 10 tasks in the table, additional random tasks (see below) are added until the total number in the table is 10.

Thus, you could run this script if you were preparing to experiment with tasks.py, since having a bunch of incomplete tasks in the database would provide a helpful starting point.

In the description above, a random task should be understood to mean one whose description has the form

VERB the PLURAL_NOUN

where VERB is selected at random from the following list (given as a list of Python string literals, for your convenience!)

[
    "Organize",
    "Update",
    "Compile",
    "Review",
    "Calculate",
    "Design",
    "Innovate",
    "Schedule",
    "Research",
    "Present",
    "Analyze",
    "Brainstorm",
    "Digitize",
    "Archive",
    "Prioritize",
    "Customize",
    "Reimagine",
    "Streamline",
    "Negotiate",
    "Facilitate"
]

and PLURAL_NOUN is similarly selected at random from the list

[
    "MCS 275 projects",
    "Taft hall water fountains",
    "water leaks in the south stairwell of SEO",
    "library electrical outlets",
    "rain gutters",
    "spreadsheets",
    "ideas",
    "proposals",
    "widgets",
    "coffee cups",
    "emails",
    "invoices",
    "reports",
    "strategies",
    "presentations",
    "budgets",
    "meetings",
    "deadlines",
    "diplomatic cables",
    "break room chairs"
]

The script should not produce any output in the terminal. Its only function is to modify the tasks table stored in tasks.db.

Revision history¶

• 2024-03-28 Initial publication