bazi

БАЗИ – В
create table Korisnik(
    k_ime VARCHAR(255),
    ime VARCHAR(255),
    prezime VARCHAR(255),
    tip VARCHAR(255),
    pretplata INT,
    datum_reg DATE,
    tel_broj VARCHAR(12),
    email VARCHAR(255),
    PRIMARY KEY (k_ime),
    CONSTRAINT  ck_check_datumReg CHECK ( datum_reg BETWEEN '2023-01-01' AND '2024-12-31')
);

create table Premium_korisnik(
    k_ime VARCHAR(255),
    datum DATE,
    procent_popust INT DEFAULT '20',
    PRIMARY KEY (k_ime),
    FOREIGN KEY (k_ime) REFERENCES Korisnik(k_ime) ON DELETE CASCADE ON UPDATE  CASCADE
                             --ova si e spored pravilo
);

create table Profil(
    k_ime VARCHAR(255),
    datum DATE,
    ime VARCHAR(255),
    PRIMARY KEY (ime,k_ime),
    FOREIGN KEY (k_ime) REFERENCES Korisnik(k_ime) ON DELETE CASCADE ON UPDATE  CASCADE
);

create table Video_zapis(
    naslov VARCHAR(255),
    jazik VARCHAR(255) DEFAULT 'English',
    vremetraenje INT,
    datum_d DATE,
    datum_p DATE,
    PRIMARY KEY (naslov),
    CONSTRAINT ck_check_datum CHECK ( datum_d >= datum_p )

);

create table Video_zapis_zanr(
    naslov VARCHAR(255),
    zanr VARCHAR(255),
    PRIMARY KEY (naslov,zanr),
    FOREIGN KEY (naslov) REFERENCES Video_zapis(naslov) ON DELETE CASCADE ON UPDATE  CASCADE
);

create table Lista_zelbi(
    naslov VARCHAR(255),
    k_ime VARCHAR(255),
    ime VARCHAR(255),
    PRIMARY KEY (naslov,k_ime,ime),
    FOREIGN KEY (naslov) REFERENCES Video_zapis(naslov) ON DELETE CASCADE ON UPDATE  CASCADE,
    FOREIGN KEY (k_ime) REFERENCES  Korisnik(k_ime) ON DELETE CASCADE ON UPDATE  CASCADE,
    FOREIGN KEY (ime) REFERENCES  Profil(ime) ON DELETE CASCADE ON UPDATE  CASCADE
);

create table Preporaka(
    ID INT,
    k_ime_od VARCHAR(255),
    k_ime_na VARCHAR(255),
    naslov VARCHAR(255) DEFAULT 'Deleted',
    datum DATE,
    komentar VARCHAR(250),
    ocena INT,
    PRIMARY KEY (ID),
    FOREIGN KEY (k_ime_od) REFERENCES Korisnik(k_ime) ON DELETE CASCADE ON UPDATE  CASCADE,
    FOREIGN KEY (k_ime_na) REFERENCES Korisnik(k_ime) ON DELETE CASCADE ON UPDATE  CASCADE,
    FOREIGN KEY (naslov) REFERENCES Video_zapis(naslov) ON DELETE SET DEFAULT,
    CONSTRAINT ck_check_ocena CHECK ( ocena>=1 AND ocena<=5),
    CONSTRAINT ck_check_datum CHECK ( datum >='2022-12-07' )
);

-----------------------------------------------------------------------------------------------------------------------------------
dis

