Database Creation with File Configuration

In SQL Server, the CREATE DATABASE statement allows precise control over database file storage. The following example creates an educational management database with customized settings for both primary data and log files:

CREATE DATABASE EduManagementDB
ON PRIMARY
(
    NAME = 'EduData',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\EduData.mdf',
    SIZE = 20MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
)
LOG ON
(
    NAME = 'EduLog',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\EduLog.ldf',
    SIZE = 10MB,
    MAXSIZE = 200MB,
    FILEGROWTH = 5MB
);
GO

The primary file (.mdf) stores database objects with an initial allocation of 20MB and grows in 10MB increments. The transaction log (.ldf) is capped at 200MB to prevent uncontrolled disk usage. Verification of successful creation queries the system catalog:

SELECT name, database_id, create_date
FROM sys.databases
WHERE name = 'EduManagementDB';
GO

Table Structure Definition with Constraints

Four interconnected tables form the core of the educational system. Each table employs various constraint types to maintain data integrity.

Learner Table

Stores student records with validation on gender and age ranges:

USE EduManagementDB;
GO
CREATE TABLE Learner
(
    learner_id CHAR(10) PRIMARY KEY,
    full_name VARCHAR(25) NOT NULL,
    gender CHAR(2) CHECK (gender IN ('M', 'F')),
    age INT CHECK (age >= 16 AND age <= 35),
    department VARCHAR(40)
);
GO

Instructor Table

Contains faculty information with a default job title:

CREATE TABLE Instructor
(
    instructor_id CHAR(8) PRIMARY KEY,
    instructor_name VARCHAR(25) NOT NULL,
    gender CHAR(2) CHECK (gender IN ('M', 'F')),
    job_title VARCHAR(20) DEFAULT 'Assistant Professor',
    department VARCHAR(40) NOT NULL
);
GO

Subject Table

Defines courses with unique names and credit validation:

CREATE TABLE Subject
(
    subject_code CHAR(6) PRIMARY KEY,
    subject_name VARCHAR(35) NOT NULL UNIQUE,
    credits INT CHECK (credits BETWEEN 1 AND 8),
    instructor_id CHAR(8) REFERENCES Instructor(instructor_id),
    CONSTRAINT CHK_Credits_Valid CHECK (credits > 0)
);
GO

Enrollment Table

Records student-course relationships with a composite primary key:

CREATE TABLE Enrollment
(
    learner_id CHAR(10) NOT NULL,
    subject_code CHAR(6) NOT NULL,
    score INT CHECK (score >= 0 AND score <= 100),
    CONSTRAINT PK_Enrollment PRIMARY KEY (learner_id, subject_code),
    CONSTRAINT FK_Enrollment_Learner FOREIGN KEY (learner_id) REFERENCES Learner(learner_id),
    CONSTRAINT FK_Enrollment_Subject FOREIGN KEY (subject_code) REFERENCES Subject(subject_code)
);
GO

Table creation can be verified through system procedures:

SELECT name, create_date FROM sys.tables;
GO
EXEC sp_help 'Learner';
GO

Constraint Validation Testing

Integrity constraints actively prevent invalid data insertion. The following tests demonstrate constraint enforcement:

USE EduManagementDB;
GO
-- CHECK constraint: Invalid gender value triggers error
INSERT INTO Learner(learner_id, full_name, gender, age, department)
VALUES ('S20240001', 'John Doe', 'X', 22, 'Computer Science');
GO

-- DEFAULT constraint: Missing job_title uses default value
INSERT INTO Instructor(instructor_id, instructor_name, gender, department)
VALUES ('INS00001', 'Jane Smith', 'F', 'Mathematics');
SELECT instructor_id, instructor_name, job_title FROM Instructor WHERE instructor_id = 'INS00001';
GO

-- UNIQUE constraint: Duplicate subject name rejected
INSERT INTO Subject(subject_code, subject_name, credits, instructor_id)
VALUES ('CS201', 'Database Systems', 4, 'INS00001');
INSERT INTO Subject(subject_code, subject_name, credits, instructor_id)
VALUES ('CS202', 'Database Systems', 3, 'INS00001');  -- Fails
GO

-- FOREIGN KEY constraint: Non-existent learner_id rejected
INSERT INTO Enrollment(learner_id, subject_code, score)
VALUES ('S99999999', 'CS201', 88);  -- Fails: learner does not exist
GO

SQL Server raises errors for each constraint violation, confirming that the data definition rules are operational. The foreign key constraint specifically maintains referential integrity between tables, ensuring enrollment records only reference valid learners and subjects.