Database Fundamentals

01 · DATABASE OVERVIEW

What Is a Database?

In practice, databases are used across many fields:

Example

A university might need to manage:

With tens of thousands of students, storing everything in separate files becomes hard to manage. A database organizes the data into related tables:

DATABASE: QuanLySinhVien

↓ 1-N relationship: one LOP has many SINHVIEN.

Database Management System (DBMS)

Some common DBMS products:

A database and a DBMS together form a complete database system: users/developers send queries through an application program, the DBMS receives and processes those queries, then retrieves the matching data from the database.

02 · SQL SERVER MANAGEMENT STUDIO

Installing SQL Server & SSMS

Before the practice sessions, install 2 components on your computer: SQL Server (the database engine) and SQL Server Management Studio - SSMS (the client tool for connecting, writing queries, and managing SQL Server). SQL Server doesn't come with a built-in interface, so SSMS is required.

Introducing Microsoft SQL Server

SQL Server is a relational database management system (RDBMS) built by Microsoft, first released in 1989 and continuously updated since then:

SOME MAJOR VERSION MILESTONES

SQL Server 2025 is the newest release, but this course still uses SQL Server 2022 to keep things stable and consistent across everyone's setup.

EDITIONS

Each SQL Server version is also packaged into several editions, serving different needs and budgets:

Installing SQL Server 2022 (Developer Edition)

1. Download the installer from the Microsoft site, choosing Download Media to download the setup files first instead of installing directly over the network.
2. Run SQLServer2022-DEV-x64-ENU.exe, go to Installation, and choose the Developer edition.
3. Accept the license terms. You can uncheck automatic update checks if you don't need them.
4. On the Feature Selection step, checking Database Engine Services alone is enough for this course.
5. Leave the Instance ID at its default, MSSQLSERVER.
6. On the authentication step (Database Engine Configuration), leave it on the default Windows Authentication Mode and click Next - no need to switch to Mixed Mode or set an sa password for individual practice.
7. Wait for the install to finish, verify the components installed successfully, then click Close.

Installing SQL Server Management Studio (SSMS)

1. Download the SSMS installer from the Microsoft site (search "Download SQL Server Management Studio").
2. Run SSMS-Setup-ENU.exe and click Install.
3. Wait a few minutes for the install to finish, then click Close.

Once installed, the SSMS interface is typically used to:

• Connect to SQL Server
• Create a Database
• Create a Table
• Write and execute SQL statements
• View data
• Manage objects within the Database

Basic Workflow

Example: Your First Statement

SELECT 'Hello SQL Server';

Result:

03 · DATA TYPES IN SQL

Data Types in SQL

Integer Types

MaSV INT
SoLuong INT
Tuoi TINYINT

Boolean Type - BIT

BIT stores true/false values, commonly used for "yes/no" columns:

DaTotNghiep BIT

Stored values: 1 (true), 0 (false), or NULL (undefined).

Decimal / Floating-Point Types

DECIMAL(p,s) lets you define the total number of digits (p) and the number of digits after the decimal point (s). For example:

DiemTB DECIMAL(4,2)

Can store: 8.50, 9.25, 10.00

MONEY and SMALLMONEY are dedicated to currency values, more precise than FLOAT/REAL for calculations:

DonGia MONEY

String Types

CHAR(n) - a fixed-length string (always occupies exactly n characters, padded with spaces):

MaSV CHAR(8)

Example value: "23520001"

VARCHAR(n) - a variable-length string (only uses as much storage as the actual number of characters):

Email VARCHAR(100)

NCHAR and NVARCHAR - similar to CHAR/VARCHAR but store Unicode strings, suitable for storing accented Vietnamese text:

HoTen NVARCHAR(50)
DiaChi NVARCHAR(200)

When text can be very long with no fixed length in advance (e.g. a product description), use VARCHAR(MAX) or NVARCHAR(MAX) instead of declaring a fixed size.

Quick Comparison - Which String Type to Use?

INSERT INTO SinhVien
VALUES (1, N'Nguyễn Văn An', 'HTTT2022.1');

Date and Time Types

NgaySinh DATE
NgayLap DATETIME

You can change the expected input format with SET DATEFORMAT. For example, to enter dates as day-month-year 'dd-mm-yyyy':

SET DATEFORMAT dmy;

INSERT INTO SinhVien (MaSV, HoTen, NgaySinh, Lop)
VALUES (104, N'Phạm Thị Dung', '20-05-2004', N'HTTT2022.1');

SET DATEFORMAT only affects the current SSMS session - it does not permanently change the database configuration.

04 · DATABASE

Database

Creating a Database

Syntax:

CREATE DATABASE <Database name>;

Example:

CREATE DATABASE QuanLySinhVien;

Selecting a Database to Work With

After creating a database, use USE:

USE QuanLySinhVien;

From this point on, subsequent statements run against the QuanLySinhVien database. Always check which database is currently selected in SSMS before running commands.

Dropping a Database

DROP DATABASE QuanLySinhVien;

05 · TABLE

Table

A Table consists of Columns (data attributes) and Rows (records). A Table is a collection of records sharing the same structure. Example:

