Schema and Query Design lesson notes

01-schema-query.md

Schema and Query Design

Objectives

• Explain what relational databases are
• Descibe what a schema is
• Describe what a query is
• Use MySQL to create schemas
• Use MySQL to run queries
• Build and run join queries in MySQL

Guiding Questions

• Explain what a Relational Databases is in your own words.

  Your answer...

• How do you create and connect to a new database in MySQL?

  Your answer...

• What is a schema?

  Your answer...

• How do you create a schema for a pet table in MySQL? (a pet should include columns for id, name, species, friendly)

  Your answer...

• What is a query?

  Your answer...

• How do you create a query to insert a new pet?

  Your answer...

• What does a join refer to in a relational database?

  Your answer...

Exercise

Galvanize Movie Database:

• Read through the following sql file: https://gist.githubusercontent.com/Shurlow/07684cf483166586d6f5fb477bc67c98/raw/4f4954947316dccbff5a7618795dcc5a4e3ee2f6/01-SQL-Joins.md

• Connect to MySQL and create a new database named movies_dev

• Run the following command outside mysql repl to create the necessary tables and insert seed data: curl https://gist.githubusercontent.com/Shurlow/405013ac8a2f3487475dc8db78b5195e/raw/605611c55f3020a8b717c428327a9ce975c55965/movies.sql | mysql -u root -p movies_dev;

• Use Inner Joins:

  • Write a join query to get all movies with awards
  • Write a join query to get the actors name, movies title, and role.
  • Using the query from above, select only name, title & role from The Princess Bride.
  • Write query that displays the follow rows.

           title        |      released_at       | score |     actor_name     |           role
    --------------------+------------------------+-------+--------------------+--------------------------
     Pulp Fiction       | 1994-10-13 17:00:00-07 |   8.9 | John Travolta      | Vincent Vega
     Pulp Fiction       | 1994-10-13 17:00:00-07 |   8.9 | Amanda Plummer     | Honey Bunny / Yolanda
     Pulp Fiction       | 1994-10-13 17:00:00-07 |   8.9 | Tim Roth           | Pumpkin / Ringo
     Pulp Fiction       | 1994-10-13 17:00:00-07 |   8.9 | Samuel L. Jackson  | Jules Winnfield
     The Princess Bride | 1987-10-08 17:00:00-07 |   8.1 | Robin Wright       | The Princess Bride
     The Princess Bride | 1987-10-08 17:00:00-07 |   8.1 | Cary Elwes         | Westley
     The Princess Bride | 1987-10-08 17:00:00-07 |   8.1 | Chris Sarandon     | Prince Humperdinck
     Frozen             | 2013-11-26 16:00:00-08 |   7.6 | Idina Menzel       | Elsa
     Frozen             | 2013-11-26 16:00:00-08 |   7.6 | Kristen Bell       | Anna
     X-Men: Apocalypse  | 2016-05-26 17:00:00-07 |   7.4 | Jennifer Lawrence  | Raven / Mystique
     X-Men: Apocalypse  | 2016-05-26 17:00:00-07 |   7.4 | Michael Fassbender | Erik Lehnsherr / Magneto
     X-Men: Apocalypse  | 2016-05-26 17:00:00-07 |   7.4 | James McAvoy       | Professor Charles Xavier
    

Resources

• https://devhints.io/mysql

movies.sql

CREATE TABLE movies (
  id serial PRIMARY KEY,
  title text,
  duration integer,
  rating varchar(10),
  genre text,
  is_3d boolean NOT NULL,
  released_at datetime,
  score decimal(3, 1)
);

CREATE TABLE plots (
  id serial PRIMARY KEY,
  movie_id integer REFERENCES movies(id),
  summary text
);

CREATE TABLE awards (
  id serial PRIMARY KEY,
  movie_id integer REFERENCES movies(id),
  kind text,
  name text
);

CREATE TABLE actors (
  id serial,
  name text,
  birthed_at datetime
);

CREATE TABLE actors_movies (
  id serial,
  actor_id integer REFERENCES actors(id),
  movie_id integer REFERENCES movies(id),
  role text
);

INSERT INTO movies (title, duration, rating, genre, is_3d, released_at, score)
VALUES ('Frozen', 102, 'PG', 'Animation', TRUE, '2013-11-27 00:00:00', 7.6);

