INFO 340 - April 14th, 2004
Notes By: Fortier, Egaas, Prins

3 to 5 pages to be turned in for the D1. Around 5 pages. Evidence of careful thinking about the project. Think about some of the issues.
Data model on 1 page. One page to discuss the data model. Introduction as well.

Relational Integrity
'Never do harm to your data'
'Protect your data'


Null
    > Occasionally, you don't know the value for a value of an attribute
    > Null means "unknown value"
    > Null is different than 0 or "" (nothing string value)
    
Required Data
    > A value of an attribute must have a value
    > CREATE TABLE Meeting
        client      varchar(5)  NOT NULL
        salesRep    varchar(5)  NOT NULL,
        meetingType char(1)     NOT NULL,
        meetingDate date()      NOT NULL

Domain Integrity
    > Recall that values of an attribute all come from the same domain
    
In SQL
    > CREATE DOMAIN MeetingType as CHAR(1)
        CHECK(VALUE IN('B','C','D'));
    > CREATE TABLE foo (
        mType   MeetingType
        ...);
        
Entity Integrity
    > No attribute of the primary key can be null
        - Thus, all tuples can be identified

Referential Integirity
    > If a foreign key exists it must match a value of a candidate key in the home relation OR it must be null
    * Sailor (sid:integer; sname:string; rating:integer; age:real)
    * Boat (bid:integer; bname:string; color:string)

Enterprise Constraints
    > Business rules are 'embedded' in the database
        - E.g., No manager can manage more than 10 employees
    > Benefits
        - Simplifies application programming
        - Enables changes more easily

Summary
    > Tables are mathematical relations and SQL is the mechanism for manipulating relations
    > Determining keys requires an analysis of the domain and judgment
    > Relational integrity can protect your data

<<<<< Relational Algebra >>>>>
Relational Algebra
    > A formalism for describing the operations that can be applied to relations
    > Both operands and results are relations
        Relation_A = FUNCTION_1(Relation_B)

Selection
    > Defines a relations that contains the tuples from R that satisfy the predicate

Projection
    > Defines a relation that is a vertical subset of R

Union
    R ��� S
    > Defines a relation that contains all the tuples of R or S or both R and S. Duplicates are removed. R and S must be *union-compatible*.

    SQL Example:
        (SELECT ip, userid, date, time FROM Traffic1)
        UNION
        (SELECT ip, userid, date, time FROM Traffic2);

Set Difference
    R ��� S
    > Defines a relation containing the tuples in R but not S. R and S are *union-compatible*.

Intersection
    R �� S
    > All tuples that are in both R and S. Rand S must be *union-compatible*.
    

Cartesian Product
    R �� S
    > Defines a relation that is the concatenation of every tuple of relation R with every tuple of relation S.

Exercise
    Use relational algebra to express:
        Find the names of sailors who have reserved a red boat
    T1 ��� B �� S �� R                  // cartesian product of Boat Sailor and Reserves
    T2 ��� ��(b.color='red')(T1)       // 
    T3 ��� ��(b.id=r.bid)(T2)          // 
    T4 ��� ��(s.id=r.id)(T3)           // 
    T5 ��� ���(s.name)(T4)              //
    
    OR
    ���s.name(
        ��(b.color='red' && b.id=r.bid && s.id = r.sid)(
            B �� S �� R
        )
    )
    //SQL equivalent
    SELECT s.name
    FROM Boat b, Reserves r, Sailor s
    WHERE (s.id = r.sid) and (b.bid = r.bid) and (b.color = 'red');
    
    Select s.sid, s.fname, s.lname, s.salary from Sailor as s inner join Reserve as r on s.sid = r.sid inner join Boat as b on r.bid = b.bid where b.color = 'Red';

Join Operations
    > Join operations can be defined in terms of the Cartesian product, selection and projection
        - Theta Join
        - Equijoin
        - _Natural Join_
        - _Outer Join_
        - Semijoin