"/* LAB #2 */\n"

    . "    /* step : 1 */\n"

    . "    /* create my second database, called PensyarahKursusD */\n"

    . "    /* syntax : CREATE DATABASE IF NOT EXISTS databasename; */\n"

    . "CREATE DATABASE IF NOT EXISTS PensyarahKursusDb";



"/* step : 2 */\n"

    . "    /* show databases in your account */\n"

    . "SHOW DATABASES\n"

    . "    /* list of databases are shown , including the one just creates, PensyarahKursusDb */";

/* step : 3 */\n"

    . "    /* Use/Open databases in your account, PensyarahKursusDb */\n"

    . "USE\n"

    . "    PensyarahKursusDb";

/* step : 4 */\n"

    . "    /* create table PENSYARAH*/\n"

    . "CREATE TABLE IF NOT EXISTS KURSUS (\n"

    . "    NoStaff VARCHAR(10) NOT NULL,\n"

    . "    NamaPensyarah VARCHAR(100) NOT NULL,\n"

    . "    KodFakulti VARCHAR(10) NOT NULL,\n"

    . "    Email VARCHAR(100) NOT NULL,\n"

    . "    TelefonBimbit VARCHAR(16),\n"

    . "    NoBilik VARCHAR(5),\n"

    . "    NamaBlock VARCHAR(15), NoPejabat VARCHAR(16), PRIMARY KEY(NoStaff)\n"


    . ")";


/* step : 5 */\n"

    . "    /* show the table created in this database, it should listed table KURSUS */\n"







/* Insert some data into table PENSYARAH */
INSERT INTO PENSYARAH
VALUES(
    'ZA001',
    'DR.ASMALIZA ABD GHANI',
    'FIK',
    'asmaliza1@gmail.com',
    '0112345678',
    'D01',
    'D',
    '065432711'
);
INSERT INTO PENSYARAH
VALUES(
    'ZA002',
    'DR.NUR FARALIZZA BINTI MANSOR',
    'FIK',
    'faralizza2@gmail.com',
    '01243556',
    'D02',
    'D',
    '06543779'
);
INSERT INTO PENSYARAH
VALUES(
    'ZA003',
    'FAUZIAH BINTI ABDUL WAHAB',
    'FIK',
    'fauziah3@gmail.com',
    '0198763726',
    'D03',
    'D',
    '06754889'
);
INSERT INTO PENSYARAH
VALUES(
    'ZA004',
    'NORAIDA BINTI MAHIDDIN',
    'FIK',
    'aida4@gmail.com',
    '01342345445',
    'D04',
    'D',
    '06754336'
);
INSERT INTO PENSYARAH
VALUES(
    'ZA005',
    'ROHANA BT ISMAIL',
    'FIK',
    'rohana6@gmail.com',
    '0147658899',
    'DO5',
    'D',
    '08768944'
);


/* create table KURSUS */\n"

    . "CREATE TABLE IF NOT EXISTS KURSUS(\n"

    . "    KodKursus VARCHAR(10) NOT NULL,\n"

    . "    NamaKursus VARCHAR(100) NOT NULL,\n"

    . "    JamKredit INT(1),\n"

    . "    PRIMARY KEY(KodKursus)\n"

    . ")";




/* Insert some data into table KURSUS */
INSERT INTO KURSUS
VALUES('ITD11103', 'ALGEBRA', '3');
INSERT INTO KURSUS
VALUES(
    'ITD11203',
    'PENGATUCARAAN KOMPUTER',
    '3'
);
INSERT INTO KURSUS
VALUES('ITD11303', 'PANGKALAN DATA', '3');

INSERT INTO KURSUS
VALUES('ITD13103', 'KOMUNIKASI DATA');

INSERT INTO KURSUS
VALUES(
    'MMD11103',
    'INTERAKSI MESIN MANUSIA'
);





/* create table SEMESTER */

CREATE TABLE IF NOT EXISTS SEMESTER (
    IDSemester varchar(10) NOT Null,
    NamaSemester varchar(100) Not Null,
    TarikhMulaSem date,
    TarikhAkhirSem date,
   PRIMARY KEY(IDSemester));

