IF DB_ID('LibraryDatabase') IS NOT NULL
	DROP DATABASE LibraryDatabase

CREATE DATABASE LibraryDatabase
GO

USE [LibraryDatabase]

GO
/****** Object:  Table [dbo].[Customers]    Script Date: 1/31/2015 1:48:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Publisher](
	[PublisherName] [varchar](30) NOT NULL,
	[PublisherAddress] [varchar](50) NOT NULL,
	[PublisherPhone] [varchar](14) NOT NULL,
CONSTRAINT [PK_Publishers] PRIMARY KEY CLUSTERED
(
	[PublisherName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Librarian]    Script Date: 1/31/2015 1:48:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Librarian](
	[LibrarianID] [char](10) NOT NULL,
	[LibrarianName] [varchar](25) NOT NULL,
	[LibrarianHireDate] [date] Default (getdate()) NOT NULL,
	[LibrarianSalary] [int] DEFAULT 0 NOT NULL,
CONSTRAINT [PK_Librarian] PRIMARY KEY CLUSTERED
(
	[LibrarianID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Users]    Script Date: 1/31/2015 1:48:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
	[UserID] [char](10) NOT NULL,
	[UserName] [varchar](25) NULL,
	[UserAge] [char](3) NULL,
	[UserJoinDate] [date] Default (getdate()) NOT NULL,
	[LateFees] [decimal](19, 2) DEFAULT 0.00 NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
	[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Book]    Script Date: 1/31/2015 1:48:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Book](
	[Barcode] [char](15) NOT NULL,
	[Title] [varchar](250) NULL,
	[Author] [varchar](25) NULL,
	[DDC] [varchar](15) NOT NULL,
	[Genre] [varchar](20) NULL,
	[CoverType] [varchar](15) NULL,
	[Condition] [varchar](15) DEFAULT 'Excellent',
	[ISBN] [varchar](20) NOT NULL,
	[Summary] [varchar](4000) NULL,
	[PublisherName] [varchar](30) NOT NULL,
	[PublisherDate] [date] NULL,
	[Value] DECIMAL(7,2) DEFAULT 0.00,
CONSTRAINT [PK_Incidents] PRIMARY KEY CLUSTERED
(
	[Barcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
alter table book
ADD CONSTRAINT [FK_Book] FOREIGN KEY (PublisherName)
	REFERENCES Publisher (PublisherName)

GO
/****** Object:  Table [dbo].[Hold]    Script Date: 1/31/2015 1:48:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hold](
   [HoldID] [char](10) NOT NULL,
   [Barcode] [char](15) NOT NULL,
   [UserID] [char](10) NOT NULL,
CONSTRAINT [PK_Hold] PRIMARY KEY CLUSTERED
(
	[holdID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
alter table Hold
ADD CONSTRAINT [FK_Hold1] FOREIGN KEY (Barcode)
	REFERENCES Book (Barcode)
alter table Hold
ADD CONSTRAINT [FK_Hold2] FOREIGN KEY (UserID)
	REFERENCES Users (UserID)

GO
/****** Object:  Table [dbo].[Checkout]    Script Date: 1/31/2015 1:48:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Checkout](
   [CheckoutID] [char](10) NOT NULL,
   [Barcode] [char](15) NOT NULL,
   [UserID] [char](10) NOT NULL,
   [CheckoutDate] [date] Default (getdate()) NOT NULL,
   [ReturnDate] [date] NULL,
   [LibrarianID] [char](10) NOT NULL,
CONSTRAINT [PK_Checkout] PRIMARY KEY CLUSTERED
(
	[CheckoutID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
alter table Checkout
ADD CONSTRAINT [FK_Checkout1] FOREIGN KEY (LibrarianID)
	REFERENCES Librarian (LibrarianID)
alter table Checkout
ADD CONSTRAINT [FK_Checkout2] FOREIGN KEY (Barcode)
	REFERENCES Book (Barcode)
alter table Checkout
ADD CONSTRAINT [FK_Checkout3] FOREIGN KEY (UserID)
	REFERENCES Users (UserID)

INSERT INTO Publisher VALUES(
'Bruce Wayne', '1007 Mountain Drive Gotham, DC', '349-431-3201'
);
INSERT INTO Publisher VALUES(
'Clark Kent', '344 Clinton Street Krypton, DC', '231-321-5432'
);
INSERT INTO Publisher VALUES(
'Diana Prince', '1234 Space Street Themyscira DC', '341-302-4032'
);
INSERT INTO Publisher VALUES(
'Selina Kyle', '9000 Meow Ave Gotham DC', '548-302-4032'
);
INSERT INTO Publisher VALUES(
'Arthur Curry', '2525 Ocean Street Atlantis DC', '654-741-5285'
);
INSERT INTO Publisher VALUES(
'Stephen King', '1053 Atlantic Ave BC', '778-911-8594'
);
INSERT INTO Publisher VALUES(
'J.K. Rowling', '5492 Hogwarts Street CA', '123-543-7684'
);
INSERT INTO Publisher VALUES(
'Stephen Curry', '4123 Lakers Street CH', '435-123-3455'
);
INSERT INTO Publisher VALUES(
'Phoenix Wright', '9106 Objection Ave JP', '591-203-991'
);
INSERT INTO Publisher VALUES(
'Mona Pizza', '1234 Eiffel Crescent FR', '532-234-7265'
);
INSERT INTO Publisher VALUES(
'Rick Grimes', '2415 Prison Street US', '115-251-6774'
);
INSERT INTO Publisher VALUES(
'Michael Chung', '1563 Lodgepole Place CA', '778-239-3088'
);
INSERT INTO Publisher VALUES(
'Anderson Phan', '1234 BCIT Street CA', '125-452-5794'
);


INSERT INTO Librarian VALUES(
'L09', 'Emma Watson', '01/28/2005', '80000'
);
INSERT INTO Librarian VALUES(
'L42', 'Daniel Radcliffe', '09/21/2011', '65000'
);
INSERT INTO Librarian VALUES(
'L12', 'Rupert Grint', '03/24/2007', '73000'
);
INSERT INTO Librarian VALUES(
'L28', 'Alan Rickman', '05/02/2009', '67000'
);
INSERT INTO Librarian VALUES(
'L69', 'Tom Felton', '12/25/2005', '90001'
);
INSERT INTO Librarian VALUES(
'L58', 'Spider Man', '05/12/2004', '55000'
);
INSERT INTO Librarian VALUES(
'L87', 'Bat Man', '08/10/2002', '67000'
);
INSERT INTO Librarian VALUES(
'L39', 'Aqua Man', '09/19/2001', '92000'
);
INSERT INTO Librarian VALUES(
'L28', 'He Man', '11/15/2011', '22000'
);
INSERT INTO Librarian VALUES(
'L96', 'Michael Phelps', '03/03/2008', '82000'
);
INSERT INTO Librarian VALUES(
'L48', 'George Miyazaki', '09/19/2007', '19000'
);
INSERT INTO Librarian VALUES(
'L15', 'Lorie Kojima', '11/03/2001', '10000'
);
INSERT INTO Librarian VALUES(
'L15', 'Dylan James', '07/17/2004', '41000'
);




INSERT INTO Users VALUES(
'U3940', 'Tony stark', '38', '09/02/2005', '90.01'
);
INSERT INTO Users VALUES(
'U213', 'Steve Rogers', '120', '03/28/2014', '0.00'
);
INSERT INTO Users VALUES(
'U2456', 'Bruce Banner', '42', '06/17/2012', '3.45'
);
INSERT INTO Users VALUES(
'U9034', 'Stephen Strange', '29', '04/20/2010', '4.20'
);
INSERT INTO Users VALUES(
'U39', 'Carol Danvers', '21', '01/01/2007', '0.50'
);
INSERT INTO Users VALUES(
'U294', 'Carl Junior', '81', '02/02/2002', '3.50'
);
INSERT INTO Users VALUES(
'U28', 'Bruce Li', '35', '06/07/2001', '6.60'
);
INSERT INTO Users VALUES(
'U152', 'Chuck Nores', '24', '08/02/2006', '9.00'
);
INSERT INTO Users VALUES(
'U19', 'Kobe Burlake', '32', '06/10/2012', '6.14'
);
INSERT INTO Users VALUES(
'U18', 'Yoshi Yamaguchi', '12', '03/21/2011', '4.50'
);
INSERT INTO Users VALUES(
'U32', 'Luigi Fettucini', '21', '09/23/2004', '0.00'
);
INSERT INTO Users VALUES(
'U4313', 'Mario Broderos', '17', '11/22/2014', '9.12'
);
INSERT INTO Users VALUES(
'U123', 'Bowser Stallone', '11', '08/22/2005', '8.45'
);



INSERT INTO Book VALUES(
'B9408', 'Inception: Subquery Within a Subquery', 'Luke Skywalker', '52043.34248',

'Fantasy', 'Paperback', 'Rekted', '978-3-16-148410-0', 'Database management 10th edition',

'Bruce Wayne', '01/23/2013', '49.95'
);
INSERT INTO book VALUES(
'B44', 'War: Star Trek', 'Obi-Wan', '21389.4323', 'Comedy', 'Hardcover', 'Torn', '978-6

-09-349833-0', 'Ha haha get the title? Star wars and star trek hue hue please don’t

flame', 'Clark Kent', '09/23/2009', '69.99'
);
INSERT INTO book VALUES(
'B234', 'Force: The Awakening', 'Yoda', '12312.45389', 'Science Fiction', 'Softcover',

'Alright', '978-9-12-694823-0', 'Luke stumbles across his long lost father and awakens the

force within him', 'Diana Prince', '12/21/2014', '29.95'
);
INSERT INTO book VALUES(
'B898', 'Jedi: The Returning Wars', 'Anakin Skywalker', '34280.54375', 'Romance',

'Hardcover', 'Pages Missing', '978-3-21-298311-0', 'After going on a journey, Ash Ketchum

finally returns to Pallet Town and marries Misty.', 'Selina Kyle', '02/14/2005', '35.00'
);
INSERT INTO book VALUES(
'B12093', 'Cloning Wars', 'Mace Windu', '43286.43249', 'Science Fiction', 'Ebook',

'Excellent', '978-0-13-934893-0', 'Harry Potter may think he actually killed Lord

Voldemort but truth is, Voldemort made plenty of clones of himself scattered throughout

the wizardry world.', 'Arthur Curry', '12/25/2008', '0.01'
);
INSERT INTO book VALUES(
'B1328', 'It', 'Stephen King', '93247.83249', 'Horror', 'Ebook',

'Excellent', '234-2-49-123890-0', 'Follows a group of children who are haunted by an evil apparition that takes form of a clown.',
'Penguin Publishers', '11/12/2001', '11.00'
);
INSERT INTO book VALUES(
'B2346', 'A Brief History of Time', 'Stephen Hawking', '93892.12789', 'Science', 'Hardcover',

'Excellent', '144-0-84-23178-0', 'Told in language we all can understand, A Brief History of Time plunges into the exotic realms of black holes and quarks, of antimatter and “arrows of time,” of the big bang and a bigger God—where the possibilities are wondrous and unexpected. With exciting images and profound imagination, Stephen Hawking brings us closer to the ultimate secrets at the very heart of creation.'
, 'Stephen Hawking', '01/1/2002', '6.10'
);
INSERT INTO book VALUES(
'B23484', 'A Series of Unfortunate Events', 'Lemony Snicket', '12435.231970', 'Science Fiction', 'Softcopy',

'Excellent', '346-5-68-98137-0', 'Are you made fainthearted by death? Does fire unnerve you? Is a villain something that might crop up in future nightmares of yours? Are you thrilled by nefarious plots? Is cold porridge upsetting to you? Vicious threats? Hooks? Uncomfortable clothing?',
'Brett Helquist', '06/08/2007', '8.15'
);
INSERT INTO book VALUES(
'B43289', 'Catch-22: 50th Anniversary Edition', 'Joseph Heller', '05348.21834', 'History', 'Softcopy',

'Scratched', '545-1-39-34879-0', 'Set in Italy during World War II, this is the story of the incomparable, malingering bombardier, Yossarian, a hero who is furious because thousands of people he has never met are trying to kill him. But his real problem is not the enemy—it is his own army, which keeps increasing the number of missions the men must fly to complete their service.',
'Christopher Buckley', '02/15/2011', '10.52'
);
INSERT INTO book VALUES(
'B23498', 'Charlie and the Chocolate Factory', 'Roald Dahl', '34654.08432', 'Science Fiction', 'Hardcopy',

'Fifty years ago, Roald Dahl created a world that captured the imagination of millions of readers, and a boy who captured their hearts. Now featuring an introduction by award-winning author Jon Scieszka, this special edition of Charlie and the Chocolate Factory is perfect for longtime Roald Dahl fans, or for any reader discovering him for the very first time.',
'Quentin Blake', '11/29/2010', '11.55'
);
INSERT INTO book VALUES(
'B34983', 'Fahrenheit 451: A Novel', 'Ray Bradbury', '63525.23465', 'Crime', 'Hardcopy',

'Guy Montag is a fireman. In his world, where television rules and literature is on the brink of extinction, firemen start fires rather than put them out. His job is to destroy the most illegal of commodities, the printed book, along with the houses in which they are hidden.',
'Quentin Blake', '11/15/2005', '9.15'
);
INSERT INTO book VALUES(
'B2390', 'Of Human Bondage', 'W. Somerset Maugham', '35263.98789', 'Drama', 'Softcopy',

'The first and most autobiographical of Maughams masterpieces. It is the story of Philip Carey, an orphan eager for life, love and adventure. After a few months studying in Heidelberg, and a brief spell in Paris as a would-be artist, he settles in London to train as a doctor where he meets Mildred, the loud but irresistible waitress with whom he plunges into a tortured and masochistic affair.',
'Jane Smiley', '06/14/2014', '2.14'
);
INSERT INTO book VALUES(
'B23473', 'Persepolis: The Story of a Childhood', 'Marjane Satrapi', '65288.46896', 'Graphic Novel', 'Hardcopy',

'In powerful black-and-white comic strip images, Satrapi tells the story of her life in Tehran from ages six to fourteen, years that saw the overthrow of the Shah’s regime, the triumph of the Islamic Revolution, and the devastating effects of war with Iraq. The intelligent and outspoken only child of committed Marxists and the great-granddaughter of one of Iran’s last emperors, Marjane bears witness to a childhood uniquely entwined with the history of her country.',
'Jane Smiley', '02/12/2004', '9.44'
);



INSERT INTO Hold VALUES(
'H123', 'B9408', 'U3940'
);
INSERT INTO Hold VALUES(
'H3492', 'B44', 'U213'
);
INSERT INTO Hold VALUES(
'H2913', 'B234', 'U39'
);
INSERT INTO Hold VALUES(
'H39408', 'B898', 'U9034'
);
INSERT INTO Hold VALUES(
'H30483', 'B12093', 'U2456'
);
INSERT INTO Hold VALUES(
'H5326', 'B19342', 'U8952'
);
INSERT INTO Hold VALUES(
'H5823', 'B90344', 'U4098'
);
INSERT INTO Hold VALUES(
'H54678', 'B79049', 'U3290'
);
INSERT INTO Hold VALUES(
'H34840', 'B60937', 'U1129'
);
INSERT INTO Hold VALUES(
'H10428', 'B65498', 'U4328'
);
INSERT INTO Hold VALUES(
'H95823', 'B56408', 'U0984'
);
INSERT INTO Hold VALUES(
'H34689', 'B25395', 'U3258'
);
INSERT INTO Hold VALUES(
'H2398', 'B59056', 'U2398'
);


INSERT INTO Checkout VALUES(
'C95454', 'B9408', 'U3940', '09/28/2014', '', 'L42'
);
INSERT INTO Checkout VALUES(
'C49380', 'B44', 'U213', '05/14/2014', '05/23/2013', 'L12'
);
INSERT INTO Checkout VALUES(
'C8574', 'B234', 'U39', '06/24/2008', '09/30/2008', 'L42'
);
INSERT INTO Checkout VALUES(
'C324', 'B898', 'U9034', '01/21/2011', '02/03/2011', 'L28'
);
INSERT INTO Checkout VALUES(
'C63847', 'B12093', 'U2456', '12/27/2013', '07/07/2014', 'L09'
);
INSERT INTO Checkout VALUES(
'C34879', 'B68092', 'U4892', '03/22/2012', '03/24/2012', 'L39'
);
INSERT INTO Checkout VALUES(
'C43289', 'B69022', 'U3248', '02/18/2002', '07/04/2002', 'L34'
);
INSERT INTO Checkout VALUES(
'C32489', 'B09532', 'U3548', '11/24/2001', '10/03/2004', 'L28'
);
INSERT INTO Checkout VALUES(
'C43298', 'B54893', 'U1024', '01/01/2001', '08/08/2007', 'L09'
);
INSERT INTO Checkout VALUES(
'C34298', 'B46980', 'U3948', '06/02/2012', '07/24/2012', 'L58'
);
INSERT INTO Checkout VALUES(
'C43298', 'B54893', 'U1024', '05/16/2009', '01/30/2010', 'L84'
);
INSERT INTO Checkout VALUES(
'C23495', 'B89032', 'U4059', '08/12/2013', '09/24/2013', 'L26'
);