Tutorial 1: Primary Key and SQL

1
Tutorial 1 Primary Key and SQL
2

• Question Consider the following table
  schemaWITHDRAW(cust-id, name, acc-id, date,
  amount)
• Whenever a customer withdraws money from her/his
  account, a tuple must be inserted in the table.
• What should be the primary key for the following
  situations, resp?
• (i) every customer is allowed to withdraw only
  once per day.
• (ii) every account is allowed to be withdrawn
  from only once per day.
• (iii) If a customer has multiple accounts, s/he
  can withdraw from each account only once per day.
• (iv) A customer can withdraw from any of her/his
  accounts as many times as s/he likes.

3

• Answer Consider the following table
  schemaWITHDRAW(cust-id, name, acc-id, date,
  amount)
• What should be the primary key for the following
  situations, resp?
• (i) every customer is allowed to withdraw only
  once per day.
• (cust-id, date)
• (ii) every account is allowed to be withdrawn
  from only once per day.
• (acc-id, date)
• (iii) If a customer has multiple accounts, s/he
  can withdraw from each account only once per day.
• (cust-id, acc-id, date)
• (iv) A customer can withdraw from any of her/his
  accounts as many times as s/he likes.
• No primary key can guarantee this. A close
  solution is (cust-id, acc-id, date, amount). But
  this solution requires a customer to withdraw a
  different amount of money each time.

4

• Question Now, let us get an idea about a good
  database design and a bad design.
• Consider the schema we saw earlierWITHDRAW1(cust
  -id, name, acc-id, date, amount)
• An alternative design is to use two
  tablesCUST(cust-id, name), andWITHDRAW2(cust-id
  , acc-id, date, amount)
• Specifically, for the 2nd design, whenever a
  withdrawal occurs, a tuple is inserted in
  WITHDRAW2. A tuple is inserted in CUST only if
  this is the first time that the customer
  withdraws money.
• Continued on the next slide.

5

• Design 1WITHDRAW1(cust-id, name, acc-id, date,
  amount)
• Design 2CUST(cust-id, name), andWITHDRAW2(cust-
  id, acc-id, date, amount)
• Which design saves space?
• In terms of the information recorded, the two
  designs are equivalent. To verify this, please
  write an SQL query to derive WITHDRAW1 from CUST
  and WITHDRAW2.

6

• Answer
• Design 1WITHDRAW1(cust-id, name, acc-id, date,
  amount)
• Design 2CUST(cust-id, name), andWITHDRAW2(cust-
  id, acc-id, date, amount)
• Which design saves space?
• Design 2. In design 1, if the same customer
  withdraws again and again, her/his name must be
  duplicated again and again.
• SQL query to derive WITHDRAW1 from CUST and
  WITHDRAW2.
• SELECT T1.cust-id, T1.name, T2.acc-id, T2.date,
  T2.amountFROM CUST T1, WITHDRAW2 T2WHERE
  T1.cust-id T2.cust-id
• In practice, we should use the second design,
  which can be obtained from the first,
  easier-to-observe, design through normalization.

7

• Question Consider the following
  schemas.CUST(cust-id, name), andWITHDRAW(cust-id
  , acc-id, date, amount)
• Write an SQL query to retrieve all the names of
  the customers who have withdrawn more than 1k
  dollars in a single withdrawal. If a customer
  made several such withdrawals, her/his name
  should be reported only once.

8

• Answer Consider the following schemas.CUST(cust-
  id, name), andWITHDRAW(cust-id, acc-id, date,
  amount)
• Write an SQL query to retrieve all the names of
  the customers who have withdrawn more than 1k
  dollars in a single withdrawal. If a customer
  made several such withdrawals, her/his name
  should be reported only once.
• SELECT DISTINCT nameFROM CUST T1, WITHDRAW
  T2WHERE T1.cust-id T2.cust-id and T2.amount gt
  1k

9

• Question Consider the following
  schemas.CUST(cust-id, name), andWITHDRAW(cust-id
  , acc-id, date, amount)
• Let the smallest withdrawal be the one with the
  lowest withdrawal amount.
• Write a query to display the cust-id of the
  customer making the smallest withdrawal, her/his
  name, the acc-id of the affected account, the
  withdrawal date and amount.

10

• Question Consider the following
  schemas.CUST(cust-id, name), andWITHDRAW(cust-id
  , acc-id, date, amount)
• Let the smallest withdrawal be the one with the
  lowest withdrawal amount.
• Write a query to display the cust-id of the
  customer making the smallest withdrawal, her/his
  name, the acc-id of the affected account, the
  withdrawal date and amount.
• (SELECT W.cust-id, C.name, W.acc-id, W.date,
  W.amount FROM CUST C, WITHDRAW WWHERE C.cust-id
  W.cust-id)EXCEPT (SELECT W2.cust-id, C2.name,
  W2.acc-id, W2.date, W2.amount FROM WITHDRAW W1,
  CUST C2, WITHDRAW W2WHERE C2.cust-id
  W2.cust-id AND W1.amount lt W2.amount)

11

• Question Consider the following
  schemas.CUST(cust-id, name), andWITHDRAW(cust-id
  , acc-id, date, amount)
• Sometimes there may be a shared account,
  namely, an account with multiple owners.
• Write an SQL query to return the acc-id of all
  the shared accounts.You may assume that all the
  owners of a shared account have made withdrawals
  from the account.

12

• Answer Consider the following schemas.CUST(cust-
  id, name), andWITHDRAW(cust-id, acc-id, date,
  amount)
• Sometimes there may be a shared account,
  namely, an account with multiple owners.
• Write an SQL query to return the acc-id of all
  the shared accounts.You may assume that all the
  owners of a shared account have made withdrawals
  from the account.
• SELECT DISTINCT T1.acc-idFROM WITHDRAW T1,
  WITHDRAW T2WHERE T1.cust-id ltgt T2.cust-id and
  T1.acc-id T2.acc-id