ddl.sql

-- Create STUDENT table
CREATE TABLE STUDENT (
    USN VARCHAR(10) PRIMARY KEY,
    SNAME VARCHAR(25),
    ADDRESS VARCHAR(25),
    PHONE VARCHAR(10),
    GENDER CHAR(1)
);

-- Create SEMSEC table
CREATE TABLE SEMSEC (
    SSID VARCHAR(5) PRIMARY KEY,
    SEM INTEGER,
    SEC CHAR(1)
);

-- Create CLASS table
CREATE TABLE CLASS (
    USN VARCHAR(10),
    SSID VARCHAR(5),
    PRIMARY KEY (USN, SSID),
    FOREIGN KEY (USN) REFERENCES STUDENT(USN),
    FOREIGN KEY (SSID) REFERENCES SEMSEC(SSID)
);

-- Create SUBJECT table
CREATE TABLE SUBJECT (
    SUBCODE VARCHAR(8) PRIMARY KEY,
    TITLE VARCHAR(20),
    SEM INTEGER,
    CREDITS INTEGER
);

-- Create IAMARKS table
CREATE TABLE IAMARKS (
    USN VARCHAR(10),
    SUBCODE VARCHAR(8),
    SSID VARCHAR(5),
    TEST1 INTEGER,
    TEST2 INTEGER,
    TEST3 INTEGER,
    FINALIA INTEGER,
    PRIMARY KEY (USN, SUBCODE, SSID),
    FOREIGN KEY (USN) REFERENCES STUDENT(USN),
    FOREIGN KEY (SUBCODE) REFERENCES SUBJECT(SUBCODE),
    FOREIGN KEY (SSID) REFERENCES SEMSEC(SSID)
);