Логички и физички дизајн - Креирање база податоци (со SQL DDL): fit.sql

File fit.sql, 7.2 KB (added by 201028, 3 weeks ago)
Line 
1
2drop schema if exists project cascade;
3create schema project;
4
5drop table if exists Rezervacija ;
6drop table if exists Ocenka;
7drop table if exists Termin;
8drop table if exists Tip;
9drop table if exists Trening;
10drop table if exists Fitnes_instruktor;
11drop table if exists Klient cascade;
12drop table if exists Sopstvenik;
13drop table if exists Fitnes_Centar;
14drop table if exists Korisnici cascade;
15
16
17create table Korisnici (
18 user_id int primary key,
19 username varchar(50),
20 password varchar(50)
21 );
22
23
24create table Fitnes_Centar (
25 ime varchar(100) not null,
26 tel_br varchar(15),
27 lokacija varchar(150)
28);
29
30
31create table Klient (
32 user_id_klient int primary key,
33 ime varchar(50) not null,
34 prezime varchar(50) not null,
35 email varchar(100) unique not null,
36 tel_broj varchar(15),
37 constraint fk20 foreign key (user_id_klient) references Korisnici(user_id)
38);
39
40
41create table Sopstvenik (
42 user_id_sopstvenik int primary key,
43 username varchar(30),
44 ime varchar(100) not null,
45 prezime varchar(100) not null,
46 tel_br varchar(15),
47 email varchar(150),
48 constraint fk1 foreign key (user_id_sopstvenik) references Korisnici(user_id)
49);
50create table Fitnes_Instruktor (
51 user_id_instruktor int primary key,
52 ime varchar(100) not null,
53 prezime varchar(100) not null,
54 kratka_bio text,
55 rab_iskustvo int,
56 tel_broj varchar(15),
57 email varchar(150),
58 constraint fk2 foreign key (user_id_instruktor) references Korisnici(user_id)
59);
60
61create table Trening (
62 trening_id int primary key,
63 tip varchar(50) not null,
64 user_id_instruktor_trening int not null,
65 constraint fk4 foreign key (user_id_instruktor_trening) references Fitnes_Instruktor(user_id_instruktor)
66);
67
68create table Tip (
69 trening_id_tip int primary key,
70 individualen varchar(20) not null,
71 grupen varchar(20) not null,
72 constraint fk5 foreign key (trening_id_tip) references Trening(trening_id)
73);
74
75create table Termin (
76 termin_id int,
77 trening_id_termin int not null,
78 user_id_klient_termin int,
79 pocetok timestamp not null,
80 vremetraenje int,
81 primary key (termin_id, trening_id_termin, user_id_klient_termin),
82 constraint fk6 foreign key (trening_id_termin) references Trening(trening_id),
83 constraint fk7 foreign key (user_id_klient_termin) references Klient(user_id_klient)
84);
85
86create table Rezervacija (
87 termin_id int not null,
88 trening_id_rez int not null,
89 user_id_klient_rez int not null,
90 primary key (termin_id, trening_id_rez, user_id_klient_rez),
91 constraint fk10 foreign key (termin_id, trening_id_rez, user_id_klient_rez) references Termin(termin_id, trening_id_termin, user_id_klient_termin)
92);
93
94create table Ocenka (
95 trening_id_o int not null,
96 user_id_klient_o int not null,
97 vrednost int not null,
98 komentar text,
99 primary key(trening_id_o, user_id_klient_o),
100 constraint fk11 foreign key (trening_id_o) references Trening(trening_id),
101 constraint fk12 foreign key (user_id_klient_o) references Klient(user_id_klient)
102);
103
104
105
106
107INSERT INTO Fitnes_Centar (ime, tel_br, lokacija)
108VALUES
109('Fitnes Center 1', '123456789', 'Location 1');
110
111
112insert into Korisnici (user_id, username, password) values
113(1, 'client1', 'pass1'),
114(2, 'client2', 'pass2'),
115(3, 'client3', 'pass3'),
116(4, 'client4', 'pass4'),
117(5, 'client5', 'pass5'),
118(6, 'client6', 'pass6'),
119(7, 'client7', 'pass7'),
120(8, 'client8', 'pass8'),
121(9, 'client9', 'pass9'),
122(10, 'client10', 'pass10'),
123(11, 'instructor1', 'pass11'),
124(12, 'instructor2', 'pass12'),
125(13, 'instructor3', 'pass13'),
126(14, 'instructor4', 'pass14'),
127(15, 'instructor5', 'pass15'),
128(16, 'instructor6', 'pass16'),
129(17, 'instructor7', 'pass17'),
130(18, 'instructor8', 'pass18'),
131(19, 'instructor9', 'pass19'),
132(20, 'owner', 'ownerpass');
133
134insert into Klient (user_id_klient, ime, prezime, email, tel_broj) values
135(1, 'John', 'Doe', 'john.doe@email.com', '0712345678'),
136(2, 'Jane', 'Smith', 'jane.smith@email.com', '0712345679'),
137(3, 'Alice', 'Johnson', 'alice.johnson@email.com', '0712345680'),
138(4, 'Bob', 'Williams', 'bob.williams@email.com', '0712345681'),
139(5, 'Charlie', 'Brown', 'charlie.brown@email.com', '0712345682'),
140(6, 'David', 'Davis', 'david.davis@email.com', '0712345683'),
141(7, 'Eve', 'Miller', 'eve.miller@email.com', '0712345684'),
142(8, 'Frank', 'Wilson', 'frank.wilson@email.com', '0712345685'),
143(9, 'Grace', 'Moore', 'grace.moore@email.com', '0712345686'),
144(10, 'Hannah', 'Taylor', 'hannah.taylor@email.com', '0712345687');
145
146
147insert into Sopstvenik (user_id_sopstvenik, username, ime, prezime, tel_br, email) values
148(20, 'owner', 'Michael', 'Jordan', '0712345690', 'michael.jordan@email.com');
149
150insert into Fitnes_Instruktor (user_id_instruktor, ime, prezime, kratka_bio, rab_iskustvo, tel_broj, email) values
151(11, 'Samuel', 'Lee', 'Experienced in strength training', 5, '0712345691', 'samuel.lee@email.com'),
152(12, 'Oliver', 'Adams', 'Specialist in cardio workouts', 4, '0712345692', 'oliver.adams@email.com'),
153(13, 'Lily', 'Clark', 'Expert in pilates and yoga', 6, '0712345693', 'lily.clark@email.com'),
154(14, 'George', 'Lewis', 'Strength coach', 7, '0712345694', 'george.lewis@email.com'),
155(15, 'Nancy', 'Walker', 'Yoga instructor', 3, '0712345695', 'nancy.walker@email.com'),
156(16, 'Daniel', 'Harris', 'Cardio and flexibility expert', 4, '0712345696', 'daniel.harris@email.com'),
157(17, 'Jessica', 'Young', 'Pilates and injury recovery expert', 6, '0712345697', 'jessica.young@email.com'),
158(18, 'Michael', 'King', 'Trainer for muscle building', 8, '0712345698', 'michael.king@email.com'),
159(19, 'Sarah', 'Scott', 'Expert in group fitness', 5, '0712345699', 'sarah.scott@email.com');
160
161insert into Trening (trening_id, tip, user_id_instruktor_trening) values
162(1, 'Individualen', 11),
163(2, 'Grupen', 12),
164(3, 'Individualen', 13),
165(4, 'Individualen', 14),
166(5, 'Grupen', 15),
167(6, 'Grupen', 16),
168(7, 'Individualen', 17),
169(8, 'Grupen', 18),
170(9, 'Individualen', 19);
171
172insert into Tip (trening_id_tip, individualen, grupen) values
173(1, 'Yes', 'No'),
174(2, 'No', 'Yes'),
175(3, 'Yes', 'No'),
176(4, 'Yes', 'No'),
177(5, 'No', 'Yes'),
178(6, 'No', 'Yes'),
179(7, 'Yes', 'No'),
180(8, 'No', 'Yes'),
181(9, 'Yes', 'No');
182
183insert into Termin (termin_id, trening_id_termin, user_id_klient_termin, pocetok, vremetraenje) values
184(1, 1, 1, '2025-01-30 08:00:00', 60),
185(2, 2, 2, '2025-01-30 09:00:00', 60),
186(3, 3, 3, '2025-01-30 10:00:00', 60),
187(4, 4, 4, '2025-01-30 11:00:00', 60),
188(5, 5, 5, '2025-01-30 12:00:00', 60),
189(6, 6, 6, '2025-01-30 13:00:00', 60),
190(7, 7, 7, '2025-01-30 14:00:00', 60),
191(8, 8, 8, '2025-01-30 15:00:00', 60),
192(9, 9, 9, '2025-01-30 16:00:00', 60);
193
194
195insert into Rezervacija (termin_id, trening_id_rez, user_id_klient_rez) values
196(1, 1, 1),
197(2, 2, 2),
198(3, 3, 3),
199(4, 4, 4),
200(5, 5, 5),
201(6, 6, 6),
202(7, 7, 7),
203(8, 8, 8),
204(9, 9, 9);
205
206insert into Ocenka (user_id_instruktor_o, user_id_klient_o, vrednost, komentar) values
207(11, 1, 5, 'Great instructor!'),
208(12, 2, 4, 'Good workout, could be improved'),
209(13, 3, 5, 'Amazing session, very calming'),
210(14, 4, 4, 'It was good'),
211(15, 5, 5, 'Intense and fun!'),
212(16, 6, 3, 'It was okay'),
213(17, 7, 5, 'Loved the class!'),
214(18, 8, 4, 'It was great, good energy'),
215(19, 9, 5, 'Session was tough but amazing');
216
217