EASY-HIRE Ltd. adalah sebuah perusahaan persewaan/ rental mobil yang berskala nasional. Perusahaan EASY-HIRE mengelola dua macam unit organisasi, yaitu DEPOTS dan HIRE-POINTS. Depot-depot tersebut digunakan untuk mengelola dan tempat/ garasi mobil. Sedangkan Hire-Points digunakan sebagai tempat para customer menyewa mobil. Setiap Hire-Point dapat meng-akses ke beberapa depot. Setiap depot dapat men-supply mobil-mobil untuk beberapa Hire-Point yang berbeda. Customer mengambil mobil dari depot dan mengembalikannya ke depot. Customer harus mengambil mobil dari depot tertentu tetapi dapat mengembalikannya ke depot lain sesuai pilihan mereka.
Karyawan dari EASY-HIRE di kategorikan baik itu sebagai karyawan hire-point atau karyawan depot. Karyawan depot hanya bekerja di satu depot tersebut. Sedangkan, karyawan hire-point berpindah-pindah tempat antar hire-point secara berkala. Setiap karyawan hire-point didefinisikan mempunyai grup tetap.
PERSOALAN :
Buatlah database system yang menyimpan data di mana mobil itu disimpan, dari mana mobil itu disewa, siapa yang menyewa mobil tersebut dan database mengenai perpindahan karyawan.
E N T I T I E S | |||
1. 2. 3. 4. | Jenis_mobil Mobil Depot Hire_point | 5. 6. 7. 8. | Employee Groups Customer Transaksi |
Entity Relationship Diagram
Data Dictionary
Ø Jenis_mobil
o Berisi informasi mengenai jenis mobil yang disewakan.
Field | Type | Keterangan |
Id_jenis | Int (3) | Kode jenis mobil |
Jenis_mobil | Varchar(30) | Nama jenis mobil |
Ø Depot
o Berisi informasi tempat mobil berada/ diparkir dan di maintained.
Field | Type | Keterangan |
Id_depot | Int (4) | Kode depot |
Nama_depot | Varchar (30) | Nama depot |
Alamat_d | Varchar (30) | Alamat depot berada |
Kota_d | Varchar (30) | Kota depot berada |
Telepon_d | Varchar (15) | Telepon depot |
Ø Hire_point
o Berisi informasi tempat customer menyewa mobil.
Field | Type | Keterangan |
Id_hire point | Int (4) | Kode hire point |
Nama_hire point | Varchar (30) | Nama hire point |
Alamat_hp | Varchar (30) | Alamat hire point berada |
Kota_hp | Varchar (30) | Kota hire point berada |
Telepon_hp | Varchar (15) | Telepon hire point |
Ø Employee
o Berisi informasi mengenai data/ identitas employee di EasyHire Ltd.
Field | Type | Keterangan |
Id_ employee | Int (4) | Kode employee |
Nama_ employee | Varchar (30) | Nama employee |
Alamat_e | Varchar (30) | Alamat employee berada |
Kota_e | Varchar (30) | Kota employee berada |
Telepon_e | Varchar (15) | Telepon employee |
Ø Customer
o Berisi informasi mengenai identitas customer yang menyewa mobil.
Field | Type | Keterangan |
Id_ customer | Int (8) | Kode customer |
Nama_ customer | Varchar (30) | Nama customer |
Alamat_c | Varchar (30) | Alamat customer berada |
Kota_c | Varchar (30) | Kota customer berada |
Telepon_c | Varchar (15) | Telepon customer |
Ø Groups
o Berisi informasi mengenai group employee yang berada di hire point.
Field | Type | Keterangan |
Id_ group | Int (4) | Kode group employee |
Id_ hire point | Int (4) | Kode hire point |
Groups | Varchar (30) | Nama group employee |
Ø Depot_employees
o Berisi informasi mengenai employee yang bekerja di depot.
Field | Type | Keterangan |
Id_ depot | Int (4) | Kode depot |
Id_ employee | Int (6) | Kode employee |
Ø Group_employees
o Berisi informasi mengenai employee yang berada di group.
Field | Type | Keterangan |
Id_ group | Int (4) | Kode group employee |
Id_ employee | Int (6) | Kode employee |
Ø Mobil
o Berisi informasi mengenai mobil yang disewakan.
Field | Type | Keterangan |
Id_mobil | Int (8) | Kode mobil yang disewakan |
Id_jenis | Int (3) | Kode jenis mobil |
Id_depot | Int (4) | Kode depot mobil berada/ di parkir |
No_polisi | Varchar (10) | Nomer polisi mobil |
Merk | Varchar (30) | Merk mobil |
Tahun_buat | Int (4) | Tahun pembuatan mobil |
Isi_silinder | Varchar (10) | Isi silinder mobil |
Warna | Varchar (10) | Warna body mobil |
Bahan_bakar | Varchar (10) | Bahan bakar mobil |
Status | Varchar (10) | Status mobil (bebas, pinjam, service) |
Harga_sewa | Int (7) | Harga sewa mobil per hari |
Ø Transaksi
o Berisi informasi mengenai transaksi persewaan dan pengembalian mobil di EasyHire Ltd.
Field | Type | Keterangan |
No_nota | Int (10) | No nota transaksi persewaan dan pengembalian mobil |
Id_hire_point | Int (4) | Kode hire point |
Id_customer | Int (8) | Kode customer peminjam mobil |
Id_mobil | Int (8) | Kode mobil yang disewa |
Id_depot | Int (4) | Kode depot, tempat mobil dikembalikan |
Tgl_pinjam | Date | Tanggal peminjaman mobil |
Tgl_kembali | Date | Tanggal pengembaliaan mobil |
Total_bayar | Int (9) | Total pembayaran persewaan mobil |
Perintah SQL pada MySQL
Ø Perintah membuat Tabel dan Constraints
mysql> CREATE TABLE jenis_mobil
(
id_jenis INT(3) NOT NULL AUTO_INCREMENT,
jenis_mobil VARCHAR(30) NOT NULL,
PRIMARY KEY (id_jenis)
);
mysql> CREATE TABLE depot
(
id_depot INT(4) NOT NULL AUTO_INCREMENT,
nama_depot VARCHAR(30) NOT NULL,
alamat_d VARCHAR(30),
kota_d VARCHAR(30),
telepon_d VARCHAR(15),
PRIMARY KEY (id_depot)
);
mysql> CREATE TABLE hire_point
(
id_hire_point INT(4) NOT NULL AUTO_INCREMENT,
nama_hire_point VARCHAR(30) NOT NULL,
alamat_hp VARCHAR(30),
kota_hp VARCHAR(30),
telepon_hp VARCHAR(15),
PRIMARY KEY (id_hire_point)
);
mysql> CREATE TABLE employee
(
id_employee INT(6) NOT NULL AUTO_INCREMENT,
nama_employee VARCHAR(30) NOT NULL,
alamat_e VARCHAR(30),
kota_e VARCHAR(30),
telepon_e VARCHAR(15),
PRIMARY KEY (id_employee)
);
mysql> CREATE TABLE customer
(
id_customer INT(8) NOT NULL AUTO_INCREMENT,
nama_customer VARCHAR(30) NOT NULL,
alamat_c VARCHAR(30),
kota_c VARCHAR(30),
telepon_c VARCHAR(15),
PRIMARY KEY (id_customer)
);
mysql> CREATE TABLE groups
(
id_group INT(4) NOT NULL AUTO_INCREMENT,
id_hire_point INT(4) NOT NULL,
groups VARCHAR(30) NOT NULL,
PRIMARY KEY (id_group),
FOREIGN KEY fid_hp_gro (id_hire_point) REFERENCES hire_point(id_hire_point)
);
mysql> CREATE TABLE depot_employees
(
id_depot INT(4) NOT NULL,
id_employee INT(6) NOT NULL,
FOREIGN KEY fid_dep_em (id_depot) REFERENCES depot(id_depot),
FOREIGN KEY fid_em_dep (id_employee) REFERENCES employee(id_employee)
);
mysql> CREATE TABLE group_employees
(
id_group INT(4) NOT NULL,
id_employee INT(6) NOT NULL,
FOREIGN KEY fid_gro_em (id_group) REFERENCES groups(id_group),
FOREIGN KEY fid_em_gro (id_employee) REFERENCES employee(id_employee)
);
mysql> CREATE TABLE mobil
(
id_mobil INT(8) NOT NULL AUTO_INCREMENT,
id_jenis INT(3) NOT NULL,
id_depot INT(4) NOT NULL,
no_polisi VARCHAR(10) NOT NULL,
merk VARCHAR(30),
tahun_buat INT(4),
isi_silinder VARCHAR(10),
warna VARCHAR(10),
bahan_bakar VARCHAR(10),
status VARCHAR(10),
harga_sewa INT(7),
PRIMARY KEY (id_mobil),
FOREIGN KEY fid_jen_mo (id_jenis) REFERENCES jenis_mobil(id_jenis),
FOREIGN KEY fid_dep_mo (id_depot) REFERENCES depot(id_depot)
);
mysql> CREATE TABLE transaksi
(
no_nota INT(10) NOT NULL AUTO_INCREMENT,
id_hire_point INT(4) NOT NULL,
id_customer INT(8) NOT NULL,
id_mobil INT(8) NOT NULL,
id_depot INT(4) NOT NULL,
tgl_pinjam DATE,
tgl_kembali DATE,
total_bayar INT(9),
PRIMARY KEY (no_nota),
FOREIGN KEY fid_hp_tr (id_hire_point) REFERENCES hire_point(id_hire_point),
FOREIGN KEY fid_cus_tr (id_customer) REFERENCES customer(id_customer),
FOREIGN KEY fid_mob_tr (id_mobil) REFERENCES mobil(id_mobil),
FOREIGN KEY fid_dep_tr (id_depot) REFERENCES depot(id_depot)
);
Ø Melihat Struktur Tabel
mysql> desc jenis_mobil;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id_jenis | int(3) | | PRI | NULL | auto_increment |
| jenis_mobil | varchar(30) | | | | |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc depot;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id_depot | int(4) | | PRI | NULL | auto_increment |
| nama_depot | varchar(30) | | | | |
| alamat_d | varchar(30) | YES | | NULL | |
| kota_d | varchar(30) | YES | | NULL | |
| telepon_d | varchar(15) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> desc hire_point;
+-----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+----------------+
| id_hire_point | int(4) | | PRI | NULL | auto_increment |
| nama_hire_point | varchar(30) | | | | |
| alamat_hp | varchar(30) | YES | | NULL | |
| kota_hp | varchar(30) | YES | | NULL | |
| telepon_hp | varchar(15) | YES | | NULL | |
+-----------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> desc employee;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| id_employee | int(6) | | PRI | NULL | auto_increment |
| nama_employee | varchar(30) | | | | |
| alamat_e | varchar(30) | YES | | NULL | |
| kota_e | varchar(30) | YES | | NULL | |
| telepon_e | varchar(15) | YES | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> desc customer;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| id_customer | int(8) | | PRI | NULL | auto_increment |
| nama_customer | varchar(30) | | | | |
| alamat_c | varchar(30) | YES | | NULL | |
| kota_c | varchar(30) | YES | | NULL | |
| telepon_c | varchar(15) | YES | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> desc groups;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| id_group | int(4) | | PRI | NULL | auto_increment |
| id_hire_point | int(4) | | | 0 | |
| groups | varchar(30) | | | | |
+---------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc depot_employees;
+-------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------+------+-----+---------+-------+
| id_depot | int(4) | | | 0 | |
| id_employee | int(6) | | | 0 | |
+-------------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc group_employees;
+-------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------+------+-----+---------+-------+
| id_group | int(4) | | | 0 | |
| id_employee | int(6) | | | 0 | |
+-------------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc mobil;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| id_mobil | int(8) | | PRI | NULL | auto_increment |
| id_jenis | int(3) | | | 0 | |
| id_depot | int(4) | | | 0 | |
| no_polisi | varchar(10) | | | | |
| merk | varchar(30) | YES | | NULL | |
| tahun_buat | int(4) | YES | | NULL | |
| isi_silinder | varchar(10) | YES | | NULL | |
| warna | varchar(10) | YES | | NULL | |
| bahan_bakar | varchar(10) | YES | | NULL | |
| status | varchar(10) | YES | | NULL | |
| harga_sewa | int(7) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)
mysql> desc transaksi;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| no_nota | int(10) | | PRI | NULL | auto_increment |
| id_hire_point | int(4) | | | 0 | |
| id_customer | int(8) | | | 0 | |
| id_mobil | int(8) | | | 0 | |
| id_depot | int(4) | | | 0 | |
| tgl_pinjam | date | YES | | NULL | |
| tgl_kembali | date | YES | | NULL | |
| total_bayar | int(9) | YES | | NULL | |
+---------------+---------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
Ø Data Tabel
mysql> select * from jenis_mobil;
+----------+-------------+
| id_jenis | jenis_mobil |
+----------+-------------+
| 1 | Sedan |
| 2 | Jeep |
| 3 | L300 |
+----------+-------------+
3 rows in set (0.00 sec)
mysql> select * from depot;
+----------+----------------+---------------------+------------+------------+
| id_depot | nama_depot | alamat_d | kota_d | telepon_d |
+----------+----------------+---------------------+------------+------------+
| 1 | Depot Bahagia | Jl Mawar no 2 | Yogyakarta | 0274515142 |
| 2 | Depot Sejahtra | Jl Diponegoro no 12 | Solo | 0271568945 |
| 3 | Depot Sentosa | Jl Kranggan no 71 | Jakarta | 0216953176 |
+----------+----------------+---------------------+------------+------------+
3 rows in set (0.00 sec)
mysql> select * from hire_point;
+---------------+-----------------+-------------------+------------+------------+
| id_hire_point | nama_hire_point | alamat_hp | kota_hp | telepon_hp |
+---------------+-----------------+-------------------+------------+------------+
| 1 | Lampar | Jl Lampar no 4 | Yogyakarta | 0274875642 |
| 2 | Podjok | Jl Tentara no 22 | Solo | 0271652341 |
| 3 | Pondhong | Jl Panjaitan no 1 | Jakarta | 0216872546 |
+---------------+-----------------+-------------------+------------+------------+
3 rows in set (0.06 sec)
mysql> select * from employee;
+-------------+---------------+--------------------+------------+------------+
| id_employee | nama_employee | alamat_e | kota_e | telepon_e |
+-------------+---------------+--------------------+------------+------------+
| 1 | Abu | Jl Kenanga no 2 | Yogyakarta | 0274575722 |
| 2 | Yanto | Jl Pelayar no 7 | Solo | 0271565488 |
| 3 | Basuki | Jl Pendekar no 21 | Jakarta | 0216765513 |
| 4 | Budi | Jl Kenanga no 2 | Yogyakarta | 0274575722 |
| 5 | Yandi | Jl Tentara no 7 | Solo | 0271565477 |
| 6 | Baskoro | Jl Setya no 1 | Jakarta | 0216456617 |
| 7 | Kalim | Jl Teja no 12 | Yogyakarta | 0274575712 |
| 8 | Kawit | Jl Pelabuhan no 27 | Solo | |
| 9 | Narto | Jl Pegadean no 1 | Jakarta | |
| 10 | Aji | Jl Wono no 8 | Yogyakarta | 0274565722 |
| 11 | Barjo | Jl Panembahan no 7 | Solo | |
| 12 | Paimin | Jl Pendega no 1 | Jakarta | |
| 13 | Bambang | Jl Kemangi no 4 | Yogyakarta | |
| 14 | Didik | Jl Lamongan no 47 | Solo | 0271565458 |
| 15 | Joko | Jl Mayoran no 21 | Jakarta | |
| 16 | Bandot | Jl Solo no 2 | Yogyakarta | |
| 17 | Yanuadi | Jl Mataram no 27 | Solo | 0271565077 |
| 18 | Lingga | Jl Warni no 11 | Jakarta | 0216450017 |
+-------------+---------------+--------------------+------------+------------+
18 rows in set (0.05 sec)
mysql> select * from customer;
+-------------+---------------+------------------+------------+------------+
| id_customer | nama_customer | alamat_c | kota_c | telepon_c |
+-------------+---------------+------------------+------------+------------+
| 1 | Joko | Jl Tanjung no 42 | Yogyakarta | 0274564722 |
| 2 | Adi Yanto | Jl Pelajar no 76 | Solo | 0271565471 |
| 3 | Bondan | Jl Pandean no 11 | Jakarta | 0216772523 |
+-------------+---------------+------------------+------------+------------+
3 rows in set (0.05 sec)
mysql> select * from groups;
+----------+---------------+---------+
| id_group | id_hire_point | groups |
+----------+---------------+---------+
| 1 | 1 | Mawar |
| 2 | 2 | Melati |
| 3 | 3 | Pelangi |
+----------+---------------+---------+
3 rows in set (0.00 sec)
mysql> select * from depot_employees;
+----------+-------------+
| id_depot | id_employee |
+----------+-------------+
| 1 | 3 |
| 1 | 6 |
| 1 | 8 |
| 2 | 1 |
| 2 | 2 |
| 2 | 4 |
| 3 | 5 |
| 3 | 7 |
| 3 | 9 |
+----------+-------------+
9 rows in set (0.05 sec)
mysql> select * from group_employees;
+----------+-------------+
| id_group | id_employee |
+----------+-------------+
| 1 | 13 |
| 1 | 10 |
| 1 | 11 |
| 2 | 12 |
| 2 | 15 |
| 2 | 14 |
| 3 | 16 |
| 3 | 17 |
| 3 | 18 |
+----------+-------------+
9 rows in set (0.00 sec)
Post a Comment