5cssAb3g

1
2CREATE DATABASE IF NOT EXISTS cce_mag;
3
4USE cce_mag;
5
6DROP TABLE IF EXISTS article_authors;
7DROP TABLE IF EXISTS article_keywords;
8DROP TABLE IF EXISTS authors;
9DROP TABLE IF EXISTS keywords;
10DROP TABLE IF EXISTS editions;
11DROP TABLE IF EXISTS articles;
12
13
14CREATE TABLE authors(
15	id 		INT AUTO_INCREMENT PRIMARY KEY,
16	f_name 	VARCHAR(50) NOT NULL,
17    l_name 	VARCHAR(50) NOT NULL
18);
19
20
21CREATE TABLE keywords(
22	id 	 INT AUTO_INCREMENT PRIMARY KEY,
23    word VARCHAR(150)
24);
25
26
27# |abstract| is NULL by default, because there are some articles with no summaries
28CREATE TABLE articles(
29	id 			INT AUTO_INCREMENT PRIMARY KEY,
30	title 		VARCHAR(255) NOT NULL,
31    abstract 	TEXT DEFAULT NULL,
32    num			VARCHAR(2083)
33);
34
35
36CREATE TABLE editions(
37	id 			INT AUTO_INCREMENT PRIMARY KEY,
38	year 		INT NOT NULL,
39    number 		INT NOT NULL,
40    article_id 	INT NOT NULL,
41    FOREIGN KEY(article_id) REFERENCES articles(id)
42);
43
44
45# Many-To-Many Tables
46CREATE TABLE article_authors(
47	id 			INT AUTO_INCREMENT PRIMARY KEY,
48	article_id 	INT NOT NULL,
49    author_id 	INT NOT NULL,
50    FOREIGN KEY(article_id) REFERENCES articles(id),
51    FOREIGN KEY(author_id) REFERENCES authors(id)
52);
53
54
55CREATE TABLE article_keywords(
56	id 			INT AUTO_INCREMENT PRIMARY KEY,
57    article_id 	INT NOT NULL,
58    keyword_id 	INT NOT NULL,
59    FOREIGN KEY(article_id) REFERENCES articles(id),
60    FOREIGN KEY(keyword_id) REFERENCES keywords(id)
61);