Now let us take the same amazoncom order data and

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

View Full Document Right Arrow Icon
Now, let us take the same amazon.com order data and show the relational database approach to store it. We will create 3 tables – one for storing book information, one for storing customer information and one for storing order information (see the figure below). Now we have three tables in the database but how are they logically connected (which is one of the properties of the relational database)? To understand the logical connection, we need to understand the concept of keys. A key is the field used to relate tables in a database. There are two types of keys – primary and foreign key. Primary key is a field/attribute (or group of fields/attributes in some cases) that uniquely identify each record/entity in a table. For example: Customer ID is the primary key for customer 9
Image of page 9

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

View Full Document Right Arrow Icon
table, ISBN is the primary key for book table, and OrderNumber is primary key for order table. In a relational database each table should have a primary key. Foreign keys is a field that is a primary key in one table and also appears in a different table (may appear as a part of the primary key of other table). For example: Customer ID in Orders table and ISBN in order table are foreign keys (as they are primary keys in customer and book table, respectively). As seen in the figure below, tables are logically connected using foreign keys (i.e., adding the primary keys in other tables to connect the records). As a result of having foreign keys in Order table, one can easily find more details about a book (or a customer) included in an order. Say we want to find out the name of the book purchased in order number 4 and what is the name and phone number of the customer. We (or actually the DBMS program) will: a) look up the order number field in order table, which will point to one unique row in the order table (as the primary key cannot have duplicate values in the table in which it acts as primary key) and find the correct row (here 4th row), b) then find the ISBN number of the book from the ISBN field (which acts as foreign key in the order table) in that (i.e., 4th) row. This would give us ISBN number 1690. c) then one can go to the book table and search for ISBN number 1690, which will return one unique row (as there cannot be duplicate ISBN numbers in the Book table). Here we will get row number 2 in book table. d) Now, one can go to that row (i.e., row 2) and read the value of the bookname field. e) Thus we get the detailed book related information for the order # 4 (i.e., book name is “DBMS Intro”). Similarly, we can get information about customer (i.e., customer is “john” and phone is “23456789” for the customer who placed order #4). 10
Image of page 10
Instead of showing the tables in a database graphically (as shown in the figure above), the formal way to represent the tables is in textual format (as shown below) by writing the name of the table and including the names of the fields (separated by commas) within parentheses: Customer (CustomerID , Name, Phone) Book (ISBN , Author, BookName, Price) Order (OrderNumber , CustomerID, ISBN, Date) Note: Primary Key (PK) is identified by underlining appropriate field/s. Foreign key can
Image of page 11

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

View Full Document Right Arrow Icon
Image of page 12
This is the end of the preview. Sign up to access the rest of the document.

{[ 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