Scribd
Upload
3 views1 page

SQL Script for Task Management DB

The document contains SQL code to create a database named 'WorkToDo' if it does not exist. It defines three tables: 'USERS' for user accounts, 'TASKS' for task management, and 'NHACVIEC' for reminders, with appropriate foreign key constraints and default values. Additionally, it includes the creation of indexes for efficient data retrieval on the 'TASKS' and 'NHACVIEC' tables.

Uploaded by

tiendien05
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
3 views1 page

SQL Script for Task Management DB

The document contains SQL code to create a database named 'WorkToDo' if it does not exist. It defines three tables: 'USERS' for user accounts, 'TASKS' for task management, and 'NHACVIEC' for reminders, with appropriate foreign key constraints and default values. Additionally, it includes the creation of indexes for efficient data retrieval on the 'TASKS' and 'NHACVIEC' tables.

Uploaded by

tiendien05
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

IF DB_ID(N'WorkToDo') IS NULL

BEGIN
EXEC(N'CREATE DATABASE [WorkToDo]');
END

/* ĐANG Ở DB: WorkToDo */


-- Xóa nếu đã tồn tại
IF OBJECT_ID('[Link]', 'U') IS NOT NULL DROP TABLE [Link];
IF OBJECT_ID('[Link]', 'U') IS NOT NULL DROP TABLE [Link];
IF OBJECT_ID('[Link]', 'U') IS NOT NULL DROP TABLE [Link];

-- USERS
CREATE TABLE [Link](
TaiKhoan VARCHAR(50) NOT NULL PRIMARY KEY,
MatKhau NVARCHAR(255) NOT NULL,
HoTen NVARCHAR(120) NOT NULL,
TaoLuc DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);

-- TASKS
CREATE TABLE [Link](
TaskId INT IDENTITY(1,1) PRIMARY KEY,
TaiKhoan VARCHAR(50) NOT NULL,
CongViec NVARCHAR(200) NOT NULL,
MoTa NVARCHAR(MAX) NULL,
IsDone BIT NOT NULL DEFAULT 0,
TaoLuc DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);
ALTER TABLE [Link]
ADD CONSTRAINT FK_TASKS_USERS
FOREIGN KEY (TaiKhoan) REFERENCES [Link](TaiKhoan)
ON DELETE CASCADE;

-- NHACVIEC (đơn giản)


CREATE TABLE [Link](
MaNhac INT IDENTITY(1,1) PRIMARY KEY,
TaskId INT NOT NULL,
NhacLuc DATETIME2(0) NOT NULL,
KieuLap TINYINT NOT NULL DEFAULT 0, -- 0=không lặp,1=hàng ngày,2=hàng
tuần
GhiChu NVARCHAR(200) NULL,
DangHoatDong BIT NOT NULL DEFAULT 1
);
ALTER TABLE [Link]
ADD CONSTRAINT FK_NHACVIEC_TASKS
FOREIGN KEY (TaskId) REFERENCES [Link](TaskId)
ON DELETE CASCADE;

-- Index tham khảo


CREATE INDEX IX_TASKS_User_Time ON [Link](TaiKhoan, TaoLuc DESC);
CREATE INDEX IX_NHACVIEC_Next ON [Link](DangHoatDong, NhacLuc)
INCLUDE(TaskId);

You might also like