Library Datebase, Sql Server

Library Datebase

CREATE SCHEMA Library AUTHORIZATION [dbo];
GO

CREATE SCHEMA Book AUTHORIZATION [dbo];
GO

CREATE SCHEMA Borrower AUTHORIZATION [dbo];
GO

CREATE TABLE Library.Branch (
	BranchId INT PRIMARY KEY NOT NULL,
	Name VARCHAR(100) NOT NULL,
	Address VARCHAR(100)
);

CREATE TABLE Book.Publisher (
	Name VARCHAR(100) PRIMARY KEY NOT NULL,
	Address VARCHAR(100),
	Phone VARCHAR(100)
);

CREATE TABLE Book.Books (
	Id INT PRIMARY KEY NOT NULL,
	Title VARCHAR(100) NOT NULL,
	PublisherName VARCHAR(100),
	CONSTRAINT fk_PublisherName_Books FOREIGN KEY (PublisherName) 
		REFERENCES Book.Publisher(Name)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE Book.Author (
	BookId INT NOT NULL,
	Name VARCHAR(100) NOT NULL,
	CONSTRAINT fk_BookId_BookAuthor FOREIGN KEY (BookId)
		REFERENCES Book.Books(Id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE Book.Copies (
	BookId INT NOT NULL,
	BranchId INT NOT NULL,
	NumberOfCopies INT NOT NULL,
	CONSTRAINT fk_BookId_BookCopies FOREIGN KEY (BookId)
		REFERENCES Book.Books(Id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT fk_BranchId_BookCopies FOREIGN KEY (BranchId) 
		REFERENCES Library.Branch(BranchId)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE Borrower.Borrower(
	CardNo INT PRIMARY KEY NOT NULL,
	Name VARCHAR(25) NOT NULL,
	Address VARCHAR(50) NOT NULL,
	Phone VARCHAR(25) NOT NULL
);

CREATE TABLE Book.Loans(
	BookId INT NOT NULL,
	BranchId INT NOT NULL,
	CardNo INT NOT NULL,
	DateOut DATE NOT NULL,
	DateDue DATE NOT NULL,
	CONSTRAINT fk_BookId_BookLoans FOREIGN KEY (BookID)
		REFERENCES Book.Books(Id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT fk_BranchId_BookLoans FOREIGN KEY (BranchId)
		REFERENCES Library.Branch(BranchId)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT fk_CardNo_BookLoans FOREIGN KEY (CardNo)
		REFERENCES Borrower.Borrower(CardNo)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

INSERT INTO Borrower.Borrower
	(CardNo, Name, Address, Phone)
	VALUES
	(1001, 'Mason Brown', '50734 Here-There St.', '375-155-2642'),
	(1002, 'Emma Johnson', '55283 Here-There St.', '336-255-7369'),
	(1003, 'Abigail Williams', '74754 Here-There St.', '324-247-8663'),
	(1004, 'William Wilson', '13875 Here-There St.', '967-227-8001'),
	(1005, 'Isabella Smith', '68029 Here-There St.', '538-227-7261'),
	(1006, 'Olivia Davis', '79813 Here-There St.', '955-434-1268'),
	(1007, 'Noah Miller', '35879 Here-There St.', '802-912-7049'),
	(1008, 'John Jones', '70480 Here-There St.', '632-353-3902'),
    (1009, 'Norah Robertson', '7658 Not-Here Ave.', '987-432-7464')
;

INSERT INTO Book.Publisher
	(Name, Address, Phone)
	VALUES
	('Picador USA', '175 Fifth Avenue, New York, NY', '800-221-7945'),
	('Wizards Of The Coast', 'P.O. Box 707, Renton, WA', '425-226-6500'),
	('O''Reilly Media', '1005 Gravenstein Highway North, Sebastopol, CA', '707-827-7000'),
	('Scribner','153-157 Fifth Avenue, New York City, NY','212-632-4915'),
	('Viking', NULL, NULL),
	('Crown Publishers', '222 Rosewood Drive, Danvers, MA', '978-750-8400'),
	('Minotaur Books', '175 5th Avenue, New York, NY', NULL),
	('Penguin Random House', '1745 Broadway, 15-3, New York, NY', NULL),
	('St. Martin''s Press','175 5th Avenue, New York, NY', NULL),
	('Wheeler Publishing', '10 Water Street, Suite 310, Waterville, ME', '800-223-1244'),
	('Harper Voyager', NULL, NULL),
	('Houghton Mifflin', NULL, NULL),
	('Arthur A. Levine Books', '557 Broadway, New York, NY', NULL)
;

INSERT INTO Library.Branch
	(BranchId, Name, Address)
	VALUES
	(3, 'Sharpstown', '500 McKinney Street, Houston, TX'),
	(1, 'Central', '700 E 6th St, Newport, RI'),
	(2, 'Alexandria', '900 Alexandria Ave, Alexandria, CA'),
	(4, 'Main Library', '800 Vine Street, Cincinnati, OH')
;
GO

INSERT INTO Book.Books
	(Id, Title, PublisherName)
	VALUES
	(1, 'The Lost Tribe', 'Picador USA'),
	(2, 'Monster manual', 'Wizards Of The Coast'),
	(3, 'Player''s Handbook', 'Wizards Of The Coast'),
	(4, 'Dungeon Master''s Guide', 'Wizards Of The Coast'),
	(5, 'C# 6.0 in a Nutshell', 'O''Reilly Media'),
	(6, 'The Outsider: a novel', 'Scribner'),
	(7, 'IT', 'Viking'),
	(8, 'Ready Player One: a novel', 'Crown Publishers'),
	(9, 'The Craftsman: a novel', 'Minotaur Books'),
	(10, 'Max Einstein: The Genius Experiment', 'Penguin Random House'),
	(11, 'American saint: The Life of Elizabeth Seton', 'St. Martin''s Press'),
	(12, 'The Never List', 'Wheeler Publishing'),
	(13, 'Soda pop soldier', 'Harper Voyager'),
	(14, 'Vengence of the Iron Dwarf', 'Wizards of the Coast'),
	(15, 'Night of the Hunter', 'Wizards of the Coast'),
	(16, 'The Lord of the Rings', 'Houghton Mifflin'),
	(17, 'Harry Potter and the Sorcerer''s Stone', 'Arthur A. Levine Books'),
	(18, 'Elevation ', 'Scribner'),
	(19, 'The Witch Elm', 'Viking'),
	(20, 'Deck the Hounds', 'Minotaur Books')	
;

INSERT INTO Book.Author
	(BookId, Name)
	VALUES
	(1, 'Mark Lee'),
	(2, 'Wizards RPG Team'),
	(3, 'Wizards RPG Team'),
	(4, 'Wizards RPG Team'),
	(5, 'Joseph Albahari'),
	(6, 'Stephen King'),
	(7, 'Stephen King'),
	(8, 'Ernest Cline'),
	(9, 'Sharon Bolton'),
	(10, 'James Patterson'),
	(11, 'Joan Barthel'),
	(12, 'Koethi Zan'),
	(13, 'Nick Cole'),
	(14, 'R.A. Salvatore'),
	(15, 'R.A. Salvatore'),
	(16,  'J.R.R Tolkien'),
	(17, 'J.K. Rowling'),
	(18, 'Stephen King'),
	(19, 'Tana French'),
	(20, 'David Rosenfelt')
;

--Randomized number of copies
INSERT INTO Book.Copies
	(BookId, BranchId, NumberOfCopies)
	VALUES
	(1,1,7),
    (2,1,0),
    (3,1,16),
    (4,1,16),
    (5,1,6),
    (6,1,8),
    (7,1,7),
    (8,1,20),
    (9,1,14),
    (10,1,20),
    (11,1,11),
    (12,1,20),
    (13,1,18),
    (14,1,16),
    (15,1,13),
    (16,1,20),
    (17,1,14),
    (18,1,12),
    (19,1,5),
    (20,1,9),
    (1,2,5),
    (2,2,14),
    (3,2,8),
    (4,2,17),
    (5,2,12),
    (6,2,7),
    (7,2,15),
    (8,2,7),
    (9,2,15),
    (10,2,16),
    (11,2,12),
    (12,2,12),
    (13,2,11),
    (14,2,6),
    (15,2,12),
    (16,2,16),
    (17,2,19),
    (18,2,16),
    (19,2,16),
    (20,2,17),
    (1,3,16),
    (2,3,20),
    (3,3,7),
    (4,3,9),
    (5,3,10),
    (6,3,6),
    (7,3,7),
    (8,3,5),
    (9,3,9),
    (10,3,18),
    (11,3,12),
    (12,3,10),
    (13,3,12),
    (14,3,13),
    (15,3,11),
    (16,3,8),
    (17,3,18),
    (18,3,20),
    (19,3,19),
    (20,3,16),
    (1,4,0),
    (2,4,0),
    (3,4,17),
    (4,4,5),
    (5,4,15),
    (6,4,0),
    (7,4,7),
    (8,4,18),
    (9,4,20),
    (10,4,9),
    (11,4,19),
    (12,4,15),
    (13,4,16),
    (14,4,6),
    (15,4,5),
    (16,4,19),
    (17,4,14),
    (18,4,7),
    (19,4,7),
    (20,4,16)
;
GO

-- Randomized table of loans
INSERT INTO Book.Loans
	(BookId, BranchId, CardNo, DateOut, DateDue)
	VALUES
	(2, 1, 1003, '10/2/2018', '10/17/2018'),
    (6, 3, 1003, '10/5/2018', '10/20/2018'),
    (13, 4, 1003, '10/2/2018', '10/17/2018'),
    (20, 4, 1003, '10/4/2018', '10/19/2018'),
    (16, 2, 1003, '10/4/2018', '10/19/2018'),
    (2, 3, 1006, '10/4/2018', '10/19/2018'),
    (13, 2, 1006, '10/5/2018', '10/20/2018'),
    (12, 2, 1006, '10/3/2018', '10/18/2018'),
    (4, 3, 1006, '9/25/2018', '10/5/2018'),
    (19, 4, 1006, '10/3/2018', '10/18/2018'),
    (4, 1, 1004, '10/2/2018', '10/17/2018'),
    (13, 2, 1008, '10/3/2018', '10/18/2018'),
    (14, 2, 1008, '10/2/2018', '10/17/2018'),
    (1, 3, 1005, '10/5/2018', '10/20/2018'),
    (1, 3, 1003, '10/3/2018', '10/18/2018'),
    (4, 2, 1007, '10/4/2018', '10/19/2018'),
    (7, 1, 1008, '10/1/2018', '10/16/2018'),
    (14, 4, 1006, '10/1/2018', '10/16/2018'),
    (6, 3, 1004, '10/6/2018', '10/21/2018'),
    (7, 3, 1008, '10/2/2018', '10/17/2018'),
    (11, 4, 1001, '10/1/2018', '10/16/2018'),
    (2, 1, 1002, '10/1/2018', '10/16/2018'),
    (9, 3, 1005, '10/2/2018', '10/17/2018'),
    (10, 3, 1004, '10/6/2018', '10/21/2018'),
    (2, 3, 1003, '10/6/2018', '10/21/2018'),
    (13, 4, 1003, '10/4/2018', '10/19/2018'),
    (14, 2, 1006, '10/1/2018', '10/16/2018'),
    (11, 4, 1004, '10/4/2018', '10/19/2018'),
    (14, 4, 1007, '10/2/2018', '10/17/2018'),
    (4, 1, 1008, '10/1/2018', '10/16/2018'),
    (16, 4, 1005, '10/1/2018', '10/16/2018'),
    (5, 3, 1002, '10/3/2018', '10/18/2018'),
    (20, 2, 1002, '10/4/2018', '10/19/2018'),
    (13, 4, 1001, '10/2/2018', '10/17/2018'),
    (11, 4, 1004, '10/5/2018', '10/20/2018'),
    (13, 2, 1003, '10/2/2018', '10/17/2018'),
    (16, 2, 1004, '10/5/2018', '10/20/2018'),
    (6, 1, 1007, '10/1/2018', '10/16/2018'),
    (20, 2, 1002, '10/2/2018', '10/17/2018'),
    (20, 2, 1005, '10/5/2018', '10/20/2018'),
    (5, 1, 1007, '9/15/2018', '10/5/2018'),
    (4, 1, 1007, '10/3/2018', '10/18/2018'),
    (18, 2, 1003, '10/4/2018', '10/19/2018'),
    (15, 2, 1005, '10/3/2018', '10/18/2018'),
    (13, 4, 1005, '10/1/2018', '10/5/2018'),
    (9, 3, 1005, '9/15/2018', '10/5/2018'),
    (9, 3, 1005, '10/5/2018', '10/20/2018'),
    (11, 2, 1005, '10/4/2018', '10/19/2018'),
    (13, 4, 1007, '10/4/2018', '10/19/2018'),
    (18, 4, 1002, '10/3/2018', '10/18/2018')
;


run \| edit \| history \| help	0