SCHOOL OF ARTS AND SCIENCES

DEPARTMENT OF COMPUTER AND INFORMATION SCIENCES

SPROCKETS
DATABASE SYSTEM PROPOSAL

FINAL PROJECT OUTPUT

INFORMATION AND COMMUNICATIONS TECHNOLOGY

BY:
ISHIZUKA, IZUMI
MARTINEZ, ROBERT
LAWRENCE, CRAIG
I. Introduction

CAFEE

COMPANY HISTORY
Cafee Coffee + Milk Tea Café is envisioned to become the creative hub for artists, photographers,
writers, imaginative young people, innovative young professionals and entrepreneurs to chill and
unwind. It will provide customers that “breathe of fresh air”, a place to breed creativity and inspiration.
Cafee Sprockets Café encourages youthful freedom and boundless imagination. It offers milk tea,
coffee and great comfort food. Cafee Coffee + Milk Tea also has Wi-Fi and it is a great place where
people can work and study.

Old Data Processing Method

Paper based inventory
File System (Excel)
II. Core Business Data

FOOD
DATE SUPPLIER INVOICE ITEMS QTY/UNIT PRICE
12/01/2016 Fooda 1014847 Asi Ve Taiwan 1.025kg ₱ 202.95
Saversmart
12/01/2016 Fooda 1014847 Spring Onion 0.225kg ₱31.05
Saversmart
12/01/2016 The Pork 61418 Pork ribs 3.26kg ₱573.76
Shop Steak
12/02/2016 All 15992 Farmers 10kg ₱850.00
Filipino Friend Fries
12/01/2016 All 15835 Farmers 10kg ₱850.00
Filipino Friend Fries
DRINKS

DATE SUPPLIER INVOICE ITEMS QTY/UNIT PRICE

12/01/2016 Naveah Sales 11334 Sago Straw 10 Packs ₱830.00
Assorted
12/01/2016 Petty Cash N/A Partial N/A ₱5,000.00
Payment
Possmei Items
12/03/2016 Cube Ice 604082 Ice Tube 2 Bags ₱100.00
12/05/2016 Livingwater 8718 Mineral Water 3 containers ₱105.00
Station
12/05/2016 Cube Ice 604877 Ice Tube 2 Bags ₱100.00
OPERATING EXPENSES

DATE SUPPLIER INVOICE ITEMS QTY/UNIT PRICE

12/01/2016 Pharmacy 580040 Staff Meal N/A ₱55.50
12/01/2016 The Pork Shop 61418 Staff Meal N/A ₱76.26
12/04/2016 7/11 10788 Staff Meal N/A ₱22.00
12/05/2016 Pharmacy 582171 Staff Meal N/A ₱15.00
12/06/2016 Pharmacy 582884 Cleaning N/A ₱139.05
Supplies

III. Business Process

IV. Enterprise Data Model
V. ENTITY RELATIONSHIP DIAGRAM
VI. RELATION TABLE (3NF)
 VII. META DATA

PAYMENT

Entity Name: Payment Attribute Name: PaymentID Meta: int(11)

INDEX: Primary Key Allow NULL: NO FK Ref: OrderID
Description: You could use this field to enter an invoice number or text to describe what the payment is
for.
Notes:

Entity Name: Payment Attribute Name: Payment_Amount Meta: int(11)

INDEX: NONE Allow NULL: NO FK Ref: NONE
Description: This attribute will hold the Payment amount. Based on an analysis of payment amount.
Notes:

ORDER

Entity Name: Order Attribute Name: OrderID Meta: int(11)

INDEX: Primary Key Allow NULL: NO FK Ref: NONE
Description: You could use this field to read an invoice number or text to describe what the order is for.
Notes:

Entity Name: Order Attribute Name: CustomerID Meta: int(11)

INDEX: Foreign Key Allow NULL: NO FK Ref: CustomerID
ON DELETE: NULL ON UPDATE: CASCADE
Description:. This attribute holds the CustomerID.
Notes:

Entity Name: Order Attribute Name: Oder_Date Meta: int(11)

INDEX: NONE Allow NULL: NO FK Ref: NONE
Description: The order date of financial instruments defines the dates at which orders are made by one
customer to another on for example a bond or derivative.
Notes:
Entity Name: Order Attribute Name: EmployeeID Meta: int(11)
INDEX: NONE Allow NULL: NO FK Ref:EmployeeID
Description: This will hold the EmployeeID.
Notes:

Entity Name: Order Attribute Name: PaymentID Meta: int(11)

INDEX: NONE Allow NULL: NO FK Ref: PaymentID
Description: This will hold the PaymentID.
Notes:

