# The Relational Model 1_ Introduction_ QBE_ and Relational Algebra by malj

VIEWS: 3 PAGES: 16

• pg 1
```									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
Project
9

Includes the word OVER followed by a list of the
columns to be included
Example:
PROJECT Customer OVER (CustomerNum, CustomerName)
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

```
To top