Fikry fitriansyah: Perancangan Basis Data Sql Server 2012 dan Website pada Asp.Net dengan Metode Normalisasi

Pada postingan kali ini saya menulis tentang perancangan basis data (database) dari sebuah struk belanja. Perancangannya melewati beberapa tahap diantaranya Unnormalized, 1NF, 2NF, 3NF & Relasi. setelah membuat perancangan lanjut ke tahap penerapannya yaitu dengan membuat tabel - tabel pada software Sql Server 2012. setelah tabel-tabel tersebut selesai selanjutnya buat project web dengan visual studio (ASP.NET) lalu mengkoneksikan database tersebut, pada database juga saya berikan perintah - perintah yaitu simpan, edit, hapus dan cari. Untuk lebih jelasnya saya akan menjelaskan sebagai berikut :

Ini Struk Belanja yang saya gunakan sebagai bahan

1. Membuat Basis data menggunakan Metode Normalisasi

A. Bentuk Unnormalized

Bentuk ini merupakan kondisi data apa adanya, tidak mengikuti suatu format tertentu termasuk ketidak lengkapan ataupun redundansi

B. Bentuk Normal 1 (1NF)
Bentuk normal pertama jika relasi tidak mengandung atribut yang bernilai ganda harus mengandung 1 nilai) dan harus diisi datanya (tidak boleh kosong)

C. Bentuk Normal 2 (2NF)
Telah memenuhi 1NF

D. Bentuk Normal 3 (3NF)
Telah memenuhi 2NF. Semua atribut bukan primer tidak punya hubungan yang transitif (tidak ada dependensi transitif).

E. Tabel Relasi
Semua tabel yang ada di 3NF dihubungkan.

2. Setelah melewati tahap normalisasi kita lanjut membuat Basis Data (DATABASE) pada Sql Server 2012 sesuai dengan bentuk normal 3 (3NF).

3. Setelah database selesai dibuat. langkah selanjutnya membuat website pada visual studio (ASP.Net)

Tampilan Awal, yang nantinya apabila di klik akan memunculkan Tabel-tabel.

Source Code Tampilan Awal

Tabel Kasir

Source Code Tabel Kasir

Imports System.Data.Sql
Imports System.Data.SqlClient
Partial Class Kasir
    Inherits System.Web.UI.Page
    Public CON As SqlConnection
    Public CMD As SqlCommand
    Public DR As SqlDataReader

    Sub Clear()
        TextKode.Text = ""
        TextNamaKasir.Text = ""
    End Sub

    Sub Koneksi()
        Try
            CON = New SqlConnection("Data Source=DESKTOP-92PA757;Initial Catalog=Fikry-fitriansyah;Integrated Security=True")
            CON.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Protected Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
        Call Koneksi()
        Dim Save As String
        Save = "insert into Table_Kasir values('" & TextKode.Text & "','" & TextNamaKasir.Text & "')"
        CMD = New SqlCommand(Save, CON)
        CMD.ExecuteNonQuery()
        MsgBox("Success")
        Response.Redirect("Kasir.aspx")
        Call Clear()
    End Sub

    Protected Sub BtnEdit_Click(sender As Object, e As EventArgs) Handles BtnEdit.Click
        Call Koneksi()
        CMD = New SqlCommand("update Table_Kasir set Nama_Kasir='" & TextNamaKasir.Text & "'where Kode_Kasir='" & TextKode.Text & "'", CON)
        CMD.ExecuteNonQuery()
        MsgBox("Success")
        Response.Redirect("Kasir.aspx")
        Call Clear()
    End Sub

    Protected Sub BtnHapus_Click(sender As Object, e As EventArgs) Handles BtnHapus.Click
        Call Koneksi()
        CMD = New SqlCommand("delete from Table_Kasir where Kode_Kasir='" & TextKode.Text & "'", CON)
        CMD.ExecuteNonQuery()
        MsgBox("Success")
        Response.Redirect("Kasir.aspx")
        Call Clear()
    End Sub

    Protected Sub BtnKembali_Click(sender As Object, e As EventArgs) Handles BtnKembali.Click
        Response.Redirect("Awal.aspx")
    End Sub

    Protected Sub BtnCari_Click(sender As Object, e As EventArgs) Handles BtnCari.Click
        Call Koneksi()
        CMD = New SqlCommand("select * from Table_Kasir where Kode_Kasir='" + TextKode.Text + "'", CON)
        DR = CMD.ExecuteReader
        If DR.HasRows Then
            DR.Read()
            TextKode.Text = DR("Kode_Kasir")
            TextNamaKasir.Text = DR("Nama_Kasir")
        Else
            MsgBox("Data Tidak Ada")
            Call Clear()
        End If
    End Sub

    Protected Sub BtnBatal_Click(sender As Object, e As EventArgs) Handles BtnBatal.Click
        Call Clear()
    End Sub
End Class

Tabel Struk

Source Code Tabel Struk

