CS 103:Lecture 11

1
CS 103Lecture 11

• Hand-in Homework 10
• REVIEW SESSION CHANGE
• Monday Oct. 15 330-400 Room 260 Main
• Weds. Oct. 17 500-600 Room 260 Main
• Test 2 (Cumulative) Thursday Oct. 18

2
Supplier

• S001,Acme Supply,Boston
• S002,Furniture Farm,La Crosse
• S003,Home Supply,Boston
• S004,Big Box,New York
• S005,Acme Supply,Madison
• S006,Big Box,Cincinnati
• S007,Acme Supply,Denver
• S008,Home Supply,Cleveland
• S009,Big Deals,Chicago

3
Agent

• A001,Jane Doe,Boston,S003
• A002,John Doe,Boston,S001
• A003,John Smith,New York,S001
• A004,Sue Green,La Crosse,S009
• A005,Mark Green,New York,S003
• A006,Jo Smith,Cincinnati,S002
• A007,Tom Jones,Cleveland,S008
• A008,Cathy Jones,Cleveland,S009
• A009,Ann Brown,Boston,S001

4
Customer

• C001,Ray Jones,Boston
• C002,Bobbi Jones,Cincinnati
• C003,Jack Jones,Cleveland
• C004,Barb Smith,La Crosse
• C005,Sue Doe,New York
• C006,Ann Green,Boston
• C007,James Green,Denver
• C008,Cathy Green,Detroit

5
Serves

• A001,C001,5
• A001,C002,4
• A001,C008,3
• A002,C001,3
• A002,C008,6
• A003,C003,4
• A003,C004,7
• A003,C008,8
• A004,C005,3
• A005,C001,6
• A005,C002,5
• A005,C003,4
• A005,C007,9
• A006,C001,6
• A006,C006,3
• A007,C001,5
• A007,C008,4

6
Supplier Join Agent

• S003,Home Supply,Boston,A001,Jane Doe,Boston,S003
• S001,Acme Supply,Boston,A002,John Doe,Boston,S001
• S001,Acme Supply,Boston,A003,John Smith,New
  York,S001
• S009,Big Deals,Chicago,A004,Sue Green,La
  Crosse,S009
• S003,Home Supply,Boston,A005,Mark Green,New
  York,S003
• S002,Furniture Farm,La Crosse,A006,Jo
  Smith,Cincinnati,S002
• S008,Home Supply,Cleveland,A007,Tom
  Jones,Cleveland,S008
• S009,Big Deals,Chicago,A008,Cathy
  Jones,Cleveland,S009
• S001,Acme Supply,Boston,A009,Ann Brown,Boston,S001

7
Agent Join Serves Join Customer(Example Rows)

• A001,Jane Doe,Boston,S003,A001,C001,5,C001,Ray
  Jones,Boston
• A005,Mark Green,New York,S003,A005,C007,9,C007,Jam
  es Green,Denver

8
Supplier Join Agent Join Serves Join
Customer(Example Rows)

• S003,Home Supply,Boston,A001,Jane
  Doe,Boston,S003,A001,C001,5,C001,Ray Jones,Boston
• S003,Home Supply,Boston,A005,Mark Green,New
  York,S003,A005,C007,9,C007,James Green,Denver

9
Query Problems(See Access Demo)

• Find the names of customers in Boston
• Find the names of Agents in Boston
• Find the names of suppliers used by agents named
  Jane Doe
• Find the names of customers served by agents
  named Jane Doe
• Find the number of customers served by agents
  named Jane Doe

10
Query Problems(See Access Demo)

• For each agent find the number of customers
  served
• For each agent find the maximum commission rate
• For each customer find the average commission
  rate
• Find the names of suppliers used by agents who
  serve customers named Ray Jones

11
Query Problems(See Access Demo)

• Find (Agent, Customer) pairs where the agent
  serves the customer and the agent and customer
  are located in the same city
• Find (Agent, Customer) pairs where the the agent
  and customer are located in the same city. Note
  the agent does not have to serve the customer

12
Homework 11

• For each customer find the number of suppliers
  who indirectly serve the customer. A supplier
  indirectly serves a customer if the supplier is
  used by an agent who serves the customer.
• Hand-in one page with the design view of the
  query.
• Due 215 Thursday October 11