SQL for Data Scientists

APPENDIXAnswers to Exercises

Chapter 1: Data Sources

Answers

1. If the “Author Full Name” field is updated (overwritten) in the existing Authors table record for the author, then when a query is run to retrieve a list of authors and their books, all past books associated with the author will now be associated with the author's new name in the database, even if that wasn't the name printed on the cover of the book.

   If instead a new row is added to the Authors table to record the new name (leaving the existing books associated with the prior name), then there might be no way to know that the two authors, who now have different Author IDs, are actually the same person.

   There are solutions to this problem that include designing the database tables and relationships to allow multiple names per Author ID, with start and stop dates, or adding a field to the Authors table such as “prior Author ID” that associates an Authors table record with another record in the same table, if one exists.

   Understanding these relationships and when and how data is updated in the database you're querying is important for understanding and explaining the results of your queries.

2. One example might be tracking personal exercise routines. You could have a table of workout sessions and a table of exercises, which would be a many to many relationship: each workout could contain multiple exercises, and each exercise could be part of multiple workouts. If you included a table of workout session locations, that ...