Imports System.Data.Sql
Imports System.Data.SqlClient
Partial Class Struk
    Inherits System.Web.UI.Page
    Public CON As SqlConnection
    Public CMD As SqlCommand
    Public DR As SqlDataReader

    Sub Clear()
        TextKode.Text = ""
        TextNamaKasir.Text = ""
        TextTgl.Text = ""
        TextWaktu.Text = ""
    End Sub

    Sub Koneksi()
        Try
            CON = New SqlConnection("Data Source=DESKTOP-92PA757;Initial Catalog=Fikry-fitriansyah;Integrated Security=True")
            CON.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Protected Sub BtnSave1_Click(sender As Object, e As EventArgs) Handles BtnSave1.Click
        Call Koneksi()
        Dim Save As String
        Save = "insert into Table_Struk values('" & TextKode.Text & "','" & TextNamaKasir.Text & "','" & TextTgl.Text &
        "','" & TextWaktu.Text & "')"
        CMD = New SqlCommand(Save, CON)
        CMD.ExecuteNonQuery()
        MsgBox("Success")
        Response.Redirect("Struk.aspx")
        Call Clear()
    End Sub

    Protected Sub BtnEdit1_Click(sender As Object, e As EventArgs) Handles BtnEdit1.Click
        Call Koneksi()
        CMD = New SqlCommand("update Table_Struk set Nama_Kasir='" & TextNamaKasir.Text & "',Tanggal='" & TextTgl.Text & "',Waktu='" & TextWaktu.Text &
        "'where Kode_Kasir='" & TextKode.Text & "'", CON)
        CMD.ExecuteNonQuery()
        MsgBox("Success")
        Response.Redirect("Struk.aspx")
        Call Clear()
    End Sub

    Protected Sub BtnHapus1_Click(sender As Object, e As EventArgs) Handles BtnHapus1.Click
        Call Koneksi()
        CMD = New SqlCommand("delete from Table_Struk where Nama_Kasir='" & TextNamaKasir.Text & "'", CON)
        CMD.ExecuteNonQuery()
        MsgBox("Success")
        Response.Redirect("Struk.aspx")
        Call Clear()
    End Sub

    Protected Sub BtnBatal1_Click(sender As Object, e As EventArgs) Handles BtnBatal1.Click
        Call Clear()
    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Response.Redirect("Awal.aspx")
    End Sub

    Protected Sub BtnCari_Click(sender As Object, e As EventArgs) Handles BtnCari.Click
        Call Koneksi()
        CMD = New SqlCommand("select * from Table_Struk where Kode_Kasir='" + TextKode.Text + "'", CON)
        DR = CMD.ExecuteReader
        If DR.HasRows Then
            DR.Read()
            TextKode.Text = DR("Kode_Kasir")
            TextNamaKasir.Text = DR("Nama_Kasir")
            TextTgl.Text = DR("Tanggal")
            TextWaktu.Text = DR("Waktu")
        Else
            MsgBox("Data Tidak Ada")
            Call Clear()
        End If
    End Sub
End Class

Tabel Transaksi

Source Code Tabel Jual Beli

Imports System.Data.Sql
Imports System.Data.SqlClient
Partial Class Transaksi
    Inherits System.Web.UI.Page
    Public CON As SqlConnection
    Public CMD As SqlCommand
    Public DR As SqlDataReader

    Sub Clear()
        TextKode.Text = ""
        TextNamaBrg.Text = ""
        TextQty.Text = ""
        TextHarga.Text = ""
        TextJumlah.Text = ""
    End Sub

    Sub Koneksi()
        Try
            CON = New SqlConnection("Data Source=DESKTOP-92PA757;Initial Catalog=Fikry-fitriansyah;Integrated Security=True")
            CON.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Protected Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
        Call Koneksi()
        Dim Save As String
        Save = "insert into Table_Transaksi values('" & TextKode.Text & "','" & TextNamaBrg.Text & "','" & TextQty.Text &
        "','" & TextHarga.Text & "','" & TextJumlah.Text & "')"
        CMD = New SqlCommand(Save, CON)
        CMD.ExecuteNonQuery()
        MsgBox("Success")
        Response.Redirect("Transaksi.aspx")
        Call Clear()
    End Sub

    Protected Sub BtnJumlah_Click(sender As Object, e As EventArgs) Handles BtnJumlah.Click
        TextJumlah.Text = TextQty.Text * TextHarga.Text
    End Sub

    Protected Sub BtnEdit_Click(sender As Object, e As EventArgs) Handles BtnEdit.Click
        Call Koneksi()
        CMD = New SqlCommand("update Table_Transaksi set Kode_Kasir='" & TextKode.Text & "',Qty='" & TextQty.Text & "',Harga='" & TextHarga.Text &
        "',Jumlah='" & TextJumlah.Text & "'where Nama_Barang='" & TextNamaBrg.Text & "'", CON)
        CMD.ExecuteNonQuery()
        MsgBox("Success")
        Response.Redirect("Transaksi.aspx")
        Call Clear()
    End Sub

    Protected Sub BtnHapus_Click(sender As Object, e As EventArgs) Handles BtnHapus.Click
        Call Koneksi()
        CMD = New SqlCommand("delete from Table_Transaksi where Nama_Barang='" & TextNamaBrg.Text & "'", CON)
        CMD.ExecuteNonQuery()
        MsgBox("Success")
        Response.Redirect("Transaksi.aspx")
        Call Clear()
    End Sub

    Protected Sub BtnBatal_Click(sender As Object, e As EventArgs) Handles BtnBatal.Click
        Call Clear()
    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles BtnKembali.Click
        Response.Redirect("Awal.aspx")
    End Sub

    Protected Sub BtnCari_Click(sender As Object, e As EventArgs) Handles BtnCari.Click
        Call Koneksi()
        CMD = New SqlCommand("select * from Table_Transaksi where Nama_Barang='" + TextNamaBrg.Text + "'", CON)
        DR = CMD.ExecuteReader
        If DR.HasRows Then
            DR.Read()
            TextKode.Text = DR("Kode_Kasir")
            TextNamaBrg.Text = DR("Nama_Barang")
            TextQty.Text = DR("Qty")
            TextHarga.Text = DR("Harga")
            TextJumlah.Text = DR("Jumlah")
        Else
            MsgBox("Data Tidak Ada")
            Call Clear()
        End If
    End Sub
