· 7 years ago · Dec 10, 2018, 01:58 PM
1"/* LAB #2 */\n"
2
3 . " /* step : 1 */\n"
4
5 . " /* create my second database, called PensyarahKursusD */\n"
6
7 . " /* syntax : CREATE DATABASE IF NOT EXISTS databasename; */\n"
8
9 . "CREATE DATABASE IF NOT EXISTS PensyarahKursusDb";
10
11
12
13"/* step : 2 */\n"
14
15 . " /* show databases in your account */\n"
16
17 . "SHOW DATABASES\n"
18
19 . " /* list of databases are shown , including the one just creates, PensyarahKursusDb */";
20
21/* step : 3 */\n"
22
23 . " /* Use/Open databases in your account, PensyarahKursusDb */\n"
24
25 . "USE\n"
26
27 . " PensyarahKursusDb";
28
29/* step : 4 */\n"
30
31 . " /* create table PENSYARAH*/\n"
32
33 . "CREATE TABLE IF NOT EXISTS KURSUS (\n"
34
35 . " NoStaff VARCHAR(10) NOT NULL,\n"
36
37 . " NamaPensyarah VARCHAR(100) NOT NULL,\n"
38
39 . " KodFakulti VARCHAR(10) NOT NULL,\n"
40
41 . " Email VARCHAR(100) NOT NULL,\n"
42
43 . " TelefonBimbit VARCHAR(16),\n"
44
45 . " NoBilik VARCHAR(5),\n"
46
47 . " NamaBlock VARCHAR(15), NoPejabat VARCHAR(16), PRIMARY KEY(NoStaff)\n"
48
49
50 . ")";
51
52
53/* step : 5 */\n"
54
55 . " /* show the table created in this database, it should listed table KURSUS */\n"
56
57
58
59
60
61
62
63/* Insert some data into table PENSYARAH */
64INSERT INTO PENSYARAH
65VALUES(
66 'ZA001',
67 'DR.ASMALIZA ABD GHANI',
68 'FIK',
69 'asmaliza1@gmail.com',
70 '0112345678',
71 'D01',
72 'D',
73 '065432711'
74);
75INSERT INTO PENSYARAH
76VALUES(
77 'ZA002',
78 'DR.NUR FARALIZZA BINTI MANSOR',
79 'FIK',
80 'faralizza2@gmail.com',
81 '01243556',
82 'D02',
83 'D',
84 '06543779'
85);
86INSERT INTO PENSYARAH
87VALUES(
88 'ZA003',
89 'FAUZIAH BINTI ABDUL WAHAB',
90 'FIK',
91 'fauziah3@gmail.com',
92 '0198763726',
93 'D03',
94 'D',
95 '06754889'
96);
97INSERT INTO PENSYARAH
98VALUES(
99 'ZA004',
100 'NORAIDA BINTI MAHIDDIN',
101 'FIK',
102 'aida4@gmail.com',
103 '01342345445',
104 'D04',
105 'D',
106 '06754336'
107);
108INSERT INTO PENSYARAH
109VALUES(
110 'ZA005',
111 'ROHANA BT ISMAIL',
112 'FIK',
113 'rohana6@gmail.com',
114 '0147658899',
115 'DO5',
116 'D',
117 '08768944'
118);
119
120
121/* create table KURSUS */\n"
122
123 . "CREATE TABLE IF NOT EXISTS KURSUS(\n"
124
125 . " KodKursus VARCHAR(10) NOT NULL,\n"
126
127 . " NamaKursus VARCHAR(100) NOT NULL,\n"
128
129 . " JamKredit INT(1),\n"
130
131 . " PRIMARY KEY(KodKursus)\n"
132
133 . ")";
134
135
136
137
138/* Insert some data into table KURSUS */
139INSERT INTO KURSUS
140VALUES('ITD11103', 'ALGEBRA', '3');
141INSERT INTO KURSUS
142VALUES(
143 'ITD11203',
144 'PENGATUCARAAN KOMPUTER',
145 '3'
146);
147INSERT INTO KURSUS
148VALUES('ITD11303', 'PANGKALAN DATA', '3');
149
150INSERT INTO KURSUS
151VALUES('ITD13103', 'KOMUNIKASI DATA');
152
153INSERT INTO KURSUS
154VALUES(
155 'MMD11103',
156 'INTERAKSI MESIN MANUSIA'
157);
158
159
160
161
162
163/* create table SEMESTER */
164
165CREATE TABLE IF NOT EXISTS SEMESTER (
166 IDSemester varchar(10) NOT Null,
167 NamaSemester varchar(100) Not Null,
168 TarikhMulaSem date,
169 TarikhAkhirSem date,
170 PRIMARY KEY(IDSemester));
171
172/* insert some data into table semester */
173
174INSERT INTO SEMESTER VALUES
175('S01','SEMESTER PENDEK','4/6/2018','9/8/2018');
176
177INSERT INTO SEMESTER VALUES
178('S02','SEMESTER 1','20/9/2018','5/1/2019');
179
180INSERT INTO SEMESTER VALUES
181('S03','SEMESTER 2','20/1/2019','20/5/2019');
182
183
184
185
186
187CREATE TABLE IF NOT EXISTS PendaftaranKursus (
188 NoMatrik varchar(10) Not Null,
189 KodKursus varchar(10)NOT Null,
190 IDSemester varchar(10) NOT Null,
191 PRIMARY KEY (NoMatrik,KodKursus,IDSemester),
192 FOREIGN KEY (NoMatrik) REFERENCES Pelajar(NoMatrik),
193 FOREIGN KEY(KodKursus) REFERENCES Subjek(KodKursus),
194 FOREIGN KEY(IDSemester) REFERENCES Semester(IDSemester));
195
196
197CREATE TABLE IF NOT EXISTS PENSYARAHKURSUS (
198 NoStaff varchar(10) Not Null,
199 KodKursus varchar(10) NOT Null,
200 IDSemester varchar(10) NOT Null,
201 PRIMARY KEY (NoStaff,KodKursus,IDSemester),
202 FOREIGN KEY(NoStaff) REFERENCES pensyarah(NoStaff),
203 FOREIGN KEY(KodKursus) REFERENCES kursus(KodKursus),
204 FOREIGN KEY(IDSemester) REFERENCES semester(IDSemester));
205
206
207CREATE TABLE IF NOT EXISTS FAKULTI(
208 KodFakulti varchar(10) NOT Null,
209 NamaFakulti varchar(10) NOT Null,
210 NoTelefon varchar(14),
211 Kampus varchar(100),
212 PRIMARY KEY (KodFakulti));
213
214
215CREATE TABLE IF NOT EXISTS PROGRAM (
216 KodProgram varchar(10) Not NUll,
217 NamaProgram varchar(100) Not Null,
218 KodFakulti varchar(10) NOT Null,
219 PRIMARY KEY (KodProgram),
220 FOREIGN KEY(KodFakulti) REFERENCES fakulti(KodFkulti));
221
222
223
224INSERT INTO pensyarahkursus VALUES
225('ZA001','PBD10602','S02');
226
227INSERT INTO pensyarahkursus VALUES
228('ZA002','MMD11103','S02');
229
230INSERT INTO pensyarahkursus VALUES
231('ZA003','ITD11303','S03');
232
233INSERT INTO pensyarahkursus VALUES
234('ZA004','ITD13103','S03');
235
236INSERT INTO pensyarahkursus VALUES
237('ZA005','ITD11203','S02');
238 . "SHOW TABLES";
239
240
241INSERT INTO fakulti VALUES
242('FIK','FAKULTI INFORMATIK KEKOMPUTERAN','06753442','BESUT');
243
244INSERT INTO fakulti VALUES
245('FBIM','Fakulti Biosumber & Industri Makanan','057889997','BESUT');
246
247INSERT INTO fakultiVALUES
248('FKI','Fakulti Pengajian Kontemporari Islam','0987765554','GONG BADAK');
249
250INSERT INTO fakulti VALUES
251('FKS','Fakulti Sains Kesihatan','0877888663','PERUBATAN');
252
253INSERT INTO fakulti VALUES
254('FBK',' Fakulti Bahasa & Komunikasi','06883321','GONG BADAK');
255
256INSERT INTO program VALUES
257('P001','DIPLOMA TEKNOLOGI MAKLUMAT','FIK');
258
259INSERT INTO program VALUES
260('P002','DIPLOMA TEKNOLOGI MAKLUMAT MULTIMEDIA','FIK');
261
262INSERT INTO program VALUES
263('P003','Diploma Pengajian Islam (Al-Quran & Al-Sunnah)','FKI');
264
265INSERT INTO program VALUES
266('P004','Ijazah Sarjana Muda Perniagaantani dengan Kepujian','FBIM');
267
268INSERT INTO program VALUES
269('P005','Diploma Pengajaran Bahasa Inggeris Sebagai Bahasa Kedua (TESL)','FBK');