sqlite homework3

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)$ 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 answerto Trueand assigns explanationto 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 answerto Falseand assigns explanationto 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}."

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:

$${\pi }_{name}({\sigma }_{affiliation="UW-Madison"}(Person))$$