ORDERLINE

Entity Name: Orderline Attribute Name: OrderID Meta: int(11)

INDEX: NONE Allow NULL: NO FK Ref: NONE
Description: Holds the OrderID
Notes:

Entity Name: Orderline Attribute Name: CustomerID Meta: int(11)

INDEX: NONE Allow NULL: NO FK Ref: CustomerID
Description: Holds the cutomer ID.
Notes:

Entity Name: Order Attribute Name: Quantity Meta: int(11)

INDEX: NONE Allow NULL: NO FK Ref: NONE
Description: Holds The quantity of the order.
Notes:
 EMPLOYEE

Entity Name: Employee Attribute Name: EmployeeID Meta: int(11)

INDEX: Primary Key Allow NULL: NO FK Ref: NONE
Description: You could use this field to enter an invoice number to get the information of the employee.
Notes:

PRODUCT

Entity Name: Product Attribute Name: ProductID Meta: int(11)

INDEX: Primary Key Allow NULL: NO FK Ref: NONE
Description: You could use this field to enter an invoice number to describe what the product is for.
Notes:

Entity Name: Product Attribute Name: Product_Desc Meta: varchar(45)

INDEX: NONE Allow NULL: NO FK Ref: NONE
Description: This attribute will input a description to the product.
Notes:

Entity Name: Product Attribute Name: Product_Price Meta: int(11)

INDEX: NONE Allow NULL: NO FK Ref: NONE
Description: This attribute will input a price to the product.
Notes:

Entity Name: Product Attribute Name: Product_Name Meta: Varchar(45)

INDEX: NONE Allow NULL: NO FK Ref: NONE
Description: This attribute will hold the product name.
Notes:
 SUPPLIER

Entity Name: Supplier Attribute Name: SupplierID Meta: int(11)

INDEX: Primary Key Allow NULL: NO FK Ref: NONE
Description: This attribute will hold the supplier ID.
Notes:

Entity Name: Supplier Attribute Name: Supplier_Name Meta: varchar(45)

INDEX: NONE Allow NULL: NO FK Ref:
Description:. This attribute will hold the Supplier Name.
Notes:

Entity Name: Supplier Attribute Name: Contact_Num Meta: int(11)

INDEX: NONE Allow NULL: NO FK Ref: NONE
Description:. This attribute will hold the suppliers contact number.
Notes:

Entity Name: Supplier Attribute Name: Supplier_Address Meta: varchar(45)

INDEX: NONE Allow NULL: NO FK Ref:
Description:. This attribute will hold the employee address.
Notes:

Entity Name: Supplier Attribute Name: Email_add Meta: varchar(45)

INDEX: NONE Allow NULL: NO FK Ref:
Description:. This attribute will hold the supplier email address.
Notes:
 CUSTOMER

Entity Name: Customer Attribute Name: CustomerID Meta: int(11)

INDEX: Primary Key Allow NULL: NO FK Ref: CustomerID
Description This attribute will hold the customer identification number.

Entity Name: Customer Attribute Name:Customer_Name Meta: Varchar(45)

INDEX:NONE Allow NULL: NO FK Ref: None
Description This attribute will hold the SupplierID.
Notes:

SUPPLIER_PRODUCTS
Entity Name Supplier_Products Attribute Name: ProductID Meta: int(11)
INDEX: NONE Allow NULL: NO FK Ref: ProductID
Description This attribute will hold ProductID.
Notes:

Entity Name: Supplier_Products Attribute Name:SupplierID Meta: int(11)

INDEX:NONE Allow NULL: NO FK Ref: SupplierID
Description This attribute will hold the SupplierID.
Notes:
VIII. Designer View
 IX. CONCLUSION

Our DB schema is complete enough if we are just referring to the data given from the corporation. The
time provided for us was just enough to analyze our business process, enterprise data model and created the
ER diagram, relation table, Meta data, designer view and others

About working with the client, overall it was a great experience because they were very easy to work
with, they were very cooperative and easy to schedule with. Actually we just went there twice because they
provided us with the complete data that we asked and also provided us great answers on the questions that
we’ve asked them. We’ll just assume that they were fine on handling us the records because they we’re
smiling and cooperative on it.

Our database modeling experience was educational, fun and a bit of annoying. It was fun and
educational because we learned a lot about database modeling during the making of this project. it was also
a bit of annoying because we made many revisions in our data because of the mistakes that we made.

We both chose to adopt this schema while we further our research. If we can think of something new
and efficient that can improve the schema that we created then we will delete, insert or update our data