Join condition vs join type
Join condition: which tuples in the two relations match, and what attributes are in the result. Join type: how tuples that do not match any tuple in the other relation are treated.
Inner join returns…
Rows only when there is at least one row from both tables that matches the join condition.
What is an outer join?
An extension of the join that avoids loss of information: it computes the join and then adds the non-matching tuples of one relation, using null values.
Left, right and full outer join: which rows are kept?
Left: all rows from the left table. Right: all rows from the right table. Full: all rows from both tables.
course natural left outer join prereq: which extra row appears, and how?
CS-315 (Robotics), with prereq_id null — it has no prereq row.
course natural full outer join prereq: which rows have nulls?
CS-315 (prereq_id null) and CS-347 (title, dept_name and credits null).
What is a view?
Any relation that is not of the conceptual model but is made visible to a user as a "virtual relation"; it hides certain data from certain users.
Does create view evaluate the query and store a new relation?
No — a view definition causes the saving of an expression, which is substituted into queries using the view.
When does view v1 depend directly on v2? When is a view recursive?
v1 depends directly on v2 if v2 is used in the expression defining v1. A view is recursive if it depends on itself.
View expansion: the loop and when it terminates
Repeat: find a view relation vi in e1 and replace it by the expression defining vi, until no view relations remain. It terminates as long as the view definitions are not recursive.
Conditions for a view that most SQL implementations can update
One database relation in from; only attribute names in select (no expressions, aggregates or distinct); unlisted attributes can be set to null; no group by or having.
insert into faculty values ('30765', 'Green', 'Music') becomes…
An insertion of ('30765', 'Green', 'Music', null) into instructor.
Materialized view
A physical table containing all the tuples of the view's query result; it becomes out of date when the underlying relations are updated, so it must be maintained.
How does an SQL transaction end, and what is the default on most databases?
With commit work or rollback work. By default, each SQL statement commits automatically (auto commit can be turned off for a session).
Atomic transaction
Either fully executed or rolled back as if it never occurred.
What do integrity constraints guard against?
Accidental damage to the database — they ensure that authorized changes do not result in a loss of data consistency.
unique (A1, …, Am): what does it declare, and may the values be null?
The attributes form a candidate key; candidate keys are permitted to be null (unlike primary keys).
Referential integrity
A value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.
Alternatives to cascade in a foreign key
set null and set default (e.g., on delete …, on update …).
Are subqueries in check clauses and create assertion supported?
Subqueries in check: not supported by pretty much any database (alternative: triggers). create assertion: also not supported by anyone.
date, time, timestamp, interval
date: year, month, date; time: hours, minutes, seconds; timestamp: date plus time of day; interval: a period of time (date − date gives an interval).
What is an index for?
A data structure used to speed up access to records with specified values for index attributes (e.g., create index studentID_index on student(ID)).
Type vs domain in SQL
They are similar, but domains can have constraints, such as not null, specified on them.
blob vs clob; what does a query return for one?
blob: binary large object; clob: character large object. A query returns a pointer rather than the large object itself.
Read, Insert, Update, Delete authorization
Read: reading, not modification. Insert: new data, not modifying existing data. Update: modification, not deletion. Delete: deletion of data.
Index, Resources, Alteration, Drop authorization
Index: create/delete indices. Resources: create new relations. Alteration: add/delete attributes. Drop: delete relations.
Does a privilege on a view give privileges on its underlying relations?
No — granting a privilege on a view does not imply granting any privileges on the underlying relations.
Revoking a privilege: three rules
Revoking from public leaves those granted it explicitly; a privilege granted twice by different grantees may be retained; all privileges that depend on the revoked one are also revoked.
grant teaching_assistant to instructor — what happens?
The role instructor inherits all privileges of teaching_assistant (roles can be granted to other roles).
references privilege and with grant option
references allows creating a foreign key (grant reference (dept_name) on department to Mariano); with grant option lets the user transfer the privilege.