SINHVIEN

SQL uses its own set of terms, corresponding to the terms used in relational database theory:

To define a table in SQL, you need: the table name, its columns, each column's data type, and any integrity constraints on it.

Creating a Table

Basic syntax:

CREATE TABLE <Table name>
(
    <Column 1> <Data type>,
    <Column 2> <Data type>,
    ...
);

Example:

CREATE TABLE SinhVien
(
    MaSV INT,
    HoTen NVARCHAR(50),
    NgaySinh DATE,
    Lop NVARCHAR(20)
);

Dropping a Table

DROP TABLE SinhVien;

ALTER TABLE

ALTER TABLE is used to change a Table's structure after it has been created.

Add a column

ALTER TABLE SinhVien
ADD Email NVARCHAR(100);

Drop a column

ALTER TABLE SinhVien
DROP COLUMN Email;

Change a column's data type

ALTER TABLE SinhVien
ALTER COLUMN NgaySinh DATE;

06 · INTEGRITY CONSTRAINTS

Constraints

Types of Integrity Constraints

Integrity constraints in SQL Server fall into 2 main categories:

Week 1 only covers the simple constraints below - TRIGGER is covered in later weeks:

PRIMARY KEY

The primary key uniquely identifies each record in a Table.

CREATE TABLE SinhVien
(
    MaSV INT PRIMARY KEY,
    HoTen NVARCHAR(50),
    NgaySinh DATE
);

This means no two students can share the same MaSV.

Composite Primary Key

Sometimes the primary key isn't a single column but a combination of several columns - common in "detail" tables representing N-N relationships. For example, an CTHD (invoice line item) table has no dedicated ID column, and instead uses the pair (SOHD, MASP) to uniquely identify each row:

CREATE TABLE CTHD
(
    SOHD INT,
    MASP CHAR(4),
    SL INT,

    CONSTRAINT PK_CTHD PRIMARY KEY (SOHD, MASP)
);

Placing the CONSTRAINT on its own line at the end is the only way to declare a multi-column primary key - you cannot write PRIMARY KEY after each individual column in this case.

NOT NULL

NOT NULL requires a column to always have a value.

CREATE TABLE SinhVien
(
    MaSV INT PRIMARY KEY,
    HoTen NVARCHAR(50) NOT NULL,
    NgaySinh DATE
);

The following statement is invalid because HoTen is declared NOT NULL:

INSERT INTO SinhVien
VALUES (1, NULL, '2004-05-20');

UNIQUE

Ensures values in a column are never duplicated.

CREATE TABLE SinhVien
(
    MaSV INT PRIMARY KEY,
    Email NVARCHAR(100) UNIQUE
);

FOREIGN KEY

FOREIGN KEY is used to create a relationship between tables. For example, given two tables:

LOP

↑ SINHVIEN.MaLop references LOP.MaLop.

SINHVIEN

CREATE TABLE Lop
(
    MaLop INT PRIMARY KEY,
    TenLop NVARCHAR(50)
);

CREATE TABLE SinhVien
(
    MaSV INT PRIMARY KEY,
    HoTen NVARCHAR(50),
    MaLop INT,

    FOREIGN KEY (MaLop)
        REFERENCES Lop(MaLop)
);

In that case, the following statement is rejected if 999 doesn't exist in Lop.MaLop:

INSERT INTO SinhVien
VALUES (101, N'Nguyễn Văn An', 999);

The foreign key must be defined on the table that holds it.

CHECK

CHECK restricts data to satisfy a condition. For example, a student's grade must be between 0 and 10:

CREATE TABLE KetQua
(
    MaSV INT,
    Diem DECIMAL(4,2),

    CHECK (Diem >= 0 AND Diem <= 10)
);

INSERT INTO KetQua
VALUES (101, 8.5);

✓ Valid. But:

INSERT INTO KetQua
VALUES (102, 15);

✗ Invalid.

DEFAULT

DEFAULT supplies a fallback value when the user doesn't provide one.

CREATE TABLE SinhVien
(
    MaSV INT PRIMARY KEY,
    HoTen NVARCHAR(50),
    GioiTinh CHAR(1) DEFAULT 'M'
);

If:

INSERT INTO SinhVien(MaSV, HoTen)
VALUES (101, N'Nguyễn Văn An');

then SQL Server automatically sets GioiTinh = 'M'.

Declaring Multiple Constraints

Multiple constraints can be combined - data types and constraints always go together when designing a Table:

CREATE TABLE SinhVien
(
    MaSV INT PRIMARY KEY,

    HoTen NVARCHAR(50) NOT NULL,

    Email NVARCHAR(100) UNIQUE,

    MaLop INT
        FOREIGN KEY REFERENCES Lop(MaLop),

    NgaySinh DATE
        CHECK (NgaySinh >= '1900-01-01'),

    GioiTinh CHAR(1)
        DEFAULT 'M'
);

07 · DATA MANIPULATION - DML

Data Manipulation Language

In Week 1, students get familiar with:

INSERT - Adding Data

Method 1: Without specifying column names

