In designing a Library Information System database, we need to identify:
Entities (main objects in the system)
Attributes (properties of those objects)
Relationships (how entities are connected)
Data Types (what kind of data each attribute holds)
---
1. Entities and Attributes
1.1. Books
Book_ID (Primary Key) – INT
Title – VARCHAR(255)
Author – VARCHAR(255)
ISBN – VARCHAR(20)
Genre – VARCHAR(50)
Publication_Year – YEAR
Availability_Status – BOOLEAN
1.2. Members
Member_ID (Primary Key) – INT
Name – VARCHAR(255)
Email – VARCHAR(255)
Phone – VARCHAR(20)
Address – TEXT
Membership_Date – DATE
1.3. Librarians
Librarian_ID (Primary Key) – INT
Name – VARCHAR(255)
Email – VARCHAR(255)
Phone – VARCHAR(20)
Hire_Date – DATE
1.4. Borrowing Transactions
Transaction_ID (Primary Key) – INT
Book_ID (Foreign Key) – INT
Member_ID (Foreign Key) – INT
Librarian_ID (Foreign Key) – INT
Borrow_Date – DATE
Due_Date – DATE
Return_Date – DATE (Nullable)
1.5. Fines
Fine_ID (Primary Key) – INT
Transaction_ID (Foreign Key) – INT
Amount – DECIMAL(10,2)
Payment_Status – BOOLEAN
---
2. Relationships
1. Books → Borrowing Transactions (One-to-Many)
A book can be borrowed multiple times but belongs to a single transaction at a time.
2. Members → Borrowing Transactions (One-to-Many)
A member can borrow multiple books.
3. Librarians → Borrowing Transactions (One-to-Many)
A librarian records multiple borrow transactions.
4. Borrowing Transactions → Fines (One-to-One or One-to-Many)
A late return can generate a fine, and each fine is linked to a borrowing transaction.
Would you like me to refine this further or add more details?
-- Create the database
CREATE DATABASE LibraryDB;
USE LibraryDB;
-- Table: Books
CREATE TABLE Books (
Book_ID INT AUTO_INCREMENT PRIMARY KEY,
Title VARCHAR(255) NOT NULL,
Author VARCHAR(255) NOT NULL,
ISBN VARCHAR(20) UNIQUE NOT NULL,
Genre VARCHAR(50),
Publication_Year YEAR,
Availability_Status BOOLEAN DEFAULT TRUE
);
-- Table: Members
CREATE TABLE Members (
Member_ID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Email VARCHAR(255) UNIQUE NOT NULL,
Phone VARCHAR(20) UNIQUE,
Address TEXT,
Membership_Date DATE DEFAULT CURRENT_DATE
);
-- Table: Librarians
CREATE TABLE Librarians (
Librarian_ID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Email VARCHAR(255) UNIQUE NOT NULL,
Phone VARCHAR(20) UNIQUE,
Hire_Date DATE DEFAULT CURRENT_DATE
);
-- Table: Borrowing Transactions
CREATE TABLE Borrowing_Transactions (
Transaction_ID INT AUTO_INCREMENT PRIMARY KEY,
Book_ID INT NOT NULL,
Member_ID INT NOT NULL,
Librarian_ID INT NOT NULL,
Borrow_Date DATE DEFAULT CURRENT_DATE,
Due_Date DATE NOT NULL,
Return_Date DATE NULL,
FOREIGN KEY (Book_ID) REFERENCES Books(Book_ID) ON DELETE CASCADE,
FOREIGN KEY (Member_ID) REFERENCES Members(Member_ID) ON DELETE CASCADE,
FOREIGN KEY (Librarian_ID) REFERENCES Librarians(Librarian_ID) ON DELETE SET NULL
);
-- Table: Fines
CREATE TABLE Fines (
Fine_ID INT AUTO_INCREMENT PRIMARY KEY,
Transaction_ID INT NOT NULL,
Amount DECIMAL(10,2) NOT NULL CHECK (Amount >= 0),
Payment_Status BOOLEAN DEFAULT FALSE,
FOREIGN KEY (Transaction_ID) REFERENCES Borrowing_Transactions(Transaction_ID) ON DELETE
CASCADE
);
