Table of Contents
What is in a Database and Why Excel IS NOT a Database
There are hordes of business people who use Excel every day and swear it`s a great database. Excel is a great spreadsheet application but, it`s not really a database at all. Excel however is an excellent complement to any database since it can turn row after row of data into attractive and comprehensive reports and charts.
What You Should Already Know
You should already be a believer in Why Use a Database.
You should already know the following key concepts:
- separation of presentation, behavior and data is a good thing
- what is a database table
- what is Microsoft Excel
What You Will Learn
You will have a conceptual understanding of:
- what are common features in modern databases
- what is a relational database (vs other types of databases)
- why Excel really isnt a database
What are Common Features in Modern Databases
After our tirade about why Excel really isn't a database, lets look again at some features that look suspiciously familiar between Excel and modern databases.
Modern Databases include Tables
A table is a unit organization of data within a database.
Much like Excel has columns and rows, a table has fields and records. In Excel, you may put a word in the top cell of each column to describe the data that appear below it. For example, in a spreadsheet containing monthly expenses, column B may represents expenses that occurred in the month of February.
In fact, a table, all by itself, looks an awfully lot like a spreadsheet.
THE WORLD USED TO BE FLAT. There is a type of database called a flat-file database which resembles one or more tables that don’t have much to do with each other – many modern databases used to be flat-file databases up until very recently - FileMaker Pro being one of them. Explains the name, doesn't it?
Databases Can Work with Database Files Much Larger Than Available RAM.
Databases are designed to in a way to refer to information without actually requiring to load all of the information into memory. Just load a 100 MB Excel file or text file and you will see a huge performance hit. On the other hand, a well designed database doesn't need to load its entire bulk into memory.
NOT ALL DATABASES ARE FAST THOUGH. Valentina takes speed farther than almost any other database on the market when it comes to handling how data is managed in memory. But almost any modern database is going to be faster than pokey Excel in handling thousands of records.
TRAPPED IN A CELL. Excel uses a workbook format, each one of which can contain up to 256 work sheets. Each sheet can have up to 65,000 rows and 256 columns. Calculate that out and you have a measily 4,292,608,000 cells. That sounds like a lot, doesn't it? Just try opening a workbook with a sheet that contains only 10,000 rows of information and try scrolling it. Prepare to take coffee breaks between each scroll!
Modern Databases Are Naturally Multi-User
Databases manage information so that it's easy to have a lot of users accessing it at the same time. Excel documents are meant to be used on a single computer at a time.
HEY, THERE IS A SINGLE-USER VERSION OF VALENTINA, YOU LIAR! Okay, you caught us! But we have a defense - there is a single connection version of Valentina. The innate engine of Valentina can handle an almost unlimited number of connections.
I`VE SEEN DATABASES THAT SAY THEY ARE SINGLE USER Yes, there are database systems that are single user. They were not built with a network in mind. Our biased point-of-view is that a single user only database isn`t keeping up with the times.
What is a Relational Database (vs other types of databases)
A relational database works with the tables inside it in a more intelligent way and in a way that would make the boys at ANSI be really proud. A relational database has more than one table, and a column in one table directly relates to a column in another – however not all information needs to be stored in the referring column.
A Relational DB Example for a Bookstore
Consider an order system at a bookstore. This order system may include a database that has three tables:
A CUSTOMER TABLE. Each customer has a unique ID that ensures that the five people named John Smith within do not get mixed up.
AN ORDER TABLE. Each order has a unique ID as well, so it is possible to differentiate between the order for Harry Potter vs Darth Vader yesterday at 11:00 AM and the one that came in at 2:00 PM.
A BOOK TABLE. Each book edition has a unique ID, so it is possible to tell apart the First, Second, and Fifth Editions of Harry Potter vs Darth Vader.
These three tables can be joined in a way that relates them with each other without really physically joining them together.
For example, each actual order is associated with a unique buyer (from the customer table) and a unique book edition (from the book table). These IDs can be extremely small - maybe an integer. Then, instead of having the string “Harry Potter vs Darth Vader” duplicated thousands of times in your Order Table - making that table bloat terribly - it could simply refer to the book ID 1295.
The common element between each of the tables is an ID – that relates records without any nasty and unnatural physical joining that the lads at ANSI would find terribly distasteful.
WHAT'S OLD IS NEW. Prior to pioneering work of Dr E.F. Codd in relational databases (see Dr Codd's 12 rules), there was a type of database called a Network Database. Network Databases stored units of information that naturally pointed to the next unit of information, kind of like how web pages can be linked together on the internet with hyperlinks. Navigating a network database was referred to as “walking the set”.