Concepts of Database Management Sixth Edition

1
Concepts of Database ManagementSixth Edition
Chapter 2 The Relational Model 1 Introduction,
QBE, and Relational Algebra
2
Objectives

• Relational model
• Query-By-Example (QBE)?
• Use criteria
• calculated columns
• Use functions
• Sort data
• Join tables
• Update data
• Relational algebra

Concepts of Database Management
2
3
Relational Databases

• A relational database is a collection of tables
• Entity Table
• Attributes of the entity Columns in the table
• Relationships common columns in two or more
  tables
• Should not permit fields that have multiple
  values in a table

Concepts of Database Management
3
4
Relational Databases (continued)?

• Relation two-dimensional table in which
• Entries are single-valued
• Each column has a distinct name (called the
  attribute name)?
• All values in a column are values of the same
  attribute
• Each row is distinct
• Order of columns is immaterial
• Order of rows is immaterial

Concepts of Database Management
4
5
Relational Databases (continued)?

• Relational database collection of relations
• Unnormalized relation
• all properties of a relation except for the first
  item

Concepts of Database Management
5
6
Unnormalized Relation
7
Relational Databases (continued)?

• Database structure representation
• tablename(column1, column2, ...)?
• Notation for duplicate column names within a
  database tablename.columnname
• You qualify the column names
• Primary key

Concepts of Database Management
7
8
Query-by-Example (QBE)?

• Query question represented in a way the DBMS can
  recognize and process
• Answer data from one or more tables
• Query-By-Example (QBE)?
• Visual approach to writing queries
• Users ask their questions using on-screen tools
• Data appears on the screen in tabular form

Concepts of Database Management
8
9
Query-by-Example (QBE) (continued)?

• Query window in Access has two panes
• Table pane field list for each table you want to
  query
• design grid, where you specify
• Fields to be included in the query results
• Sort order for query results
• Any criteria the records must satisfy

Adding a table in the table pane is significant
Concepts of Database Management
9
10
Simple Queries

• To include a field in an Access query,
  double-click the field in the field list to place
  it in the design grid
• Clicking Run button in Results group on the Query
  Tools Design tab runs query and displays query
  results
• Add all fields from a table to the design grid by
  double-clicking the asterisk in the tables field
  list

Concepts of Database Management
10
11
Simple Queries (continued)?
FIGURE 2-3 Fields added to the design grid
Concepts of Database Management
11
12
Simple Queries (continued)?
FIGURE 2-4 Query results
Concepts of Database Management
12
13
Simple Criteria

• Criteria conditions that rows must satisfy to be
  shown as a result of a query
• To enter a criterion for a field
• Include field in the design grid
• Enter criterion in Criteria row for that field

Concepts of Database Management
13
14
Simple Criteria (continued)?

• Comparison operators
• (equal to)?
• gt (greater than)?
• lt (less than)?
• gt (greater than or equal to)?
• lt (less than or equal to)?
• NOT (not equal to)?

Concepts of Database Management
14
15
Compound Criteria

• Compound criteria, or compound conditions
• AND operator
• OR operator
• To create an AND criterion in QBE
• Place the criteria for multiple fields on the
  same Criteria row in the design grid
• To create an OR criterion in QBE
• Place the criteria for multiple fields on
  different Criteria rows in the design grid

Concepts of Database Management
15
16
Compound Criteria (continued)?
FIGURE 2-9 Query that uses an AND criterion
Concepts of Database Management
16
17
Compound Criteria (continued)?
FIGURE 2-11 Query that uses an OR criterion
Concepts of Database Management
17
18
Computed Fields

• Computed field or calculated field
• Enter it directly as a Field
• right-click, Zoom in a Field and enter it there.
• AvailableCredit CreditLimit Balance

Concepts of Database Management
18
19
Computed Fields (continued)?
FIGURE 2-15 Query that uses a computed field
Concepts of Database Management
19
20
Aggregate Functions

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

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

Concepts of Database Management
20
21
Functions (continued)?
FIGURE 2-17 Query to count records
Concepts of Database Management
21
22
Functions (continued)?
FIGURE 2-18 Query results
Concepts of Database Management
22
23
Grouping

• Grouping In a query result, groups of records
  that share some common characteristic calculate
  an aggregate function on each group
• Select Group By operator in the Total row for the
  field on which to group

Concepts of Database Management
23
24
Grouping (continued)?
FIGURE 2-21 Query to group records
Concepts of Database Management
24
25
Sorting

• Sorting
• Sort key
• Major sort key (primary sort key)?
• Minor sort key (secondary sort key)?

Concepts of Database Management
25
26
Sorting (continued)?
FIGURE 2-23 Query to sort records
Concepts of Database Management
26
27
Sorting on Multiple Keys

• Major (primary) sort key has to be to the left of
  the minor (secondary) sort key in the design grid

Concepts of Database Management
27
28
Sorting on Multiple Keys (continued)?
FIGURE 2-27 Correct query design to sort by
RepNum and then by CustomerName
Concepts of Database Management
28
29
Joining Tables

• Queries to select data from more than one table
• based on matching fields in corresponding columns
• Join line

Concepts of Database Management
29
30
Joining Tables (continued)?
FIGURE 2-29 Query design to join two tables
Concepts of Database Management
30
31
Joining Multiple Tables

