Terse Notes — Fundamentals of Database Systems Part 7

More SQL — Complex Queries, Triggers, Views and Schema Modification

Photo by Joshua Rawson-Harris on Unsplash

Book Credit to Elmasri Ramez And Navathe Shamkant

Book: Fundamentals Of Database System, 7th Edition by Elmasri Ramez And Navathe Shamkant

Ch 7 — More SQL — Complex Queries, Triggers, Views and Schema Modification

Three-Value logic — true false or unknown; since null has three meanings; as opposed to boolean true/false.

IS Null

IS NOT Null

— There’s one way to compare an attribute value to null.

Nested query — complete select-from-where blocks within another SQL query

Outer query — the SQL query that has a nested query in it.

IN

— a comparison operator — which compares a value v with a set or multisite or values V and evaluates to true if v is one of the elements in V.

Other comparison operators:

ANY

SOME

ALL

Correlated nested queries — when a condition in the WHERE clause of a nested query references some attribute of a relation declared in the outer query.

“nested query is evaluated once for each tuple (or combination of tuples) in the outer query.” (pp. 211)

EXISTS — Boolean function used in a where clause condition to see if the results of a nested query contain tuples or not.

NOT EXISTS — same as exists but the negative; if none exist then it is selected.

UNIQUE — Boolean function used in a where clause condition that returns TRUE if no duplicates are found

Joins:

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• NATURAL LEFT OUTER JOIN
• NATURAL RIGHT OUTER JOIN
• NATURAL FULL OUTER JOIN
• CROSS JOIN

Multiway join — nest join specifications together.

Aggregate Functions:

• COUNT
• MIN
• MAX
• SUM
• AVG

These are used in the select clause or the having clause.

GROUP BY — a clause that specifies the grouping attributes; which also need to appear in the SELECT clause

HAVING — clause that provides a condition on the summary information for each value of the grouping attributes. Chooses whole groups.

Partition — to section the relation into non overlapping subsets or groups.

Notice — HAVING and WHERE clauses work differently; WHERE clause is executed first and selects individual tuples or joined tuples; HAVING is applied later to select individual groups of tuples.

WITH — clause that allows user to define a table that will only be used in a particular query.

CASE — a construct used when a value can be different based on certain conditions

SPECIFYING CONSTRAINTS

CREATE ASSERTION

• You can use this to create more constraints beyond the ones available in the CREATE TABLE statement of SQL.
• It is used with the CHECK statement
• Give it a name
• You can either use a WHERE Clause or use EXISTS/NOT EXISTS
• The falsity results in violating the constraint
• You write it so that the query finds and selects any tuples that violate the desired condition, then including that in a NOT EXISTS clause.
• CHECK only works when tuples are inserted or updated in a specific table; used on attributes, domains and tuples.

CREATE TRIGGER

-you specify actions when certain events occur and certain conditions are satisfied

-you can run a stored procedure, which is common

ECA trigger

• Event Condition Action rule has three components:

1. Events — usually database update operations that are explicitly applied to the database. Specified in the keyword BEFORE or AFTER.
2. Condition — determines whether the rule action should be executed. Specified in the WHEN clause of the trigger.
3. Action — what action to be taken. Usually a sequence of SQL statements.

View in SQL (Virtual Tables)

View — a single table that is derived from other tables, either base tables or previously defined views.

Base tables- these tables have tuples that are always physically stored in the database.

*Limits update operations that can be done on views.

Defining tables- the tables where the view is derived from.

CREATE VIEW — the command to specify a view in SQL; views are useful because they simplify certain queries and they can be used as a security and authorization mechanism too.

DROP VIEW — this command is used to delete a view.

How does SQL actually implement a view?

There are different strategies for this complex issue…

1. Query Modification — modifying or transforming the view query into a query on the underlying base tables. This could be inefficient if the query is time-consuming.
2. View Materialization — physically creating a temporary or permanent view table when the view is first queried/created. This necessitate keeping the views up to date.
3. Incremental update — this is a method used to keep views up to date. The DBMS keeps tabs on new tuples in the base tables and updates the materialized view.

View updates

