CREATE TABLE `course` (
  `course_name` varchar(20) NOT NULL,
  `description` varchar(100) default NULL,
  PRIMARY KEY  (`course_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `department` (
  `name` varchar(15) NOT NULL,
  `phoneno` int(10) default NULL,
  PRIMARY KEY  (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `employee` (
  `ssn` int(9) NOT NULL,
  `datefojoin` date default NULL,
  `designation` varchar(15) default NULL,
  `salary` int(6) default NULL,
  PRIMARY KEY  (`ssn`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`ssn`) REFERENCES `person` (`ssn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `employee` (
  `ssn` int(9) NOT NULL,
  `datefojoin` date default NULL,
  `designation` varchar(15) default NULL,
  `salary` int(6) default NULL,
  PRIMARY KEY  (`ssn`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`ssn`) REFERENCES `person` (`ssn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `hired_by` (
  `t_ssn` int(9) NOT NULL,
  `dept_name` varchar(15) default NULL,
  PRIMARY KEY  (`t_ssn`),
  UNIQUE KEY `dept_name` (`dept_name`),
  CONSTRAINT `hired_by_ibfk_1` FOREIGN KEY (`t_ssn`) REFERENCES `teachingstaff` (`essn`),
  CONSTRAINT `hired_by_ibfk_2` FOREIGN KEY (`dept_name`) REFERENCES `department` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `offers` (
  `dname` varchar(15) NOT NULL,
  `cname` varchar(20) default NULL,
  `noofseats` int(4) default NULL,
  PRIMARY KEY  (`dname`),
  UNIQUE KEY `cname` (`cname`),
  CONSTRAINT `offers_ibfk_1` FOREIGN KEY (`dname`) REFERENCES `department` (`name`),
  CONSTRAINT `offers_ibfk_2` FOREIGN KEY (`cname`) REFERENCES `course` (`course_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `person` (
  `ssn` int(9) NOT NULL,
  `name` varchar(50) default NULL,
  `houseno` int(3) default NULL,
  `city` varchar(15) default NULL,
  `zipcode` int(6) default NULL,
  `phoneno` int(10) default NULL,
  `dateofbirth` date default NULL,
  PRIMARY KEY  (`ssn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `researcher` (
  `s_ssn` int(9) NOT NULL,
  `t_ssn` int(9) default NULL,
  `areaofresearch` varchar(50) default NULL,
  `noofpapers` int(3) default NULL,
  PRIMARY KEY  (`s_ssn`),
  UNIQUE KEY `t_ssn` (`t_ssn`),
  CONSTRAINT `researcher_ibfk_1` FOREIGN KEY (`s_ssn`) REFERENCES `employee` (`ssn`),
  CONSTRAINT `researcher_ibfk_2` FOREIGN KEY (`t_ssn`) REFERENCES `teachingstaff` (`essn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `student` (
  `ssn` int(9) NOT NULL,
  `semester` int(1) default NULL,
  `dept_name` varchar(15) default NULL,
  `course_name` varchar(20) default NULL,
  PRIMARY KEY  (`ssn`),
  KEY `dept_name` (`dept_name`),
  KEY `course_name` (`course_name`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`ssn`) REFERENCES `person` (`ssn`),
  CONSTRAINT `student_ibfk_2` FOREIGN KEY (`dept_name`) REFERENCES `department` (`name`),
  CONSTRAINT `student_ibfk_3` FOREIGN KEY (`course_name`) REFERENCES `course` (`course_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `teachingstaff` (
  `essn` int(9) NOT NULL,
  `qualification` varchar(10) default NULL,
  `specialization` varchar(10) default NULL,
  `dept_name` varchar(15) default NULL,
  `cname` varchar(20) default NULL,
  PRIMARY KEY  (`essn`),
  KEY `dept_name` (`dept_name`),
  KEY `cname` (`cname`),
  CONSTRAINT `teachingstaff_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`name`),
  CONSTRAINT `teachingstaff_ibfk_2` FOREIGN KEY (`cname`) REFERENCES `course` (`course_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;