Intermediate SQLSQL المتوسطة

• The join operations are typically used as subquery expressions in the from clause (slides 3 and 9).

• prereq information is missing for CS-315 and course information is missing for CS-347.

• Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions (slide 5).

• on gives the join condition explicitly (the matched attribute appears from both relations); natural matches all attributes with the same name; using (course_id) names the attribute to match.
• Slide 15: to list all students with the courses they have taken — including students who took none — use student natural left outer join takes (or takes natural right outer join student).

Slide 19

• select * from student join takes using (ID); is equivalent to select * from student inner join takes using (ID);
• Similarly, natural join is equivalent to natural inner join.

• View definition is not the same as creating a new relation by evaluating the query expression.
• Rather, a view definition causes the saving of an expression; the expression is substituted into queries using the view.

• As long as the view definitions are not recursive, this loop will terminate.

Some updates cannot be translated uniquely (slide 28)

• instructor_info shows ID, name, building from instructor and department. Inserting ('69987', 'White', 'Taylor'): which department, if multiple departments are in Taylor? What if no department is in Taylor?

Most SQL implementations allow updates only on simple views

• The from clause has only one database relation.
• The select clause contains only attribute names of the relation — no expressions, aggregates or distinct.
• Any attribute not listed in select can be set to null.
• The query has no group by or having clause.

And some not at all (slide 30)

• create view history_instructors as select * from instructor where dept_name = 'History';
• What happens if we insert ('25566', 'Brown', 'Biology', 100000) into history_instructors? (The slide asks the question and gives no answer.)

• If the relations used in the query are updated, the materialized view result becomes out of date.
• The view must be maintained — updated whenever the underlying relations are updated.

• A transaction is a unit of work.
• Atomic transaction: either fully executed or rolled back as if it never occurred.
• Isolation from concurrent transactions.
• Transactions begin implicitly and are ended by commit work or rollback work.
• Default on most databases: each SQL statement commits automatically; auto commit can be turned off for a session (e.g., using an API).
• SQL:1999: begin atomic … end — not supported on most databases.

Examples (slide 33)

• A checking account must have a balance greater than $10,000.00.
• A salary of a bank employee must be at least $4.00 an hour.
• A customer must have a (non-null) phone number.

• unique (A1, …, Am): the attributes form a candidate key; candidate keys are permitted to be null (in contrast to primary keys).
• check (P), P a predicate — e.g., check (semester in ('Fall', 'Winter', 'Spring', 'Summer')).

• If A is the primary key of S, A is a foreign key of R if every value of A appearing in R also appears in S.

• Alternative actions to cascade: set null, set default.

• How to insert a tuple without causing constraint violation? (slide 40 asks this; it gives no answer)

Complex check clauses (slide 41)

• check (time_slot_id in (select time_slot_id from time_slot)).
• Subquery in check clause: not supported by pretty much any database; alternative: triggers.
• create assertion <assertion-name> check <predicate> — also not supported by anyone.

Questions on slide 41

• check (time_slot_id in (select time_slot_id from time_slot)) — why not use a foreign key here?
• Every section has at least one instructor teaching the section — how to write this?

• Subtracting a date/time/timestamp value from another gives an interval value; interval values can be added to date/time/timestamp values.
• default: tot_cred numeric(3,0) default 0.
• create index studentID_index on student(ID) — indices are data structures used to speed up access to records with specified values for index attributes; select * from student where ID = '12345' can find the record without looking at all records.

• Types and domains are similar. Domains can have constraints, such as not null, specified on them.

• When a query returns a large object, a pointer is returned rather than the large object itself.

• Granting a privilege on a view does not imply granting any privileges on the underlying relations.
• The grantor must already hold the privilege on the item (or be the database administrator).
• Privileges: select (read access, or querying using the view), insert, update, delete, all privileges.
• Example: grant select on instructor to U1, U2, U3

• Revoking from public: all users lose the privilege except those granted it explicitly.
• If the same privilege was granted twice by different grantees, the user may retain it after the revocation.
• All privileges that depend on the privilege being revoked are also revoked.

• Privileges can be granted to roles; roles can be granted to users, as well as to other roles.
• After grant teaching_assistant to instructor, instructor inherits all privileges of teaching_assistant (a chain of roles).
• references privilege to create a foreign key: grant reference (dept_name) on department to Mariano.
• Transfer of privileges: grant select on department to Amit with grant option; revoke … from Amit, Satoshi cascade or restrict.

What if (slide 52)

• geo_staff does not have permissions on instructor?
• creator of view did not have some permissions on instructor?

• references privilege to create foreign key — why is this required? (slide 53)