Chapter 9 - Foundations of Relational Implementation

Defining Relational Data
- Review of Terminology
  - A relation is a table
    1. entries are single valued
    2. all entries in a column (attributes) are of the same kind; each attribute has a domain (physical & logical description)
    3. no two rows(tuple) are identical
  - Confusion Regarding the Term Key
    1. logical key - one or more columns that uniquely identify a row in a relation
    2. physical key - column on which the DBMS builds an index
  - INDEXES (physical key) - used
    1. allows rows to be quickly accessed
    2. facilitate sorting rows
    3. to remove duplicates
- Implementing a Relational Database
  1. Defining the Database Structure to the DBMS
    1. Using a data definition language (DDL)
    2. Using a GUI like MS Access
  2. Allocating Media Space
  3. Creating the Database Data
Relational Data Manipulation
- Categories of Relational Data Manipulation Language
  1. relational algebra - defines operators that work on relations
  2. relational calculus - nonprocedural language used for relational data manipulation
  3. transform-oriented languages - SEQUEL, SQL
  4. query-by-example and query-by-form
- DML Interfaces to the DBMS
  - Data Manipulation by Means of Forms
  - Query/Update Language Interface (SQL)
  - Stored Procedure Interface (batch files)
  - Application Program Interface (API) - uses 3GLs

Relational Algebra

Relational Operators

JUNIOR(Snum, Name, Major)
HONOR-STUDENT(Number, Name, Interest)
STUDENT(SID, Name, Major,GradeLevel, Age)
CLASS(Name, Time, Room)
ENROLLMENT(StudentNumber, ClassName, PositionNumber)
FACULTY (FID, Name, Department)

UNION - (A + B): adding tuples from two relations to form a third [union compatible - same number of attributes from the same domain]
DIFFERENCE (A - B): relation where the tuples are in A but not B
INTERSECTION: relation containing tuples that are in both A and B
PRODUCT (AxB): concatenation of every tuple of one relation with every tuple from the second relation
PROJECTION (vertical subset): operation that selects specified attributes from a relation [ex. STUDENT[Major, GradeLevel]
SELECTION (horizontal subset): operation that selects specified tuples from a relation
JOIN (combination of product, selection, and possibly projection)

Expressing Queries in Relational Algebra
1. What are the names of all students? (STUDENT[Name])
2. What are the student numbers of all students enrolled in a class? (ENROLLMENT[StudentNumber])
3. What are the student numbers of all students not enrolled in a class? (STUDENT[SID] - ENROLLMENT[StudentNumber])
4. What are the numbers of students enrolled in the class BD445? (ENROLLMENT WHERE ClassName = 'BD445' [StudentNumber])
5. What are the names of the students enrolled in class BD445? (STUDENT JOIN (SID = StudentNumber) ENROLLMENT WHERE ClassName = 'BD445' [STUDENT.Name])
6. What are the names and meeting times of 'PARKS' classes? (STUDENT WHERE Name='PARKS' JOIN (SID = StudentNumber) ENROLLMENT JOIN (ClassName = Name) CLASS [CLASS.Name, Time])