Design

Database Diagrams

Create Database

CREATE DATABASE StudentManagementSystem;

Create Table

USE StudentManagementSystem;

CREATE TABLE Students (
    StudentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT
    -- Single Line Comments
    -- Birthdate DATE
);

CREATE TABLE Courses (
    CourseID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Description VARCHAR(250)
);

CREATE TABLE Enrollments (
    EnrollmentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    EnrollmentDate DATE NOT NULL DEFAULT GETDATE(),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Alter Table

ALTER TABLE Students
DROP COLUMN Age;

ALTER TABLE Students
ADD Birthdate DATE;

Insert Data

Master Data

INSERT INTO Courses (Name, Description)
VALUES ('Mathematics', 'Fundamentals of Mathematics'),
       ('Physics', 'Introduction to Physics'),
       ('Chemistry', 'Basic Concepts of Chemistry'),
       ('Computer Science', 'Programming and Algorithms');

/* Multi-line Comments
INSERT INTO Students (Name, Age)
VALUES ('Alice', 20); */

INSERT INTO Students (Name, Birthdate)
VALUES ('Alice', '2002-01-01'),
       ('Bob', '2000-05-15'),
       ('Charlie', '2003-03-07'),
       ('David', '2005-11-25');

Transaction Data

INSERT INTO Enrollments (StudentID, CourseID)
VALUES (1, 1),
       (1, 2);

INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate)
VALUES (2, 3, '2023-01-01'),
       (3, 4, '2022-01-02');