Table Creation

Create Table using Oracle SQL

-- Create Table: program
CREATE TABLE program (
    P_ID NUMBER PRIMARY KEY,
    P_name VARCHAR2(255),
    P_duration VARCHAR2(50),
    P_type VARCHAR2(50)
);

-- Create Table: dept
CREATE TABLE dept (
    D_ID NUMBER PRIMARY KEY,
    D_name VARCHAR2(255),
    D_head VARCHAR2(255)
);

-- Create Table: prodept
CREATE TABLE prodept (
    P_ID NUMBER PRIMARY KEY,
    D_ID NUMBER,
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID),
    FOREIGN KEY (P_ID) REFERENCES program(P_ID)
);

-- Create Table: deptconductexam
CREATE TABLE deptconductexam (
    E_ID NUMBER PRIMARY KEY,
    E_Date DATE,
    E_type VARCHAR2(50),
    E_roomno VARCHAR2(50),
    Mark NUMBER,
    D_ID NUMBER,
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID),
    FOREIGN KEY (E_ID, E_Date, E_type, E_roomno, Mark) REFERENCES exam(E_ID, E_Date, E_type, E_roomno, Mark)
);

-- Create Table: student
CREATE TABLE student (
    S_ID NUMBER PRIMARY KEY,
    S_name VARCHAR2(255),
    S_mail VARCHAR2(255),
    S_contact VARCHAR2(50),
    EnrollYear DATE
);

-- Create Table: exam
CREATE TABLE exam (
    E_ID NUMBER PRIMARY KEY,
    E_Date DATE,
    E_type VARCHAR2(50),
    E_roomno VARCHAR2(50),
    Mark NUMBER
);

-- Create Table: stdexam
CREATE TABLE stdexam (
    S_ID NUMBER PRIMARY KEY,
    E_ID NUMBER,
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (E_ID) REFERENCES exam(E_ID)
);

-- Create Table: stdfac
CREATE TABLE stdfac (
    S_ID NUMBER PRIMARY KEY,
    S_name VARCHAR2(255),
    S_contact VARCHAR2(50),
    S_mail VARCHAR2(255),
    EnrollYear DATE,
    F_ID NUMBER,
    FOREIGN KEY (S_ID, S_name, S_contact, S_mail, EnrollYear) REFERENCES student(S_ID, S_name, S_contact, S_mail, EnrollYear),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID)
    
);

-- Create Table: faculty
CREATE TABLE faculty (
    F_ID NUMBER PRIMARY KEY,
    F_name VARCHAR2(255),
    F_contact VARCHAR2(50),
    F_mail VARCHAR2(255)
);

-- Create Table: classroom
CREATE TABLE classroom (
    Cl_roomno VARCHAR2(50) PRIMARY KEY,
    Cl_time VARCHAR2(50),
    Cl_name VARCHAR2(255)
);

-- Create Table: stdcls
CREATE TABLE stdcls (
    S_ID NUMBER,
    Cl_roomno VARCHAR2(50),
    PRIMARY KEY (S_ID),
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (Cl_roomno) REFERENCES classroom(Cl_roomno)
);

-- Create Table: stdpro
CREATE TABLE stdpro (
    S_ID NUMBER PRIMARY KEY,
    P_ID NUMBER,
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (P_ID) REFERENCES program(P_ID)
);

-- Create Table: course
CREATE TABLE course (
    C_ID NUMBER PRIMARY KEY,
    C_name VARCHAR2(255),
    C_duration VARCHAR2(50),
    C_credit NUMBER
);

-- Create Table: stdcrs
CREATE TABLE stdcrs (
    S_ID NUMBER PRIMARY KEY,
    C_ID NUMBER,
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID)
);

-- Create Table: faccrs
CREATE TABLE faccrs (
    F_ID NUMBER PRIMARY KEY,
    C_ID NUMBER,
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID)
);

-- Create Table: crsexam
CREATE TABLE crsexam (
    E_ID NUMBER PRIMARY KEY,
    E_date DATE,
    E_type VARCHAR2(50),
    E_roomno VARCHAR2(50),
    Mark NUMBER,
    C_ID NUMBER,
    FOREIGN KEY (E_ID, E_date, E_type, E_roomno, Mark) REFERENCES exam(E_ID, E_Date, E_type, E_roomno, Mark),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID)
);

