funlearning/Database at master · nadyazahra/funlearning · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
drop database gampar;
CREATE database gampar;
use gampar;

CREATE TABLE fakultas
(
	id 			int auto_increment PRIMARY key,
	kode 			CHAR(10) not null unique,
	nama_fakultas		VARCHAR(50) not null,
	dekan			VARCHAR(50) not null,
	updated_by		VARCHAR(15) not null,
	updated_at		datetime not null,
);

CREATE TABLE prodi
(
	id 				int auto_increment PRIMARY key,
	kode 				CHAR(10) not null unique,
	nama_prodi			VARCHAR(50) not null,
	kaprodi				VARCHAR(50) not null,
	id_fakultas			int not null,
	updated_by			VARCHAR(15) not null,
	updated_at			datetime not null,
	constraint prodifakultas foreign key id_fakultas references fakultas(id)
);

CREATE TABLE mahasiswa
(
	id 			int auto_increment PRIMARY key,
	NIM 			CHAR(9) not null unique,
	nama_mahasiswa		VARCHAR(50) not null,
	password		VARCHAR(255) not null,
	idprodi			int not null,
	idfakultas		int not null,
	xp_akademik		CHAR(6) not null,
	xp_nonakademik		CHAR(6) not null,
	updated_by		VARCHAR(15) not null,
	updated_at		datetime not null,
	constraint mahassiwaprodi foreign key idprodi references prodi(id),
	constraint mahassiwafakultas foreign key idfakultas references fakultas(id)
);

CREATE TABLE admin
{
	id 				int auto_increment PRIMARY key,
	kode_admin			CHAR(10) not null unique,
	nama_admin			VARCHAR(10) not null,
	password 			VARCHAR(255) not null,
	updated_by			VARCHAR(15) not null,
	updated_at			datetime not null
}