# SQL Library Database: Tables, Keys & JOINs

TechBuilt Open School — Free, self-guided technical project kit

- **Track:** Databases & SQL
- **Level:** Beginner
- **Access:** No account, signup, payment or enrollment required
- **Format:** Guided plan and runnable starter example, not a complete solution or a live class
- **Online guide:** https://techbuiltos.online/projects/sql-library-database

## Project goal

Understand primary keys, foreign keys, constraints and readable relational queries.

Design a three-table library lending database and write queries for active loans and overdue items.

## Before you begin

- SQL editor or local SQLite database
- Basic SELECT and INSERT familiarity

## Local setup

1. Create a new SQLite database using sqlite3 or DB Browser for SQLite.
2. Run the starter SQL below; SQLite foreign keys must be enabled per connection.
3. Insert sample members and books before adding a loan.

## Starter example — library.sql

The sample below is a starting point. It may require the prerequisites above. Complete the subsequent milestones to finish your own project.

```sql
PRAGMA foreign_keys = ON;

CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL
);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);
CREATE TABLE loans (
  id INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id),
  book_id INTEGER NOT NULL REFERENCES books(id),
  returned_at TEXT
);

INSERT INTO members (id, full_name) VALUES (1, 'Aisha');
INSERT INTO books (id, title) VALUES (1, 'Python Basics');
INSERT INTO loans (id, member_id, book_id) VALUES (1, 1, 1);

SELECT m.full_name, b.title
FROM loans AS l
JOIN members AS m ON m.id = l.member_id
JOIN books AS b ON b.id = l.book_id
WHERE l.returned_at IS NULL;
```

## Build milestones

1. Create members, books and loans tables with appropriate keys.
2. Insert at least three books, two members and three loan records.
3. Write JOIN queries for active loans and members with borrowed books.
4. Reject an invalid member ID by keeping foreign key checks enabled.
5. Explain how a loan record differs from a book record.

## Completion checklist

- [ ] Each member and book has a unique primary key.
- [ ] The loans table references valid members and books.
- [ ] Active-loan query lists book title and borrower name.
- [ ] You can explain why deleting a member with active loans should be handled carefully.

## Optional extensions

- Add loan dates and overdue queries with a documented due-date rule.
- Create a simple Python sqlite3 command-line client using parameterized queries.

## Related learning

- Individual course roadmap: https://techbuiltos.online/courses/sql
- Individual course roadmap: https://techbuiltos.online/courses/database-fundamentals

- Specialization: https://techbuiltos.online/specializations/database-developer
- Specialization: https://techbuiltos.online/specializations/backend-developer

---

This original self-study project is independent of paid cohorts. Review any external library documentation before use. Do not commit real credentials, private client files or personal data to public repositories.
