Chapter 4: Intermediate SQL

Join Expressions

Def. Join operations take two relations and return as a result another relation.

Def. Join operations is a Cartesian product requires that tuples in two relations match.

Def. Join condition defines which tuples in two relations match, and what attributes are present as the result.

Def. Join type defines how tuples in each relation that do not match any tuple in the other relation are treated.

We suppose we have two relations, their tables are as follow:

course_id	title	dept_name	credits
BIO-301	Genetics	Biology	4
CS-190	Game Design	Comp.Sci	4
CS-315	Robotics	Comp.Sci	3

Table 1: Course table

course_id	prereq_id
BIO-301	BIO-101
CS-190	CS-101
CS-347	CS-101

Table 2: Prereq table

Natural Join Operations

SELECT *
FROM course NATURAL JOIN prereq;

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp.Sci	4	CS-101

Table 3: The result table of natural join

Outer Join

Def. Extension of the join operation avoids loss of information. This operation will compute the join, and then adds tuples form one relation that does not match tuples in another relation to the result of the join using the null values.

Left outer join:

SELECT *
FROM course NATURAL LEFT OUTER JOIN prereq

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp.Sci	4	CS-101
CS-315	Robotics	Comp.Sci	3	null

Table 4: The result table of natural left outer join

Right outer join:

SELECT *
FROM course NATURAL RIGHT OUTER JOIN prereq;

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp.Sci	4	CS-101
CS-347	null	null	null	CS-101

Table 5: The result table of natural right outer join

Full outer join:

SELECT *
FROM course NATURAL FULL OUTER JOIN prereq;

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp.Sci	4	CS-101
CS-315	Robotics	Comp.Sci	3	null
CS-347	null	null	null	CS-101

Table 5: The result table of natural full outer join

More examples:

If course INNER JOIN prereq ON course.course_id = prereq.course_id, the result table is:

course_id	title	dept_name	credits	prereq_id	course_id
BIO-301	Genetics	Biology	4	BIO-101	BIO-301
CS-190	Game Design	Comp.Sci	4	CS-101	CS-190

Table 6: The result table of inner join

If course LEFT OUTER JOIN prereq ON course.course_id = prereq.course_id

course_id	title	dept_name	credits	prereq_id	course_id
BIO-301	Genetics	Biology	4	BIO-101	BIO-301
CS-190	Game Design	Comp.Sci	4	CS-101	CS-190
CS-315	Robotics	Comp.Sci	3	null	null

Table 7: The result table of left join

If course FULL OUTER JOIN prereq USING(course_id)

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp.Sci	4	CS-101
CS-315	Robotics	Comp.Sci	3	null
CS-347	null	null	null	CS-101

Table 8: The result table of full outer join with using clause

Views

Actually, not desirable for all users to see the entire logical model. A view hides certain data from view of certain users.

Note:

• Views equal to projection on one or more relations
• View is known as virtual relation/table
  • Only the definition of view itself is stored in DBS, the tuples of the view is stored in relations
  • Evaluation of view is reduced to evaluation of relation algebra expression that defines the view (chapter 16)
• Differences between the with clause and the create view clause
  • With clause creates a temporal table to store the query results, the created table will be cancelled after SQL execution ends

Def. Any relation that is not of the conceptual model, but is made visible to a user as a virtual relation.

Def. View is defined using the create view statement CREATE VIEW view_name AS <query expression>.

• The view name can be used to refer to the virtual relation
• View definition is not the same as creating a new relation. A view definition causes the saving of an expression

For example,

CREATE VIEW department_total_salary(dept_name, total_salary) AS
SELECT dept_name, SUM(salary)
FROM instructor
GROUP BY dept_name;

Note: one view may be used in the expression defining another view.

Def. A view relation \(v_1\) is said to depend directly on a view relation \(v_2\), if \(v_2\) is used in the expression defining \(v_1\).

Def. A view relation \(v_1\) is said to depend on view relation \(v_2\), if either \(v_1\) depends on directly to \(v_2\) or there is a path of dependencies from \(v_1\) to \(v_2\).

Def. A view relation \(v\) is said to be recursive if it depends on itself.

We can update a view. For example, add a new tuple to faculty view INSERT INTO faculty VALUES('30765', 'Green', 'Music');.

Materializing a view means creating a physical table containing all the tuples in the result of the query defined view. If relations used in the query are updated, the materialized view result becomes out of date.

