home Relational Database tutorial - Data Modeling

Free Relational Database Tutorial and Tips For Beginners!

Intro To Data Modeling

Lesson 2

First, the relational concept says break up the single file into three files. Each one will be dedicated to serving one of the three types of information that that the single file was trying to handle.

One type of info was keeping track of who the patrons are, so a whole file is dedicated only to patron info. Looks like this:

PATRON FILE

Patron Name Patron Address
Andrew 80 Pleasant St., Santa Cruz, CA
Martina 7 D St, Chicago, IL

The other general type of info was the book info. It looks like this:

BOOK FILE

Book Title Book Author
Tennis! Scott Brook
Soccer! Tom Brown
American! Susan Thomas

Finally, we have a cool concept that handles the fact that a patron checked out a book. We create a third file that contains just enough info to identify a patron, the book that was checked out, and when. This clever file looks like this:

INTERSECTION FILE

Patron Name Book Title Date Book Checked Out
Andrew Tennis! 3/3/2001
Andrew Soccer! 3/3/2001
Martina American! 2/2/2001

Its called an intersection file because its kind of an intersection between the patron file and the book file

Notice that you've only had to type in Andrew's address once (in the patron file), and now that he's ready to check out books, you use the intersection file, where you only have to type in his name, the book title and when it was checked out. You don't have to type in Andrew's address over and over again! Hooray!

Hmmm...interesting you say, but so far all I've done was create 3 files. What kind of weirdo system is this? The information is more scattered around than before!

Don't worry! There's software that does all the rest for you! Software like dBase, Oracle, Microsoft Access, FoxPro, etc. The software ties together all of the information from all three files and displays it so that its logically connected. Here's what it does: first, it matches all the patron names from the intersection file with the patron names from the patron file and brings the patron and intersection info together.

So far, the software is starting to collect the data from the patron and intersection files like this:

Patron Name from intersection file
Patron Address from patron file
Book Title from intersection file
Checkout Date from intersection file
Andrew 80 Pleasant St., Santa Cruz, CA Tennis! 3/3/2001

The software saw that "Andrew" was in the intersection file and that "Andrew" was in the patron file, so it took all the matching data from both files relating to "Andrew" and brought it together as shown in the table above. But its not done yet.

Then the software gets the book names from the intersection file and matches that with the book names from the book file and adds the author info from there to the information already collected in the table above. Then, the info looks like this:

(Below is a computer screen display of what you would see)

Patron Name from intersection file
Patron Address from patron file
Book Title from intersection file
Book Author from book file
Checkout Date from intersection file
Andrew 80 Pleasant St., Santa Cruz, CA Tennis! Scott Brook 3/3/2001

The software saw that "Tennis!" was in the intersection file and that "Tennis!" was in the book file, so it took all the data from both files relating to "Tennis" and brought it together.

So now, the software has done all the work matching the info from the three files and it displays the results for you in a logical manner. It then repeats all of the above for Andrew's other checked out book and Martina's checked out book.

Okay, you say, but, isn't it a hassle to keep 3 files open like that at the same time to enter the data?

Click here to continue
Click here to go back one lesson