INSERT INTO movies (title, duration, rating, genre, is_3d, released_at, score)
VALUES ('X-Men: Apocalypse', 144, 'PG-13', 'Action', TRUE, '2016-05-27 00:00:00', 7.4);

INSERT INTO movies (title, duration, rating, genre, is_3d, released_at, score)
VALUES ('The Princess Bride', 98, 'PG', 'Adventure', FALSE, '1987-10-09 00:00:00', 8.1);

INSERT INTO movies (title, duration, rating, genre, is_3d, released_at, score)
VALUES ('Pulp Fiction', 154, 'R', 'Crime', FALSE, '1994-10-14 00:00:00', 8.9);

INSERT INTO plots (movie_id, summary)
VALUES (1, 'Anna, a fearless optimist, sets off on an epic journey - teaming up with rugged mountain man Kristoff and his loyal reindeer Sven - to find her sister Elsa, whose icy powers have trapped the kingdom of Arendelle in eternal winter. Encountering Everest-like conditions, mystical trolls and a hilarious snowman named Olaf, Anna and Kristoff battle the elements in a race to save the kingdom. From the outside Anna''s sister, Elsa looks poised, regal and reserved, but in reality, she lives in fear as she wrestles with a mighty secret-she was born with the power to create ice and snow. It''s a beautiful ability, but also extremely dangerous. Haunted by the moment her magic nearly killed her younger sister Anna, Elsa has isolated herself, spending every waking minute trying to suppress her growing powers. Her mounting emotions trigger the magic, accidentally setting off an eternal winter that she can''t stop. She fears she''s becoming a monster and that no one, not even her sister, can help her.');

INSERT INTO plots (movie_id, summary)
VALUES (2, 'Since the dawn of civilization, he was worshipped as a God. Apocalypse, the first and most powerful mutant from Marvel''s X-Men universe, amassed the powers of many other mutants, becoming immortal and invincible. Upon awakening after thousands of years, he is disillusioned with the world as he finds it and recruits a team of powerful mutants, including a disheartened Magneto, to cleanse mankind and create a new world order, over which he will reign. As the fate of the Earth hangs in the balance, Raven with the help of Professor X must lead a team of young X-Men to stop their greatest nemesis and save mankind from complete destruction.');

INSERT INTO plots (movie_id, summary)
VALUES (3, 'A kindly grandfather sits down with his ill grandson and reads him a story. The story is one that has been passed down from father to son for generations. As the grandfather reads the story, the action comes alive. The story is a classic tale of love and adventure as the beautiful Buttercup, engaged to the odious Prince Humperdinck, is kidnapped and held against her will in order to start a war, It is up to Westley (her childhood beau, now returned as the Dread Pirate Roberts) to save her. On the way he meets a thief and his hired helpers, an accomplished swordsman and a huge, super strong giant, both of whom become Westley''s companions in his quest.');

INSERT INTO plots (movie_id, summary)
VALUES (4, 'Jules Winnfield and Vincent Vega are two hitmen who are out to retrieve a suitcase stolen from their employer, mob boss Marsellus Wallace. Wallace has also asked Vincent to take his wife Mia out a few days later when Wallace himself will be out of town. Butch Coolidge is an aging boxer who is paid by Wallace to lose his next fight. The lives of these seemingly unrelated people are woven together comprising of a series of funny, bizarre and uncalled-for incidents.');


INSERT INTO awards (movie_id, kind, name)
VALUES (1, 'Oscar', 'Best Animated Feature Film of the Year');

INSERT INTO awards (movie_id, kind, name)
VALUES (1, 'Oscar', 'Best Achievement in Music Written for Motion Pictures, Original Song');

INSERT INTO awards (movie_id, kind, name)
VALUES (3, 'Saturn Award', 'Best Fantasy Film');

INSERT INTO awards (movie_id, kind, name)
VALUES (3, 'Saturn Award', 'Best Costumes');

INSERT INTO awards (movie_id, kind, name)
VALUES (4, 'Oscar', 'Best Writing, Screenplay Written Directly for the Screen');


INSERT INTO actors (name, birthed_at)
VALUES ('Kristen Bell', '1980-07-18 00:00:00');

