UNIVERSITI KUALA LUMPUR

Malaysia Italy design institute (midi)

PXB 20303 DATABASE MANAGEMENT

POINT OF SALES SYSTEM

[GROUP 4]

PREPARED BY:

NAME STUDENT ID COURSE

ZA’IM BIN NORHAN 58220118094 BTM
ADILAH BINTI ADNAN 58220219012 BTM
IQBAL ZAFRI BIN M. ZUHADI 58220118023 BTM

PREPARED FOR: SIR KHAIRUL AZIZAN

 Conceptual Design

Point of Sale
System

Suppliers Customers Staff Address

Products

Orders

Orders items
 Customer_name
Customer_id Customer_contact
number
ER MODEL

Customer_id
expired Customers
Customer_date
register

Supplier_id Supplier_email

Order_day
Suppliers
Supplier_contact Order_id
number
Supplier_company Order
name
Person_incharge Order_status
code

Order_date Order_date

Staff_gender

Staff_name Address
Staff
Staff_id
Address _ day

Staff_contact
number
Address_status Address _ date
code

Address_id Address_day
Entity and attribute table

Table name Attributes Explanation/Glossary

Staff -Staff ID -The staff can access
-Full name many data in order.
-Contact no -The staff can login to the
-Gender order system to check
whether quantity is
available or not if
requested by customer.

Products -Product ID -The product can be

-Name access anytime by the
-Price customer.
-Category -Suppliers can restock
-Quantity the product.
-Supplier ID
Suppliers -Supplier ID -Provide the stock
-Company name according to request.
-Person in charge -Deliver the stock by
-Contact no schedule.
-Email -Connect with order for
business deals.
Order -Order ID -Relate with the products,
-Staff ID to make sure stock are in
-Customer ID place.
-Status Code -Direct with supplier if the
-Date staff make request for the
-Product ID product.
-Price
-Quantity
-Total price
Customer -Customer ID -Stores the information if
-Name the user is a customer. A
-Contact no customer can have many
-Email grocery entries.
-Date register
-ID expired
Customer Addresses -Customer ID -Data that the staff can
-Line 1 trace if required.
-Line 2 -Data for purposes if the
-City customer wants to make
-Postcode member card and
-State redeem for the point.
 Relationship

CUSTOMER
ADDRESSE
S
CUSTOEMR
ID

LINE 1

LINE 2
STAFF CITY

POSTCODE
CUSTOMER
STATE
STAFF ID
FULL NAME CUSTOMER ID
ORDER
NAME
CONTACT NO
ORDER ID CONTACT NO
GENDER
STAFF ID EMAIL

CUSTOMER ID DATE REGISTER

STATUS CODE ID EXPIRED

SUPPLIERS PRODUCTS
DATE
SUPPLIERS ID PRODUCT ID PRODUCT ID
COMPANY NAME NAME PRICE
PERSON IN PRICE QUANTITY
CHARGE
CATEGORY TOTAL PRICE
CONTACT ID
QUANTITY
EMAIL
SUPPLIER ID
DATABASE INFRASTRUCTURE

Access is used as the interface design tool which data is inserted, deleted, updated,
and queried. Database also can be viewed with the help of Access through the forms
that have been generated. Generally, we can use form to control access to data, such
as which fields of data are displayed. For example, certain users may not need to see
all the fields in a table. Providing those users with a form that contains just the
necessary fields makes it easier for us to use the database. An effective form speeds
the use of the database, because people don’t have to search for what they need. A
visually attractive form makes working with the database more pleasant and more
efficient and it can also help prevent incorrect data from being entered. While data can
be entered directly into a table, the larger the table, the harder it is to be sure that data
is in right field and record.

CREATING TABLES, INSERTING, DELETING AND MODIFYING DATA

1. CREATE TABLES:
A table is an organized structure that holds information. It consists of “fields” of
information into which you enter the “records” of the table. A field is a single
column within a table, consisting of one category of information. A record is a
collection of related fields that describe a single item, contained in a row within
a table. Below figures showing the tables that have been generated in Access
and contain the data sheet of customer, customer addresses, order, products,
staff, and suppliers.
 In “Design View,” the actual data stored in the table will not been seen. The
