Chapter 2 l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 34

Chapter 2 PowerPoint PPT Presentation


  • 391 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Chapter 2

An Image/Link below is provided (as is) to download presentation

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 - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Chapter 2 l.jpg

Chapter 2

The Relational Model 1:

Introduction, QBE, and

Relational Algebra

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


Objectives l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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


Slide7 l.jpg

Simple Queries

Figure 2.3

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


Slide8 l.jpg

Simple Queries (con’t.)

Figure 2.4

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


Slide9 l.jpg

Query that Includes All Fields

Figures 2.5 – 2.6

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


Slide10 l.jpg

Query with Simple Criteria

Figures 2.7 – 2.8

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


Slide11 l.jpg

Query Using AND Criteria

Figures 2.9 – 2.10

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


Slide12 l.jpg

Query Using OR Criteria

Figures 2.11 – 2.12

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


Slide13 l.jpg

Query Using Two

Conditions on a Single Field

Figures 2.13 – 2.14

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


Slide14 l.jpg

Query Using Computed Field

Figures 2.15 – 2.16

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


Slide15 l.jpg

Query to Count Records

Figures 2.17 – 2.18

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


Slide16 l.jpg

Query to Calculate an Average

Figures 2.19 – 2.20

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


Slide17 l.jpg

Query to Sort Records

Figures 2.23 – 2.24

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


Slide18 l.jpg

Query to Sort on Multiple Keys

Figure 2.27

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


Slide19 l.jpg

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

Figure 2.28

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


Slide20 l.jpg

Query to Join Tables

Figure 2.29

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


Slide21 l.jpg

Query to Join Tables (con’t.)

Figure 2.30

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


Slide22 l.jpg

Query to Restrict Records in a Join

Figures 2.31 – 2.32

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


Slide23 l.jpg

Update Query

Figure 2.35

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


Slide24 l.jpg

Delete Query

Figure 2.36

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


Slide25 l.jpg

Make-Table Query

Figure 2.37

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


Slide26 l.jpg

Make-Table Query (con’t.)

Figure 2.39

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


Relational algebra l.jpg

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


Slide28 l.jpg

Customer & Sales Rep Tables

Figure 2.40

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


Slide29 l.jpg

Join of Customer and Sales Rep

Figure 2.41

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


Slide30 l.jpg

Outer Join of Customer and Sales Rep

Figure 2.42

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


Normal set operations l.jpg

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 l.jpg

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


Slide33 l.jpg

Product of Two Tables

Figure 2.43

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


Slide34 l.jpg

Dividing One Table by Another

Figure 2.44

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


  • Login