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:

1. stevejobs – 1996-08-28

2. billgates – 1998-06-18

3. larry – 1997-05-25

4. mark – 1984-03-27

5. dell – 1955-08-15

6. eric – 1955-07-28

tblOrder:

1. (2, 2002-12-01)

2. (3, 2000-03-02)

3. (2, 2004-08-03)

4. (1, 2001-05-12)

5. (4, 2002-10-04)

6. (6, 2002-03-08)

7. (5, 2002-05-02)

tblProduct:

1. Asus Zen – 2 – 10 – "See what others can’t see."

2. BPhone – 10 – 20 – "The first flat-design smartphone in the world."

3. iPhone – 13 – 300 – "The only thing that’s changed is everything."

4. XPéria – 7 – 80 – "The world’s first 4K smartphone."

5. 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”.