Praktek DATABASE #3

1. Buatlah sebuah database dengan nama perpustakaan dengan table2 di bawah ini:

Mahasiswa

____________________________________________________________

Nama_Field           Type Data              Panjang                  Ket

NIM                         Char                        9                      Primary Key

Nama                      Varchar                  30                         -

Alamat                    Varchar                  30                         -

____________________________________________________________

Penerbit

____________________________________________________________

Nama Field            Type Data              Panjang                  Ket

Kd_Penerbit         Char                        2                              Primary Key

Nama_Penerbit    varchar                  30                              -

____________________________________________________________                   

Buku

____________________________________________________________

Nama_Field           Type Data              Panjang                  Ket

Kode_Buku           Char                          4                         Primary Key

Judul                      Char                          20                            -

Pengarang              Char                        15                             -

Jmlh_Buku             Int                             -                               -

Kode_Penerbit      Char                          2                         Foreign Key

____________________________________________________________

Pinjam

____________________________________________________________

Nama_Field             Type Data            Panjang                  Ket

Tgl_Pinjam               Datetime              -                               -

NIM                           Char                     9                              Foreign Key

Buk_Kode_Buku    Char                      4                              Foreign Key

Tgl_Hrs_Kembali    Datetime              -                               -

Tgl_Kembali            Datetime              -                               -

____________________________________________________________

Menginput data kedalam table

Table Mahasiswa

_____________________________________________

NIM                         Nama                      Alamat

12301001             Surya                      jln.Jawa

12301002             Dharma                  jln.Teratai

12301003             Sarah                      jln.Medan

12301004             Melani                    jln.Melati

_____________________________________________

Table Penerbit

________________________________________

Kode_Penerbit                     Nama_Penerbit

01                                           PIKSI ITB

02                                           Genesha

03                                           Epsilon

04                                           Gramedia

________________________________________

Table Buku

_______________________________________________________________________________________

Kode Buku             Judul                                       Pengarang             Jmlh Buku              Kode Penerbit

S001                       DOS                                          M.Urip                    10                           01

BD01                      DataBase III Plus                   EPSI B                       5                           01

BD04                      Clipper                                    Ahmad G                   4                           02

F101                       FISIKA                                     Sutrisno                 10                           04

_______________________________________________________________________________________

Table Buku

__________________________________________________________________________________________

Tanggal Pinjam      Mahasiswa No Induk   Buku Kode Buku      Tanggal Harus Kembali        Tanggal Kembali

2003/04/20          123010001                           S001                       2003/04/23                          2003/04/23

2003/04/20          123010002                           BD01                      2003/04/23                          2003/04/22

2003/04/20          123010003                           BD04                      2003/04/23                          2003/04/24

2003/04/21          123010003                           F101                       2003/04/24                          2003/04/25

2003/04/21          123010004                           S001                       2003/04/24                          2003/04/25

2003/04/21          123010001                           BD01                      2003/04/24                          2003/04/24

__________________________________________________________________________________________

 
STEP TO FINISH DATABASE
1. create database perpustakaan

2. use perpustakaan

3. create table Mahasiswa(NIM char(9)primary key,Nama varchar(30),Alamat varchar(30))

4. select * from Mahasiswa

5. create table Penerbit(Kd_Penerbit char(2)primary key,Nama_Penerbit varchar(30))

6. select * from Penerbit

7. create table Buku(Kode_Buku char(4) primary key,Judul char(20),Pengarang char(15),Jmlh_Buku int,Kode_Penerbit char(2) References Penerbit(Kd_Penerbit))

8. select * from Buku

9. create table Pinjam(Tgl_Pinjam Datetime,NIM char(9) References Mahasiswa(NIM),Buk_Kode_Buku char(4) references Buku(Kode_Buku), Tgl_Hrs_Kembali datetime,Tgl_Kembali datetime)

10. select * from Pinjam

Menginput Data Kedalam Table
1. Table Mahasiswa
    insert into Mahasiswa values('123010001','Surya','Jln.Jawa')
    insert into Mahasiswa values('123010002','Dharma','Jln.Teratai')
    insert into Mahasiswa values('123010003','Sarah','Jln.Medan')
    insert into Mahasiswa values('123010004','Melani','Jln.Melati')
        Select * From Mahasiswa
2. Table Penerbit
    insert into Penerbit values('01','PIKSI ITB')
    insert into Penerbit values('02','Genesha')
    insert into Penerbit values('03','Epsilon')
    insert into Penerbit values('04','Gramedia')
        Select * From Penerbit
3. Table Buku
    insert into Buku values('S001','DOS','M.Urip','10','01')
    insert into Buku values('BD01','DataBase III Plus','EPSI B','5','01')
    insert into Buku values('BD04','Clipper','Ahmad G','4','02')
    insert into Buku values('F101','FISIKA','Sutrisno','10','04')
        Select * From Buku
4. Table Pinjam
    insert into Pinjam values('2003/04/20','123010001','S001','2003/04/23','2003/04/23')
    insert into Pinjam values('2003/04/20','123010002','BD01','2003/04/23','2003/04/22')
    insert into Pinjam values('2003/04/20','123010003','BD04','2003/04/23','2003/04/24')
    insert into Pinjam values('2003/04/21','123010003','F101','2003/04/24','2003/04/25')
    insert into Pinjam values('2003/04/21','123010004','S001','2003/04/24','2003/04/25')
    insert into Pinjam values('2003/04/21','123010001','BD01','2003/04/24','2003/04/24')
        Select * From Pinjam