What are the four ways we can interact with data?
Draw out what your schema (structure of your tables and columns) would be for the following domains. Consider what tables are needed, what columns belong on which tables, and where the foreign keys belong.
- Books and Authors where each book has a single author. Books should have a title and authors should have a name
Q: Write the SQL to find all books written by a certain author given the author's id.
- Books and Authors where each book can have one or multiple authors. Books should have a title and authors should have a name
Q: Write the SQL to find all books written by a certain author given their name
- Twitter where Tweets can have Tags (i.e. '#fun', '#hottake', '#tbt'). A tweet can have multiple tags, many tweets can be tagged with the same tag. Tweets have a message and user_id; tags have a name.
Q: Write the SQL to find all the tweets tagged '#tbt'
- After completing the questions above, is there a rule you can determine about which table the foreign key belongs on given two associated tables?