Database project: a library system
You design and build a library’s lending system yourself end to end: from requirements to schema, from data to queries, from privileges to backups.
Level 201 · 15 lessons · 12 practice labs
Lessons
Design
- Gathering requirements
- The ER model and tables
- Rules: loan limits and dates
- Short task: review and fix the schema
Data and queries
- Importing sample data
- Loan and return transactions
- Overdue books and fines
- Monthly reports
- Book search
- Book reservations and waiting lists
- Short task: views for library staff
Operations
- Staff, member and admin privileges
- Indexes for speed
- Backup and restore plan
- Capstone: document and hand over the system
Practice labs
- Pull entities out of requirements: List entities, fields and relationships from Kardelen Public Library's one-page request.
- Build a schema that separates titles from copies: Separate title and copy tables so several copies of one book can be tracked, and test with sample data.
- Enforce loan rules with constraints: Enforce at most five books per member and a return date after the loan date at database level.
- Record a loan: Record a loan in one transaction that also updates the copy's status.
- Calculate late fines: Write a query or procedure that computes a per-day fine for returned books and adds it to the member balance.
- Stop one copy being lent to two people: Reproduce two librarians lending the same copy at the same moment and prevent it with locking.
- List overdue books: List overdue books with member contact details and days late.
- Manage the reservation queue: Build logic that holds a returned book for the first member in the waiting list and keeps the rest queued.
- Prepare monthly report views for staff: Create views for most-read books, active member rate and branch comparison, and measure their speed.
- Separate staff and member privileges: Define separate database roles for librarians, the member app and the admin, and test each one.
- Speed up book search with indexes: Inspect the plan for title and author search, add suitable indexes and compare response times.
- Hand over the library system with a handover document: Test and verify backup and restore, then gather the schema and operating notes into a handover document.
About this course
You design and build a library’s lending system yourself end to end: from requirements to schema, from data to queries, from privileges to backups.
What you will learn
- Design
- Data and queries
- Operations
Prerequisites
Finishing the level 101 courses of the same path, or knowing those topics, is enough.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 201; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.