main.sql

CREATE DATABASE mobisecure;
USE mobisecure;
CREATE TABLE government (
CaseID int(11) NOT NULL,
FName varchar(25) NOT NULL,
LName varchar(25) NOT NULL,
IDNO 	VARCHAR(11) NOT NULL,
IDStatus varchar(25) NOT NULL,
DateReported date NOT NULL,
AbstractNo varchar(25) NOT NULL,
Email varchar(40) NOT NULL,
Location varchar(40) NOT NULL);
INSERT INTO government (CaseID, FName, LName, IDNO, IDStatus, DateReported, AbstractNo, Email, Location)
VALUES
(1, 'Leila', 'Kibagendi', 33567890, 'LOST', '2019-01-04', 'JUJA/KP-0097', 'leilak16@gmail.com', 'Juja'),
(2, 'Keith', 'Muthama', 24567845, 'VALID', '2019-02-04', 'NULL', 'muthamak@yahoo.com', ''),
(3, 'Peter', 'Oluoch', 10198763, 'REPLACED', '2018-12-07', 'KSM/KP-4065', 'opeter@gmail.com', 'Kisumu'),
(4, 'Amara', 'Kemunto', 12356874, 'VALID', '2019-01-02', 'NULL', 'akemunto@gmail.com', ''),
(5, 'Billy', 'Kiprop', 56789012, 'LOST', '2019-02-04', 'JUJA/KP-0187', 'billyk@yahoo.com', 'Juja'),
(6, 'Mark', 'Too', 24568489, 'REPLACED', '2017-06-20', 'THIKA/KP-0208', 'marktoo@gmail.com', 'Thika'),
(7, 'Karen', 'Wangari', 34565845, 'LOST', '2018-11-10', 'KIAMBU/KP-6785', 'karenw@gmail.com', 'Kiambu'),
(8, 'Rahma', 'Ishmael', 32567845, 'REPLACED', '2017-12-21', 'STAREHE/KP-2345', 'rahma56@gmail.com', 'Starehe'),
(9, 'Alice', 'Mammito', 31267848, 'VALID', '2019-01-07', 'NULL', 'alicem@gmail.com', ''),
(10, 'Sandra', 'Amayo', 24567765, 'REPLACED', '2016-10-10', 'BUNGOMA/KP-3456', 'sandraamayo@yahoo.com', 'Bungoma');

CREATE TABLE BITVH(
SerialNo int(11) NOT NULL,
FName varchar(25) NOT NULL,
LName varchar(25) NOT NULL,
IDNO int(11) NOT NULL,
IDStatus varchar(25) NOT NULL,
MobileNo int(11) NOT NULL,
Email varchar(40) NOT NULL);

INSERT INTO BITVH (SerialNo, FName, LName, IDNO, IDStatus ,MobileNo, Email)
VALUES
(1, 'Leila', 'Kibagendi', 33567890, 'LOST', 923456789, 'leilak16@gmail.com9'),
(2, 'Keith', 'Muthama', 24567845, 'VALID', 954765432, 'muthamak@yahoo.com'),
(3, 'Peter', 'Oluoch', 10198763, 'REPLACED', 965443221, 'opeter@gmail.com'),
(4, 'Amara', 'Kemunto', 12356874, 'VALID', '965445643', 'akemunto@gmail.com'),
(5, 'Billy', 'Kiprop', 56789012, 'LOST', '956433214', 'billyk@yahoo.com'),
(6, 'Mark', 'Too', 245678489, 'REPLACED', '976543221', 'marktoo@gmail.com'),
(7, 'Karen', 'Wangari', 34565845, 'LOST', '995443765', 'karenw@gmail.com'),
(8, 'Rahma', 'Ishmael', 32567845,'REPLACED','945443654','rahma56@gmail.com'),
(9, 'Alice', 'Mammito', 31267848, 'VALID', '954443987', 'alicem@gmail.com'),
(10, 'Sandra', 'Amayo', 24567765,'LOST','935443673','sandraamayo@yahoo.com');
ALTER TABLE BITVH
MODIFY IDNO VARCHAR(25);
ALTER TABLE BITVH
MODIFY MobileNo VARCHAR(15);
CREATE TABLE mobi_subscriber (
  CustID int(11) NOT NULL,
  FName varchar(25) NOT NULL,
  LName varchar(25) NOT NULL,
  IDNO VARCHAR(25) NOT NULL,
  Email varchar(40) NOT NULL);
INSERT INTO mobi_subscriber(CustID, FName, LName, IDNO, Email)
VALUES
(1, 'Leila', 'Kibagendi', 33567890, 'leilak16@gmail.com'),
(2, 'Keith', 'Muthama', 24567855, 'muthamak@yahoo.com'),
(3, 'Peter', 'Oluoch', 10198763, 'opeter@gmail.com'),
(4, 'Amara', 'Kemunto', 12356874, 'akemunto@gmail.com'),
(5, 'Billy', 'Kiprop', 56789012,  'billyk@yahoo.com'),
(6, 'Mark', 'Too', 245678489,  'marktoo@gmail.com'),
(7, 'Karen', 'Wangari', 34565845, 'karenw@gmail.com'),
(8, 'Rahma', 'Ishmael', 32567845,'rahma56@gmail.com'),
(9, 'Alice', 'Mammito', 31267848, 'alicem@gmail.com'),
(10, 'Sandra', 'Amayo', 24567765,'sandraamayo@yahoo.com');

ALTER TABLE government
ADD CONSTRAINT governmentPK_CaseID
PRIMARY KEY(CaseID);
ALTER TABLE BITVH
ADD CONSTRAINT BITVHPK_SerialNo
PRIMARY KEY(SerialNo);
ALTER TABLE mobi_subscriber
ADD CONSTRAINT mobi_subscriberPK_CustID
PRIMARY KEY(CustID);
ALTER TABLE mobi_subscriber
ADD FOREIGN KEY (IDNO)
REFERENCES BITVH(SerialNo);