Start with a data model
Model a library with Books, Members and Loans. Assign stable primary keys, connect each loan to one member and one book, and decide which fields may be null. Keep a book title on Books rather than copying it into every loan. This makes updates consistent and exposes the purpose of normalization.
Write useful SQL
Create the tables, insert five books and three members, and write a JOIN that lists current loans with member names. Add a due-date filter and use GROUP BY to count loans per member. Compare INNER JOIN with LEFT JOIN by including a member who has never borrowed a book.
Protect consistency
Use foreign keys and a unique constraint where the domain requires uniqueness. Wrap a checkout operation in a transaction so a failure cannot leave half an update committed. Explain atomicity, consistency, isolation and durability using that checkout example rather than memorizing definitions.
Practice and review
Deliver an entity relationship diagram, schema SQL, seed data and ten queries. Use EXPLAIN to inspect a lookup before and after an index. Test an invalid member ID, a duplicate identifier and a rolled-back transaction. Review why indexing every column can slow writes and consume storage.
DBMS lab: reproduce a failed checkout
Create two concurrent checkout requests for the same available copy. First observe the result without a constraint or locking strategy, then add a transaction and an appropriate uniqueness rule for active loans. Record the database's actual behaviour instead of assuming both requests execute sequentially. Your acceptance criteria are one successful checkout, a clear response to the other request and no orphaned loan row. Include the exact SQL and expected rows after rollback.
Check your progress
Keep your working files, test cases and notes together. Explain one design decision, demonstrate a failure case and list what you would improve next. These concrete outputs make it easier to identify the next skill to practice.