Tugas UTS Basis Data STMIK ERESHA Kelas 05TPLP001

Welcome to Jima's Blog.

Tugas kali ini adalah membuat normalisasi database dari sebuah faktur dan membuat web design serta menampilkan hasil dari database tersebut kedalam web menggunakan Visual Studio 2012 dan juga Microsoft Office Access 2007.

Foto dari Faktur yang akan di normalisasi :

1. Normal Form (1NF)

Bentuk normal form yang pertama atau 1NF mensyaratkan beberapa kondisi dalam sebuah database, berikut adalah fungsi dari bentuk normal pertama ini.

• Menghilangkan duplikasi kolom dari tabel yang sama.
• Buat tabel terpisah untuk masing-masing kelompok data terkait dan mengidentifikasi setiap baris dengan kolom yang unik (Primary Key).

Contoh normalisasi database bentuk 1NF:

dari manual bon pembelian diatas kita dapat menjadi bentuk normal pertama dengan memisah-misahkan data pada atribut-atribut yang tepat dan bernilai atomik, juga seluruh record / baris harus lengkap adanya.

2. Normal Form (2NF)

Syarat untuk menerapkan normalisasi bentuk kedua ini adalah data telah dibentuk dalam 1NF, berikut adalah beberapa fungsi normalisasi 2NF.

• Menghapus beberapa subset data yang ada pada tabel dan menempatkan mereka pada tabel terpisah.
• Menciptakan hubungan antara tabel baru dan tabel lama dengan menciptakan foreign key.
• Tidak ada atribut dalam tabel yang secara fungsional bergantung pada candidate key tabel tersebut.

Contoh normalisasi database bentuk 2NF:

Bentuk normal kedua dengan melakukan dekomposisi tabel diatas menjadi beberapa tabel dan mencari kunci primer dari tiap-tiap tabel tersebut dan atribut kunci haruslah unik.

3. Normal Form (3NF)

Normalisasi database dalam bentuk 3NF bertujuan untuk menghilangkan seluruh atribut atau field yang tidak berhubungan dengan primary key. Dengan demikian tidak ada ketergantungan transitif pada setiap kandidat key. Syarat dari bentuk normal ketiga atau 3NF adalah :

• Memenuhi semua persyaratan dari bentuk normal kedua.
• Menghapus kolom yang tidak tergantung pada primary key.

Contoh Normalisasi Database Bentuk 3NF

Bentuk normal ketiga mempunyai syarat, setiap relasi tidak mempunyai atribut yang bergantung transitif, harus bergantung penuh pada kunci utama dan harus memenuhi bentuk normal kedua (2 NF).

Screen Shoot Table Hasil Analisa

Screenshot Web + Source Code.

Transaksi:

Imports System.Data

Imports System.Data.OleDb

Public Class transaksi

    Inherits System.Web.UI.Page

    Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\File Kampus\Semester 5\Selasa\Basis Data\Tugas\UTS\dbinvoice.mdb"

    Dim con As New OleDb.OleDbConnection(constring)

    Dim reader As OleDb.OleDbDataReader

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btninput_Click(sender As Object, e As EventArgs) Handles btninput.Click

        con.Open()

        Dim add As String = "INSERT INTO tbl_transaksi VALUES('" + txtinvoice.Text + "','" + txtpenjual.Text + "','" + txttgl.Text + "','" + txtshipping.Text + "','" + txtnamacustomer.Text + "','" + txtalamatcustomer.Text + "','" + txttlp.Text + "','" + txtindexid.Text + "','" + txtproduk.Text + "','" + txtqty.Text + "','" + txthargasatuan.Text + "','" + txthargatotal.Text + "','" + txttotalitem.Text + "','" + txtongkir.Text + "','" + txttotal.Text + "')"

        Dim cmd = New OleDbCommand(add, con)

        cmd.ExecuteNonQuery()

        con.Close()

        MsgBox("Berhasil Menambahkan List Data!")

    End Sub

    Protected Sub btnedit_Click(sender As Object, e As EventArgs) Handles btnedit.Click

        con.Open()

        Dim edit As String = "UPDATE tbl_transaksi set no_invoice ='" + txtinvoice.Text + "' WHERE nama_penjual = '" + txtpenjual.Text + "'"

        Dim cmd = New OleDbCommand(edit, con)

        cmd.ExecuteNonQuery()

        con.Close()

        MsgBox("Berhasil Mengubah Data!")

    End Sub

    Protected Sub btndelete_Click(sender As Object, e As EventArgs) Handles btndelete.Click

        con.Open()

        Dim delete As String = "DELETE * From tbl_transaksi WHERE nama_penjual = '" + txtpenjual.Text + "'"

        Dim cmd = New OleDbCommand(delete, con)

        cmd.ExecuteNonQuery()

        con.Close()

        MsgBox("Berhasil Menghapus Data!")

    End Sub

    Protected Sub btnfind_Click(sender As Object, e As EventArgs) Handles btnfind.Click

        If Not Len(txtpenjual.Text) = 0 Then

            con.Close()

            con.Open()

            Dim cmd As New OleDb.OleDbCommand("SELECT * From tbl_transaksi WHERE nama_penjual = '" + txtpenjual.Text + "'", con)

            reader = cmd.ExecuteReader()

            If reader.HasRows Then

                reader.Read()

                txtinvoice.Text = reader("no_invoice")

                txtpenjual.Text = reader("nama_penjual")

                txttgl.Text = reader("tanggal")

                txtshipping.Text = reader("shipping")

                txtnamacustomer.Text = reader("nama_Customer")

                txtalamatcustomer.Text = reader("alamat")

                txttlp.Text = reader("no_tlp")

                txtindexid.Text = reader("index_id")

                txtproduk.Text = reader("deskripsi_produk")

                txtqty.Text = reader("qty")

                txthargasatuan.Text = reader("harga_Satuan")

                txthargatotal.Text = reader("harga_total")

                txttotalitem.Text = reader("total_item")

                txtongkir.Text = reader("ongkir")

                txttotal.Text = reader("total")

                btndelete.Enabled = True

                btnedit.Enabled = True

                MsgBox("Data Ditemukan!")

            Else

                MsgBox("Data Tidak Ditemukan!")

            End If

        End If

    End Sub

    Protected Sub btnclear_Click(sender As Object, e As EventArgs) Handles btnclear.Click

        txtinvoice.Text = ""

        txtpenjual.Text = ""

        txttgl.Text = ""

        txtshipping.Text = ""

        txtnamacustomer.Text = ""

        txtalamatcustomer.Text = ""

        txttlp.Text = ""

        txtindexid.Text = ""

        txtproduk.Text = ""

        txtqty.Text = ""

        txthargasatuan.Text = ""

        txthargatotal.Text = ""

        txttotalitem.Text = ""

        txtongkir.Text = ""

        txttotal.Text = ""

    End Sub