Transactions

Def. A transaction consists of a sequence of query and/or update statements and it is a unit of work.

Note: ACID (atomicity, consistency, isolation, durability) properties.

Atomic transaction: either fully executed or rolled back as if never occurred.

Isolation from concurrent transactions.

A transaction begins implicitly when an statement is executed, ends with statements.

• Commit work. The updates become permanent
• Rollback work. All the updates performed are undone

Here is an example

-- Declare a transaction name
DECLARE @transfer_name VARCHAR(50);
SET @transfer_name = 'I-transfer-from-A-to-B';

-- Begin the transaction
BEGIN TRANSACTION;

-- Switch to the target database
USE ACCOUNT;

-- Deduct balance from table A
UPDATE A
SET balance = balance - 50
WHERE branch_name = 'Brooklyn';

-- Add balance to table A
UPDATE A
SET balance = balance + 50
WHERE branch_name = 'Brooklyn';

-- Commit the transaction
COMMIT TRANSACTION;

Integrity Constraints

Def. Integrity constraints guard against accidental damage to DB, ensuring that the authorized changes to DB do not result in loss of data consistency.

Examples

• An instructor name can not be null
• No two instructors can have the same instructor ID
• Every department name in course relation must have a matching department name in department relation.

type	attribute-level	tuple-level	relation-level
static	data type data format domain constraints null value	constraints among attributes values	entity integrity referential integrity functional dependency
dynamic	constraints on updating of attribute values or attribute definition	constraints among attributes values	transaction constraint: ACID

Table 9: Classification of integrity constraints

Constraints on Single Relation

Integrity constraints include

• primary key
• not null
  • Declare name and budget to be not null
• unique
• check(P), where P is a predicate

Def. Unique constraint is as follows: unique \((A_1, A_2, \cdots, A_m)\).

• Attributes \(A_i\) form a candidate key
• Candidate keys are permitted to be null (in contrast to primary keys)

CREATE TABLE customer (
  customer_id char(15),
  customer_name char(15),
  customer_city char(30),
  PRIMARY KEY (customer_id),
  UNIQUE (customer_name)
);

The check clause is applied to relation declaration as well as domain declaration.

CREATE TABLE section (
  course_id VARCHAR(8),
  sec_id VARCHAR(8),
  semester VARCHAR(6),
  year NUMERIC(4, 0),
  room_number VARCHAR(7),
  PRIMARY KEY (course_id, sec_id, semester, year),
  CHECK (semester IN ('Fall', 'Winter', 'Spring', 'Summer'))
);

Referential Integrity

Def. Referential integrity dependencies

• Let \(r_1(R_1)\) and \(r_2(R_2)\) be relations with primary keys \(K_1\) and \(K_2\) respective
• The subset \(\alpha\) of \(R_2\) (dept_name) is a foreign key (from \(r_2\), course) referencing \(K_1\) in relation \(r_1\) (dept_name in department), if every \(t_2\) in \(r_2\), there must be a tuple \(t_1\) in \(r_1\) such that \(t_1 [K_1] = t_2 [\alpha]\)

Def. Referential integrity constraint called subset dependency since it can be written as \(\Pi_{\alpha}(r_2) \subseteq \Pi_{K_1}(r_1)\). Foreign key \(\alpha\) of table \(r_2\) references primary key attributes \(K_1\) of the referenced table \(r_1\).

Cascading Actions in Referential Integrity

The delete/update operations on the referenced department will result in the delete/update on the referencing course.

CREATE TABLE course (
  ...
  dept_name VARCHAR(20),
  FOREIGN KEY (dept_name) REFERENCES department ON DELETE CASCADE ON UPDATE CASCADE,
  ...
)

What changes in department will happen in course on dept_name.

Data Types and Schemas

Type Conversion

• CAST e AS t: convert a character string e to the type t, e.g. cast '2017-07-20 AS DATE`
• EXTRACT VALUE d FROM DAY (or TIME): for day or time values d, its individual fields can be extracted, e.g. EXTRACT year FROM ’2017-07-20‘ = 2017

Default Values

CREATE TABLE student (
  ID VARCHAR(5),
  name VARCHAR(20) NOT NULL,
  dept_name VARCHAR(20),
  tot_cred numeric(3, 0) DEFAULT 0,
  PRIMARY KEY (ID)
);