window only representation of the structure of the table. This will give more
control on the properties of the fields versus using “Datasheet View” to create
a table. This window is divided into two panes. First, there is the “design grid”
at the top, where we can enter field names and data types. Then there is the
“properties” section beneath it. Figure below shows example of properties field:

2. INSERTING AND DELETING

Access provides several ways to add or remove the columns in a datasheet.

Datasheet view to add or remove columns and set the data types for those
columns. Fields also can be added from a task pane, or the table that underlies
the datasheet can be opened and data field can be added in Design view. There
are several steps to do insertion:

i. In the Navigation Pane, locate and double-click the table to which you
want to add the field. Access opens the table in Datasheet view.
ii. Scroll to the right or left side of the datasheet (depending on your
Windows Regional and Language settings), and locate the blank
column. By default, the words Click to Add appear in the column header
of the blank column.
iii. Enter some data in the first blank row beneath the header.
-or-
Paste one or more pieces of data into the field, starting with the first blank
cell. Save the changes.

Before remove a column from datasheet, there are two rules which need to be
consider:
 • When you remove a column, you delete all the data in the column, and
you cannot undo the deletion. For that reason, you should back up the
table before you delete the column

• You cannot delete certain types of columns without some additional

work. For example, you cannot use Datasheet view to delete a primary
key field. You must use Design view to perform that task. Also, you
cannot delete a primary key or a lookup field without first deleting the
relationship between the field and the tables from which the field takes
its data. Explaining how to delete related fields is beyond the scope of
this article

3. Modifying Data
Access provides one text control for use with Short Text and Long Text (also
called Memo) fields. Typically, you can tell if the underlying field is short or long
text by the size of the control, which usually reflects the size needed for the
underlying table field. A Short Text field can store up to 255 characters and a
Long Text field can store 64,000 characters. Furthermore, the data can be
modified in Form View which by highlighting the data that are required to
modify, then edit the data by typing new information. Below is the example of
modification that have been made:

Before After
CREATING QUERIES

A query is a request for data results, and for action on data. You can use a query to
answer a simple question, to perform calculations, to combine data from different
tables, or even to add, change, or delete table data. Below is the example of queries
that have been generated in Access.

i. Design View

Design view of Queries

ii. SQL View

SELECT Customer.[CUSTOMER ID], Customer.NAME, Customer.[CONTACT

NO], Customer.EMAIL, Customer.[DATE REGISTER], Customer.[ID EXPIRED],
[Customer Addresses].[LINE 1], [Customer Addresses].[LINE 2], [Customer
Addresses].CITY, [Customer Addresses].POSTCODE, [Customer
Addresses].STATE

FROM Customer INNER JOIN [Customer Addresses] ON Customer.[CUSTOMER

ID] = [Customer Addresses].[CUSTOMER ID];
 iii. Datasheet View

CREATING FORMS
Access gives three main ways to create a form:

• Single mouse click

• Form wizard
• Design View

In this report, Form wizard are the method that have been used to generate the form.
The form that have been created as follow:
As shown in figure Order Form, the details which required are shown and detail of the
order can be select, edit and delete. Moreover, new order can be made by clicking the
sign . After new data have been insert, click save to ensure the data enter are send
to the database. Finally, the total price is formulated which the price times with the
quantity in order to get the result.

Conclusion
We created a database that a market can use for keeping track on business
purposes. Designing databases simply is a time-consuming business. Historical
sources will give rise to all manner of complications and problems, intellectual and
in terms of the mechanics of databases, and the more you can anticipate these
and accommodate them in the design of the database, the more efficient and less
frustrating the subsequent use of the database will be. No database is ever perfect,
and the only indicator of quality, or success, when it comes to database design is
whether or not it serves the various functions that you intended. If you can manage
the information from your sources in the way that you need, and if you can perform
the analysis that you require, and if you can be as flexible as you need in both of
these areas, then your design is successful. New things learn are if information
that would like to analyse which appears repeatedly, but have nowhere specific to
put it (i.e. for which you will need to add new fields). Watch out for your datatypes,
and change them where they are unhelpful. Look for data that could be
standardised or classified. Last but not least, look out for information that you had
not anticipated when designing the database.