· 4 years ago · Apr 14, 2021, 10:26 PM
1DROP TABLE "Centre";
2DROP TABLE "AdminStaff";
3DROP TABLE "Instructor";
4DROP TABLE "Client";
5DROP TABLE "School";
6DROP TABLE "Lesson";
7DROP TABLE "Car";
8DROP TABLE "Test";
9
10-- TASK 1
11
12-- Centre Definition
13CREATE TABLE IF NOT EXISTS "Centre" (
14 "CentreID" int PRIMARY KEY NOT NULL,
15 "Name" varchar(30) NOT NULL,
16 "Address" varchar(100) NOT NULL,
17 "PhoneNo" varchar(20) NOT NULL
18);
19
20-- Car Definition
21CREATE TABLE IF NOT EXISTS "Car" (
22 "CarID" int PRIMARY KEY NOT NULL,
23 "RegNo" varchar(20) NOT NULL,
24 "Model" varchar(30) NOT NULL
25);
26
27-- School Definition
28CREATE TABLE IF NOT EXISTS "School" (
29 "SchoolID" int PRIMARY KEY NOT NULL,
30 "Address" varchar(100) NOT NULL
31);
32
33-- Gender enumeration for AdminStaff, Instructor and Client
34--CREATE TYPE gender AS ENUM ('M', 'F', 'O');
35
36-- AdminStaff Definition
37CREATE TABLE IF NOT EXISTS "AdminStaff" (
38 "EmpID" int PRIMARY KEY NOT NULL,
39 "Forename" varchar(60) NOT NULL,
40 "Surname" varchar(60) NOT NULL,
41 "Gender" gender NOT NULL,
42 "PhoneNo" varchar(20) NOT NULL,
43 "Address" varchar(100) NOT NULL,
44 "Role" varchar(20) NOT NULL,
45 "SchoolID" int NOT NULL,
46 CONSTRAINT "AdminStaff_School_fk"
47 FOREIGN KEY("SchoolID")
48 REFERENCES "School"("SchoolID")
49);
50
51-- Instructor Definition
52CREATE TABLE IF NOT EXISTS "Instructor" (
53 "EmpID" int PRIMARY KEY NOT NULL,
54 "Forename" varchar(60) NOT NULL,
55 "Surname" varchar(60) NOT NULL,
56 "Gender" gender NOT NULL,
57 "PhoneNo" varchar(20) NOT NULL,
58 "Address" varchar(100) NOT NULL,
59 "LicenceNo" varchar(50) NOT NULL,
60 "SchoolID" int NOT NULL,
61 "CarID" int NOT NULL,
62 CONSTRAINT "Instructor_School_fk"
63 FOREIGN KEY("SchoolID")
64 REFERENCES "School"("SchoolID"),
65 CONSTRAINT "Instructor_Car_fk"
66 FOREIGN KEY("CarID")
67 REFERENCES "Car"("CarID")
68);
69
70-- Client Definition
71CREATE TABLE IF NOT EXISTS "Client" (
72 "ClientID" int NOT NULL,
73 "Forename" varchar(60) NOT NULL,
74 "Surname" varchar(60) NOT NULL,
75 "Gender" gender NOT NULL,
76 "DoB" date NOT NULL,
77 "PhoneNo" varchar(20) NOT NULL,
78 "Address" varchar(100) NOT NULL,
79 "ProvLicenceNo" varchar(50) NOT NULL,
80 "SchoolID" int NOT NULL,
81 CONSTRAINT "Client_School_fk"
82 FOREIGN KEY ("SchoolID")
83 REFERENCES "School"("SchoolID")
84);
85
86-- Lesson Definition
87CREATE TABLE IF NOT EXISTS "Lesson" (
88 "OnDate" date NOT NULL,
89 "OnTime" time NOT NULL,
90 "ClientID" int NOT NULL,
91 "EmpID" int NOT NULL,
92 CONSTRAINT "Lesson_pk"
93 PRIMARY KEY ("OnDate", "OnTime", "ClientID")
94);
95
96--CREATE TYPE status AS ENUM ('Not Taken', 'Passed', 'Failed');
97
98-- Test Definition
99CREATE TABLE IF NOT EXISTS "Test" (
100 "OnDate" date NOT NULL,
101 "OnTime" time NOT NULL,
102 "ClientID" int NOT NULL,
103 "EmpID" int NOT NULL,
104 "CentreID" int NOT NULL,
105 "Status" status NOT NULL,
106 "Reason" varchar(150)
107);
108
109-- Centre Insert
110INSERT INTO "Centre"
111("CentreID", "Name", "Address", "PhoneNo")
112VALUES
113(1, 'Canterbury', '12 Meryl Street', '+44 1227-968-5287'),
114(2, 'Whitstable', '5 The Strand, Whitstable', '01227457012'),
115(3, 'Faversham', '1 High Street', '01795 865129');
116
117-- Car Insert
118INSERT INTO "Car"
119("CarID", "RegNo", "Model")
120VALUES
121(124, 'BD51 SMR', 'VW Polo'),
122(653, 'WS62 QWE', 'Ford Focus'),
123(912, 'FD52 TGF', 'VW Polo'),
124(167, 'FD52 YTR', 'VW Polo');
125
126-- School Insert
127INSERT INTO "School"
128("SchoolID", "Address")
129VALUES
130(1, '12 Whitechapel, Canterbury'),
131(2, '9 Middle Wall, Whitstable');
132
133-- AdminStaff Insert
134INSERT INTO "AdminStaff"
135("EmpID", "Forename", "Surname", "Gender", "PhoneNo", "Address", "Role", "SchoolID")
136VALUES
137(1006, 'Fred', 'Grimes', 'M', '012275435665', '27 Cherry Street', 'assistant', 2),
138(1009, 'Jill', 'Joffries', 'F', '+44776618645', '27 Cherry Street', 'manager', 1),
139(1019, 'Justine', 'Joffries', 'F', '(01227) 812035', '19 Creosote Road', 'assistant', 1)
140;
141
142-- Instructor Insert
143INSERT INTO "Instructor"
144("EmpID", "Forename", "Surname", "Gender", "PhoneNo", "Address", "LicenceNo", "SchoolID", "CarID")
145VALUES
146(2009, 'James', 'Joffries', 'M', '012275435665', '27 Cherry Street', 'FTR76398', 1, 124),
147(2011, 'Jim', 'Adams', 'M', '065490125674', '4 The Vale', 'TGY98555a', 2, 912),
148(2013, 'Trinny', 'Vair', 'F', '0044587208725', '17 High Street, Chartham', 'YHF7665467', 1, 653);
149
150-- Client Insert
151INSERT INTO "Client"
152("ClientID", "Forename", "Surname", "Gender", "DoB", "PhoneNo", "Address", "ProvLicenceNo", "SchoolID")
153VALUES
154(1, 'Andy', 'Twill', 'M', '1998-02-01', '00446784129876', '27 Cherry Street, CT4 7NF', 'TYH7890', 2),
155(2, 'Sue', 'Adams', 'F', '1989-06-14', '0841-234-876', '45 Eggy Lane', 'CIO67891', 1),
156(3, 'Jean', 'Adams', 'F', '2001-11-19', '01227765329', '4 Harkness Lane, Canterbury', 'RTY678923', 1);
157
158-- Lesson Insert
159INSERT INTO "Lesson"
160("OnDate", "OnTime", "ClientID", "EmpID")
161VALUES
162('2020-06-24', '10:00:00', 1, 2011),
163('2019-06-07', '10:00:00', 2, 2009),
164('2020-07-12', '14:00:00', 1, 2011),
165('2020-08-19', '16:00:00', 1, 2011),
166('2020-08-17', '16:00:00', 2, 2009),
167('2020-08-01', '14:00:00', 1, 2011);
168
169-- Test Insert
170INSERT INTO "Test"
171("OnDate", "OnTime", "ClientID", "EmpID", "CentreID", "Status", "Reason")
172VALUES
173('2021-03-01', '11:00:00', 1, 2011, 2, 'Passed', NULL),
174('2019-08-13', '13:00:00', 2, 2009, 3, 'Failed', 'Lack of Observation'),
175('2019-10-21', '11:00:00', 2, 2009, 2, 'Failed', 'Speeding'),
176('2020-08-19', '10:00:00', 2, 2013, 2, 'Not Taken', NULL);
177