dopolnitelno_bazi

MUZICAR (osnoven kod)
create table Muzicar(
    id INT,
    ime VARCHAR(255),
    prezime VARCHAR(255),
    datum_raganje DATE,
    PRIMARY KEY (id)
);

create table Muzicar_instrument(
    id_muzicar INT,
    instrument VARCHAR(255),
    PRIMARY KEY (id_muzicar, instrument),
    FOREIGN KEY (id_muzicar) references Muzicar(id)
);

create table Bend(
    id INT,
    ime VARCHAR(255),
    godina_osnovanje DATE,
    PRIMARY KEY (id)
);

create table Bend_znar(
    id_bend INT,
    zanr VARCHAR(255),
    PRIMARY KEY (id_bend,zanr),
    FOREIGN KEY (id_bend) references Bend(id)
);

create table Nastan(
    id INT,
    cena INT,
    kapacitet INT,
    PRIMARY KEY (id)
);

create table Koncert(
    id INT,
    datum DATE,
    vreme TIME,
    --valjda mozhe i int
    PRIMARY KEY (id),
    FOREIGN KEY (id) references Nastan(id)
);

create table Festival(
    id INT,
    ime VARCHAR(255),
    PRIMARY KEY (id),
    FOREIGN KEY (id) references Nastan(id)
);

create table Festival_odrzuvanje(
    id INT,
    datum_od DATE,
    datum_do DATE,
    PRIMARY KEY (id,datum_od),
    FOREIGN KEY (id) references Festival(id)
);

create table Muzicar_bend(
    id_muzicar INT,
    id_bend INT,
    datum_napustanje DATE,
    PRIMARY KEY (id_muzicar,id_bend),
    FOREIGN KEY (id_muzicar) references Muzicar(id),
    FOREIGN KEY (id_bend) REFERENCES Bend(id)
);

create table Ucestvo_festival(
    id_festival INT,
    datum_od DATE,
    id_bend INT,
    den VARCHAR(255),
    vremetraenje_nastap INT,
    plata_nastap INT,
    PRIMARY KEY (id_festival,datum_od,id_bend),
    FOREIGN KEY (id_festival) REFERENCES Festival_odrzuvanje(id),
    FOREIGN KEY (datum_od) REFERENCES Festival_odrzuvanje(datum_od),
    FOREIGN KEY (id_bend) REFERENCES Bend(id)
);

create table Ucestvo_koncert(
    id_koncert INT,
    id_muzicar INT,
    id_bend INT,
    PRIMARY KEY (id_koncert,id_muzicar,id_bend),
    FOREIGN KEY (id_koncert) references Koncert(id),
    FOREIGN KEY (id_muzicar) references Muzicar(id),
    FOREIGN KEY (id_bend) references Bend(id)
);
---------------------------------------------------------------------------------------------------------------------------------
GPT
create table Muzicar(
    id INT,
    ime VARCHAR(255),
    prezime VARCHAR(255),
    datum_raganje DATE,
    PRIMARY KEY (id)
);

create table Muzicar_instrument(
    id_muzicar INT,
    instrument VARCHAR(255),
    PRIMARY KEY (id_muzicar, instrument),
    FOREIGN KEY (id_muzicar) references Muzicar(id)
);

create table Bend(
    id INT,
    ime VARCHAR(255),
    godina_osnovanje DATE,
    PRIMARY KEY (id)
);

create table Bend_znar(
    id_bend INT,
    zanr VARCHAR(255),
    PRIMARY KEY (id_bend, zanr),
    FOREIGN KEY (id_bend) references Bend(id)
);

create table Nastan(
    id INT,
    cena INT,
    kapacitet INT,
    PRIMARY KEY (id)
);

create table Koncert(
    id INT,
    datum DATE,
    vreme TIME,
    PRIMARY KEY (id),
    FOREIGN KEY (id) references Nastan(id)
);

create table Festival(
    id INT,
    ime VARCHAR(255),
    PRIMARY KEY (id),
    FOREIGN KEY (id) references Nastan(id)
);

