CPSC 350

From today’s class:

1. The additional practice queries (and sample solutions)
2. The B&J console program:
   • 12:30pm version
   • 3:30pm version

Taking the time to go through the rest of the questions on item #1 on your own would be an excellent and praiseworthy idea.

Assignment #3 has been posted, and is due next Sunday Feb. 23rd at midnight. Good luck!

A quick shout out to John Database and GoldCannon for being the first to reach the “Beet It” level!

Trivia question for the masses: what’s different about “Beet it” than any of the other semester’s levels?

A couple students asked a question about Item 9 on the quiz: why is the answer 5, not 4? The tables in question were PETS:

+----------------------+----------+----------+
|        owner         |   dog    | numYears |
+----------------------+----------+----------+
| Laura Ingalls Wilder | Jack     | 5        |
| Veronica Mars        | Backup   | 3        |
| Robert Barone        | Chamsky  | 9        |
| Laura Ingalls Wilder | Bandit   | 2        |
| Fourth Doctor        | K9       | 4        |
| Sarah Jane Smith     | K9       | 8        |
| Raj Koothrappali     | Cinnamon | 5        |
| Emily Elizabeth      | Clifford | 6        |
+----------------------+----------+----------+

and CHARACTERS:

+-----------------------------+----------------------+-------+
|            show             |      character       | debut |
+-----------------------------+----------------------+-------+
| Little House on the Prairie | Laura Ingalls Wilder | 1974  |
| Clifford the Big Red Dog    | Emily Elizabeth      | 2000  |
| Doctor Who                  | Fourth Doctor        | 1974  |
| Doctor Who                  | Sarah Jane Smith     | 1973  |
| The Mandalorian             | Greef Karga          | 2019  |
+-----------------------------+----------------------+-------+

The query in question was this:

select count(*) from pets join characters on owner=character;

The students asked, “why doesn’t this give you 4 rows, since table rows must be unique and therefore Laura Ingalls Wilder can’t appear twice?”

But realize that this query isn’t a “select owner” or “select distinct(owner)“; rather, it’s a “select *“. So therefore, you’re counting the entire number of rows returned by the join, not the number of distinct owner names (or any other attribute).

If you get rid of the count(*) and just use * to see the whole table of results, you get:

+----------------------+----------+----------+-----------------------------+----------------------+-------+
|        owner         |   dog    | numYears |            show             |      character       | debut |
+----------------------+----------+----------+-----------------------------+----------------------+-------+
| Laura Ingalls Wilder | Jack     | 5        | Little House on the Prairie | Laura Ingalls Wilder | 1974  |
| Laura Ingalls Wilder | Bandit   | 2        | Little House on the Prairie | Laura Ingalls Wilder | 1974  |
| Fourth Doctor        | K9       | 4        | Doctor Who                  | Fourth Doctor        | 1974  |
| Sarah Jane Smith     | K9       | 8        | Doctor Who                  | Sarah Jane Smith     | 1973  |
| Emily Elizabeth      | Clifford | 6        | Clifford the Big Red Dog    | Emily Elizabeth      | 2000  |
+----------------------+----------+----------+-----------------------------+----------------------+-------+

As you can see: 5 rows, with no duplicate rows.

If you miss points on a quiz question that you clearly had correct, but simply entered in a different format than I was expecting, please don’t just accept the loss and sulk. Instead, tell me about it.

For example, suppose the answer to some question was:

stephen

but for your answer you entered:

"stephen"

Depending on the exact nature of the question, this is quite possibly just as “correct” an answer as the one the quiz was looking for, yet it will be marked wrong because it wasn’t expecting you to put quotation marks.

All such answers should be regraded, and you should tell me about them. (Note that this is not an open invitation for you to tell be about the individual items you legitimately missed because you put the wrong answer. Only the ones where your obvious intent was to give the correct answer, but differences in formatting choices got in the way.)

I thought this would have been obvious, I guess, but in case not: no, you are not permitted to use the SQLite command line while taking the quiz. (If you think about it, the quiz would be pointless if you could do that, since anyone who knew how to type could get the questions right, even if they knew nothing about databases. The quiz is testing your understanding of the material in class, not your ability to type.)

Unfortunately, I won’t be able to make my usual 11:30am-12:30pm office hours on Tuesday, Feb. 18th. I will be there for the 2-3:30pm block, though.

For quizzes, you are allowed to use all handouts from class, any “cheat sheets” or other things posted on the class website, etc. Basically, anything written down that you want to use is okay. The only things that are not okay are programs (like the Python command line, or ChatGPT.)

From today’s class:

• Sample solutions to the first nine queries on the page I handed out
• the beginnings of the ice cream purchasing console app we started on

From today’s class, the queries against the B&J database that (1) reviewed selects, wheres, froms, Cartesian products, and joins, and (2) introduced group bys, havings, order bys, limits, and offsets. (Whew!)

At the usual time, at the same link we’ll be using for class (see Canvas announcement).

Will we have class tomorrow? Yes! See Zoom link in Canvas announcement.

Is class mandatory? No, class is never mandatory. You are big boys and girls, and can come and go whenever you choose. If you don’t think you will benefit from being there, you are free to skip any class at all, including tomorrow’s.

If I don’t come, should I get the notes from a classmate? Yes, as always.

