Create the Database and Books Table (Criterion 1)

First, open MS Access and create a new blank desktop database. Save it with a relevant name (e.g., LibraryManagementSystem.accdb).

Next, create the Books table:

• Go to the Create tab and select Table Design.
• Add the following fields with their respective data types:
  • BookID: AutoNumber (Set as Primary Key)
  • Title: Short Text
  • Author: Short Text
  • Genre: Short Text
  • YearPublished: Number (Field Size: Integer)
• Save the table as Books.

• Go to the Create tab and select Table Design.
• Add the following fields with their respective data types:
  • MemberID: AutoNumber (Set as Primary Key)
  • FirstName: Short Text
  • LastName: Short Text
  • Email: Short Text
  • PhoneNumber: Short Text
• Save the table as Members.

Open each table in Datasheet View and add at least 8 records.

• Books Table Example Data:

  • BookID | Title | Author | Genre | YearPublished
  • 1 | The Great Gatsby | F. Scott Fitzgerald | Classic | 1925
  • 2 | To Kill a Mockingbird | Harper Lee | Fiction | 1960
  • 3 | 1984 | George Orwell | Dystopian | 1949
  • 4 | Pride and Prejudice | Jane Austen | Romance | 1813
  • 5 | The Hobbit | J.R.R. Tolkien | Fantasy | 1937
  • 6 | Sapiens | Yuval Noah Harari | Non-Fiction | 2011
  • 7 | Educated | Tara Westover | Memoir | 2018
  • 8 | The Lord of the Rings | J.R.R. Tolkien | Fantasy | 1954
  • 9 | Becoming | Michelle Obama | Memoir | 2018
  • 10 | The Alchemist | Paulo Coelho | Philosophical | 1988

• Members Table Example Data:

  • MemberID | FirstName | LastName | Email | PhoneNumber
  • 1 | Alice | Smith | alice.s@example.com | 555-123-4567
  • 2 | Bob | Johnson | bob.j@example.com | 555-234-5678
  • 3 | Carol | Davis | carol.d@example.com | 555-345-6789
  • 4 | David | Wilson | david.w@example.com | 555-456-7890
  • 5 | Eve | Brown | eve.b@example.com | 555-567-8901
  • 6 | Frank | Miller | frank.m@example.com | 555-678-9012
  • 7 | Grace | Taylor | grace.t@example.com | 555-789-0123
  • 8 | Henry | Moore | henry.m@example.com | 555-890-1234
  • 9 | Ivy | White | ivy.w@example.com | 555-901-2345
  • 10 | Jack | Green | jack.g@example.com | 555-012-3456

• Go to the Create tab and select Table Design.
• Add the following fields with their respective data types:
  • LoanID: AutoNumber (Set as Primary Key)
  • BookID: Number (Field Size: Long Integer)
  • MemberID: Number (Field Size: Long Integer)
  • LoanDate: Date/Time (Format: Short Date)
  • ReturnDate: Date/Time (Format: Short Date)
• Save the table as Loans.

Now, establish relationships:

• Go to Database Tools tab and select Relationships.
• Add Books, Members, and Loans tables.
• Drag BookID from Books to BookID in Loans to create a relationship. Enforce Referential Integrity.
• Drag MemberID from Members to MemberID in Loans to create a relationship. Enforce Referential Integrity.

Open the Loans table in Datasheet View and add at least 5 records. Ensure BookID and MemberID correspond to existing IDs in their respective tables.

• Loans Table Example Data:
  • LoanID | BookID | MemberID | LoanDate | ReturnDate
  • 1 | 1 | 1 | 2023-01-05 | 2023-01-19
  • 2 | 3 | 2 | 2023-01-07 | 2023-01-21
  • 3 | 5 | 1 | 2023-01-10 | 2023-01-24
  • 4 | 2 | 3 | 2023-01-12 | 2023-01-26
  • 5 | 4 | 2 | 2023-01-15 | 2023-01-29

• Open the Books table in Design View.

• Add a new field named Location with Short Text as its data type.

• Save the table.

• Open the Books table in Datasheet View and add example locations for each book.

• Books Table with Location Example Data:

  • BookID | Title | Author | Genre | YearPublished | Location
  • 1 | The Great Gatsby | F. Scott Fitzgerald | Classic | 1925 | Shelf A1
  • 2 | To Kill a Mockingbird | Harper Lee | Fiction | 1960 | Shelf B2
  • 3 | 1984 | George Orwell | Dystopian | 1949 | Shelf C3
  • 4 | Pride and Prejudice | Jane Austen | Romance | 1813 | Shelf A2
  • 5 | The Hobbit | J.R.R. Tolkien | Fantasy | 1937 | Shelf D1
  • 6 | Sapiens | Yuval Noah Harari | Non-Fiction | 2011 | Shelf E5
  • 7 | Educated | Tara Westover | Memoir | 2018 | Shelf F2
  • 8 | The Lord of the Rings | J.R.R. Tolkien | Fantasy | 1954 | Shelf D1
  • 9 | Becoming | Michelle Obama | Memoir | 2018 | Shelf F3
  • 10 | The Alchemist | Paulo Coelho | Philosophical | 1988 | Shelf G1

• Open the Members table in Design View.
• Select the PhoneNumber field.
• In the Field Properties pane (at the bottom):
  • Set the Required property to Yes.
  • Set the Input Mask property to (000)-000-0000. You can use the Input Mask Wizard for this.
• Save the table.

All tables (Books, Members, Loans) and fields (BookID, Title, FirstName, LoanDate, etc.) use clear, descriptive names without spaces, following standard database naming conventions. This ensures consistency and readability.

The database structure is now complete, fulfilling all specified requirements.
Database design and implementation complete.