RWrSQ663

1-- ErdPlus ohjelman luoma sql-skripti.
2-- HUOM 1: Tämä ei ole valmis luontiskripti.
3  
4-- HUOM 2: Tyhmä ope hävitti Tile-taulun luontilauseet.
5-- Sinun täytyy korjata tilanne.
6DROP DATABASE IF EXISTS RPG;
7CREATE DATABASE RPG;
8USE RPG;
9
10
11CREATE TABLE Enemy_Type
12(
13  Id INT NOT NULL AUTO_INCREMENT,
14  Attack_Value INT NOT NULL,
15  Def_Value INT NOT NULL,
16  Initial_Hp INT NOT NULL,
17  Description VARCHAR(40) NOT NULL,
18  PRIMARY KEY (Id)
19);
20
21/*CREATE TABLE Tile_Type
22(
23  Id INT NOT NULL,
24  Movement_Diff INT,
25  Combat_Diff INT,
26  Description VARCHAR(40) NOT NULL,
27  PRIMARY KEY (Id)
28);
29*/
30
31CREATE TABLE Tile_Type
32(
33  Id INT NOT NULL AUTO_INCREMENT,
34  Movement_Diff INT,
35  Combat_Diff INT,
36  Description VARCHAR(40) NOT NULL,
37  PRIMARY KEY (Id)
38);
39
40-- *** TODOOOOOOO ***
41-- *** Tässä pitäisi olla Tile-taulun luonti ***
42
43CREATE TABLE Tile 
44(
45Id INT NOT NULL AUTO_INCREMENT,
46Tile_Type_Id INT NOT NULL,
47X_Coordinate INT NOT NULL,
48Y_Coordinate INT NOT NULL,
49PRIMARY KEY (Id),
50FOREIGN KEY (Tile_Type_Id) REFERENCES Tile_type (Id)
51);
52
53
54CREATE TABLE Player
55(
56  Id INT NOT NULL AUTO_INCREMENT,
57  NAME VARCHAR(40) NOT NULL,
58  Level INT NOT NULL,
59  Hp INT NOT NULL,
60  Wealth INT NOT NULL,
61  Tile_Id INT,
62  PRIMARY KEY (Id),
63  FOREIGN KEY (Tile_Id) REFERENCES Tile(Id)
64);
65
66CREATE TABLE Enemy
67(
68  Id INT NOT NULL AUTO_INCREMENT,
69  NAME VARCHAR(40) NOT NULL,
70  Hp INT NOT NULL,
71  Tile_Id INT NOT NULL,
72  Enemy_Type_Id INT NOT NULL,
73  PRIMARY KEY (Id),
74  FOREIGN KEY (Tile_Id) REFERENCES Tile(Id),
75  FOREIGN KEY (Enemy_Type_Id) REFERENCES Enemy_Type(Id)
76);
77
78CREATE TABLE Object
79(
80  Id INT NOT NULL AUTO_INCREMENT,
81  NAME VARCHAR(40) NOT NULL,
82  Owner_Id INT,
83  Tile_Id INT,
84  Attack_Value INT NOT NULL,
85  Def_Value INT NOT NULL,
86  Worth INT NOT NULL,
87  PRIMARY KEY (Id),
88  FOREIGN KEY (Owner_Id) REFERENCES Player(Id),
89  FOREIGN KEY (Tile_Id) REFERENCES Tile(Id)
90);
91
92/*
93INSERT INTO Tile_Type (Id, Description)
94VALUES(1,"Testi");
95
96INSERT INTO  Tile_type
97VALUES(2, 13, 11, "Mountain");
98*/
99
100INSERT INTO Tile_type
101VALUES(NULL, 5, 8, "Forest");
102
103INSERT INTO  Tile_type
104VALUES(NULL, 13, 11, "Mountain");
105
106INSERT INTO  Tile_type
107VALUES(NULL, 2, 3, "Road");
108
109INSERT INTO Tile 
110VALUES (NULL, 2, 0, 0);
111
112INSERT INTO Tile 
113VALUES (NULL, 2, 1, 0);
114
115INSERT INTO Tile 
116VALUES (NULL, 1, 2, 0);
117
118INSERT INTO Player
119VALUES (NULL, "avatar", 10, 1000, 100000, 1);
120
121INSERT INTO Enemy_type
122VALUES (NULL, 5, 4, 100, "orc");
123
124INSERT INTO Enemy_type
125VALUES (NULL, 55, 40, 1000, "dragon");
126
127INSERT INTO Enemy_type
128VALUES (NULL, 3, 2, 50, "goblin");
129
130INSERT INTO Enemy
131VALUES (NULL, "Foxy", 37, 1 ,3);
132
133INSERT INTO Enemy
134VALUES (NULL, "Azog", 80, 2, 1);
135
136INSERT INTO Enemy
137VALUES (NULL, "Grrr", 100, 1, 1);
138
139INSERT INTO Object
140VALUES (NULL, "Sword", 1, NULL, 100, 50, 25);
141
142INSERT INTO Object 
143VALUES (NULL, "Shield", NULL, 2, 10, 100, 12);
144
145INSERT INTO Object 
146VALUES (NULL, "Book", NULL, 3, 1, 1, 1000);
147
148
149INSERT INTO Tile_type
150VALUES (NULL, 1, 1, "Swamp");
151
152INSERT INTO Tile
153VALUES (NULL, 1, 3, 0);
154
155INSERT INTO Tile 
156VALUES (NULL, 2, 0, 1);
157
158INSERT INTO Tile
159VALUES (NULL, 1, 1, 1);
160
161INSERT INTO Tile
162VALUES (NULL, 1, 2, 1);
163
164INSERT INTO Tile
165VALUES (NULL, 4, 3, 1);
166
167INSERT INTO Tile 
168VALUES (NULL, 2, 0, 2);
169
170INSERT INTO Tile
171VALUES (NULL, 3, 1, 2);
172
173INSERT INTO Tile
174VALUES (NULL, 3, 2, 2);
175
176INSERT INTO Tile
177VALUES (NULL, 1, 3, 2);
178
179INSERT INTO Enemy
180VALUES (NULL, "Xodor", 50, 4, 2);
181
182INSERT INTO Object
183VALUES (NULL, "Magic Scroll", 1, NULL, 30, 30, 500);