CSC321.01 2016S, Class 10: Database Design, Revisited ===================================================== _Overview_ * Preliminaries. * Admin. * Upcoming Work. * Extra Credit. * Questions. * Bad Spuds, Continued. Preliminaries ------------- ### Admin ### Upcoming Work * SaaSBook 8 for Friday * Reading Journal for Friday (due Thursday at 8pm) * Subject: Reading Journal for Class 12 (Your Name) * How does Fox and Patterson's presentation of test-driven development compare to your prior experiences with unit testing? * Choose the most confusing idea from 8.2 - 8.6. Make a serious attempt to explain this idea in your own words. What questions remain? * Choose the most confusing idea from 8.7 - 8.9. Make a serious attempt to explain this idea in your own words. What questions remain? * What do you see as the relationship(s) between BDD and TDD? Which do you prefer? * What do you see as the most important or enduring concept in this chapter overall? * Citation: Questions are based on journal questions from JLND. ### Good Things to Do #### Academic * Misc. department stuff * Thursday convo: Hilary Mason '00 * Thursday extra: Hilary Mason '00 #### Peer * Track and Field Saturday at Wartburg, in Waverly, IA ### Questions Bad Spuds, A Preliminary Solution --------------------------------- Here's one solution to Bad Spuds. We'll take a look at it. Tables * A _Movie_ table with * Title * Rating * Release Date * Description * Reviews * A _Reviewer_ table with * Name * Description * Reviews * A _Reviews_ table with * Movie Title * Score * Reviewer Name * Description. Relationships * a one-to-many relationship between movies and reviews * a one-to-many relationship between reviewers and reviews Observation * Is a review just a more complex many-many relationship between reviewers and movies? Analyzing the Preliminary Solution ---------------------------------- * Is this DRY? Why or why not? * What types would you associate with these various fields? * What other fields would you want in each table? * What other tables would you want? * What restrictions would you place on various relationships? (E.g., can the same reviewer review a movie more than once?) Observations * The movie title appears in two places. * Problem-Ambiguity: Which movie do you mean if two movies have the same title? * Problem-Updates: If you need to make a correction (e.g., "Star Wars" to "Star Wars Episode IV: A New Hope") you may need to do so in multiple places. * Problem-Overhead: Storing a string requires much more effort than storing an integer. * Problem-Efficiency: Comparing two strings requires much more effort than comparing two integers. * We have bi-directional relationships: Movies reference reviews, reviews reference movies. * Is it faster to have a list of reviews in the movies table? * Some DBMS's do not support lists. * Most DBMS's support fast lookup in relations by external id if you tell them you'll be doing that. * Issue: How do you update the average score * Program logic! When the reviews table gets updated, we also update the movie table. * Program logic! We keep an last updated field. When someone queries the average score, and it's been more than X time units since the average score was last updated, we recompute. * Database design: Might a review be of multiple movies? (One review per movie might be wrong). * Administrative decision. Reviewers should only review one movie at a time. * We should keep track of directors, but just saying "String" is ambiguous. Add another table. * We use identifiers for internal reference * We should keep track of actors. A new table for actors, a many-many relationship. We'll represent that relationship with another table, which we'll call _Roles_. * Our MVP isn't very M. A Revised Solution ------------------ Revised Tables * A _Movies_ table with * Id: Identifier * Title: String * Rating: One of (G, PG, PG13, R, X, Unrated, Deadpool) * Average Score: Real (computed?) * Release Date: Date * Description: Text * Director: Identifier references Directors table. * Runtime: Integer (minutes) * A _Reviewers_ table with * Id: Identifier (integer, for fast lookup) * Email: Email * Username: String (e.g., "wickedawesome321guy") * Name: String (e.g., "Adam The Monroe") * Description: Text * Password: Hashed string * DOB: Date * Reputation: Real (computed) * Location: String (not checked; you can write 'Cyberspace') * A _Reviews_ table with * Id: Identifier * Movie: Identifier references Movie table * Reviewer: Identifier references Reviewer table * Score: Number (0-10) * Description: Text * A _Directors_ table with * Id: Identifier * LName: String * FName: String * DOB: Date * An _Actors_ table with * Id: Identifier * LName: String * FName: String * DOB: Date * Height: Integer in centimeters * A _Roles_ table with * Actor: Identifier references _Actors_ * Movie: Identifier references _Movies_ * Character: String * OscarCategory: One of "Leading Actor, Leading Actress, Supporting Actor, Supporting Actress, Supporting Animal" Revised Relationships * a one-to-many relationship between movies and reviews * a one-to-many relationship between reviewers and reviews Behavior-Driven Development --------------------------- * BDD is an agile approach to software development * We focus on the expected behavior of the application * We write "tests" in a language understood by the programmer and the client * They are tests that take the form of user stories. * Like most good tests, they are automated. * Hierarchical, often with three levels * Feature ("As a movie buff, I want to be able to search movies by the director's name, so that I can make sure that I see all of my favorite director's movies") * Scenarios: Particular examples ("In a database containing 'Star Wars IV', if I search for movies by George Lucas, I should find 'Star Wars IV'") * Steps: The basics of achieving the scenario ("I enter "George Lucas" on the search page ...")