BACHELOR OF INFORMATION TECHNOLOGY WITH HONOURS

SEPTEMBER 2022 Semester 4

CBDB3403
DATABASE

LEARNING CENTRE : Sibu Learning Centre

1.0 Introduction
The COVID-19 immunisation campaign in Malaysia is being carried out by the
Malaysian government and was launched on February 24, 2021. It is a method for controlling
and interrupting the coronavirus disease's 2019 spread (COVID-19) by successfully fostering
herd immunity among Malaysian citizens and visitors.

The COVID-19 crisis saw a rise in the digital economy. People went to online
shopping for purchasing more frequently than ever before as they embraced social
withdrawal, and it has since taken on a bigger role in daily life. Digital information and
communication technologies, including computers and mobile devices, are used by online
stores to access and manage user, customer, product, supplier, cart, order, and payment data.
Database design for the online platform is needed for them to manage their business
activities. So, a database system is needed to manage all the information.

1
2.0 Entity, Attributes and Primary Key
There are seven entities have been discovered in this business activities process. Those can be
used to register as customer, log in as user to view the products they need to purchase from
supplier and add to carts for check out to make payments.

Entities Description
Customer Contain personal information about the customer who register as user.
User Use to identify user for log in to do online shopping.
Supplier Record the supplier information who supply the products.
Products Record the product information and price which register for online
shopping.
Carts Store the products information that user adds the products to the carts.
Orders Store the order information which have checked by User.
Payments Record the payment information.

Entities Attributes Primary key

Customer Customer_Id, Name, IC, Gender, DateOfBirth, address, Customer_Id
Postcode , City, State, Country, Tel, email, password
User User_Id, Customer_Id, UserName, email, password User_Id
Supplier Supplier_Id, Supplier_Name, Products_Quantity, Location Supplier_Id
Products Products_Id, Product_Name, Category, Price, Quantity, Products_Id
Brand, Supplier_Id
Carts Cart_Id, Customer_Id, Product_Id, Quantity, Add_at Cart_Id
Orders Order_Id, Cart_Id, Product_Name, Price, Quantity, Order_Id
Purchase_Date
Payments Payment_Id, Order_Id, Invoice_No, Total_Price, Payment_Id
Purchase_Date

2
3.0 ALL relationships, cardinality, and participation for each identified entity.

User
Relationship table Relationship type cardinality participation
Customer table One to one Exactly one Compulsory participation

Customer
Relationship table Relationship type cardinality participation
User table One to one Exactly one Compulsory participation

Products
Relationship table Relationship type cardinality participation
User table One to Many Zero, one or more Optional participation
Customer table One to Many Zero, one or more Optional participation

Supplier
Relationship table Relationship type cardinality participation
Products table One to Many Zero, one or more Optional participation

Carts
Relationship table Relationship type cardinality participation
User table One to Many Zero, one or more Optional participation
Products table Many to Many Zero, one or more Optional participation

Orders
Relationship table Relationship type cardinality participation
Cart Table One to Many Zero, one or more Optional participation

Payments
Relationship table Relationship type cardinality participation
Order Table One to One Exactly one Compulsory participation

3
4.0 Entity Relationships Diagram (ERD)

4
5.0 Relational Schema

Schema Relations Item

Customer Customer_Id, Name, IC, Gender, DateOfBirth, address, Postcode , City,
State, Country, Tel, email, password
User User_Id, Customer_Id, UserName, email, password
Supplier Supplier_Id, Supplier_Name, Products_Quantity, Location
Products Products_Id, Product_Name, Category, Price, Quantity, Brand, Supplier_Id
Carts Cart_Id, Customer_Id, Product_Id, Quantity, Add_at
Orders Order_Id, Cart_Id, Product_Name, Price, Quantity, Purchase_Date
Payments Payment_Id, Order_Id, Invoice_No, Total_Price, Purchase_Date

5
6.0 Relations tables

Table 1

User
Key Attributes Data Types
Primary Key User_Id Short Text
Foreign Key Customer_Id Short Text
UserName Short Text
email Short Text
password Short Text

6
Table 2

Customer
Key Attributes Data Types
Primary Key Customer_Id Short Text
Name Short Text
IC Number
Gender Short Text
DateOfBirth Date
address Long Text
Postcode Number
City Short Text
State
Country Short Text
Tel Number
email Short Text
password Short Text

7
Table 3

Products
Key Attributes Data Types
Primary Key Products_Id Short Text
Product_Name Short Text
Category Short Text
Price Decimal
Quantity Number
Brand Short Text
Foreign Key Supplier_Id Short Text

Table 4

Supplier
Key Attributes Data Types
Primary Key Supplier_Id Short Text
Supplier_Name Short Text
Products_Quantity Number
Location Short Text

8
Table 5

Carts
Key Attributes Data Types
Primary Key Cart_Id Short Text
Foreign Key Customer_Id Short Text
Foreign Key Product_Id Short Text
Quantity Number
Add_at Date

Table 6

Orders
Key Attributes Data Types
Primary Key Order_Id Short Text
Foreign Key Cart_Id Short Text
Product_Name Short Text
Price Decimal
Quantity Number
Purchase_Date Date

9
Table 7

Payments
Key Attributes Data Types
Primary Key Payment_Id Short Text
Foreign Key Order_Id Short Text
Invoice_No Short Text
Total_Price Decimal
Purchase_Date Date

10
7.0 Sample Record for each relation table

Customer table

User table

Supplier table

Products table

Carts table

11
Orders table

Payments table

12
8.0 SQL QUERY

1. Simple Query

Display the information of the customer.

SELECT * from Customer.

Result

13
2. Sorting result

Sorting the result of product in ascending order based on price.

SELECT * FROM Products ORDER BY [Price] ASC;

Result

14
3. AGGREGATE FUNCTION

This query calculates the total of the price in each order by multi the Price with the quantity
of the products which order by the customers.

SELECT Orders.*, [Orders]![Price]*[Orders]![Quantity] AS Total

FROM Orders;

Result

15
4. Sub-queries

Display and retrieve Customer Id, Name, phone number, Email from table carts.

SELECT [Customer_Id],[Name],[tel],[Email] FROM CUSTOMER WHERE Customer_Id

IN(SELECT Customer_Id FROM Carts WHERE Product_Id = "PROD0001");

Result

16
5. Query to alter the existing tables

Add a new Entities which is status after the password.

ALTER TABLE Customer

ADD STATUS VARCHAR(5);

Result

17
18