You should already have MySQL installed from last week but if you don't, please install it now.
We need to alter a setting in our cloud database in order to import the dataset that we need. This setting has to do with "triggers" which we have not learned about directly but that you will look for more practice with later.
-
Navigate to cloud.google.com and make sure you are logged in.
-
Navigate to your SQL instance (Cloud SQL)
-
Under the "configuration" tab on the right side of your screen, select "edit configuration"
-
Under "Enable auto backups" DESELECT "Enable Point-in-Time Recovery"/"enable binary logging"
-
Save and restart the instance
We are going to use a sample schema given to use by MySQL.
- Download the zip directory and extract it
- https://dev.mysql.com/doc/sakila/en/sakila-installation.html
- The DB is called "sakila" under the Example Databases section
- cd into that directory
- Likely
cd ~/Downloads/sakila-db
- Run the connect command followed by
< sakila-schema.sql
to load that database
mysql -u root -h <HOST IP FROM WORKBENCH> -p < sakila-schema.sql
-
After the operation is complete (may take a couple mins) you should have automatically been exited from the
mysql
command -
Pull up MySQL Workbench so that we can work with a familiar interface
-
You should see an "sakila" database on the left hand side
-
Double-click that database
-
Open a new query and run
select * from actor;
-
Did you see any data? If not that's ok. The schema is more important here
-
You should see many tables under this database
-
With MySQL Workbench open, click the "Database" tab
-
Select "Reverse Engineer"
-
Make sure your connection information is correct and then click "continue"
-
Under "Select the schemas you want to include:" chose "sakila"
-
DESELECT everything except "Import MySQL Table Objects"
-
Click "Execute"
-
You should see a pretty comprehensive ER diagram consisting of 16 tables
-
Answer the following questions about this diagram
-
What is the relationship between the "actor" and "film_actor" tables? ** actor_id **
-
What does the blue diamond next to the "last_update" column on the "inventory" table represent? ** Blue diamond represents "NOT NULL" **
-
How many foreign keys does the "payments" table have? How can you tell? ** There are three foreign keys, observed by the red diamonds to the left. When clicked on the table, there is also a foreign key tab that lists them as well **
-
Take a screenshot of the ER diagram you created and name it "wk6_er_diagram"
-
Copy the screenshot to this directory and upload it (git push) along with this README