1

Given These Schemas:

Account: bname, acct_no, balance
Depositor: cname, acct_no
Customer: cname, street, city <-(all customers / both loan and account customers)
Loan: loan_no, amount, b_name
Borrower: cname, loan_no
Branch: bname, b_city, assets

Question: Find branches whose customers include all customers that live in Tulsa

My professor gave this solution:

Π cname, bname(account ⋈ depositor) / Πcname(σcity == ‘Tulsa’ (customers))

I don't think the part Π cname, bname(account ⋈ depositor) is correct because that ONLY includes the cname and bname of customers with accounts and does not include ALL customers (leaves out those with loans). The question does not specifically say "Find branches whose customers with accounts include all customers that live in Tulsa".

What am I missing?

philipxy
  • 14,867
  • 6
  • 39
  • 83
csguy
  • 1,354
  • 2
  • 17
  • 37
  • @philipxy i didn't realize there was more info that had to be assumed. i mean these are questions that would be asked on an exam given exactly what i provided. mb – csguy Jan 19 '20 at 03:57
  • Hope you let us know what your prof says in response. – philipxy Jan 19 '20 at 21:23
  • 1
    @philipxy brought it up in class and he said he was wrong. hopefully no more mistakes – csguy Jan 22 '20 at 04:25

1 Answers1

1

We can guess--per names, symmetry & your mention of "loan and account customers"--that there is a correct query involving (the union of projections of) (Account join Depositor) & (Loan join Borrower). So it seems like your take on a query is reasonable. But you don't give the base table predicates (criteria under which rows appear); you rely on us to guess.

Under constraints some queries return the same results as others that otherwise wouldn't. Maybe your professor thinks that (it is obvious that) a borrower must have an account. Under that constraint, if your take is correct then so is theirs. Without certain constraints like that, you are right that they are wrong. But you don't also give the constraints.

However you are presumably both wrong: If a certain branch & Tulsa have no customers then the result should hold that branch. But a quotient will not. The specification is only similar to one corresponding to a division. Your division returns "branches whose customers include all customers that live in Tulsa" and that have at least one customer. This is a case of classic errors & ambiguities in specification & implementation involving division & almost involving division. On the other hand, maybe there is a constraint that no bank has no customers. Then your query is correct--but not your reasoning.

Re relational querying. (Which you can use to justify your query & arguments precisely & soundly.)

philipxy
  • 14,867
  • 6
  • 39
  • 83