This presentation is the property of its rightful owner.
1 / 34

# Chapter 2 PowerPoint PPT Presentation

Chapter 2. The Relational Model 1: Introduction, QBE, and Relational Algebra. Objectives. Describe the relational model Understand Query-by-Example (QBE) Use Criteria in QBE Create Calculated Columns in QBE Calculate Statistics in QBE. Objectives (con’t.). Sort data in QBE

Chapter 2

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

### Chapter 2

The Relational Model 1:

Introduction, QBE, and

Relational Algebra

Concepts of Database Management, 4th Edition, Pratt & Adamski

### Objectives

• Describe the relational model

• Understand Query-by-Example (QBE)

• Use Criteria in QBE

• Create Calculated Columns in QBE

• Calculate Statistics in QBE

Concepts of Database Management, 4th Edition, Pratt & Adamski

### Objectives (con’t.)

• Sort data in QBE

• Join Tables in QBE

• Update data using QBE

• Understand relational algebra

Concepts of Database Management, 4th Edition, Pratt & Adamski

### Relational Databases

• Collection of tables

• Each entity in own table

• Attributes are fields (columns) in table

• Relationships are common columns in two or more tables

• Order of rows and columns is immaterial

• Repeating groups are not permitted

• Entries with repeating groups are unnormalized

Concepts of Database Management, 4th Edition, Pratt & Adamski

### Relations

• Two dimensional table in which:

• Entries are single-valued

• Each column (field or attribute) has a distinct name

• All values in a column represent the same attribute

• Order of columns is immaterial

• Each row (record or tuple) is distinct

• Order of rows is immaterial

Concepts of Database Management, 4th Edition, Pratt & Adamski

### Query-by-Example (QBE)

• Query

• Questions represented in a way the DBMS can recognize and process

• QBE

• Visual approach to writing queries

• Used in MS-Access

Concepts of Database Management, 4th Edition, Pratt & Adamski

Simple Queries

Figure 2.3

Concepts of Database Management, 4th Edition, Pratt & Adamski

Simple Queries (con’t.)

Figure 2.4

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query that Includes All Fields

Figures 2.5 – 2.6

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query with Simple Criteria

Figures 2.7 – 2.8

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query Using AND Criteria

Figures 2.9 – 2.10

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query Using OR Criteria

Figures 2.11 – 2.12

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query Using Two

Conditions on a Single Field

Figures 2.13 – 2.14

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query Using Computed Field

Figures 2.15 – 2.16

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query to Count Records

Figures 2.17 – 2.18

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query to Calculate an Average

Figures 2.19 – 2.20

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query to Sort Records

Figures 2.23 – 2.24

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query to Sort on Multiple Keys

Figure 2.27

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query to Sort on Multiple Keys (con’t.)

Figure 2.28

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query to Join Tables

Figure 2.29

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query to Join Tables (con’t.)

Figure 2.30

Concepts of Database Management, 4th Edition, Pratt & Adamski

Query to Restrict Records in a Join

Figures 2.31 – 2.32

Concepts of Database Management, 4th Edition, Pratt & Adamski

Update Query

Figure 2.35

Concepts of Database Management, 4th Edition, Pratt & Adamski

Delete Query

Figure 2.36

Concepts of Database Management, 4th Edition, Pratt & Adamski

Make-Table Query

Figure 2.37

Concepts of Database Management, 4th Edition, Pratt & Adamski

Make-Table Query (con’t.)

Figure 2.39

Concepts of Database Management, 4th Edition, Pratt & Adamski

### Relational Algebra

• Theoretical way of manipulating a relational database to produce new tables

• Major commands

• SELECT

• Retrieves certain rows

• PROJECT

• Include certain columns

• JOIN

• Pull data from more than one table

Concepts of Database Management, 4th Edition, Pratt & Adamski

Customer & Sales Rep Tables

Figure 2.40

Concepts of Database Management, 4th Edition, Pratt & Adamski

Join of Customer and Sales Rep

Figure 2.41

Concepts of Database Management, 4th Edition, Pratt & Adamski

Outer Join of Customer and Sales Rep

Figure 2.42

Concepts of Database Management, 4th Edition, Pratt & Adamski

### Normal Set Operations

• Union of two tables

• Result contains all rows that are in either the first table, the second table, or both

• Intersection of two tables

• Result contains all rows common to both

• Difference of tables

• Result is the set of rows in one table but not the other

Concepts of Database Management, 4th Edition, Pratt & Adamski

### Normal Set Operations (con’t.)

• Product of two tables

• Result contains Cartesian product

• Obtained by concatenating every row in first table with every row in second table

• Division Process

• Result contains quotient

Concepts of Database Management, 4th Edition, Pratt & Adamski

Product of Two Tables

Figure 2.43

Concepts of Database Management, 4th Edition, Pratt & Adamski

Dividing One Table by Another

Figure 2.44

Concepts of Database Management, 4th Edition, Pratt & Adamski