Downloaded 116 times
Access Database Library Assignment
- 1.
- 2. Let's try tounderstand one simple database
- 3. A library allowscustomers to check out books
- 4. So what tablesdo we need?
- 5. Customer table: anydata the library needs to keep about its customers
- 6. Customer table primarykey: Autonumber
- 7. Book table: storeany data the library needs to keep about its books
- 8. Primary key forbook table
- 9. Is there arelationship between the book and customer table?
- 10. To see if2 tables have a relationship you must ask 3 questions.
- 11. You have toask 3 questions for each side of the relationship
- 12. Could you havea book in the book table that no one has checked out?
- 13. Could you havea book that 1 customer has checked out?
- 14. Could you havea book that more than 1 customer has checked out?
- 15. Since you canhave a book checked out by more than one customer M Book Customer
- 16. Could you havea customer in the customer table that has not checked out a book?
- 17. Could you havea customer that has checked out 1 book?
- 18. Could you havea customer that has checked out more than 1 book?
- 19. This side ofthe relationship is also many! M Book Customer
- 20. So we havea many to many relationship! M M Book Customer
- 21. Each customer cancheck out more than one book and each book can be checked out by more than one customer
- 22. We can’t trackwhich book a customer checks out without adding a new table.
- 23. To create abridge table you start with the primary keys of the related tables
- 24. What should bethe primary key for the bridge table?
- 25. Entity relationship diagram showsthe relationship 1 M M 1 customer checkout book
- 26. But a customercan check out more than one book at a time!
- 27. So instead ofcreating a bridge table between customer and book tables we will create a check out table to show each time a customer checks out books.
- 28. So we needa table to show each time a customer checks out books…
- 29. …and another tableto show exactly what books that customer checked out that day
- 30. The Checkout_Book table wouldbe the bridge or junction table here for the Book and Checkout tables many to many relationship.
- 31. To enter datain this database
- 32. You could notenter data about a customer checking out a book if…
- 33.
Editor's Notes
- #13 The first 3 questions are asked from the book table looking at the customer table. Here the answer is yes. You could have a new book for example.
- #14 Yes to this question.
- #15 Again yes to this question.
- #16 this side of the relationship is many and now we have to see what the other side of the relationship is.
- #17 The next 3 questions are asked from the customer table looking at the book table. Here the answer is yes.
- #18 Again yes!
- #19 And yes again.
- #20 Since a book could be checked out by more than one customer.
- #27 If this database only had 3 tables then there would have to be another entry for each book each customer checked out. We can do better than that!
- #28 So instead of a bridge table we will create a checkout table and bring the customer primary key over as the foreign key and also include a Date_Checked_Out field. To reduce errors and to save time we will have Access enter a default time of today for this field.What would be a good primary key for this table?
- #29 This is a bridge or junction table so we need both primary keys of the 2 tables in the many to many relationship to serve as foreign keys and here they both can be the primary key. We can never use just one of these two foreign keys as the primary key. And sometimes there are good reasons not to use both foreign keys as the primary key. But here we can totally eliminate errors like accidently entering the same book two times by using both foreign keys as the primary key.