1. Database Creation
-
Create a database named
ProductManagementSystem -
Make use of the database
2. Table Creation
tblUser – table to store users info
-
UserID: INT, NOT NULL
-
UserName: NVARCHAR(50)
tblOrder – table to store orders info
-
OrderID: INT, NOT NULL
-
UserID: INT, NOT NULL
-
OrderDate: DATETIME
tblProduct – table to store products info
-
ProductID: INT, NOT NULL
-
ProductName: NVARCHAR(50)
-
Quantity: INT
-
Price: MONEY
-
Description: NTEXT
tblOrderDetail – table to store order details
-
OrderID: INT, NOT NULL
-
ProductID: INT, NOT NULL
-
Quantity: INT
-
Price: MONEY
3. Index Creation and Table Alternation
-
Create a Clustered Index:
-
CI_tblUser_UserID on
tblUser(UserID)
-
-
Drop the clustered index created above.
-
Alter table
tblUser, add new column[BirthDate]with datatype DATETIME.
DEFAULT
-
DF_tblOrder_OrderDate → Table: tblOrder, Column: OrderDate, Default Value: GETDATE()
PRIMARY KEY
-
PK_tblUser → tblUser(UserID)
-
PK_tblOrder → tblOrder(OrderID)
-
PK_tblProduct → tblProduct(ProductID)
-
PK_tblOrderDetail → tblOrderDetail(OrderID, ProductID)
FOREIGN KEY
-
FK_tblOrder_tblUser → tblOrder(UserID) → tblUser(UserID)
-
FK_tblOrderDetail_tblOrder → tblOrderDetail(OrderID) → tblOrder(OrderID)
-
FK_tblOrderDetail_tblProduct → tblOrderDetail(ProductID) → tblProduct(ProductID)
CHECK
-
CK_tblOrder_OrderDate → tblOrder(OrderDate), condition: OrderDate is between ‘2000-01-01’ and current date
UNIQUE
-
UN_tblUser_UserName → tblUser(UserName)
5. Inserting Data
tblUser:
-
stevejobs – 1996-08-28
-
billgates – 1998-06-18
-
larry – 1997-05-25
-
mark – 1984-03-27
-
dell – 1955-08-15
-
eric – 1955-07-28
tblOrder:
-
(2, 2002-12-01)
-
(3, 2000-03-02)
-
(2, 2004-08-03)
-
(1, 2001-05-12)
-
(4, 2002-10-04)
-
(6, 2002-03-08)
-
(5, 2002-05-02)
tblProduct:
-
Asus Zen – 2 – 10 – "See what others can’t see."
-
BPhone – 10 – 20 – "The first flat-design smartphone in the world."
-
iPhone – 13 – 300 – "The only thing that’s changed is everything."
-
XPéria – 7 – 80 – "The world’s first 4K smartphone."
-
Galaxy Note – 12 – 120 – "Created to reflect your desire."
tblOrderDetail:
-
(1,1,10,10)
-
(1,2,4,20)
-
(2,3,5,50)
-
(3,4,6,80)
-
(4,2,21,120)
-
(5,2,122,300)
6. Query Operations
-
Update [Price] of all records in the table [tblProduct] with 10% discount, where [ProductID] = 3.
-
Display all records from 4 tables tblUser, tblOrder, tblOrderDetail, tblProduct as:
(UserName, OrderID, OrderDate, Quantity, Price, ProductName)
7. Views
-
Create a view named
[view_Top2Product]to display top 2 best seller products.
8. Procedure
Create a procedure named [sp_TimSanPham] with parameters:
-
@GiaMua MONEY (Max price to filter product)
-
@count INT OUTPUT (Total number of records found)
Execute [sp_TimSanPham] with @GiaMua = 50.
9. Trigger
Create a trigger for UPDATE event named [TG_tblProduct_Update] to check value of [Price] column of tblProduct.
-
If updated Price < 10 → rollback and print message: “You don’t update price less than 10”.
Drop this trigger.
10. Trigger
Create a trigger for UPDATE event named [TG_tblUser_Update] to check value of [UserName] column of tblUser.
-
If UserName column is updated → rollback and print message: “You don’t update the column UserName”.
