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

Chapter 2 PowerPoint PPT Presentation


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

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


  • Login