Scribd
Upload
3 views3 pages

School Library Database Project-1

The document outlines the design of a School Library Management Database, detailing its goal to manage information about books, students, librarians, and borrowing activities. It includes business rules, entity attributes, SQL DDL queries for creating the database and tables, as well as sample data insertion and DML queries. The author reflects on their achievement in establishing relationships between tables and suggests potential improvements for future development.

Uploaded by

asaahestebane
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views3 pages

School Library Database Project-1

The document outlines the design of a School Library Management Database, detailing its goal to manage information about books, students, librarians, and borrowing activities. It includes business rules, entity attributes, SQL DDL queries for creating the database and tables, as well as sample data insertion and DML queries. The author reflects on their achievement in establishing relationships between tables and suggests potential improvements for future development.

Uploaded by

asaahestebane
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

MINI-PROJECT 7 – DATABASE FOR A PURPOSE

School Library Management Database

Step 1: Define – State Your Goal


The goal of this project is to design a School Library Management Database that stores and
manages information about books, students, librarians, and borrowing activities.

Business Rules:
1. Each student can borrow many books.
2. Each book has a unique Book ID.
3. A librarian manages borrowing transactions.
4. A student cannot borrow unavailable books.
5. Every borrowing transaction contains borrow and return dates.
6. A book may be borrowed by different students at different times.

Entities and Attributes


STUDENT(Student_ID, First_Name, Last_Name, Class, Phone_Number)

BOOK(Book_ID, Title, Author, Category, Quantity)

LIBRARIAN(Librarian_ID, Librarian_Name, Phone_Number, Email)

BORROW(Borrow_ID, Student_ID, Book_ID, Librarian_ID, Borrow_Date, Return_Date, Status)

SQL DDL Queries


CREATE DATABASE SchoolLibrary;

USE SchoolLibrary;

CREATE TABLE STUDENT (


Student_ID INT PRIMARY KEY,
First_Name VARCHAR(30),
Last_Name VARCHAR(30),
Class VARCHAR(10),
Phone_Number VARCHAR(15)
);

CREATE TABLE BOOK (


Book_ID INT PRIMARY KEY,
Title VARCHAR(50),
Author VARCHAR(40),
Category VARCHAR(30),
Quantity INT
);

CREATE TABLE LIBRARIAN (


Librarian_ID INT PRIMARY KEY,
Librarian_Name VARCHAR(40),
Phone_Number VARCHAR(15),
Email VARCHAR(50)
);

CREATE TABLE BORROW (


Borrow_ID INT PRIMARY KEY,
Student_ID INT,
Book_ID INT,
Librarian_ID INT,
Borrow_Date DATE,
Return_Date DATE,
Status VARCHAR(15),
FOREIGN KEY (Student_ID) REFERENCES STUDENT(Student_ID),
FOREIGN KEY (Book_ID) REFERENCES BOOK(Book_ID),
FOREIGN KEY (Librarian_ID) REFERENCES LIBRARIAN(Librarian_ID)
);

Insert Queries
INSERT INTO STUDENT VALUES
(101, 'John', 'Peter', 'Upper6', '677889900'),
(102, 'Mary', 'Ashu', 'Lower6', '655223344');

INSERT INTO BOOK VALUES


(201, 'Database Systems', 'K. Connolly', 'Education', 5),
(202, 'Things Fall Apart', 'Chinua Achebe', 'Literature', 3);

INSERT INTO LIBRARIAN VALUES


(301, 'Mrs Linda', '677111222', 'linda@[Link]'),
(302, 'Mr James', '699888777', 'james@[Link]');

INSERT INTO BORROW VALUES


(401, 101, 201, 301, '2026-02-10', '2026-02-20', 'Returned'),
(402, 102, 202, 302, '2026-02-12', '2026-02-22', 'Pending');

DML Queries
Query 1:
SELECT * FROM BOOK;
Query 2:
SELECT STUDENT.First_Name, [Link], [Link]
FROM BORROW
JOIN STUDENT ON BORROW.Student_ID = STUDENT.Student_ID
JOIN BOOK ON BORROW.Book_ID = BOOK.Book_ID;

Reflection
I am most proud of creating relationships between the tables using primary and foreign
keys because it made the database organized and efficient.

If I had more time, I would add a login system and automatic fine calculation for overdue
books.

Sample BOOK Table


Book_ID Title Author Category Quantity

201 Database K. Connolly Education 5


Systems

202 Things Fall Chinua Achebe Literature 3


Apart

You might also like