yzKc58jU

1DROP DATABASE IF EXISTS ALEXAMARA;
2
3CREATE DATABASE ALEXAMARA;
4
5USE ALEXAMARA;
6
7CREATE TABLE Marina
8(MarinaNum INT PRIMARY KEY AUTO_INCREMENT,
9Name CHAR(20),
10Address CHAR(15),
11City CHAR(15),
12State CHAR(2),
13Zip CHAR(5) );
14
15CREATE TABLE MarinaSlip
16(SlipID DECIMAL(4,0) PRIMARY KEY,
17MarinaNum INT,
18SlipNum CHAR(4),
19Length DECIMAL(4,0),
20RentalFee DECIMAL(8,2),
21BoatName CHAR(50),
22BoatType CHAR(50),
23OwnerNum CHAR(4) );
24
25CREATE TABLE Owner
26(OwnerNum CHAR(4) PRIMARY KEY,
27LastName CHAR(50),
28FirstName CHAR(20),
29Address CHAR(15),
30City CHAR(15),
31State CHAR(2),
32Zip CHAR(5) );
33
34CREATE TABLE ServiceCategory
35(CategoryNum DECIMAL(4,0) PRIMARY KEY,
36CategoryDescription CHAR(255) );
37
38CREATE TABLE ServiceRequest
39(ServiceID DECIMAL(4,0) PRIMARY KEY,
40SlipID DECIMAL(4,0),
41CategoryNum DECIMAL(4,0),
42Description CHAR(255),
43Status CHAR(255),
44EstHours DECIMAL(4,2),
45SpentHours DECIMAL(4,2),
46NextServiceDate DATE );
47
48INSERT INTO Marina
49VALUES
50('1','Alexamara East','108 2nd Ave','Brinman','FL','32273');
51INSERT INTO Marina
52VALUES
53('2','Alexamara Central','283 Branston','W Brinman','FL','32274');
54INSERT INTO MarinaSlip
55VALUES
56(1,'1','A1',40,3800.00,'Anderson II','Sprite 4000','AN75');
57INSERT INTO MarinaSlip
58VALUES
59(2,'1','A2',40,3800.00,'Our Toy','Ray 4025','EL25');
60INSERT INTO MarinaSlip
61VALUES
62(3,'1','A3',40,3600.00,'Escape','Sprite 4000','KE22');
63INSERT INTO MarinaSlip
64VALUES
65(4,'1','B1',30,2400.00,'Gypsy','Dolphin 28','JU92');
66INSERT INTO MarinaSlip
67VALUES
68(5,'1','B2',30,2600.00,'Anderson III','Sprite 3000','AN75');
69INSERT INTO MarinaSlip
70VALUES
71(6,'2','1',25,1800.00,'Bravo','Dolphin 25','AD57');
72INSERT INTO MarinaSlip
73VALUES
74(7,'2','2',25,1800.00,'Chinook','Dolphin 22','FE82');
75INSERT INTO MarinaSlip
76VALUES
77(8,'2','3',25,2000.00,'Listy','Dolphin 25','SM72');
78INSERT INTO MarinaSlip
79VALUES
80(9,'2','4',30,2500.00,'Mermaid','Dolphin 28','BL72');
81INSERT INTO MarinaSlip
82VALUES
83(10,'2','5',40,4200.00,'Axxon II','Dolphin 40','NO27');
84INSERT INTO MarinaSlip
85VALUES
86(11,'2','6',40,4200.00,'Karvel','Ray 4025','TR72');
87INSERT INTO Owner
88VALUES
89('AD57','Adney','Bruce and Jean','208 Citrus','Bowton','FL','31313');
90INSERT INTO Owner
91VALUES
92('AN75','Anderson','Bill','18 Wilcox','Glander Bay','FL','31044');
93INSERT INTO Owner
94VALUES
95('BL72','Blake','Mary','2672 Commodore','Bowton','FL','31313');
96INSERT INTO Owner
97VALUES
98('EL25','Elend','Sandy and Bill','462 Riverside','Rivard','FL','31062');
99INSERT INTO Owner
100VALUES
101('FE82','Feenstra','Daniel','7822 Coventry','Kaleva','FL','32521');
102INSERT INTO Owner
103VALUES
104('JU92','Juarez','Maria','8922 Oak','Rivard','FL','31062');
105INSERT INTO Owner
106VALUES
107('KE22','Kelly','Alyssa','5271 Waters','Bowton','FL','31313');
108INSERT INTO Owner
109VALUES
110('NO27','Norton','Peter','2811 Lakewood','Lewiston','FL','32765');
111INSERT INTO Owner
112VALUES
113('SM72','Smeltz','Becky and Dave','922 Garland','Glander Bay','FL','31044');
114INSERT INTO Owner
115VALUES
116('TR72','Trent','Ashton','922 Crest','Bay Shores','FL','30992');
117INSERT INTO ServiceCategory
118VALUES
119(1,'Routine engine maintenance');
120INSERT INTO ServiceCategory
121VALUES
122(2,'Engine repair');
123INSERT INTO ServiceCategory
124VALUES
125(3,'Air conditioning');
126INSERT INTO ServiceCategory
127VALUES
128(4,'Electrical systems');
129INSERT INTO ServiceCategory
130VALUES
131(5,'Fiberglass repair');
132INSERT INTO ServiceCategory
133VALUES
134(6,'Canvas installation');
135INSERT INTO ServiceCategory
136VALUES
137(7,'Canvas repair');
138INSERT INTO ServiceCategory
139VALUES
140(8,'Electronic systems (radar, GPS, autopilots, etc.)');
141INSERT INTO ServiceRequest
142VALUES
143(1,1,3,'Air conditioner periodically stops with code indicating low coolant level. Diagnose and repair.','Technician has verified the problem. Air conditioning specialist has been called.','4','2','2013-07-12');
144INSERT INTO ServiceRequest
145VALUES
146(2,5,4,'Fuse on port motor blown on two occasions. Diagnose and repair.','Open','2','0','2013-07-12');
147INSERT INTO ServiceRequest
148VALUES
149(3,4,1,'Oil change and general routine maintenance (check fluid levels, clean sea strainers etc.).','Service call has been scheduled.','1','0','2013-07-16');
150INSERT INTO ServiceRequest
151VALUES
152(4,1,2,'Engine oil level has been dropping drastically. Diagnose and repair.','Open','2','0','2013-07-13');
153INSERT INTO ServiceRequest
154VALUES
155(5,3,5,'Open pockets at base of two stantions.','Technician has completed the initial filling of the open pockets. Will complete the job after the initial fill has had sufficient time to dry.','4','2','2010-07-13');
156INSERT INTO ServiceRequest
157VALUES
158(6,11,4,'Electric-flush system periodically stops functioning. Diagnose and repair.','Open','3','0',null);
159INSERT INTO ServiceRequest
160VALUES
161(7,6,2,'Engine overheating. Loss of coolant. Diagnose and repair.','Open','2','0','2013-07-13');
162INSERT INTO ServiceRequest
163VALUES
164(8,6,2,'Heat exchanger not operating correctly.','Technician has determined that the exchanger is faulty. New exchanger has been ordered.','4','1','2013-07-17');
165INSERT INTO ServiceRequest
166VALUES
167(9,7,6,'Canvas severely damaged in windstorm. Order and install new canvas.','Open','8','0','2013-07-16');
168INSERT INTO ServiceRequest
169VALUES
170(10,2,8,'Install new GPS and chart plotter.','Scheduled','7','0','2013-07-17');
171INSERT INTO ServiceRequest
172VALUES
173(11,2,3,'Air conditioning unit shuts down with HHH showing on the control panel.','Technician not able to replicate the problem. Air conditioning unit ran fine through multiple tests. Owner to notify technician if the problem recurs.','1','1',null);
174INSERT INTO ServiceRequest
175VALUES
176(12,4,8,'Both speed and depth readings on data unit are significantly less than the owner thinks they should be.','Technician has scheduled appointment with owner to attempt to verify the problem.','2','0','2013-07-16');
177INSERT INTO ServiceRequest
178VALUES
179(13,8,2,'Customer describes engine as making a clattering sound.','Technician suspects problem with either propeller or shaft and has scheduled the boat to be pulled from the water for further investigation.','5','2','2013-07-12');
180INSERT INTO ServiceRequest
181VALUES
182(14,7,5,'Owner accident caused damage to forward portion of port side.','Technician has scheduled repair.','6','0','2013-07-13');
183INSERT INTO ServiceRequest
184VALUES
185(15,11,7,'Canvas leaks around zippers in heavy rain. Install overlap around zippers to prevent leaks.','Overlap has been created. Installation has been scheduled.','8','3','2013-07-17');