End Class

Customer:

Imports System.Data

Imports System.Data.OleDb

Public Class customer

    Inherits System.Web.UI.Page

    Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\File Kampus\Semester 5\Selasa\Basis Data\Tugas\UTS\dbinvoice.mdb"

    Dim con As New OleDb.OleDbConnection(constring)

    Dim reader As OleDb.OleDbDataReader

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btninput_Click(sender As Object, e As EventArgs) Handles btninput.Click

        Dim add As String = "INSERT INTO tbl_customer values ('" + txtemail.Text + " ', '" + txtnama.Text + "','" + txtalamat.Text + "','" + txttlp.Text + "')"

        Dim cmd = New OleDbCommand(add, con)

        cmd.ExecuteNonQuery()

        con.Close()

        MsgBox("Berhasil Menambahkan Data!")

    End Sub

    Protected Sub btnedit_Click(sender As Object, e As EventArgs) Handles btnedit.Click

        Dim edit As String = "update tbl_customer set email = '" + txtemail.Text + "' where nama_customer = '" + txtnama.Text + "'"

        Dim cmd = New OleDbCommand(edit, con)

        cmd.ExecuteNonQuery()

        con.Close()

        MsgBox("Berhasil Mengubah Data!")

    End Sub

    Protected Sub btndelete_Click(sender As Object, e As EventArgs) Handles btndelete.Click

        con.Open()

        Dim delete As String = "DELETE * From tbl_customer WHERE email = '" + txtemail.Text + "'"

        Dim cmd = New OleDbCommand(delete, con)

        cmd.ExecuteNonQuery()

        con.Close()

        MsgBox("Berhasil Menghapus Data!")

    End Sub

    Protected Sub btnfind_Click(sender As Object, e As EventArgs) Handles btnfind.Click

        If Not Len(txtemail.Text) = 0 Then

            con.Close()

            con.Open()

            Dim cmd As New OleDb.OleDbCommand("SELECT * From tbl_customer WHERE email = '" + txtemail.Text + "'", con)

            reader = cmd.ExecuteReader()

            If reader.HasRows Then

                reader.Read()

                txtnama.Text = reader("nama_customer")

                txtalamat.Text = reader("alamat")

                txtemail.Text = reader("email")

                txttlp.Text = reader("no_tlp")

                MsgBox("Data Ditemukan!")

            Else

                MsgBox("Data Tidak Ditemukan!")

            End If

        End If

    End Sub

    Protected Sub btnclear_Click(sender As Object, e As EventArgs) Handles btnclear.Click

        txtemail.Text = ""

        txtnama.Text = ""

        txtalamat.Text = ""

        txttlp.Text = ""

    End Sub

End Class

Barang:

Imports System.Data

Imports System.Data.OleDb

