Changes between Version 2 and Version 3 of MarketAssessmentRelationalSchema

Timestamp:

11/14/18 17:59:24 (6 years ago)

Author:

179007

Comment:

—

MarketAssessmentRelationalSchema

@@ -1 @@
-= Market Assessment Database Relational Schema =
 
-{{{#!rbox
-Back to MarketAssessment
-}}}
 
-{{{#!box type="info"
-Тука треба да се стават SQL DDL изрази со кои се креира структурата на релациската шема во PostgreSQL. Може да се извадат од последната структура на базата со DBeaver или со друга алатка.
-}}}
+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 schema ...
+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 ...
-}}}
+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
+);