/* ---------------------------------------------------------------------- */
/* Script generated with: DeZign for Databases v4.1.3                     */
/* Target DBMS:           Oracle 9i                                       */
/* Project file:          projekt2.dez                                    */
/* Project name:                                                          */
/* Author:                                                                */
/* Script type:           Database creation script                        */
/* Created on:            2006-12-05 03:34                                */
/* ---------------------------------------------------------------------- */


/* ---------------------------------------------------------------------- */
/* Tables                                                                 */
/* ---------------------------------------------------------------------- */

/* ---------------------------------------------------------------------- */
/* Add table "profile"                                                    */
/* ---------------------------------------------------------------------- */

CREATE TABLE profile (
    firma VARCHAR2(100) CONSTRAINT NN_profile_firma NOT NULL,
    kraj VARCHAR2(50) CONSTRAINT NN_profile_kraj NOT NULL,
    adres_siedziby VARCHAR2(100) CONSTRAINT NN_profile_adres_siedziby NOT NULL,
    mail VARCHAR2(50) CONSTRAINT NN_profile_mail NOT NULL,
    branza VARCHAR2(50) CONSTRAINT NN_profile_branza NOT NULL,
    opis_dzialalnosci VARCHAR2(50) CONSTRAINT NN_profile_opis_dzialalnosci NOT NULL,
    odnosnik_do_strony_firmy VARCHAR2(50),
    roczny_obrot INTEGER CONSTRAINT NN_profile_roczny_obrot NOT NULL,
    liczba_pracownikow INTEGER CONSTRAINT NN_profile_liczba_pracownikow NOT NULL,
    referencje VARCHAR2(50),
    CONSTRAINT PK_profile PRIMARY KEY (firma)
);

/* ---------------------------------------------------------------------- */
/* Add table "filie"                                                      */
/* ---------------------------------------------------------------------- */

CREATE TABLE filie (
    firma VARCHAR2(100) CONSTRAINT NN_filie_firma NOT NULL,
    adres_filii VARCHAR2(100) CONSTRAINT NN_filie_adres_filii NOT NULL,
    CONSTRAINT PK_filie PRIMARY KEY (firma, adres_filii)
);

/* ---------------------------------------------------------------------- */
/* Add table "poczatek_wsp"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE poczatek_wsp (
    firma1 VARCHAR2(100) CONSTRAINT NN_poczatek_wsp_firma1 NOT NULL,
    firma2 VARCHAR2(100) CONSTRAINT NN_poczatek_wsp_firma2 NOT NULL,
    data_rozp DATE CONSTRAINT NN_poczatek_wsp_data_rozp NOT NULL,
    plan_wspolpracy VARCHAR2(500),
    CONSTRAINT PK_poczatek_wsp PRIMARY KEY (firma1, firma2, data_rozp)
);

/* ---------------------------------------------------------------------- */
/* Add table "koniec_wsp"                                                 */
/* ---------------------------------------------------------------------- */

CREATE TABLE koniec_wsp (
    firma1 VARCHAR2(100) CONSTRAINT NN_koniec_wsp_firma1 NOT NULL,
    firma2 VARCHAR2(100) CONSTRAINT NN_koniec_wsp_firma2 NOT NULL,
    data_rozp DATE CONSTRAINT NN_koniec_wsp_data_rozp NOT NULL,
    data_zak DATE CONSTRAINT NN_koniec_wsp_data_zak NOT NULL,
    komentarz_firma1 VARCHAR2(500),
    komentarz_firma2 VARCHAR2(500),
    CONSTRAINT PK_koniec_wsp PRIMARY KEY (firma1, firma2, data_rozp)
);

/* ---------------------------------------------------------------------- */
/* Add table "logowanie"                                                  */
/* ---------------------------------------------------------------------- */

CREATE TABLE logowanie (
    firma VARCHAR2(100) CONSTRAINT NN_logowanie_firma NOT NULL,
    login VARCHAR2(50) CONSTRAINT NN_logowanie_login NOT NULL,
    haslo VARCHAR2(50) CONSTRAINT NN_logowanie_haslo NOT NULL,
    status VARCHAR2(50) CONSTRAINT NN_logowanie_status NOT NULL,
    CONSTRAINT PK_logowanie PRIMARY KEY (firma, login)
);

