ITS472
DATABASE MANAGEMENT SYSTEM
PROPOSAL
VENDOR PRODUCT MANAGEMENT SYSTEM
GROUP
M3CS2303A
MEMBERS’ NAME
NUR NAJIHAH ARINA BINTI ZULHERDI – 2016645018
ZA BINTI MOHD RADZUAN – 2016317363
IYLIA ASYIQIN BINTI ABD MAJID – 2016645072
NORLIYANA BINTI MOHD SHAMSUDIN – 2016395631
NOR FAQIHATUL AFIFAH MOHAMAD YUSOFF - 2016760459
LECTURER’S NAME
MADAM ZUHRI ARAFAH BINTI ZULKIFLI
SUBMISSION DATE
11 OKTOBER 2017
TABLE OF CONTENT
PAGE
NO TITLE
NO
1 Company Background 3
2 Problem Statement 4
3 Objective 5
4 ERD 6
5 3NF Relational Scheme 7-9
2
COMPANY BACKGROUND
ZULIN Café was founded in 2014 by Zulherdi bin Mohd Zain and his beloved wife
Fazarina Adlina bt A.Rahim.Before the café was founded,Fazarina Adlina was doing the
business only in front of their home and only a few customer can get their products because
they cannot accomodate the demand from customer.Due to high demand from customer,she
planned with her husband to open a café to meet the customer demand.Together with her
husband who has experience in business management,ZULIN Café was opened and started
selling various type of food, dessert and beverages from different vendors around Johor
Bahru.Besides selling vendors food,the shop also sell a local and international homemade
dish.
After years of operating,ZULIN Café not only gain popularity from community
around Johor Bahru but also people around Malaysia because the uniqueness of the product
sold at the shop.Now,not only it sell food and dessert,it also sell different types of product
and continue to fulfill the high demand from local community and visitor outside Johor.
3
Problem Statement
Recording the stock manually by workers is really inconvenience.Typically,there will be
many files when using manual system.So it will be inconvenience for the workers to check
the records everyday then write it onto the paper.Sometimes the data can be accidently
recorded repetitively and data redundancy will happen definitely.Workers also have to
calculate the profit of the shop manually every single day.It will definitely be a troublesome.
Using Vendor Product Management System that we propose,the business will run
smoothly and become easy for the workers to handle.The workers will know when the goods
is out of stock because it will appear in the system as an alert.Checking of stock count will
become easier for the workers because workers will not have to keep many file and search it
one by one.
By using this system, the shop will save money from reducing paper consumption and
files. There will be no space needed to keep the files or papers.Separation of data will become
easier when using this system and there will be no duplication of data.The profit also will
automatically calculate in the system when the customer buy the goods.It is because the
workers can key in the data of the customer directly into the system when customer buy the
goods.
4
Objectives
I. To create a system so store’s owner can keep track of staffs’ information, customers’
information, stock’s availability, stock’s expiry date, types of stock stored in
inventory and more.
II. To design, develop and test a database system for store worker to record stock of
goods.
III. To design a database that is able to provide wide variety of information on the stock
inventory.
IV. To develop a system that is able to record data based on the data criteria and organize
the data into a table accurately.The system also should be able to retrieve data and
generate required reports.
V. To test the developed system in the intended environment to make sure it is suitable to
be used and meet the needs of the company or organization.
5
ENTITY RELATIONSHIP DIAGRAM (ERD)
MANAGER
PK STAFF_ID VENDOR
STAFF PK VENDOR_ID
PK STAFF_ID VENDOR_NAME
d
STAFF_NAME MANAGER VENDOR_ADD
STAFF_ADD PK STAFF_ID VENDOR_NPHONE
STAFF_TYPE
STAFF_TYPE
serves
produce
serves
CUSTOMER FOOD PRODUCT
PK CUST_ID PK FOOD_ID PK PRODUCT_ID
CUST_NAME PRODUCT_TYPE
d
CUST_NPHONE BEVERAGE PRODUCT_PRICE
FK STAFF_ID PK FOOD_ID PRODUCT_QTY
PRODUCT_TYPE PRODUCT_EXPDATE
FK VENDOR_ID
places
exists
ORDERLINE
ORDER PK ORDER_NO
lists
PK ORDER_NO ORDER_QTY
ORDER_DATE SALES
FK CUST_ID FK PRODUCT_ID
6
NORMALIZATION PROCESS
Unnormalization Form
ORDERLINE (ORDER_NO, ORDER_DATE, STAFF_ID, STAFF_NAME, STAFF_ADD, CUST_ID,
CUST_NAME, CUST_NPHONE, PRODUCT_ID, PRODUCT_TYPE, PRODUCT_PRICE, PRODUCT_QTY,
PRODUCT_EXPDATE, VENDOR_ID, VENDOR_NAME, VENDOR_ADD, VENDOR_NPHONE, ORDER_QTY,
SALES)
Fisrt Normal Form (1NF)
1. Eliminate repeating group
2. Identify primary keys
3. Identify all dependencies
ORDERLINE (ORDER_NO, PRODUCT_ID, ORDER_DATE, STAFF_ID, STAFF_NAME, STAFF_ADD,
STAFF_TYPE, CUST_ID, CUST_NAME, CUST_NPHONE, PRODUCT_ID, PRODUCT_TYPE,
PRODUCT_PRICE, PRODUCT_QTY, PRODUCT_EXPDATE, VENDOR_ID, VENDOR_NAME,
VENDOR_ADD, VENDOR_NPHONE, ORDER_QTY, SALES)
Second Normal Form (2NF)
- Table is in First Normal Form (1NF)
ORDER_NO ORDER_DATE, CUST_ID
ORDER (ORDER_NO, ORDER_DATE, CUST_ID)
PRODUCT_ID PRODUCT_TYPE, PRODUCT_PRICE, PRODUCT_QTY,
PRODUCT_EXPDATE, VENDOR_ID
PRODUCT (PRODUCT_ID, PRODUCT_TYPE, PRODUCT_PRICE, PRODUCT_QTY, PRODUCT_EXPDATE,
VENDOR_ID)
ORDERLINE (ORDER_NO*, PRODUCT_ID*, STAFF_ID, STAFF_NAME, STAFF_ADD, STAFF_TYPE,
CUST_NAME, CUST_NPHONE, VENDOR_NAME, VENDOR_ADD, VENDOR_NPHONE, ORDER_QTY,
SALES)
7
Third Normalization Form (3NF)
- Table is in Second Normal Form (2NF)
ORDER_NO ORDER_DATE, CUST_ID
ORDER (ORDER_NO, ORDER_DATE, CUST_ID*)
PRODUCT_ID PRODUCT_TYPE, PRODUCT_PRICE, PRODUCT_QTY,
PRODUCT_EXPDATE, VENDOR_ID
PRODUCT (PRODUCT_ID, PRODUCT_TYPE, PRODUCT_PRICE, PRODUCT_QTY, PRODUCT_EXPDATE,
VENDOR_ID*)
CUST_ID CUST_NAME, CUST_NPHONE, STAFF_ID
CUSTOMER (CUST_ID, CUST_NAME, CUST_NPHONE, STAFF_ID*)
STAFF-ID STAFF_NAME, STAFF_ADD, STAFF_TYPE
STAFF (STAFF_ID, STAFF_NAME, STAFF_ADD, STAFF_TYPE)
VENDOR_ID VENDOR_NAME, VENDOR_ADD, VENDOR_NPHONE
VENDOR (VENDOR_ID, VENDOR_NAME, VENDOR_ADD, VENDOR_NPHONE)
ORDERLINE ( ORDER_NO*, PRODUCT_ID, ORDER_QTY, SALES)
8
ORDER
ORDER_NO ORDER_DATE CUST_ID*
PRODUCT
PRODUCT_I PRODUCT_TYPE PRODUCT_PRICE PRODUCT_QT PRODUCT_EXPDATE VENDOR_ID*
D Y
CUSTOMER
CUST_ID CUST_NAME CUST_NPHONE STAFF_ID*
STAFF
STAFF_ID STAFF_NAME STAFF_ADD STAFF_TYPE
VENDOR
VENDOR-ID VENDOR_NAME VENDOR_ADD VENDOR_NPHONE
ORDERLINE
ORDER_NO* PRODUCT_ID* ORDER_QTY SALES
