· 8 years ago · Jul 14, 2018, 08:38 AM
1/* Should i add duplication prevention checks ? */
2DROP TABLE IF EXISTS author;
3DROP TABLE IF EXISTS publisher;
4DROP TABLE IF EXISTS book;
5DROP TABLE IF EXISTS chapter;
6DROP TABLE IF EXISTS wrote_book;
7DROP TABLE IF EXISTS wrote_chapter;
8
9/* define variables to re-use for string sizes */
10SET @NAME_LEN=30, @MAIL_LEN=60, @ADDR_LEN=150, @PHONE_LEN=15, @ISBN_LEN=13, @DESC_LEN=@ADDR_LEN
11/* For the ISBN, it is 13 digits long if assigned on or after 1 January 2007; acknowledgment : https://en.wikipedia.org/wiki/International_Standard_Book_Number */
12
13CREATE TABLE author (
14 /*since we only have 1 PK can i set it like this and not with CONSTRAINT ? */
15 author_id INTEGER PRIMARY KEY,
16 first_name VARCHAR(@NAME_LEN) NOT NULL,
17 middle_name VARCHAR(@NAME_LEN) NOT NULL,
18 last_name VARCHAR(@NAME_LEN) NOT NULL,
19 email VARCHAR(@MAIL_LEN) UNIQUE NOT NULL,
20 /* phone stored as string, acknowledgement : https://stackoverflow.com/a/24353813 */
21 phone VARCHAR(@PHONE_LEN) UNIQUE NOT NULL,
22 address VARCHAR(@ADDR_LEN) NOT NULL
23);
24
25CREATE TABLE publisher (
26 publisher_id INTEGER PRIMARY KEY,
27 name VARCHAR(@NAME_LEN) NOT NULL,
28 email VARCHAR(@MAIL_LEN) UNIQUE NOT NULL,
29 phone INTEGER UNIQUE NOT NULL,
30 address VARCHAR(@ADDR_LEN) NOT NULL
31);
32
33CREATE TABLE book (
34 book_id INTEGER PRIMARY KEY,
35 isbn VARCHAR(@ISBN_LEN) UNIQUE NOT NULL,
36 title VARCHAR(@NAME_LEN) NOT NULL,
37 publisher INTEGER NOT NULL,
38 pub_date DATE NOT NULL,
39 edition INTEGER,
40
41 CONSTRAINT fk_publisher
42 FOREIGN KEY (publisher)
43 REFERENCES publisher(publisher_id)
44);
45
46CREATE TABLE chapter (
47 chapter_number INTEGER UNIQUE NOT NULL,
48 book INTEGER,
49 title VARCHAR(@NAME_LEN),
50 contents VARCHAR(@DESC_LEN),
51
52 CONSTRAINT pk_chapter
53 PRIMARY KEY (chapter_number, book),
54
55 CONSTRAINT fk_book
56 FOREIGN KEY (book)
57 REFERENCES book(book_id)
58);
59
60CREATE TABLE wrote_book (
61 author INTEGER,
62 book INTEGER,
63
64 CONSTRAINT pk_wrote_book
65 PRIMARY KEY (author, book)
66);
67
68CREATE TABLE wrote_chapter (
69 author INTEGER,
70 chapter INTEGER,
71 book INTEGER,
72
73 CONSTRAINT pk_wrote_chapter
74 PRIMARY KEY (author, chapter, book),
75
76 CONSTRAINT fk_author
77 FOREIGN KEY (author)
78 REFERENCES author(author_id),
79
80 CONSTRAINT fk_chapter
81 FOREIGN KEY (chapter)
82 REFERENCES chapter(chapter_number),
83
84 CONSTRAINT fk_book
85 FOREIGN KEY (book)
86 REFERENCES chapter(book)
87);
88
89/* I read in the docs (v5.1) that foreign keys can be done differently, hence can i do the 'wrote_chapter' table as such instead ? :
90
91CREATE TABLE wrote_chapter (
92 author INTEGER REFERENCES author(author_id),
93 chapter INTEGER REFERENCES chapter(chapter_number),
94 book INTEGER REFERENCES chapter(book),
95
96 CONSTRAINT pk_wrote_chapter
97 PRIMARY KEY (author, chapter, book),
98);