Public Class barang

    Inherits System.Web.UI.Page

    Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\File Kampus\Semester 5\Selasa\Basis Data\Tugas\UTS\dbinvoice.mdb"

    Dim con As New OleDb.OleDbConnection(constring)

    Dim reader As OleDb.OleDbDataReader

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btninput_Click(sender As Object, e As EventArgs) Handles btninput.Click

        Dim add As String = "INSERT INTO tbl_barang values ('" + txtindex.Text + " ', '" + txtproduk.Text + "','" + txtharga.Text + "','" + txtstok.Text + "')"

        Dim cmd = New OleDbCommand(add, con)

        cmd.ExecuteNonQuery()

        con.Close()

        MsgBox("Berhasil Menambahkan Data!")

    End Sub

    Protected Sub btnedit_Click(sender As Object, e As EventArgs) Handles btnedit.Click

        Dim edit As String = "update tbl_barang set harga = '" + txtharga.Text + "' where deskripsi_produk = '" + txtproduk.Text + "'"

        Dim cmd = New OleDbCommand(edit, con)

        cmd.ExecuteNonQuery()

        con.Close()

        MsgBox("Berhasil Mengubah Data!")

    End Sub

    Protected Sub btndelete_Click(sender As Object, e As EventArgs) Handles btndelete.Click

        con.Open()

        Dim delete As String = "DELETE * From tbl_barang WHERE deskripsi_produk = '" + txtproduk.Text + "'"

        Dim cmd = New OleDbCommand(delete, con)

        cmd.ExecuteNonQuery()

        con.Close()

        MsgBox("Berhasil Menghapus Data!")

    End Sub

    Protected Sub btnclear_Click(sender As Object, e As EventArgs) Handles btnclear.Click

        txtindex.Text = ""

        txtproduk.Text = ""

        txtharga.Text = ""

        txtstok.Text = ""

    End Sub

    Protected Sub btnfind_Click(sender As Object, e As EventArgs) Handles btnfind.Click

        If Not Len(txtproduk.Text) = 0 Then

            con.Close()

            con.Open()

            Dim cmd As New OleDb.OleDbCommand("SELECT * From tbl_barang WHERE deskripsi_produk = '" + txtproduk.Text + "'", con)

            reader = cmd.ExecuteReader()

            If reader.HasRows Then

                reader.Read()

                txtindex.Text = reader("index_id")

                txtproduk.Text = reader("deskripsi_produk")

                txtharga.Text = reader("harga")

                txtstok.Text = reader("stok")

                MsgBox("Data Ditemukan!")

            Else

                MsgBox("Data Tidak Ditemukan!")

            End If

        End If

    End Sub

End Class

Toko:

Imports System.Data

Imports System.Data.OleDb

Public Class WebForm2

    Inherits System.Web.UI.Page

    Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\File Kampus\Semester 5\Selasa\Basis Data\Tugas\UTS\dbinvoice.mdb"

    Dim con As New OleDb.OleDbConnection(constring)

    Dim reader As OleDb.OleDbDataReader

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btninput_Click(sender As Object, e As EventArgs) Handles btninput.Click

        con.Open()

        Dim add As String = "INSERT INTO tbl_toko VALUES('" + txtkode.Text + "','" + txtnama.Text + "','" + txtalamat.Text + "','" + txtemail.Text + "','" + txttlp.Text + "')"

        Dim cmd = New OleDbCommand(add, con)

        cmd.ExecuteNonQuery()

        con.Close()

        MsgBox("Berhasil Menambahkan List Data!")

    End Sub

    Protected Sub btnedit_Click(sender As Object, e As EventArgs) Handles btnedit.Click

        con.Open()

        Dim edit As String = "update tbl_toko set email_penjual = '" + txtemail.Text + "' where nama_penjual = '" + txtnama.Text + "'"

        Dim cmd = New OleDbCommand(edit, con)

        cmd.ExecuteNonQuery()

        con.Close()

        MsgBox("Berhasil Mengubah Data!")

    End Sub

    Protected Sub btndelete_Click(sender As Object, e As EventArgs) Handles btndelete.Click

        con.Open()

        Dim delete As String = "DELETE * From tbl_toko WHERE kd_penjual = '" + txtkode.Text + "'"

        Dim cmd = New OleDbCommand(delete, con)

        cmd.ExecuteNonQuery()

        con.Close()

        MsgBox("Berhasil Menghapus Data!")

    End Sub

    Protected Sub btnclear_Click(sender As Object, e As EventArgs) Handles btnclear.Click

        txtemail.Text = ""

        txtnama.Text = ""

        txtkode.Text = ""

        txtalamat.Text = ""

        txttlp.Text = ""

    End Sub

    Protected Sub btnfind_Click(sender As Object, e As EventArgs) Handles btnfind.Click

        If Not Len(txtemail.Text) = 0 Then

            con.Close()

            con.Open()

            Dim cmd As New OleDb.OleDbCommand("SELECT * From tbl_toko WHERE email_penjual = '" + txtemail.Text + "'", con)

            reader = cmd.ExecuteReader()

            If reader.HasRows Then

                reader.Read()

                txtkode.Text = reader("kd_penjual")

                txtnama.Text = reader("nama_penjual")

                txtalamat.Text = reader("alamat_penjual")

                txtemail.Text = reader("email_penjual")

                txttlp.Text = reader("tlp_penjual")

                MsgBox("Data Ditemukan!")

            Else

                MsgBox("Data Tidak Ditemukan!")

            End If

        End If

    End Sub

End Class

Sekian Postingan Saya kali ini, kurang lebihnya mohon maaf.

Wassallam :)