Spreadsheet vs Database

1. Spreadsheet vs Database What’s the difference and who cares?

2. Spreadsheets • Microsoft Excel is a spreadsheet program • Originally designed to manage flat lists and handle equations • Excel lets you link data between “sheets” making it a very powerful tool. • Excel offers database-like features such as sorting and filtering. • Many people use Excel as a database.

3. Databases • Microsoft Access is a Database program • Designed to store and retrieve data • Databases can do some of the calculations that spreadsheets do • Databases are the cornerstone of all computing – not sexy, but way important! • Databases can be flat, like a spreadsheet, or relational, which makes them super powerful.

4. Flat vs Relational • An example of a flat database might be a phonebook. One record per person. Very simple • Lets look at a library in comparison. In an old fashioned card catalog, how many cards exist for any non-fiction book?

5. 3 Cards per book (at least) Title Author Subject Author Title Title Subject Subject Author 1 Card listed by the title of the book, one under the author’s name and one for the subject – assuming there is only one subject. Many books have multiple subjects.

6. This is what the table looks like In a database, you only need one record for each book and you can sort the database by title, author or subject. But a spreadsheet can do this much! What about our situation with multiple subjects? In a spread sheet, you might create different columns for multiple subjects, but this leads to problems when it comes to filtering and sorting.

7. Enter the relationships… Now there is a relationship between two tables based on the ID of the book. One record per book, one record per subject. This is called a one to many relationship because one book can have many subjects. Finding them all is a cinch.

8. And it gets more complicated… Of course one book can have many authors. Now it is a many to many relationship between authors and subjects. The book table joins them together. In database terminology, this is a join.

9. Databases are… …a way of structuring information for efficient retrieval. …limited only by the technology used, not by the structure of the information. The important thing is to identify the many to many relationships and the joins. Lets try to identify a few that we interact with all the time.