Welcome to Access

1. Welcome to Access

2. Welcome to Access Day 1 • You have been hired to create a Database for the new library in town. • We initially tried to use Excel to keep track of our data, but realized that we just couldn’t keep track of our data in a spreadsheet. • Over the course of the next five days you will learn the concepts of creating a database and making it functional for every day use.

3. Excel vs Access Excel Access Create databases Stores LOTS of data and relates it Retrieves data based on criteria Enables easy data entry Creates customizable reports • Creates spreadsheets • Stores flat data • Analyzes data and performs sophisticated mathematical functions • Presents data in charts and graphs.

4. When we made it in Excel… In Excel we realized that we would have to duplicate the book record every time a the book was borrowed, or essentially create a new record for the book each time.

5. If we made it in Access… Access can store one record of a book in a database and tie all the instances of a book borrow to that one book.

6. When we made it in Excel… We tried to create a record for a patron, but we realized we couldn’t do it unless they checked out a book at the same time.

7. If we made it in Access… We can have a separate table for Patrons and just connect each patron to a borrow when they check out a book.

8. When we made it in Excel… We needed to change a patron’s phone number, but we realized we’d have to change all the instances of a patron borrowing a book. Such a headache!

9. If we made it in Access… We can just go to the patron’s record in the Patron table, change their phone number and every borrow will still be connected to the same record.

10. When we made it in Excel… We wanted to delete a borrow from the spreadsheet, but we realized that in doing so we’d also delete the customer information. What if that were the only time they checked out a book? We’d loose that information completely.

11. If we made it in Access… Because the patrons are stored in a separate table from the borrows, we can delete a borrow without deleting the Patron.

12. Library Database Example • Access can store data in separate tables and relate them • Access can create queries that can pull out very specific pieces of data • Access can create forms that allow for quick and user friendly data entry • Access can create reports to present your data.

13. Table Design • What fields should we include in the Books and Patrons table? • Data types • Each field should be the smalled data chuncks you can think of. • Example: Separate First_Name and Last Name into two different fields.

14. What fields would you want? Books • Book ID (PK) • Title • Author First • Author Last • Book Year • Genre • Checked In • Patrons • Patron ID (PK) • Patron First Name • Patron Last Name • Patron Phone • Library Card Number • Address • City, State , ZIP • Country • Patron Info

15. Define the relationships • Get into pairs and define the relationships between the following entities: • For example how many social security numbers would one citizen have? • One to one • How many passengers can one bus have? • One to many or Many to many

16. REA Diagrams • Resources—what kind of resources do we need to keep track of for the library? • Events—what kind of events will we need to track in the library? • Agents—Who will be the participants in the library?

17. Resources Events Agents • Patrons • Patron ID (PK) • Patron First Name • Patron Last Name • Patron Phone • Library Card Number • Address • City • State • ZIP • Country • Patron Info • Books • Book ID (PK) • Title • Author First • Author Last • Book Year • Genre • Checked In • Borrows • Borrow ID (PK) • Book ID (FK) • Patron ID (FK) • Employee ID (FK) • Checked Out Date • Due Date • Checked In Date • Employees • Employee ID (PK) • Employee First Name • Employee Last Name • Address • Hire_Date =one to many

18. Now it’s your turn! • Let’s start creating the tables in Access and make the relationships between them!