create table Festival_odrzuvanje(
    id INT,
    datum_od DATE,
    datum_do DATE,
    PRIMARY KEY (id, datum_od),
    FOREIGN KEY (id) references Festival(id)
);

create table Muzicar_bend(
    id_muzicar INT,
    id_bend INT,
    datum_napustanje DATE,
    PRIMARY KEY (id_muzicar, id_bend),
    FOREIGN KEY (id_muzicar) references Muzicar(id),
    FOREIGN KEY (id_bend) REFERENCES Bend(id)
);

create table Ucestvo_festival(
    id_festival INT,
    datum_od DATE,
    id_bend INT,
    den VARCHAR(255),
    vremetraenje_nastap INT,
    plata_nastap INT,
    PRIMARY KEY (id_festival, datum_od, id_bend),
    FOREIGN KEY (id_festival) REFERENCES Festival_odrzuvanje(id),
    FOREIGN KEY (id_bend) REFERENCES Bend(id)
);

create table Ucestvo_koncert(
    id_koncert INT,
    id_muzicar INT,
    id_bend INT,
    PRIMARY KEY (id_koncert, id_muzicar, id_bend),
    FOREIGN KEY (id_koncert) references Koncert(id),
    FOREIGN KEY (id_muzicar) references Muzicar(id),
    FOREIGN KEY (id_bend) references Bend(id)
);
----------------------------------------------------------------------------------------------------------------------------
DOPLINTELNI BARANJA:
• Инструментите што ги свират музичарите мора да бидат еден од 'guitar', 'bass', 'drums', 'keyboards' или 'vocals'
---------------------------
create table Muzicar_instrument(
    id_muzicar INT,
    instrument VARCHAR(255),
    PRIMARY KEY (id_muzicar,instrument),
    FOREIGN KEY (id_muzicar) references Muzicar(id),
    CONSTRAINT ck_check_instrument CHECK ( instrument IN ('guitar','bass','drums', 'keyboards','vocals'))
);
---------------------------
· Доколку не е внесен денот на настап на бенд на некој фестивал, треба да се пополни предефинирана вредност 1
---------------------------
create table Ucestvo_festival(
    id_festival INT,
    datum_od DATE,
    id_bend INT,
    den VARCHAR(255) DEFAULT '1',
    vremetraenje_nastap INT,
    plata_nastap INT,
    PRIMARY KEY (id_festival,datum_od,id_bend),
    FOREIGN KEY (id_festival) REFERENCES Festival_odrzuvanje(id),
    FOREIGN KEY (datum_od) REFERENCES Festival_odrzuvanje(datum_od),
    FOREIGN KEY (id_bend) REFERENCES Bend(id)
);
---------------------------
· При внесување на концерт во базата на податоци, задолжително е датумот а биде внесен.
---------------------------
create table Koncert(
    id INT  ,
    datum DATE NOT NULL,
    vreme INT,
    --тука не мора not null, дека битно е да земее кога е закажан концертот
    --може да е и TIME
    PRIMARY KEY (id),
    FOREIGN KEY (id) references Nastan(id)
);
---------------------------
· Промените и бришењето на музичари треба да бидат проследени и во табелата за чување на инструменти на музичари.
---------------------------
create table Muzicar_instrument(
    id_muzicar INT,
    instrument VARCHAR(255),
    PRIMARY KEY (id_muzicar,instrument),
    FOREIGN KEY (id_muzicar) references Muzicar(id) ON DELETE CASCADE ON UPDATE CASCADE ,
    CONSTRAINT ck_check_instrument CHECK ( instrument IN ('guitar','bass','drums', 'keyboards','vocals'))
);
---------------------------
· Во базата се чуваат информации само за музичари родени пред 2005 година
--------------------------
create table Muzicar(
    id INT,
    ime VARCHAR(255),
    prezime VARCHAR(255),
    datum_raganje DATE,
    PRIMARY KEY (id),
    CONSTRAINT ck_check_datum CHECK ( datum_raganje < '2005-01-01' )
);
--------------------------
FINALNA

