SELECT
	person.person_name
FROM
	program, cast, person, cast_person
WHERE
	program.cast_id=cast.cast_id
AND
	cast.cast_id =cast_person.cast_id
AND
	person.person_id=cast_person.person_id

-- ----------------------------
-- Table structure for cast
-- ----------------------------
CREATE TABLE `cast` (
  `cast_id` smallint(6) NOT NULL auto_increment,
  `cast_name` varchar(30) NOT NULL,
  PRIMARY KEY  (`cast_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for cast_person
-- ----------------------------
CREATE TABLE `cast_person` (
  `cast_person_id` smallint(6) NOT NULL,
  `cast_id` smallint(6) NOT NULL,
  `person_id` smallint(6) NOT NULL,
  `cast_member_name` varchar(30) NOT NULL,
  PRIMARY KEY  (`cast_person_id`),
  KEY `person_id_fk` (`person_id`),
  KEY `cast_id_fk` (`cast_id`),
  CONSTRAINT `cast_person_ibfk_1` FOREIGN KEY (`cast_id`) REFERENCES `cast` (`cast_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `cast_person_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `person` (`person_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- ----------------------------
-- Table structure for person
-- ----------------------------
CREATE TABLE `person` (
  `person_id` smallint(6) NOT NULL auto_increment,
  `person_name` varchar(20) NOT NULL,
  `person_detail` longtext NOT NULL,
  PRIMARY KEY  (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 3072 kB';

-- ----------------------------
-- Table structure for program
-- ----------------------------
CREATE TABLE `program` (
  `program_id` int(11) NOT NULL auto_increment,
  `program_name` varchar(30) character set utf8 NOT NULL,
  `program_description` varchar(500) NOT NULL,
  `date` date NOT NULL,
  `start_time` datetime NOT NULL,
  `finish_time` datetime NOT NULL,
  `widescreen` enum('Yes','No') NOT NULL,
  `subtitles` enum('Yes','No') NOT NULL,
  `program_type_id` tinyint(4) NOT NULL,
  `director_id` smallint(6) NOT NULL,
  `cast_id` smallint(6) NOT NULL,
  PRIMARY KEY  (`program_id`),
  KEY `program_program_type_id_fk` (`program_type_id`),
  KEY `program_director_fk` (`director_id`),
  CONSTRAINT `program_director_fk` FOREIGN KEY (`director_id`) REFERENCES `director` (`director_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `program_program_type_id_fk` FOREIGN KEY (`program_type_id`) REFERENCES `program_type` (`program_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;