What if I don’t have the proper Zoom equipment? This situation should be exceedingly rare in the post-Covid age, but I will attempt to record tomorrow’s lecture and post it on Canvas for anybody who isn’t able to “come to class.”

Quiz #2 has been posted, and is due Saturday, Feb 15th at midnight. It’s timed at 45 minutes, and covers all the material from class and readings up through and including Tuesday Feb 11th’s class. Good luck!

Really? Where I come from there at least have to be a few flakes falling first.

Anyway, we’re holding to a good pace so far this semester, and I do not want to fall behind. And given past UMW history, my bet is that class is canceled for more than just tomorrow. So here’s the official dealio:

1. Enjoy your day off tomorrow. Fair’s fair. But:
2. If class is also canceled for Wednesday and/or Thursday, we will have class those days at the appointed time on Zoom. If you need to bust out your webcam or dust off your lens, you’ve got over 24 hours to do that.

See you guys Wednesday, regardless!

From today’s class, the SQL code to recreate our bootstrapped database:

• early class version
• late class version

From today’s classes:

• The SQL code (first section)
• The SQL code (second section)
• The handout for which I ran out of copies

Assignment #2 has been posted, and is due next Thursday at midnight. Note carefully: this assignment will probably take a medium amount of time if your assignment #1 was pretty clean, but a large amount of time if your assignment #1 had lots of mistakes. (This is because you’ll need to fix all those mistakes — or use Stephen’s solution instead — for assignment #2.)

Here’s my solution to assignment #1. If you got less than 35XP or so for this assignment, I strongly suggest you carefully look over my solution, and perhaps use it instead of your own as a basis for assignment #2.

Quiz #1 has been posted to Canvas, and is due on Friday at midnight. It is open-book and open-notes, closed-other-humans and closed-AI-assistants-such-as-ChatGPT, and timed at 60 minutes.

Good luck!

Abe, a pilot in our class, just now told me that there is in fact an “AID-type thing,” which is called a tail number, and it’s written on the fuselage. Call it what you wish on your homework!

Walk-in tutoring is available for this class (and others)!

• Kenzie: Wednesdays, 6-8pm, Farmer 025
• Matt: Thursdays, 6-8pm, Farmer 026

This may seem inconsistent, but it’s the law in Stephenworld: your database does need to know who flew last October 29th’s United 277, but it does not need to know what runway United 277 might have had reserved on that date — all that it needs to know about runway reservations is what runways are currently reserved. (In other words, information about flight crew assignments must be kept in perpetuity, whereas your database only needs to know what current flight has each runway reserved, if any.)

Clarifications around crew members:

1. Crew assignments are not normally the same from day to day. (If Charlie/Julie were the pilot/co-pilot on this morning’s United 678, they are very likely not going to be the pilot/co-pilot on tomorrow’s United 678.)
2. All crew members who are certified to fly a type of plane can be either the pilot, or the co-pilot, for a flight that uses that type of plane. (There’s no such thing as “being certified to co-pilot a Boeing 747, but not certified to pilot it.”)
3. Every crew member is a pilot on some flights, and a co-pilot on others. (They’re rotated so they don’t get too fatigued.)
4. Your database does need to know who the pilot is, and who the co-pilot is, for a given day’s flight, not merely “who the two crew members are.” If I go to your database and ask “who was the co-pilot for yesterday’s American 113?” your database is not allowed to respond, “well, Sallie and Jeff were the two crew members, but I’m really not sure which one was the pilot and which was the co-pilot.” (Hint: I think you’ll want two different one-to-many associations in this part of your design, not a single many-to-many association.)

I don’t know if there is something like this in reality (I’m sure there is, though) but we’re going to say that all physical airplanes have an “AID” (“airplane ID”) which is globally unique.

Clarification: when I say to “email me” (with questions, to turn in an assignment, etc.) I really do mean “email me” (to stephen _AT_ umw _DOT_ edu), not “send me a Canvas message” (which I normally do not see and do not read).

Assignment #1 has been posted, and is due a week from today at 5pm (not midnight) to the “350” manila folder hanging outside my office door. If you have any uncertainty about what any of the requirements mean, please be sure to ask! If you don’t ask, and misinterpret a requirement as a result, you’ll miss out on points that you might otherwise have received.

Procrastinate on this to your peril!

You can earn up to +2XP per week by spending 30 minutes (per week) completing codingbat.com problems. All you need to do to claim these points each week is send me an email on Friday of the given week with subject line “CPSC/DATA 350 codingbat XP“. In the body of the email, you should type “I spent 30 minutes this week working Python exercises on codingbat.com,” followed by the Honor Pledge and your full name as a signature.

Happy coding!

Your first graded assignment of the semester — getting used to Python or brushing out the cobwebs — has been posted, and is due Tuesday, Jan 21st at midnight. Send questions!

Okay everybody, it’s time to earn your very first XP (experience point) of the semester, which is not only admirable in itself but is also required in order to earn any other XP at all!

All you need to do is head over to the Scoreboard (see link at top of page) and add yourself as a new student. Be sure to choose a screen name that is super secret, that you have never used before, and that you will not reveal to anyone.

Greetings, Earthlings, and welcome to the spring 2025 version of CPSC/DATA 350: Applications of Databases with Stephen! This site is gonna be hip-hoppin’ with all kinds of great stuff real soon, so stay tuned!