CREATE TABLE IF NOT EXISTS public."Class"
(
    classid integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    classname character varying(50) COLLATE pg_catalog."default",
    CONSTRAINT pk__class__cb1927a0a166e951 PRIMARY KEY (classid)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Class"
    OWNER to postgres;




CREATE TABLE IF NOT EXISTS public."Course"
(
    courseid integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    title character varying(50) COLLATE pg_catalog."default",
    facultyid integer,
    name character varying(50) COLLATE pg_catalog."default",
    credits character varying(50) COLLATE pg_catalog."default",
    classid integer,
    CONSTRAINT pk__course__c92d71878c25379a PRIMARY KEY (courseid),
    CONSTRAINT "fk_dbo.class_dbo.course_classid" FOREIGN KEY (classid)
        REFERENCES public."Class" (classid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT "fk_dbo.course_dbo.faculty_facultyid" FOREIGN KEY (facultyid)
        REFERENCES public."Faculty" (facultyid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Course"
    OWNER to postgres;



CREATE TABLE IF NOT EXISTS public."Enrollment"
(
    enrollmentid integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    courseid integer NOT NULL,
    studentid integer NOT NULL,
    termid integer,
    marks integer DEFAULT 100,
    marksobtained integer,
    CONSTRAINT pk__enrollme__7f6877fb43b17c50 PRIMARY KEY (enrollmentid),
    CONSTRAINT "fk_dbo.enrollment_dbo.course_courseid" FOREIGN KEY (courseid)
        REFERENCES public."Course" (courseid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT "fk_dbo.enrollment_dbo.student_studentid" FOREIGN KEY (studentid)
        REFERENCES public."Student" (studentid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT "fk_dbo.term_dbo.student_termid" FOREIGN KEY (termid)
        REFERENCES public."Term" (termid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Enrollment"
    OWNER to postgres;


CREATE TABLE IF NOT EXISTS public."Faculty"
(
    facultyid integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "facultyfirstname " character varying(50) COLLATE pg_catalog."default",
    facultylastname character varying(50) COLLATE pg_catalog."default",
    CONSTRAINT pk__faculty__306f636e1530f78b PRIMARY KEY (facultyid)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Faculty"
    OWNER to postgres;



CREATE TABLE IF NOT EXISTS public."Section"
(
    sectionid integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    " classsectionname " character varying(50) COLLATE pg_catalog."default",
    CONSTRAINT pk__section__80ef0892521faf0f PRIMARY KEY (sectionid)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Section"
    OWNER to postgres;


CREATE TABLE IF NOT EXISTS public."Student"
(
    studentid integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    classid integer,
    "lastname " character varying(50) COLLATE pg_catalog."default",
    " firstname " character varying(50) COLLATE pg_catalog."default",
    " phoneno " integer,
    email character varying(50) COLLATE pg_catalog."default",
    " sectionid " integer,
    rollno integer,
    CONSTRAINT pk__student__32c52a7988904b48 PRIMARY KEY (studentid),
    CONSTRAINT "fk_dbo.class_dbo.student_classid" FOREIGN KEY (classid)
        REFERENCES public."Class" (classid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT "fk_dbo.section_dbo.student_sectionid" FOREIGN KEY (" sectionid ")
        REFERENCES public."Section" (sectionid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Student"
    OWNER to postgres;


CREATE TABLE IF NOT EXISTS public."Term"
(
    termid integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    termname character varying(50) COLLATE pg_catalog."default",
    CONSTRAINT pk__term__410a2e45e499cfeb PRIMARY KEY (termid)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Term"
    OWNER to postgres;