ch4

Chapter Overview

  • Intermediate SQL Database System Concepts, 6th Ed. by Silberschatz, Korth, and Sudarshan

Table of Contents

  • Join Expressions

  • Views

  • Transactions

  • Integrity Constraints

  • SQL Data Types and Schemas

  • Authorization

Join Expressions

Join Operations

  • Join operations take two relations and produce another relation as a result.

  • The operation involves a Cartesian product, where tuples from both relations match based on specified conditions.

  • The attributes present in the resulting relation are defined during the join operation.

Types of Join Statements

  1. Join Statement Differences:

    • SELECT * FROM student JOIN takes ON student.ID = takes.ID;

    • SELECT * FROM student NATURAL JOIN takes;

    • Both types of joins match tuples based on ID but differ in syntax and result set.

Example Relations

Instructor Relation

  • Attributes:

    • ID, course_id, sec_id, semester, name, dept_name, salary, year

  • Sample Data:

    • ID: 10101; Name: Srinivasan; Dept_Name: Comp. Sci.; Salary: 65000; Year: 2009

Teaches Relation

  • Attributes:

    • ID, name, dept_name, salary, course_id, sec_id, semester, year

Natural Join Operations

  • A natural join merges data from instructor and teaches relations where matching attributes (e.g., ID) are present.

  • Missing tuples from one relation will be omitted in the results since the intersection defines the output.

Join Enhancements

Outer Joins

  • Left Outer Join: Keeps all tuples from the left relation and adds matching tuples from the right; unmatched keep nulls.

  • Right Outer Join: Keeps all tuples from the right relation.

  • Full Outer Join: Combines both, maintaining all information, though not supported in MySQL.

Joins with Conditions

  • Use the USING clause to specify common attributes for matching in joins (e.g., JOIN USING(course_id)).

Views

Definition and Usage

  • A view is a virtual relation that represents a subset of data in the database.

  • Views can hide sensitive information (e.g., employee salaries) and simplify complex queries.

  • Created using CREATE VIEW view_name AS <query_statement>.

Creating and Using Views

  • Example:

    • CREATE VIEW faculty AS SELECT ID, name, dept_name FROM instructor;

  • Views can depend on other views, creating a hierarchy of data access.

Views and Data Manipulation

  • Updates to views may affect base tables; certain restrictions apply to maintain integrity.

  • Materializing Views: Creating physical representations in the database that need maintenance upon updates to underlying data.

Transactions

Transaction Concepts

  • Each transaction is a unit of work that must be completed fully or not at all (atomicity).

  • Transactions maintain data integrity, even amid concurrent transactions.

  • They start implicitly and must end with either a commit (save) or rollback (revert).

Integrity Constraints

Definitions

  • They ensure data accuracy and consistency, preventing invalid entries into the database.

  • Types of constraints:

    • NOT NULL: Field must contain valid data.

    • PRIMARY KEY: Unique identifiers for a record.

    • UNIQUE: Values in a column must be unique across the dataset.

    • CHECK: Custom condition for validity checks (e.g., valid semester).

SQL Data Types

Common SQL Data Types

  • DATE: Represents dates.

  • TIME: Represents time of day.

  • TIMESTAMP: Combines date and time.

  • INTERVAL: Represents a span of time.

  • BLOB/CLOB: Used for large objects (binary/character).

Authorization in SQL

Privileges

  • Different rights granted to users include:

    • SELECT: Read access.

    • INSERT: Add new data.

    • UPDATE: Modify existing data.

    • DELETE: Remove data.

  • Use GRANT and REVOKE statements to manage permissions on database objects.