Relational Database Specifications

1. Entity-Relationship (ER) Architecture

Logical data routing mapping across the 4 primary collections:

• Course ➔ Assignment: One-to-Many relationship linked via CourseID.
• Assignment ➔ Submission: One-to-Many relationship linked via AssignmentID.
• Student ➔ Submission: One-to-Many relationship linked via StudentID.

2 & 3. Table Schemas & Data Constraints (DDL)

Run this script inside your local database terminal to build your tables with cascading deletes and checks:

-- 1. Create Course Table
CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Department VARCHAR(100),
    Credits INT
);

-- 2. Create Student Table
CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    FullName VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Major VARCHAR(50),
    SubmissionsCount INT DEFAULT 0
);

-- 3. Create Assignment Table (with Cascade and MaxMarks Constraint)
CREATE TABLE Assignment (
    AssignmentID INT PRIMARY KEY,
    CourseID INT,
    Title VARCHAR(200) NOT NULL,
    MaxMarks DECIMAL(5,2),
    PublishDate DATE,
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID) ON DELETE CASCADE,
    CONSTRAINT chk_MaxMarks CHECK (MaxMarks > 0)
);

-- 4. Create Submission Table (with Cascades and Grade Constraint)
CREATE TABLE Submission (
    SubmissionID INT PRIMARY KEY,
    AssignmentID INT,
    StudentID INT,
    SubmissionDate DATE,
    Grade DECIMAL(5,2),
    FOREIGN KEY (AssignmentID) REFERENCES Assignment(AssignmentID) ON DELETE CASCADE,
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ON DELETE CASCADE,
    CONSTRAINT chk_PositiveGrade CHECK (Grade >= 0)
);

4 - 7. Mock Data, Selections, Aggregations & Subqueries

Queries for handling calculations, string filtering, and missing data audits:

-- Task 4: Insert Sample Rows & Add Bonus Marks for Early Submissions
INSERT INTO Course VALUES (101, 'Data Structures', 'Computer Science', 4);
INSERT INTO Student VALUES (201, 'Saugat Niraula', 'saugat@email.com', 'CS', 0);
INSERT INTO Assignment VALUES (301, 101, 'Term Project Framework', 50.00, '2026-05-01');
INSERT INTO Submission VALUES (401, 301, 201, '2026-04-28', 42.00);

UPDATE Submission 
SET Grade = Grade + 5 
WHERE SubmissionDate < '2026-05-01';

-- Task 5: Wildcard Text Filtering Query
SELECT * FROM Assignment 
WHERE Title LIKE '%Project%' AND MaxMarks > 20;

-- Task 6: Class Performance Aggregates (Struggling Courses)
SELECT CourseID, AVG(Grade) AS ClassAverage 
FROM Submission 
JOIN Assignment USING (AssignmentID)
GROUP BY CourseID 
HAVING AVG(Grade) < 60.0;

-- Task 7: Subquery tracking missing assignments
SELECT FullName FROM Student 
WHERE StudentID NOT IN (SELECT DISTINCT StudentID FROM Submission);

8 - 10. System Triggers, Global Assertions & Security

Scripts for data access authorization, timeline protection rules, and dynamic database triggers:

-- Task 8: Database Trigger to Increment SubmissionsCount Automatically
CREATE OR REPLACE FUNCTION update_submission_count()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE Student 
    SET SubmissionsCount = SubmissionsCount + 1 
    WHERE StudentID = NEW.StudentID;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_AfterSubmissionInsert
AFTER INSERT ON Submission
FOR EACH ROW 
EXECUTE FUNCTION update_submission_count();

-- Task 9: Global Timeline Assertion Verification Rule
CREATE ASSERTION assert_TimelineSafety CHECK (
    NOT EXISTS (
        SELECT 1 FROM Assignment a
        JOIN Submission s USING (AssignmentID)
        WHERE s.SubmissionDate < a.PublishDate
    )
);

-- Task 10: Access Profiles (Privilege Management)
CREATE ROLE AppAdmin;
CREATE ROLE RegularUser;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO AppAdmin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO RegularUser;

-- Revoke specific deletion access to avoid data loss accidents
REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM AppAdmin;