Sally has a shadow. Wherever Sally goes, her shadow goes.
Sally’s shadow contains information that companies and organizations know about Sally,
like her name, date of birth, and sometimes even her shopping preferences.
We call these pieces of information data. The data might be about Sally or the things
that Sally owns like her car or house. This is Jack. He owns Jack’s Auto.
A small business like Jack’s might store Sally’s data in a spreadsheet.
Jack sells Sally a blue car. We call the data stored about an object or
a person a Record. We call each piece of information of the same
type, like a name or color, a field. While a spreadsheet is easy to use, some problems
can occur. One day Sally marries Tom and changes her
last name. If Sally is identified by name only, Jack
might confuse her customer data. His spreadsheet doesn’t know which Sally
is the “Real” Sally. Jack might even create a duplicate record
under Sally’s new married name. So… what’s the big deal?
Jack likes to send free promotional air fresheners through the mail to his customers.
Now that Jack has duplicate records for Sally, because Sally is listed under multiple names,
Sally is getting duplicate air fresheners. Jack might try to fix his data by adding a
unique identifier for Sally. Unlike her last name, this unique identifier
will never change. This unique identifier is called a primary
key because it is the primary way to uniquely identify a record.
However, Jack could still run into more problems with his spreadsheets.
Sally bought another car after getting married. Even with the unique identifier, Jack can’t
be sure what information in his spreadsheet is correct.
What is Sally’s current last name? Does she own both cars or is that another piece
of bad data? What Jack needs is a database.
A database is a structured collection of information. Instead of storing his data in a spreadsheet,
Jack stores his data in separate tables in a database.
A table is a collection of records of the same type.
Jack will have one table that contains Customers and another table that contains Cars.
A relational database connects pieces of data using common identifiers called keys.
A database keeps data valid by enforcing rules. A relationship is one kind of rule that governs
how data can be related to other data. Now that Jack has his data about Sally split
into two tables, he needs to create a relationship between the Cars and Customers tables.
He does this by creating a Foreign Key field in the Cars table.
A Foreign Key identifies a different related record, often in a different table, such as
a customer record in the customer table. A relational database might use a number of
different types of relationships. The relationship between the Cars and Customers
table where Sally can own multiple cars is called a One to Many Relationship.
A One to Many relationship allows one record in one table to be related to many records
in another table. Sally can own many cars, but each car can
only have one owner. This is why the CustomerID foreign key is
added to the car table, and not the other way around.
The infinity symbol indicates the many side of the relationship, while the one symbolizes
the one side. Luckily, Jack does not have to know how all
those tables and relationships work in his new business database.
Instead, Jack interacts with the database using forms and reports.
Now, when Sally buys a car, Jack uses a form to create the appropriate record in the car
table. Or when Sally gets married, Jack uses a form
to update the same customer record. A form is graphical user interface to the
database that allows users to manipulate the data without having to know the structure
of the database. Let’s review…
While you can store data in a spreadsheet, a database is better because the rules in
a database will help make sure that the data is valid.
The person using the database does not need to understand the structure of the database
because they can interact with forms and reports to manipulate the data.