DROP TABLE Borrow;
DROP TABLE Available;
DROP TABLE Book;
DROP TABLE Library;
DROP TABLE Student;

CREATE TABLE Student (
ID VarChar(15) Primary Key,
SName VarChar(30),
SCity VarChar(30)
);
CREATE TABLE Book (
ISBN VarChar(15) Primary Key,
Title VarChar (30) NOT NULL,
Author VarChar (30)
);
CREATE TABLE Library (
LName VarChar(30) Primary Key,
City VarChar(30) NOT NULL
);
CREATE Table Available (
ISBN VarChar(15),
LName VarChar(30),
noOfCopies Integer NOT NULL,
Primary Key (ISBN, LName),
Foreign Key (ISBN) References Book(ISBN),
Foreign Key (LName) References
Library(LName)
);
CREATE TABLE Borrow (
ID VarChar(15),
LName VarChar(30),
ISBN VarChar(15),
Primary Key (ID, LName, ISBN),
Foreign Key(ID) References Student(ID),
Foreign Key (ISBN ,LName) References Available(ISBN ,LName)
);
INSERT INTO Book (ISBN, Title, Author) VALUES ('1878351001', 'Filippo Lippi', 'Gloria
Fossi');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0521830842', 'Foundations of
Cryptography', 'Oded Goldreich');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0471751561', 'Linear Algebra', 'Peter
Lax');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0824774566', 'Abstract Algebra', 'Gary
Crown');
INSERT INTO Book (ISBN, Title, Author) VALUES ('052188473X', 'Computational
Complexity', 'Oded Goldreich');
INSERT INTO Book (ISBN, Title, Author) VALUES ('9889950316', 'Trivial Matters', 'Ho-
Cheung Pang');
INSERT INTO Book (ISBN, Title, Author) VALUES ('1596912839', 'The Brain', 'Sam
Wang');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0195145704', 'Biggest Bang',
'Jonathan Katz');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0000025127', 'Lust, Caution', 'Ailing
Zhang');
INSERT INTO Book (ISBN, Title, Author) VALUES ('1843834227', 'The Spy From the Coop',
'David Burke');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0521035368', 'Foundations of
Cryptography', 'Oded Goldreich');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0972169520', 'Piled Higher and
Deeper', 'Jorge Cham');
INSERT INTO Book (ISBN, Title, Author) VALUES ('174104779X', 'Chile and Easter
Island', 'Carolyn McCarthy');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0007162219', 'The Big Bang Theory',
'Simon Singh');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0312425074', 'The World is Flat',
'Thomas L. Friedman');
INSERT INTO Book (ISBN, Title, Author) VALUES ('9621747082', 'Monopoly', 'Hon Yuen
Ng');
INSERT INTO Book (ISBN, Title, Author) VALUES ('1571310487', 'Cracking India', 'Bapsi
Sidhwa');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0471556041', 'Functional Analysis',
'Peter Lax');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0452010926', 'Gay American History',
'Jonathan Katz');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0061234001', 'Freakonomics', 'Steven
D. Levitt');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0140367152', 'Journey to the Center',
'Jules Verne');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0071497838', 'Essential Phrasal
Verbs', 'Richard Spears');
INSERT INTO Book (ISBN, Title, Author) VALUES ('6666666666', 'Terrorism for Dummies',
'Bernard Cushing');
INSERT INTO Book (ISBN, Title, Author) VALUES ('1586039474', 'Certificateless
Cryptography', 'Sherman Chow');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0688146198', 'NLP', 'Steve Andreas');
INSERT INTO Book (ISBN, Title, Author) VALUES ('7806780769', 'Hong Kong', 'Mong
Fung Leung');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0060554738', 'Secret Society', 'Neil
Strauss');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0874834430', 'Piled Higher and
Deeper', 'Simon J. Bronner');
INSERT INTO Book (ISBN, Title, Author) VALUES ('1400067936', 'Randomness', 'Nassim
Nicholas Taleb');
INSERT INTO Book (ISBN, Title, Author) VALUES ('0802134297', 'Candy', 'Terry
Southern');
INSERT INTO Library (LName, City) VALUES ('Bobst', 'New York');
INSERT INTO Library (LName, City) VALUES ('Courant', 'New York');
INSERT INTO Library (LName, City) VALUES ('Cooper', 'New York');
INSERT INTO Library (LName, City) VALUES ('Luther', 'Sunnyvale');
INSERT INTO Library (LName, City) VALUES ('Chinese', 'San Jose');
INSERT INTO Library (LName, City) VALUES ('Engineering', 'New York');
INSERT INTO Library (LName, City) VALUES ('Microsoft', 'Remond');
INSERT INTO Library (LName, City) VALUES ('Central', 'Seattle');
INSERT INTO Library (LName, City) VALUES ('Aviation', 'Seattle');
INSERT INTO Library (LName, City) VALUES ('History', 'Dallas');
INSERT INTO Library (LName, City) VALUES ('Sherman', 'New York');
INSERT INTO Library (LName, City) VALUES ('Business', 'Denver');
INSERT INTO Library (LName, City) VALUES ('JFK', 'Newark');
INSERT INTO Library (LName, City) VALUES ('IBM', 'Zurich');
INSERT INTO Library (LName, City) VALUES ('Spy', 'DC');
INSERT INTO Library (LName, City) VALUES ('Main', 'Cambridge');
INSERT INTO Library (LName, City) VALUES ('Fine Art', 'Boston');
INSERT INTO Library (LName, City) VALUES ('Othmer', 'New York');
INSERT INTO Library (LName, City) VALUES ('Law', 'San Jose');
INSERT INTO Library (LName, City) VALUES ('Star', 'Hollywood');
INSERT INTO Student (ID, SName, SCity) VALUES ('655', 'Mark Lee', 'Boston');
INSERT INTO Student (ID, SName, SCity) VALUES ('099', 'John Lee', 'New York');
INSERT INTO Student (ID, SName, SCity) VALUES ('384', 'Amy Hill', 'Cambridge');
INSERT INTO Student (ID, SName, SCity) VALUES ('295', 'Jim Cook', 'San Jose');
INSERT INTO Student (ID, SName, SCity) VALUES ('820', 'Karl Marx', 'Sunnyvale');
INSERT INTO Student (ID, SName, SCity) VALUES ('341', 'Erin Bell', 'Newark');
INSERT INTO Student (ID, SName, SCity) VALUES ('937', 'Kyle Ward', 'San Diego');
INSERT INTO Student (ID, SName, SCity) VALUES ('995', 'Sara Cox', 'Chicago');
INSERT INTO Student (ID, SName, SCity) VALUES ('735', 'Mark Diaz', '');
INSERT INTO Student (ID, SName, SCity) VALUES ('710', 'Mary Wood', 'New Haven');
INSERT INTO Student (ID, SName, SCity) VALUES ('424', 'Lisa Gray', 'Hollywood');
INSERT INTO Student (ID, SName, SCity) VALUES ('314', 'Sean Cruz', 'Denver');
INSERT INTO Student (ID, SName, SCity) VALUES ('825', 'Paul Long', 'Las Vegas');
INSERT INTO Student (ID, SName, SCity) VALUES ('398', 'Eric Yoon', 'New York');
INSERT INTO Student (ID, SName, SCity) VALUES ('672', 'Tara Lee', 'Boston');
INSERT INTO Student (ID, SName, SCity) VALUES ('861', 'Sara Lee', 'Dallas');
INSERT INTO Student (ID, SName, SCity) VALUES ('040', 'Adam Smith', 'Boston');
INSERT INTO Student (ID, SName, SCity) VALUES ('865', 'Mark Hill', 'New York');
INSERT INTO Student (ID, SName, SCity) VALUES ('646', 'Jane Hall', 'New York');
INSERT INTO Student (ID, SName, SCity) VALUES ('267', 'Rob Gray', 'Cambridge');
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('1878351001', 'Fine Art',
1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0521830842', 'Courant',
1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0471751561', 'Courant',
0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0824774566', 'Cooper', 0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('052188473X', 'Microsoft',
0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('9889950316', 'Central', 2);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('1596912839', 'Sherman',
0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0195145704', 'Luther', 0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0000025127', 'Chinese',
3);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('1843834227', 'Spy', 1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0521035368', 'Sherman',
3);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0972169520', 'Star', 1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('174104779X', 'Star', 1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0007162219', 'Star', 0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0312425074', 'Star', 0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('9621747082', 'Luther', 0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('1571310487', 'Chinese',
2);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0471556041', 'IBM', 0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0452010926', 'History', 1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0061234001', 'Business',
0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0140367152', 'Main', 2);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0071497838', 'Othmer', 3);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('6666666666', 'Spy', 1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('1586039474', 'Courant',
0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0688146198', 'JFK', 2);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('7806780769', 'Bobst', 2);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0060554738', 'Aviation',
3);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0874834430', 'Law', 0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('1400067936', 'Law', 1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0802134297', 'History', 0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0521830842', 'Bobst', 2);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0471751561', 'Cooper', 2);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0824774566', 'Courant',
0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('052188473X', 'Main', 1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('1586039474', 'Central', 0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0312425074', 'IBM', 1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('9621747082', 'Cooper', 1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('9889950316', 'History', 0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('1596912839', 'Law', 0);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0195145704', 'Law', 1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0000025127', 'Aviation',
1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('1843834227', 'JFK', 4);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0972169520', 'Spy', 1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('174104779X', 'Othmer',
3);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0007162219', 'Main', 1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0312425074', 'Business',
1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('9621747082', 'History', 4);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('1571310487', 'Star', 3);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0471556041', 'Chinese',
1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0452010926', 'Central', 3);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0061234001', 'Bobst', 3);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0140367152', 'Cooper', 2);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0071497838', 'Courant',
3);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('6666666666', 'Main', 1);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0688146198', 'Microsoft',
2);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0060554738', 'Microsoft',
4);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0874834430', 'Fine Art',
2);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('1400067936', 'Fine Art',
3);
INSERT INTO Available (ISBN, LName, noOfCopies) VALUES ('0802134297', 'Fine Art',
4);
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('865', '0802134297', 'History');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('295', '0312425074', 'Star');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('865', '1586039474', 'Central');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('825', '9621747082', 'Cooper');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('295', '1586039474', 'Courant');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('672', '9889950316', 'History');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('861', '9621747082', 'Luther');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('735', '0471556041', 'IBM');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('646', '0061234001', 'Business');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('040', '0824774566', 'Courant');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('646', '0874834430', 'Law');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('710', '0521830842', 'Bobst');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('424', '0802134297', 'History');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('424', '9889950316', 'Central');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('655', '1596912839', 'Sherman');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('861', '9889950316', 'History');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('861', '0824774566', 'Courant');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('099', '0140367152', 'Main');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('937', '0874834430', 'Law');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('424', '0521830842', 'Courant');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('995', '0521830842', 'Courant');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('655', '0195145704', 'Luther');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('384', '0000025127', 'Chinese');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('655', '0007162219', 'Star');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('424', '1596912839', 'Law');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('295', '9621747082', 'Cooper');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('099', '1596912839', 'Law');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('820', '0471751561', 'Courant');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('295', '0824774566', 'Cooper');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('099', '9889950316', 'Central');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('295', '1596912839', 'Law');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('937', '0824774566', 'Cooper');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('820', '0312425074', 'Star');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('820', '7806780769', 'Bobst');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('735', '1596912839', 'Law');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('710', '9889950316', 'History');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('398', '0521830842', 'Bobst');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('825', '0471751561', 'Cooper');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('314', '0802134297', 'History');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('672', '0824774566', 'Courant');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('099', '0824774566', 'Cooper');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('099', '052188473X', 'Microsoft');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('825', '0802134297', 'History');
INSERT INTO Borrow (ID, ISBN, Lname) VALUES ('995', '0471556041', 'IBM');