Homework #3
Deliverables:
Submit your answers using the submission_template.txt
file that is posted on Canvas. Follow the instructions on the file! Upload the file at Canvas.
Instructions / Notes:
Read these carefully
- You may create new IPython notebook cells to use for e.g. testing, debugging, exploring, etc.- this is encouraged in fact!- just make sure that your final answer for each question is in its own cell and clearly indicated
- When you see
In [*]:
to the left of the cell you are executing, this means that the code / query is running.- If the cell is hanging- i.e. running for too long: To restart the SQL connection, you must restart the entire python kernel
- To restart kernel using the menu bar: “Kernel >> Restart >> Clear all outputs & restart”), then re-execute the sql connection cell at top
- You will also need to restart the connection if you want to load a different version of the database file
- Remember:
%sql [SQL]
is for single line SQL queries%%sql [SQL]
is for multi line SQL queries
- Have fun!
Problem 2: Superkeys & Decompositions [25 points]
Consider a relation S ( A , B , C , D , E , F ) S(A,B,C,D,E,F) S(A,B,C,D,E,F) with the following functional dependencies:
- { A } → { D } \{A\} \rightarrow \{D\} {A}→{D}
- { A } → { E } \{A\} \rightarrow \{E\} {A}→{E}
- { D } → { C } \{D\} \rightarrow \{C\} {D}→{C}
- { D } → { F } \{D\} \rightarrow \{F\} {D}→{F}
In each part of this problem, we will examine different properties the provided schema.
To answer yes, provide python code that assigns the variable answer
to True
and assigns explanation
to be a python string which contains a (short!) explanation of why. For example:
answer = True
explanation = "All keys are superkeys."
To answer no, provide python code that assigns the variable answer
to False
and assigns explanation
to be a python string which contains a (short!) explanation of why. For example:
answer = False
explanation = "D is not a superkey because its closure is {D,C,F}."
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/e8cd1bab76cb41abad68a86492c215b2.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/ee3cb9bf83e0aeb4f4489124bce15223.png)
Problem 3: Relational Algebra [25 points]
Consider the following relational schema for conference publications:
Article(artid, title, confid, numpages)
Conference(confid, name, year, location)
Author(artid, pid)
Person(pid, name, affiliation)
Express the following queries in the extended Relational Algebra (you can also use the aggregation operator if necessary). To write the RA expression, use the LaTex mode that ipython notebook provides. For example:
π n a m e ( σ a f f i l i a t i o n = " U W − M a d i s o n " ( P e r s o n ) ) \pi_{name}(\sigma_{affiliation="UW-Madison"}(Person)) πname(σaffiliation="UW−Madison"(Person))