yLiD8zdT

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);
39
40
41###NewsArticle
42
43CREATE TABLE NewsArticle (
44
45	NewsArticleID BIGINT,
46	Topic VARCHAR (20) NOT NULL,
47	Title VARCHAR (50) NOT NULL,
48
49	PublishDate DATE NOT NULL,
50
51	Content VARCHAR (250) NOT NULL,
52
53	Country VARCHAR (20) NOT NULL,
54
55	NI BIGINT NOT NULL,
56	PRIMARY KEY (NewsArticleID)
57);
58
59###Comments
60
61CREATE TABLE Comments (
62
63        CommentID BIGINT UNSIGNED NOT NULL,
64	NewsArticleID BIGINT,
65	Username VARCHAR (20) NOT NULL,
66
67	TimeSet TIME NOT NULL,
68
69	Content VARCHAR (100) NOT NULL,
70	PRIMARY KEY (CommentID)
71);
72
73
74###Likes
75
76CREATE TABLE Likes (
77
78	LikeID INT,
79	NewsArticleID BIGINT,
80	TimeSet TIME NOT NULL,
81
82	Publish_date DATE NOT NULL,
83
84	PRIMARY KEY (LikeID)
85
86);
87###insert data
88
89### Author values from spreadsheet
90
91INSERT INTO Author VALUES (1987234920, 'John','Smith','M',34,20000,'England');
92
93INSERT INTO Author VALUES (9348098234, 'Tom','Potter','M',32,32000,'Scotland');
94
95INSERT INTO Author VALUES (4564547547, 'Jack','Kingman','M',23,14000,'India');
96
97INSERT INTO Author VALUES (5654674575, 'Chris','Trethick','M',45,24000,'India');
98
99INSERT INTO Author VALUES (5675675675, 'Dave','Carter','M',56,40000,'Ireland');
100
101INSERT INTO Author VALUES (7775656765, 'Ian','Packwood','M',33,19000,'England');
102
103INSERT INTO Author VALUES (8964334324, 'Lucy','Teague','F',18,20000,'America');
104
105INSERT INTO Author VALUES (6612355667, 'Jane','Connor','F',19,49000,'France');
106
107INSERT INTO Author VALUES (3456456457, 'Mary','Van Schmidt','F',42,32000,'France');
108
109INSERT INTO Author VALUES (1875432325, 'Liz','Fogarty','F',53,34000,'America');
110
111INSERT INTO Author VALUES (1134576877, 'Emma','Baker','F',37,20000,'England');
112
113INSERT INTO Author VALUES (2345457654, 'Anne','Nuttley','F',43,34000,'Indonesia');
114
115
116
117### NewsArticle values from spreadsheet
118INSERT INTO NewsArticle VALUES (1,'Politics','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',1987234920);
119
120INSERT INTO NewsArticle VALUES (2,'Politics','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',9348098234);
121
122INSERT INTO NewsArticle VALUES (3,'Politics','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',5675675675);
123
124INSERT INTO NewsArticle VALUES (4,'Financial','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',7775656765);
125
126INSERT INTO NewsArticle VALUES (5,'Financial','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',1134576877);
127
128INSERT INTO NewsArticle VALUES (6,'Entertainment','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',1987234920);
129
130INSERT INTO NewsArticle VALUES (7,'Financial','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',1134576877);
131
132INSERT INTO NewsArticle VALUES (8,'Politics','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',1134576877);
133
134INSERT INTO NewsArticle VALUES (9,'Science','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',3456456457);
135
136INSERT INTO NewsArticle VALUES (10,'Science','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',2345457654);
137
138###Comments for commenter
139INSERT INTO Comments VALUES (1,1,'2evan2',CURTIME(),'great article!');
140INSERT INTO Comments VALUES (2,1,'blinkenlicten',CURTIME(),'very interesting read!');
141INSERT INTO Comments VALUES (3,3,'axepear',CURTIME(),'what a load of rubbish');
142INSERT INTO Comments VALUES (4,4,'cinnamon123',CURTIME(),'inspiring read');
143INSERT INTO Comments VALUES (5,5,'shojalace',CURTIME(),'wow, what a twist. I cant believe it');
144
145INSERT INTO Comments VALUES (6,7,'piero',CURTIME(),'didnt rate it');
146INSERT INTO Comments VALUES (7,7,'mac008',CURTIME(),'commenting is very fun');
147INSERT INTO Comments VALUES (8,10,'lee',CURTIME(),'tá, mo chara');
148INSERT INTO Comments VALUES (9,9,'weirdybeardyman',CURTIME(),'whens the next article?');
149INSERT INTO Comments VALUES (10,10,'avantar',CURTIME(),'journalism at its finest');
150
151INSERT INTO Comments VALUES (11,3,'drax0',CURTIME(),'jag blir så irriterad just nu');
152INSERT INTO Comments VALUES (12,2,'melkor',CURTIME(),'mitt liev är en meme');
153INSERT INTO Comments VALUES (13,5,'axepear',CURTIME(),'jag kommer nu');
154INSERT INTO Comments VALUES (14,6,'09willisc',CURTIME(),'wie gehts');
155INSERT INTO Comments VALUES (15,1,'bayorn',CURTIME(),'die nacht ist is mir');
156
157
158###likes
159INSERT INTO Likes VALUES (1,1,NOW(),CURDATE());
160INSERT INTO Likes VALUES (2,2,NOW(),CURDATE());
161INSERT INTO Likes VALUES (3,3,NOW(),CURDATE());
162INSERT INTO Likes VALUES (4,4,NOW(),CURDATE());
163INSERT INTO Likes VALUES (5,5,NOW(),CURDATE());
164INSERT INTO Likes VALUES (6,6,NOW(),CURDATE());
165INSERT INTO Likes VALUES (7,7,NOW(),CURDATE());
166INSERT INTO Likes VALUES (8,8,NOW(),CURDATE());
167INSERT INTO Likes VALUES (9,9,NOW(),CURDATE());
168INSERT INTO Likes VALUES (10,9,NOW(),CURDATE());
169INSERT INTO Likes VALUES (11,8,NOW(),CURDATE());
170INSERT INTO Likes VALUES (12,7,NOW(),CURDATE());
171INSERT INTO Likes VALUES (13,6,NOW(),CURDATE());
172INSERT INTO Likes VALUES (14,6,NOW(),CURDATE());
173INSERT INTO Likes VALUES (15,4,NOW(),CURDATE());
174
175
176
177#
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192## Query world_news_corp;
193
194SELECT * FROM Author;
195
196SELECT * FROM NewsArticle;
197
198SELECT * FROM Comments;
199
200SELECT * FROM Likes;