Library Management System:
Build a database to manage library resources, including books, borrowers, and transactions.
2.1 Tables
Feel free to customize the column names, data types, and constraints based on your specific requirements.
Feel free to adjust the schema according to your course guidelines and any additional features you'd like to include!
2.1.1 Book Table
The Books table stores information about books, including their title, author, genre, and ISBN.
CREATE TABLE Books (
BookID INT PRIMARY KEY,
Title VARCHAR(255) NOT NULL,
Author VARCHAR(255),
Genre VARCHAR(50),
ISBN VARCHAR(20) UNIQUE
);
2.1.2 Borrowers Table
The Borrowers table contains details about library patrons, such as their first name, last name, email, and phone number.
CREATE TABLE Borrowers (
BorrowerID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE,
Phone VARCHAR(15)
);
2.1.3 Transactions Table
The Transactions table records borrowing transactions, linking books to borrowers and tracking borrow and return dates.
CREATE TABLE Transactions (
TransactionID INT PRIMARY KEY,
BookID INT,
BorrowerID INT,
BorrowDate DATE,
ReturnDate DATE,
FOREIGN KEY (BookID) REFERENCES Books(BookID),
FOREIGN KEY (BorrowerID) REFERENCES Borrowers(BorrowerID)
);
2.2 Requirements
Implement queries to track book availability, overdue books, and popular genres.
Explore JOINs to link borrower information with borrowed books.
Employee Management System:
Develop a database for an organization's employee records. Include tables for employees, departments, and job roles.
