LeNAvXYH

1### This is a script file, which will be executed in MySQL using batch mode
2
3### Lines that start with # are comments
4
5# 
6
7###Create a database (remember to drop it if it exists first)
8
9DROP DATABASE IF EXISTS world_news_corp_cms;
10
11CREATE DATABASE world_news_corp_cms;
12
13
14
15USE world_news_corp_cms;
16
17
18###Author
19
20
21CREATE TABLE Author (
22
23	NI BIGINT NOT NULL,
24
25	FirstName VARCHAR (30) NOT NULL,
26
27	Surname VARCHAR (30) NOT NULL,
28
29	Gender ENUM ('M', 'F') NOT NULL,
30
31	Age INT UNSIGNED NOT NULL,
32
33	Salary DECIMAL (8,2),
34
35	Country VARCHAR (30) NOT NULL,
36	PRIMARY KEY(NI)
37
38	FOREIGN KEY (NewsArticleID) REFERENCES Author (NI)
39);
40
41
42
43### Author values from spreadsheet
44
45INSERT INTO Author VALUES (1987234920, 'John','Smith','M',34,20000,'England');
46
47INSERT INTO Author VALUES (9348098234, 'Tom','Potter','M',32,32000,'Scotland');
48
49INSERT INTO Author VALUES (4564547547, 'Jack','Kingman','M',23,14000,'India');
50
51INSERT INTO Author VALUES (5654674575, 'Chris','Trethick','M',45,24000,'India');
52
53INSERT INTO Author VALUES (5675675675, 'Dave','Carter','M',56,40000,'Ireland');
54
55INSERT INTO Author VALUES (7775656765, 'Ian','Packwood','M',33,19000,'England');
56
57INSERT INTO Author VALUES (8964334324, 'Lucy','Teague','F',18,20000,'America');
58
59INSERT INTO Author VALUES (6612355667, 'Jane','Connor','F',19,49000,'France');
60
61INSERT INTO Author VALUES (3456456457, 'Mary','Van Schmidt','F',42,32000,'France');
62
63INSERT INTO Author VALUES (1875432325, 'Liz','Fogarty','F',53,34000,'America');
64
65INSERT INTO Author VALUES (1134576877, 'Emma','Baker','F',37,20000,'England');
66
67INSERT INTO Author VALUES (2345457654, 'Anne','Nuttley','F',43,34000,'Indonesia');
68
69
70
71
72###NewsArticle
73
74CREATE TABLE NewsArticle (
75
76	Title VARCHAR (50) NOT NULL,
77
78	PublishDate DATE NOT NULL,
79
80	Content VARCHAR (250) NOT NULL,
81
82	Country VARCHAR (20) NOT NULL,
83
84	NewsArticleID BIGINT NOT NULL,
85	PRIMARY KEY (Title),
86	FOREIGN KEY (TopicID) REFERENCES Topic (Title)
87);
88
89
90
91
92###NewsArticle values from spreadsheet
93
94INSERT INTO NewsArticle VALUES ('Europe crackdown on jihadist network',CURDATE(),'Police target 17 people in raids in several European countries on suspicion of links to a jihadist network.','England');
95
96INSERT INTO NewsArticle VALUES ('Modi visit historic opportunity for UK',CURDATE(),'Indian prime minister arrives in the UK for a three day visit, described by David Cameron as a historic opportunity for both countries.','India');
97
98INSERT INTO NewsArticle VALUES ('Sweden brings in migrant border checks',CURDATE(),'Sweden brings in temporary border checks to control the flow of migrants into the country, as an EU Africa summit continues.','Sweden');
99
100INSERT INTO NewsArticle VALUES ('Apple apologises after racism outcry',CURDATE(),'Apple apologises to six schoolboys who were asked to leave one of their shops in Australia, in what the students described as a racist incident.','Australia');
101
102INSERT INTO NewsArticle VALUES ('HMRC reveals tax office shake-up',CURDATE(),'The UKs tax authority will close 137 local offices and replace them with 13 regional centres, raising fears over job losses.','England');
103
104INSERT INTO NewsArticle VALUES ('Film star visits cafe for homeless',CURDATE(),'Hollywood star George Clooney visits a sandwich shop which helps homeless people during a visit to Edinburgh.','Scotland');
105
106INSERT INTO NewsArticle VALUES ('Rolls-Royce shares dive on profit woes',CURDATE(),'Shares in aerospace group RollsRoyce sink after it warns that its profits will be hit by sharply weaker demand.','England');
107
108INSERT INTO NewsArticle VALUES ('Ex-MPs GBP13,700 on shredding and skips',CURDATE(),'The Independent Parliamentary Standards Authority releases expenses claims for 182 MPs who left the Commons at the election - with GBP705,000 spent on closing down their offices.','England');
109
110INSERT INTO NewsArticle VALUES ('Action needed to protect UK coast',CURDATE(),'The UK is ignoring known risks of flood and erosion at the coast and immediate action is needed to manage the threats, the National Trust warns.','England');
111
112INSERT INTO NewsArticle VALUES ('Venus twin excites astronomers',CURDATE(),'Astronomers hunting distant worlds say they have made one of their most significant discoveries to date, what could be a kind of hot twin to our Venus.','America');
113
114
115
116###Topic
117
118CREATE TABLE Topic (
119
120	TopicID BIGINT NOT NULL,
121	Science VARCHAR (20) NOT NULL,
122
123	Sport VARCHAR (20) NOT NULL,
124
125	Politics VARCHAR (20) NOT NULL,
126
127	Financial VARCHAR (20) NOT NULL,
128
129	Entertainment VARCHAR (20) NOT NULL,
130
131	PRIMARY KEY (TopicID)
132	
133FOREIGN KEY (NewsArticleID) REFERENCES Topic (TopicID)
134);
135
136
137###Topic values
138INSERT INTO Topic VALUES(1);
139
140INSERT INTO Topic VALUES(2);
141
142INSERT INTO Topic VALUES(3);
143
144INSERT INTO Topic VALUES(4);
145
146
147###Comments
148
149CREATE TABLE Comments (
150
151        CommentID BIGINT UNSIGNED NOT NULL,
152	Username VARCHAR (20) NOT NULL,
153
154	TimeSet TIME NOT NULL,
155
156	Content VARCHAR (100) NOT NULL,
157
158	PRIMARY KEY (CommentID)
159
160	FOREIGN KEY (NewsArticleID) REFERENCES Comments (CommentID)
161);
162###Comments for commenter
163INSERT INTO Comments VALUES (1,'2evan2',CURTIME(),'great article!');
164INSERT INTO Comments VALUES (2,'blinkenlicten',CURTIME(),'very interesting read!');
165INSERT INTO Comments VALUES (3,'axepear',CURTIME(),'what a load of rubbish');
166INSERT INTO Comments VALUES (4,'cinnamon123',CURTIME(),'inspiring read');
167INSERT INTO Comments VALUES (5,'shojalace',CURTIME(),'wow, what a twist. I cant believe it');
168
169INSERT INTO Comments VALUES (6,'piero',CURTIME(),'didnt rate it');
170INSERT INTO Comments VALUES (7,'mac008',CURTIME(),'commenting is very fun');
171INSERT INTO Comments VALUES (8,'lee',CURTIME(),'yes m8');
172INSERT INTO Comments VALUES (9,'weirdybeardyman',CURTIME(),'whens the next article?');
173INSERT INTO Comments VALUES (10,'avantar',CURTIME(),'journalism at its finest');
174
175INSERT INTO Comments VALUES (11,'drax0',CURTIME(),'jag blir så irriterad just nu');
176INSERT INTO Comments VALUES (12,'melkor',CURTIME(),'mitt liev är en meme');
177INSERT INTO Comments VALUES (13,'axepear',CURTIME(),'jag kommer nu');
178INSERT INTO Comments VALUES (14,'09willisc',CURTIME(),'wie gehts');
179INSERT INTO Comments VALUES (15,'bayorn',CURTIME(),'die nacht ist is mir');
180
181
182
183###Likes
184
185CREATE TABLE Likes (
186
187	NewsArticleTitle VARCHAR (30),
188	LikeID VARCHAR (30),
189	TimeSet TIME NOT NULL,
190
191	Publish_date DATE NOT NULL,
192
193	PRIMARY KEY (LikeID)
194
195);
196
197INSERT INTO Likes VALUES ();
198INSERT INTO Likes VALUES ();
199INSERT INTO Likes VALUES ();
200INSERT INTO Likes VALUES ();
201INSERT INTO Likes VALUES ();
202INSERT INTO Likes VALUES ();
203INSERT INTO Likes VALUES ();
204INSERT INTO Likes VALUES ();
205INSERT INTO Likes VALUES ();
206INSERT INTO Likes VALUES ();
207INSERT INTO Likes VALUES ();
208INSERT INTO Likes VALUES ();
209INSERT INTO Likes VALUES ();
210INSERT INTO Likes VALUES ();
211INSERT INTO Likes VALUES ();
212
213
214
215#
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230## Query world_news_corp;
231
232###SELECT * FROM aTable;