/* ---------------------------------------------------------------------- */
/* Add table "bezp_znajomi"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE bezp_znajomi (
    firma1 VARCHAR2(100) CONSTRAINT NN_bezp_znajomi_firma1 NOT NULL,
    firma2 VARCHAR2(100) CONSTRAINT NN_bezp_znajomi_firma2 NOT NULL,
    d_zapoznania DATE CONSTRAINT NN_bezp_znajomi_d_zapoznania NOT NULL,
    CONSTRAINT PK_bezp_znajomi PRIMARY KEY (firma1, firma2)
);

/* ---------------------------------------------------------------------- */
/* Add table "posr_znajomi"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE posr_znajomi (
    firma1 VARCHAR2(100) CONSTRAINT NN_posr_znajomi_firma1 NOT NULL,
    firma2 VARCHAR2(100) CONSTRAINT NN_posr_znajomi_firma2 NOT NULL,
    d_zapoznania DATE CONSTRAINT NN_posr_znajomi_d_zapoznania NOT NULL,
    CONSTRAINT PK_posr_znajomi PRIMARY KEY (firma1, firma2)
);

/* ---------------------------------------------------------------------- */
/* Add table "certyfikaty"                                                */
/* ---------------------------------------------------------------------- */

CREATE TABLE certyfikaty (
    firma VARCHAR2(100) CONSTRAINT NN_certyfikaty_firma NOT NULL,
    nazwa VARCHAR2(50) CONSTRAINT NN_certyfikaty_nazwa NOT NULL,
    opis VARCHAR2(500),
    CONSTRAINT PK_certyfikaty PRIMARY KEY (firma, nazwa)
);

/* ---------------------------------------------------------------------- */
/* Add table "kraje"                                                      */
/* ---------------------------------------------------------------------- */

CREATE TABLE kraje (
    firma VARCHAR2(100) CONSTRAINT NN_kraje_firma NOT NULL,
    nazwa VARCHAR2(50) CONSTRAINT NN_kraje_nazwa NOT NULL,
    CONSTRAINT PK_kraje PRIMARY KEY (firma, nazwa)
);

/* ---------------------------------------------------------------------- */
/* Foreign key constraints                                                */
/* ---------------------------------------------------------------------- */

ALTER TABLE filie ADD CONSTRAINT profile_filie 
    FOREIGN KEY (firma) REFERENCES profile (firma);

ALTER TABLE poczatek_wsp ADD CONSTRAINT poczatek_profil 
    FOREIGN KEY (firma1) REFERENCES profile (firma);

ALTER TABLE poczatek_wsp ADD CONSTRAINT profil_poczatek 
    FOREIGN KEY (firma2) REFERENCES profile (firma);

ALTER TABLE koniec_wsp ADD CONSTRAINT poczatek_wsp_koniec_wsp 
    FOREIGN KEY (firma1, firma2, data_rozp) REFERENCES poczatek_wsp (firma1,firma2,data_rozp);

ALTER TABLE logowanie ADD CONSTRAINT profile_logowanie 
    FOREIGN KEY (firma) REFERENCES profile (firma);

ALTER TABLE bezp_znajomi ADD CONSTRAINT profile_bezp_znajomi 
    FOREIGN KEY (firma1) REFERENCES profile (firma);

ALTER TABLE bezp_znajomi ADD CONSTRAINT profil_znajomi_bezp 
    FOREIGN KEY (firma2) REFERENCES profile (firma);

ALTER TABLE posr_znajomi ADD CONSTRAINT profile_posr_znajomi 
    FOREIGN KEY (firma1) REFERENCES profile (firma);

ALTER TABLE posr_znajomi ADD CONSTRAINT profil_znajomi_posr 
    FOREIGN KEY (firma2) REFERENCES profile (firma);

ALTER TABLE certyfikaty ADD CONSTRAINT profile_certyfikaty 
    FOREIGN KEY (firma) REFERENCES profile (firma);

ALTER TABLE kraje ADD CONSTRAINT profile_kraje 
    FOREIGN KEY (firma) REFERENCES profile (firma);