create table Muzicar(
    id INT,
    ime VARCHAR(255),
    prezime VARCHAR(255),
    datum_raganje DATE,
    PRIMARY KEY (id),
    CONSTRAINT ck_check_datum CHECK ( datum_raganje < '2005-01-01' )
);

create table Muzicar_instrument(
    id_muzicar INT,
    instrument VARCHAR(255),
    PRIMARY KEY (id_muzicar,instrument),
    FOREIGN KEY (id_muzicar) references Muzicar(id) ON DELETE CASCADE ON UPDATE CASCADE ,
                                                        --spored pravilo
    CONSTRAINT ck_check_instrument CHECK ( instrument IN ('guitar','bass','drums', 'keyboards','vocals'))
);

create table Bend(
    id INT,
    ime VARCHAR(255),
    godina_osnovanje DATE,
    PRIMARY KEY (id)
);

create table Bend_znar(
    id_bend INT,
    zanr VARCHAR(255),
    PRIMARY KEY (id_bend,zanr),
    FOREIGN KEY (id_bend) references Bend(id) ON DELETE SET NULL ON UPDATE CASCADE
);

create table Nastan(
    id INT,
    cena INT,
    kapacitet INT,
    PRIMARY KEY (id)
);

create table Koncert(
    id INT  ,
    datum DATE NOT NULL,
    vreme INT,
    --тука не мора not null, дека битно е да земее кога е закажан концертот
    --може да е и TIME
    PRIMARY KEY (id),
    FOREIGN KEY (id) references Nastan(id) ON DELETE SET NULL ON UPDATE CASCADE
);

create table Festival(
    id INT,
    ime VARCHAR(255),
    PRIMARY KEY (id),
    FOREIGN KEY (id) references Nastan(id) ON DELETE SET NULL ON UPDATE CASCADE
);

create table Festival_odrzuvanje(
    id INT,
    datum_od DATE,
    datum_do DATE,
    PRIMARY KEY (id,datum_od),
    FOREIGN KEY (id) references Festival(id) ON DELETE SET NULL ON UPDATE CASCADE
);

create table Muzicar_bend(
    id_muzicar INT,
    id_bend INT,
    datum_napustanje DATE,
    PRIMARY KEY (id_muzicar,id_bend),
    FOREIGN KEY (id_muzicar) references Muzicar(id) ON DELETE SET NULL ON UPDATE CASCADE ,
    FOREIGN KEY (id_bend) REFERENCES Bend(id) ON DELETE SET NULL ON UPDATE CASCADE
);

create table Ucestvo_festival(
    id_festival INT,
    datum_od DATE,
    id_bend INT,
    den VARCHAR(255) DEFAULT '1',
    vremetraenje_nastap INT,
    plata_nastap INT,
    PRIMARY KEY (id_festival,datum_od,id_bend),
    FOREIGN KEY (id_festival) REFERENCES Festival_odrzuvanje(id) ON DELETE SET NULL ON UPDATE CASCADE ,
    FOREIGN KEY (datum_od) REFERENCES Festival_odrzuvanje(datum_od) ON DELETE SET NULL ON UPDATE CASCADE ,
    FOREIGN KEY (id_bend) REFERENCES Bend(id)ON DELETE SET NULL ON UPDATE CASCADE
);

create table Ucestvo_koncert(
    id_koncert INT,
    id_muzicar INT,
    id_bend INT,
    PRIMARY KEY (id_koncert,id_muzicar,id_bend),
    FOREIGN KEY (id_koncert) references Koncert(id)ON DELETE SET NULL ON UPDATE CASCADE ,
    FOREIGN KEY (id_muzicar) references Muzicar(id)ON DELETE SET NULL ON UPDATE CASCADE ,
    FOREIGN KEY (id_bend) references Bend(id)ON DELETE SET NULL ON UPDATE CASCADE
);