BU 453 Unit 6 joins and aggregation queries example

A Total That Came Back $1,375 Too Large, and Four Joins That Matched Their Prediction: Joins and Aggregation Queries for a Composite Boise Music School

Student Name

Business, Herzing University

BU 453: Database Management

Instructor Name

March 25, 2026

A Total That Came Back $1,375 Too Large, and Four Joins That Matched Their Prediction: Joins and Aggregation Queries for a Composite Boise Music School

Predict, Then Run

These queries combine tables in the test database of Harmony Hill Music School, the invented Idaho lesson school this course follows. Each item names the relationship its statement crosses, predicts from the model how many rows should come back, runs the statement and compares. The model from the second unit makes the predictions possible: if an enrollment belongs to exactly one teacher, then joining enrollments to teachers cannot produce more rows than there are enrollments. When a result disagrees with the prediction, the disagreement is treated as a defect in the statement, not as a discovery about the business.

Beaulieu (2020) explains that a join produces one output row for every matching pair of rows, so joining across a one-to-many relationship repeats the rows on the one side. That behavior is correct and useful, but it silently inflates any total taken from the repeated side, which is the defect this unit hunts.

Join 1: Teaching Minutes per Teacher

Question: how many minutes a week is each teacher scheduled to teach this term, including any teacher with no students? Relationship crossed: teacher teaches enrollments, one to many, optional on the teacher's side. Prediction: 14 rows, one per teacher, since the question asks for every teacher. Statement: SELECT t.last_name, COALESCE(SUM(e.lesson_minutes), 0) AS weekly_minutes FROM teacher t LEFT JOIN enrollment e ON e.teacher_id = t.teacher_id AND e.term_id = 7 GROUP BY t.teacher_id, t.last_name ORDER BY weekly_minutes DESC; Result: 14 rows, from 720 minutes down to 0 for the cello teacher hired in February. Matches prediction. An inner join returned 13 rows and dropped her, which would have told the scheduler she did not exist. Placing the term condition in the join clause rather than in a WHERE clause is also deliberate: in a WHERE clause it would have removed her row again, because her missing enrollment has no term.

Joins 2 and 3: Rooms and Rentals

Question 2: how many lessons does each room host on each weekday? Relationship crossed: room hosts enrollments. Prediction: no more than 54 rows, nine rooms by six days, and the lesson counts must sum to 52. Result: 41 rows, one for each room and day that has lessons, summing to 52. Matches. An inner join is right here, since the question concerns lessons that exist.

Question 3: what monthly rental income does each instrument type bring in from current rentals? Relationship crossed: instrument is out on rentals, one to many. Prediction: one row per type with at least one open rental; the total must equal the sum of monthly fees on the 12 open rentals, $486. Result: five rows totaling $486. Matches.

Join 4: The Total That Arrived Too Large

Question: how much has been invoiced to each billing account, and how much paid? Relationships crossed: account receives invoices, one to many; invoice is settled by payments, one to many, optional. Prediction: the invoiced amounts must add to $9,840, the sum of all 31 invoices checked directly.

The first statement joined invoices to payments with a left join and summed invoice amounts and payment amounts by account. It returned invoiced totals adding to $11,215, which is $1,375 more than the invoices contain. The row count explained why: the join produced 37 rows from 31 invoices, because six invoices had been paid in two installments and each appeared twice, carrying its full amount both times. Those six invoices total exactly $1,375.

The fix aggregates payments to one row per invoice before joining: a subquery sums payments by invoice, and the invoices are joined to that summary, one row each. The corrected statement returned invoiced totals of $9,840 and paid totals of $8,556, leaving $1,284 open, which matches the five overdue invoices found in the single-table unit. The first statement would have overstated what families owe by nearly 14 percent, and the report would have looked entirely reasonable.

Where the Predictions Came From

Each prediction came from two sources: the cardinalities in the relationship register and a count taken from a single table. The register says an enrollment has exactly one teacher, so a join from enrollments to teachers can never return more rows than there are enrollments; the single-table count supplies the number. When the two are combined before a statement is written, a wrong result announces itself. Without them, the inflated invoice total in Join 4 would have looked like any other figure. The habit costs a minute per query and turns the model from a drawing made early in the term into a working tool for checking every later result. It also makes reviews faster, since a reviewer can check the prediction and the row count without reading the statement at all.

Join 5: Students Who Both Take Lessons and Rent

Question: which enrolled students also rent an instrument, for a family newsletter offer? Relationships crossed: student holds enrollments and takes rentals, both one to many. Prediction: at most 11 students, the number of distinct students with open rentals. The first statement joined enrollments to rentals on the student and returned 13 rows, more than the prediction allows. Three renting students take two instruments, so each appeared twice. Rewritten with EXISTS, asking for each enrolled student whether any open rental exists, the statement returned 10 students. One renting student has no enrollment this term, which is why the answer is 10, not 11. Hoffer et al. (2019) recommend testing for existence rather than joining when the question is whether a related row exists, and this case shows why.

Every result in this unit was checked against a total or count established independently, and the PostgreSQL documentation's description of join behavior was the reference for each choice of join type (PostgreSQL Global Development Group, 2024). The school, its families and every total are invented for the course.

References

Beaulieu, A. (2020). Learning SQL: Generate, manipulate, and retrieve data (3rd ed.). O'Reilly Media.

Hoffer, J. A., Ramesh, V., & Topi, H. (2019). Modern database management (13th ed.). Pearson.

PostgreSQL Global Development Group. (2024). PostgreSQL 16 documentation. https://www.postgresql.org/docs/16/