eworker.ddl

-- Generated by Oracle SQL Developer Data Modeler 18.4.0.339.1532
--   at:        2019-04-12 15:27:08 CEST
--   site:      SQL Server 2008
--   type:      SQL Server 2008



CREATE TABLE company (
    id              INTEGER NOT NULL,
    company_name    VARCHAR(50) NOT NULL,
    city            VARCHAR(127) NOT NULL,
    street          VARCHAR(127),
    house_number    VARCHAR(20) NOT NULL,
    flat_number     VARCHAR(20),
    email           VARCHAR(127) NOT NULL,
    phone           VARCHAR(20) NOT NULL,
    description     VARCHAR(255) NOT NULL,
    password        VARCHAR(255) NOT NULL,
    creation_date   datetime NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores information about company.' , 'USER' , 'dbo' , 'TABLE' , 'company' , 'COLUMN' , 'id' go

ALTER TABLE company ADD constraint company_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE degree_course (
    id              INTEGER NOT NULL,
    name            VARCHAR(255) NOT NULL,
    department_id   INTEGER NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores information about available degree courses.' , 'USER' , 'dbo' , 'TABLE' , 'degree_course' , 'COLUMN' , 'id' go

ALTER TABLE degree_course ADD constraint degree_course_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE department (
    id     INTEGER NOT NULL,
    name   VARCHAR(255) NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores available departments to study in.' , 'USER' , 'dbo' , 'TABLE' , 'department' , 'COLUMN' , 'id' go

ALTER TABLE department ADD constraint department_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE job_info (
    id                               INTEGER NOT NULL,
    text                             text NOT NULL,
    job_offer_id                     INTEGER NOT NULL,
    job_type_info_job_type_info_id   NUMERIC(28) NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores information about job.' , 'USER' , 'dbo' , 'TABLE' , 'job_info' , 'COLUMN' , 'id' go

ALTER TABLE job_info ADD constraint job_info_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE job_offer (
    id              INTEGER NOT NULL,
    sallary_from    INTEGER NOT NULL,
    sallary_to      INTEGER NOT NULL,
    creation_date   datetime NOT NULL,
    company_id      INTEGER NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores job offers.' , 'USER' , 'dbo' , 'TABLE' , 'job_offer' , 'COLUMN' , 'id' go

ALTER TABLE job_offer ADD constraint job_offer_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE job_offer_language (
    id             INTEGER NOT NULL,
    language_id    INTEGER NOT NULL,
    job_offer_id   INTEGER
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores information about required language for specific job.' , 'USER' , 'dbo' , 'TABLE' , 'job_offer_language' , 'COLUMN' , 'id' go

ALTER TABLE job_offer_language ADD constraint job_offer_language_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE job_offer_skill (
    id              INTEGER NOT NULL,
    job_offer_id1   INTEGER NOT NULL,
    skill_id        INTEGER NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores information about required skill for specific job offer. ' , 'USER' , 'dbo' , 'TABLE' , 'job_offer_skill' , 'COLUMN' , 'id' go

ALTER TABLE job_offer_skill ADD constraint job_offer_skill_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE job_type_info 
    (
    id                 INTEGER NOT NULL,
    name               text NOT NULL,
    job_type_info_id   NUMERIC(28) NOT NULL IDENTITY NOT FOR REPLICATION 
    )
GO 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores types of requirements for job offer. ' , 'USER' , 'dbo' , 'TABLE' , 'job_type_info' , 'COLUMN' , 'id' go

ALTER TABLE job_type_info ADD constraint job_type_info_pk PRIMARY KEY CLUSTERED (job_type_info_ID)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE language (
    id     INTEGER NOT NULL,
    name   VARCHAR(50) NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores available languages to pick from. ' , 'USER' , 'dbo' , 'TABLE' , 'language' , 'COLUMN' , 'id' go

ALTER TABLE language ADD constraint language_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE language_lvl (
    id            INTEGER NOT NULL,
    level         VARCHAR(50) NOT NULL,
    language_id   INTEGER NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores information about available levels of specific language.' , 'USER' , 'dbo' , 'TABLE' , 'language_lvl' , 'COLUMN' , 'id' go

ALTER TABLE language_lvl ADD constraint language_lvl_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE message (
    id              INTEGER NOT NULL,
    header          VARCHAR(50) NOT NULL,
    content         VARCHAR(512) NOT NULL,
    creation_date   datetime NOT NULL,
    student_id      INTEGER NOT NULL,
    company_id      INTEGER NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores messages for eworker system.' , 'USER' , 'dbo' , 'TABLE' , 'message' , 'COLUMN' , 'id' go

ALTER TABLE message ADD constraint message_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE offer_degree_course (
    id                 INTEGER NOT NULL,
    degree_course_id   INTEGER NOT NULL,
    job_offer_id       INTEGER NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores required degree course for specific job offer.' , 'USER' , 'dbo' , 'TABLE' , 'offer_degree_course' , 'COLUMN' , 'id' go

ALTER TABLE offer_degree_course ADD constraint offer_degree_course_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE skill (
    id     INTEGER NOT NULL,
    name   VARCHAR(255) NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores available skills to pick from.' , 'USER' , 'dbo' , 'TABLE' , 'skill' , 'COLUMN' , 'id' go

ALTER TABLE skill ADD constraint skill_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE student (
    id              INTEGER NOT NULL,
    name            VARCHAR(50) NOT NULL,
    surname         VARCHAR(50) NOT NULL,
    phone           VARCHAR(20) NOT NULL,
    email           VARCHAR(127) NOT NULL,
    password        VARCHAR(255) NOT NULL,
    city            VARCHAR(127),
    street          VARCHAR(127),
    house_number    VARCHAR(20),
    flat_number     VARCHAR(20),
    creation_date   datetime NOT NULL,
    sallary_from    INTEGER,
    sallary_to      INTEGER,
    document_url    CHAR,
    interest_text   VARCHAR(255),
    description     VARCHAR(255)
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores required information about student.' , 'USER' , 'dbo' , 'TABLE' , 'student' , 'COLUMN' , 'id' go

ALTER TABLE student ADD constraint student_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE student_degree_course (
    id                 INTEGER NOT NULL,
    student_id         INTEGER NOT NULL,
    degree_course_id   INTEGER NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores information about student's degree course. ' , 'USER' , 'dbo' , 'TABLE ' , ' student_degree_course ' , ' column ' , ' id ' 
GO

ALTER TABLE student_degree_course ADD constraint student_degree_course_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE student_info (
    id                     INTEGER NOT NULL,
    text                   text,
    start_date             DATE,
    end_date               DATE,
    present                bit,
    student_type_info_id   INTEGER NOT NULL,
    student_id             INTEGER NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores optional student info.' , 'USER' , 'dbo' , 'TABLE' , 'student_info' , 'COLUMN' , 'id' go

ALTER TABLE student_info ADD constraint student_info_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE student_language (
    id            INTEGER NOT NULL,
    student_id    INTEGER NOT NULL,
    language_id   INTEGER NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores student languages. ' , 'USER' , 'dbo' , 'TABLE' , 'student_language' , 'COLUMN' , 'id' go

ALTER TABLE student_language ADD constraint student_language_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE student_skill (
    id           INTEGER NOT NULL,
    student_id   INTEGER NOT NULL,
    skill_id     INTEGER NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores student skills. ' , 'USER' , 'dbo' , 'TABLE' , 'student_skill' , 'COLUMN' , 'id' go

ALTER TABLE student_skill ADD constraint student_skill_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

CREATE TABLE student_type_info (
    id     INTEGER NOT NULL,
    name   text NOT NULL
)

go 


EXEC sp_addextendedproperty 'MS_Description' , 'Table which stores types of information for ex. achievements, volunteer etc. ' , 'USER' , 'dbo' , 'TABLE' , 'student_type_info' , 'COLUMN' , 'id' go

ALTER TABLE student_type_info ADD constraint student_type_info_pk PRIMARY KEY CLUSTERED (id)
     WITH (
     ALLOW_PAGE_LOCKS = ON , 
     ALLOW_ROW_LOCKS = ON ) go

ALTER TABLE offer_degree_course
    ADD CONSTRAINT course_job_offer_fk FOREIGN KEY ( job_offer_id )
        REFERENCES job_offer ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE student_degree_course
    ADD CONSTRAINT course_student_fk FOREIGN KEY ( student_id )
        REFERENCES student ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE degree_course
    ADD CONSTRAINT degree_course_department_fk FOREIGN KEY ( department_id )
        REFERENCES department ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE offer_degree_course
    ADD CONSTRAINT degree_course_fk FOREIGN KEY ( degree_course_id )
        REFERENCES degree_course ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE student_degree_course
    ADD CONSTRAINT degree_course_fkv2 FOREIGN KEY ( degree_course_id )
        REFERENCES degree_course ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE job_info
    ADD CONSTRAINT job_info_job_offer_fk FOREIGN KEY ( job_offer_id )
        REFERENCES job_offer ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE job_info
    ADD CONSTRAINT job_info_job_type_info_fk FOREIGN KEY ( job_type_info_job_type_info_id )
        REFERENCES job_type_info ( job_type_info_id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE job_offer
    ADD CONSTRAINT job_offer_company_fk FOREIGN KEY ( company_id )
        REFERENCES company ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE job_offer_language
    ADD CONSTRAINT job_offer_fk FOREIGN KEY ( job_offer_id )
        REFERENCES job_offer ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE job_offer_language
    ADD CONSTRAINT job_offer_language_language_fk FOREIGN KEY ( language_id )
        REFERENCES language ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE job_offer_skill
    ADD CONSTRAINT job_offer_skill_job_offer_fk FOREIGN KEY ( job_offer_id1 )
        REFERENCES job_offer ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE job_offer_skill
    ADD CONSTRAINT job_offer_skill_skill_fk FOREIGN KEY ( skill_id )
        REFERENCES skill ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE language_lvl
    ADD CONSTRAINT language_lvl_language_fk FOREIGN KEY ( language_id )
        REFERENCES language ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE message
    ADD CONSTRAINT message_company_fk FOREIGN KEY ( company_id )
        REFERENCES company ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE message
    ADD CONSTRAINT message_student_fk FOREIGN KEY ( student_id )
        REFERENCES student ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE student_info
    ADD CONSTRAINT student_info_student_fk FOREIGN KEY ( student_id )
        REFERENCES student ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE student_language
    ADD CONSTRAINT student_language_language_fk FOREIGN KEY ( language_id )
        REFERENCES language ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE student_language
    ADD CONSTRAINT student_language_student_fk FOREIGN KEY ( student_id )
        REFERENCES student ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE student_skill
    ADD CONSTRAINT student_skill_skill_fk FOREIGN KEY ( skill_id )
        REFERENCES skill ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE student_skill
    ADD CONSTRAINT student_skill_student_fk FOREIGN KEY ( student_id )
        REFERENCES student ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go

ALTER TABLE student_info
    ADD CONSTRAINT type_info_fk FOREIGN KEY ( student_type_info_id )
        REFERENCES student_type_info ( id )
ON DELETE NO ACTION 
    ON UPDATE no action go



-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                            19
-- CREATE INDEX                             0
-- ALTER TABLE                             40
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- ALTER TRIGGER                            0
-- CREATE DATABASE                          0
-- CREATE DEFAULT                           0
-- CREATE INDEX ON VIEW                     0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE ROLE                              0
-- CREATE RULE                              0
-- CREATE SCHEMA                            0
-- CREATE PARTITION FUNCTION                0
-- CREATE PARTITION SCHEME                  0
-- 
-- DROP DATABASE                            0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0