Seminar about Online School. (Watch the my video first on youtube)

Please watch this (https://www.youtube.com/watch?v=MGD0o6BRyQY) before read this thankyou :D

Hello guys back to me again.

This is the summary about the Online School Seminar.
It held in Binus University FX Campus at 23rd October 2015.
So, Lets Check it out!

So first, the speaker is explaining about the Broadcasting Business Cases. It is referring to the system of the business that could automatically broadcast to the other. So there are a lot of solution to broadcast something to the other by the media of the internet. It could be uploaded or it could be Live Streamed. Live Stream is the action that recording something and then directly broadcasted widely through the internet. Information Technology (IT) is taking a strong part at this point, so the business or the other matter could be solved easily. The example of that website is the Twitch.tv, YouTube, Afreeca.com, and Korea.org. Korea.org is the website that contain the course about the bible and Christian education online designed by Korea. That website will be the example of the education and business could be handy by the touch of the IT. At the Korea.org there is the MYTV content that the function is to see the process learning online. Teachers will using the broadcast content, Students will using the watch content, and the Administrator will use the Management Information System (MIS) to manage the content and the process.

There are the breakdown of the MIS at the Online School

·         CMS (Content Management System)

·         LMS (Learning Management System)

·         BMS (Broadcast Management System)

·         SES (Social Education System)

·         MMS (Membership Management System)

CMS System is the management of the Multimedia at the Online School. It is more to the text, audio, video, etc. LMS is the management of the course, it is included the management of the course content and the schedule of the learning. BMS is the management of broadcasting that is controlling the process of the broadcasting and video on demand. SES is the management that comparing and matching the Teacher and Student, the cases is “Is it match with the student capacity for the education” or “Is that teacher suitable to teach that subject”. MMS is the management of the membership. The role is controlling the member and also maintain the loyalty of the customer. If the customer appreciated, they will satisfied and will come back again without any problem. For the CMS, the factual example is WordPress for the source of the content, because it is a lot of blog posted through it. It is about 74,652,825 sites. For the LMS example is Moodle. Moodle is the website contains the learning method from the authentic educator. The example of the implementation of BMS is WOWZA streaming engine. WOWZA is instant streaming engine that directly broadcast the live streaming from the various input (Camera, Mic, etc.), and then it is processed by the WOWZA itself including the process of the preparation, delivery, and deploying the video, and finally it is outputted directly through the various media (TV, Tablet, HP, etc.). WOWZA also providing the Telestream Wirecast live encoder and also the JW Player. For the demo and the explanation of the JW Player please visit the Wowza.com. For the actual BMS implementation example is Binus.tv. Binus.tv is providing the flash live stream and also the Mobile device support for portability matter. For the SES, the example is the button at the Korea.org matching and students button: Pray, Teach, and Learn. So it will automatically synchronizing what is the desire of the student to learn in that website. And last for the MMS. It is about the Customer Relationship Management (CRM) and Loyalty Management System (LMS). The CRM is the process of the relation maintenance from the customer to the provider. And LMS is the form of something that is for maintaining the relationship between customer and provider for the loyalty matter. The example is the membership point system. If the customer shop a lot, they will get a point that could be redeemed as the item on the shop. Because of that, the customer will be satisfied shopping at that store.

To sum up, Information Technology is taking a great part at this problem. Business or Education will be easily served because of the role of the Information Technology. And then, Management Information System is taking a lot of part at this problem. MIS has a few breakdown by its own function: CMS, LMS, BMS, SES, and MMS. The function of the breakdown is for the optimization of the MIS process so it would work on track without any trouble. 

Thats it! hope it will be useful for your knowledge!

Have a nice day guys :) 

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

 

My first post. iTunes database with Microsoft SQL

Greeting everyone.

Thanks for reading my blog. Now you are honorably the reader of my first post.

For this post, i want to share about my tutorial for making the iTunes database in the SQL query language. Which is i have learned in this semester of my university. By the way, I am from Binus International University in South Jakarta, Indonesia. I am majoring at Information System and still on semester 2 (freshmen).  And now back to the topic. 

