CREATE TABLE korisnik(
MBR CHAR(13) PRIMARY KEY,
tip VARCHAR(15),
grad VARCHAR(20),
ulica VARCHAR(20),
broj INTEGER
);
INSERT INTO korisnik
VALUES ('2411993424002','Pre-paid','Skopje','Naroden Front',5)
INSERT INTO korisnik
VALUES ('2002966424036','Post-paid','Kriva Palanka','Sv.Joakim Osogovski',138)
INSERT INTO korisnik
VALUES ('0806965429012','Pre-paid','Kriva Palanka','Heroj Karposh',196)
SELECT * FROM korisnik
CREATE TABLE mobilen_operator(
naziv VARCHAR(10) CHECK (naziv IN ('T-Mobile','VIP','One')) PRIMARY KEY,
opis VARCHAR(50)
);
INSERT INTO mobilen_operator
VALUES('T-Mobile','Makedonski Telekom')
INSERT INTO mobilen_operator
VALUES('One','Telekom Slovenia')
INSERT INTO mobilen_operator
VALUES('VIP','Telekom Austria')
SELECT * FROM mobilen_operator
CREATE TABLE tel_broj(
tel_broj CHAR(9) PRIMARY KEY CHECK (tel_broj LIKE '07%'),
MBR CHAR(13)
);
ALTER TABLE tel_broj ADD CONSTRAINT broj UNIQUE(tel_broj)
ALTER TABLE tel_broj ADD CONSTRAINT tel_br_FK FOREIGN KEY (MBR) REFERENCES korisnik(MBR)
INSERT INTO tel_broj
VALUES('078566409','2411993424002')
INSERT INTO tel_broj
VALUES('072254690','2002966424036')
INSERT INTO tel_broj
VALUES('075999709','0806965429012')
SELECT * FROM tel_broj
CREATE TABLE paket(
opis VARCHAR(50),
naziv VARCHAR(30),
dop_uslugi VARCHAR(40),
naziv_oper VARCHAR(10),
broj_korisnik CHAR(9),
CONSTRAINT paket_oper_FK FOREIGN KEY(naziv_oper) REFERENCES mobilen_operator(naziv)
ON DELETE CASCADE,
CONSTRAINT paket_broj_FK FOREIGN KEY(broj_korisnik) REFERENCES tel_broj(tel_broj)
ON DELETE CASCADE
);
INSERT INTO paket
VALUES ('post-paid, XL package','FLEX XL',
        'unlimited via Telekom',
        'T-Mobile',
        '072254690')
INSERT INTO paket
VALUES ('pre-paid, VIP SMART 100','VIP SMART 100',
        'unlimited via VIP',
        'VIP',
        '078566409')
INSERT INTO paket
VALUES ('pre-paid, DzaBEST','DzaBEST',
        'unlimited via ONE',
        'One',
        '075999709')
SELECT * FROM paket
CREATE TABLE izbor(
datum DATE,
tel_broj CHAR(9),
CONSTRAINT tel_br_izbor_FK FOREIGN KEY (tel_broj) REFERENCES tel_broj(tel_broj)
);
ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-YYYY';
INSERT INTO izbor
VALUES('07-15-2014',
        '072254690')
INSERT INTO izbor
VALUES('05-05-2013',
        '075999709')
INSERT INTO izbor
VALUES('05-11-2013',
        '078566409')
SELECT * FROM izbor
CREATE TABLE dogovor(
datum DATE,
oper_naziv VARCHAR(10)
);
ALTER TABLE dogovor ADD CONSTRAINT dogovor_oper_FK FOREIGN KEY(oper_naziv) REFERENCES mobilen_operator(naziv)
ON DELETE CASCADE
ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-YYYY';
INSERT INTO dogovor
VALUES ('07-15-2014',
        'T-Mobile')
INSERT INTO dogovor
VALUES ('05-04-2013',
        'One')
INSERT INTO dogovor
VALUES ('05-11-2013',
        'VIP')
SELECT * FROM dogovor