Library Management System

1. System Description (وصف النظام)The objective of this project is to design and implement a database system that manages the daily operations of a library. The system serves two main types of users: Administrators (Librarians) and Library Members (Borrowers). Librarians can add new books, update book information, and register new library members. Members can search for available books, borrow them for a specific period, and return them back to the library. 2. Main System Requirements & Features (المميزات والعمليات)Your database design must support the following core functional operations: Registering and managing library members and librarians. Keeping track of books, categories, and authors. Recording borrowing transactions (who borrowed what book, when it was borrowed, and when it is due). Managing the actual status of books (Available or Borrowed). Generating analytical reports such as viewing currently borrowed books, available books, and complete borrowing history. 3. Database Design: Suggested Entities & Attributes (الكيانات والخصائص)To fulfill the minimum size requirement of 6–8 tables, your database should include the following core entities: Books Table: Stores book profiles.Book_ID (Primary Key), Title, ISBN, Publication_Year, Category_ID (Foreign Key).Members Table: Stores information about library members.Member_ID (Primary Key), Full_Name, Email, Phone_Number, Registration_Date.Librarians Table: Stores details of the library staff.Librarian_ID (Primary Key), Name, Email, Shift.Categories Table: Organizes books by genre.Category_ID (Primary Key), Category_Name (e.g., Fiction, Science, History).Authors Table: Stores book authors' information.Author_ID (Primary Key), Author_Name, Nationality.Borrowing_Transactions Table: A crucial junction table to track borrowings (Many-to-Many relationship between Members and Books). Transaction_ID (Primary Key), Member_ID (Foreign Key), Book_ID (Foreign Key), Borrow_Date, Due_Date, Return_Date (can be null until returned), Librarian_ID (Foreign Key).4. Expected Relationships & Cardinalities (العلاقات)Your ERD must properly display these connections: Category to Books (1:M): One category can contain many books, but a book belongs to only one category. Members to Books (M:N - Many-to-Many): A member can borrow multiple books over time, and a book can be borrowed by multiple members over time. This is resolved using the Borrowing_Transactions table. Librarian to Transactions (1:M): A librarian can process multiple borrowing transactions. 5. Project Development Phases (مراحل تسليم المشروع)Your grading is strictly split into two evaluation phases: Phase 1: System Analysis and ERD Design (30 Marks) You need to present the conceptual design of your project: Project Idea & Problem Description: Explain the purpose of the library system and the problems it solves (e.g., preventing manual paperwork errors). System Requirements: Define the list of operations supported. ERD Diagram: Create the visual layout demonstrating your Entities, Attributes, Primary Keys, Foreign Keys, and exact Cardinalities (1:M, M:N). Phase 2: Database Implementation & SQL (70 Marks) You will convert your diagram into a working database and write practical SQL code: Relational Schema: Map your ERD directly into table structures. SQL Implementation (CREATE TABLE): Write the actual DDL script with suitable data types (INT, VARCHAR, DATE) and constraints (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY). Sample Data (INSERT): Put enough meaningful sample data into your 6–8 tables to test if things work properly. CRUD Operations: Write examples of INSERT new books, UPDATE member details, and DELETE records where allowed. Analytical Queries & Reports: Write SELECT queries utilizing advanced SQL commands: JOIN: To list which member borrowed which book title. GROUP BY & COUNT: To show how many books are available under each category. ORDER BY: To sort borrowed transactions from newest to oldest.