SQL Natural Join

Join Expressions

• Join operations combine two relations, creating a new relation
• A join is a Cartesian product where tuples from both relations meet a specified condition
• Attributes present in the resultant join are also specified

Types of Joins

• Natural Join: Matches and retains a single copy of tuples sharing common attributes
• Inner Join
• Outer Join

Natural Join in SQL

• Natural join matches tuples with the same values for all common attributes, keeping one copy of each common column
• To list instructor names and course IDs they teach:
  • Use select name, course_id from students, takes where student.ID = takes.ID;
  • A "natural join" SQL query is: select name, course_id from student natural join takes;
• Multiple relations in the from clause can be combined using natural join with a where clause

Dangers of Natural Join

• Unrelated attributes sharing a name may lead to incorrect equating
• To list student names and titles of courses they've taken (Correct Version):
  • select name, title from student natural join takes, course where takes.course_id = course.course_id;
• Incorrect version, omits pairs if a student's department differs from the course.
  • select name, title from student natural join takes natural join course;

Outer Join

• Its an extension of the join operation that prevents data loss.
• Null values are used when there tuple matches in the other relation.
• Computes the join and includes tuples from one relation without matches in the other.
• Three forms exist: left, right, and full outer join

Outer Join examples

• Relations course and prereq can be joined
• Course info missing CS-347
• prereq info missing CS-315

Left Outer Join

• Keeps all tuples from the left relation (course)
• Natural left outer join query: course natural left outer join prereq
• In relational algebra: course ⟕ prereq

Right Outer Join

• Keeps all tuples from the right relation (prereq)
• Natural right outer join query: course natural right outer join prereq
• In relational algebra: course ⟖ prereq

Full Outer Join

• Keeps all tuples from both relations
• Natural full outer join query: course natural full outer join prereq
• In relational algebra: course ⟗ prereq

Join Types and Conditions

• Join operations combine two relations, creating a new relation
• These operations functions as subquery expressions inside the from clause
• Join condition: specifies how tuples from the two relations should match
• Join type: determines how unmatched tuples are treated (based on the join condition)

Joined Relations examples

• Natural Right Outer Join: course natural right outer join prereq
• Full Outer Join using: course full outer join prereq using (course_id)
• Inner Join with condition: course inner join prereq on course.course_id = prereq.course_id
• Left Outer Join with condition: course left outer join prereq on course.course_id = prereq.course_id

Views

• Views prevent all users from seeing the entire logical model
• The view is implemented by
  • select ID, name, dept_name from instructor
• A view hides data from certain users
• A "virtual relation" not in the conceptual model is referred to as a view

View Definition

• Is defined using: create view v as < query expression >
• The <query expression> can be any legal SQL expression, with the view name represented by v
• After definition, a view name can be used to represent the virtual relation
• View definition saves expression rather than creating a new relation by evaluating the query
• The expression is substituted when queries use the view

Examples of View Definition and Use:

• A view of instructors without their salary:
  • create view faculty as select ID, name, dept_name from instructor
• Finding all instructors in the Biology department:
  • select name from faculty where dept_name = 'Biology'
• Creating a view of department salary totals:
  • create view departments_total_salary(dept_name, total_salary) as select dept_name, sum (salary) from instructor group by dept_name;

Views defined using other views:

• A view can be used in defining another view
• A view relation v1 depends directly on v2 if v2 is in expression defining v1
• A view relation v1 depends on v2
  • If v1 depends directly on v2
  • There is a path of dependencies from v1 to v2
• A view relation v is recursive if it depends on itself

Example of views defined using other views:

• create view physics_fall_2017 as select course.course_id, sec_id, building, room_number from course, section where course.course_id = section.course_id and course.dept_name = 'Physics' and section.semester = 'Fall' and section.year = '2017’;
• create view physics_fall_2017_watson as select course_id, room_number from physics_fall_2017 where building= 'Watson';

View expanision

• Views defined in terms of other views can be re-expressed
• For example, from:
  • create view physics_fall_2017_watson as select course_id, room_number from physics_fall_2017 where building= 'Watson'
