Database purchase order system.

hello guys, back with me again Chris.

I will report our group project about the database purchase order system.

This project has made by me, Halim, Jacky, Michael, and Sandy It is about making the purchase order in database. And we will explain about that further.

Features of database in purchase order system:

• Directly Integrated with Accounts Payable
• Portable Market Purchase Order system using a laptop PC
• Matrix Capability for tracking by size, color, width, etc.
• EMT Purchase Order System (allows SKU and matrix purchase orders to be entered on one screen)
• Automatic creation of inventory record when new item is ordered (ability to sell items not yet on the floor)
• Vendor Look Up by Name or Number
• Inventory Lookup by multiple criteria simultaneously
• Complete or partial purchase order receiving
• Creation and editing of Purchase Order and Vendor Notes
• Automatic Red Line Purchase Order creation
• Tracking of vendor returns with reason for return
• Purchase orders printed with item detail for verification
• Receive from packing slip and update accounts payable when invoice is received

1nf

all item in purchase order need to be stored in one table, create all of the artribute/column in one table.

2nf

all of the first column name has number aka primary key, we split it become different table, but the purchase order table and customer  cannot be split because the purchase order come from customer

well 1 customer can make a few purchases, but it is no need to make the independent table for customer  .

3nf

in 3rd nf , we split the purchase order and customer in order to get the data of customer.

We have created 5 tables for database of purchase order system which is Purchase Order table, Customer table, Vendor table, Item table, POItem table.

Purchase Order table

Customer table

Vendor table

Item table

POItem table

This the DDL and DML for purchase order system:

CREATE DATABASE FinalGroupDatabaseProject

CREATE TABLE PurchaseOrder

(

PurchaseOrderNumber VARCHAR(100) PRIMARY KEY NOT NULL,

    PurchaseOrderDate VARCHAR(100)    NOT NULL,

    Requisitioner VARCHAR(25)     NOT NULL,

    ShipVia VARCHAR(25) NULL,

FOB VARCHAR(3) NOT NULL,

ShippingTerms VARCHAR(10) NULL

)

CREATE TABLE Customer

(

CustomerNo VARCHAR(10) PRIMARY KEY NOT NULL,

CustomerName VARCHAR(50) NOT NULL,

CustomerAddress VARCHAR(100) NOT NULL,

CustomerCity VARCHAR(50) NOT NULL,

CustomerZipcode INT NOT NULL,

CustomerPhone VARCHAR(50) NOT NULL

)

CREATE TABLE Vendor

(

VendorNo VARCHAR(10) PRIMARY KEY NOT NULL,

VendorName VARCHAR(25) NOT NULL,

VendorCompanyName VARCHAR(50) NOT NULL,

VendorAddress VARCHAR(100) NOT NULL,

VendorCity VARCHAR(50) NOT NULL,

VendorZipcode INT NOT NULL,

VendorPhone VARCHAR(50) NOT NULL

)

CREATE TABLE POItem

(

PurchaseOrderNumber VARCHAR(100) NOT NULL REFERENCES PurchaseOrder(PurchaseOrderNumber),

ItemNo VARCHAR(25) NOT NULL REFERENCES Item(ItemNo),

ItemQuantity INT NOT NULL,

ItemUnitPrice INT NOT NULL, 

ItemTotalPrice INT

)

CREATE TABLE Item

(

ItemNo VARCHAR(25) PRIMARY KEY NOT NULL,

ItemDescription VARCHAR(100) NOT NULL,

ItemUnitPrice INT

)

INSERT INTO PurchaseOrder VALUES('123456','2015-06-27','','Tiki','Yes','');

INSERT INTO PurchaseOrder VALUES('123456','2015-06-27','','Tiki','Yes','');

INSERT INTO PurchaseOrder VALUES('234567','2015-06-27','','JNE','Yes','');

INSERT INTO Vendor VALUES('V111','SamuelMpt','SamGrocery','Jl.Listrik no 8','Jakarta','51221','08123456789');

INSERT INTO Vendor VALUES('V112','KumarAmt','KumarGrocery','Jl.Kontak no 7','Jakarta','51222','09763723242');

INSERT INTO Vendor VALUES('V113','MinsaniMn','MinsaniGrocery','Jl. Tiang no 68','Jakarta','51223','05745834234');

INSERT INTO Customer VALUES('C001','Halim','Jl. Hang Lekir no 4','Jakarta','22451','08198765432');

INSERT INTO Customer VALUES('C002','Jacky','Jl. Hang Lekir no 5','Jakarta','22451','09877654432');

