LocalLibrary/LibraryDatabase.session.sql at main · IsaacAkin/LocalLibrary · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
CREATE TABLE User (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    date_of_birth DATE NOT NULL,
    role TEXT NOT NULL,
    password TEXT NOT NULL
);
--@block
INSERT INTO User (
        first_name,
        last_name,
        date_of_birth,
        role,
        password
    )
VALUES (
        "Arthur",
        "Nightingale",
        "2000-05-12",
        "Admin",
        "Bookw0rm"
    ),
    (
        "John",
        "Doe",
        "1990-01-01",
        "Customer",
        "password"
    ),
    (
        "Jane",
        "Doe",
        "1995-02-12",
        "Customer",
        "specificnumber"
    ),
    (
        "Alice",
        "Smith",
        "1985-03-14",
        "Customer",
        "something"
    ),
    (
        "Bob",
        "Smith",
        "1980-07-05",
        "Customer",
        "special"
    ),
    (
        "Charlie",
        "Brown",
        "1975-11-04",
        "Customer",
        "mybirthday"
    ),
    (
        "Daisy",
        "Johnson",
        "1970-12-09",
        "Customer",
        "bestfriend"
    );
--@block
SELECT *
FROM User;
--@block
CREATE TABLE Book (
    name TEXT NOT NULL,
    author TEXT NOT NULL,
    isbn INT PRIMARY KEY AUTO_INCREMENT,
    library_acquisition_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    copies INT NOT NULL
);
--@block
INSERT INTO Book (name, author, library_acquisition_date, copies)
VALUES (
        "The Great Gatsby",
        "F. Scott Fitzgerald",
        "1990-05-10",
        5
    ),
    (
        "To Kill a Mockingbird",
        "Harper Lee",
        "1992-07-11",
        3
    ),
    ("1984", "George Orwell", "1995-08-15", 4),
    (
        "Pride and Prejudice",
        "Jane Austen",
        "1998-09-20",
        2
    ),
    (
        "The Catcher in the Rye",
        "J.D. Salinger",
        "2000-10-25",
        6
    ),
    ("The Hobbit", "J.R.R. Tolkien", "2003-11-30", 7),
    ("Moby Dick", "Herman Melville", "2005-12-05", 1),
    ("War and Peace", "Leo Tolstoy", "2008-01-10", 3);
--@block
SELECT *
FROM Book;
--@block
CREATE TABLE Loan (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    book_isbn INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES User(id),
    FOREIGN KEY (book_isbn) REFERENCES Book(isbn)
);
--@block
SELECT *
FROM loan;