Final project program (mart.) serve as a service to buy and sell mobile phone.

Here are the program,

The program run on a SQL server that has this specification table :

1. CREATE TABLE `brand` (
   `id` int(11)  AUTO_INCREMENT,`name` varchar(20) NOT NULL, PRIMARY KEY (`id`))
2. CREATE TABLE `os` (
   `id` int(11) AUTO_INCREMENT,`name` varchar(20) NOT NULL, PRIMARY KEY (`id`))
3. CREATE TABLE `country` (
   `country_id` int(11) AUTO_INCREMENT,`country_name` varchar(50) NOT NULL, PRIMARY KEY (`country_id`))*
4. CREATE TABLE `custdetail` (
   `username` varchar(50) NOT NULL,`password` varchar(50) NOT NULL,`fname` varchar(50) NOT NULL,`lName` varchar(50) NOT NULL,`email` varchar(100) NOT NULL,`phone` varchar(20) DEFAULT NULL,`birthday` date NOT NULL, PRIMARY KEY (`username`))
5. CREATE TABLE `custaddress` (
   `id` int(11) AUTO_INCREMENT,`detail` text NOT NULL,`sub_district` varchar(50) DEFAULT NULL,`district` varchar(50) DEFAULT NULL,`province` varchar(50) DEFAULT NULL,`postal_code` int(11) NOT NULL,`country_id` int(11) DEFAULT NULL,`username` varchar(50) NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY  (`username`) REFERENCES `custdetail`(`username`), FOREIGN KEY  (`country_id`) REFERENCES `country`(`country_id`))
6. CREATE TABLE `store` (
   `name` varchar(50) NOT NULL,`username` varchar(50) NOT NULL,`address_id` int(11) NOT NULL, PRIMARY KEY (`name`), FOREIGN KEY  (`username`) REFERENCES `custdetail`(`username`))
7. CREATE TABLE `item` (
   `id` int(11) AUTO_INCREMENT,`name` varchar(50) NOT NULL,`quantity` int(11) NOT NULL,`price` double NOT NULL,`brand_id` int(11) NOT NULL,`os_id` int(11) NOT NULL,`storage` double NOT NULL,`ram` double NOT NULL,`description` text NOT NULL,`imageloc` text NOT NULL,`store_name` varchar(50) NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY  (`brand_id`) REFERENCES `brand`(`id`), FOREIGN KEY  (`os`) REFERENCES `os`(`id`), FOREIGN KEY  (`store_name`) REFERENCES `brand`(`id`), FOREIGN KEY  (`store_name`) REFERENCES `store`(`name`))