INSERT INTO Customer VALUES('C003','Hezki','Jl. Hang Lekir no 6','Jakarta','22451','09873846323');

INSERT INTO Item VALUES('P1111','XYZ Base Product','1234');

INSERT INTO Item VALUES('P1112','option ABC','234');

INSERT INTO Item VALUES('P1113','option DEF','67');

INSERT INTO POItem VALUES('123456','P1111','1','1234','1234');

INSERT INTO POItem VALUES('123456','P1112','1','234','234');

INSERT INTO POItem VALUES('234567','P1113','2','67','134');

Relation Algebra:

s_predicate (R)

Works on a single relation R and defines a relation that contains only those tuples (rows) of R that satisfy the specified condition (predicate).

SELECT*FROM PurchaseOrder

SELECT*FROM Customer

SELECT*FROM Vendor

SELECT*FROM Item

SELECT*FROM POItem

all of the select syntax is to list all item in the table.

SELECT MAX(ItemUnitPrice)AS HighestPrice FROM Item;

by using aggregate function  MAX we can display the highest price of all item.

Database design methodology: conceptual, logical, physical design.

Logical

Conceptual

Physical Design 

Those are our screenshot pictures of our work. If you want to know more about it, please watch our video in youtube. 

manual link: 

https://www.youtube.com/watch?v=zXvzYIAJHP0&feature=youtu.be

That is all, Thanks guys for reading and watching our video :)

Making an Application for iTunes database.

Hello guys, back with me again. Hope you guys have a lovely day.
Glad that i could make a new post again in my blog :D

So, the topic is, The next episode of my database project (see the first post, including my introduction). I will make my database is accessible in application. I have made it with my visual studio 2013 with window form application in c# syntax. Well, i have only made the application for editing the master song table, which is consist of:

(

songID char(5) primary key not null,

songTitle varchar(100) not null,

songArtist varchar(100) not null,

srecordingCompany varchar(100) not null,

songPrice float not null,

)

So, the user could easily input the data with my app :) not only inputting the data, it could update, delete and also show the data that is stored in database with that app. 

here is the syntax for my application!

1. Utilities (using class): This code is for connecting the application to the database.

//Utilities

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace hezkii

{

    class Utilities

    {

        public static SqlConnection ConnectToSql(string integratedSecurity, string dataSource,

                                                   string initialCatalog, string userID, string password)

        {

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString =

             "integrated security=" + integratedSecurity + ";" +

             "data source=" + dataSource + ";" +

             "persist security info=false;" +

             "initial catalog=" + initialCatalog + ";" +

             "User ID=" + userID + ";" +

             "Password=" + password;

            //"User ID=user;Password=password";

            try

            {

                conn.Open();

                MessageBox.Show("Connected to Database successfully!", "Database connect status:", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            catch (Exception ex)

            {

                MessageBox.Show("Failed to connect to data source, note: " + ex, "Database connect status:", MessageBoxButtons.OK, MessageBoxIcon.Stop);

            }

            finally

            {

                //conn.Close();

            }

            return conn;

        }

    }

}

2. Songs: the main syntax that cold insert, update, delete, and show the detail of the data in the application and     it is all connected to the database

//songs

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace hezkii

{

    public partial class song : Form

    {

        private static SqlConnection conn = null;

        public song()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            conn = Utilities.ConnectToSql("sspi", ".\\SQLExpress", "master", "", "");

            if (textBox1.Text != "") // if not empty

            {

                SqlCommand addSite =

                new 

                SqlCommand("INSERT INTO MasSong (songID, songTitle, songArtist, srecordingCompany, songPrice) VALUES (@songID, @songTitle, @songArtist, @srecordingCompany, @songPrice)", conn);

                addSite.Parameters.AddWithValue("@songID", textBox1.Text);

                addSite.Parameters.AddWithValue("@songTitle", textBox2.Text);

                addSite.Parameters.AddWithValue("@songArtist", textBox3.Text);

                addSite.Parameters.AddWithValue("@srecordingCompany", textBox4.Text);

                addSite.Parameters.AddWithValue("@songPrice", textBox5.Text);

                addSite.ExecuteNonQuery();

                MessageBox.Show("Data is successfully saved!");

            }

            else

            {

                MessageBox.Show("Empty field is not allowed!");

            }

        }

        private void button2_Click(object sender, EventArgs e)

        {

            conn = Utilities.ConnectToSql("sspi", ".\\SQLExpress", "master", "", "");

            if (textBox1.Text != "") // if not empty

            {

                SqlCommand addSite =

                    new SqlCommand("UPDATE MasSong SET songID = @songID, songTitle =  @songTitle, songArtist = @songArtist, srecordingCompany = @srecordingCompany, songPrice = @songPrice WHERE songID = @songID", conn);

                addSite.Parameters.AddWithValue("@songID", textBox1.Text);

                addSite.Parameters.AddWithValue("@songTitle", textBox2.Text);

                addSite.Parameters.AddWithValue("@songArtist", textBox3.Text);

                addSite.Parameters.AddWithValue("@srecordingCompany", textBox4.Text);

                addSite.Parameters.AddWithValue("@songPrice", textBox5.Text);

                addSite.ExecuteNonQuery();

                MessageBox.Show("Data is successfully saved!");

            }

            else

            {

                MessageBox.Show("Empty field is not allowed!");

            }

        }

        private void button3_Click(object sender, EventArgs e)

        {

            conn = Utilities.ConnectToSql("sspi", ".\\SQLExpress", "master", "", "");

            if (textBox1.Text != "") // if not empty

            {

                SqlCommand addSite =

                    new SqlCommand("DELETE FROM MasSong WHERE SongID = @SongID", conn);

                addSite.Parameters.AddWithValue("@songID", textBox1.Text);

                addSite.Parameters.AddWithValue("@songTitle", textBox2.Text);

                addSite.Parameters.AddWithValue("@songArtist", textBox3.Text);

                addSite.Parameters.AddWithValue("@srecordingCompany", textBox4.Text);

                addSite.Parameters.AddWithValue("@songPrice", textBox5.Text);

                addSite.ExecuteNonQuery();

                MessageBox.Show("Data is successfully saved!");

            }

            else

            {

                MessageBox.Show("Empty field is not allowed!");

            }

        }

        private void button4_Click(object sender, EventArgs e)

        {

            showdetail b = new showdetail();

            b.Show();

        }

    }

}

//Form1

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

namespace hezkii

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void songToolStripMenuItem_Click(object sender, EventArgs e)

        {

            song a = new song();

            a.Show();

        }

    }

}

