Database System Assignment

Group 1

1. Data Requirement Analysis and

Description
a) Research on related applications/systems (reference)
One of the most representative e-commerce platforms in Vietnam is Shopee
([Link] This is a large-scale online marketplace that operates under a hybrid model of
B2C (Business to Customer) and C2C (Customer to Customer). Shopee acts as an intermediary
that connects buyers and sellers, while also providing various supporting services to ensure that
transactions are safe and convenient.

The business process of Shopee can be analyzed as follows: for buyers, they search for
products through the search bar or categories, add items to the shopping cart, select shipping and
payment methods, and then confirm their orders. During the process, buyers can track the status of
their orders and write reviews after successful delivery. For sellers, they are required to create a shop
account, upload products, manage orders, and coordinate with shipping providers. Revenue from each
order is processed and transferred according to the platform’s policies. Shopee also provides
promotional tools such as discount codes and marketing campaigns to support sellers.

According to the “Vietnam E-commerce Business Landscape Report, H1 2023” by Metric,

Shopee accounted for 63% of the market share in the number of products sold in Q2/2023,
ranking first in Vietnam’s e-commerce industry. This demonstrates that Shopee is a relevant and
typical reference case for analyzing and designing a sample system.

b) Description of the proposed system

i. Description of the application/system, user types, and main functions

The proposed system is an online e-commerce platform inspired by Shopee but designed at a
medium scale to manage the entire online sales process. The system includes three main types of
users:

1. Buyers – end users who search for products, purchase them, manage shopping carts, and
write product reviews.

2. Sellers – individuals or businesses that own a shop, manage products, and handle customer
orders.

3. Administrators – responsible for overseeing the entire system, handling complaints,

managing users, and ensuring data validity.

The main functions include: user account management, shop management, product creation
and updates, product search and categorization, cart and order management, discount code
management, shipping tracking, review submission and browsing, as well as support for multiple
payment methods.

ii. Description of entities, attributes, and relationships

The system is built upon several tightly connected entities. Users are the central entity, each
identified by a unique ID and associated with information such as username, password, email, phone
number, date of birth, and role. A user can become a seller by creating a Shop. Each shop has its own
ID, name, description, rating, and is linked to exactly one user.

Within a shop, sellers upload multiple Products, each defined by attributes such as product
ID, name, description, price, stock quantity, and images. Products are categorized under Categories,
which may be hierarchical since one category can contain subcategories.

Buyers can add products to their Cart, which can then be converted into Orders. Each order
has a unique ID, creation date, status, total value, and shipping address, linked to the buyer. Orders are
detailed through OrderItems, which specify the quantity and unit price of each product at the time of
purchase. To complete the process, each order has a corresponding Payment record with attributes
such as payment method, date, and amount, as well as an entry in Shipping, which manages delivery
methods, costs, and shipping status.

After order completion, buyers can write Reviews for products, including a rating and
comments, linked to both the user and the product. In addition, the system manages Discounts with
applicable conditions and links them to orders through OrderDiscounts to track promotion usage
history.

c) Semantic constraints
The system enforces several semantic constraints, including:

● Each user can create only one shop.

● Each cart belongs to exactly one user.

● Every product must be linked to a shop.

● A user may write a review only for products they have purchased.

● Ratings must be between 1 and 5.

● Each order must include at least one product.

● Discounts can only be applied if the order meets the minimum condition.

● Each order can have only one payment record.

These constraints ensure that the system reflects real-world business logic and maintains data
integrity.

2. EERD Design
3. Mapping to Database Schema
Based on the EERD design in Part 2, we mapped all entities, attributes, and relationships into
relational tables. The resulting schema contains 13 tables, which satisfies the requirement of at least
12 tables for a group of four members. Each table is created with appropriate primary keys, foreign
keys, and constraints to ensure referential integrity and enforce the semantic rules described in Part 1.

The mapping process is as follows:

● Users: This entity is mapped into the Users table with attributes such as UserID (PK),
Username, Password, Email, Phone, BirthDate, and Role.

● Shops: Each shop is linked to exactly one user. Therefore, the Shops table includes ShopID
(PK), ShopName, Description, Rating, and UserID (FK, unique).

● Categories: The recursive relationship among categories is modeled by ParentID, a foreign

key referencing CategoryID.

● Products: Mapped into the Products table with ProductID (PK), ProductName, Description,
Price, Stock, Image, and foreign keys ShopID and CategoryID.
 ● Cart and CartItems: To support the shopping cart function, we introduce the Cart table
(CartID, UserID, CreatedDate) and the CartItems table to capture the many-to-many
relationship between Cart and Products.

● Orders and OrderItems: The Orders table represents purchase orders, while the OrderItems
table records the products, quantity, and price details in each order.

● Payments: Each order has one payment, represented by the Payments table with attributes
such as PaymentID, PaymentMethod, PaymentDate, Amount, and OrderID (unique FK).

● Shipping: Delivery information is stored in the Shipping table, linked 1:1 with an order.

● Reviews: User feedback on purchased products is stored in the Reviews table, linked to both
Users and Products.

● Discounts and OrderDiscounts: Discounts are represented by the Discounts table, and the
many-to-many relationship between Orders and Discounts is managed via OrderDiscounts.

In total, the schema has 13 tables, and each relationship type (1:1, 1:N, and M:N) has been
carefully mapped into either foreign keys or intersection tables. Semantic constraints such as "a user
can only own one shop," "an order must have at least one order item," or "a review must belong to a
purchased product" are enforced through a combination of schema design and application logic.