davidjonkariz / grpprojo Goto Github PK

-- Host: 127.0.0.1 -- Generation Time: Sep 25, 2017 at 11:03 AM -- Server version: 10.1.19-MariaDB -- PHP Version: 5.6.28

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /; /!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /; /!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /; /!40101 SET NAMES utf8mb4 */;

CREATE TABLE cases ( OB_ID int(100) NOT NULL, Description varchar(1000) NOT NULL, Attachment longblob NOT NULL, ID_No varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE inspector ( Insp_ID varchar(100) NOT NULL, Name text NOT NULL, OB_ID int(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE officer ( Officer_No varchar(100) NOT NULL, Name text NOT NULL, On_duty tinyint(1) NOT NULL, Insp_ID varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE reporter ( ID_No varchar(100) NOT NULL, Name text NOT NULL, Location varchar(100) NOT NULL, E-mail varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE cases ADD PRIMARY KEY (OB_ID), ADD KEY ID_No (ID_No);

ALTER TABLE inspector ADD PRIMARY KEY (Insp_ID), ADD KEY OB_ID (OB_ID);

ALTER TABLE officer ADD PRIMARY KEY (Officer_No), ADD UNIQUE KEY Insp_ID (Insp_ID);

ALTER TABLE reporter ADD PRIMARY KEY (ID_No);

ALTER TABLE cases ADD CONSTRAINT cases_ibfk_1 FOREIGN KEY (ID_No) REFERENCES reporter (ID_No) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE inspector ADD CONSTRAINT inspector_ibfk_1 FOREIGN KEY (OB_ID) REFERENCES cases (OB_ID) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE officer ADD CONSTRAINT officer_ibfk_1 FOREIGN KEY (Insp_ID) REFERENCES inspector (Insp_ID) ON DELETE CASCADE ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /; /!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /; /!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;