lab2

DROP TABLE TERMS CASCADE CONSTRAINTS;
DROP TABLE PAYMENT CASCADE CONSTRAINTS;
DROP TABLE STUDENTS CASCADE CONSTRAINTS;
DROP TABLE ATTESTATION CASCADE CONSTRAINTS;
DROP TABLE SUBJECTS CASCADE CONSTRAINTS;
DROP SEQUENCE STUDENT_SEQ;
DROP SEQUENCE SUBJECTS_SEQ;
DROP SEQUENCE ATTESTATION_SEQ;
DROP SEQUENCE PAYMENT_SEQ;

CREATE TABLE STUDENTS (
    STUDENT_ID      INTEGER PRIMARY KEY ,
    n_record_book   VARCHAR2(7) NOT NULL UNIQUE,
    fio             VARCHAR2(128) NOT NULL,
    SEMESTER        VARCHAR(2) NOT NULL,
    CHECK (SEMESTER>0)
);


CREATE INDEX STUDENTS_INDEX ON STUDENTS(N_RECORD_BOOK, fio);
COMMIT;

CREATE SEQUENCE STUDENT_SEQ
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20;
  COMMIT;


CREATE SEQUENCE SUBJECTS_SEQ
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20;
  COMMIT;

CREATE SEQUENCE ATTESTATION_SEQ
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20;
  COMMIT;

CREATE SEQUENCE PAYMENT_SEQ
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20;
  COMMIT;

INSERT INTO STUDENTS VALUES(STUDENT_SEQ.NEXTVAL,'1423369','Sidorov Petr', '1' );
COMMIT;

CREATE TABLE SUBJECTS (
    subject_id     INTEGER PRIMARY KEY ,
    subject_name   VARCHAR2(128)    NOT NULL
);

INSERT INTO SUBJECTS VALUES(SUBJECTS_SEQ.NEXTVAL, 'SUBD' );
COMMIT;

CREATE TABLE terms (
    N_SEMESTER                INTEGER NOT NULL,
    TYPE                  VARCHAR2(128)    NOT NULL,
    hours                 INTEGER NOT NULL,
    SUBJECT_ID            INTEGER NOT NULL REFERENCES SUBJECTS(SUBJECT_ID) ON DELETE CASCADE,
    test                  VARCHAR2(1),
    exam                  INTEGER
);

CREATE TABLE ATTESTATION (
    ATTESTATION_ID         INTEGER NOT NULL PRIMARY KEY,
    date_attestation       DATE NOT NULL,
    mark                   INTEGER NOT NULL,
    subject_id             INTEGER NOT NULL REFERENCES SUBJECTS(SUBJECT_ID) ON DELETE CASCADE
);

CREATE TABLE PAYMENT (
    PAYMENT_ID                         INTEGER NOT NULL PRIMARY KEY,
    data_payment                       DATE NOT NULL,
    account_number                     INTEGER NOT NULL,
    amount_money                       INTEGER NOT NULL,
    n_card                             INTEGER,
    card_type                          VARCHAR2(15),
    paymaster_id                       INTEGER,
    attestation_id                     INTEGER NOT NULL REFERENCES ATTESTATION(ATTESTATION_ID) ON DELETE CASCADE
);