Jonathan Arya Priguna / LK12 BDT

CREATE TABLE departemen_1111015 (
  DNAME_1111015 VARCHAR(255),
  DNumber_1111015 INT(15),
  MGRSSN_1111015 VARCHAR(15) NOT NULL,
  MGR_TGL_1111015 DATE,
  PRIMARY KEY (DNumber_1111015)
);

CREATE TABLE EMPLOYEE_1111015 (
  SSN_1111015 INT(15) PRIMARY KEY,
  Nama_1111015 VARCHAR(255) NOT NULL,
  Alamat_E_1111015 VARCHAR(255) NOT NULL,
  Telepon_1111015 BIGINT NOT NULL,
  DNO_1111015 INT NOT NULL,
  SuperSSN_1111015 VARCHAR(10) NOT NULL,
  Gaji_1111015 DECIMAL(15, 2) NOT NULL,
  Jenis_K_1111015 VARCHAR(10) NOT NULL,
  FOREIGN KEY (DNO_1111015) REFERENCES departemen_1111015(DNumber_1111015)
);

CREATE TABLE dep_lokasi_1111015 (
  DNumber_1111015 INT(15),
  Alamat_D_1111015 VARCHAR(255) PRIMARY KEY,
  FOREIGN KEY (DNumber_1111015) REFERENCES departemen_1111015(DNumber_1111015)
);

CREATE TABLE keluarga_1111015 (
  ESSN_1111015 INT,
  Nama_Anggota_1111015 VARCHAR(255) NOT NULL PRIMARY KEY,
  Hubungan_1111015 VARCHAR(20) NOT NULL,
  Jenis_Kelamin_1111015 VARCHAR(5) NOT NULL,
  FOREIGN KEY (ESSN_1111015) REFERENCES EMPLOYEE_1111015(SSN_1111015)
);

CREATE TABLE project_1111015 (
  PNumber_1111015 INT PRIMARY KEY,
  PName_1111015 VARCHAR(255) NOT NULL,
  Lokasi_P_1111015 VARCHAR(255) NOT NULL,
  DNum_1111015 INT NOT NULL,
  FOREIGN KEY (DNum_1111015) REFERENCES departemen_1111015(DNumber_1111015)
);

CREATE TABLE bekerja_1111015 (
  ESSN_1111015 INT(15),
  PNO_1111015 INT(15),
  Jam_1111015 TIME,
  PRIMARY KEY (PNO_1111015),
  FOREIGN KEY (ESSN_1111015) REFERENCES EMPLOYEE_1111015(SSN_1111015),
  FOREIGN KEY (PNO_1111015) REFERENCES project_1111015(PNumber_1111015)
);

INSERT INTO departemen_1111015 (DNAME_1111015, DNumber_1111015, MGRSSN_1111015, MGR_TGL_1111015) VALUES ('HR', 1000, '049004511', '1999-07-24');
INSERT INTO departemen_1111015 (DNAME_1111015, DNumber_1111015, MGRSSN_1111015, MGR_TGL_1111015) VALUES ('Sales', 1001, '876734557', '1996-08-25');
INSERT INTO departemen_1111015 (DNAME_1111015, DNumber_1111015, MGRSSN_1111015, MGR_TGL_1111015) VALUES ('Finance', 1002, '3564784019', '1997-03-04');
INSERT INTO departemen_1111015 (DNAME_1111015, DNumber_1111015, MGRSSN_1111015, MGR_TGL_1111015) VALUES ('Security', 1003, '0013391544', '1980-01-10');
INSERT INTO departemen_1111015 (DNAME_1111015, DNumber_1111015, MGRSSN_1111015, MGR_TGL_1111015) VALUES ('IT', 1004, '091355667', '2001-09-20');

