CS377: Database Design - The SQL Join (100 Points)

Assignment Goals

The goals of this assignment are:
  1. To read and interpret relational models for database systems according to specifications

Background Reading and References

Please refer to the following readings and examples offering templates to help get you started:

The Assignment

Purpose

Joins are the mechanism that makes a normalized database usable: once we split data into multiple tables to eliminate redundancy and anomalies, joins are how we reassemble it to answer questions. Practicing joins on paper — predicting exactly which rows appear in the result before running anything — is how database professionals debug queries that return too many rows, too few rows, or unexpected NULLs. This assignment builds that prediction skill.

Task

Complete Exercises 1 and 2 in Chapter 10 of the Database Design textbook.

For each exercise:

  1. Read the tables first. Identify the join column(s) shared between the tables, and note any rows that will not find a match on the other side — these unmatched rows are what distinguish the join types.
  2. Write the SQL statement for the requested join, using explicit INNER JOIN / LEFT JOIN / RIGHT JOIN / FULL OUTER JOIN ... ON ... syntax rather than comma-separated tables.
  3. Show the result table row by row, including NULL values in unmatched columns where an outer join preserves a row.
  4. Explain in one or two sentences why each unmatched row was kept or dropped for that join type.

If you are unsure how to trace a join by hand, work through the step-by-step walkthrough in the SQL Join Activity first — it traces an inner, left, right, and full outer join on the same pair of small tables.

A common pitfall: rows with NULL in the join column match nothing — not even other NULLs. Watch for these when deciding which rows pair up.

Criteria

Your submission is complete when you can check off each of the following:

  • Each join is written in explicit JOIN ... ON ... syntax and names the correct join type for the question asked.
  • Each result table contains exactly the rows the join produces — no missing matches, no invented rows.
  • Unmatched rows preserved by outer joins show NULL in the columns from the other table.
  • For every row that appears in one join type’s result but not another’s, your explanation identifies which side of the join it came from and why it was kept or dropped.
  • Your README answers all bolded questions on this page.

Exporting your Project for Submission

When you’re done, write a README for your project, and save all your files, before exporting your project to ZIP. In your README, answer any bolded questions presented on this page.

Submission

In your submission, please include answers to any questions asked on the assignment page, as well as the questions listed below, in your README file. If you wrote code as part of this assignment, please describe your design, approach, and implementation in a separate document prepared using a word processor or typesetting program such as LaTeX. This document should include specific instructions on how to build and run your code, and a description of each code module or function that you created suitable for re-use by a colleague. In your README, please include answers to the following questions:
  • Describe what you did, how you did it, what challenges you encountered, and how you solved them.
  • Please answer any questions found throughout the narrative of this assignment.
  • If collaboration with a buddy was permitted, did you work with a buddy on this assignment? If so, who? If not, do you certify that this submission represents your own original work?
  • Please identify any and all portions of your submission that were not originally written by you (for example, code originally written by your buddy, or anything taken or adapted from a non-classroom resource). It is always OK to use your textbook and instructor notes; however, you are certifying that any portions not designated as coming from an outside person or source are your own original work.
  • Approximately how many hours it took you to finish this assignment (I will not judge you for this at all...I am simply using it to gauge if the assignments are too easy or hard)?
  • Your overall impression of the assignment. Did you love it, hate it, or were you neutral? One word answers are fine, but if you have any suggestions for the future let me know.
  • Using the grading specifications on this page, discuss briefly the grade you would give yourself and why. Discuss each item in the grading specification.
  • Any other concerns that you have. For instance, if you have a bug that you were unable to solve but you made progress, write that here. The more you articulate the problem the more partial credit you will receive (it is fine to leave this blank).

Please refer to the Style Guide for code quality examples and guidelines.