Designing a Library Database: Tables and Structure Modification
When designing a database for a library system, it is important to organize data efficiently and maintain clear relationships between authors, books, members, and loans.
1. Creating Tables for the Library System
We will create four tables: Authors, Books, Members, and Loans. Each table has a primary key to uniquely identify records and foreign keys to maintain relationships.
SQL CREATE TABLE Statements
CREATE TABLE Authors (
author_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
birthdate DATE
);
CREATE TABLE Books (
book_id INT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author_id INT,
publication_year INT,
genre VARCHAR(50),
FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);
CREATE TABLE Members (
member_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
membership_date DATE,
address VARCHAR(255)
);
CREATE TABLE Loans (
loan_id INT PRIMARY KEY,
book_id INT,
member_id INT,
loan_date DATE,
return_date DATE,
FOREIGN KEY (book_id) REFERENCES Books(book_id),
FOREIGN KEY (member_id) REFERENCES Members(member_id)
);
2. Modifying the Members Table Using ALTER TABLE
To add a new column email for storing member email addresses, we use the following command:
ALTER TABLE Members ADD COLUMN email VARCHAR(100);
Summary
- Each table is designed with appropriate primary keys to ensure unique identification.
- Foreign keys enforce relationships between authors, books, members, and loans.
- The ALTER TABLE command allows us to add new columns to an existing table without losing data.
- This schema supports efficient management of library data including tracking loans and member details.