· 6 years ago · Dec 08, 2019, 06:54 PM
1DROP TABLE IF EXISTS inspection
2DROP TABLE IF EXISTS repair
3DROP TABLE IF EXISTS part
4DROP TABLE IF EXISTS plane
5DROP TABLE IF EXISTS component
6DROP TABLE IF EXISTS partType
7DROP TABLE IF EXISTS permission
8DROP TABLE IF EXISTS tool
9DROP TABLE IF EXISTS technician
10DROP TABLE IF EXISTS mechanic
11DROP TABLE IF EXISTS employee
12
13
14
15CREATE TABLE employee
16(
17 e_id INT IDENTITY PRIMARY KEY NOT NULL,
18 e_name VARCHAR(50) NOT NULL,
19 e_surname VARCHAR(30) NOT NULL,
20 age INT CHECK(age>=18),
21 gender char(1) CHECK (gender IN('M','F','N')),
22 telephone_number varchar(9) NOT NULL UNIQUE,
23 job_seniority INT CHECK(job_seniority >= 0)
24);
25ALTER TABLE employee
26ADD CONSTRAINT e_check_age CHECK (age >= job_seniority + 18);
27
28CREATE TABLE mechanic
29(
30 m_id INT REFERENCES employee PRIMARY KEY,
31 available_yn INT CHECK (available_yn >= 0 AND available_yn <=1),
32 license_nr varchar(10) UNIQUE
33);
34CREATE TABLE technician
35(
36 t_id INT REFERENCES employee PRIMARY KEY,
37 available_yn INT CHECK (available_yn >= 0 AND available_yn <=1),
38 license_nr varchar(10) UNIQUE
39);
40CREATE TABLE tool
41(
42 tool_id INT IDENTITY PRIMARY KEY NOT NULL,
43 tool_name varchar(50) NOT NULL,
44 producer varchar(20)
45);
46CREATE TABLE permission
47(
48 permission_id INT IDENTITY PRIMARY KEY NOT NULL,
49 mechanic_id INT REFERENCES mechanic NOT NULL,
50 tool_id INT REFERENCES tool NOT NULL,
51 date_of_issue DATE NOT NULL,
52 expiration_date DATE NOT NULL
53);
54ALTER TABLE permission
55ADD CONSTRAINT p_check_dates CHECK (expiration_date > date_of_issue);
56
57CREATE TABLE partType
58(
59 id INT IDENTITY PRIMARY KEY NOT NULL,
60 pt_name varchar(50) UNIQUE,
61 producer varchar(50)
62);
63CREATE TABLE component
64(
65 serial_nr varchar(12) PRIMARY KEY NOT NULL,
66 c_name varchar(30) NOT NULL,
67 dimensions varchar(50),
68 c_description varchar(100),
69 producer varchar(20),
70);
71CREATE TABLE plane
72(
73 serial_nr varchar(12) PRIMARY KEY NOT NULL,
74 time_of_service INT CHECK(time_of_service>=0) NOT NULL,
75 producer varchar(30) NOT NULL,
76 date_of_production date NOT NULL
77);
78CREATE TABLE part
79(
80 serial_nr varchar(12) PRIMARY KEY NOT NULL,
81 last_repair_date date,
82 last_inspection_date date,
83 part_name varchar(30) NOT NULL,
84 manufacturer varchar(20) NOT NULL,
85 plane_nr varchar(12) REFERENCES plane NOT NULL,
86 part_type INT REFERENCES partType NOT NULL
87);
88CREATE TABLE repair
89(
90 id INT IDENTITY PRIMARY KEY NOT NULL,
91 technician_id INT REFERENCES technician NOT NULL,
92 part_nr varchar(12) REFERENCES part NOT NULL,
93 repair_start_date date NOT NULL,
94 finish_date date,
95 completion_status varchar(30) NOT NULL
96);
97ALTER TABLE repair
98ADD CONSTRAINT r_check_dates CHECK (finish_date > repair_start_date);
99
100CREATE TABLE inspection
101(
102 id INT IDENTITY PRIMARY KEY NOT NULL,
103 technician_id INT REFERENCES technician NOT NULL,
104 part_nr varchar(12) REFERENCES part NOT NULL,
105 date_of_inspection date NOT NULL,
106 expiration_date date NOT NULL
107
108);
109ALTER TABLE inspection
110ADD CONSTRAINT i_check_dates CHECK (expiration_date > date_of_inspection);
111
112
113
114
115INSERT employee
116 (e_name, e_surname, age, gender, telephone_number, job_seniority)
117VALUES
118 ('Michael', 'Douglas', 19, 'M', '983402123', 1),
119 ('Ryan', 'Gosling', 24, NULL, '256321025', 5),
120 ('Freddie', 'Mercury', 99, 'N', '741852963', 24),
121 ('Andriej', 'Golota', 43, 'M', 'BR2030', 12),
122 ('Barb', 'Von Dead', 19, 'F', '777777777', 1),
123 ('Oktawian', 'September', 30, NULL, '21370', 11),
124 ('Alberto', 'del Piero', 42, 'M', '85208520', 2),
125 ('Omega', 'LUL', 33, 'N', '123456789', 11),
126 ('Bogdan', 'Tasiemiec', 24, 'M', '777777822', 1),
127 ('Romuald', 'Tolkien', 67, 'M', '997112997', 28),
128 ('Abra', 'Kadabra', NULL, 'N', '541236589', 1),
129 ('Mama', 'Muminka', 18, NULL, '5555222', 0),
130 ('Bon', 'Jovi', 55, 'M', 'BON-20202', 11),
131 ('Ozajasz', 'Goldberg', 73, 'M', '05441454', 47),
132 ('Ten', 'Typ', 24, 'M', '22020200', 3),
133 ('El', 'Primo', 19, 'N', '545225', 1),
134 ('Robo', 'Cop', 44, 'M', '45545445', 22),
135 ('Ahmed', 'Asiimov', 76, 'M', '987321654', 24),
136 ('Bogdan', 'Tasiemieczewicz', 44, 'M', '791980311', 2),
137 ('Anastazja', 'Bejm', 45, 'F', '142503655', 20);
138
139INSERT mechanic
140 (m_id, available_yn, license_nr)
141VALUES
142 (1, 1, 'cp-2000'),
143 (2, 1, 'cp-2001'),
144 (3, 1, 'cp-2002'),
145 (4, 0, 'cp-2003'),
146 (5, 1, 'cp-2004'),
147 (6, 1, 'cp-2005'),
148 (7, 1, 'cp-2006'),
149 (8, 0, 'cp-2007'),
150 (9, 1, 'cp-2008'),
151 (10, 1, 'cp-2009');
152
153INSERT technician
154 (t_id, available_yn, license_nr)
155VALUES
156 (11, 1, 'me-2000'),
157 (12, 0, 'me-2001'),
158 (13, 1, 'me-2002'),
159 (14, 1, 'me-2003'),
160 (15, 1, 'me-2004'),
161 (16, 1, 'me-2005'),
162 (17, 1, 'me-2006'),
163 (18, 0, 'me-2007'),
164 (19, 1, 'me-2008'),
165 (20, 1, 'me-2009');
166
167INSERT tool
168VALUES
169 ('Hammer', 'OBI'),
170 ('Saw', 'Casto'),
171 ('Cultivator', 'OBI'),
172 ('Ladder', 'OBI'),
173 ('Rake', 'OBI'),
174 ('Gloves', 'Casto'),
175 ('Screwdriver', 'OBI'),
176 ('Drill Ax', 'OBI'),
177 ('Bigger Hammer', 'BRW'),
178 ('Drill Bx', NULL);
179
180INSERT permission
181 (mechanic_id, tool_id, date_of_issue, expiration_date)
182VALUES
183 (1, 3, '2001-12-13', '2021-12-14'),
184 (2, 2, '2001-12-13', '2021-12-14'),
185 (2, 5, '2001-12-13', '2021-12-14'),
186 (6, 3, '2001-12-13', '2021-12-14'),
187 (1, 5, '2001-12-13', '2021-12-14'),
188 (7, 3, '2001-12-13', '2021-12-14'),
189 (1, 6, '2001-12-13', '2021-12-14'),
190 (9, 3, '2001-12-13', '2021-12-14'),
191 (10, 8, '2001-12-13', '2021-12-14'),
192 (3, 9, '2001-12-13', '2021-12-14');
193
194INSERT partType
195 (pt_name, producer)
196VALUES
197 ('Winglet', 'Acq Inducom'),
198 ('Wing', 'ADPma'),
199 ('Turbine Engine', 'Electronic Hardware Corporation'),
200 ('Fuselage', 'FCI Aerospace'),
201 ('Slat', 'Duracote'),
202 ('Spoiler', 'Duracote'),
203 ('Aileron', 'Duracote'),
204 ('Flap', 'ADPma'),
205 ('Elevator', 'Cox Manufacturing Company'),
206 ('Rudder', 'Cox Manufacturing Company');
207
208INSERT component
209 (serial_nr, c_name, dimensions, c_description, producer)
210VALUES
211 ('AXN 2033', 'Screw', '12`', NULL, 'OBI'),
212 ('AXN 3252', 'Pin', '2`', NULL, 'OBI'),
213 ('AXN 2023', 'Screws', '12`', NULL, 'OBI'),
214 ('AXN 2011', 'Screws', '12`', NULL, 'OBI'),
215 ('CBD 209', 'Screws', '12`', NULL, 'OBI'),
216 ('GMD 2137', 'Screws', '12`', NULL, 'OBI'),
217 ('00 2342', 'Screws', '12`', NULL, 'OBI'),
218 ('12345678910', 'Screws', '12`', NULL, 'OBI'),
219 ('XDDDDDD1', 'Screws', '12`', NULL, 'OBI'),
220 ('AXN 11111', 'Screws', '12`', NULL, 'OBI');
221
222INSERT plane
223 (serial_nr, time_of_service, producer, date_of_production)
224VALUES
225 ('AABBCCDD-7', 13, 'OMEGA Planes', '2015-03-11'),
226 ('BOEING-001', 13, 'Boeing', '2015-03-11'),
227 ('xx', 13, 'OMEGA Planes', '2015-03-11'),
228 ('dd', 13, 'OMEGA Planes', '2015-03-11'),
229 ('aa', 13, 'OMEGA Planes', '2015-03-11'),
230 ('vv', 13, 'OMEGA Planes', '2015-03-11'),
231 ('xxd', 13, 'OMEGA Planes', '2015-03-11'),
232 ('Aasf', 13, 'OMEGA Planes', '2015-03-11'),
233 ('AABBCCfs', 13, 'OMEGA Planes', '2015-03-11'),
234 ('AABBCCDf-7', 13, 'OMEGA Planes', '2015-03-11');
235
236INSERT part
237 (serial_nr, last_repair_date, last_inspection_date, part_name, manufacturer, plane_nr, part_type)
238VALUES
239 ('ABV 1', NULL, NULL, 'Fuselage 12c', 'FCI Aerospace', 'BOEING-001', 4),
240 ('ABV 2', NULL, NULL, 'Fuselage 12c', 'FCI Aerospace', 'BOEING-001', 4),
241 ('ABV 3', NULL, NULL, 'Fuselage 12c', 'FCI Aerospace', 'BOEING-001', 4),
242 ('ABV 4', NULL, NULL, 'Fuselage 12c', 'FCI Aerospace', 'BOEING-001', 4),
243 ('ABV 5', NULL, NULL, 'Fuselage 12c', 'FCI Aerospace', 'BOEING-001', 4),
244 ('ABV 6', NULL, NULL, 'Fuselage 12c', 'FCI Aerospace', 'BOEING-001', 4),
245 ('ABV 7', NULL, NULL, 'Fuselage 12c', 'FCI Aerospace', 'BOEING-001', 4),
246 ('ABV 8', NULL, NULL, 'Fuselage 12c', 'FCI Aerospace', 'BOEING-001', 4),
247 ('ABV 9', NULL, NULL, 'Fuselage 12c', 'FCI Aerospace', 'BOEING-001', 4),
248 ('ABV 10', NULL, NULL, 'Fuselage 12c', 'FCI Aerospace', 'BOEING-001', 4);
249
250INSERT repair
251 (technician_id, part_nr, repair_start_date, finish_date, completion_status)
252VALUES
253 (12, 'ABV 1', '2019-12-08', NULL, 'in repair'),
254 (12, 'ABV 1', '2019-12-08', NULL, 'in repair'),
255 (12, 'ABV 1', '2019-12-08', NULL, 'in repair'),
256 (12, 'ABV 1', '2019-12-08', NULL, 'in repair'),
257 (12, 'ABV 1', '2019-12-08', NULL, 'in repair'),
258 (12, 'ABV 1', '2019-12-08', NULL, 'in repair'),
259 (12, 'ABV 1', '2019-12-08', NULL, 'in repair'),
260 (12, 'ABV 1', '2019-12-08', NULL, 'in repair'),
261 (12, 'ABV 1', '2019-12-08', NULL, 'in repair'),
262 (12, 'ABV 1', '2019-12-08', NULL, 'in repair');
263
264INSERT inspection
265 (technician_id, part_nr, date_of_inspection, expiration_date)
266VALUES
267 (12, 'ABV 1', '2019-12-01', '2020-12-01'),
268 (12, 'ABV 1', '2019-12-01', '2020-12-01'),
269 (12, 'ABV 1', '2019-12-01', '2020-12-01'),
270 (12, 'ABV 1', '2019-12-01', '2020-12-01'),
271 (12, 'ABV 1', '2019-12-01', '2020-12-01'),
272 (12, 'ABV 1', '2019-12-01', '2020-12-01'),
273 (12, 'ABV 1', '2019-12-01', '2020-12-01'),
274 (12, 'ABV 1', '2019-12-01', '2020-12-01'),
275 (12, 'ABV 1', '2019-12-01', '2020-12-01'),
276 (12, 'ABV 1', '2019-12-01', '2020-12-01');
277
278
279
280
281
282SELECT * FROM employee
283SELECT * FROM mechanic
284SELECT * FROM technician
285SELECT * FROM tool
286SELECT * FROM permission