Core Objectives

1. Master SQL Server authentication mechanisms: generate server-level logins and database-level users via CREATE LOGIN and CREATE USER, and build custom database roles with CREATE ROLE.
2. Apply the principle of least privilege using GRANT, DENY, and REVOKE to isolate access for student, instructor, and administrator roles.
3. Execute full and differential database backups using BACKUP DATABASE, and understand the use cases for each backup type.
4. Perform database restoration from full and differential backups using RESTORE DATABASE, and validate data recoveyr after simulated loss events.

Task 1: Security Configuration (Users, Roles, Permissions)

1.1 Create Server Logins and Database Users

Generate three SQL-authenticated logins for different roles, then map them to users in the EduManageDB database:

-- Switch to master to create server-level logins
USE master;
GO

-- Create student login
CREATE LOGIN Stu_Login 
WITH PASSWORD = 'StuPass2025!',
     DEFAULT_DATABASE = EduManageDB,
     CHECK_POLICY = ON,
     CHECK_EXPIRATION = ON;
GO

-- Create instructor login
CREATE LOGIN Inst_Login 
WITH PASSWORD = 'InstPass2025!',
     DEFAULT_DATABASE = EduManageDB,
     CHECK_POLICY = ON,
     CHECK_EXPIRATION = ON;
GO

-- Create admin login
CREATE LOGIN Admin_Login 
WITH PASSWORD = 'AdminPass2025!',
     DEFAULT_DATABASE = EduManageDB,
     CHECK_POLICY = ON,
     CHECK_EXPIRATION = ON;
GO

-- Map logins to database users in EduManageDB
USE EduManageDB;
GO
CREATE USER Stu_User FOR LOGIN Stu_Login;
GO
CREATE USER Inst_User FOR LOGIN Inst_Login;
GO
CREATE USER Admin_User FOR LOGIN Admin_Login;
GO

1.2 Define Custom Roles and Assign Permissions

Create roles for each user type and configure granular access:

USE EduManageDB;
GO

-- Student role: read-only access to relevant views
CREATE ROLE R_Student;
GO
GRANT SELECT ON V_Student_Computer TO R_Student;
GRANT SELECT ON V_Student_Grades TO R_Student;
DENY INSERT, UPDATE, DELETE ON V_Student_Computer TO R_Student;
DENY INSERT, UPDATE, DELETE ON V_Student_Grades TO R_Student;
GO

-- Instructor role: query core tables, update grade fields only
CREATE ROLE R_Instructor;
GO
GRANT SELECT ON Instructor TO R_Instructor;
GRANT SELECT ON Course TO R_Instructor;
GRANT SELECT ON Enrollment TO R_Instructor;
GRANT UPDATE (Score) ON Enrollment TO R_Instructor;
DENY DELETE ON Enrollment TO R_Instructor;
GO

-- Admin role: full database control via db_owner
CREATE ROLE R_Admin;
GO
EXEC sp_addrolemember 'db_owner', 'R_Admin';
GO

1.3 Associate Users with Roles

Link each user to their corresponding role and validate access:

USE EduManageDB;
GO
EXEC sp_addrolemember 'R_Student', 'Stu_User';
EXEC sp_addrolemember 'R_Instructor', 'Inst_User';
EXEC sp_addrolemember 'R_Admin', 'Admin_User';
GO

-- Validation examples (run under respective logins):
-- Stu_Login: SELECT * FROM V_Student_Grades works; UPDATE V_Student_Grades SET Score=90 fails
-- Inst_Login: UPDATE Enrollment SET Score=90 works; DELETE FROM Enrollment fails
-- Admin_Login: All operations including DDL work

Task 2: Database Backup (Full and Differential)

2.1 Full Backup

Create a baseline full backup of the EduManageDB database:

BACKUP DATABASE EduManageDB
TO DISK = 'D:\DBBackups\EduManageDB_Full.bak'
WITH 
    NAME = 'EduManageDB_Full_Base',
    DESCRIPTION = 'Baseline full backup for teaching system',
    COMPRESSION,
    INIT;
