fm0FMUFD

1BEGIN;
2
3CREATE TABLE IF NOT EXISTS publisher (
4    name VARCHAR(60) UNIQUE NOT NULL,
5    website VARCHAR (128),
6    email VARCHAR(128),
7    phone VARCHAR(20)
8);
9
10CREATE TABLE IF NOT EXISTS book (
11    isbn CHAR(13) UNIQUE NOT NULL,
12    title VARCHAR(60) UNIQUE NOT NULL,
13    publisher_id INT NOT NULL,
14    order_code VARCHAR(60),
15    edition INT NOT NULL,
16    media_attached BOOL NOT NULL,
17    info_url VARCHAR(512),
18    school_owned BOOL NOT NULL,
19    FOREIGN KEY(publisher_id) REFERENCES publisher(rowid) ON DELETE RESTRICT ON UPDATE CASCADE
20);
21
22CREATE TABLE IF NOT EXISTS author (
23    name VARCHAR(60) UNIQUE NOT NULL,
24    email VARCHAR(128)
25);
26
27CREATE TABLE IF NOT EXISTS book_author (
28    book_id INT NOT NULL,
29    author_id INT NOT NULL,
30    FOREIGN KEY(book_id) REFERENCES book(rowid) ON UPDATE CASCADE,
31    FOREIGN KEY(author_id) REFERENCES author(rowid) ON DELETE RESTRICT ON UPDATE CASCADE
32);
33
34CREATE TABLE IF NOT EXISTS field (
35    name VARCHAR(30) UNIQUE NOT NULL
36);
37
38CREATE TABLE IF NOT EXISTS book_field (
39    book_id INT NOT NULL,
40    field_id INT NOT NULL,
41    FOREIGN KEY(book_id) REFERENCES book(rowid) ON UPDATE CASCADE,
42    FOREIGN KEY(field_id) REFERENCES field(rowid) ON DELETE RESTRICT ON UPDATE CASCADE
43);
44
45CREATE TABLE IF NOT EXISTS class (
46    name VARCHAR(8) UNIQUE NOT NULL
47);
48
49CREATE TABLE IF NOT EXISTS user (
50    name VARCHAR(60) UNIQUE NOT NULL,
51    email VARCHAR (128) NOT NULL,
52    is_admin BOOL NOT NULL,
53    class_id INT,
54    FOREIGN KEY (class_id) REFERENCES class(rowid) ON DELETE RESTRICT ON UPDATE CASCADE
55);
56
57CREATE TABLE IF NOT EXISTS room (
58    name VARCHAR(30) UNIQUE NOT NULL
59);
60
61CREATE TABLE IF NOT EXISTS location_type (
62    name VARCHAR(30) UNIQUE NOT NULL
63);
64
65CREATE TABLE IF NOT EXISTS location (
66    room_id INT NOT NULL,
67    location_type_id INT NOT NULL,
68    FOREIGN KEY (room_id) REFERENCES room(rowid) ON DELETE RESTRICT ON UPDATE CASCADE,
69    FOREIGN KEY (location_type_id) REFERENCES location_type(rowid) ON DELETE RESTRICT ON UPDATE CASCADE
70);
71
72CREATE TABLE IF NOT EXISTS book_location (
73    book_id INT NOT NULL,
74    location_id INT NOT NULL,
75    amount INT,
76    FOREIGN KEY (book_id) REFERENCES book(rowid) ON UPDATE CASCADE,
77    FOREIGN KEY (location_id) REFERENCES location(rowid) ON DELETE RESTRICT ON UPDATE CASCADE
78);
79
80CREATE TABLE IF NOT EXISTS rental (
81    user_id INT NOT NULL,
82    book_id INT NOT NULL,
83    location_id INT NOT NULL,
84    rental_date CHAR(10) NOT NULL DEFAULT CURRENT_DATE,
85    return_date CHAR(10),
86    FOREIGN KEY(user_id) REFERENCES user(rowid) ON DELETE RESTRICT ON UPDATE CASCADE,
87    FOREIGN KEY(book_id) REFERENCES book(rowid) ON UPDATE CASCADE,
88    FOREIGN KEY(location_id) REFERENCES location(rowid) ON DELETE RESTRICT ON UPDATE CASCADE
89);
90
91CREATE TABLE IF NOT EXISTS archived_book (
92    isbn CHAR(13) UNIQUE NOT NULL,
93    title VARCHAR(60) UNIQUE NOT NULL,
94    publisher_id INT NOT NULL,
95    order_code VARCHAR(60),
96    edition INT NOT NULL,
97    media_attached BOOL NOT NULL,
98    info_url VARCHAR(512),
99    school_owned BOOL NOT NULL,
100    FOREIGN KEY(publisher_id) REFERENCES publisher(rowid) ON DELETE RESTRICT ON UPDATE CASCADE
101);
102
103COMMIT;