End Class

Tabel Barang

Source Code Tabel Barang

Imports System.Data.Sql
Imports System.Data.SqlClient
Partial Class Barang
    Inherits System.Web.UI.Page
    Public CON As SqlConnection
    Public CMD As SqlCommand
    Public DR As SqlDataReader

    Sub Clear()
        TextNamaBrg.Text = ""
        TextQty.Text = ""
        TextHarga.Text = ""
        TextJumlah.Text = ""
    End Sub

    Sub Koneksi()
        Try
            CON = New SqlConnection("Data Source=DESKTOP-92PA757;Initial Catalog=Fikry-fitriansyah;Integrated Security=True")
            CON.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Protected Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
        Call Koneksi()
        Dim Save As String
        Save = "insert into Table_Barang values('" & TextNamaBrg.Text & "','" & TextQty.Text & "','" & TextHarga.Text & "','" & TextJumlah.Text & "')"
        CMD = New SqlCommand(Save, CON)
        CMD.ExecuteNonQuery()
        MsgBox("Success")
        Response.Redirect("Barang.aspx")
        Call Clear()
    End Sub

    Protected Sub BtnJumlah_Click(sender As Object, e As EventArgs) Handles BtnJumlah.Click
        TextJumlah.Text = TextQty.Text * TextHarga.Text
    End Sub

    Protected Sub BtnEdit_Click(sender As Object, e As EventArgs) Handles BtnEdit.Click
        Call Koneksi()
        CMD = New SqlCommand("update Table_Barang set Qty='" & TextQty.Text & "',Harga='" & TextHarga.Text &
        "',Jumlah='" & TextJumlah.Text & "'where Nama_Barang='" & TextNamaBrg.Text & "'", CON)
        CMD.ExecuteNonQuery()
        MsgBox("Success")
        Response.Redirect("Barang.aspx")
        Call Clear()
    End Sub

    Protected Sub BtnHapus_Click(sender As Object, e As EventArgs) Handles BtnHapus.Click
        Call Koneksi()
        CMD = New SqlCommand("delete from Table_Barang where Nama_Barang='" & TextNamaBrg.Text & "'", CON)
        CMD.ExecuteNonQuery()
        MsgBox("Success")
        Response.Redirect("Barang.aspx")
        Call Clear()
    End Sub

    Protected Sub BtnBatal_Click(sender As Object, e As EventArgs) Handles BtnBatal.Click
        Call Clear()
    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles BtnKembali.Click
        Response.Redirect("Awal.aspx")
    End Sub

    Protected Sub BtnCari_Click(sender As Object, e As EventArgs) Handles BtnCari.Click
        Call Koneksi()
        CMD = New SqlCommand("select * from Table_Barang where Nama_Barang='" + TextNamaBrg.Text + "'", CON)
        DR = CMD.ExecuteReader
        If DR.HasRows Then
            DR.Read()
            TextNamaBrg.Text = DR("Nama_Barang")
            TextQty.Text = DR("Qty")
            TextHarga.Text = DR("Harga")
            TextJumlah.Text = DR("Jumlah")
        Else
            MsgBox("Data Tidak Ada")
            Call Clear()
        End If
    End Sub
End Class

Sekian Pembuatan Perancangan Basis Data Pada Sql Server 2012 dan Website pada Asp.Net Menggunakan Metode Normalisasi

Terima Kasih

Fikry fitriansyah

Rabu, 24 April 2019

Perancangan Basis Data Sql Server 2012 dan Website pada Asp.Net dengan Metode Normalisasi

Tidak ada komentar:

Posting Komentar

Arsip Blog