Docstoc

The Relational Model 1_ Introduction_ QBE_ and Relational Algebra

Document Sample
The Relational Model 1_ Introduction_ QBE_ and Relational Algebra Powered By Docstoc
					THE RELATIONAL MODEL 1: INTRODUCTIO
QBE, AND RELATIONAL ALGEBRA
Chapter 2
Instructional Objectives
Introduce Relational Databases
  Discuss associated terms and concepts
Relational Algebra
In-class exercises
Relational databases
    What is a relation?
    
    
    What are its characteristics?
    1.
    2.
    3.
    4.
    5.
Relational databases
What is a relational database?
  A collection of relations (or related tables)
Shorthand notation: (used to depict relational
 database structure)
  General notation:
    Table name (attribute 1, …., attribute n)
    Underline primary key (or composite key)
  Example:
    Order (OrderNum, OrderDate, CustomerNum)
Relational Data Manipulation
Four approaches:
   Relational Algebra:
     Operators that work on relation (+,-,etc.)
   Relational calculus:
     Not used in commercial database processing
     Express what we want (and not how to get it)
   Transform-oriented languages:
     Nonprocedural
     SQL
   QBE:
     MS Access uses it
Relational Algebra
 What is it?
  
  
 Why learn about it?
  
  
Commands
Select
  
Project
  
Join
  
  
    Select
8


    Includes the word WHERE followed by a condition
    Example:
     SELECT Customer WHERE
     CustomerNum=282
             GIVING Answer
    Project
9


    Includes the word OVER followed by a list of the
     columns to be included
    Example:
      PROJECT Customer OVER (CustomerNum, CustomerName)
           GIVING Answer
     Join
10


     Allows extraction of data from more than one table
     Natural join: joins records from each original table
      that is common to both tables
     Outer join: joins records from each original table
      including records not common to both tables
Normal Set Operations
 Union (A+B=C)
   
   
 Intersection
   
 Difference (A-B=C)
   
 Product (A*B)
   
 Division (p.65)
     Union
12




      JOIN Orders, Customer
           WHERE
        Orders.CustomerNum=Customer.CustomerNum
           GIVING Temp1
      PROJECT Temp1 OVER CustomerNum, CustomerName
           GIVING Temp2
      SELECT Customer WHERE RepNum='65'
           GIVING Temp3
      PROJECT Temp3 OVER CustomerNum, CustomerName
           GIVING Temp4
      UNION Temp2 WITH Temp4 GIVING Answer
     Intersection
13

     JOIN Orders, Customer
          WHERE Orders.CustomerNum=Customer.CustomerNum
          GIVING Temp1
     PROJECT Temp1 OVER CustomerNum, CustomerName
          GIVING Temp2
     SELECT Customer WHERE RepNum='65'
          GIVING Temp3
     PROJECT Temp3 OVER CustomerNum, CustomerName
          GIVING Temp4
     INTERSECT Temp2 WITH Temp4 GIVING Answer
     Difference
14

     JOIN Orders, Customer
          WHERE Orders.CustomerNum=Customer.CustomerNum
          GIVING Temp1
     PROJECT Temp1 OVER CustomerNum, CustomerName
          GIVING Temp2
     SELECT Customer WHERE RepNum='65'
          GIVING Temp3
     PROJECT Temp3 OVER CustomerNum, CustomerName
          GIVING Temp4
     SUBTRACT Temp4 FROM Temp2 GIVING Answer
     Product
15

      Mathematically called the Cartesian product
      Table obtained by concatenating every row in first table with every row in
       second table




                       FIGURE 2-43: Product of two tables
     Division
16


     Best illustrated by considering division of a table with
      two columns by a table with a single column
     Result contains quotient




                FIGURE 2-44: Dividing one table by another

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:4/20/2014
language:Latin
pages:16