
CREATE TABLE branch (
       branch               INTEGER NOT NULL,
       specialization       INTEGER NOT NULL,
       branchdesc           VARCHAR2(20) NOT NULL,
       sepcializationdesc   VARCHAR2(20) NULL
);

CREATE UNIQUE INDEX XPKbranch ON branch
(
       branch                         ASC,
       specialization                 ASC
);


ALTER TABLE branch
       ADD  ( PRIMARY KEY (branch, specialization) ) ;


CREATE TABLE courses (
       year                 INTEGER NOT NULL,
       result               VARCHAR2(20) NULL,
       exam                 DATE NULL,
       term                 INTEGER NULL,
       assessment           DATE NULL,
       credits              INTEGER NULL,
       code                 VARCHAR2(20) NOT NULL,
       personalid           INTEGER NOT NULL,
       lector               INTEGER NOT NULL
);

CREATE UNIQUE INDEX XPKcourses ON courses
(
       personalid                     ASC,
       year                           ASC,
       code                           ASC
);


ALTER TABLE courses
       ADD  ( PRIMARY KEY (personalid, year, code) ) ;


CREATE TABLE person (
       bornnumber           VARCHAR2(20) NOT NULL,
       name                 VARCHAR2(20) NOT NULL,
       familyname           VARCHAR2(20) NOT NULL,
       street               VARCHAR2(20) NULL,
       city                 VARCHAR2(20) NULL,
       zipcode              VARCHAR2(20) NULL,
       nationality          VARCHAR2(20) NULL,
       district             VARCHAR2(20) NULL
);

CREATE UNIQUE INDEX XPKperson ON person
(
       bornnumber                     ASC
);


ALTER TABLE person
       ADD  ( PRIMARY KEY (bornnumber) ) ;


CREATE TABLE student (
       personalid           INTEGER NOT NULL,
       class                INTEGER NULL,
       studygroup           VARCHAR2(20) NULL,
       repeat               VARCHAR2(20) NULL,
       stopout              VARCHAR2(20) NULL,
       form                 VARCHAR2(20) NULL,
       finished             DATE NULL,
       state                VARCHAR2(20) NULL,
       firstassessment      DATE NULL,
       bornnumber           VARCHAR2(20) NOT NULL,
       branch               INTEGER NOT NULL,
       specialization       INTEGER NOT NULL
);

CREATE UNIQUE INDEX XPKstudent ON student
(
       personalid                     ASC
);


ALTER TABLE student
       ADD  ( PRIMARY KEY (personalid) ) ;


CREATE TABLE subject (
       code                 VARCHAR2(20) NOT NULL,
       name                 VARCHAR2(20) NOT NULL,
       credit               INTEGER NULL,
       gestor               INTEGER NOT NULL
);

CREATE UNIQUE INDEX XPKsubject ON subject
(
       code                           ASC
);


ALTER TABLE subject
       ADD  ( PRIMARY KEY (code) ) ;


CREATE TABLE teacher (
       personalid           INTEGER NOT NULL,
       name                 VARCHAR2(20) NULL,
       familyname           VARCHAR2(20) NOT NULL,
       department           VARCHAR2(20) NULL
);

CREATE UNIQUE INDEX XPKteacher ON teacher
(
       personalid                     ASC
);


ALTER TABLE teacher
       ADD  ( PRIMARY KEY (personalid) ) ;


ALTER TABLE courses
       ADD  ( FOREIGN KEY (lector)
                             REFERENCES teacher
                             ON DELETE SET NULL ) ;


ALTER TABLE courses
       ADD  ( FOREIGN KEY (personalid)
                             REFERENCES student ) ;


ALTER TABLE courses
       ADD  ( FOREIGN KEY (code)
                             REFERENCES subject ) ;


ALTER TABLE student
       ADD  ( FOREIGN KEY (branch, specialization)
                             REFERENCES branch
                             ON DELETE SET NULL ) ;


ALTER TABLE student
       ADD  ( FOREIGN KEY (bornnumber)
                             REFERENCES person
                             ON DELETE SET NULL ) ;


ALTER TABLE subject
       ADD  ( FOREIGN KEY (gestor)
                             REFERENCES teacher
                             ON DELETE SET NULL ) ;



