UAS Lab SMBD

TabelWaiter

JadwalReservasi

Database ini adalah database restoran dalam jadwal reservasi tempat oleh pelanggan dan siapa yang akan menjadi waiter utama dalam melayani pelanggan tersebut.

Trigger:
Jika ada pelanggan baru yang mereservasi tempat.
CREATE TRIGGER TambahJadwal on JadwalReservasi
for insert
as
update data set data.JamReservasi = data.JamReservasi = mdl.JamReservasi
from JadwalReservasi mdl join inserted data on data.KodeWaiter = mdl.KodeWaiter

Stored Procedure:
 CREATE PROCEDURE TambahWaiter
 @KodeWaiter Short Text(50), @Nama Short Text (50), @Alamat Short Text (50)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO DataWaiter
(
KodeWaiter, Nama, Alamat
)
VALUES
(
@KodeWaiter, @Nama, @Alamat
)
 END

UTS Lab. SMBD

1NF
Table_Penjualan (ID_Pelanggan, Nama_Pelanggan, ID_Merk, Merk_Barang, Kondisi_Barang, Harga_Barang)

2NF dan 3NF
Table_Penjualan (ID_Pelanggan, Nama_Pelanggan, ID_Merk, Kondisi_Barang)
Table_Harga (Kondisi_Barang, Harga_Barang)
Table_Barang (ID_Merk, Merk_Barang)

Database:






WebForm:

Source:

Imports System.Data
Imports System.Data.OleDb

Public Class WebForm2
    Inherits System.Web.UI.Page

    Public constring As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    Public oConn As New OleDbConnection(constring)
    Public oTbl As New DataTable
    Public xReader As OleDbDataReader

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

    End Sub

    Protected Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        oConn.Close()
        oConn.Open()
        Dim Cmd As New OleDbCommand("SELECT * FROM Table_Penjualan where ID_Pelanggan ='" + TxtIDPelanggan.Text + "'", oConn)
        xReader = Cmd.ExecuteReader
        If xReader.HasRows Then
            xReader.Read()
            TxtNamaPelanggan.Text = xReader("Nama_Pelanggan")
            TxtIDMerk.Text = xReader("ID_Merk")
            TxtKondisiBrg.Text = xReader("Kondisi_Barang")


        Else
            MsgBox("Kode Buku Tidak Ada")
            TxtIDPelanggan.Text = ""
            TxtNamaPelanggan.Text = ""
            TxtIDMerk.Text = ""
            TxtKondisiBrg.Text = ""

            Exit Sub
        End If
        xReader.Close()


    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim sql As String = "INSERT INTO Tabel_Pelanggan VALUES ('" & TxtIDPelanggan.Text & "','" & TxtNamaPelanggan.Text & "','" & TxtIDMerk.Text & "','" & TxtKondisiBrg.Text & "')"
        Dim oCmd As New OleDbCommand
        oConn.Open()
        oCmd.Connection = oConn
        oCmd.CommandText = sql
        oCmd.ExecuteNonQuery()
    End Sub

    Protected Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim sql As String = " UPDATE Tabel_Pelanggan set Nama_Pelanggan  ='" & TxtNamaPelanggan.Text & "', ID_Merk = " & TxtIDMerk.Text & ", Kondisi_Barang = '" & TxtKondisiBrg.Text & "' where ID_Pelanggan = '" & TxtIDPelanggan.Text & "'"

        Dim oCmd As New OleDbCommand
        oConn.Close()
        oConn.Open()
        oCmd.Connection = oConn
        oCmd.CommandText = sql

        oCmd.ExecuteNonQuery()

    End Sub

    Protected Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim hapus = MsgBox("Konfirmasi", MsgBoxStyle.YesNo, "Hapus")

        If hapus = vbYes Then
            oConn.Close()
            oConn.Open()
            Dim delet As String = "DELETE FROM Tabel_Pelanggan where ID_Pelanggan ='" + TxtIDPelanggan.Text + "'"
            Dim oCmd As New OleDbCommand
            oConn.Close()
            oConn.Open()
            oCmd.Connection = oConn
            oCmd.CommandText = delet
            oCmd.ExecuteNonQuery()
            MsgBox("Sudah terhapus", vbArchive)

            TxtIDPelanggan.Text = ""
            TxtNamaPelanggan.Text = ""
            TxtIDMerk.Text = ""
            TxtKondisiBrg.Text = ""

        End If

    End Sub

    Protected Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Response.Redirect("WebForm1.aspx")
    End Sub
End Class