INSERT INTO student(ID, name, dept_name) VALUES ('12789', 'Newman', 'Comp.Sci') (tot_cred not required)

User-defined Types

The create type clause can be used for user-defined types, e.g.

CREATE TYPE Dollars AS NUMERIC(12, 2)
CREATE TABLE department (
  dept_name VARCHAR(20),
  building VARCHAR(15),
  budget Dollars
);

User-defined Domains

Create domain construct creates user-defined domain types, e.g. CREATE DOMAIN person_name CHAR(20) NOT NULL.

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

CREATE DOMAIN degree_level VARCHAR(10)
CONSTRAINT degree_level_test
CHECK (VALUE IN ('Bachelors', 'Masters', 'Doctorate'));

Complex Check Conditions and Assertions

Some constructs such as check and assertion are defined to specify the complex integrity constraints. However, they are not currently supported by most database systems.

An assertion is a predicate expressing a condition that we wish the database always to satisfy. An assertion takes the form CREATE ASSERTION <assertion_name> CHECK <predicate>. This testing may introduce a significant amount of overhead, hence assertions should be used with great care. Here is an example

CREATE ASSERTION credits_earned_constraints CHECK
(
  NOT EXISTS (SELECT ID FROM student WHERE tot_cred <> (SELECT SUM(credits) FROM takes NATURAL JOIN course WHERE student.ID = takes.ID AND grade IS NOT NULL AND grade <> 'F'))
);

Index Definition in SQL

Many queries reference only a small proportion of the records in a table. It is inefficient to read every record to find a record with particular value.

Def. An index on an attribute of a relation is a data structure that allows the DBS to find those tuples in the relation that have a specified value for that attribute efficiently, without scanning through all the tuples of the relation.

Create an index with the create index command: CREATE INDEX <name> ON <relation_name> (<attribute_name>);

CREATE INDEX studentID_index ON student(ID)

The query:

SELECT *
FROM student
WHERE ID = '12345';

can be executed by using the index to find the required record, without looking at all records of student.

Authorization

• Forms of authorization on parts of the database
  • Read allows reading, but not modification of data
  • Insert allows modification, but not deletion of data
  • Delete allows deletion of data
• Forms of authorization to modify the database schema
  • Resources allows creation of new relations
  • Alteration allows addition or deletion of attributes in a relation
  • Drop allows deletion of relations
  • Index

The grant statement is used to confer authorization

• GRANT <privilege_list> ON <relation_name or view_name> TO <user_list>
• user_list is a user_id or public (which allows all valid users the privilege granted)

Granting a privilege on a view does not imply granting any privileges on the underlying relations.

The grantor of the privilege must already hold the privilege on the specified item (or be the database administrator).

Privileges

• SELECT: allows read access to relation, or ability to query using the view
  • e.g. GRANT SELECT ON instructor TO user_1, user_2, user_3
• INSERT
• UPDATE
• DELETE
• ALL PRIVILEGES

Revoking Authorization

The revoke statement is used to revoke authorization

• REVOKE <privilege_list> ON <relation_name or view_name> FROM <user_list>
• e.g. REVOKE SELECT ON instructor FROM user_1, user_2, user_3

Privilege_list may be all to revoke all privileges the revoker may hold.

If user_list includes public, all users lose the privilege except those granted it explicitly.

Roles

Chain of roles

CREATE ROLE dean;
GRANT instructor TO dean;
GRANT dean TO Satoshi'

• Privileges can be granted to roles
  • GRANT select ON takes TO instructor;
• Roles can be granted to users, as well as to other roles
  • CREATE ROLE teaching_assistant; GRANT teaching_assistant TO instructor;
  • Instructor inherits all privileges of teaching_assistant

Authorization on Views

CREATE VIEW geo_instructor AS (SELECT * FROM instructor WHERE dept_name = 'Geology');
GRANT SELECT ON geo_instructor TO geo_staff;

Other Authorization Features

• References privilege to create foreign key
  • GRANT REFERENCE(dept_name) ON department TO Mariano;
• Transfer of privilege
  • GRANT SELECT ON department TO Amit WITH GRANT OPTION;
  • REVOKE SELECT ON department FROM Amit, Satoshi CASCADE;
  • REVOKE SELECT ON department FROM Amit, Satoshi RESTRICT;