JDBC-Project/SQL database at master · Stvduong/JDBC-Project · 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
CREATE TABLE WritingGroup(
    groupname   VARCHAR(20) NOT NULL,
    headwriter  VARCHAR(20) NOT NULL,
    yearformed  VARCHAR(4)  NOT NULL,
    subject     VARCHAR(20) NOT NULL,
    CONSTRAINT pk_writinggroup PRIMARY KEY (groupname)
    );

CREATE TABLE book (
    booktitle   VARCHAR(50) NOT NULL,
    yearpublished   VARCHAR(4)  NOT NULL,
    numberpages int         NOT NULL,
    groupname   VARCHAR(20) NOT NULL,
    publisherName   VARCHAR(20) NOT NULL,
    CONSTRAINT pk_book  PRIMARY KEY (groupname, booktitle)
);

CREATE TABLE publisher(
    publishername   VARCHAR(20) NOT NULL,
    publisheraddress    VARCHAR(20) NOT NULL,
    publisherphone  VARCHAR(15) NOT NULL,
    publisheremail  VARCHAR(20) NOT NULL,
    CONSTRAINT pk_publisher PRIMARY KEY (publishername)
);
--FK1
ALTER TABLE book
    ADD CONSTRAINT writinggroup_book_fk01
    FOREIGN KEY (groupname)
    REFERENCES writinggroup (groupname);
--FK2
ALTER TABLE book
    ADD CONSTRAINT publishers_book_fk
    FOREIGN KEY (publishername)
    REFERENCES publisher (publishername);
--Expanding fields
ALTER TABLE publisher
    ALTER COLUMN publisheraddress SET DATA TYPE VARCHAR(100);

ALTER TABLE publisher
    ALTER COLUMN publisheremail SET DATA TYPE VARCHAR(100);

--Adding writinggroups
INSERT INTO writinggroup (groupname, headwriter, yearformed, subject)
    VALUES('Space Club', 'Stephen Hawkin', '1994', 'Science');

INSERT INTO writinggroup (groupname, headwriter, yearformed, subject)
    VALUES('Fantasy Land', 'J. R. R. Tolkien', '1950', 'Fantasy');

INSERT INTO writinggroup (groupname, headwriter, yearformed, subject)
    VALUES('Fiction Junction', 'Stephen Kin', '1989', 'Fiction');

INSERT INTO writinggroup (groupname, headwriter, yearformed, subject)
    VALUES('Time Travelers', 'Mark Twain', '1900', 'History');

--Publishers

INSERT INTO publisher(publishername, publisheraddress, publisherphone, publisheremail)
    VALUES('Penguin Random House', '1745 Broadway, New York, NY 10019', '212-423-5827', 'inquiry@penguinrandomhouse.com');

INSERT INTO publisher(publishername, publisheraddress, publisherphone, publisheremail)
    VALUES('Simon & Schuster', '1230 Avenue of the Americas, New York, NY 10017', '845-470-3254', 'contact@simonschuster.com');

INSERT INTO publisher(publishername, publisheraddress, publisherphone, publisheremail)
    VALUES('HarperCollins', '195 Broadway, New York, NY 10021', '217-861-4801', 'Contact@harpercollins.com');

INSERT INTO publisher(publishername, publisheraddress, publisherphone, publisheremail)
    VALUES('Hachette Livre', '43 quai de Grenelle, 75015 Paris, France', '33-5097-58351', 'support@penguinrandomhouse.com');

--Books
INSERT INTO book(booktitle, yearpublished,numberpages,groupname,publishername)
    VALUES('1984', '1961', 328, 'Fiction Junction', 'Penguin Random House');

INSERT INTO book(booktitle, yearpublished,numberpages,groupname,publishername)
    VALUES('A Brief History of Time', '1998', 212, 'Space Club', 'Simon & Schuster');

INSERT INTO book(booktitle, yearpublished,numberpages,groupname,publishername)
    VALUES('A Heartbreaking Work of Staggering Genius', '2001', 437, 'Time Travelers', 'HarperCollins');

INSERT INTO book(booktitle, yearpublished,numberpages,groupname,publishername)
    VALUES('Guns, Germs, and Steel', '2005', 480, 'Time Travelers', 'Hachette Livre');

SELECT * from book;