-general rule is that it is permitted when only one possible update on the base relations can accomplish the update on the view.

-else when the update on the view maps to more than one underlying base relation, then it is not permitted.

1. View with a single defining table is updatable if the view attributes contain the primary key of the base table and attributes with NOT NULL.
2. Views defined on multiple tables are not updatable (generally).
3. Views using grouping and aggregate functions are not updatable.

WITH CHECK OPTION

-this is a clause used at the end of the view definition if it is meant to be updated. Tells the system to reject operations that violate rules for view updates.

SCHEMA CHANGE STATEMENTS IN SQL

DROP — a command to drop schema elements like tables, domains, types or constraints;

DROP TABLE — it will delete the table and also remove the table definition from the catalog.

Two drop behavior options exist:

CASCADE and RESTRICT

CASCADE — makes the DROP command include all the tables associated constraints, views, and other elements that reference the table being dropped.

RESTRICT — makes the schema drop only if it has no elements; or else makes the table drop only if its not referenced in any constraints or views or any other elements.

ALTER — a command that lets you alter tables; does not allow for NOT NULL constraints.

ALTER TABLE — a set of actions you can take to alter a table including add or drop columns, changing column definition, adding or dropping table constraints.

To drop a column you must use CASCADE or RESTRICT.

Review Questions

Review Question 7.1

An SQL retrieval query can consist of up to six clauses, but only the SELECT and FROM clauses are mandatory. The clauses are specified in the following order:

1. SELECT: Lists the attributes or functions to be retrieved.
2. FROM: Specifies all relations (tables) needed in the query, including joined relations, but not those in nested queries.
3. WHERE (optional): Specifies the conditions for selecting and joining tuples from the relations.
4. GROUP BY (optional): Specifies grouping attributes.
5. HAVING (optional): Specifies a condition on the groups being selected rather than on individual tuples.
6. ORDER BY (optional): Specifies an order for displaying the query result.

Review Question 7.2

Conceptually, an SQL retrieval query is evaluated in the following order:

1. The FROM clause is applied to identify all tables involved in the query or to materialize any joined tables.
2. The WHERE clause is applied to select and join tuples.
3. The GROUP BY clause is applied to group the tuples.
4. The HAVING clause is applied to select groups of tuples.
5. The ORDER BY clause is applied to sort the query result.
6. The SELECT clause is applied to project the specified attributes or functions from the result.

Review Question 7.3

• In SQL, NULL represents a missing or unknown value. When comparing a value with NULL using comparison operators like =, <>, >, >=, <, or <=, the result is UNKNOWN (or NULL).
• When aggregate functions like COUNT, SUM, MIN, MAX, or AVG are applied to a set of values containing NULLs, the NULLs are ignored (except for COUNT(*) which counts all tuples including those with NULLs).
• If a grouping attribute contains a NULL value, NULLs are considered as a single group.

Review Question 7.4

a. Nested queries: Used to construct queries that reference other queries. They can appear in the WHERE, FROM, SELECT or other clauses as needed. Useful for queries that cannot be easily expressed without nesting.

b. Joined tables and outer joins: Used to combine rows from two or more tables based on a join condition. Outer joins include rows from one table that do not match any rows in the other table.

c. Aggregate functions and grouping: Aggregate functions like COUNT, SUM, MIN, MAX, AVG are used with the GROUP BY clause to group rows based on one or more columns and apply the aggregate to each group.

d. Triggers: Used to specify automatic actions that the database system will perform when certain events and conditions occur, such as inserting, updating or deleting data.

e. Assertions: Used to specify general constraints that do not fall into built-in categories like primary keys or referential integrity. They differ from triggers in that assertions define conditions that must hold, while triggers define actions to take on certain events.

f. SQL WITH clause: Allows defining a temporary table that will only be used in a particular query, similar to creating a view for one query.

g. SQL CASE construct: Used to perform conditional logic and return different values based on different conditions.

h. Views: Present the user with what appears to be a table, but the data is derived from previously defined base tables. Views can be updatable under certain conditions.

i. Schema change commands: Include CREATE TABLE, DROP TABLE, ALTER TABLE for creating, dropping and modifying database tables and constraints.