If you only had two tables, Session and Booking, you could do this:

1. Add a UNIQUE constraint on [dbo].[Session]([ID], [CourseSizeMax]) - it is needed later.
2. Add CourseSizeMax column, and BookingNumber column to your dbo.Booking table.
3. Add a FK constraint on dbo.Booking(CourseId, CourseSizeMax) referring to Session
4. Add a CHECK(BookingNumber BETWEEN 1 AND CourseSizeMax)

and you are all set, as long as all your constraints are trusted.

I am not sure why would you need the third table CourseSize at all.

If you only had two tables, Session and Booking, you could do this:

1. Add CourseSizeMax column to your dbo.Session table, and add a UNIQUE constraint on [dbo].[Session]([ID], [CourseSizeMax]) - it is needed later.
2. Add CourseSizeMax column, and BookingNumber column to your dbo.Booking table.
3. Add a FK constraint on dbo.Booking(CourseId, CourseSizeMax) referring to Session
4. Add a CHECK(BookingNumber BETWEEN 1 AND CourseSizeMax)
5. Add a UNIQUE constraint on dbo.Booking(CourseId, BookingNumber)

and you are all set, as long as all your constraints are trusted.

I am not sure why would you need the third table CourseSize at all.