/* insert some data into table semester */

INSERT INTO SEMESTER VALUES
('S01','SEMESTER PENDEK','4/6/2018','9/8/2018');

INSERT INTO SEMESTER VALUES
('S02','SEMESTER 1','20/9/2018','5/1/2019');

INSERT INTO SEMESTER VALUES
('S03','SEMESTER 2','20/1/2019','20/5/2019');





CREATE TABLE IF NOT EXISTS PendaftaranKursus (
    NoMatrik varchar(10) Not Null,
    KodKursus varchar(10)NOT Null,
    IDSemester varchar(10) NOT Null,
    PRIMARY KEY (NoMatrik,KodKursus,IDSemester),
    FOREIGN KEY (NoMatrik) REFERENCES Pelajar(NoMatrik),
    FOREIGN KEY(KodKursus) REFERENCES Subjek(KodKursus),
    FOREIGN KEY(IDSemester) REFERENCES Semester(IDSemester));


CREATE TABLE IF NOT EXISTS PENSYARAHKURSUS (
    NoStaff varchar(10) Not Null,
    KodKursus varchar(10) NOT Null,
    IDSemester varchar(10) NOT Null,
    PRIMARY KEY (NoStaff,KodKursus,IDSemester),
    FOREIGN KEY(NoStaff) REFERENCES pensyarah(NoStaff),
    FOREIGN KEY(KodKursus) REFERENCES kursus(KodKursus),
    FOREIGN KEY(IDSemester) REFERENCES semester(IDSemester));


CREATE TABLE IF NOT EXISTS FAKULTI(
    KodFakulti varchar(10) NOT Null,
    NamaFakulti varchar(10) NOT Null,
    NoTelefon varchar(14),
    Kampus varchar(100),
    PRIMARY KEY (KodFakulti));


CREATE TABLE IF NOT EXISTS PROGRAM (
    KodProgram varchar(10) Not NUll,
    NamaProgram varchar(100) Not Null,
    KodFakulti varchar(10) NOT Null,
    PRIMARY KEY (KodProgram),
    FOREIGN KEY(KodFakulti) REFERENCES fakulti(KodFkulti));



INSERT INTO pensyarahkursus VALUES
('ZA001','PBD10602','S02');

INSERT INTO pensyarahkursus VALUES
('ZA002','MMD11103','S02');

INSERT INTO pensyarahkursus VALUES
('ZA003','ITD11303','S03');

INSERT INTO pensyarahkursus VALUES
('ZA004','ITD13103','S03');

INSERT INTO pensyarahkursus VALUES
('ZA005','ITD11203','S02');
    . "SHOW TABLES";


INSERT INTO fakulti VALUES
('FIK','FAKULTI INFORMATIK KEKOMPUTERAN','06753442','BESUT');

INSERT INTO fakulti VALUES
('FBIM','Fakulti Biosumber & Industri Makanan','057889997','BESUT');

INSERT INTO fakultiVALUES
('FKI','Fakulti Pengajian Kontemporari Islam','0987765554','GONG BADAK');

INSERT INTO fakulti VALUES
('FKS','Fakulti Sains Kesihatan','0877888663','PERUBATAN');

INSERT INTO fakulti VALUES
('FBK',' Fakulti Bahasa & Komunikasi','06883321','GONG BADAK');

INSERT INTO program VALUES
('P001','DIPLOMA TEKNOLOGI MAKLUMAT','FIK');

INSERT INTO program VALUES
('P002','DIPLOMA TEKNOLOGI MAKLUMAT MULTIMEDIA','FIK');

INSERT INTO program VALUES
('P003','Diploma Pengajian Islam (Al-Quran & Al-Sunnah)','FKI');

INSERT INTO program VALUES
('P004','Ijazah Sarjana Muda Perniagaantani dengan Kepujian','FBIM');

INSERT INTO program VALUES
('P005','Diploma Pengajaran Bahasa Inggeris Sebagai Bahasa Kedua (TESL)','FBK');