8. CREATE TABLE `transaction` (
   `id` int(11) NOT NULL,`purchased` tinyint(1) NOT NULL,`username` varchar(50) NOT NULL,`address_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), FOREIGN KEY  (`username`) REFERENCES `custdetail`(`username`))
9. CREATE TABLE `detailtrans` (
   `id` int(11) NOT NULL,`item_id` int(11) NOT NULL,`quantity` int(11) NOT NULL,`transaction_id` int(11) NOT NULL,`status` varchar(20) DEFAULT ‘On Process’, PRIMARY KEY (`id`), FOREIGN KEY  (`transaction_id`) REFERENCES `transaction`(`id`), FOREIGN KEY  (`item_id`) REFERENCES `item`(`id`))

That are represented in ERD diagram as below

*with pre-filled data. (195 Countries)

query :

1.  SELECT item.id,item.name,item.imageloc,item.price FROM item,brand,os WHERE item.quantity > 0 AND item.brand_id = brand.id AND item.os_id = os.id AND item.name LIKE ‘%” + search + “%’  AND brand.name LIKE ‘%” + brand + “%’ AND os.name ORDER BY ” + orderBy + ” ASC/DSC  LIKE ‘%” + os + “%’ LIMIT ” + CStr(start_id) + “,8
2. Calculate the amount max “for Limit”
   SELECT count(*) FROM item,brand,os WHERE item.quantity > 0 AND item.brand_id = brand.id AND item.os_id = os.id AND item.name LIKE ‘%” + Search + “%’ AND brand.name LIKE ‘%” + brand + “%’ AND os.name LIKE ‘%” + os + “%’
3. Transaction (after login)
   1. SELECT * FROM transaction WHERE purchased = 0 AND username = ‘” + username + “‘
   2. INSERT INTO transaction(purchased,username) VALUES(0,'” + username + “‘)

This are the main of the program. Here is where you can interact with the program such as Register, Details, Buy, Create Store, Cart, Search, and Sort.

If you clicked on the views of phone, the details of the phone will pop up (even without log-in).

query :

1. “SELECT * FROM item WHERE id = ” + CStr(id)
2. “SELECT name FROM brand WHERE id = ” + CStr(id)
3. “SELECT name FROM os WHERE id = ” + CStr(id)
4. SELECT custaddress.detail FROM custaddress, store WHERE custaddress.id = store.address_id AND store.name = ‘” + storeName + “‘
5. SELECT price *” + CStr(amtOfpur) + ” AS Totalprice FROM item WHERE id = ” + CStr(item_id)
6. INSERT INTO detailtrans(item_id,quantity,transaction_id) VALUES(” + CStr(item_id) + “,” + CStr(qty) + “,” + CStr(trans_id) + “)

The Add to Cart will enable if it’s not your store or you have log-in. It will be display as below,

Before you can log you must register, here’s how through the log-in page.

query (after log-in press):

1. “SELECT password FROM custDetail where username = ‘” + username + “‘”

After you click on register, this windows will pop up.

query (after submit button pressed):

1. “Insert Into custDetail Values(‘” + username + “‘,'” + password + “‘,'” + fname + “‘,'” + lname + “‘,'” + email + “‘,'” + phone + “‘,'” + birthday + “‘)”

You need to fill in the form to create an account. Remember that the program is validating all your data before submission. Such as, password length should be more that 8 character. If you makes any mistakes there should be no worries because you can edit them later on (except you username) .

As an example of form filling,
Here are a new username call “test” with password “12345678” and the other details as show bellow.

After registration than you can log in,

Only by then, you can add item to the cart (if item bought quantity is more than 0 and below the max purchasable that was set by the seller) and purchased them. Here are shown result of a multiple purchased by “test” user.

query :

1. SELECT transaction.id, item.store_name, detailtrans.id,item.id, item.name,item.price, detailtrans.quantity, item.price * detailtrans.quantity as price, detailtrans.status FROM detailtrans,item,transaction WHERE detailtrans.item_id = item.id AND transaction.id = detailtrans.transaction_id AND transaction.username = ‘” + username + “‘ AND transaction.purchased = 1 ORDER By detailtrans.status
2. SELECT transaction.id, item.store_name, detailtrans.id,item.id, item.name,item.price, detailtrans.quantity, item.price * detailtrans.quantity as price, detailtrans.status FROM detailtrans,item,transaction WHERE detailtrans.item_id = item.id AND transaction.id = detailtrans.transaction_id AND transaction.id = ” + CStr(currTrans)  ORDER By detailtrans.status
3. UPDATE item SET quantity = quantity –  + CStr(quantity)
4. DELETE FROM detailtrans WHERE id =  + CStr(id)
5. SELECT * FROM custaddress WHERE username = ‘ + username + ‘
6. SELECT price *” + CStr(amtOfpur) + ” AS Totalprice FROM item WHERE id = ” + CStr(item_id)

Before you can click buy now, you need to add an address in the user settings.

Here is where you can add a multiple address, update any details and change your password.
For example,
Adding a address (as it is necessary for buying items)

As an example result,

It will be automatically updated to the multiple address section (as shown below).

Change Password
For example “12345678” to “87654321”

You can edit your address detail by clicking the address list and this view will pop up.

Continue with the purchase process. After you click buy now, the purchase will be send automatically to the seller and it will disappear from cart and move to the purchased history after selecting the address you want to send it through .

After that there are information that your purchased is being processed.

It will change the detail of the transaction status to On Process, but if the user say that it is already sent to the user. The user “test” can fulfilled it by themselves.

Then, it is up to the seller to decide whether to rejected or to sent it. For example,

Then, now how to create your store. This is where you can only create one store for one account only. This will require you to choose you store address in the create store section.

After this you will have a store dashboard, as you can see below.

In this dashboard, you can add, delete or update an item.
To add an item you need to click on the button.

Example of inserting,

To add the picture, you need to click on the picture column. Then, picture form will pop up.
*
*In this form you can only add a link type of data.
The result after add the picture.

Click the add item to add you to the selling list.

Now, how to access your sales? it is located at the all sales button. The sales dashboard will appear, as below.

Here you can edit the status to rejected, fulfilled, or sent (type in status column)
Fulfilled and rejected will be transfer to Past Sales. To make the sale fulfilled you need to change the status to sent which you can do by type it in the status column.

The if you or the user fulfilled the purchase it will be move to the past sales. Which looks like, as below.

That’s the detail of our team project. In here there are some query doesn’t given since the query are similar to those query.

Title                                                   : Mobile Phone Store
Amount of Table                             : Approx. 8 – 10 Tables
Deadline                                           : 13^th Week of Database System
Project Team                                   :

1. Ivan Ezechial Suratno, 2101693920
   1. http://2101693920ivan.blog.binusian.org/
2. David Honasan, 2101693933
   1. http://2101693933david.blog.binusian.org/

General Description                        : We’ll try to create offline system mobile phone store.

Description (SQL)                            : The system that are thought are Buyer and Seller (Admin) as the user login input. We will create a multiple seller and buyer that can do trading system. The description of the phone are detail in the linking such as the table mobile phone will have a subsidiaries table that containing the detail of each one specs (For instance, mobile phone will have a processor detail that are contain in the other table).The visual basic description will be created by David Honasan.

Section of Creation*                      :

1. Ivan Ezechial Suratno : Database (MariaDB)
2. David Honasan : Visual Basic (vb.net)

Exception                           :
* Even though there are section, the project is created together backing one and another.