DROP TABLE Author;
DROP TABLE Book;
DROP TABLE Person;
DROP SEQUENCE personIDseq;

CREATE TABLE Book(
       title VARCHAR(70),
       publisher VARCHAR(20),
       year INT,
       isbn CHAR(13) NOT NULL, 
       CONSTRAINT pk_book PRIMARY KEY (isbn) 
);

CREATE TABLE Person(
       personID INT,
       firstname VARCHAR(20), 
       lastname VARCHAR(20),
       CONSTRAINT pk_person PRIMARY KEY (personID)
);

	
CREATE TABLE Author(
       isbn CHAR(13),
       personID INT,
       CONSTRAINT pk_author PRIMARY KEY (isbn, personID),
       CONSTRAINT fk_authBook FOREIGN KEY (isbn) REFERENCES 
Book,
       CONSTRAINT fk_authPerson FOREIGN KEY (personID) REFERENCES Person
);

CREATE SEQUENCE personIDseq;

-- Inserting tuples

-- into Book


INSERT INTO Book VALUES ('A First Course in Database Systems', 'Prentice-Hall', 1997, '0-13-861337-0');

INSERT INTO Book VALUES ('Introduction to Automata Theory, Languages and Computation', 'Addison-Wesley', 1979, '0-201-02988-X');

-- into Person

INSERT INTO Person VALUES (personIDseq.nextVal, 'Jeffrey', 'Ullman');
INSERT INTO Person VALUES (personIDseq.nextVal, 'Jennifer', 'Widom');
INSERT INTO Person VALUES (personIDseq.nextVal, 'John', 'Hopcroft');

-- into Author

INSERT INTO Author VALUES ('0-13-861337-0', (SELECT PersonID FROM Person WHERE LastName = 'Ullman'));

INSERT INTO Author VALUES ('0-13-861337-0', 
(SELECT PersonID FROM Person WHERE LastName = 'Widom'));

INSERT INTO Author VALUES ('0-201-02988-X', 
(SELECT PersonID FROM Person WHERE LastName = 'Hopcroft'));

INSERT INTO Author VALUES ('0-201-02988-X', 
(SELECT PersonID FROM Person WHERE LastName = 'Ullman'));


COLUMN title FORMAT A30;
COLUMN publisher FORMAT A15;
