· 9 years ago · Jan 29, 2017, 08:04 PM
1Klan(naziv~~, opis , liderKorisnickoIme*)
2Sojuz(klan1~~*, klan2~~*)
3Nep(klan1~~*, klan2~~*)
4Borec(borecID~~, korisnicko_ime~~*, poeniNap, poeniOdb, brzina, sila)
5borecNapad(borecID~~*, korisnicko_ime~~*, procent)
6borecOdbrana(borecID~~*, korisnicko_ime~~*, procent)
7duel(id~~,vreme, datum, napadnatKorisnickoIme*, napagjacKorisnickoIme*, vojnaId*, napBId*, napBuN*, odBId*, odBuN*)
8vojna(id~~, klan1*, klan2*)
9Igrac(korisnicko_ime~~, email, vozrast, drzava, nazivKlan*)
10Create table Igrac(
11 korisnicko_ime Varchar(20),
12 email varchar(20),
13 vozrast integer,
14 drzava varchar(20),
15 nazivKlan* varchar(20),
16 constraint PK primary key(korisnicko_ime),
17 constraint FK_Igrac_Klan foreign key(nazivKlan) references Klan(naziv)
18 on update cascade on delete set null
19 constraint emailDomen check(email like "%@gmail.com")
20)
21Create Table Borec (
22 borecID Integer not null,
23 korisnicko_ime Varchar(20) not null,
24 poeniNap Integer,
25 poeniOdb Integer,
26 brzina Integer,
27 sila Integer,
28 Constraint PK primary key(borecID, korisnicko_ime),
29 Constraint FK_borec_UN foreign key(korisnicko_ime) references Igrac(korisnicko_ime),
30 on Update Cascade on Delete Cascade
31
32)
33select *
34from igraci
35minus
36((select i.*
37from igrac as i, borciNapad as bN
38where i.korisnicko_ime == bn.korisnicko_ime)
39union
40(select i.*
41from igrac as i, borciOdbrana as bO
42where i.korisnicko_ime == bO.korisnicko_ime))
43
44Create view dueli as
45select distinct i1.korisnicko_ime napadnat, i1.nazivKlan napadnatKlan, i2.korisnicko_ime napagjac, i2.nazivKlan napagjacKlan
46from ((Igrac i1 join duel d on d.napadnatKorisnickoIme = i1.korisnicko_ime) join Igrac i2 on d.napagjacKorisnickoIme = i2.korisnicko_ime)
47
48Create view napagjaciPoEden
49Select napagjac
50from (Select distinct d.napagjac, d.napadnatKlan from dueli as d)
51group by napagjac
52having Count(*) = 1
53
54select npe.napagjac
55from (napagjaciPoEden as npe join duel d on d.napagjac = npe.napagjac)
56where not Exists (
57 select *
58 from duels as d2
59 where d2.napagjacKlan = d.napadnatKlan and d2.napadnatKlan = d.napagjacKlan
60)
61create view igracNapad as
62select korisnicko_ime un, SUM(poeniNap) napad
63from Borec b
64group by korisnicko_ime
65
66create view maxNapadpoKlan as
67select naziv, max(napad) maxNapad
68from igracNapad, igrac
69group by naziv
70
71select in.un
72from igracNapad in, igrac i, maxNapadpoKlan mN
73where i.naziv = mN.naziv and i.napad = mN.maxNapad
74
75// Borec(borecID~~, korisnicko_ime~~*, poeniNap, poeniOdb, brzina, sila)
76Create trigger vkupnoBorci
77before insert on Borec
78FOR EACH ROW
79BEGIN
80 UPDATE Igrac SET VkupnoBorciIgrac = (select VkupnoBorciIgrac from Igrac i where i.korisnicko_ime = NEW.)
81 WHERE <conditions>
82
83END