Lecture 7-8 Flashcards
What is a subquery?
A nested query/subquery is a query within a query, the WHERE clause of the outer relies on the inner in some way, the inner is solved first.
For the comparison operators the inner query must produce a single tuple. IN can be a useful alternative.
Can also be used in HAVING sub clauses.
What is a correlated subquery?
When WHERE of inner refers to taple in FROM of outer, subquery is executed once for each row of outer query table.
Stores value of current row in alias named in FROM clause of outer, subquery is then performed and evaluated, repeating for each row in outer query table.
What does the exists operator do?
Takes subquery as argument, returns true if any output is returned, False otherwise.
In a correlated subquery it is evaluated once for each row in the outer query table.
What does the ANY operator do? What about the ALL?
must be preceded by comparison operator. At least one tuple returned by subquery must satisfy the condition.
ALL works the same way, but every returned tuple must satisfy the condition.
Can INSERTS be combined with selects?
Yes, just place the SELECT query after the INTO part of the insert.
Can we create tables from existing tables?
Yes
CREATE TABLE table2 AS (SELECT * FROM table1);
What are the 4 guidelines to schema creation?
- Design a schema so that it is easy to explain meaning, do not combine attributes from multiple entity types and relationship types into a single relation.
- Design the schema so there are no insertion,deletion, or modification anomalies.
- Avoid placing attributes in a relationif the attribute will mostly be NULL, if unavoidable, make sure they only occur in exceptional cases.
- Design schemas so they can be joined with equality conditions on primary or foreign keys so that no spurious tuples are created, don’t have relations that contain matching attributes other than primay key foreign key combinations, as joining on such an attribute may create spurious tuples.
What is a functional dependency?
denoted X -> Y where X and Y are two subsets of attributes on the same relation, it is a functional dependency if for any two tuples with x1 =x2, y1=y2. This means y is determined by x it doesn’t mean x is determined by y.
What is a decomposition? What are the properties of a successful one?
The process of breaking down a relation schema into a set to remove unwanted dependencies.
A successful one should preserve attributes, meaning an attribute in the original is in at least one subset.
dependencies are preserved meaning, each functional dependency either appeared directly in a subset or could be inferred from dependencies in some subset.
Lossless join: no spurious tuples created by natural join
What is closure?
The closure of a set of functional dependencies denoted F+ is the set of all dependencies that come from F, including F.
What does it mean for a set of functional dependencies to cover another?
A set of functional dependencies covers another if every functional dependcy in the second is in the closure of the 1st.
They are equivalent if the 2 closures =.
What is a minimal set of functional dependencies?
A set of functional dependencies is minimal if:
Every RHS is a single attribute, every LHS is irreducible(no redundant attributes) and no functional dependy is redundant.