INSERT INTO SinhVien
VALUES (101, N'Nguyễn Văn An', '2004-05-20', N'HTTT2022.1');

Method 2: Specifying column names - this is the preferred method, since the statement is clearer and less dependent on column order.

INSERT INTO SinhVien
(
    MaSV,
    HoTen,
    NgaySinh,
    Lop
)
VALUES
(
    101,
    N'Nguyễn Văn An',
    '2004-05-20',
    N'HTTT2022.1'
);

Multiple records can be inserted in a single statement:

INSERT INTO SinhVien
VALUES
    (101, N'Nguyễn Văn An', '2004-05-20', N'HTTT2022.1'),
    (102, N'Trần Thị Bình', '2004-08-15', N'HTTT2022.1'),
    (103, N'Lê Văn Cường', '2004-11-02', N'HTTT2022.2');

Then verify:

SELECT *
FROM SinhVien;

DELETE - Removing Data

DELETE FROM <Table name>
WHERE <Condition>;

Example - only the student with MaSV = 101 is deleted:

DELETE FROM SinhVien
WHERE MaSV = 101;

UPDATE - Modifying Data

UPDATE <Table name>
SET <Column 1> = <New value>
WHERE <Condition>;

UPDATE SinhVien
SET HoTen = N'Nguyễn Văn Nam'
WHERE MaSV = 101;

Multiple columns can be updated at once:

UPDATE SinhVien
SET
    HoTen = N'Nguyễn Văn Nam',
    Lop = N'HTTT2022.2'
WHERE MaSV = 101;

SELECT INTO - Copying Data

SELECT INTO creates a new Table from a query's result.

SELECT *
INTO SinhVien_Backup
FROM SinhVien;

To copy only part of the data, filter with WHERE combined with the LIKE operator - used to match strings against a pattern with 2 wildcard characters:

SELECT *
INTO SinhVien_HTTT
FROM SinhVien
WHERE Lop LIKE 'HTTT%';

Comprehensive Example - Student Management

Step 1 - Create the Database

CREATE DATABASE QuanLySinhVien;
GO

USE QuanLySinhVien;
GO

Step 2 - Create the Class Table

CREATE TABLE Lop
(
    MaLop INT PRIMARY KEY,
    TenLop NVARCHAR(50) NOT NULL
);

Step 3 - Create the Student Table

CREATE TABLE SinhVien
(
    MaSV INT PRIMARY KEY,
    HoTen NVARCHAR(50) NOT NULL,
    NgaySinh DATE,
    MaLop INT,

    CONSTRAINT FK_SinhVien_Lop
        FOREIGN KEY (MaLop)
        REFERENCES Lop(MaLop),

    CONSTRAINT CK_SinhVien_NgaySinh
        CHECK (NgaySinh >= '1900-01-01')
);

Step 4 - Insert Classes

INSERT INTO Lop
VALUES
    (1, N'HTTT2022.1'),
    (2, N'HTTT2022.2');

Step 5 - Insert Students

INSERT INTO SinhVien
VALUES
    (101, N'Nguyễn Văn An', '2004-05-20', 1),
    (102, N'Trần Thị Bình', '2004-08-15', 1),
    (103, N'Lê Văn Cường', '2004-11-02', 2);

Step 6 - Verify

SELECT *
FROM SinhVien;

Step 7 - Update

UPDATE SinhVien
SET HoTen = N'Nguyễn Văn Nam'
WHERE MaSV = 101;

Step 8 - Delete

DELETE FROM SinhVien
WHERE MaSV = 103;

08 · PRACTICE EXERCISES & Q&A

Comprehensive Practice

Additional worked examples using the sample QuanLyBanHang database, to reinforce this week's DDL/Constraint content - not part of the BTTH1 submission.

Exercise 6 - Constraint Testing Example

Try entering invalid data to observe SQL Server's error messages:

INSERT INTO SinhVien
VALUES (101, N'Duplicate ID student', '2004-01-01', 1);

➜ Violates PRIMARY KEY.

INSERT INTO SinhVien
VALUES (104, N'Student without a class', '2004-01-01', 999);

➜ Violates FOREIGN KEY.

INSERT INTO SinhVien
VALUES (105, N'Student with invalid date', '1800-01-01', 1);

➜ Violates CHECK.

09 · END-OF-WEEK 1 CHECKLIST

Self-Check

After completing this week, students should check that they can:

• Know what a Database is
• Know how to use SQL Server Management Studio
• Know the basic data types
• Know CREATE DATABASE
• Know USE DATABASE
• Know CREATE TABLE
• Know DROP TABLE
• Know ALTER TABLE
• Understand PRIMARY KEY
• Understand FOREIGN KEY
• Understand UNIQUE
• Understand NOT NULL
• Understand CHECK
• Understand DEFAULT
• Know INSERT
• Know UPDATE
• Know DELETE
• Know SELECT INTO
• Know how to verify data after an operation

Core Concepts to Remember

This is the foundation for all of IT004. In Week 2, students begin using this data to perform SELECT, operators, functions, JOIN, set operations, and Subqueries.