praktikum bdl

7/18/2019 Praktikum Bdl

1/3

CREATE DATABASE Perusahaan

CREATE TABLE Departemen (ID_Dep varchar(2) NOT NULL,Nama_Dep varchar(12) NOT NULL,PRIMARY KEY(ID_Dep));

CREATE TABLE Karyawan (No_Karyawan varchar(8) NOT NULL,Nama varchar(35) NOT NULL,Jenis_Kelamin boolean default '1' NOT NULL,Tanggal_Lahir date NOT NULL,Jenis_Karyawan char(1) default '*' NOT NULL,ID_Dep varchar(2) NOT NULL,PRIMARY KEY(No_Karyawan),FOREIGN KEY (ID_Dep) REFERENCES Departemen(ID_Dep));

CREATE TABLE Manajer (No_Karyawan varchar(8) NOT NULL,Tipe varchar(15) NOT NULL,Nopol varchar(8) NOT NULL,

ID_Dep varchar(2) NOT NULL,PRIMARY KEY(No_Karyawan),FOREIGN KEY(ID_Dep) REFERENCES Departemen(ID_Dep),FOREIGN KEY(No_Karyawan) REFERENCES Karyawan(No_Karyawan));

CREATE TABLE Proyek (ID_Proyek int(11) auto_increment NOT NULL,Deskripsi varchar(100) NOT NULL,Selesai bool NOT NULL default '0',PRIMARY KEY(ID_Proyek));

CREATE TABLE Sekertaris (No_Karyawan varchar(8) NOT NULL,Bahasa_Asing varchar(10) NOT NULL,PRIMARY KEY(No_Karyawan, Bahasa_Asing),FOREIGN KEY(No_Karyawan) REFERENCES Karyawan(No_Karyawan));

CREATE TABLE Teknisi (No_Karyawan varchar(8) NOT NULL,Keahlian_Teknis varchar(25) NOT NULL,PRIMARY KEY(No_Karyawan, Keahlian_Teknis),FOREIGN KEY(No_Karyawan) REFERENCES Karyawan(No_Karyawan));

CREATE TABLE Teknisi_Proyek (No_Karyawan varchar(8) NOT NULL,ID_Proyek int(11) auto_increment NOT NULL,Mulai_kerja date,Sampai_Tanggal date,PRIMARY KEY(No_Karyawan,ID_Proyek),FOREIGN KEY(No_Karyawan) REFERENCES Karyawan(No_Karyawan),FOREIGN KEY(ID_Proyek) REFERENCES Proyek(ID_Proyek));


2/3

INSERT INTO Departemen VALUES('AK ','Akuntansi'),('EN ','Engineering'),('PS ','Pemasaran'),('RD ','R & D'),('SD ','SDM');

INSERT INTO Karyawan VALUES('20050001','Rusdianto','1','1975-03-24','M ','PS'),('20050002','Darmaji','1','1977-03-17','M ','AK'),('20050003','Dirham ','1','1969-12-01','M ','SD'),('20050004','Duhan','1','1972-03-28','T ','EN'),('20060001','Dewi Nurhayati','0','1973-04-01','S ','PS'),('20060002','Diah Ariyanti','0','1977-10-14','* ','AK'),('20060003','Danang Danar Hadi','1','1976-01-16','M ','EN'),('20060004','Sakroni','1','1977-06-23','* ','PS'),('20080001','Sita Amelia ','0','1980-12-11','* ','SD'),('20080002','Rita Andini','0','1982-03-01','S ','AK'),('20080003','Rudi Hartono ','1','1981-08-08','M ','RD'),('20080004','Karmen','1','1979-05-14','T ','EN'),('20080005','Santi Wardani','0','1985-11-17','* ','PS'),('20080006','Savitri','0','1988-12-02','* ','PS'),

('20080007','Firanda','0','1986-07-01','* ','PS'),('20080008','Rian Adiwarna','1','1986-01-13','* ','PS'),('20090001','Ratna Maharani ','0','1977-08-29','* ','PS'),('20090002','Robert Sinamurna','1','1969-11-30','T ','EN'),('20090003','Saman Alhamdi','1','1978-02-28','* ','PS'),('20090004','Sumartini','0','1980-07-24','S ','SD'),('20090005','Karim Sujarwadi','1','1985-10-01','* ','RD'),('20090006','Suwandi','1','1989-05-15','* ','PS'),('20090007','Siti Aminah','0','1985-09-01','* ','AK'),('20090008','Victor Sumardi','1','1976-03-03','* ','SD'),('20090009','Jaja Miharja','1','1974-07-23','T ','EN'),('20090010','Tarmidzi','1','1981-05-21','* ','PS'),('20090011','Novia Fitriana','0','1982-08-19','* ','AK');

INSERT INTO Manajer VALUES('20050001','AVANZA G ','AB1674GA ','PS'),('20050002','AVANZA G ','AB1675GA ','SD'),('20050003','RUSH G ','AB6776BA ','SD'),('20060003','RUSH G ','AB7374SA ','EN'),('20080003','BALENO ','AB1111TA ','RD');

INSERT INTO Proyek VALUES('','Proyek Pengembangan Aplikasi A/R Internal ','0'),('','Proyek Pengembangan A/P Internal ','0'),

('','Proyek LAN di CV Mayapada','1'),('','Proyek Instalasi Jaringan Komputer di PT Suramaya','0'),('','Proyek Instalasi Jaringan Komputer di PT Jayaraya','1'),('','Pelatihan LAN di PT Durajati','1'),('','Pra-tender PT Sukamaju','1'),('','Pengembangan Aplikasi BBN ','0');

INSERT INTO Sekertaris VALUES('20060001','Inggris'),


3/3

('20060001','Mandarin'),('20080002','Inggris'),('20090004','Inggris'),('20090004','Jerman');

INSERT INTO Teknisi VALUES('20050004','Hardware'),('20050004','Jaringan Komputer'),('20080004','Jaringan Komputer'),('20080004','Linux'),('20090002','MySQL'),('20090002','Pemrograman C'),('20090002','PHP'),('20090002','SQL Server'),('20090009','ASP'),('20090009','FoxPro'),('20090009','Jaringan Komputer');

INSERT INTO Teknisi_Proyek VALUES('20050004','3','2009-03-21','2000-04-10'),('20050004','4','2009-04-20','2000-04-10'),('20050004','5','2009-06-01','2009-07-01'),

('20050004','6','2009-08-10','2009-08-25'),('20080004','4','2009-06-01','2009-08-25'),('20080004','5','2009-06-01','2009-06-15'),('20080004','7','2009-10-10','2009-10-15'),('20080004','8','2009-11-15','2009-10-15'),('20090002','1','2009-02-01','2009-10-15'),('20090002','2','2009-02-01','2009-10-15'),('20090009','1','2009-02-01','2009-10-15'),('20090009','2','2009-02-01','2009-10-15'),('20090009','8','2009-11-15','2009-11-25');

praktikum bdl

Documents