Beginner Project: A School Library Database
Put everything you have learned together and build a real, working library database.
- plan a small database of books, students and loans,
- write
CREATE TABLEstatements with a primary key and foreign keys, - add rows with
INSERT, - answer real questions using
SELECT,WHERE,ORDER BY,JOINandGROUP BY.
1. The plan: three tables
A school library keeps track of three kinds of thing, so we will use three tables. Think of each table as one neat list:
- books - one row for each book the library owns.
- students - one row for each student who can borrow.
- loans - one row every time a student borrows a book.
Why three tables and not one big one? Because a single giant table would repeat the same book title and the same student name over and over. Splitting the data keeps each fact stored once. The loans table is the clever part: instead of copying a whole book and a whole student, each loan just stores the book_id and the student_id. Those little numbers point back to the real rows. That pointer is called a foreign key.
Analogy: imagine a shelf of books and a class of students. A loan is like a little sticky note that says "book number 1 is with student number 3". The note does not copy the whole book or the whole student - it just writes down their numbers. That is exactly what a foreign key does.