• To:
  • create view physics_fall_2017_watson as select course_id, room_number from (select course.course_id, building, room_number from course, section where course.course_id = section.course_id and course.dept_name = 'Physics' and section.semester = 'Fall' and section.year = '2017') where building= 'Watson';

View Expansion (Cont.)

• v1 is defined by an expression e1 which may contain uses of view relations
• View expansion repeats the following replacement step
  • Find any view relation vi in e1
  • Replace the view relation vi by the expression defining vi

Materialized Views

• Some database systems allow physical storage of view relations
• When view is defined it also creates a physical copy
• Updated used relations can be out of date
• Its important to maintain the view, by updating when relations are updated

Update of a View

• To add a new tuple and it must be represented by insertion into the instructor relation
  • insert into faculty values ('30765', 'Green', 'Music');
• Must have a value for salary
• Two approaches:
  • Reject the insert
  • Insert the tuple: ('30765', 'Green', 'Music', null) into instructor relation

Situations where updates cannot be translated

• create view instructor_info as select ID, name, building from instructor, department where instructor.dept_name = department.dept_name;
• Issues from,insert into instructor_info values ('69987', 'White', 'Taylor');
  • Which department, if multiple departments in Taylor?
  • What if no department is in Taylor?

Situations where some updates aren't possible

• create view history_instructors as select * from instructor where dept_name= 'History';
• Issue with, ('25566', 'Brown', 'Biology', 100000) into history_instructors?

View Updates in SQL

• Implementations mostly allow updates only on simple views
• From clause must only have one database relation
• Select clause contains only relation attribute names and has no expressions, aggregates, or distinct specification
• Attributes not listed in the select clause can be null
• The query does not have a group by or having clause

Transactions

• A transaction is a sequence of query and/or update statements, representing a "unit" of work
• SQL standard says transaction begin implicitly when SQL executed
• Ends with:
  • Commit work: updates become permanent
  • Rollback work: all statements in transaction are undone
• Fully executed or rolled back
• Atomic transaction isolated from concurrent transactions

Integrity Constraints

• Guard against accidental damage
• Ensures that authorized changes don't cause data inconsistencies.
  • Checking account balance > $10,000.00
  • Bank employee salary >= $4.00/hour
  • Customer must have (non-null) phone number

Constraints on a Single Relation include

• not null
• primary key
• unique
• check (P), where P is a predicate

Not Null Constraint

• Enforces that a specific attribute cannot contain null values.
• To ensure that name and budget aren't null
  • name varchar(20) not null
  • budget numeric(12,2) not null

Unique Constraints

• unique ( A1, A2, …, Am)
• States that the attributes A1, A2, …, Am form a candidate key.
• Is are permitted to be null - unlike primary keys

Check clause

• check (P) clause applies a predicate P to every applicable tuple in a relation.
• Example guaranteeing that semester is from appropriate season
  • create table section (course_id varchar (8), sec_id varchar (8), semester varchar (6), year numeric (4,0), building varchar (15), room_number varchar (7), time slot id varchar (4), primary key (course_id, sec_id, semester, year), check (semester in ('Fall', 'Winter', 'Spring', 'Summer')))

Referential Integrity

• Value in one relation also appears for a given set of attributes in another relation.
  • Department name in instructor relation
  • Then there is a department relation tuple named Biology
• Example
  • A is said to be a foreign key of R if for any values of A appearing in R attributes values also appear in S when R and S contain attributes A and where A is the primary key of S.

Referential Integrity (Cont.)

• Is specified as part of SQL create table statement
  • foreign key (dept_name) references department
• By default, a foreign key references the primary-key attributes of the referenced table
• SQL allows an explicit attribute list for the referenced relation
  • foreign key (dept_name) references department (dept_name)

Cascading Actions in Referential Integrity

