IMPLEMENTASI DATABASE SQL SERVER MANAGEMENT STUDIO 2008 R2 ENTERPRISE WITH SERVICE PACK 2

Nama Database : rentalvcddvd

Syntax CREATE DATABASE :

 create database rentalvcddvd

Message : Command(s) completed successfully.

Nama Tabel : petugas

Syntax CREATE TABEL PETUGAS :

use rentalvcddvd

create table petugas (

id_ptgs char(5),

nm_ptgs varchar(25),

alamat_ptgs varchar(50),

status_ptgs char(5),

no_telp_ptgs numeric(12, 0),

primary key (id_ptgs)

)

Message : Command(s) completed successfully.

Syntax VIEW GRID RESULT TABEL PETUGAS :

sp_help petugas

Nama Tabel : pelanggan

Syntax CREATE TABEL PELANGGAN :

use rentalvcddvd

create table pelanggan (

id_plgn char(5),

nm_plgn varchar(25),

alamat_plgn varchar(50),

no_telp_plgn numeric(12, 0),

primary key (id_plgn)

)

Message : Command(s) completed successfully.

Syntax VIEW GRID RESULT TABEL PELANGGAN :

sp_help pelanggan

Nama Tabel : barang

Syntax CREATE TABEL BARANG :

use rentalvcddvd

create table barang (

id_barang char(5),

nm_barang varchar(50),

tarif_sewa int,

stok_barang int,

tahun_keluar datetime,

primary key (id_barang)

)

Message : Command(s) completed successfully.

Syntax VIEW GRID RESULT TABEL BARANG :

sp_help barang

Nama Tabel : struk

Syntax CREATE TABEL STRUK :

use rentalvcddvd

create table struk (

no_sewa char(5),

nm_barang char(50),

tgl_pinjam datetime,

tgl_kembali datetime,

tarif_sewa int,

jumlah_barang int,

total int,

dibayar int,

kembali int,

id_ptgs char(5),

id_plgn char(5),

id_barang char(5),

primary key (no_sewa,id_ptgs,id_plgn),

constraint FK_idptgs foreign key (id_ptgs) references petugas(id_ptgs),

constraint FK_idplgn foreign key (id_plgn) references pelanggan(id_plgn),

constraint FK_idbarang foreign key (id_barang) references barang(id_barang)

)

Message : Command(s) completed successfully.

Syntax VIEW GRID RESULT TABEL STRUK :

sp_help struk

MENGISI DATA TABEL :

use rentalvcddvd

/** Isi data tabel petugas **/

INSERT INTO petugas VALUES('A001','Abdul Hasan Siswanto','Surabaya','Admin','085749699125')

INSERT INTO petugas VALUES('A002','Eko Sulaiman','Surabaya','Admin','085732250112')

Message :        (1 row(s) affected)

(1 row(s) affected)

/** Isi data tabel pelanggan **/

INSERT INTO pelanggan VALUES('M001','Fadilla','Surabaya','085749699125')

INSERT INTO pelanggan VALUES('M002','Leonardo Jobaigo','Surabaya','085732250112')

Message :        (1 row(s) affected)

(1 row(s) affected)

/** Isi data tabel barang **/

INSERT INTO barang VALUES('00001','Harry Potter','3500','6','2014-08-17')

INSERT INTO barang VALUES('00002','The Lord Of The Ring','3500','3','2014-08-17')

Message :        (1 row(s) affected)

(1 row(s) affected)

/** Isi data tabel struk **/

INSERT INTO struk VALUES ('S0001','Harry Potter','2014-06-29','2014-07-02','3500','2','7000','10000','3000','A001','M001','00001')

INSERT INTO struk VALUES ('S0002','The Lord Of The Ring','2014-06-29','2014-07-02','3500','2','7000','13000','','A001','M001','00001')

Message :        (1 row(s) affected)

(1 row(s) affected)

RECORD FISIK DATABASE :

use rentalvcddvd

SELECT * FROM petugas

SELECT * FROM pelanggan

SELECT * FROM barang

SELECT * FROM struk

MENGHAPUS SALAH SATU DATA RECORD DALAM TABEL :

DELETE FROM struk WHERE no_sewa='S0002'

MENGUBAH / UPDATE SALAH SATU DATA / COLOUMN RECORD DALAM TABEL :

UPDATE pelanggan SET nm_plgn='Fadila Ruz' WHERE id_plgn='M001'

MENGHAPUS TABEL BESERTA RECORD / ISI DATA :

DROP TABLE barang

MENGHAPUS DATABASE :

DROP DATABASE rentalvcddvd

DATABASE DIAGRAMS SQL SERVER 2008 :