DDL
CREATE TABLE Applicant (
id INT PRIMARY KEY AUTO_INCREMENT,
fname VARCHAR(50) NOT NULL, -- First name (JINA LA KWANZA)
mname VARCHAR(50), -- Middle name (JINA LA KATI)
lname VARCHAR(50) NOT NULL, -- Last name (JINA LA MWISHO)
nickname VARCHAR(50), -- (Jina Maarufu)
dob DATE NOT NULL, -- Date of birth (TAREHE YA KUZALIWA)
phone VARCHAR(15) NOT NULL, -- Phone number (NAMBA YA SIMU)
gender ENUM('M','F','Other') NOT NULL, -- (JINSI)
marital ENUM('Single','Married','Divorced','Widowed') NOT NULL, -- (NDOA)
occupation VARCHAR(50), -- (KAZI)
employment_status ENUM('Self-employed','Employed','Unemployed') --
(UMEJIAJIRI?)
);
CREATE TABLE Parent (
id INT PRIMARY KEY AUTO_INCREMENT,
names VARCHAR(100) NOT NULL, -- Combined names (JINA LA BABA/MAMA)
dob DATE, -- Birth date
nationality VARCHAR(50), -- (NCHI)
phone VARCHAR(15), -- Contact number
national_id VARCHAR(20), -- (NAMBA YA KITAMBULISHO)
passport VARCHAR(20), -- (PASIPOTI)
applicant_id INT NOT NULL, -- Links to applicant
relation ENUM('Father','Mother'), -- Distinguish parents
FOREIGN KEY (applicant_id) REFERENCES Applicant(id)
);
CREATE TABLE Identification_Records (
id INT PRIMARY KEY AUTO_INCREMENT,
applicant_id INT NOT NULL,
-- Education
primary_school VARCHAR(100), -- (SHULE YA MSINGI)
primary_district VARCHAR(50), -- (WILAYA YA MSINGI)
primary_year INT, -- (MWAKA WA MSINGI)
secondary_cert VARCHAR(30), -- (CHETI CHA SEKONDARI)
advanced_cert VARCHAR(30), -- (CHETI CHA SEKONDARI YA JUU)
-- Government IDs
voter_id VARCHAR(30), -- (NAMBA YA KURA)
ssf_no VARCHAR(30), -- (MFUKO WA JAMII)
passport VARCHAR(30),
driver_license VARCHAR(30),
tax_id VARCHAR(30), -- (TIN)
-- Parent IDs
father_nin VARCHAR(30), -- (BABA NIDA)
mother_nin VARCHAR(30), -- (MAMA NIDA)
-- Biometrics
profile_pic_path VARCHAR(255), -- Stores file path
-- Nationality
nationality_type ENUM('By Birth','By Descent','Naturalization'),
FOREIGN KEY (applicant_id) REFERENCES Applicant(id)
);
CREATE TABLE Identification_Records (
id INT PRIMARY KEY AUTO_INCREMENT,
applicant_id INT NOT NULL,
-- Education
primary_school VARCHAR(100), -- (SHULE YA MSINGI)
primary_district VARCHAR(50), -- (WILAYA YA MSINGI)
primary_year INT, -- (MWAKA WA MSINGI)
secondary_cert VARCHAR(30), -- (CHETI CHA SEKONDARI)
advanced_cert VARCHAR(30), -- (CHETI CHA SEKONDARI YA JUU)
-- Government IDs
voter_id VARCHAR(30), -- (NAMBA YA KURA)
ssf_no VARCHAR(30), -- (MFUKO WA JAMII)
passport VARCHAR(30),
driver_license VARCHAR(30),
tax_id VARCHAR(30), -- (TIN)
-- Parent IDs
father_nin VARCHAR(30), -- (BABA NIDA)
mother_nin VARCHAR(30), -- (MAMA NIDA)
-- Biometrics
profile_pic_path VARCHAR(255), -- Stores file path
-- Nationality
nationality_type ENUM('By Birth','By Descent','Naturalization'),
FOREIGN KEY (applicant_id) REFERENCES Applicant(id)
);
CREATE TABLE Place_of_Birth (
id INT PRIMARY KEY AUTO_INCREMENT,
identification_id INT NOT NULL, -- Links to Identification_Records
country VARCHAR(50) NOT NULL, -- (NCHI)
region VARCHAR(50), -- (MKOA)
district VARCHAR(50), -- (WILAYA)
ward VARCHAR(50), -- (KATA/WADI)
FOREIGN KEY (identification_id) REFERENCES Identification_Records(id)
);
CREATE TABLE Place_of_Birth (
id INT PRIMARY KEY AUTO_INCREMENT,
identification_id INT NOT NULL, -- Links to Identification_Records
country VARCHAR(50) NOT NULL, -- (NCHI)
region VARCHAR(50), -- (MKOA)
district VARCHAR(50), -- (WILAYA)
ward VARCHAR(50), -- (KATA/WADI)
FOREIGN KEY (identification_id) REFERENCES Identification_Records(id)
);
CREATE TABLE Application_Center (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL, -- (JINA LA KITUO)
number VARCHAR(20), -- (NAMBA YA KITUO)
region VARCHAR(50) NOT NULL, -- (MKOA)
district VARCHAR(50) NOT NULL, -- (WILAYA)
ward VARCHAR(50) NOT NULL -- (KATA/WADI)
);
CREATE TABLE Application (
id INT PRIMARY KEY AUTO_INCREMENT,
date DATE NOT NULL, -- (TAREHE YA MAOMBI)
center_id INT NOT NULL, -- Processing center
officer_id INT NOT NULL, -- Handling officer
applicant_id INT NOT NULL, -- Applicant
status ENUM('Pending','Approved','Rejected','Processing'),
das_name VARCHAR(100), -- District Admin Secretary
weo_name VARCHAR(100), -- Ward Executive Officer
FOREIGN KEY (center_id) REFERENCES Application_Center(id),
FOREIGN KEY (officer_id) REFERENCES Officer(id),
FOREIGN KEY (applicant_id) REFERENCES Applicant(id)
);
DML
INSERT INTO Application_Center (name, number, region, district, ward)
VALUES
('NIDA City Center', 'NCC001', 'Dar es Salaam', 'Ilala', 'Gerezani'),
('Arusha Regional Office', 'ARO002', 'Arusha', 'Arusha City', 'Themi'),
('Mwanza Main Office', 'MMO003', 'Mwanza', 'Ilemela', 'Nyamagana');
INSERT INTO Officer (name, position, center_id) VALUES
('John Mwambene', 'Registration Officer', 1),
('Sarah Chalamila', 'Verification Officer', 1),
('Michael Kavishe', 'Senior Officer', 2),
('Grace Nyanda', 'Data Entry Clerk', 2),
('Robert Mwakasege', 'Manager', 3),
('Elizabeth Mboya', 'Supervisor', 3);
INSERT INTO Applicant (fname, mname, lname, nickname, dob, phone,
gender, marital, occupation, employment_status) VALUES
-- First 5 applicants
('Juma', 'Hassan', 'Mwinyi', 'JJ', '1990-05-15', '+255712345678', 'M',
'Married', 'Teacher', 'Employed'),
('Asha', 'Salum', 'Rajab', 'Ashu', '1985-08-22', '+255713456789', 'F',
'Single', 'Nurse', 'Employed'),
('Baraka', NULL, 'Kipanga', 'Bara', '1995-11-30', '+255714567890', 'M',
'Single', 'Farmer', 'Self-employed'),
('Neema', 'John', 'Mrema', 'Nemo', '1988-03-10', '+255715678901', 'F',
'Divorced', 'Businesswoman', 'Self-employed'),
('Rajabu', 'Athumani', 'Kombo', 'Raja', '1977-07-25', '+255716789012', 'M',
'Married', 'Driver', 'Employed'),
-- Next 15 applicants
('Zainab', 'Omar', 'Ali', 'Zee', '1992-09-18', '+255717890123', 'F', 'Single',
'Student', 'Unemployed'),
('Daudi', 'Michael', 'Mwakasege', 'Dady', '1980-12-05', '+255718901234',
'M', 'Married', 'Engineer', 'Employed'),
('Fatuma', 'Juma', 'Hemed', 'Fatty', '1993-04-22', '+255719012345', 'F',
'Single', 'Secretary', 'Employed'),
('Yusuf', 'Ramadhani', 'Simba', 'Yusu', '1987-01-30', '+255720123456', 'M',
'Married', 'Shopkeeper', 'Self-employed'),
('Halima', NULL, 'Mohamed', 'Hally', '1998-06-15', '+255721234567', 'F',
'Single', 'Tailor', 'Self-employed'),
('Samwel', 'Edward', 'Nyoni', 'Sammy', '1991-07-12', '+255722345678',
'M', 'Single', 'Accountant', 'Employed'),
('Rehema', 'James', 'Mkamba', 'Rehy', '1984-10-08', '+255723456789', 'F',
'Married', 'Doctor', 'Employed'),
('Mzee', 'Hamisi', 'Kondo', 'Mzee', '1970-02-28', '+255724567890', 'M',
'Widowed', 'Retired', 'Unemployed'),
('Pendo', 'Daniel', 'Shayo', 'Penny', '1996-05-20', '+255725678901', 'F',
'Single', 'Waitress', 'Employed'),
('Amani', 'Robert', 'Kisena', 'Amu', '1989-08-03', '+255726789012', 'M',
'Single', 'Electrician', 'Self-employed'),
('Salma', 'Idd', 'Kibwana', 'Sal', '1994-11-25', '+255727890123', 'F',
'Married', 'Teacher', 'Employed'),
('Omar', 'Rajab', 'Mwinyimkuu', 'Omy', '1982-04-17', '+255728901234',
'M', 'Married', 'Businessman', 'Self-employed'),
('Tatu', 'Juma', 'Mwantimwa', 'Taty', '1997-09-09', '+255729012345', 'F',
'Single', 'Student', 'Unemployed'),
('Japhet', 'Simon', 'Mwasaga', 'Japh', '1986-12-14', '+255730123456', 'M',
'Single', 'Mechanic', 'Self-employed'),
('Mariam', 'Ali', 'Khamis', 'Mari', '1999-01-05', '+255731234567', 'F',
'Single', 'Hairdresser', 'Self-employed');
-- Parents for first 5 applicants
INSERT INTO Parent (names, dob, nationality, phone, national_id, passport,
applicant_id, relation) VALUES
('Hassan Mwinyi', '1965-03-20', 'Tanzanian', '+255710111222',
'123456789', NULL, 1, 'Father'),
('Zainab Mwinyi', '1970-07-12', 'Tanzanian', '+255710222333',
'987654321', NULL, 1, 'Mother'),
('Salum Rajab', '1960-01-15', 'Tanzanian', '+255710333444', '456123789',
NULL, 2, 'Father'),
('Mwanamisi Rajab', '1963-09-28', 'Tanzanian', '+255710444555',
'789456123', NULL, 2, 'Mother'),
('Mzee Kipanga', '1955-11-10', 'Tanzanian', NULL, '321654987', NULL, 3,
'Father'),
('Shida Kipanga', '1958-04-05', 'Tanzanian', '+255710555666',
'654987321', NULL, 3, 'Mother'),
('John Mrema', '1950-08-22', 'Tanzanian', '+255710666777', '147258369',
NULL, 4, 'Father'),
('Grace Mrema', '1953-12-30', 'Tanzanian', '+255710777888',
'258369147', NULL, 4, 'Mother'),
('Athumani Kombo', '1948-06-18', 'Tanzanian', '+255710888999',
'369258147', NULL, 5, 'Father'),
('Mama Kombo', '1952-02-14', 'Tanzanian', '+255710999000',
'159357486', NULL, 5, 'Mother');
-- Insert parents for remaining applicants similarly...
-- Addresses for first 5 applicants
INSERT INTO Address (applicant_id, type, region, district, ward, street,
postal_code, house_no) VALUES
(1, 'Permanent', 'Dar es Salaam', 'Ilala', 'Gerezani', 'Sokoine Drive',
'11101', '123'),
(1, 'Current', 'Dar es Salaam', 'Kinondoni', 'Mikocheni', 'Ali Hassan Mwinyi
Rd', '14112', '456'),
(2, 'Permanent', 'Arusha', 'Arusha City', 'Themi', 'Nyerere Road', '23101',
'78'),
(2, 'Current', 'Arusha', 'Arusha City', 'Sekei', 'Sokoine Road', '23102',
'34B'),
(3, 'Permanent', 'Mwanza', 'Ilemela', 'Nyamagana', 'Kenyatta Road',
'33101', '12'),
(3, 'Current', 'Mwanza', 'Nyamagana', 'Igogo', 'Mirabo Street', '33102', '9'),
(4, 'Permanent', 'Dodoma', 'Dodoma Urban', 'Changombe', 'Mazengo
Street', '41101', '56'),
(4, 'Current', 'Dodoma', 'Dodoma Urban', 'Nala', 'Nyerere Road', '41102',
'22'),
(5, 'Permanent', 'Tanga', 'Tanga City', 'Central', 'Usambara Street', '21101',
'3'),
(5, 'Current', 'Tanga', 'Tanga City', 'Ngamiani', 'Hospital Road', '21102',
'15A');
-- Insert addresses for remaining applicants similarly...
INSERT INTO Identification_Records (applicant_id, primary_school,
primary_district, primary_year, secondary_cert, advanced_cert, voter_id,
ssf_no, passport, driver_license, tax_id, father_nin, mother_nin,
nationality_type) VALUES
(1, 'Gerezani Primary', 'Ilala', 2002, 'S01234', 'A05678', 'V12345',
'SSF789', 'P1234567', 'DL8910', 'TIN111', '123456789', '987654321', 'By
Birth'),
(2, 'Themi Primary', 'Arusha', 1997, 'S02345', 'A06789', 'V23456',
'SSF890', 'P2345678', 'DL91011', 'TIN222', '456123789', '789456123', 'By
Birth'),
(3, 'Nyamagana Primary', 'Ilemela', 2007, 'S03456', NULL, 'V34567', NULL,
NULL, NULL, NULL, '321654987', '654987321', 'By Birth'),
(4, 'Changombe Primary', 'Dodoma', 1999, 'S04567', 'A07890', 'V45678',
'SSF901', NULL, 'DL10112', 'TIN333', '147258369', '258369147', 'By
Descent'),
(5, 'Central Primary', 'Tanga', 1989, 'S05678', NULL, 'V56789', NULL, NULL,
'DL11213', NULL, '369258147', '159357486', 'By Birth');
-- Insert records for remaining applicants similarly...
INSERT INTO Place_of_Birth (identification_id, country, region, district,
ward) VALUES
(1, 'Tanzania', 'Dar es Salaam', 'Temeke', 'Kurasini'),
(2, 'Tanzania', 'Arusha', 'Arusha', 'Themi'),
(3, 'Tanzania', 'Mwanza', 'Ilemela', 'Nyamagana'),
(4, 'Tanzania', 'Dodoma', 'Dodoma Rural', 'Chamwino'),
(5, 'Tanzania', 'Tanga', 'Tanga', 'Central');
-- Insert places for remaining applicants similarly...
INSERT INTO Application (date, center_id, officer_id, applicant_id, status,
das_name, weo_name) VALUES
('2023-01-15', 1, 1, 1, 'Approved', 'John Mbowe', 'Sarah Kondo'),
('2023-01-16', 1, 2, 2, 'Approved', 'John Mbowe', 'Sarah Kondo'),
('2023-01-17', 2, 3, 3, 'Pending', 'Michael Mwita', 'James Muro'),
('2023-01-18', 2, 4, 4, 'Rejected', 'Michael Mwita', 'James Muro'),
('2023-01-19', 3, 5, 5, 'Approved', 'Grace Nyanda', 'Robert Kingi'),
('2023-01-20', 1, 1, 6, 'Processing', 'John Mbowe', 'Sarah Kondo'),
('2023-01-21', 1, 2, 7, 'Approved', 'John Mbowe', 'Sarah Kondo'),
('2023-01-22', 2, 3, 8, 'Approved', 'Michael Mwita', 'James Muro'),
('2023-01-23', 2, 4, 9, 'Pending', 'Michael Mwita', 'James Muro'),
('2023-01-24', 3, 5, 10, 'Rejected', 'Grace Nyanda', 'Robert Kingi'),
('2023-01-25', 1, 1, 11, 'Approved', 'John Mbowe', 'Sarah Kondo'),
('2023-01-26', 1, 2, 12, 'Processing', 'John Mbowe', 'Sarah Kondo'),
('2023-01-27', 2, 3, 13, 'Approved', 'Michael Mwita', 'James Muro'),
('2023-01-28', 2, 4, 14, 'Approved', 'Michael Mwita', 'James Muro'),
('2023-01-29', 3, 5, 15, 'Pending', 'Grace Nyanda', 'Robert Kingi'),
('2023-01-30', 1, 1, 16, 'Rejected', 'John Mbowe', 'Sarah Kondo'),
('2023-01-31', 1, 2, 17, 'Approved', 'John Mbowe', 'Sarah Kondo'),
('2023-02-01', 2, 3, 18, 'Approved', 'Michael Mwita', 'James Muro'),
('2023-02-02', 2, 4, 19, 'Processing', 'Michael Mwita', 'James Muro'),
('2023-02-03', 3, 5, 20, 'Approved', 'Grace Nyanda', 'Robert Kingi');
-- Parents for applicants 6-10
INSERT INTO Parent (names, dob, nationality, phone, national_id, passport,
applicant_id, relation) VALUES
('Omar Ali', '1968-09-15', 'Tanzanian', '+255710111213', '753159486',
NULL, 6, 'Father'),
('Fatma Ali', '1972-04-22', 'Tanzanian', '+255710212324', '486753159',
NULL, 6, 'Mother'),
('Michael Mwakasege', '1955-11-30', 'Tanzanian', '+255710313435',
'159486753', NULL, 7, 'Father'),
('Rose Mwakasege', '1959-07-18', 'Tanzanian', '+255710414546',
'753486159', NULL, 7, 'Mother'),
('Juma Hemed', '1962-02-14', 'Tanzanian', '+255710515657', '357159486',
NULL, 8, 'Father'),
('Amina Hemed', '1965-10-05', 'Tanzanian', '+255710616768',
'486357159', NULL, 8, 'Mother'),
('Ramadhani Simba', '1958-05-20', 'Tanzanian', '+255710717879',
'159357486', NULL, 9, 'Father'),
('Zuhura Simba', '1961-12-25', 'Tanzanian', '+255710818990',
'486159357', NULL, 9, 'Mother'),
('Mohamed Khamis', '1964-08-10', 'Tanzanian', NULL, '753486357', NULL,
10, 'Father'),
('Saada Khamis', '1967-03-05', 'Tanzanian', '+255710919101',
'357753486', NULL, 10, 'Mother');
-- Parents for applicants 11-15
INSERT INTO Parent (names, dob, nationality, phone, national_id, passport,
applicant_id, relation) VALUES
('Edward Nyoni', '1960-01-12', 'Tanzanian', '+255710020111',
'159753486', NULL, 11, 'Father'),
('Grace Nyoni', '1963-06-28', 'Tanzanian', '+255710121222', '486159753',
NULL, 11, 'Mother'),
('James Mkamba', '1957-09-15', 'Tanzanian', '+255710222333',
'753159486', NULL, 12, 'Father'),
('Sarah Mkamba', '1960-04-20', 'Tanzanian', '+255710323444',
'486753159', NULL, 12, 'Mother'),
('Hamisi Kondo', '1945-12-10', 'Tanzanian', NULL, '159486753', NULL, 13,
'Father'),
('Mariam Kondo', '1950-07-25', 'Tanzanian', '+255710424555',
'753486159', NULL, 13, 'Mother'),
('Daniel Shayo', '1963-03-18', 'Tanzanian', '+255710525666', '357159486',
NULL, 14, 'Father'),
('Ester Shayo', '1966-11-30', 'Tanzanian', '+255710626777', '486357159',
NULL, 14, 'Mother'),
('Robert Kisena', '1959-02-14', 'Tanzanian', '+255710727888',
'159357486', NULL, 15, 'Father'),
('Juliana Kisena', '1962-09-05', 'Tanzanian', '+255710828999',
'486159357', NULL, 15, 'Mother');
-- Parents for applicants 16-20
INSERT INTO Parent (names, dob, nationality, phone, national_id, passport,
applicant_id, relation) VALUES
('Idd Kibwana', '1961-05-22', 'Tanzanian', '+255710929000', '753486357',
NULL, 16, 'Father'),
('Asha Kibwana', '1964-10-15', 'Tanzanian', '+255710030111',
'357753486', NULL, 16, 'Mother'),
('Rajab Mwinyimkuu', '1956-08-30', 'Tanzanian', '+255710131222',
'159753486', NULL, 17, 'Father'),
('Mwanahawa Mwinyimkuu', '1959-01-25', 'Tanzanian', '+255710232333',
'486159753', NULL, 17, 'Mother'),
('Juma Mwantimwa', '1967-04-12', 'Tanzanian', '+255710333444',
'753159486', NULL, 18, 'Father'),
('Neema Mwantimwa', '1970-07-18', 'Tanzanian', '+255710434555',
'486753159', NULL, 18, 'Mother'),
('Simon Mwasaga', '1960-11-05', 'Tanzanian', '+255710535666',
'159486753', NULL, 19, 'Father'),
('Rose Mwasaga', '1963-06-20', 'Tanzanian', '+255710636777',
'753486159', NULL, 19, 'Mother'),
('Ali Khamis', '1965-09-15', 'Tanzanian', '+255710737888', '357159486',
NULL, 20, 'Father'),
('Fatma Khamis', '1968-12-28', 'Tanzanian', '+255710838999',
'486357159', NULL, 20, 'Mother');
-- Addresses for applicants 6-10
INSERT INTO Address (applicant_id, type, region, district, ward, street,
postal_code, house_no) VALUES
(6, 'Permanent', 'Dar es Salaam', 'Temeke', 'Kurasini', 'Kilwa Road',
'11102', '78'),
(6, 'Current', 'Dar es Salaam', 'Kinondoni', 'Kawe', 'Bibi Titi Mohamed',
'14113', '22C'),
(7, 'Permanent', 'Arusha', 'Arusha City', 'Sekei', 'Old Moshi Road', '23103',
'15'),
(7, 'Current', 'Arusha', 'Arusha City', 'Themi', 'Nyerere Road', '23104', '9A'),
(8, 'Permanent', 'Mwanza', 'Nyamagana', 'Igogo', 'Kenyatta Road', '33103',
'34'),
(8, 'Current', 'Mwanza', 'Ilemela', 'Nyamagana', 'Mirabo Street', '33104',
'7B'),
(9, 'Permanent', 'Dodoma', 'Dodoma Urban', 'Nala', 'Mazengo Street',
'41103', '12'),
(9, 'Current', 'Dodoma', 'Dodoma Urban', 'Changombe', 'Nyerere Road',
'41104', '45'),
(10, 'Permanent', 'Tanga', 'Tanga City', 'Ngamiani', 'Usambara Street',
'21103', '8'),
(10, 'Current', 'Tanga', 'Tanga City', 'Central', 'Hospital Road', '21104',
'17C');
-- Addresses for applicants 11-15
INSERT INTO Address (applicant_id, type, region, district, ward, street,
postal_code, house_no) VALUES
(11, 'Permanent', 'Mbeya', 'Mbeya City', 'Isanga', 'Nkrumah Street',
'53101', '56'),
(11, 'Current', 'Mbeya', 'Mbeya City', 'Ilemi', 'Karume Road', '53102', '23'),
(12, 'Permanent', 'Morogoro', 'Morogoro Urban', 'Mazimbu', 'Kihonda Road',
'67101', '89'),
(12, 'Current', 'Morogoro', 'Morogoro Urban', 'Morogoro', 'Boma Road',
'67102', '11A'),
(13, 'Permanent', 'Mtwara', 'Mtwara Urban', 'Chuno', 'Lumumba Street',
'63101', '45'),
(13, 'Current', 'Mtwara', 'Mtwara Urban', 'Mikindani', 'Ocean Road',
'63102', '6B'),
(14, 'Permanent', 'Kagera', 'Bukoba Urban', 'Kashai', 'Kagera Road',
'35101', '78'),
(14, 'Current', 'Kagera', 'Bukoba Urban', 'Bukoba', 'Hospital Street',
'35102', '14'),
(15, 'Permanent', 'Kilimanjaro', 'Moshi Urban', 'Rau', 'Old Moshi Road',
'25101', '32'),
(15, 'Current', 'Kilimanjaro', 'Moshi Urban', 'Moshi', 'Mwenge Street',
'25102', '5C');
-- Addresses for applicants 16-20
INSERT INTO Address (applicant_id, type, region, district, ward, street,
postal_code, house_no) VALUES
(16, 'Permanent', 'Pwani', 'Kibaha', 'Kibaha', 'Bagamoyo Road', '61101',
'67'),
(16, 'Current', 'Pwani', 'Kibaha', 'Maili Moja', 'Chalinze Street', '61102', '9'),
(17, 'Permanent', 'Singida', 'Singida Urban', 'Mungumaji', 'Dodoma Road',
'43101', '43'),
(17, 'Current', 'Singida', 'Singida Urban', 'Singida', 'Market Street', '43102',
'12B'),
(18, 'Permanent', 'Shinyanga', 'Shinyanga Urban', 'Ndembezi', 'Mwanza
Road', '37101', '21'),
(18, 'Current', 'Shinyanga', 'Shinyanga Urban', 'Shinyanga', 'Posta Street',
'37102', '8A'),
(19, 'Permanent', 'Lindi', 'Lindi Urban', 'Mtanda', 'Kilwa Road', '65101',
'54'),
(19, 'Current', 'Lindi', 'Lindi Urban', 'Lindi', 'Ocean View Street', '65102',
'3'),
(20, 'Permanent', 'Ruvuma', 'Songea Urban', 'Mfaranyaki', 'Njombe Road',
'57101', '76'),
(20, 'Current', 'Ruvuma', 'Songea Urban', 'Songea', 'Market Street',
'57102', '11C');
-- Identification records for applicants 6-10
INSERT INTO Identification_Records (applicant_id, primary_school,
primary_district, primary_year, secondary_cert, advanced_cert, voter_id,
ssf_no, passport, driver_license, tax_id, father_nin, mother_nin,
nationality_type) VALUES
(6, 'Kurasini Primary', 'Temeke', 2008, 'S06789', NULL, 'V67890', NULL,
NULL, NULL, NULL, '753159486', '486753159', 'By Birth'),
(7, 'Sekei Primary', 'Arusha', 1995, 'S07890', 'A08901', 'V78901', 'SSF012',
'P3456789', 'DL21314', 'TIN444', '159486753', '753486159', 'By Birth'),
(8, 'Igogo Primary', 'Nyamagana', 2009, 'S08901', NULL, 'V89012', NULL,
NULL, NULL, NULL, '357159486', '486357159', 'By Birth'),
(9, 'Nala Primary', 'Dodoma', 2000, 'S09012', 'A09012', 'V90123', 'SSF123',
NULL, 'DL31415', 'TIN555', '159357486', '486159357', 'By Descent'),
(10, 'Ngamiani Primary', 'Tanga', 2010, 'S00123', NULL, 'V01234', NULL,
NULL, NULL, NULL, '753486357', '357753486', 'By Birth');
-- Identification records for applicants 11-15
INSERT INTO Identification_Records (applicant_id, primary_school,
primary_district, primary_year, secondary_cert, advanced_cert, voter_id,
ssf_no, passport, driver_license, tax_id, father_nin, mother_nin,
nationality_type) VALUES
(11, 'Isanga Primary', 'Mbeya', 2003, 'S01234', 'A01234', 'V12345',
'SSF234', NULL, 'DL41516', 'TIN666', '159753486', '486159753', 'By
Birth'),
(12, 'Mazimbu Primary', 'Morogoro', 1998, 'S02345', 'A02345', 'V23456',
'SSF345', 'P4567890', 'DL51617', 'TIN777', '753159486', '486753159', 'By
Birth'),
(13, 'Chuno Primary', 'Mtwara', 2007, 'S03456', NULL, 'V34567', NULL,
NULL, NULL, NULL, '159486753', '753486159', 'By Birth'),
(14, 'Kashai Primary', 'Kagera', 2001, 'S04567', 'A04567', 'V45678',
'SSF456', NULL, 'DL61718', 'TIN888', '357159486', '486357159', 'By
Descent'),
(15, 'Rau Primary', 'Moshi', 2011, 'S05678', NULL, 'V56789', NULL, NULL,
NULL, NULL, '159357486', '486159357', 'By Birth');
-- Identification records for applicants 16-20
INSERT INTO Identification_Records (applicant_id, primary_school,
primary_district, primary_year, secondary_cert, advanced_cert, voter_id,
ssf_no, passport, driver_license, tax_id, father_nin, mother_nin,
nationality_type) VALUES
(16, 'Kibaha Primary', 'Kibaha', 2004, 'S06789', 'A06789', 'V67890',
'SSF567', 'P5678901', 'DL71819', 'TIN999', '753486357', '357753486', 'By
Birth'),
(17, 'Mungumaji Primary', 'Singida', 1999, 'S07890', NULL, 'V78901', NULL,
NULL, 'DL81920', NULL, '159753486', '486159753', 'By Birth'),
(18, 'Ndembezi Primary', 'Shinyanga', 2008, 'S08901', 'A08901', 'V89012',
'SSF678', NULL, 'DL92021', 'TIN101', '753159486', '486753159', 'By
Birth'),
(19, 'Mtanda Primary', 'Lindi', 2002, 'S09012', NULL, 'V90123', NULL, NULL,
NULL, NULL, '159486753', '753486159', 'By Birth'),
(20, 'Mfaranyaki Primary', 'Songea', 2012, 'S00123', 'A00123', 'V01234',
'SSF789', 'P6789012', 'DL02122', 'TIN202', '357159486', '486357159', 'By
Descent');
-- Places of birth for applicants 6-10
INSERT INTO Place_of_Birth (identification_id, country, region, district,
ward) VALUES
(6, 'Tanzania', 'Dar es Salaam', 'Temeke', 'Kurasini'),
(7, 'Tanzania', 'Arusha', 'Arusha', 'Sekei'),
(8, 'Tanzania', 'Mwanza', 'Nyamagana', 'Igogo'),
(9, 'Tanzania', 'Dodoma', 'Dodoma Urban', 'Nala'),
(10, 'Tanzania', 'Tanga', 'Tanga', 'Ngamiani');
-- Places of birth for applicants 11-15
INSERT INTO Place_of_Birth (identification_id, country, region, district,
ward) VALUES
(11, 'Tanzania', 'Mbeya', 'Mbeya City', 'Isanga'),
(12, 'Tanzania', 'Morogoro', 'Morogoro Urban', 'Mazimbu'),
(13, 'Tanzania', 'Mtwara', 'Mtwara Urban', 'Chuno'),
(14, 'Tanzania', 'Kagera', 'Bukoba Urban', 'Kashai'),
(15, 'Tanzania', 'Kilimanjaro', 'Moshi Urban', 'Rau');
-- Places of birth for applicants 16-20
INSERT INTO Place_of_Birth (identification_id, country, region, district,
ward) VALUES
(16, 'Tanzania', 'Pwani', 'Kibaha', 'Kibaha'),
(17, 'Tanzania', 'Singida', 'Singida Urban', 'Mungumaji'),
(18, 'Tanzania', 'Shinyanga', 'Shinyanga Urban', 'Ndembezi'),
(19, 'Tanzania', 'Lindi', 'Lindi Urban', 'Mtanda'),
(20, 'Tanzania', 'Ruvuma', 'Songea Urban', 'Mfaranyaki');
QUERIES
SELECT
a.fname, a.lname, a.phone,
ac.name AS center_name,
o.name AS officer_name,
app.date AS application_date
FROM Application app
JOIN Applicant a ON app.applicant_id = a.id
JOIN Application_Center ac ON app.center_id = ac.id
JOIN Officer o ON app.officer_id = o.id
WHERE app.status = 'Approved'
LIMIT 5;
-- Applications with applicant names and status
SELECT
a.fname, a.lname,
app.date AS application_date,
app.status,
center.name AS center_name
FROM Application app
JOIN Applicant a ON app.applicant_id = a.id
JOIN Application_Center center ON app.center_id = center.id
LIMIT 5;
-- Which officer works where?
SELECT
o.name AS officer_name,
o.position,
c.name AS center_name,
c.region
FROM Officer o
JOIN Application_Center c ON o.center_id = c.id
ORDER BY c.region;
-- How many pending apps per center?
SELECT
c.name AS center_name,
COUNT(*) AS pending_applications
FROM Application a
JOIN Application_Center c ON a.center_id = c.id
WHERE a.status = 'Pending'
GROUP BY c.name;
-- Who was born in Dar?
SELECT
a.fname, a.lname, a.dob,
pob.ward AS birth_ward
FROM Applicant a
JOIN Identification_Records id_rec ON a.id = id_rec.applicant_id
JOIN Place_of_Birth pob ON id_rec.id = pob.identification_id
WHERE pob.region = 'Dar es Salaam'
LIMIT 5;
