Business at Mom and Pop Store is good. So good that the owners want to expand. They've learned that they must organize and automate their paperwork. They hire a Data Systems Analyst for the job.
The Data Systems Analyst walks around each department in the company, looks at the jobs performed by employees, the items (probably on paper) that flow around the company between different departments. The analyst literally follows each item on each piece of paper around the company, note who uses it, changes it, and where it goes.
At the end of this analysis, the Data Systems Analyst produces a "Data Model". This describes the company in terms of data: what the data are, who reads it, changes it, things like that.
Once the company agrees that "Yes, that's exactly how our business works!" the analyst can go about putting this data model into a Relational Database.
Relational databases provide us one way to organize data. It's quite common to define relational databases in the same way we talk about the "Real World".
| In the Real World | In the Database World |
|---|---|
| we have an Entity (a noun phrase) | an Entity is represented with Table Name |
| each entity has one or more Attributes (properties) | each table has one or more Fields |
We start by defining our "Real World"
The relationships are written as English sentences. Each sentence looks something like this:
Entity1 verb_phrase Entity2
such as
A Library loans out Books
Library and Book are entities--things! Notice that my sentence has the plural of books. Most times we need to state the quantity in each relationship. For our purposes, we will have one or many.
So each sentence expands to look something like this:
Entity1 verb_phrase quantity Entity2
such as
A LIBRARY loans out many BOOKs
A DOCTOR has many PATIENTs
Each PATIENT may have many DOCTORs
Each DEPARTMENT has one SUPERVISOR
Each EMPLOYEE works at only one OFFICE
Let's try modeling a library. Our library has two entities: BOOKS and PATRONS. The relationship
between these entities is one-to-many:
A PATRON may borrow many BOOKS.
and one-to-one:
Each BOOK may be loaned to one PATRON.
Note: You have to go BOTH ways when defining relationships between two entities.
We've seen how we can import an Excel Spreadsheet into Access. It worked well for nutrition data. Let's create a spreasheet for our library. (Ultimately we would want more data, but this is our first try.)
| CardNumber | | BorrowerAddress | BookBorrowed |
|
|---|---|---|---|
| 123456 | John Smith | 1 Memory Lane | The Tao of Pooh | 3749277 | Jane Jones | 20 Eagle Ave | Cat's Cradle | 123456 | John Smith | 1 Memory Lane | Mystic River | 123456 | John Smith | 1 Memory Lane | A Tale of Two Cities | 123456 | John Smith | 1 Memory Lane | The Te of Piglet | 300243 | Sam Shepard | 1984 Space St | Myths to Live By |
NOT GOOD!!! Why? Because we have REDUNDANT DATA! John Smith is an avid reader. Our library DOES have to keep track of the books he has out, but WHY should it repeat his name and address for every book? We should just need to record this data ONCE!
In database terminology, removing the redundancy is called data normalization. The way we remove redundancy is by defining a foreign key. PATRON and BOOK are in a 1:Many relationship. In order to remove the redundancy (the borrower's name and address repeated for each book taken out), we define a foreign key in the BOOK table.
A foreign key (a new field) is placed in BOOK that contains the primary key in PATRON.
Our table for PATRON becomes simpler because it only has information about the PATRON. Our table for BOOK is a tad more complicated than before (since we have a foreign key now), but it is easy to see who has borrowed the book.
Library databaseThere are several departments in Mom and Pop Store. This is a data model of the Orders Department.
Google search for images of ER diagrams for examples.
ER-Diagrams can be very detailed!! We are working with the simplest.
Remember this from Part I.
Example: Entity A -- 1-to-Many -- Entity B
A foreign key is placed in B that matches the primary key in A.