0

Consider the Customer account database of the ABC big bank, which keeps data for customers and their orders. A database of only one relation is designed, as shown below:

ABC(
   customerNo,
   customerName,
   phone,
   address,
   dateofBirth,
   EmailAddress,
   AccountNo,
   Balance,
   Type,
   RegisterBranchBSB,
   Branchaddress,
   ServicePhone,
   ServiceEmployeeNo,
   ServiceEmployeeName
)
  • A customer has a unique customer number (customerNo) and is also described by name, phone number, address, dateofBirith and EmailAddress.

  • An account is identified by a unique account number ('AccountNo').

  • A Branch has a unique branch BSB (RegisterBranchBSB) and has an address (Branchaddress).

  • A customer can open several accounts and an account is made by only one customer.

  • A customer can own several bank account types, and a bank account type can belong to several customers

  • A customer account has one service employee (ServiceEmplyeeNo) and one service employee can be responsible for several customer accounts.

    1. I just want to know what are the likely functional dependencies in this relation, such as partial,transitive, multi-valued, join dependencies.

    2. The candidate keys in that relation

bumped to the homepage by Community yesterday

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

  • 3
    Welcome to the site. Have you attempted this yourself? Where did you get stuck? It's much better to learn & attempt things & ask for help when you get stuck, rather than asking us to do it for you. – Philᵀᴹ Oct 15 at 8:09
  • Yes Phil I have tried it but could not find functional dependencies other then partial dependencies. – Rapheal Oct 15 at 17:19
  • 2
    @Rapheal this feels an awful lot like a homework question, which this network routinely prefers to not answer. What we would love to see from you to help you is some suggestions of what you have tried. Are the bullet points from the problem definition or are those the things you have worked out on your own? I definitely see a way to decompose this into at least 5 entity tables but the number of joins and junction tables may grow depending on a few requirements. – jcolebrand Oct 15 at 17:30
  • This is a exercise question from a book that I am reading. The thing is we are not allowed to decompose the table. – Rapheal Oct 15 at 17:41
  • if we consider Accountno as primary key. Then serviceEmployeeNo ---> ServiceEmployeeName RegisterBranchBSB ---> Branchaddress,ServicePhone customerNo --> customerName,phone,address,dateofBirth,EmailAddress – Rapheal Oct 16 at 16:45
0

A functional dependency says "if I'm given the value in this column can I definitely know that value that column?"

If you're given the value of customerNo can you determine what the value of customerName? Likely yes. So customerName is functionally dependent on customerNo. How about the other way around - given name can you say what number will be? No, since there'll be a lot of people called "Chris" in the world.

Continuing in this way, given a customerNo can you determine the value of Balance? No, because a customer can have many accounts, each with its own balance. You need to know the account number to uniquely determine a balance. So Balance is functionally dependent on AccountNo and transitively dependent on customerNo.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.