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 :)