INSERT INTO actors (name, birthed_at)
VALUES ('Idina Menzel', '1971-05-30 00:00:00');

INSERT INTO actors (name, birthed_at)
VALUES ('James McAvoy', '1979-04-21 00:00:00');

INSERT INTO actors (name, birthed_at)
VALUES ('Michael Fassbender', '1977-04-02 00:00:00');

INSERT INTO actors (name, birthed_at)
VALUES ('Jennifer Lawrence', '1990-08-15 00:00:00');

INSERT INTO actors (name, birthed_at)
VALUES ('Cary Elwes', '1962-10-26 00:00:00');

INSERT INTO actors (name, birthed_at)
VALUES ('Robin Wright', '1966-04-08 00:00:00');

INSERT INTO actors (name, birthed_at)
VALUES ('Chris Sarandon', '1942-07-24 00:00:00');

INSERT INTO actors (name, birthed_at)
VALUES ('John Travolta', '1954-02-18 00:00:00');

INSERT INTO actors (name, birthed_at)
VALUES ('Samuel L. Jackson', '1948-12-21 00:00:00');

INSERT INTO actors (name, birthed_at)
VALUES ('Tim Roth', '1961-05-14 00:00:00');

INSERT INTO actors (name, birthed_at)
VALUES ('Amanda Plummer', '1957-03-23 00:00:00');


INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (1, 1, 'Anna');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (2, 1, 'Elsa');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (3, 2, 'Professor Charles Xavier');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (4, 2, 'Erik Lehnsherr / Magneto');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (5, 2, 'Raven / Mystique');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (6, 3, 'Westley');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (7, 3, 'The Princess Bride');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (8, 3, 'Prince Humperdinck');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (9, 4, 'Vincent Vega');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (10, 4, 'Jules Winnfield');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (11, 4, 'Pumpkin / Ringo');

INSERT INTO actors_movies (actor_id, movie_id, role)
VALUES (12, 4, 'Honey Bunny / Yolanda');

movies_queries.sql

SELECT * FROM movies INNER JOIN awards ON movies.id = awards.movie_id;

SELECT name, role, title FROM actors INNER JOIN actors_movies ON actors.id = actors_movies.actor_id INNER JOIN movies ON movies.id = actors_movies.movie_id;

SELECT name, role, title FROM actors INNER JOIN actors_movies ON actors.id = actors_movies.actor_id INNER JOIN movies ON movies.id = actors_movies.movie_id WHERE title='The Princess Bride';

pets.sql

DROP TABLE IF EXISTS pets CASCADE;
DROP TABLE IF EXISTS toys;
DROP TABLE IF EXISTS owners CASCADE;
DROP TABLE IF EXISTS owners_pets;

CREATE TABLE pets (
  id serial PRIMARY KEY,
  name varchar(255),
  species varchar(255),
  age integer
);

CREATE TABLE toys (
  id serial PRIMARY KEY,
  type varchar(255),
  pet_id integer NOT NULL REFERENCES pets ON DELETE CASCADE
);

CREATE TABLE owners (
  id serial PRIMARY KEY,
  name varchar(255)
);

CREATE TABLE owners_pets (
  id serial PRIMARY KEY,
  owner_id integer NOT NULL REFERENCES owners ON DELETE CASCADE,
  pet_id integer NOT NULL REFERENCES pets ON DELETE CASCADE
);

INSERT INTO pets (name, species, age) VALUES ('Zane', 'lizard', 12);
INSERT INTO pets (name, species, age) VALUES ('Pesto', 'dog', 3);
INSERT INTO pets (name, species, age) VALUES ('Mimi', 'pig', 5);

INSERT INTO toys (type, pet_id) VALUES ('just a rock', 1);
INSERT INTO toys (type, pet_id) VALUES ('chewy bone', 2);
INSERT INTO toys (type, pet_id) VALUES ('plush bear', 2);

INSERT INTO owners (name) VALUES ('Terry');
INSERT INTO owners (name) VALUES ('Jo');
INSERT INTO owners_pets (owner_id, pet_id) VALUES (1, 1);
INSERT INTO owners_pets (owner_id, pet_id) VALUES (1, 2);
INSERT INTO owners_pets (owner_id, pet_id) VALUES (2, 2);