GO

2.2 Simulate Data Changes

Insert new enrollment records to represent routine data updates:

USE EduManageDB;
GO
INSERT INTO Enrollment (StudentID, CourseID, Score)
VALUES
('2023010118', 'MA102', 88),
('2023010117', 'MA101', 92),
('2023010116', 'CS102', 85);
GO

-- Verify inserts
SELECT * FROM Enrollment 
WHERE StudentID IN ('2023010118', '2023010117', '2023010116');
GO

2.3 Differential Backup

Backup only the data changed since the full backup:

BACKUP DATABASE EduManageDB
TO DISK = 'D:\DBBackups\EduManageDB_Diff.bak'
WITH 
    NAME = 'EduManageDB_Diff_Update',
    DESCRIPTION = 'Differential backup with new enrollment records',
    COMPRESSION,
    DIFFERENTIAL,
    INIT;
GO

Task 3: Database Restoration (Full and Differential)

3.1 Simulate Data Loss

Delete the newly inserted records to mimic accidental data deletion:

USE EduManageDB;
GO
DELETE FROM Enrollment
WHERE StudentID IN ('2023010118', '2023010117', '2023010116')
  AND CourseID IN ('MA102', 'MA101', 'CS102');
GO

-- Confirm deletion
SELECT * FROM Enrollment 
WHERE StudentID IN ('2023010118', '2023010117', '2023010116');
GO

3.2 Full Restoration

Restore the database to the state of the initial full backup:

USE master;
GO
-- Set single-user mode to avoid connection conflicts
ALTER DATABASE EduManageDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

RESTORE DATABASE EduManageDB
FROM DISK = 'D:\DBBackups\EduManageDB_Full.bak'
WITH 
    RECOVERY,
    REPLACE;
GO

-- Switch back to multi-user mode
ALTER DATABASE EduManageDB
SET MULTI_USER;
GO

-- Verify: new records are not present (full backup predates inserts)
USE EduManageDB;
GO
SELECT * FROM Enrollment 
WHERE StudentID IN ('2023010118', '2023010117', '2023010116');
GO

3.3 Differential Restoration

Restore the database to include the post-backup data changes:

USE master;
GO
ALTER DATABASE EduManageDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Restore full backup first without recovering the database
RESTORE DATABASE EduManageDB
FROM DISK = 'D:\DBBackups\EduManageDB_Full.bak'
WITH 
    NORECOVERY,
    REPLACE;
GO

-- Apply differential backup and bring database online
RESTORE DATABASE EduManageDB
FROM DISK = 'D:\DBBackups\EduManageDB_Diff.bak'
WITH 
    RECOVERY;
GO

ALTER DATABASE EduManageDB
SET MULTI_USER;
GO

-- Validate: all 3 deleted records are recovered
USE EduManageDB;
GO
SELECT * FROM Enrollment 
WHERE StudentID IN ('2023010118', '2023010117', '2023010116');
GO

-- Check total record count to ensure no data loss
SELECT COUNT(*) AS TotalEnrollments FROM Enrollment;
GO

Key Takeaways

• Security controls enforce role-based access: students have read-only view access, instructors can update grades only, and admins have full control, adhering to least privilege.
• Full backups capture the entire data base, while differential backups only store changes since the last full backup, making them efficient for frequent incremental backups.
• Restoration requires single-user mode to avoid connection conflicts, and differential recovery must follow a full backup restore in NORECOVERY state.

DBMS Core Functions

A Database Management System provides four primary functions:

1. Data Definition: Supports DDL statements (CREATE, ALTER, DROP) to manage database objects like tables, views, and indexes.
2. Data Manipulation: Provides DML statements (SELECT, INSERT, UPDATE, DELETE) for querying and modifying data.
3. Data Control & Security: Implements permission management, role-based access, and encryption to ensure data integrity, security, and consistent concurrent access.
4. Storage & Maintenance: Manages persistent data storage, backup/recovery, and performance optimization for reliable and efficient data access.