Skip to content
Newer
Older
100644 51 lines (43 sloc) 1.44 KB
2cd8297 @theory Add first rev of database schema.
authored
1 BEGIN;
2
3 CREATE TABLE books (
4 isbn ISBN13 PRIMARY KEY,
5 title TEXT NOT NULL DEFAULT '',
6 rating SMALLINT NOT NULL DEFAULT 0 CHECK (rating BETWEEN 0 AND 5)
7 );
8
9 CREATE TABLE authors (
10 id BIGSERIAL PRIMARY KEY,
11 surname TEXT NOT NULL DEFAULT '',
12 given_name TEXT NOT NULL DEFAULT ''
13 );
14
15 CREATE TABLE book_author (
16 isbn ISBN13 REFERENCES books(isbn),
17 author_id BIGINT REFERENCES authors(id),
18 PRIMARY KEY (isbn, author_id)
19 );
20
21 INSERT INTO books
22 VALUES ('1587201534', 'CCSP SNRS Exam Certification Guide', 5),
23 ('978-0201633467', 'TCP/IP Illustrated, Volume 1', 5),
24 ('978-0130183804', 'Internetworking with TCP/IP Vol.1', 4),
25 ('978-1-56592-243-3', 'Perl Cookbook', 5),
26 ('978-0735712010', 'Designing with Web Standards', 5)
27 ;
28
29 INSERT INTO authors (surname, given_name)
30 VALUES ('Bastien', 'Greg'),
31 ('Nasseh', 'Sara'),
32 ('Degu', 'Christian'),
33 ('Stevens', 'Richard'),
34 ('Comer', 'Douglas'),
35 ('Christiansen', 'Tom'),
36 ('Torkington', 'Nathan'),
37 ('Zeldman', 'Jeffrey')
38 ;
39
40 INSERT INTO book_author
41 VALUES ('1587201534', 1),
42 ('1587201534', 2),
43 ('1587201534', 3),
44 ('978-0201633467', 4),
45 ('978-0130183804', 5),
46 ('978-1-56592-243-3', 6),
47 ('978-1-56592-243-3', 7),
48 ('978-0735712010', 8);
49
50 COMMIT;
Something went wrong with that request. Please try again.