• Normal procedure is to reject when referential-integrity constraint is violated
• Or alternative is to cascade if deleting or updating
  • create table course ((… dept_name varchar(20), foreign key (dept_name) references department on delete cascade on update cascade,...)
• Can alternatively use set null or set default

Integrity Constraint Violation During Transactions

• Inserting a person tuple has 3 cases
  • Insert father and mother before insert person
  • Set father and mother attributes to null initially, update after inserting all persons (if is not declared to be not null)
  • Defer constraint checking
  • create table person (ID char(10), name char(40), mother char(10), father char(10), primary key ID, foreign key father references person, foreign key mother references person)

Complex Check Conditions

• Check conditions allow subqueries
  • check (time_slot_id in (select time_slot_id from time_slot))
    • time_slot_id in section relation has to be the time_slot identifier in time_slot relation

Assertions

• Predicate that enforces a condition that the database always satisfies.
• Following constraints can be expressed using assertions:
  • In student relation, attribute tot_cred must equal the sum of credits of courses the student successfully completed
  • An instructor cannot teach in two different classrooms in a semester
• In SQL :create assertion check ();

Built-in Data Types in SQL

• date: Stores year, month, and the day date ('2005-7-27')
• time: Stores time of day, with hours, minutes, and optional seconds ('09:00:30')
• timestamp: Stores date and time of day ('2005-7-27 09:00:30.75')
• interval: Stores period of time with interval '1' day - Values for both value + date/time/timestamp can be made available

Large Object Types

• These allow large objects (photos, videos, etc.)
  • blob: Object is a large collection of uninterpreted binary data
  • clob: Object is a large collection of character data
• A pointer is returned rather than the large object itself, when a query returns a large object

User-Defined Types

• A basic create type can be created
  • create type Dollars as numeric (12,2) final.
• Create an example user type
  • create table department (dept_name varchar (20), building varchar (15), budget Dollars);

Domains

• User-defined domain types can be created in SQL
• create domain person_name char(20) not null
• Domains and types are similar and can have constraints (e.g. not null) specified
• Create an example domain for the degree_level test
• create domain degree_level varchar(10) constraint degree_level_test check (value in ('Bachelors', 'Masters', 'Doctorate'));

Index Creation

• Many queries reference small data portions in a larger table.
• Its inefficient to read every record to find a record with a particular value
• Allows database system to efficiently scan for specified data.
• Syntax
  • create index on (attribute);

Index Creation Example

• Create the following table
  • 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))
• A studentID can be indexed
  • create index studentID_index on student(ID)
• So then
  • select * from student where ID = '12345'
  • Can scan by index and get rid of the need to look at all records of student

Authorization

• Forms of authorization allows controls over parts of the database.
• read: allows reading data, but not modification
• insert: allows insertion of new data, but not modification of existing data
• update: allows modification, but not deletion of data.
• delete: allows deletion of data
• These authorizations is a privilege and grants varying access to relations or views

Authorization (Cont.)

• Forms of authorization to modify the database schema - index: creation/deletion of indices. - resources: creation of new relations - alteration: addition/deletion of attributes in a relation - drop: deletion of relations

Authorization Specification in SQL

• grant statement confer authorization to relation or view to a list of user-ids, public users or a role
• grant select on department to Amit, Satoshi
• Privileges granted on a view does not grant any privileges on the underlying relations.
• The grantor must already hold the privilege on the specified item or database administrator

Privileges in SQL

• select: Provides read access or permits querying a relation.
  • Granting select: grant select on instructor to U1, U2, U3
    • Ability to insert tuples
    • Ability to update with a SQL Update statement
    • Ability to delete tuples
    • All specified privileges.

Revoking Authorization in SQL

• The revoke statement removes authorization.
  • revoke on from
    • revoke select on student from U1, U2, U3
• all revokes all privileges a user holds Public loses permissions if includes public

Roles

• Is a method to identify access / update abilities to database
• Created using create a role
  • create role instructor
• Once created user can be granted access to granted role
  • grant to

Roles Example

• Role is created
• grant instructor to Amit;
• Permissions available to grant to a role grant select on takes to instructor;
• Other roles granted to role users includes user as well -create role teaching_assistant grant teaching_assistant to instructor; - Instructor inherits all privileges of teaching_assistant
• Chaining roles by example - create role dean; grant instructor to dean; grant dean to Satoshi;

Authorization on Views

• Grants viewcreate view geo_instructor as(select * from instructor where dept_name = 'Geology');grant select on geo_instructor to geo_staff
• Suppose that a geo_staff member executes select * from geo_instructor;

If in case:

• If the viewer does not have permission of what the instructor has
• What happens where creator doesn't have permissions