================================================================
LAB 5 — SQL & Databases: Library Schema
================================================================
Course:    Web Technologies
Topic:     Lecture 7 (Databases)
Time:      2 hours lab + 2 hours self-study

----------------------------------------------------------------
LEARNING GOALS
----------------------------------------------------------------
- Design a small relational schema (3+ tables, 1+ foreign key)
- Write CREATE, INSERT, SELECT, UPDATE, DELETE
- Use JOIN, GROUP BY, ORDER BY, LIMIT
- Recognise and prevent SQL injection
- Use transactions

----------------------------------------------------------------
TOOL
----------------------------------------------------------------
SQLite (free, no server). Use either:
  - DB Browser for SQLite (GUI), or
  - sqlite3 CLI

----------------------------------------------------------------
TASKS
----------------------------------------------------------------

TASK 1 — Design + create schema
  Three tables for a small library:
    members  (id, name, email UNIQUE, joined_at)
    books    (id, title, author, isbn UNIQUE, copies)
    loans    (id, member_id REFERENCES members, book_id REFERENCES books,
              loaned_at, returned_at NULLABLE)

  Add appropriate NOT NULL, UNIQUE, CHECK, and an index on loans(member_id).

TASK 2 — Seed
  - Insert 10 members, 20 books, 30 loans (mix of returned and outstanding).

TASK 3 — Queries (write each as a .sql file in your repo)
  q1.sql  All books, sorted by title
  q2.sql  Members who have at least 3 outstanding loans
  q3.sql  Top 5 most-loaned books (count of loans)
  q4.sql  Average loan duration in days for returned loans
  q5.sql  Members with their current outstanding loans (LEFT JOIN, GROUP BY)

TASK 4 — Update / delete + transaction
  - Inside a transaction:
      mark loan id=42 as returned
      decrement nothing (copies stays the same)
  - Demonstrate ROLLBACK in a separate script

TASK 5 — SQL injection demo
  - Write two short examples in pseudo-code:
      a) bad_query.txt -- string concatenation, attacker enters: ' OR '1'='1
      b) good_query.txt -- parameterised query that's safe
  - Explain in 3 sentences each why the bad one fails

----------------------------------------------------------------
DELIVERABLES
----------------------------------------------------------------
- schema.sql, seed.sql, q1..q5.sql, transaction.sql
- bad_query.txt + good_query.txt
- README explaining how to run the scripts

----------------------------------------------------------------
GRADING (10 marks)
----------------------------------------------------------------
Schema correctness ................ 2
Seed data quality ................. 1
5 queries correct ................. 4
Transaction with rollback ......... 1
SQL injection explanation ......... 2