COMS W4111: Introduction to Databases Spring 2020 − Sections 002, H02, V02 Homework 2: Common to Both Tracks
Introduction
- This notebook is the template for the common material for both tracks for homework 2.
- Your submission format is a zip file containing this notebook and embedded images.
-
You must name your file
uni_W4111_s20_hw2_common.zip.
- I strongly suggest that you verify that your uploaded zip file is correct.
- There are cells for entering your answers and instructions for what to enter.
Relational Algebra
Setup
-
You will use an on-line relational algebra calculator (https://dbis-uibk.github.io/relax/calc.htm) to write and test your answers.
-
You can find the data for testing your expressions in the GitHub repository for the homework.(https://github.com/donald-f-ferguson/W4111HW2/blob/master/Data/book_tables.gist).
- This is the data from the sample database for the new textbook for the course.
-
When on the calculator web page. You should choose to create a new dataset.
- Click on the pull down button in the upper left hand corner.
- Choose to create a new dataset.
- Cut and paste all of the text from the testing data file into the window and click preview.
- Scroll down to the bottom and click "Use in Group Editor."
- Scroll back to the top and click "Relational Algebra."
- You can now start entering relational algebra expressions.
-
For each question and relational algebra expression:
- Copy and paste your expression into the provided markdown cell.
- Take a screen capture of the result of evaluating the expression. Copy and paste the image into the markdown cell.
-
There is an example below.
-
You will also write SQL queries using the database for the new book. Previous lectures have explained how to install the database.
- Row order in result tables does not matter unless specified.
In [2]:
%load_ext sql
%sql mysql+pymysql://root:@localhost/newbook
The sql extension is already loaded. To reload it, use: %reload_ext sql
Out[2]:
'Connected: root@newbook'
Example
Example Question:
Question
-
Produce the follow table. The information comes from
course, department, teaches, instructor.
2020-03-03