Concepts of Database Management, Fifth Edition

1
Concepts of Database Management, Fifth Edition

• Chapter 2
• The Relational Model 1
• Introduction, QBE, and
• Relational Algebra

2
Objectives

• Describe the relational model
• Understand Query-by-Example (QBE)
• Use Criteria in QBE
• Create Calculated Columns in QBE
• Calculate Statistics in QBE

3
Objectives (cont.)

• Sort data in QBE
• Join Tables in QBE
• Update data using QBE
• Understand relational algebra

4
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

5
Premiere Products Sample Data
6
Premiere Products Sample Data
7
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

8
An Unnormalized Relation

• Relational Database a collection of relations
• Unnormalized relation
• When a structure satisfies all the properties of
  a relation except for the first item
• Some of the entries contain repeating groups and
  thus are not single-valued

9
Database Structure Representation

• Write the name of the table followed by a list of
  all columns within parentheses
• Each table should appear on its own line
• Use the notation, tablename.columnname, with
  duplicate column names within a database
• Using this combination qualifies column names
• Primary key the column or collection of columns
  that uniquely identifies a given row in a table

10
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

11
Figure 2.3 An Example of Simple Queries
12
Figure 2.4 Simple Queries (cont.)
13
Simple Criteria

• Criteria conditions that data must satisfy
• Criterion a single condition
• To display specific query results, enter the
  condition in the appropriate column in the design
  grid

14
Figures 2.7-2.8 Query with Simple Criteria
15
Comparison (Relational) Operators

• Finds something other than an exact match
• Comparison operators are
• (equal to)
• gt (greater than)
• lt (less than)
• gt (greater than or equal to)
• lt (less than or equal to)
• NOT (not equal to)

16
Compound Criteria

• Combines comparison operators
• Many languages use AND or OR between the separate
  criteria
• In an AND criterion, both criteria must be true
• In an OR criterion, the overall criterion is true
  if either of the individual criteria is true

17
Figures 2.9-2-10 Query Using AND Criteria
18
Figures 2.11 2.12 Query Using OR Criteria
19
Computed Fields

• You can include calculated fields that are not in
  the database in queries
• Computed field (calculated field) a field that
  is the result of a calculation using one or more
  existing fields
• In a query that uses computed fields, if a field
  name contains spaces you must enclose it in
  square brackets

20
Figures 2.15-2.16 Query Using Computed Field
21
Calculating Statistics

• Built-in statistics (called aggregate functions
  in
  Access) include

• Count
• Sum
• Avg (average)
• Max (largest value)
• Min (smallest value)

• StDev (standard deviation)
• Var (variance)
• First
• Last

22
Figures 2.19-2.20 Query to Calculate an Average
23
Grouping

• Grouping creating groups of records that share
  some common characteristic
• Functions can be used in combination with
  grouping where statistics are calculated for
  groups of records

24
Sorting

• Sorting listing records in a querys results in
  a particular way
• It is possible to sort using more than one field
• Sort Key the field on which records are sorted
• Major sort key (primary sort key) the more
  important field
• Minor sort key (secondary sort key) the less
  important field
• Major sort key is on the left of the grid and the
  minor sort key is on the right

25
Figures 2.23-2.24 Query to Sort Records
26
Figure 2.27 Query to Sort on Multiple Keys
27
Figure 2.28 Query to Sort on Multiple Keys
(cont.)
28
Joining Tables

• Queries to select data from more than one table
• Join the tables based on matching fields in
  corresponding columns
• In an Access query, a join line between matching
  fields in the two tables will be created
  indicating how the tables are related
• When joining multiple tables
• Add all the tables involved to the upper pane
• Add the query results grid in the desired order

29
Figure 2.29 Query to Join Table
30
Figure 2.30 Query to Join Tables (cont.)
31
Update Query

• Update query a query that changes data
• Makes a specified change to all records
  satisfying the criteria in the query
• In Access, a new row is created that is used to
  indicate how to update the data selected by the
  query

32
Figure 2.35 Update Query
33
Delete Query

• Queries can be used to delete one or more records
  at a time
• Delete query deletes all the records satisfying
  the criteria entered into the query
• When you change the Query type to Delete Query,
  an extra row, called a Delete row, is added to
  the design grid

34
Figure 2.36 Delete Query
35
Make-Table Query

• Queries can be used to create a new table in
  either the current database or in a separate
  database
• Make-table query creates a new table using the
  query results
• The data added to the new table is separate from
  the original table in which it appears

36
Figure 2.37 Make-Table Query
37
Figure 2.39 Make-Table Query (cont.)
38
Relational Algebra

• Theoretical way of manipulating a relational
  database to produce new tables
• Relational algebra includes operations that act
  on existing tables to produce new tables
• Retrieving data using relational algebra involves
  issuing relational algebra commands to operate on
  existing tables to form a new table containing
  the desired information

39
Major commands

• SELECT retrieves certain rows from existing
  table
• PROJECT causes only certain columns to be
  included in the new table
• JOIN
• Allows extraction of data from more than one
  table
• Rows in new table will be the concatenation
  (combination) of a row from the first table and a
  row from the second

40
The Join Command Continued

• If there is a row in one table that does not
  match any row in the other table, that row will
  not appear in the result of the join
• PROJECT command can be used to restrict the
  output from the join
• Natural join joins the records from each
  original table that is common to both tables
• Outer join joins the records from each original
  table including the records not common to both
  tables

41
Normal Set Operations

• Union of two tables
• Result contains all rows that are in either the
  first table, the second table, or both
• Union compatible tables are union compatible if
  they have the same number of columns and their
  corresponding columns represent the same type of
  data
• Intersection of two tables
• Result contains all rows common to both
• Use the INTERSECT command

42
Normal Set Operations (cont.)

• Difference of tables
• Result is the set of rows in one table but not
  the other
• Performed by using the SUBTRACT command
• Product of two tables
• Mathematically called the Cartesian product
• Obtained by concatenating every row in first
  table with every row in second table

43
Normal Set Operations (cont.)

• Division Process
• Best illustrated by considering the division of a
  table with two columns by a table with a single
  column
• Result contains quotient

44
Summary

• Relation two-dimensional table in which the
  entries are single-valued
• Relational database collection of relations
• Field name qualified by preceding it with the
  table name and a period
• Tables primary key field or fields that
  uniquely identify a given row within the table
• Query-By-Example (QBE) visual tool for
  manipulating relational databases
• Created by completing on-screen forms

45
Summary (cont.)

• Queries can be used to select specific records
  based on certain criteria
• A make-table query creates a new table using the
  query results
• Relational Algebra - a theoretical way of
  manipulating a relational database to produce new
  tables
• The normal set operations include the union,
  intersection, difference and product of two
  tables and the division process