Library Management System — Core Schema

Problem Description

A public library district is modernizing its catalog and lending software. The tech team needs a clean SQL Server schema for the core domain: books, members, and loans. The existing prototype was written hastily with raw SQL types everywhere, inconsistent nullability, and constraint names that are meaningless in error logs. The team wants to start fresh with a maintainable schema that will serve as the foundation for the next decade of development.

The library manages a catalog of books (with authors, ISBN, and genre classification), members who can borrow books, and loan records that track which member has which book and when it's due. Books can be in one of several states (Available, OnLoan, Reserved, Lost). Members have a membership tier (Standard, Premium, Staff). The schema should enforce these classifications at the database level.

The developer's job is to produce the foundational DDL for this system: the custom type definitions, reference/lookup tables, and the core entity tables with proper relationships. The schema does not need stored procedures yet — just the structural layer.

Output Specification

Produce a single SQL script file named schema.sql that contains:

• The custom type definitions for all types used in the schema
• A YAML file named types.yaml that serves as the central manifest documenting all custom types with their underlying base type and default value
• Reference/lookup tables for book states and membership tiers (seeded with their values in the same script)
• The core entity tables: Book, Author, Book_Author (associative), Member, and Loan
• All foreign key and check constraints with appropriate names
• No stored procedures or views are needed — only the type and table DDL

The SQL script should be self-contained and runnable on a fresh SQL Server database.