· 6 years ago · Mar 12, 2019, 10:22 PM
1#@(#) script.ddl
2
3DROP TABLE IF EXISTS Materials;
4DROP TABLE IF EXISTS Job;
5DROP TABLE IF EXISTS WinterJob;
6DROP TABLE IF EXISTS WeatherConditions;
7DROP TABLE IF EXISTS Created;
8DROP TABLE IF EXISTS RoadSection;
9DROP TABLE IF EXISTS QualityControl;
10DROP TABLE IF EXISTS Mechanism;
11DROP TABLE IF EXISTS Warehouse;
12DROP TABLE IF EXISTS User;
13DROP TABLE IF EXISTS Subunit;
14DROP TABLE IF EXISTS JobType;
15CREATE TABLE JobType
16(
17 unnamed1,
18 unnamed2,
19 unnamed3,
20 unnamed4,
21 unnamed5,
22 unnamed6,
23 id_JobType integer,
24 PRIMARY KEY(id_JobType)
25);
26
27CREATE TABLE Subunit
28(
29 id_Subunit integer NOT NULL,
30 Name varchar (255),
31 Adress varchar (255),
32 Phone varchar (255),
33 Email varchar (255),
34 PRIMARY KEY(id_Subunit)
35);
36
37CREATE TABLE User
38(
39 id_User integer NOT NULL,
40 Name varchar (255),
41 Surname varchar (255),
42 Phone varchar (255),
43 Adress date,
44 Email varchar (255),
45 Role char (17),
46 fk_Subunitid_Subunit integer NOT NULL,
47 CHECK(Role in ('ADMIN', 'WORKER', 'SUPER_ADMIN', 'SUPER_VIEWER', 'MANAGER', 'QUALITY_CONTROLER')),
48 PRIMARY KEY(id_User),
49 CONSTRAINT Has FOREIGN KEY(fk_Subunitid_Subunit) REFERENCES Subunit (id_Subunit)
50);
51
52CREATE TABLE Warehouse
53(
54 id_Warehouse integer,
55 Capacity float,
56 CurrentCapacity float,
57 Latitude float,
58 Longitude float,
59 fk_Subunitid_Subunit integer NOT NULL,
60 PRIMARY KEY(id_Warehouse),
61 CONSTRAINT Belongs FOREIGN KEY(fk_Subunitid_Subunit) REFERENCES Subunit (id_Subunit)
62);
63
64CREATE TABLE Mechanism
65(
66 id_Mechanism integer NOT NULL,
67 VehicleCode varchar (255),
68 LastChecked date,
69 IsUsable boolean DEFAULT true,
70 VehicleType char (7),
71 fk_WinterJobid_WinterJob integer NOT NULL,
72 CHECK(VehicleType in ('Grader', 'Truck', 'Cleaner')),
73 PRIMARY KEY(id_Mechanism),
74 CONSTRAINT Are used FOREIGN KEY(fk_WinterJobid_WinterJob) REFERENCES WinterJob (id_WinterJob)
75);
76
77CREATE TABLE QualityControl
78(
79 id_QualityControl integer NOT NULL,
80 Score int,
81 Note varchar (255),
82 IsReviewed boolean DEFAULT false,
83 Date date,
84 fk_WinterJobid_WinterJob integer NOT NULL,
85 fk_Userid_User integer NOT NULL,
86 PRIMARY KEY(id_QualityControl),
87 UNIQUE(fk_WinterJobid_WinterJob),
88 CONSTRAINT Evaluates FOREIGN KEY(fk_WinterJobid_WinterJob) REFERENCES WinterJob (id_WinterJob),
89 CONSTRAINT Creates FOREIGN KEY(fk_Userid_User) REFERENCES User (id_User)
90);
91
92CREATE TABLE RoadSection
93(
94 ,
95 id_RoadSection integer NOT NULL,
96 SectionType char (15),
97 SectionName varchar (255),
98 SectionBegin float,
99 SectionEnd float,
100 AverageWidth float,
101 MaintenanceLevel char (6),
102 fk_WinterJobid_WinterJob integer NOT NULL,
103 CHECK(MaintenanceLevel in ('Medium', 'High', 'Low')),
104 CHECK(SectionType in ('Highway', 'Contry road', 'Connection road', 'District road')),
105 PRIMARY KEY(id_RoadSection),
106 CONSTRAINT Belongs to FOREIGN KEY(fk_WinterJobid_WinterJob) REFERENCES WinterJob (id_WinterJob)
107);
108
109CREATE TABLE Created
110(
111 fk_WinterJobid_WinterJob integer,
112 fk_Userid_User integer,
113 PRIMARY KEY(fk_WinterJobid_WinterJob, fk_Userid_User),
114 CONSTRAINT Created FOREIGN KEY(fk_WinterJobid_WinterJob) REFERENCES WinterJob (id_WinterJob)
115);
116
117CREATE TABLE WeatherConditions
118(
119 id_WeatherConditions integer NOT NULL,
120 Temperature float,
121 MoistureLevel float,
122 PressureLevel float,
123 GeneralCondition char (7),
124 fk_QualityControlid_QualityControl integer NOT NULL,
125 CHECK(GeneralCondition in ('Wet', 'Icy', 'Snowing', 'Raining', 'Storm')),
126 PRIMARY KEY(id_WeatherConditions),
127 UNIQUE(fk_QualityControlid_QualityControl),
128 CONSTRAINT Has FOREIGN KEY(fk_QualityControlid_QualityControl) REFERENCES QualityControl (id_QualityControl)
129);
130
131CREATE TABLE WinterJob
132(
133 id_WinterJob integer NOT NULL,
134 CreatedAt date NOT NULL,
135 FinishedAt date,
136 StartedAt date,
137 EstimatedCost float,
138 ActualCost float,
139 fk_WeatherConditionsid_WeatherConditions integer NOT NULL,
140 fk_WeatherConditionsid_WeatherConditions1 integer NOT NULL,
141 PRIMARY KEY(id_WinterJob),
142 UNIQUE(fk_WeatherConditionsid_WeatherConditions),
143 UNIQUE(fk_WeatherConditionsid_WeatherConditions1),
144 CONSTRAINT Has FOREIGN KEY(fk_WeatherConditionsid_WeatherConditions) REFERENCES WeatherConditions (id_WeatherConditions)
145);
146
147CREATE TABLE Job
148(
149 id_Job integer NOT NULL,
150 Description varchar (255),
151 Quadrature float,
152 QuadratureUnit varchar (255),
153 Code varchar (255),
154 DangerLevel char (6),
155 fk_WinterJobid_WinterJob integer NOT NULL,
156 CHECK(DangerLevel in ('Medium', 'High', 'Low')),
157 PRIMARY KEY(id_Job),
158 CONSTRAINT Consists FOREIGN KEY(fk_WinterJobid_WinterJob) REFERENCES WinterJob (id_WinterJob)
159);
160
161CREATE TABLE Materials
162(
163 id_Materials integer NOT NULL,
164 Amount float,
165 Material char (8),
166 fk_Warehouseid_Warehouse integer NOT NULL,
167 fk_WinterJobid_WinterJob integer NOT NULL,
168 CHECK(Material in ('Salt', 'Sand', 'Solution', 'Other')),
169 PRIMARY KEY(id_Materials),
170 CONSTRAINT Has FOREIGN KEY(fk_Warehouseid_Warehouse) REFERENCES Warehouse (id_Warehouse),
171 CONSTRAINT Uses FOREIGN KEY(fk_WinterJobid_WinterJob) REFERENCES WinterJob (id_WinterJob)
172);