Table Relationships: Apply It 1

  • Discuss database principles
  • Differentiate between a flat file and a normalized data
  • Understand table relationships
  • One-to-One Relationship: Where each record in Table A has one, and only one, corresponding record in Table B.
  • One-to-Many Relationship: Where a single record in Table A can relate to many records in Table B, but each record in Table B relates to only one record in Table A.
  • Many-to-Many Relationship: Where multiple records in Table A can relate to multiple records in Table B. This type of relationship typically requires a junction table.

You have been tasked with designing a simple database for a small bookstore that tracks customers, orders, and books. The bookstore needs to understand which customers purchase which books and manage the inventory accordingly.

  1. Open Microsoft Access. Save a new database to the Rowan folder on your desktop as LastName_Bookstore.accdb, replacing “LastName” with your own last name. (Example: Hywater_Bookstore) Remember to save your work periodically.
  2. Create three tables: Customers, Orders, and Books.
  3. Define the following fields:
    • Customers: CustomerID (Primary Key), FirstName, LastName, Email
    • Orders: OrderID (Primary Key), OrderDate, CustomerID (Foreign Key)
    • Books: BookID (Primary Key), Title, Author, ISBN, Price
  4. Establish a one-to-many relationship between Customers and Orders.
  5. Establish a many-to-many relationship between Orders and Books by creating an OrderDetails table with OrderID and BookID as foreign keys, and an additional field for Quantity.
  6. Use the Relationships tool in Access to create and enforce these relationships.
  7. Enter sample data into each table (at least 3 customers, 5 books, and 5 orders).
  8. Save your database one final time. You may need to submit this assignment for your class.

Remember: The CustomerID in Orders must match an existing customer, and the OrderID and BookID in OrderDetails must match existing orders and books!