-- Create Table: deptofferfaccrs
CREATE TABLE deptofferfaccrs (
    F_ID NUMBER,
    F_name VARCHAR2(255),
    F_contact VARCHAR2(50),
    F_mail VARCHAR2(255),
    C_ID NUMBER,
    C_name VARCHAR2(255),
    C_duration VARCHAR2(50),
    C_credit NUMBER,
    D_ID NUMBER,
    PRIMARY KEY (F_ID, C_ID),
    FOREIGN KEY (F_ID, F_name, F_contact, F_mail) REFERENCES faculty(F_ID, F_name, F_contact, F_mail),
    FOREIGN KEY (C_ID, C_name, C_duration, C_credit) REFERENCES course(C_ID, C_name, C_duration, C_credit),
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID)
);

-- Create Table: deptauth
CREATE TABLE deptauth (
    D_ID NUMBER,
    D_name VARCHAR2(255),
    D_head VARCHAR2(255),
    A_mail VARCHAR2(255),
    PRIMARY KEY (D_ID),
    FOREIGN KEY (D_ID, D_name, D_head) REFERENCES dept(D_ID, D_name, D_head),
    FOREIGN KEY (A_mail) REFERENCES authority(A_mail)
);

-- Create Table: authority
CREATE TABLE authority (
    A_mail VARCHAR2(255) PRIMARY KEY,
    A_name VARCHAR2(255),
    A_role VARCHAR2(50),
    A_contact VARCHAR2(50)
);

-- Create Table: stdgrade
CREATE TABLE stdgrade (
    S_ID NUMBER PRIMARY KEY,
    S_name VARCHAR2(255),
    S_contact VARCHAR2(50),
    S_mail VARCHAR2(255),
    EnrollYear DATE,
    G_ID NUMBER,
    FOREIGN KEY (S_ID, S_name, S_contact, S_mail, EnrollYear) REFERENCES student(S_ID, S_name, S_contact, S_mail, EnrollYear),
    FOREIGN KEY (G_ID) REFERENCES grade(G_ID)
);

-- Create Table: grade
CREATE TABLE grade (
    G_ID NUMBER PRIMARY KEY,
    CGPA NUMBER(3, 2),
    Semester VARCHAR2(50),
    Sem_year VARCHAR2(50)
);

-- Create Table: facgrade
CREATE TABLE facgrade (
    G_ID NUMBER PRIMARY KEY,
    CGPA NUMBER(3, 2),
    Semester VARCHAR2(50),
    Sem_year VARCHAR2(50),
    F_ID NUMBER,
    FOREIGN KEY (G_ID, CGPA, Semester, Sem_year) REFERENCES grade(G_ID, CGPA, Semester, Sem_year),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID)
);

Create Table using MySQL

-- Create Table: program
CREATE TABLE program (
    P_ID INT PRIMARY KEY,
    P_name VARCHAR(255),
    P_duration VARCHAR(50),
    P_type VARCHAR(50)
);

-- Create Table: dept
CREATE TABLE dept (
    D_ID INT PRIMARY KEY,
    D_name VARCHAR(255),
    D_head VARCHAR(255)
);

-- Create Table: prodept
CREATE TABLE prodept (
    P_ID INT,
    D_ID INT,
    PRIMARY KEY (P_ID),
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID),
    FOREIGN KEY (P_ID) REFERENCES program(P_ID)
);

-- Create Table: deptconductexam
CREATE TABLE deptconductexam (
    E_ID INT PRIMARY KEY,
    E_Date DATE,
    E_type VARCHAR(50),
    E_roomno VARCHAR(50),
    Mark INT,
    D_ID INT,
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID),
    FOREIGN KEY (E_ID, E_Date, E_type, E_roomno, Mark) REFERENCES exam(E_ID, E_Date, E_type, E_roomno, Mark)
);

-- Create Table: student
CREATE TABLE student (
    S_ID INT PRIMARY KEY,
    S_name VARCHAR(255),
    S_mail VARCHAR(255),
    S_contact VARCHAR(50),
    EnrollYear DATE
);

-- Create Table: exam
CREATE TABLE exam (
    E_ID INT PRIMARY KEY,
    E_Date DATE,
    E_type VARCHAR(50),
    E_roomno VARCHAR(50),
    Mark INT
);

-- Create Table: stdexam
CREATE TABLE stdexam (
    S_ID INT PRIMARY KEY,
    E_ID INT,
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (E_ID) REFERENCES exam(E_ID)
);

-- Create Table: stdfac
CREATE TABLE stdfac (
    S_ID INT PRIMARY KEY,
    S_name VARCHAR(255),
    S_contact VARCHAR(50),
    S_mail VARCHAR(255),
    EnrollYear DATE,
    F_ID INT,
    FOREIGN KEY (S_ID, S_name, S_contact, S_mail, EnrollYear) REFERENCES student(S_ID, S_name, S_contact, S_mail, EnrollYear),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID)
);