• To join three or more tables
• Add all tables in the upper pane
• Add the fields to appear in query results to
  design grid
• Examples
• Orders, Customer, Rep
• What if there is no Join Line between the tables?
• Orders, Rep

Concepts of Database Management
31
32
Using an Update Query

• Update query a query that changes data
• To change a query to an update query
• Select Update button in the Query Type group.
• Update To row is added
• Used to indicate how to update data selected by
  the query

Concepts of Database Management
32
33
Using an Update Query (continued)?
FIGURE 2-35 Query design to update data
Concepts of Database Management
33
34
Using a Delete Query

• Delete query permanently deletes all records
  satisfying the criteria entered in the query
• To change query type to a delete query
• Select Delete button in the Query Type group
• Delete row is added
• Indicates this is a delete query

Concepts of Database Management
34
35
Using a Delete Query (continued)?
FIGURE 2-36 Query design to delete records
Concepts of Database Management
35
36
Using a Make-Table Query

• Make-table query creates a new table using
  results of a query
• Select Make Table button in the Query Type
• In Make Table dialog box, enter the new tables
  name and choose where to create it

Concepts of Database Management
36
37
Using a Make-Table Query (continued)?
FIGURE 2-38 Make Table dialog box
Concepts of Database Management
37
38
Relational Algebra

• Theoretical way of manipulating a relational
  database
• operations that act on existing tables to produce
  new tables
• end with a GIVING clause, followed by a table
  name
• the result of the command is placed in a
  temporary table with the specified name

Concepts of Database Management
38
39
Select

• Retrieves certain rows from an existing table
  (based on criteria) and saves them as a new table
• Example
• SELECT Customer WHERE CustomerNum282
• GIVING Answer

Concepts of Database Management
39
40
Project

• Takes a vertical subset of a table
• Example
• PROJECT Customer OVER (CustomerNum,
  CustomerName)? GIVING Answer

Concepts of Database Management
40
41
Join

• Allows extraction of data from more than one
  table
• Join column
• Rows in new table will be the concatenation of
  rows from each original table

Concepts of Database Management
41
42
Natural Join and Outer Join

• Natural join only include rows where the join
  fields from both tables are equal
• Outer join
• Left include all records from the left table and
  only the records from the right table where the
  joined fields are equal
• Right
• Example join Rep and Customer

Concepts of Database Management
42
43
Normal Set Operations

• Union of tables A and B
• rows that are in either A or B or in both
• Intersection of tables A and B
• rows that are common in both A and B
• Difference of tables A and B (A B)?
• rows that are in A but not in B

Concepts of Database Management
43
44
Union

• Two tables are union compatible when
• They have the same number of columns
• Corresponding columns represent the same type of
  data
• JOIN Orders, Customer
• WHERE Orders.CustomerNumCustomer.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

Concepts of Database Management
44
45
Intersection

• Performed by the INTERSECT command
• JOIN Orders, Customer
• WHERE Orders.CustomerNumCustomer.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

Concepts of Database Management
45
46
Difference

• Performed by the SUBTRACT command
• JOIN Orders, Customer
• WHERE Orders.CustomerNumCustomer.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

Concepts of Database Management
46
47
Product

• Cartesian product
• Table obtained by concatenating every row in
  first table with every row in second table

FIGURE 2-43 Product of two tables
Concepts of Database Management
47
48
Product Exercise

• Calculate Customer X Rep
• Save that in a table called product
• Select from product only the rows for which
  Customer.RepNum Rep.RepNum

49
Division

• 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
Concepts of Database Management
49
50
Division
The product of these tables is included in the
original table
(c) Wikipedia
51
Summary

• Relation two-dimensional table in which the
  entries are single-valued, each field has a
  distinct name, all values in a field are values
  of the same attribute, order of fields is
  immaterial, each row is distinct, and order of
  rows is immaterial
• Relational database collection of relations
• A tables primary key is the field or fields that
  uniquely identify a given row within the table
• Query-By-Example (QBE) is a visual tool for
  manipulating relational databases

Concepts of Database Management
51
52
Summary (continued)?

• To indicate AND criteria in an Access query,
  place both criteria in the same Criteria row of
  the design grid to indicate OR criteria, place
  criteria on separate Criteria rows of the design
  grid
• To create a computed field in Access, enter
  expression in the desired column of design grid
• To use functions to perform calculations in
  Access, include the appropriate function in the
  Total row
• To sort query results in Access, select Ascending
  or Descending in Sort row for the field or fields
  that are sort keys

Concepts of Database Management
52
53
Summary (continued)?

• To join tables in Access, place field lists for
  both tables in upper pane of Query window
• To make the same change to all records that
  satisfy certain criteria, use an update query
• To delete all records that satisfy certain
  criteria, use a delete query
• To save the results of a query as a table, use a
  make-table query
• Relational algebra is a theoretical method of
  manipulating relational databases

Concepts of Database Management
53
54
Summary (continued)?

• SELECT command selects only certain rows
• PROJECT command selects only certain columns
• JOIN command combines data from two or more
  tables based on common columns
• Normal set of operations union, intersection,
  and difference
• Product of two tables results from concatenating
  every row in the first with every row in the
  second
• Division process divides one table by another
  table

Concepts of Database Management
54