zestaw8(Tworzenie_tabel).sql

--Utworzyć strukturę bazy danych
CREATE TABLE klient(
    customerID INTEGER not null 
        constraint customerID_pk primary key,
    name varchar(25) null,
    surname varchar(25) null,
    addr_street varchar(45) null,
    addr_zip char(5) null,
    addr_city varchar(45) null,
    login varchar(14) not null,
    passwd varchar(12) not null
);
CREATE TABLE zamowienia(
    orderID INTEGER not null 
        constraint orderID_pk primary key,
    IDcustomer INTEGER NULL
        constraint rel_klient2zamowienia
        REFERENCES klient(customerID),
    crDATE DATE
);
CREATE TABLE produkt(
    productID INTEGER not null
        constraint productID_pk primary key,
    name VARCHAR(35) not null,
    price_net FLOAT null,
    price_gross FLOAT null,
    description CLOB null
);
CREATE TABLE pozycja(
    IDproduct INTEGER not null
        constraint rel_produkt2pozycja
        REFERENCES produkt(productID),
    IDorder INTEGER not null
        constraint rel_zamowienia2pozycja
        REFERENCES zamowienia(orderID),
    quantity INTEGER not null
);
--2.1 Dodać do tabeli klient kolumnę umożliwiającą przechowywanie adresu email. 
ALTER TABLE klient ADD (EMAIL VARCHAR(35) NULL);

--2.2 Zmodyfikować kolumnę addr_zip w ten sposób, aby miała nazwę addr_postalcode i umożliwiała przechowywanie 7 znaków 
ALTER TABLE klient RENAME COLUMN addr_zip TO addr_postalcode;
ALTER TABLE klient MODIFY(addr_postalcode CHAR(7));

--2.3 Zmodyfikować tabelę zamowienia w taki sposób, aby można był przechowywać informację o tym, czy dane zgłoszenie jest zrealizowane. 
ALTER TABLE zamowienia ADD(realized CHAR(1) DEFAULT 'N' NOT NULL CHECK(realized IN ('N','Y')));

--2.4 Dodać kolumnę umożliwiającą przechowywanie informacji o dacie (i godzinie) zrealizowania zamówienia. 
ALTER TABLE zamowienia ADD(realizationTime TIMESTAMP NULL);

--2.5 Jakie zmiany należy wprowadzić do tabeli zamowienia, żeby można było przechowywać informacje ostatusie realizacji zamówienia 
--Należy zmienić typ kolumny "realized" na VARCHAR(35),DEFAULT na "Nowe zamówienie" oraz CHECK na nastepujace statusy: "Nowe zamówienie","Realizowane","Przesyka wyslana","Realizacja zakonczona";

--2.6 Zmodyfikować tabelę produktów w celu przechowywania informacji o cenie brutto. 
--Cena brutto powinna być wyliczana na podstawie stawki podatku VAT przypisanego towarowi.
ALTER TABLE produkt ADD(VAT FLOAT DEFAULT 0.23);
ALTER TABLE produkt drop column price_gross;
ALTER TABLE produkt ADD(price_gross  AS VAT*price_net+price_net));

--2.7 Dodać indeks do odpowiednich tabel,pozwalający na szybsze wyszukiwanie klientów użytkowników po nazwisku, loginie i adresie poczty elektronicznej. 
CREATE INDEX klient_index ON klient(surname,login,EMAIL);

--2.8 Jaki rodzaj indeksu (jakie ograniczenie) należy zastosować, aby unikalność loginu była gwarantowana na poziomie bazy danych. Napisać zapytanie realizujące taką funkcjonalność.
--Nalezy dodac ograniczenie unique do kolumny EMAIL;
ALTER TABLE klient
ADD CONSTRAINT EMAIL_UNIQUE
UNIQUE (EMAIL);