· 6 years ago · May 18, 2019, 08:32 PM
1/*
2Created 5/18/2019
3Modified 5/18/2019
4Project
5Model
6Company
7Author
8Version
9Database mySQL 5
10*/
11
12
13drop table IF EXISTS Zaposleni;
14drop table IF EXISTS Kupci;
15drop table IF EXISTS Izposoja;
16drop table IF EXISTS Pogon;
17drop table IF EXISTS Menjalnik;
18drop table IF EXISTS Gorivo;
19drop table IF EXISTS Vozila;
20
21
22Create table Vozila (
23 ID_Vozila Int NOT NULL AUTO_INCREMENT,
24 ID_pogona Int NOT NULL,
25 ID_Goriva Int NOT NULL,
26 ID_menjalnika Int NOT NULL,
27 Vrsta_vozila Varchar(20) NOT NULL,
28 Znamka Varchar(20) NOT NULL,
29 Moc_motorja Int NOT NULL,
30 Letnik Int NOT NULL,
31 Prevozeni_Kilometri Int,
32 Cena Int NOT NULL,
33 Barva Varchar(10) NOT NULL,
34 Primary Key (ID_Vozila)) ENGINE = MyISAM;
35
36Create table Gorivo (
37 ID_Goriva Int NOT NULL AUTO_INCREMENT,
38 Vrsta_Goriva Varchar(20) NOT NULL,
39 Primary Key (ID_Goriva)) ENGINE = MyISAM;
40
41Create table Menjalnik (
42 ID_menjalnika Int NOT NULL AUTO_INCREMENT,
43 Vrsta_Menjalnika Varchar(20) NOT NULL,
44 Primary Key (ID_menjalnika)) ENGINE = MyISAM;
45
46Create table Pogon (
47 ID_pogona Int NOT NULL AUTO_INCREMENT,
48 Vrsta_pogona Varchar(20) NOT NULL,
49 Primary Key (ID_pogona)) ENGINE = MyISAM;
50
51Create table Izposoja (
52 ID_izpsoje Int NOT NULL AUTO_INCREMENT,
53 Datum_Izposoje Date NOT NULL,
54 datum_vracila Date NOT NULL,
55 Cena Float NOT NULL,
56 ID_Kupca Int NOT NULL,
57 ID_zaposlenega Int NOT NULL,
58 ID_Vozila Int NOT NULL,
59 Primary Key (ID_izpsoje,ID_Kupca,ID_zaposlenega,ID_Vozila)) ENGINE = MyISAM;
60
61Create table Kupci (
62 ID_Kupca Int NOT NULL AUTO_INCREMENT,
63 ime Varchar(20) NOT NULL,
64 priimek Varchar(20) NOT NULL,
65 Datum_Rojstva Date NOT NULL,
66 Telefon Varchar(13) NOT NULL,
67 Primary Key (ID_Kupca)) ENGINE = MyISAM;
68
69Create table Zaposleni (
70 ID_zaposlenega Int NOT NULL AUTO_INCREMENT,
71 ime Varchar(20) NOT NULL,
72 priimek Varchar(20) NOT NULL,
73 Telefon Varchar(12) NOT NULL,
74 Primary Key (ID_zaposlenega)) ENGINE = MyISAM;
75
76
77Alter table Izposoja add Foreign Key (ID_Vozila) references Vozila (ID_Vozila) on delete restrict on update restrict;
78Alter table Vozila add Foreign Key (ID_Goriva) references Gorivo (ID_Goriva) on delete restrict on update restrict;
79Alter table Vozila add Foreign Key (ID_menjalnika) references Menjalnik (ID_menjalnika) on delete restrict on update restrict;
80Alter table Vozila add Foreign Key (ID_pogona) references Pogon (ID_pogona) on delete restrict on update restrict;
81Alter table Izposoja add Foreign Key (ID_Kupca) references Kupci (ID_Kupca) on delete restrict on update restrict;
82Alter table Izposoja add Foreign Key (ID_zaposlenega) references Zaposleni (ID_zaposlenega) on delete restrict on update restrict;
83
84
85/* Users permissions */