Database - OnlineShop

Sơ đồ thực thể mối quan hệ trong CSDL

Explanation:

1. The Customer entity has a one-to-many relationship with the Order entity, indicating that a customer can place multiple orders.
2. The Employee entity has a one-to-many relationship with the Order entity, indicating that an employee can process multiple orders.
3. The Order entity has a one-to-many relationship with the OrderDetails entity, indicating that an order can include multiple order details.
4. The OrderDetails entity has a one-to-many relationship with the Product entity, indicating that each order detail describes a single product.
5. The Product entity has a many-to-one relationship with the Category entity, indicating that each product belongs to a single category.
6. The Product entity has a many-to-one relationship with the Supplier entity, indicating that each product is supplied by a single supplier.

Categories

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	Name	nvarchar(50)				UNIQUE
3	Description	nvarchar(500)	yes

Sample data:

Id	Name	Description
1	Books	Category for all types of books
2	Electronics	Category for electronic devices
3	Clothing	Category for clothing items
4	Home Goods	Category for items used in the home
5	Beauty	Category for cosmetic and personal care products

Suppliers

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	Name	nvarchar(100)
3	Email	varchar(50)				UNIQUE
4	PhoneNumber	varchar(50)				UNIQUE
5	Address	nvarchar(500)

Sample data:

Id	Name	Email	PhoneNumber	Address
1	Supplier A	suppliera@example.com	(555) 555-1234	123 Main St, USA
2	Supplier B	supplierb@example.com	(555) 555-5678	456 Elm St, USA
3	Supplier C	supplierc@example.com	(555) 555-9012	789 Oak St, USA
4	Supplier D	supplierd@example.com	(555) 555-3456	1011 Pine St, USA
5	Supplier E	suppliere@example.com	(555) 555-7890	1213 Maple St, USA

Customers

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	FirstName	nvarchar(50)
3	LastName	nvarchar(50)
4	Email	varchar(50)				UNIQUE
5	PhoneNumber	varchar(50)				UNIQUE
6	Address	nvarchar(500)
7	Birthday	datetime	yes

Sample data:

Id	FirstName	LastName	Email	PhoneNumber	Address	Birthday
1	John	Smith	johnsmith@example.com	0905-555-123	123 Main St, USA	1990-01-01
2	Jane	Doe	janedoe@example.com	0905-555-567	456 Elm St, USA	1985-03-15
3	Mike	Johnson	mikejohnson@example.com	0905-555-901	789 Oak St, USA	1995-11-23
4	Sarah	Lee	sarahlee@example.com	0905-555-345	1011 Pine St, USA	1982-06-10
5	Alex	Kim	alexkim@example.com	0905-555-789	1213 Maple St, USA	1992-12-31

Employees

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	FirstName	nvarchar(50)
3	LastName	nvarchar(50)
4	Email	varchar(50)				UNIQUE
5	PhoneNumber	varchar(50)				UNIQUE
6	Address	nvarchar(500)
7	Birthday	datetime	yes

Sample data:

Id	FirstName	LastName	Email	PhoneNumber	Address	Birthday
1	David	Johnson	davidjohnson@example.com	0905-555-123	123 Main St, USA	1980-05-15
2	Lisa	Smith	lisasmith@example.com	0905-555-567	456 Elm St, USA	1987-09-21
3	Michael	Brown	michaelbrown@example.com	0905-555-901	789 Oak St, USA	1992-02-28
4	Sarah	Davis	sarahdavis@example.com	0905-555-345	1011 Pine St, USA	1985-12-07
5	Kevin	Wilson	kevinwilson@example.com	0905-555-789	1213 Maple St, USA	1994-06-30

Products

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	Name	nvarchar(100)
3	Price	money				n > 0
4	Discount	decimal(18,2)			0	0 <= n <= 90
5	Stock	decimal(18,2)			0	n >= 0
6	Description	nvarchar(max)	yes
7	CategoryId	int		FK		Refrence to Categories (Id)
8	SupplierId	int		FK		Refrence to Suppliers (Id)

Sample data:

Id	Name	Price	Discount	Stock	Description	CategoryId	SupplierId
1	Product A	10.99	0.00	100	Description of Product A	2	1
2	Product B	19.99	0.20	50	Description of Product B	1	2
3	Product C	5.99	0.00	200	Description of Product C	3	3
4	Product D	49.99	0.10	10	Description of Product D	4	1
5	Product E	29.99	0.05	75	Description of Product E	2	2

Orders

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	CreatedDate	datetime			NOW
3	ShippedDate	datetime	yes			n > CreatedDate
4	Status	varchar(50)			WAITING	n in ['WAITING', 'COMPLETED', 'CANCELED']
5	Description	nvarchar(max)
6	ShippingAddress	nvarchar(500)	yes
7	ShippingCity	nvarchar(50)
8	PaymentType	varchar(20)			CASH	n in ['CASH', 'CREDIT CARD']
9	CustomerId	int		FK		Refrence to Customers (Id)
10	EmployessId	int		FK		Refrence to Employees (Id)

Sample data:

Id	CreatedDate	ShippedDate	Status	Description	ShippingAddress	ShippingCity	PaymentType	CustomerId	EmployeeId
1	2022-01-15	2022-01-20	Completed	Order #1	123 Main St, USA	Anytown	Credit Card	1	3
2	2022-02-10	2022-02-15	Completed	Order #2	456 Elm St, USA	Anytown	Cash	2	2
3	2022-03-05	2022-03-08	Completed	Order #3	789 Oak St, USA	Anytown	Credit Card	3	4
4	2022-04-01	2022-04-05	Completed	Order #4	1011 Pine St, USA	Anytown	Cash	1	5
5	2022-05-10		Waiting	Order #5			Credit Card	2	1

---

Order Details

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	OrderId	int		PK + FK		Refrence to Orders (Id)
2	ProductId	int		PK + FK		Refrence to Products (Id)
3	Quantity	decimal(18,2)				n > 0
4	Price	decimal(18,2)				n > 0
5	Discount	decimal(18,2)				0 <= n <=90

Sample data:

OrderId	ProductId	Quantity	Price	Discount
1	1	2	10.99	0.00
1	3	1	5.99	0.00
2	2	1	19.99	0.20
2	4	2	49.99	0.10
3	1	3	10.99	0.00
3	5	2	29.99	0.05