DDL

1. Вовед

DDL-от беше генериран од страна на Visual Paradigm, испробан локално и дополнет со constraints. Потоа, направивме pg_dump и го импортиравме на нашата remote база за предметот.

2. Дел од кодот

CREATE TABLE user_app (

user_id bigint NOT NULL, username character varying(20) NOT NULL, email character varying(50) NOT NULL, contact_phone character varying(50), CONSTRAINT chk_email_format CHECK (((email)::text '%@%.%'::text))

);

CREATE TABLE order_cart (

user_id bigint NOT NULL, discount_id integer, status_id integer NOT NULL, order_id bigint NOT NULL, created_at timestamp without time zone NOT NULL, total_price numeric(12,2) NOT NULL, CONSTRAINT chk_order_total CHECK ((total_price >= (0)::numeric))

);

CREATE TABLE attribute (

name text NOT NULL, attribute_id integer NOT NULL, data_type character varying(20) NOT NULL, description text, is_required boolean NOT NULL

);

CREATE TABLE payment (

order_id bigint NOT NULL, method_id integer NOT NULL, payment_id integer NOT NULL, amount_paid numeric(12,2), processed_at timestamp without time zone NOT NULL, transaction_id character varying(255) NOT NULL, is_refunded boolean, CONSTRAINT chk_payment_amount CHECK ((amount_paid >= (0)::numeric))

);

ALTER TABLE ONLY waitlist_entry

ADD CONSTRAINT fk_waitlist_user FOREIGN KEY (user_id) REFERENCES public.user_app(user_id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY ticket

ADD CONSTRAINT fk_ticket_seat FOREIGN KEY (seat_id) REFERENCES public.seat(seat_id) ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE ONLY payment_method

ADD CONSTRAINT payment_method_method_name_key UNIQUE (method_name);

3. Резултат

Моментално имаме табели со точни типови на атрибути и со сите потребни (за сега) ограничувања.