penerapan fuzzy database untuk rekomendasi pembelian laptop
TRANSCRIPT
PENERAPAN FUZZY DATABASE UNTUK REKOMENDASI PEMBELIAN LAPTOP
TEKNIK INFORMATIKA
UNIVERSITAS MUHAMMADIYAH PURWOKERTO
Nama : Abdul Gani Putra Suratma
NIM : 0803040050
http://ganip.wordpress.com/
http://www.facebook.com/csharpid
Aplikasi fuzzy database
Menu login
Menu
Data laptop ALL
Data pencarian OR
Data management laptop
TABEL
Pembuatan tabel menggunakan SQL server
Tabel 1. Login
Nama Filed Tipe Data Keterangan
usernamae varchar(50) Nama Pengguna
password varchar(50) Password
Tabel 2. M_laptop
Nama Filed Tipe Data Keterangan
laptop_id* varchar(255) Kode laptop (new id())
jenis_id int Foreign Key jenis_id
merek_id int Foreign Key merek_id
nama_laptop varchar(255) Nama laptop
harga float input fuzzy
processor float input fuzzy
harddisk float input fuzzy
memory float input fuzzy
lcd float input fuzzy
berat float input fuzzy
picture image Gambar Laptop
detail text Detail laptop
Ket: * Primary Key
Tabel 3. L_jenis
Nama Filed Tipe Data Keterangan
jenis_id* int Kode jenis (Identity)
jenis varchar(50) Nama jenis laptop
Ket: * Primary Key
Tabel 4. L_merek
Nama Filed Tipe Data Keterangan
merek_id* int Kode merk (Identity)
merek varchar(50) Nama merk laptop
Ket: * Primary Key
Tabel 5. Himpunan
Nama Filed Tipe Data Keterangan
variabel varchar(50) Nama variabel
himpunan varchar(50) Nama himpunan
fungsi varchar(50) Nama fungsi kurva
alfa float Batas ()
beta float Batas ()
pusatY float Batas (pusat )
gamma float Batas ()
betane float Batas (dan)
Ket: * Primary Key
beta ((alfa+gamma)/2)
pusatY ((alfa+gamma)/2)
Betane ((alfa+gamma)/2) - (alfa+((alfa+gamma)/2))/2
LAMPIRAN
Lampiran 1. Script query view penyusutan CREATE VIEW dbo.penyusutan (
variabel,
himpunan,
laptop_id,
nama_laptop,
miu
)
AS
--harga
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN (b.harga <= a.alfa) THEN 1
WHEN (a.alfa <= b.harga AND b.harga <= a.beta)
THEN 1 - 2*((b.harga - a.alfa)/(a.gamma - a.alfa) * (b.harga -
a.alfa)/(a.gamma - a.alfa))
WHEN (a.beta <= b.harga AND b.harga <= a.gamma)
THEN 2*((a.gamma - b.harga)/(a.gamma-a.alfa)*(a.gamma -
b.harga)/(a.gamma - a.alfa))
WHEN b.harga >= a.gamma THEN 0
END
FROM Himpunan a, M_Laptop b
WHERE (a.variabel = 'harga' AND a.himpunan ='murah' AND a.fungsi
='penyusutan')
UNION
--processor
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN (b.processor <= a.alfa) THEN 1
WHEN (a.alfa <= b.processor AND b.processor <= a.beta)
THEN 1 - 2*((b.processor - a.alfa)/(a.gamma - a.alfa) * (b.processor -
a.alfa)/(a.gamma - a.alfa))
WHEN (a.beta <= b.processor AND b.processor <= a.gamma)
THEN 2*((a.gamma - b.processor)/(a.gamma - a.alfa)*(a.gamma-
b.processor)/(a.gamma - a.alfa))
WHEN b.processor >= a.gamma THEN 0
END
FROM Himpunan a, M_Laptop b
WHERE (a.variabel = 'processor' AND a.himpunan ='rendah' AND a.fungsi
='penyusutan')
UNION
--harddisk
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN (b.harddisk <= a.alfa) THEN 1
WHEN (a.alfa <= b.harddisk AND b.harddisk <= a.beta)
THEN 1 - 2*((b.harddisk - a.alfa)/(a.gamma - a.alfa) * (b.harddisk -
a.alfa)/(a.gamma - a.alfa))
WHEN (a.beta <= b.harddisk AND b.harddisk <= a.gamma)
THEN 2*((a.gamma - b.harddisk)/(a.gamma - a.alfa)*(a.gamma-
b.harddisk)/(a.gamma - a.alfa))
WHEN b.harddisk >= a.gamma THEN 0
END
FROM Himpunan a, M_Laptop b
WHERE (a.variabel = 'harddisk' AND a.himpunan ='kecil' AND a.fungsi
='penyusutan')
UNION
--memory
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN (b.memory <= a.alfa) THEN 1
WHEN (a.alfa <= b.memory AND b.memory <= a.beta)
THEN 1 - 2*((b.memory - a.alfa)/(a.gamma - a.alfa) * (b.memory -
a.alfa)/(a.gamma - a.alfa))
WHEN (a.beta <= b.memory AND b.memory <= a.gamma)
THEN 2*((a.gamma - b.memory)/(a.gamma - a.alfa)*(a.gamma-
b.memory)/(a.gamma - a.alfa))
WHEN b.memory >= a.gamma THEN 0
END
FROM Himpunan a, M_Laptop b
WHERE (a.variabel = 'memory' AND a.himpunan ='kecil' AND a.fungsi
='penyusutan')
UNION
--lcd
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN (b.lcd <= a.alfa) THEN 1
WHEN (a.alfa <= b.lcd AND b.lcd <= a.beta)
THEN 1 - 2*((b.lcd - a.alfa)/(a.gamma - a.alfa) * (b.lcd -
a.alfa)/(a.gamma - a.alfa))
WHEN (a.beta <= b.lcd AND b.lcd <= a.gamma)
THEN 2*((a.gamma - b.lcd)/(a.gamma - a.alfa)*(a.gamma-b.lcd)/(a.gamma
- a.alfa))
WHEN b.lcd >= a.gamma THEN 0
END
FROM Himpunan a, M_Laptop b
WHERE (a.variabel = 'lcd' AND a.himpunan ='kecil' AND a.fungsi
='penyusutan')
UNION
--berat
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN (b.berat <= a.alfa) THEN 1
WHEN (a.alfa <= b.berat AND b.berat <= a.beta)
THEN 1 - 2*((b.berat - a.alfa)/(a.gamma - a.alfa) * (b.berat -
a.alfa)/(a.gamma - a.alfa))
WHEN (a.beta <= b.berat AND b.berat <= a.gamma)
THEN 2*((a.gamma - b.berat)/(a.gamma - a.alfa)*(a.gamma-
b.berat)/(a.gamma - a.alfa))
WHEN b.berat >= a.gamma THEN 0
END
FROM Himpunan a, M_Laptop b
WHERE (a.variabel = 'berat' AND a.himpunan ='ringan' AND a.fungsi
='penyusutan')
Lampiran 2. Script query view beta
CREATE VIEW dbo.beta (
variabel,
himpunan,
laptop_id,
nama_laptop,
miu
)
AS
--harga
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN ( b.harga <= a.alfa OR b.harga >= a.gamma) THEN 0
WHEN ( a.alfa <= b.harga and b.harga <= a.pusatY)
THEN 1/(1+( ((b.harga-a.pusatY)/ a.betane)*((b.harga-a.pusatY)/
a.betane) ))
WHEN ( a.pusatY <= b.harga AND b.harga <=a.gamma )
THEN ( 1/(1+ (((b.harga - a.pusatY)/ a.betane)*((b.harga - a.pusatY)/
a.betane))))
END
FROM Himpunan a, M_laptop b
WHERE (a.variabel = 'harga' AND a.himpunan ='sedang' AND a.fungsi
='beta')
UNION
--processor
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN ( b.processor <=a.alfa OR b.processor >=a.gamma) THEN 0
WHEN ( a.alfa <= b.processor and b.processor <= a.pusatY)
THEN 1/(1+( ((b.processor-a.pusatY)/ a.betane)*((b.processor-
a.pusatY)/ a.betane) ))
WHEN ( a.pusatY <= b.processor AND b.processor <=a.gamma )
THEN ( 1/(1+ (((b.processor - a.pusatY)/ a.betane)*((b.processor -
a.pusatY)/ a.betane))))
END
FROM Himpunan a, M_laptop b
WHERE (a.variabel = 'processor' AND a.himpunan ='sedang' AND a.fungsi
='beta')
UNION
--harddisk
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN ( b.harddisk <= a.alfa OR b.harddisk >= a.gamma) THEN 0
WHEN ( a.alfa <= b.harddisk and b.harddisk <= a.pusatY)
THEN 1/(1+( ((b.harddisk-a.pusatY)/ a.betane)*((b.harddisk-
a.pusatY)/ a.betane) ))
WHEN ( a.pusatY <= b.harddisk AND b.harddisk <=a.gamma )
THEN ( 1/(1+ (((b.harddisk - a.pusatY)/ a.betane)*((b.harddisk -
a.pusatY)/ a.betane))))
END
FROM Himpunan a, M_laptop b
WHERE (a.variabel = 'harddisk' AND a.himpunan ='sedang' AND a.fungsi
='beta')
UNION
--memory
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN ( b.memory <= a.alfa OR b.memory >= a.gamma) THEN 0
WHEN ( a.alfa <= b.memory and b.memory <= a.pusatY)
THEN 1/(1+( ((b.memory-a.pusatY)/ a.betane)*((b.memory-a.pusatY)/
a.betane) ))
WHEN ( a.pusatY <= b.memory AND b.memory <=a.gamma )
THEN ( 1/(1+ (((b.memory - a.pusatY)/ a.betane)*((b.memory -
a.pusatY)/ a.betane))))
END
FROM Himpunan a, M_laptop b
WHERE (a.variabel = 'memory' AND a.himpunan ='sedang' AND a.fungsi
='beta')
UNION
--lcd
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN ( b.lcd <= a.alfa OR b.lcd >= a.gamma) THEN 0
WHEN ( a.alfa <= b.lcd and b.lcd <= a.pusatY)
THEN 1/(1+( ((b.lcd-a.pusatY)/ a.betane)*((b.lcd-a.pusatY)/
a.betane) ))
WHEN ( a.pusatY <= b.lcd AND b.lcd <=a.gamma )
THEN ( 1/(1+ (((b.lcd - a.pusatY)/ a.betane)*((b.lcd - a.pusatY)/
a.betane))))
END
FROM Himpunan a, M_laptop b
WHERE (a.variabel = 'lcd' AND a.himpunan ='sedang' AND a.fungsi
='beta')
UNION
--berat
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN (b.berat <= a.alfa OR b.berat >= a.gamma) THEN 0
WHEN ( a.alfa <= b.berat and b.berat <= a.pusatY)
THEN 1/(1+( ((b.berat-a.pusatY)/ a.betane)*((b.berat-a.pusatY)/
a.betane) ))
WHEN ( a.pusatY <= b.berat AND b.berat <=a.gamma )
THEN ( 1/(1+ (((b.berat - a.pusatY)/ a.betane)*((b.berat - a.pusatY)/
a.betane))))
END
FROM Himpunan a, M_laptop b
WHERE (a.variabel = 'berat' AND a.himpunan ='sedang' AND a.fungsi
='beta')
Lampiran 3. Script query view pertumbuhan
CREATE VIEW dbo.pertumbuhan (
variabel,
himpunan,
laptop_id,
nama_laptop,
miu
)
AS
--harga
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN ( b.harga <= a.alfa ) THEN 0
WHEN ( a.alfa <= b.harga AND b.harga <= a.beta)
THEN 2*((b.harga - a.alfa)/(a.gamma - a.alfa)*(b.harga -
a.alfa)/(a.gamma - a.alfa))
WHEN ( a.beta <= b.harga AND b.harga <= a.gamma)
THEN 1-2*((a.gamma - b.harga)/(a.gamma - a.alfa)*(a.gamma -
b.harga)/(a.gamma - a.alfa))
WHEN (b.harga >= a.gamma)THEN 1
END
FROM Himpunan a, M_laptop b
WHERE (a.variabel = 'harga' AND a.himpunan ='mahal' AND a.fungsi
='pertumbuhan')
UNION
--processor
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN ( b.processor <= a.alfa ) THEN 0
WHEN ( a.alfa <= b.processor AND b.processor <= a.beta)
THEN 2*((b.processor - a.alfa)/(a.gamma - a.alfa)*(b.processor -
a.alfa)/(a.gamma - a.alfa))
WHEN ( a.beta <= b.processor AND b.processor <= a.gamma)
THEN 1-2*((a.gamma - b.processor)/(a.gamma - a.alfa)*(a.gamma -
b.processor)/(a.gamma - a.alfa))
WHEN (b.processor >= a.gamma)THEN 1
END
FROM Himpunan a, M_laptop b
WHERE (a.variabel = 'processor' AND a.himpunan ='tinggi' AND a.fungsi
='pertumbuhan')
UNION
--harddisk
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN ( b.harddisk <= a.alfa ) THEN 0
WHEN ( a.alfa <= b.harddisk AND b.harddisk <= a.beta)
THEN 2*((b.harddisk - a.alfa)/(a.gamma - a.alfa)*(b.harddisk -
a.alfa)/(a.gamma - a.alfa))
WHEN ( a.beta <= b.harddisk AND b.harddisk <= a.gamma)
THEN 1-2*((a.gamma - b.harddisk)/(a.gamma - a.alfa)*(a.gamma -
b.harddisk)/(a.gamma - a.alfa))
WHEN (b.harddisk >= a.gamma)THEN 1
END
FROM Himpunan a, M_laptop b
WHERE (a.variabel = 'harddisk' AND a.himpunan ='besar' AND a.fungsi
='pertumbuhan')
UNION
--memory
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN ( b.memory <= a.alfa ) THEN 0
WHEN ( a.alfa <= b.memory AND b.memory <= a.beta)
THEN 2*((b.memory - a.alfa)/(a.gamma - a.alfa)*(b.memory -
a.alfa)/(a.gamma - a.alfa))
WHEN ( a.beta <= b.memory AND b.memory <= a.gamma)
THEN 1-2*((a.gamma - b.memory)/(a.gamma - a.alfa)*(a.gamma -
b.memory)/(a.gamma - a.alfa))
WHEN (b.memory >= a.gamma)THEN 1
END
FROM Himpunan a, M_laptop b
WHERE (a.variabel = 'memory' AND a.himpunan ='besar' AND a.fungsi
='pertumbuhan')
UNION
--lcd
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN ( b.lcd <= a.alfa ) THEN 0
WHEN ( a.alfa <= b.lcd AND b.lcd <= a.beta)
THEN 2*((b.lcd - a.alfa)/(a.gamma - a.alfa)*(b.lcd - a.alfa)/(a.gamma
- a.alfa))
WHEN ( a.beta <= b.lcd AND b.lcd <= a.gamma)
THEN 1-2*((a.gamma - b.lcd)/(a.gamma - a.alfa)*(a.gamma -
b.lcd)/(a.gamma - a.alfa))
WHEN (b.lcd >= a.gamma)THEN 1
END
FROM Himpunan a, M_laptop b
WHERE (a.variabel = 'lcd' AND a.himpunan ='besar' AND a.fungsi
='pertumbuhan')
UNION
--berat
SELECT
a.variabel,
a.himpunan,
b.laptop_id,
b.nama_laptop,
miu=
CASE
WHEN ( b.berat <= a.alfa ) THEN 0
WHEN ( a.alfa <= b.berat AND b.berat <= a.beta)
THEN 2*((b.berat - a.alfa)/(a.gamma - a.alfa)*(b.berat -
a.alfa)/(a.gamma - a.alfa))
WHEN ( a.beta <= b.berat AND b.berat <= a.gamma)
THEN 1-2*((a.gamma - b.berat)/(a.gamma - a.alfa)*(a.gamma -
b.berat)/(a.gamma - a.alfa))
WHEN (b.berat >= a.gamma)THEN 1
END
FROM Himpunan a, M_laptop b
WHERE (a.variabel = 'berat' AND a.himpunan ='berat' AND a.fungsi
='pertumbuhan')
Lampiran 4. Script query view vmiu
CREATE VIEW dbo.vmiu (
variabel,
himpunan,
laptop_id,
nama_laptop,
miu
)
AS
select * from penyusutan
union
select * from beta
union
select * from pertumbuhan
Lampiran 5. Script query view miudetail
CREATE VIEW dbo.miudetail
AS
SELECT laptop_id,
nama_laptop,
--harga
sum(harga_murah)as harga_murah ,
sum(harga_sedang)as harga_sedang,
sum(harga_mahal)as harga_mahal,
--processor
sum(processor_rendah)as processor_rendah,
sum(processor_sedang)as processor_sedang,
sum(processor_tinggi)as processor_tinggi,
--harddisk
sum(harddisk_kecil)as harddisk_kecil,
sum(harddisk_sedang)as harddisk_sedang,
sum(harddisk_besar)as harddisk_besar,
--memory
sum(memory_kecil)as memory_kecil,
sum(memory_sedang)as memory_sedang,
sum(memory_besar)as memory_besar,
--berat
sum(berat_ringan)as berat_ringan ,
sum(berat_sedang)as berat_sedang,
sum(berat_berat)as berat_berat,
--lcd
sum(lcd_kecil)as lcd_kecil,
sum(lcd_sedang)as lcd_sedang,
sum(lcd_besar)as lcd_besar
FROM (
--harga murah-
SELECT a.laptop_id,
a.nama_laptop,
--harga
a.miu as harga_murah,
0 as harga_sedang,
0 as harga_mahal,
--processor
0 as processor_rendah,
0 as processor_sedang,
0 as processor_tinggi,
--harddisk
0 as harddisk_kecil,
0 as harddisk_sedang,
0 as harddisk_besar,
--memory
0 as memory_kecil,
0 as memory_sedang,
0 as memory_besar,
--berat
0 as berat_ringan,
0 as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='harga' and a.himpunan='murah')
--harga sedang
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
a.miu harga_sedang,
0 as harga_mahal,
--processor
0 as processor_rendah,
0 as processor_sedang,
0 as processor_tinggi,
--harddisk
0 as harddisk_kecil,
0 as harddisk_sedang,
0 as harddisk_besar,
--memory
0 as memory_kecil,
0 as memory_sedang,
0 as memory_besar,
--berat
0 as berat_ringan,
0 as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='harga' and a.himpunan='sedang')
--harga mahal
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
a.miu as harga_mahal,
--processor
0 as processor_rendah,
0 as processor_sedang,
0 as processor_tinggi,
--harddisk
0 as harddisk_kecil,
0 as harddisk_sedang,
0 as harddisk_besar,
--memory
0 as memory_kecil,
0 as memory_sedang,
0 as memory_besar,
--berat
0 as berat_ringan,
0 as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='harga' and a.himpunan='mahal')
--processor rendah
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
a.miu as processor_rendah,
0 as processor_sedang,
0 as processor_tinggi,
--harddisk
0 as harddisk_kecil,
0 as harddisk_sedang,
0 as harddisk_besar,
--memory
0 as memory_kecil,
0 as memory_sedang,
0 as memory_besar,
--berat
0 as berat_ringan,
0 as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='processor' and a.himpunan = 'rendah')
--processor sedang
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
a.miu as processor_sedang,
0 as processor_tinggi,
--harddisk
0 as harddisk_kecil,
0 as harddisk_sedang,
0 as harddisk_besar,
--memory
0 as memory_kecil,
0 as memory_sedang,
0 as memory_besar,
--berat
0 as berat_ringan,
0 as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='processor' and a.himpunan='sedang')
--processor tinggi
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
0 processor_sedang,
a.miu as processor_tinggi,
--harddisk
0 as harddisk_kecil,
0 as harddisk_sedang,
0 as harddisk_besar,
--memory
0 as memory_kecil,
0 as memory_sedang,
0 as memory_besar,
--berat
0 as berat_ringan,
0 as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='processor' and a.himpunan='tinggi')
--harddisk kecil
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
0 processor_sedang,
0 processor_tinggi,
--harddisk
a.miu as harddisk_kecil,
0 as harddisk_sedang,
0 as harddisk_besar,
--memory
0 as memory_kecil,
0 as memory_sedang,
0 as memory_besar,
--berat
0 as berat_ringan,
0 as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='harddisk' and a.himpunan='kecil')
--harddisk sedang
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
0 processor_sedang,
0 processor_tinggi,
--harddisk
0 harddisk_kecil,
a.miu as harddisk_sedang,
0 as harddisk_besar,
--memory
0 as memory_kecil,
0 as memory_sedang,
0 as memory_besar,
--berat
0 as berat_ringan,
0 as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='harddisk' and a.himpunan='sedang')
--harddisk besar
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
0 processor_sedang,
0 processor_tinggi,
--harddisk
0 harddisk_kecil,
0 harddisk_sedang,
a.miu as harddisk_besar,
--memory
0 as memory_kecil,
0 as memory_sedang,
0 as memory_besar,
--berat
0 as berat_ringan,
0 as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='harddisk' and a.himpunan='besar')
--memory kecil
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
0 processor_sedang,
0 processor_tinggi,
--harddisk
0 harddisk_kecil,
0 harddisk_sedang,
0 harddisk_besar,
--memory
a.miu as memory_kecil,
0 as memory_sedang,
0 as memory_besar,
--berat
0 as berat_ringan,
0 as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='memory' and a.himpunan='kecil')
--memory sedang
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
0 processor_sedang,
0 processor_tinggi,
--harddisk
0 harddisk_kecil,
0 harddisk_sedang,
0 harddisk_besar,
--memory
0 memory_kecil,
a.miu as memory_sedang,
0 as memory_besar,
--berat
0 as berat_ringan,
0 as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='memory' and a.himpunan='sedang')
--memory besar
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
0 processor_sedang,
0 processor_tinggi,
--harddisk
0 harddisk_kecil,
0 harddisk_sedang,
0 harddisk_besar,
--memory
0 memory_kecil,
0 memory_sedang,
a.miu as memory_besar,
--berat
0 as berat_ringan,
0 as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='memory' and a.himpunan='besar')
-- berat ringan
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
0 processor_sedang,
0 processor_tinggi,
--harddisk
0 harddisk_kecil,
0 harddisk_sedang,
0 harddisk_besar,
--memory
0 memory_kecil,
0 memory_sedang,
0 memory_besar,
--berat
a.miu as berat_ringan,
0 as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='berat' and a.himpunan='ringan')
-- berat sedang
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
0 processor_sedang,
0 processor_tinggi,
--harddisk
0 harddisk_kecil,
0 harddisk_sedang,
0 harddisk_besar,
--memory
0 memory_kecil,
0 memory_sedang,
0 memory_besar,
--berat
0 berat_ringan,
a.miu as berat_sedang,
0 as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='berat' and a.himpunan='sedang')
-- berat berat
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
0 processor_sedang,
0 processor_tinggi,
--harddisk
0 harddisk_kecil,
0 harddisk_sedang,
0 harddisk_besar,
--memory
0 memory_kecil,
0 memory_sedang,
0 memory_besar,
--berat
0 berat_ringan,
0 berat_sedang,
a.miu as berat_berat,
--lcd
0 as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='berat' and a.himpunan='berat')
--lcd kecil
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
0 processor_sedang,
0 processor_tinggi,
--harddisk
0 harddisk_kecil,
0 harddisk_sedang,
0 harddisk_besar,
--memory
0 memory_kecil,
0 memory_sedang,
0 memory_besar,
--berat
0 berat_ringan,
0 berat_sedang,
0 berat_berat,
--lcd
a.miu as lcd_kecil,
0 as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='lcd' and a.himpunan='kecil')
--lcd sedang
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
0 processor_sedang,
0 processor_tinggi,
--harddisk
0 harddisk_kecil,
0 harddisk_sedang,
0 harddisk_besar,
--memory
0 memory_kecil,
0 memory_sedang,
0 memory_besar,
--berat
0 berat_ringan,
0 berat_sedang,
0 berat_berat,
--lcd
0 lcd_kecil,
a.miu as lcd_sedang,
0 as lcd_besar
FROM vmiu a where (a.variabel='lcd' and a.himpunan='sedang')
--lcd besar
UNION
SELECT a.laptop_id,
a.nama_laptop,
--harga
0 harga_murah,
0 harga_sedang,
0 harga_mahal,
--processor
0 processor_rendah,
0 processor_sedang,
0 processor_tinggi,
--harddisk
0 harddisk_kecil,
0 harddisk_sedang,
0 harddisk_besar,
--memory
0 memory_kecil,
0 memory_sedang,
0 memory_besar,
--berat
0 berat_ringan,
0 berat_sedang,
0 berat_berat,
--lcd
0 lcd_kecil,
0 lcd_sedang,
a.miu as lcd_besar
FROM vmiu a where (a.variabel='lcd' and a.himpunan='besar')
) AS D
group by laptop_id,nama_laptop
Lampiran 6. Script query procedure AND
CREATE PROCEDURE [dbo].[sp_laptop_and]
(
@harga varchar (100),
@processor varchar (100),
@harddisk varchar (100),
@memory varchar (100),
@lcd varchar (100),
@berat varchar (100)
)
AS
BEGIN
/* Procedure body */
SELECT
A.laptop_id,
A.nama_laptop,
b.jenis_id,
b.merek_id,
A.nilai
FROM
(
SELECT
D.laptop_id,
D.nama_laptop,
MIN (D.miu) as nilai
FROM
(SELECT
a.laptop_id,
a.nama_laptop,
miu =
CASE (@harga)
WHEN 'harga_murah' THEN a.harga_murah
WHEN 'harga_sedang' THEN a.harga_sedang
WHEN 'harga_mahal' THEN a.harga_mahal
END
FROM miudetail a
UNION
SELECT
a.laptop_id,
a.nama_laptop,
miu =
CASE (@processor)
WHEN 'processor_rendah' THEN a.processor_rendah
WHEN 'processor_sedang' THEN a.processor_sedang
WHEN 'processor_tinggi' THEN a.processor_tinggi
END
FROM miudetail a
UNION
SELECT
a.laptop_id,
a.nama_laptop,
miu =
CASE (@harddisk)
WHEN 'harddisk_kecil' THEN a.harddisk_kecil
WHEN 'harddisk_sedang' THEN a.harddisk_sedang
WHEN 'harddisk_besar' THEN a.harddisk_besar
END
FROM miudetail a
UNION
SELECT
a.laptop_id,
a.nama_laptop,
miu =
CASE (@memory)
WHEN 'memory_kecil' THEN a.memory_kecil
WHEN 'memory_sedang' THEN a.memory_sedang
WHEN 'memory_besar' THEN a.memory_besar
END
FROM miudetail a
UNION
SELECT
a.laptop_id,
a.nama_laptop,
miu =
CASE (@lcd)
WHEN 'lcd_kecil' THEN a.lcd_kecil
WHEN 'lcd_sedang' THEN a.lcd_sedang
WHEN 'lcd_besar' THEN a.lcd_besar
END
FROM miudetail a
UNION
SELECT
a.laptop_id,
a.nama_laptop,
miu =
CASE (@berat)
WHEN 'berat_ringan' THEN a.berat_ringan
WHEN 'berat_sedang' THEN a.berat_sedang
WHEN 'berat_berat' THEN a.berat_berat
END
FROM miudetail a ) as D
GROUP BY
D.laptop_id,
D.nama_laptop) as A
LEFT JOIN M_Laptop b on A.laptop_id = b.laptop_id
WHERE A.nilai <> 0
END
Lampiran 7. Script query procedure OR
CREATE PROCEDURE dbo.sp_laptop_or
(
@harga varchar (100),
@processor varchar (100),
@harddisk varchar (100),
@memory varchar (100),
@lcd varchar (100),
@berat varchar (100)
)
AS
BEGIN
/* Procedure body */
SELECT
A.laptop_id,
A.nama_laptop,
b.jenis_id,
b.merek_id,
A.nilai
FROM
(
SELECT
D.laptop_id,
D.nama_laptop,
MAX (D.miu) as nilai
FROM
(SELECT
a.laptop_id,
a.nama_laptop,
miu =
CASE (@harga)
WHEN 'harga_murah' THEN a.harga_murah
WHEN 'harga_sedang' THEN a.harga_sedang
WHEN 'harga_mahal' THEN a.harga_mahal
END
FROM miudetail a
UNION
SELECT
a.laptop_id,
a.nama_laptop,
miu =
CASE (@processor)
WHEN 'processor_rendah' THEN a.processor_rendah
WHEN 'processor_sedang' THEN a.processor_sedang
WHEN 'processor_tinggi' THEN a.processor_tinggi
END
FROM miudetail a
UNION
SELECT
a.laptop_id,
a.nama_laptop,
miu =
CASE (@harddisk)
WHEN 'harddisk_kecil' THEN a.harddisk_kecil
WHEN 'harddisk_sedang' THEN a.harddisk_sedang
WHEN 'harddisk_besar' THEN a.harddisk_besar
END
FROM miudetail a
UNION
SELECT
a.laptop_id,
a.nama_laptop,
miu =
CASE (@memory)
WHEN 'memory_kecil' THEN a.memory_kecil
WHEN 'memory_sedang' THEN a.memory_sedang
WHEN 'memory_besar' THEN a.memory_besar
END
FROM miudetail a
UNION
SELECT
a.laptop_id,
a.nama_laptop,
miu =
CASE (@lcd)
WHEN 'lcd_kecil' THEN a.lcd_kecil
WHEN 'lcd_sedang' THEN a.lcd_sedang
WHEN 'lcd_besar' THEN a.lcd_besar
END
FROM miudetail a
UNION
SELECT
a.laptop_id,
a.nama_laptop,
miu =
CASE (@berat)
WHEN 'berat_ringan' THEN a.berat_ringan
WHEN 'berat_sedang' THEN a.berat_sedang
WHEN 'berat_berat' THEN a.berat_berat
END
FROM miudetail a ) as D
GROUP BY
D.laptop_id,
D.nama_laptop) as A
LEFT JOIN M_Laptop b on A.laptop_id = b.laptop_id
WHERE A.nilai <> 0
END
Untuk pembuatan aplikasi saya menggunakan visual studio C#
Thanks