A relational database solution for Cooper Books, a two-location bookstore. Designed from scratch based on a business requirements document, covering schema design, normalization, sample data, and a full query library.
Eight tables across three layers:
Base tables — tblPublisher, tblAuthors, tblGenre, tblStore
Dependent tables — tblBooks (FK → Publisher)
Junction tables — tblInventory (Store ↔ Books), tblBookAuthor
(Books ↔ Authors), tblBookGenre (Books ↔ Genres)
Many-to-many relationships handled via junction tables — a book can have multiple authors and multiple genres, and inventory is tracked per branch.
- Follows 1NF, 2NF, and 3NF throughout
- All primary/foreign key relationships defined before data insertion
- Genre enforced via controlled list (
tblGenre) rather than free text - Per-branch inventory allows different stock levels across locations
- All code commented and formatted for readability
- Required queries
- All books with author, publisher, genre, and price
- All books by branch with quantity on hand
- Books sorted by genre and title
- Aggregate pricing — highest, lowest, and average
- Search queries
- Search by title (
LIKE) - Search by author name
- Search by genre
- Search by store location
- Business analytics (extra credit)
- Total inventory value per store formatted as currency
- Top 5 most expensive books in stock
- Authors with more than one title, with book list via
STRING_AGG
- 5 publishers (major trade publishers with real corporate contacts)
- 10 authors across Fiction, Thriller, Fantasy, Horror, Mystery, and Classic
- 16 book titles across both store locations
- Inventory varied per branch — not all titles carried at both locations
- Microsoft SQL Server 2022
- T-SQL