Understanding Database Design and Management with Microsoft Access

1. CS 101 – Access notes Databases (Microsoft Access) • 4 parts of a database • database design • Try to understand the ideas behind database design, not just the mechanics.

2. Access vs. Excel • Excel emphasizes numbers • Access emphasizes relationships • some overlap • Access is perfect for finding common info from 2 separate tables

3. Purpose of Access • It’s a “database management system” • Record keeping for a business • Requires a plan • one file, with many components • Keep track of … so that: • Find cross-references • Generate reports

4. Database format Usually four parts: • Tables (at least 1 is required) • Queries • Forms • Reports

5. Navigating a table • A database table looks like a spreadsheet! • Record = row • Field = column • Two ways to view: • Design view, to start • Datasheet view, to enter data • Primary Key!

6. Example questions • In a stock portfolio: • Which stocks have lost money? • When did I buy those stocks? • Which class is easier: CS 22 or CS 25? • Check grades in each class • Check grades for students who’ve taken both • Among customers who bought a new generator last year, what have they bought since?

7. Relieve tedium • Suppose we want to maintain info on orders. • Better to have two tables than one!

8. Need multiple tables • Ex. Librarian wants to contact graduating seniors who have overdue books. • Need multiple tables: • Student info table • Student #, name, year • Book table • ISBN, title, author, … • Transaction or “check out” table • Transaction #, ISBN, student #, Due date

9. Relationships • When you have 2+ tables, there is almost always a relationship • They share one field in common. • Can you tell what it is? • Ex. Customers & Orders • Ex. Students & Class roster • Ex. Publishers & books

10. Animal hospital • Keep track of customers, pets, visits • Each gets its own table • What fields for each table? • Relationships • What else does a database need?

11. Relationships

12. Fields in your table • Anticipate questions • Age?  store birth date • GPA?  store credits and quality points • What year?  store date of admission • Store data in its smallest parts (e.g. address) • Calculated fields don’t belong in table!

13. Table review • We want to “manage” information: • Organize, insert, delete, retrieve • To organize we… • Create a table (or “set”, “class”) consisting of records (or “objects”) each having fields (or “attributes”) • Usually we’ll want 2+ related tables. 

14. Fields in your table • Anticipate questions • Age?  store birth date • GPA?  store credits and quality points • What year?  store date of admission • Store data in its smallest parts (e.g. address) • Calculated fields don’t belong in table!

15. Queries • Usually we ask about info from 2+ tables. • By default, a query will perform an operation called a Cartesian Product, which gives all possible combinations. • Ex. Name and City tables:

16. Cartesian Product • Given 2 sets, find all possible ordered pairs. • Analogously for more than 2 sets.  • Great example: choosing a menu. • Appetizer • Entrée • Dessert • Unfortunately, most DB queries are not like this! We get too many results.

17. Relationships • We want to tell Access that there is a relationship between the tables, so we can create meaningful query. • One-to-many is most common • “Each city has one or more employees.” • Now, query will return 3 results instead of 6: Miami Bob Pittsburgh Mary Ken

18. One-to-One • Can be useful if some information is confidential. • What if we didn’t have any relationship?

19. 1-1 Query • When you combine tables that have a 1-1 relationship: Access will look for fields that are the same, and use this as a filter. • In previous example, we’ll have 2 results instead of 4.  Employee 101’s information Employee 102’s information • Let’s look at another example.

20. What happens when we “join” these 1-1 tables?

22. Referential integrity Keep related records consistent Cascade delete: allow deletion of “one” Cascade update: allow update of “one” For example, changing someone’s CustomerID. One-many Relationships

23. Many-to-many • Ex. Customers to products • Implement as 2 one-to-one • “Order details” table • Think of possible queries based on the 5 tables given in handout.

24. Other queries Besides ordinary “select” queries: • Total – special case: also do subtotals  • Parameter – prompt user to tailor the result • Action – modify underlying table • Make, delete from, append to, update • Crosstab – 2-D subtotal • Ex. $, by species and month!