INSERT INTO EMPLOYEE_1111015 (SSN_1111015, Nama_1111015, Alamat_E_1111015, Telepon_1111015, DNO_1111015, SuperSSN_1111015, Gaji_1111015, Jenis_K_1111015) VALUES (874329015, 'Rafli', 'Vermont Square', 3232921397, 1000, 'Z047',5000000.00,'Man');
INSERT INTO EMPLOYEE_1111015 (SSN_1111015, Nama_1111015, Alamat_E_1111015, Telepon_1111015, DNO_1111015, SuperSSN_1111015, Gaji_1111015, Jenis_K_1111015) VALUES (571906832, 'Atha', 'Jl Moh Yamin', 0213101068, 1001, 'Z033',10000000.00,'Man');
INSERT INTO EMPLOYEE_1111015 (SSN_1111015, Nama_1111015, Alamat_E_1111015, Telepon_1111015, DNO_1111015, SuperSSN_1111015, Gaji_1111015, Jenis_K_1111015) VALUES (236487519, 'Adhimu', 'Threadneedle St', 442034615545, 1002, 'Z022',40000000.00,'Man');
INSERT INTO EMPLOYEE_1111015 (SSN_1111015, Nama_1111015, Alamat_E_1111015, Telepon_1111015, DNO_1111015, SuperSSN_1111015, Gaji_1111015, Jenis_K_1111015) VALUES (682951743, 'Xing', '3rd Ring', 861068450715, 1003, 'Z011',20000000.00,'Woman');
INSERT INTO EMPLOYEE_1111015 (SSN_1111015, Nama_1111015, Alamat_E_1111015, Telepon_1111015, DNO_1111015, SuperSSN_1111015, Gaji_1111015, Jenis_K_1111015) VALUES (398165724, 'Baboon', 'Bayfront Ave', 6566888868, 1004, 'Z047',750000000.00,'Man');

INSERT INTO dep_lokasi_1111015 (DNumber_1111015, Alamat_D_1111015) VALUES (1000, 'Slausen Ave');
INSERT INTO dep_lokasi_1111015 (DNumber_1111015, Alamat_D_1111015) VALUES (1001, 'Kipling Ave');
INSERT INTO dep_lokasi_1111015 (DNumber_1111015, Alamat_D_1111015) VALUES (1002, 'Threadneedle St');
INSERT INTO dep_lokasi_1111015 (DNumber_1111015, Alamat_D_1111015) VALUES (1003, 'Jalan Trunojoyo');
INSERT INTO dep_lokasi_1111015 (DNumber_1111015, Alamat_D_1111015) VALUES (1004, '12th Ring');

INSERT INTO keluarga_1111015 (ESSN_1111015, Nama_Anggota_1111015, Hubungan_1111015, Jenis_Kelamin_1111015) VALUES
  (874329015, 'Raras', 'Istri', 'Woman'),
  (571906832, 'Pratama', 'Anak', 'Man'),
  (236487519, 'Rizky', 'Anak', 'Man'),
  (682951743, 'Jonathan', 'Suami', 'Man'),
  (398165724, 'Arya', 'Ayah', 'Man');

INSERT INTO project_1111015 (PNumber_1111015, PName_1111015, Lokasi_P_1111015, DNum_1111015) VALUES (1001, 'HRM', 'Slausen Ave', 1000);
INSERT INTO project_1111015 (PNumber_1111015, PName_1111015, Lokasi_P_1111015, DNum_1111015) VALUES (1002, 'POS', 'Kipling Ave', 1001);
INSERT INTO project_1111015 (PNumber_1111015, PName_1111015, Lokasi_P_1111015, DNum_1111015) VALUES (1003, 'PNS', 'Threadneedle St', 1002);
INSERT INTO project_1111015 (PNumber_1111015, PName_1111015, Lokasi_P_1111015, DNum_1111015) VALUES (1004, 'SCS', 'Jalan Trunojoyo', 1003);
INSERT INTO project_1111015 (PNumber_1111015, PName_1111015, Lokasi_P_1111015, DNum_1111015) VALUES (1005, 'Networking', '12th Ring', 1004);

INSERT INTO bekerja_1111015 (ESSN_1111015, PNO_1111015, Jam_1111015) VALUES (874329015, 1001, '08:00:00');
INSERT INTO bekerja_1111015 (ESSN_1111015, PNO_1111015, Jam_1111015) VALUES (571906832, 1002, '09:00:00');
INSERT INTO bekerja_1111015 (ESSN_1111015, PNO_1111015, Jam_1111015) VALUES (236487519, 1003, '10:00:00');
INSERT INTO bekerja_1111015 (ESSN_1111015, PNO_1111015, Jam_1111015) VALUES (682951743, 1004, '11:00:00');
INSERT INTO bekerja_1111015 (ESSN_1111015, PNO_1111015, Jam_1111015) VALUES (398165724, 1005, '12:00:00');