3. The showdetail: the syntax that required to show the stored data in the showdetail box

//Showdetail

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace hezkii

{

    public partial class showdetail : Form

    {

        private static SqlConnection conn = null;

        public showdetail()

        {

            InitializeComponent();

        }

        private void fill_my_grid()

        {

            conn = Utilities.ConnectToSql("sspi", ".\\SQLExpress", "master", "", "");

            //SET SOME PROPERTIES OF DATAGRID:

            dataGridView1.AllowUserToAddRows = false;

            dataGridView1.ReadOnly = true;

            //CREATING HEADER IN GRID:

            dataGridView1.ColumnCount = 5;

            dataGridView1.Columns[0].Name = "songID";

            dataGridView1.Columns[1].Name = "songTitle";

            dataGridView1.Columns[2].Name = "songArtist";

            dataGridView1.Columns[3].Name = "srecordingCompany";

            dataGridView1.Columns[4].Name = "songPrice";

            //FILL DATAGRID WITH BARANG

            using (SqlDataAdapter a = new SqlDataAdapter("SELECT * FROM MasSong", conn))

            {

                DataTable t = new DataTable();

                a.Fill(t);

                //prepare rows empty space as much as data

                dataGridView1.RowCount = t.Rows.Count;

                //FILL ALL DATA BARANG INTO GRID

                for (int row = 0; row < t.Rows.Count; row++)

                {

                    dataGridView1[0, row].Value = t.Rows[row]["songID"];

                    dataGridView1[1, row].Value = t.Rows[row]["songTitle"];

                    dataGridView1[2, row].Value = t.Rows[row]["songArtist"];

                    dataGridView1[3, row].Value = t.Rows[row]["srecordingCompany"];

                    dataGridView1[4, row].Value = t.Rows[row]["songPrice"];

                }

            }

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)

        {

        }

        private void showdetail_Load(object sender, EventArgs e)

        {

            fill_my_grid();

        }

    }

}

That is all of my syntax that i have used. You can free to copy and paste the syntax. 

Well again, i made my video also just like last time. I made it with a help of my friends. Thanks guys, hope you have a good life :D

Well, This syntax and the video is honorably dedicated to my lecturer in the Introduction to Database System subject, Mr. Samuel Mahatmaputera and to my teaching assistant in the Introduction to Database System subject, Ms. Sharon Jesica. Thanks for teaching me about the query language of database. This knowledge is very precious for me. 

Thanks for reading my post and thanks for having a time to see my video! Goodbye.

(You can contact me from the comment if you have a question based on the tutorial)

Manual link

Application on iTunes database: https://www.youtube.com/watch?v=B-zBYU53hrw&feature=youtu.be