... but, there is no mechanism to understand why the DVD drive was considered bootable and the tape drive not.
There are different kind of SCSI devices. The one we're most familiar is direct access devices, colloquially known as disks. They have in common that you can seek to a different place, and read there. Reads are of a fixed size, usually known as the block size (wrongly called the sector size). Examples include hard disks (spinning rust), SSDs, various SD card and USB devices (think of them as SSDs with different interfaces), floppies, CDs and DVDs. Today's BIOSes know how to boot from many of them.
Then there is sequential access devices, colloquially known as tapes. Seeking on them is somewhere between hard and impossible. Today's BIOSes don't know how to boot from them. Using today's boot loader code in the BIOS on them makes no sense ... they have no MBR or GPT, you can't seek to a boot partition, you can't read specific blocks (wrongly called "sectors") from them, and so on. In the earlier days of computing, booting from tape was great, and implemented: You prepared a tape that had the executable code right at the beginning, and the boot code simply copied whatever data was at the beginning of the tape (up to the first tape mark) into RAM starting at address zero, and then branched to zero, and we're off to the races. The world isn't that simple any more.
And then there are lots of other types of SCSI devices. For example printers, scanners, media changers (the little or giant robots that put the correct tape into the drive), and so on. Clearly, you will never boot from a SCSI-attached printer. You could in theory boot from a scanner (it is at least capable of reading data), but clearly that is completely impractical.
In the old days, computers booted from lots of other media. I still have some cp/m machines in the basement that are theoretically capable of booting from paper tape. Alas, I don't have a paper tape reader or punch. Early computers used to "boot" from punched card readers, but here the term "boot" is kind of misapplied: they didn't have what we would call an operating system, so if you wanted to run a program, you would compile it and link it with the necessary IO libraries. When you want to run that program, you stop your computer, put the compiled/linked deck of cards into the card reader, press the buttons on the console to start executing whatever is in the card reader, and it would "boot" that program.
Getting back to how to boot from tape: The functionality of distinguishing "disks" (meaning direct access devices) from "tape" (meaning sequential devices) from others is very fundamental to the SCSI interface, and to today's SATA implementation (in the old IDE/ATA that was fuzzy). Matter-of-fact, SCSI has a concept of "peripheral device type" which is baked deep into the protocol, and disks are device type 0, and tapes are device type 1, and then the fun ones start. I always use SCSI printers (which do exist, but are very rare today) to scare people who think they understand IO systems. Ever tried to create a file system on a printer?
To get a BIOS to boot from tape, the problem isn't LUNs or IRQs, but the basic strategy for booting: where on the tape do you find the executable code? What commands do you use to read it? Doing this would require a major rewrite of the BIOS. There are open-source BIOS implementations, but (a) they are hard to work with (16-bit C code, yuck yuck), and (b) they only support very limited hardware.