CS 103: Lecture 10

1
CS 103 Lecture 10

• Hand-in Homework 9
• No office hours today
• I can answer a few questions immediately after
  class
• Remember the tutor will be available from 1-3
  tomorrow
• Test 1 Review (in class)
• Questions 12-20 on 10/4
• Questions 21-32 on 10/9
• Today I will review homeworks 5 through 9,
  discuss groupby and, if there is enough time do
  some queries
• In these slides I restated homeworks 5 through 9
  for you to reference as I discuss the solutions

2
Homework 5

• Create an ER diagram for the following problem
  statement. Implement the ER diagram in Access
• Create a database to store information about
  Suppliers and Agents. Associated with a supplier
  is a unique supplier number, a supplier name and
  a phone number. Associated with each agent is a
  unique agent number and an agent name. A supplier
  can be used by many agents but an agent can only
  use one supplier.

3
ER Diagram
Agent ANum AName
Supplier SNum SName Phone
1
M
Supplies
4
Homework 6

• Write a query to find the names of Agents who use
  a Supplier named Acme Supply.

5
Homework 7

• Write a query that finds the number of agents who
  use a supplier named Acme Supply

6
Homework 8

• Implement a database in Access for the following
  problem
• Create a database to store information about
  agents and customers. Associated with an agent is
  a unique agent number and an agent name.
  Associated with each customer is a unique
  customer id and a customer name. An agent can
  serve many customers and a customer can be served
  by many agents. Agents can have different
  commission rates for each customer they serve.
  Commission rate is similar to grade in the
  student/section problem.

7
ER Diagram
Commission
Agent ANum AName
Customer CId CName
M
M
Serves
8
Homework 9

• Enter data into the Customer, Agent and Serves
  (or the name you used) tables. There should be at
  least 4 rows each in Customer and Agent and 6
  rows in Serves

9
Agent Rows

• A001,Jane Doe
• A002,John Doe
• A003,Sue Smith
• A004,John Smith

10
Customer Rows

• C001,Cathy Smith
• C002,John Doe
• C003,Sam Green
• C004,Sue Smith

11
Serves Rows

• A001,C003,5
• A001,C001,4
• A002,C001,5
• A003,C001,3
• A004,C002,6
• A003,C004,4

12
Join the Tables

• Agent Join Serves Join Customer
• Join rows based on the primary key/foreign
  relationship
• In Access if you have created the relationships
  the join is automatically done in a query unless
  you explicitly delete the relationship in the
  query

13
Temporary Table Created by the Agent Join Serves
Join Customer

• A001,Jane Doe,A001,C003,5,C003,Sam Green
• A001,Jane Doe,A001,C001,4,C001,Cathy Smith
• A002,John Doe,A002,C001,5,C001,Cathy Smith
• A003,Sue Smith,A003,C001,3,C001,Cathy Smith
• A004,John Smith,A004,C002,6,C002,John Doe
• A003,Sue Smith,A003,C004,4,C004,Sue Smith

14
Groups Created by GroupBy on ANum

• Group A001
• A001,Jane Doe,A001,C003,5,C003,Sam Green
• A001,Jane Doe,A001,C001,4,C001,Cathy Smith
• Group A002
• A002,John Doe,A002,C001,5,C001,Cathy Smith
• Group A003
• A003,Sue Smith,A003,C001,3,C001,Cathy Smith
• A003,Sue Smith,A003,C004,4,C004,Sue Smith
• Group A004
• A004,John Smith,A004,C002,6,C002,John Doe

15
Groups Created by GroupBy on CNum

• Group C001
• A001,Jane Doe,A001,C001,4,C001,Cathy Smith
• A002,John Doe,A002,C001,5,C001,Cathy Smith
• A003,Sue Smith,A003,C001,3,C001,Cathy Smith
• Group C002
• A004,John Smith,A004,C002,6,C002,John Doe
• Group C003
• A001,Jane Doe,A001,C003,5,C003,Sam Green
• Group C004
• A003,Sue Smith,A003,C004,4,C004,Sue Smith