Database Individual Assignment: Design A Database Entity
Database Individual Assignmentdesign A Database Entity And Relations
Design a database (entity and relationship tables in Access) for a small college department library. You should have the entities and relationships you need to support: Purchase books, Sell or discard books, Check out books, Return books, Search books by: Title, Author (multiple authors possible), Subject (multiple subjects possible), ISBN.
Paper For Above instruction
Designing a database for a small college department library involves creating a structured system that efficiently manages book information, transactions, and associated entities. The primary goal is to support various functions such as purchasing new books, discarding outdated or damaged ones, checking out and returning books, and searching the collection based on different attributes like title, author, subject, and ISBN. To achieve these objectives, a comprehensive relational database model must be developed, encompassing core entities, their attributes, and the relationships among them.
Entities and Their Attributes
Books: The central entity in the database is the 'Books' entity. Each record in this table represents a unique book in the library’s collection. Attributes include ISBN (as a primary key), title, publisher, publication year, and a status indicator to specify whether the book is available, checked out, or discarded.
Authors: Since books may have multiple authors, the 'Authors' entity captures information such as author_id (primary key), author_name, and possibly contact details or affiliations. To accommodate multiple authors per book, a many-to-many relationship must be established between 'Books' and 'Authors' via a junction table.
Subjects: Books can belong to multiple subjects or categories. The 'Subjects' entity includes subject_id (primary key) and subject_name. Similar to authors, a many-to-many relationship is created between 'Books' and 'Subjects' through an associative table.
Transactions: The 'Transactions' entity records all check-out and return activities. Attributes include transaction_id (primary key), book_id (foreign key), member_id (foreign key to a 'Members' table), transaction_type (check-out or return), transaction_date, and due_date for check-outs.
Members: The 'Members' entity contains information about library users, including member_id (primary key), name, contact details, and membership status. This supports the issuance and return transactions.
Relationships and Their Implementation
The relationships are constructed to reflect the real-world connections between entities:
- Books–Authors: Many-to-many. Implemented via a junction table (e.g., BookAuthors) with foreign keys book_id and author_id.
- Books–Subjects: Many-to-many. Implemented via a junction table (e.g., BookSubjects) with foreign keys book_id and subject_id.
- Books–Transactions: One-to-many, as each book can have multiple transactions. The 'Transactions' table links to 'Books' via book_id.
- Members–Transactions: One-to-many, as each member can have multiple transactions. Linkage via member_id.
Additional Features and Functionality
The database should include search functionalities that allow users to find books by title, author(s), subject(s), and ISBN. Since multiple authors and subjects are possible, search queries should support filtering across related tables using JOIN operations in SQL. The system should also handle the status of each book, enabling easy identification of available, checked-out, or discarded books, and facilitate operations like purchasing new books or discarding old ones by updating the 'Books' table accordingly.
Implementation in Microsoft Access
Using Access, each entity translates into a table with appropriate primary keys and attributes. Relationships are established via the Relationships view, enforcing referential integrity. Junction tables for many-to-many relationships are created to link 'Books' with 'Authors' and 'Subjects.' Forms and queries can be designed for user interaction, including search functions, transaction recording, and book management.
Conclusion
The proposed database design provides a comprehensive structure capable of supporting all specified library functions efficiently. Proper normalization ensures minimal data redundancy, while relationships facilitate complex queries necessary for effective library management. Implementation in Access allows for straightforward creation of tables, forms, and reports, supporting the operational needs of the college department library.
References
- Date, C. J. (2019). Database Design and Relational Theory: Normal Forms and All That Jazz. O'Reilly Media.
- Fundamentals of Database Systems. Pearson.