What to write or search on github to get the code for what is written below:

[Dennis Lee Bieber]

	o/~		talking to myself in public	o/~

On Mon, 17 Jan 2022 15:39:25 -0500, Dennis Lee Bieber
<wlfraed at ix.netcom.com> declaimed the following:


>	In one respect, given the limited functionality stated, one gets the
>impression of a class GROUP assignment, in which the individual functions
>were divvied up to each group member to work, with pretty much no analysis
>of suitable data storage, etc. (You only gave some sort of CONOPS for
>"users" to "reserve" books; no CONOPS for unreserving books, an implication
>that after reserving books the user will go to some desk [in library, in
>store {unlikely if there is a return date for books}, some warehouse] and
>receive a copy of the reserved book -- but no CONOPS for how this deck
>validates that the user has reserved any particular book [no report printed
>by user of reservations made during a session, no search function for desk
>staff, etc.).

	I'm in a fey mood today... The OP (and the rest of you reading) will
likely just skip this message... It's a rambling free-association harangue
on a possible RDBM schema -- which makes a few assumptions (described
following) and looks wildly different from the OP's vague data...

	Inferred from various posts is that this
		*	NOT a library (which may have at most 5 copies of popular
books)
		*	NOT a book store (which would not have check-out/check-in
dates)

	The best use case I can see is for something like a high school in
which textbooks are issued to students at the start of the course, and
collected at the end. For a small high school, say 600 students, there
might be 150 taking, say 3rd year Trigonometry, split among 6 session of 25
students each day. This justifies not tracking every copy of a book by full
title, et al.

	I'm going to take that as the overall system CONOPS.

	Needed data: book title, book author(s), ISBN and/or other call number
(some texts may be old enough to predate ISBN), # copies, # available,
dates for check-out and return, student name. Probably more as I go down...

	Schema format notation:

		relation(attribute list)
			special constraints description

where 
	*attribute* is a non-null, unique index, primary key (preferably
autonumber)
	_attribute_ is a non-null, duplicates allowed index, foreign key (in
the form relation-attribute)
	/attribute/ is an optional/alternate indexed attribute

Publisher(*ID*, name)

Book(*ID*, ISBN, alt-call, title, _Publisher-ID_, copyright-date)
	constraint(ISBN not null OR alt-call not null)

Author(*ID*, /last-name/, first-name)
	constraint(last-name not null)

Book-Author(*ID*, _Book-ID_, _Author-ID_)
	{this links authors to books, and allows for books to have more than
one author}

Copy(*ID*, _Book-ID_, copy-number, status, condition)
	constraint(status in "A", "O", "R", status not null, default "A")
	constraint(copy-number not null, [Book-ID copy-number] is unique)
		{A = available, O = checked out, R = reserved but not yet checked
out}
		{condition contains notes on known damage, etc. for this copy}

Student(*ID*, /last-name/, first-name)
	constraint(last-name not null)
		{I expect this to expand with student ID number, home address,
etc.)

CheckOut(*ID*, _Copy-ID_, _Student-ID_, reservation-date, checkout-date,
due-date, active)
	constraint([Copy-ID Student-ID] is unique)
	constraint(active not null boolean default true)
		{can automate cancelling reservations after n-days, and can also be
used to produce a list of past-due}
		{active is used to allow for history of check outs, set to false
when book is returned (and hence made Available again)

	SEVEN relations (aka "tables") in an RDBM. While each book /title/
appears only once, it does take a small record to identify each copy of the
book and track availability of the copy. Reserving a book requires
transaction to select the first copy with status = "A" for that title,
update status to "R", insert a checkout record with the currently selected
student and current date for reservation date. When actually given the
book, a transaction to find the active checkout record for the student, for
that title is done, the checkout and due dates are set, the copy record is
updated for status = "O". When returned, a transaction is done to locate
the checkout record, update active to false, update Copy record to set
status to "A", and optionally update the condition field to indicate
damages inflicted by the student while it was checked out. If a reserve
operation fails (another session snagged the same copy record and updated
it) one just repeats the transaction trying for the next available copy
record -- only if there are no copies with status "A" do you have to abort
and tell the student they can't have the book.

	There is no "count" of copies, no "count" of available -- these can be
computed based upon SQL cnt() functions; untested

	select b.title as Title, 
		cnt(cpy1.copy_number) as Total, 
		cnt(cpy2.copy_number) as Available
	from Book as b 
	inner join Copy as cpy1 
		on b.ID = cpy1.Book-ID
	left join Copy as cpy2
		on cpy1.ID = cpy2.ID and cpy2.status = "A"
	group by b.title
	order by b.title;


-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
	wlfraed at ix.netcom.com    http://wlfraed.microdiversity.freeddns.org/