IDE, AKA Parallel ATA / PATA, is a control interface for storage devices that have an onboard disk controller. The interface allows the host controller to issue commands to the disk controller. The command set used by PATA/SATA is called ATA. ATA stands for AT Attachment, and was introduced by IBM with the IBM PC/AT.
SCSI is a set of standards for attaching peripheral devices to a computer. In many ways it competes with ATA, but there's a much stronger amount of separation between the various components of the SCSI standards. Indeed, the SCSI command set is very independent of the SCSI physical layers.
Unlike ATA, SCSI is a multi-master / multi-slave architecture. Masters can communicate with other masters, masters can communicate with slaves, and slaves can communicate with slaves. This allows multiple devices to be attached to multiple hosts, which allows for some interesting redundant deployment configurations that are very desirable in enterprise environments.
One of the weaknesses of the ATA command set is that it is suitable for use with only hard disk drives or devices that can effectively be emulated as hard disk drives. It is not suitable for use with devices that have removable media such as floppy disk drives, optical drives, and tape drives.
SCSI on the other hand is fairly agnostic with respect to the nature of attached devices, albeit at the cost of some logical efficiency.
This presents a problem, SCSI is fairly expensive but ATA is not sufficient for all devices that may be found on a PC.
The solution is somewhat interesting.
All devices that are capable of operating natively using standard ATA do so. All devices that are not capable of operating natively using standard ATA operate using SCSI and then encapsulate the SCSI packet inside of an ATA packet. This approach is known as ATA Packet Interface, or ATAPI.
You will most likely see your CD-Drive/DVD-Drive/BlyRay-Drive listed as an ATAPI device separate from ATA speaking hard disk drives and native SCSI devices (if your platform supports some form of SCSI).
The same is true for USB Mass Storage devices (USB Flash drives, but not necessarily USB hard disks as these use an ATA-USB bridge that can cause massive issues). ATA on its own is simply insufficient, so SCSI is used instead due to its independence from the physical layer.
Were SCSI more affordable and more mechanically/electrically consistent it probably would have displaced ATA quite some time ago.