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

• 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.

4

• 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.

5

• 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.

6

• 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.

7

• 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.