| 1 |
As a customer in her 20s |
I want to borrow a couple of books a month |
So that that I can read for leisure |
Need to create a borrowed table – linking copies and customer together. UPDATE borrowed table SET copy = loaned. UPDATE copy table SET loan |
No age field |
| 2 |
As a teacher |
I want to find books by genre |
So that I can plan lessons |
SELECT title, genre FROM book [consider INNER JOIN to show genre with the title – title and genre table |
|
| 3 |
As a parent |
I want to search for books by age range |
So that my child has suitable reading material |
SELECT title, age range FROM book [consider INNER JOIN to show age range with the title – title and age range table) |
|
| 4 |
As a student |
I want to reserve books out on loan |
So that I can borrow them by a specified date |
Need to create a borrowed table – linking copies and customer together. UPDATE borrowed table SET copy = reserved. UPDATE copy table SET reserved |
|
| 5 |
As a university professor |
I want to see a list of previously borrowed books in my account area |
So that I can remember titles I have read |
SELECT statement (where would we be storing history? Are we designing an account area |
|
| 6 |
As a customer |
I want to search for books by title |
so that I can quickly find what I am looking for |
Book title, InBranch, IsAvailable, Author, Year published, Genre, age range, ISBN |
|
| 7 |
As a customer |
I want to search for books by author |
so that I can see everything they have published |
Book title, InBranch, IsAvailable, Author, Year published, Genre, age range, ISBN |
|
| 8 |
As a customer |
I want to search for books by ISBN |
so that I can quickly find what I am looking for |
Book title, InBranch, IsAvailable, Author, Year published, Genre, age range, ISBN |
|
| 9 |
As a customer |
I want to have a 'my account' area |
So that I can see personal and borrowing information |
|
|
| 10 |
As a customer |
I want to delete reserved books from my account |
so that I can change my mind |
|
|
| 11 |
As a customer |
I want to be able to update my name and address information |
so that I can keep my credentials up-to-date |
UPDATE statement that accesses LibraryCardHolder to update firstname/lastname/email etc with join to address tables to update address |
|
| 12 |
As a customer |
I want to be able to change my password |
so that I can access my account if I forget my existing password |
UPDATE statement that accesses user's password field in LibraryCardHolder |
|
| 13 |
As a library website administrator |
I want to search for user accounts |
So that so that I can answer users’ account queries |
SELECT statement that selects users from LibraryCardHolder joined to loans table (not built yet) |
|
| 14 |
As a library website administrator |
I want add new book titles to the database as they become available |
So that users can borrow them |
INSERT to add new books to book table with join to author table |
|
| 15 |
As a library website administrator |
I want to set a control on the maximum number of books a user can borrow |
So that I can control stock |
OUT OF SCOPE. SELECT to access Loan table where loan ID matches UserCard ID and calculate if <= 5 |
|
| 16 |
As a library website administrator |
I want to see alerts when loaned books are overdue |
So that I can chase customers |
An alert would need web functionality, but would ultimately call on a SELECT statement that filters for books where (Today's Date - DateOut)>30 |
|
| 17 |
As an administrator |
I want to have a log of all users with a library card |
so that they can be contacted as required |
SELECT would be used to access the LibraryCardHolder table for customer details, with a join on the address tables to also get the address. |
|
| 18 |
As an administrator/customer |
I want to be able to see what books are available |
so that I don't get/recommend a book that's already on loan |
SELECT statement that selects books (joined to the loans table) where DateReturned not NULL |
|
| 19 |
As a manager |
I want to securely store the details of staff on our payroll |
so that they can only be accessed by me/other authorised personel |
CREATE table, then INSERT to add new staff members, (UPDATE if details need changing, DELETE to remove old members of staff)-these latter permissions could be added at a later date so that we keep permissions to minimum requirements. Access for manager (and admin) ONLY |
|
| 20 |
As a librarian |
I want to be able to see stock levels of books across the city |
so that we can recommend customers to go elsewhere if we are out of stock |
SELECT statement similar to user story 18, however we would also join to the LibraryBranches table to see book availability across branches. |
|
| 21 |
As a customer |
I want to check out a book |
so I can read it |
UPDATE statement |
|
| 22 |
As a customer |
I want to check in a book |
so I don't get a libary fine |
UPDATE statement |
|