-
Notifications
You must be signed in to change notification settings - Fork 3
/
ShipsDB.sql
110 lines (100 loc) · 4.99 KB
/
ShipsDB.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
-- Using MySQL Workbench
-- Create DB
CREATE DATABASE IF NOT EXISTS Ships;
Use Ships;
-- Create tables
CREATE TABLE IF NOT EXISTS BATTLES (
NAME VARCHAR(20) NOT NULL ,
DATE DATETIME NOT NULL
);
CREATE TABLE IF NOT EXISTS CLASSES(
CLASS VARCHAR(50) NOT NULL ,
TYPE VARCHAR(2) NOT NULL ,
COUNTRY VARCHAR (20) NOT NULL ,
NUMGUNS INTEGER,
BORE REAL,
DISPLACEMENT INTEGER
);
CREATE TABLE IF NOT EXISTS SHIPS(
NAME VARCHAR(50) NOT NULL ,
CLASS VARCHAR(50) NOT NULL ,
LAUNCHED INTEGER
);
CREATE TABLE IF NOT EXISTS OUTCOMES(
SHIP VARCHAR(50) NOT NULL ,
BATTLE VARCHAR(20) NOT NULL ,
RESULT VARCHAR(10) NOT NULL
);
-- Create constraints
ALTER TABLE BATTLES ADD CONSTRAINT PK_BATTLES PRIMARY KEY (NAME);
ALTER TABLE CLASSES ADD CONSTRAINT PK_CLASSES PRIMARY KEY (CLASS);
ALTER TABLE SHIPS ADD CONSTRAINT PK_SHIPS PRIMARY KEY (NAME);
ALTER TABLE SHIPS ADD CONSTRAINT FK_SHIPS_CLASSES FOREIGN KEY (CLASS) REFERENCES CLASSES(CLASS);
ALTER TABLE OUTCOMES ADD CONSTRAINT PK_OUTCOMES PRIMARY KEY (SHIP,BATTLE);
ALTER TABLE OUTCOMES ADD CONSTRAINT FK_OUTCOMES_BATTLES FOREIGN KEY (BATTLE) REFERENCES BATTLES (NAME);
ALTER TABLE OUTCOMES ADD CONSTRAINT FK_OUTCOMES_SHIPS FOREIGN KEY (SHIP) REFERENCES SHIPS (NAME);
-- Insert Classes
INSERT INTO CLASSES VALUES ('Bismarck', 'bb', 'Germany', 8, 15, 42000);
INSERT INTO CLASSES VALUES ('Iowa', 'bb', 'USA', 9, 16, 46000);
INSERT INTO CLASSES VALUES ('Kongo', 'bc', 'Japan', 8, 14, 32000);
INSERT INTO CLASSES VALUES ('North Carolina', 'bb', 'USA', 12, 16, 37000);
INSERT INTO CLASSES VALUES ('Renown', 'bc', 'Gt.Britain', 6, 15, 32000);
INSERT INTO CLASSES VALUES ('Revenge', 'bb', 'Gt.Britain', 8, 15, 29000);
INSERT INTO CLASSES VALUES ('Tennessee', 'bb', 'USA', 12, 14, 32000);
INSERT INTO CLASSES VALUES ('Yamato', 'bb', 'Japan', 9, 18, 65000);
-- Insert Battles
INSERT INTO BATTLES VALUES ('Guadalcanal', '1942-11-15');
INSERT INTO BATTLES VALUES ('North Atlantic', '1941-05-25');
INSERT INTO BATTLES VALUES ('North Cape', '1943-12-26');
INSERT INTO BATTLES VALUES ('Surigao Strait', '1944-10-25');
-- Insert Ships
INSERT INTO SHIPS VALUES ('California', 'Tennessee', 1921);
INSERT INTO SHIPS VALUES ('Haruna', 'Kongo', 1916);
INSERT INTO SHIPS VALUES ('Hiei', 'Kongo', 1914);
INSERT INTO SHIPS VALUES ('Iowa', 'Iowa', 1943);
INSERT INTO SHIPS VALUES ('Kirishima', 'Kongo', 1915);
INSERT INTO SHIPS VALUES ('Kongo', 'Kongo', 1913);
INSERT INTO SHIPS VALUES ('Missouri', 'Iowa', 1944);
INSERT INTO SHIPS VALUES ('Musashi', 'Yamato', 1942);
INSERT INTO SHIPS VALUES ('New Jersey', 'Iowa', 1943);
INSERT INTO SHIPS VALUES ('North Carolina', 'North Carolina', 1941);
INSERT INTO SHIPS VALUES ('Ramillies', 'Revenge', 1917);
INSERT INTO SHIPS VALUES ('Renown', 'Renown', 1916);
INSERT INTO SHIPS VALUES ('Repulse', 'Renown', 1916);
INSERT INTO SHIPS VALUES ('Resolution', 'Renown', 1916);
INSERT INTO SHIPS VALUES ('Revenge', 'Revenge', 1916);
INSERT INTO SHIPS VALUES ('Royal Oak', 'Revenge', 1916);
INSERT INTO SHIPS VALUES ('Royal Sovereign', 'Revenge', 1916);
INSERT INTO SHIPS VALUES ('Tennessee', 'Tennessee', 1920);
INSERT INTO SHIPS VALUES ('Washington', 'North Carolina', 1941);
INSERT INTO SHIPS VALUES ('Wisconsin', 'Iowa', 1944);
INSERT INTO SHIPS VALUES ('Yamato', 'Yamato', 1941);
INSERT INTO SHIPS VALUES ('Yamashiro', 'Yamato', 1947);
INSERT INTO SHIPS VALUES ('South Dakota', 'North Carolina', 1941);
INSERT INTO SHIPS VALUES ('Bismarck', 'North Carolina', 1911);
INSERT INTO SHIPS VALUES ('Duke of York', 'Renown', 1916);
INSERT INTO SHIPS VALUES ('Fuso', 'Iowa', 1940);
INSERT INTO SHIPS VALUES ('Hood', 'Iowa', 1942);
INSERT INTO SHIPS VALUES ('Rodney', 'Yamato', 1915);
INSERT INTO SHIPS VALUES ('Yanashiro', 'Yamato', 1918);
INSERT INTO SHIPS VALUES ('Schamhorst', 'North Carolina', 1917);
INSERT INTO SHIPS VALUES ('Prince of Wales', 'North Carolina', 1937);
INSERT INTO SHIPS VALUES ('King George V', 'Iowa', 1942);
INSERT INTO SHIPS VALUES ('West Virginia', 'Iowa', 1942);
-- Insert Outcomes
INSERT INTO OUTCOMES VALUES ('Bismarck', 'North Atlantic', 'sunk');
INSERT INTO OUTCOMES VALUES ('California', 'Surigao Strait', 'ok');
INSERT INTO OUTCOMES VALUES ('Duke of York', 'North Cape', 'ok');
INSERT INTO OUTCOMES VALUES ('Fuso', 'Surigao Strait', 'sunk');
INSERT INTO OUTCOMES VALUES ('Hood', 'North Atlantic', 'sunk');
INSERT INTO OUTCOMES VALUES ('King George V', 'North Atlantic', 'ok');
INSERT INTO OUTCOMES VALUES ('Kirishima', 'Guadalcanal', 'sunk');
INSERT INTO OUTCOMES VALUES ('Prince of Wales', 'North Atlantic', 'damaged');
INSERT INTO OUTCOMES VALUES ('Rodney', 'North Atlantic', 'ok');
INSERT INTO OUTCOMES VALUES ('Schamhorst', 'North Cape', 'sunk');
INSERT INTO OUTCOMES VALUES ('South Dakota', 'Guadalcanal', 'damaged');
INSERT INTO OUTCOMES VALUES ('Tennessee', 'Surigao Strait', 'ok');
INSERT INTO OUTCOMES VALUES ('Washington', 'Guadalcanal', 'ok');
INSERT INTO OUTCOMES VALUES ('West Virginia', 'Surigao Strait', 'ok');
INSERT INTO OUTCOMES VALUES ('Yamashiro', 'Surigao Strait', 'sunk');
INSERT INTO OUTCOMES VALUES ('California', 'Guadalcanal', 'damaged');