[] [ ]readmore

Rabu, 25 April 2012

Entity Relationship Diagram

PERUSAHAAN PERSEWAAN MOBIL EasyHire Ltd.


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


Ø ER Diagram : Mobil dan Jenis Mobil

 

  Schema Diagram

 








ER Diagram : Depot dan Mobil

 

Schema Diagram

 








Ø ER Diagram : Employee dan Depot



Schema Diagram





Ø ER Diagram : Employee dan Groups


  Schema Diagram


 



Ø ER Diagram : Groups dan Hire Point
 

Schema Diagram









Ø ER Diagram :
Transaksi Peminjaman dan Pengembalian Mobil


 

Schema Diagram






Ø Overall Entity Relationship Diagram





Schema 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)

by Facebook Comment

Tidak ada komentar:

Poskan Komentar