CREATE TABLE Korisnik(
    k_ime TEXT PRIMARY KEY,
    ime TEXT,
    prezime TEXT,
    tip TEXT,
    pretplata TEXT,
    datum_reg DATE,
    tel_broj VARCHAR(12),
    email TEXT,
    CONSTRAINT datum_reg_check CHECK (datum_reg>='2023-01-01' OR datum_reg<='2024-12-31'),
    --CONSTRAINT tel_broj_max CHECK (tel_broj != '____________%')
);
CREATE TABLE Premium_korisnik(
    k_ime TEXT PRIMARY KEY,
    datum DATE,
    procent_popust INT DEFAULT 20,
    FOREIGN KEY (k_ime) REFERENCES Korisnik (k_ime) ON DELETE SET NULL ON UPDATE SET NULL
);
CREATE TABLE Profil(
    k_ime TEXT,
    ime TEXT,
    datum DATE,
    PRIMARY KEY (k_ime,ime),
    FOREIGN KEY (k_ime) REFERENCES Korisnik (k_ime) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE Video_zapis(
    naslov TEXT PRIMARY KEY DEFAULT 'Deleted',
    jazik TEXT DEFAULT 'English',
    vremetraenje INT,
    datum_d DATE,
    datum_p DATE,
    CONSTRAINT datum_check CHECK (datum_d<datum_p)
);
CREATE TABLE Video_zapis_zanr(
    naslov TEXT,
    zanr TEXT,
    PRIMARY KEY (naslov,zanr),
    FOREIGN KEY (naslov) REFERENCES Video_zapis (naslov) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE Lista_zelbi(
    --ID TEXT PRIMARY KEY,
    naslov TEXT,
    k_ime TEXT,
    ime TEXT,
    PRIMARY KEY(naslov,k_ime,ime),
    FOREIGN KEY (k_ime,ime) REFERENCES Profil (k_ime,ime) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (naslov) REFERENCES Video_zapis (naslov) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE Preporaka(
    ID TEXT PRIMARY KEY,
    k_ime_od TEXT,
    k_ime_na TEXT,
    naslov TEXT,
    datum DATE,
    komentar VARCHAR(250),
    ocena INT,
    FOREIGN KEY (k_ime_od) REFERENCES Korisnik (k_ime) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (k_ime_na) REFERENCES Korisnik (k_ime) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (naslov) REFERENCES Video_zapis (naslov) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    CONSTRAINT ocena_values (ocena IN (1,2,3,4,5)),
    CONSTRAINT datum_preporaka_check CHECK (datum>'2022-12-07'),
    CONSTRAINT komentar_vnes CHECK (komentar!='')
);
------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Korisnik(
  k_ime VARCHAR(20) PRIMARY KEY,
  ime VARCHAR(20),
  prezime VARCHAR(20),
  tip VARCHAR(20), ---?
  pretplata INT,
  datum_reg DATE,
  tel_broj VARCHAR(12),
  email VARCHAR(30),
  CONSTRAINT CH_datum_reg CHECK ( datum_reg BETWEEN '2023-01-01' AND '2024-12-31')
);

CREATE TABLE Premium_korisnik(
  k_ime VARCHAR(20) PRIMARY KEY REFERENCES Korisnik(k_ime) ON DELETE CASCADE ON UPDATE CASCADE,
  datum DATE,
  procent_popust FLOAT DEFAULT 20.0
);

CREATE TABLE Profil(
  k_ime VARCHAR(20) REFERENCES Korisnik(k_ime) ON DELETE CASCADE ON UPDATE CASCADE,
  ime VARCHAR(20),
  datum DATE,
  CONSTRAINT PK PRIMARY KEY (k_ime,ime)
);

CREATE TABLE Video_zapis(
  naslov VARCHAR(50) PRIMARY KEY,
  jazik VARCHAR(20) DEFAULT 'English',
  vremetraenje INT,
  datum_d DATE,
  datum_p DATE,
  CONSTRAINT CH_datum CHECK (datum_d > datum_p)
);

CREATE TABLE Video_zapis_zanr(
  naslov VARCHAR(50) REFERENCES Video_zapis(naslov) ON DELETE CASCADE ON UPDATE CASCADE,
  zanr VARCHAR(20),
  CONSTRAINT PK PRIMARY KEY (naslov, zanr)
);

CREATE TABLE Lista_zelbi(
  naslov VARCHAR(50) REFERENCES Video_zapis_zanr(naslov) ON DELETE CASCADE ON UPDATE CASCADE,
  k_ime VARCHAR(20),
  ime VARCHAR(20),
  CONSTRAINT PK PRIMARY KEY (naslov, k_ime, ime),
  CONSTRAINT FK_lz_k FOREIGN KEY (k_ime,ime) REFERENCES Profil(k_ime,ime) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE Preporaka(
  ID INT PRIMARY KEY,
  k_ime_od VARCHAR(20) REFERENCES Korisnik(k_ime) ON DELETE CASCADE ON UPDATE CASCADE,
  k_ime_na VARCHAR(20) REFERENCES Korisnik(k_ime) ON DELETE CASCADE ON UPDATE CASCADE,
  naslov VARCHAR(50) DEFAULT 'Deleted' REFERENCES Video_zapis(naslov) ON DELETE SET DEFAULT ON UPDATE CASCADE,
  datum DATE,
  komentar VARCHAR(250) NOT NULL,
  ocena INT,
  CONSTRAINT CH_ocena CHECK (ocena BETWEEN 1 AND 5),
  CONSTRAINT CH_datum CHECK (datum > '2022-12-7')
);