-- Create Table: faculty
CREATE TABLE faculty (
    F_ID INT PRIMARY KEY,
    F_name VARCHAR(255),
    F_contact VARCHAR(50),
    F_mail VARCHAR(255)
);

-- Create Table: classroom
CREATE TABLE classroom (
    Cl_roomno VARCHAR(50) PRIMARY KEY,
    Cl_time VARCHAR(50),
    Cl_name VARCHAR(255)
);

-- Create Table: stdcls
CREATE TABLE stdcls (
    S_ID INT,
    Cl_roomno VARCHAR(50),
    PRIMARY KEY (S_ID),
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (Cl_roomno) REFERENCES classroom(Cl_roomno)
);

-- Create Table: stdpro
CREATE TABLE stdpro (
    S_ID INT PRIMARY KEY,
    P_ID INT,
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (P_ID) REFERENCES program(P_ID)
);

-- Create Table: course
CREATE TABLE course (
    C_ID INT PRIMARY KEY,
    C_name VARCHAR(255),
    C_duration VARCHAR(50),
    C_credit INT
);

-- Create Table: stdcrs
CREATE TABLE stdcrs (
    S_ID INT PRIMARY KEY,
    C_ID INT,
    FOREIGN KEY (S_ID) REFERENCES student(S_ID),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID)
);

-- Create Table: faccrs
CREATE TABLE faccrs (
    F_ID INT PRIMARY KEY,
    C_ID INT,
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID)
);

-- Create Table: crsexam
CREATE TABLE crsexam (
    E_ID INT PRIMARY KEY,
    E_date DATE,
    E_type VARCHAR(50),
    E_roomno VARCHAR(50),
    Mark INT,
    C_ID INT,
    FOREIGN KEY (E_ID, E_date, E_type, E_roomno, Mark) REFERENCES exam(E_ID, E_Date, E_type, E_roomno, Mark),
    FOREIGN KEY (C_ID) REFERENCES course(C_ID)
);

-- Create Table: deptofferfaccrs
CREATE TABLE deptofferfaccrs (
    F_ID INT,
    F_name VARCHAR(255),
    F_contact VARCHAR(50),
    F_mail VARCHAR(255),
    C_ID INT,
    C_name VARCHAR(255),
    C_duration VARCHAR(50),
    C_credit INT,
    D_ID INT,
    PRIMARY KEY (F_ID, C_ID),
    FOREIGN KEY (F_ID, F_name, F_contact, F_mail) REFERENCES faculty(F_ID, F_name, F_contact, F_mail),
    FOREIGN KEY (C_ID, C_name, C_duration, C_credit) REFERENCES course(C_ID, C_name, C_duration, C_credit),
    FOREIGN KEY (D_ID) REFERENCES dept(D_ID)
);

-- Create Table: deptauth
CREATE TABLE deptauth (
    D_ID INT,
    D_name VARCHAR(255),
    D_head VARCHAR(255),
    A_mail VARCHAR(255),
    PRIMARY KEY (D_ID),
    FOREIGN KEY (D_ID, D_name, D_head) REFERENCES dept(D_ID, D_name, D_head),
    FOREIGN KEY (A_mail) REFERENCES authority(A_mail)
);

-- Create Table: authority
CREATE TABLE authority (
    A_mail VARCHAR(255) PRIMARY KEY,
    A_name VARCHAR(255),
    A_role VARCHAR(50),
    A_contact VARCHAR(50)
);

-- Create Table: stdgrade
CREATE TABLE stdgrade (
    S_ID INT PRIMARY KEY,
    S_name VARCHAR(255),
    S_contact VARCHAR(50),
    S_mail VARCHAR(255),
    EnrollYear DATE,
    G_ID INT,
    FOREIGN KEY (S_ID, S_name, S_contact, S_mail, EnrollYear) REFERENCES student(S_ID, S_name, S_contact, S_mail, EnrollYear),
    FOREIGN KEY (G_ID) REFERENCES grade(G_ID)
);

-- Create Table: grade
CREATE TABLE grade (
    G_ID INT PRIMARY KEY,
    CGPA DECIMAL(3, 2),
    Semester VARCHAR(50),
    Sem_year VARCHAR(50)
);

-- Create Table: facgrade
CREATE TABLE facgrade (
    G_ID INT PRIMARY KEY,
    CGPA DECIMAL(3, 2),
    Semester VARCHAR(50),
    Sem_year VARCHAR(50),
    F_ID INT,
    FOREIGN KEY (G_ID, CGPA, Semester, Sem_year) REFERENCES grade(G_ID, CGPA, Semester, Sem_year),
    FOREIGN KEY (F_ID) REFERENCES faculty(F_ID)
);