Xyz road khagrachhori a 103 1 customer account now we

Info icon This preview shows pages 7–9. Sign up to view the full content.

XYZ Road Khagrachhori A-103 1 customer account Now we can definitely say that the owner of the account A-103 is a customer named Somebody who lives in Dhaka (and not in Bogra ). A field which uniquely identifies a record in a table is called a primary key . In customer table, customer-id is a primary key. Again, a primary key which is used as a field in another table for linking (i.e., relationship establishing) purposes, is called a foreign key . In account table, customer-id is a foreign key. Primary key and foreign key attributes are usually underlined to express their nature. customer-id customer-name customer-street customer-city account-number customer-id 1 Somebody Mirpur Road Dhaka A-101 3 2 Somebody Aga Kha Road Bogra A-102 3 3 Anybody XYZ Road Khagrachhori A-103 1 customer account Note that we can also define a primary key for the account table, and it would be the account-number field. So what we’ve learnt from this section is that we need a primary key to uniquely identify a record in a table, and we need to uniquely identify a record in a table to properly establish relationship among attributes; thus design an efficient database. However, primary key is also used to prevent inserting records which contain duplicate primary key field values. You might have started thinking, “Well, we’ve solved the problem quite well!” But you’re wrong! It’s true that a customer might have more than one account at a time; but it’s also true that more than one Primary Key Foreign Key
Image of page 7

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

4 customer might own a single account (which we call joint-account ). So, the actual relationship between customer-name and account-number is many-to-many . Now, what happens when the account number A-104 is owned by both the customers named Somebody and A nybody ? In the account table, we’ll either have to break the rule of atomic data , or we’ll have to keep duplicate records. Both of the solutions are unacceptable. So, again, let’s split up the table account into two tables: customer- id customer- name customer- street customer- city account -number account -number customer- id 1 Somebody Mirpur Road Dhaka A-101 A-101 3 2 Somebody Aga Kha Road Bogra A-102 A-102 3 3 Anybody XYZ Road Khagrachhori A-103 A-103 1 A-104 1 A-104 3 customer account depositor What we’ve done here is, we’ve created a link table named depositor containing two foreign keys – each of which refers to the corresponding primary key in the tables customer and account . Thus, from the depositor table, we can say that the account number A-104 is owned by two customers whose id s are 1 and 3 . And then from the customer table, we can find the details about those customers. The interesting fact is that, the previous queries we’ve tried can also be executed easily from these tables with this configuration.
Image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.
  • Winter '16
  • zeenath
  • Relational Database, ........., Entity-relationship model, Relational model

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern