We're going to build a SQL database that will keep track of books from a fantasy series in a library. These types of books can get complex, with many characters that span many books in a series, or just appear in one book, and characters that are species other than human. We will have tables for: Characters
, Books
, Series
, Authors
, and Sub-Genres
. For a refresher on SQL syntax as you work through this lab, the W3Schools SQL Tutorial is a helpful reference, as well as the resources listed below.
- Become comfortable writing SQL statements to create tables that have complex relations with each other
- Understand and implement JOINs to write complex
SELECT
statements to query a database
Build out the schema for our Fantasy Library database:
- All tables must have a
PRIMARY KEY
on the id Series
have a title and belong to an author and a sub-genre- A
Sub-Genre
has a name Authors
have a nameBooks
have a title and year and belong to a seriesCharacters
have a name, motto, and species and belong to an author and a seriesBooks
have many characters and characters are in many books in a series. How do we accomplish this complex association? With a join table between Characters and Books. This join table (let's call it character_books) will just have -in addition to its primary key- two foreign key columns for the character and book ids. Each row in this join table acts as a relation between a book and a character.
Populate the database with the following:
-
2 series
-
2 sub-genres
-
2 authors
-
3 books in each series
-
8 characters
- 4 characters in each series
- of each of those 4, make 2 in all of the books in a series, and 2 in just 1 book in a series
- 4 characters in each series
-
Note you will need to insert values into your character_books join table
-
Feel free to make these up if you don't know any Fantasy series :)
Update the species of the last character in the database to "Martian" by writing an update statement in update.sql
.
In spec/querying_spec.rb
, complete the tests by writing the appropriate queries to satisfy the queries. Note that for this section, the database will be seeded with external data so don't expect it to reflect the data you added above.