By the way for the video, sorry for the complicated process to watch it, You must watch the Introduction Part and then the Tutorial Part. I am really sorry because i do not have any time left to edit the video. I have already tried the video editor application but, there are several lack of system. When i wanted to edit the video in the laptop, the part of the screen capture video is blur, And when i wanted to edit it in my handphone, The video can not be loaded at the video editor application. I have found many solution for this problem. But still, i do not get it at all. It is also because of the time that i cannot find another video editor program. So, sorry for the inconvenient at watching the videos.

This is my full query of my iTunes database so you can try it. (for the further explanation is on the videos)

--------------------------------------------------------------------------------------------------------------------------

create database iTunes -- Creating a Database

create table masterSong -- designing a Main song list table in the store

(

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,

)

create table masterPlaylist -- designing a playlist list table in the store

(

playlistID char(6) primary key not null,

playlistName varchar(100) not null,

playlistArtist varchar(100) not null,

precordingCompany varchar(100) not null,

playlistPrice float not null,

)

create table masterCustomer -- designing a customer list who has registered in iTunes

(

customerID char(7) primary key not null,

customerName varchar(100) not null,

customerAddress varchar(100) not null,

customerZipcode char(5) not null,

creditcardNumber char(16) not null,

)

create table songTransaction -- designing the transactional table for song

(

STID varchar(10) primary key not null,

customerID char(7) references masterCustomer(customerID) not null,

songID char(5) references masterSong(songID) not null,

STDescription varchar(100) not null,

)

create table playlistTransaction -- designing the transactional table for playlist

(

PTID varchar(10) primary key not null,

customerID char(7) references masterCustomer(customerID) not null,

playlistID char(6) references masterPlaylist(playlistID) not null,

PTDesctiption varchar(100) not null,

)

--this is for editing the column

alter table playlistTransaction -- effecting the playlisttransaction table

drop column total -- delete the column named total

add total money

--display for the data that has been input

select * from masterSong

select * from masterPlaylist

select * from masterCustomer

select * from songTransaction

select * from playlistTransaction

--input the value 

insert into masterSong values('00001', 'Dont stop the madness','Hardwell','Revealed Recording',7000.00)

insert into masterSong values('00002','Secrets','Tiesto','Musical Freedom',7000.00)

insert into masterSong values('00003','Blank Space','Taylor Swift','Sony Recording','8000.00')

insert into masterPlaylist values('000001','United We Are','Hardwell','Revealed Recording','34000.00')

insert into masterPlaylist values('000002','Ultra Music Festival 2015 OST','Various Artist','Ultra Recording',70000.00)

insert into masterPlaylist values('000003','1989','Taylor Swift','Sony Recording',80000.00)

insert into masterCustomer values('0000001','Nickolas Tunggara', 'Bukit Gading Villa nomor 20','14250','0123456789765248')

insert into masterCustomer values('0000002','Alexander','Jalan kramat jati 3','12340','0978654738495087')

insert into masterCustomer values('0000003','Andrew','Kelapa Nias23','12453','4354676543347890')

insert into songTransaction values('1','0000001','00001','Success')

insert into playlistTransaction values('1','0000001','000001','success')

--sort with condition

select * from masterCustomer

where customerName like 'a%'

--delete the value with condition

delete from masterCustomer

where customerName = 'Alexander'

--checking the customer

select * from  masterCustomer

--update the table customer

update masterCustomer

set customerName = 'Andrew Natanael'

where customerZipcode = '12453'

--order by

select * from masterCustomer

order by customerName desc

select * from masterCustomer

order by customerAddress 

--union method

select playlistID, playlistName, playlistArtist

from masterPlaylist

union

select songID, songTitle, songArtist

from masterSong

--intersect method

select  playlistArtist

from masterPlaylist

intersect

select  songArtist

from masterSong

-- Joining the table

select * from masterCustomer

select * from masterSong a

join songTransaction b

on a.songid = b.songID

join masterCustomer c

on b.customerID = c.customerID

--------------------------------------------------------------------------------------------------------------------------

Well, This database query 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. 

That is all about my post. Thank you for reading and also watching to my video. I hope you guys enjoy it and sorry for my mistake in making of these.

Christoffel Yehezkiel Angga (1801441972).

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

Manual link:
Part 1:  https://youtu.be/XCorcbttNDY .
Part 2: https://youtu.be/jscLONzxurY .

Updated 14th April, At last i have done my full video version of my tutorial. Enjoy all! :)

Manual link:
Full version: https://www.youtube.com/watch?v=uxmOcGSU7Cw