MarketAssessmentRelationalSchema: market_assesment_DDL.sql

CREATE TABLE market_assesment.individuals (
        individual_id int4 NOT NULL DEFAULT nextval('market_assesment.individuals_individual_id_seq'::regclass),
        name_surname varchar(255) NOT NULL,
        experience_level varchar(255) NULL,
        contact varchar(255) NOT NULL,
        profession varchar(255) NULL,
        education varchar(255) NULL,
        company_training varchar(255) NULL,
        training_interest varchar(255) NULL,
        registration_id int4 NULL,
        CONSTRAINT individuals_experience_level_check CHECK((experience_level)::text = ANY ((ARRAY['Студент'::character varying, 'Професионалец'::character varying])::text[])),
        CONSTRAINT individuals_pkey PRIMARY KEY (individual_id),
        CONSTRAINT individuals_registration_id_fk FOREIGN KEY (registration_id) REFERENCES market_assesment.registrations(registration_id)
)
WITH (
        OIDS=FALSE
);

CREATE TABLE market_assesment.organizations (
        organization_id int4 NOT NULL DEFAULT nextval('market_assesment.organizations_organization_id_seq'::regclass),
        org_name varchar(255) NOT NULL,
        org_type varchar(255) NOT NULL,
        register_number varchar(255) NOT NULL,
        employee_size int4 NULL,
        contact varchar(255) NOT NULL,
        database_in_use varchar(255) NULL,
        registration_id int4 NULL,
        CONSTRAINT organizations_pkey PRIMARY KEY (organization_id),
        CONSTRAINT organizations_registration_id_fk FOREIGN KEY (registration_id) REFERENCES market_assesment.registrations(registration_id)
)
WITH (
        OIDS=FALSE
);

CREATE TABLE market_assesment.registrations (
        registration_id int4 NOT NULL DEFAULT nextval('market_assesment.registrations_registration_id_seq'::regclass),
        username varchar(80) NOT NULL,
        password varchar(255) NOT NULL,
        register_date timestamp NULL DEFAULT now(),
        user_type varchar(13) NULL,
        CONSTRAINT registrations_pkey PRIMARY KEY (registration_id),
        CONSTRAINT registrations_user_type_check CHECK((user_type)::text = ANY ((ARRAY['Организација'::character varying, 'Поединец'::character varying])::text[])),
        CONSTRAINT registrations_username_key UNIQUE (username)
)
WITH (
        OIDS=FALSE
);

CREATE TABLE market_assesment.services (
        service_id int4 NOT NULL DEFAULT nextval('market_assesment.services_service_id_seq'::regclass),
        service_direction varchar(4) NOT NULL,
        service_name varchar(255) NULL,
        service_type varchar(255) NOT NULL,
        database_name varchar(255) NULL,
        database_type varchar(255) NULL,
        dbms_version varchar(255) NULL,
        service_description text NULL,
        salary varchar(255) NULL,
        status varchar(8) NOT NULL,
        start_date date NOT NULL,
        end_date date NULL,
        price varchar(255) NULL,
        organization_id int4 NULL,
        individual_id int4 NULL,
        CONSTRAINT services_pkey PRIMARY KEY (service_id),
        CONSTRAINT services_status_check CHECK((status)::text = ANY ((ARRAY['Активна'::character varying, 'Неактивна'::character varying])::text[])),
        CONSTRAINT services_individual_fkey FOREIGN KEY (individual_id) REFERENCES market_assesment.individuals(individual_id),
        CONSTRAINT services_organization_fkey FOREIGN KEY (organization_id) REFERENCES market_assesment.organizations(organization_id)
)
WITH (
        OIDS=FALSE
);

CREATE TABLE market_assesment.users (
        user_id int4 NOT NULL DEFAULT nextval('market_assesment.users_user_id_seq'::regclass),
        last_login timestamp NULL DEFAULT now(),
        CONSTRAINT users_pkey PRIMARY KEY (user_id)
)
WITH (
        OIDS=FALSE
);