By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--CREATE DATABASE db_City_Library
--USE db_City_Library
CREATE TABLE Library_Branch (
BranchID INT PRIMARY KEY NOT NULL IDENTITY (1, 1),
BranchName varchar(100) NOT NULL,
BranchAddress varchar(100)
);
CREATE TABLE Book_Copies (
BookID INT NOT NULL,--FK Done
BranchID INT NOT NULL, --FK DONE
Number_Of_Copies INT NOT NULL
);
CREATE TABLE Books (
BookID INT NOT NULL PRIMARY KEY IDENTITY (10000, 1),
Title VARCHAR(100) NOT NULL,
PublisherName VARCHAR(100) --FK Done
);
CREATE TABLE Book_Authors (
BookID INT NOT NULL CONSTRAINT fk_book_id REFERENCES Books(BookID) ON DELETE CASCADE ON UPDATE CASCADE,
AuthorName VARCHAR(100) NOT NULL
);
CREATE TABLE Publisher (
PublisherName VARCHAR(100) NOT NULL PRIMARY KEY,
Address VARCHAR(100) NOT NULL,
Phone VARCHAR(15)
);
CREATE TABLE Borrower (
CardNo INT NOT NULL PRIMARY KEY IDENTITY (100, 1),
Name VARCHAR(100) NOT NULL,
Address VARCHAR(100) NOT NULL,
Phone VARCHAR (15) NOT NULL
);
CREATE TABLE Book_Loans (
BookID INT NOT NULL CONSTRAINT fk_bookid REFERENCES Books(BookID) ON DELETE CASCADE ON UPDATE CASCADE,
BranchID INT NOT NULL CONSTRAINT fk_branch_id REFERENCES Library_Branch(BranchID) ON DELETE CASCADE ON UPDATE CASCADE,
CardNo INT NOt NULL CONSTRAINT fk_card_no REFERENCES Borrower(CardNo) ON DELETE CASCADE ON UPDATE CASCADE,
DateOut DATE NOT NULL,
DateIn DATE NOT NULL
);
ALTER TABLE Book_Copies
ADD CONSTRAINT fk_branch_id_book_copies
FOREIGN KEY (BranchID)
REFERENCES Library_Branch(BranchID)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE Book_Copies
ADD CONSTRAINT fk_book_ID_book_copies
FOREIGN KEY (BookID) REFERENCES Books(BookID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Books
ADD CONSTRAINT fk_publisher_name_books
FOREIGN KEY (PublisherName)
REFERENCES Publisher(PublisherName)
ON DELETE CASCADE
ON UPDATE CASCADE;
INSERT INTO Library_Branch
(BranchName, BranchAddress)
VALUES
('Sharpstown', '121 Sharpstown Blvd'),
('Randolph', '17 Tamari Court'),
('Waterbury', '46 Henry Hough Rd'),
('Stowe', '192 Thomas Lane'),
('Burlington', '142 South Winooski Ave'),
('South Burlington', '190 South Willard Ave');
6 rows affected
SELECT * FROM Library_Branch
| BranchID | BranchName | BranchAddress |
|---|---|---|
| 1 | Sharpstown | 121 Sharpstown Blvd |
| 2 | Randolph | 17 Tamari Court |
| 3 | Waterbury | 46 Henry Hough Rd |
| 4 | Stowe | 192 Thomas Lane |
| 5 | Burlington | 142 South Winooski Ave |
| 6 | South Burlington | 190 South Willard Ave |
INSERT INTO Borrower
(Name, Address, Phone)
VALUES
('John Doe', '12 Thatplace place', '973-598-5837'),
('Justin Doughnut', '91 Thisplace place', '973-598-5832'),
('Jennifer Dee', '64 Programmer ave', '973-598-5981'),
('Katy Perry', '12 Thisandthatplace rd', '973-598-5837'),
('Louie Labrador', '75 State Route 109', '971-528-5997'),
('Susana Lander', '36 whoknows place', '575-555-9090'),
('Chee Mann', '90 Whoknows plaza', '802-253-9090'),
('Johnny Dee', '12 1st ave', '878-889-1010')
;
8 rows affected
INSERT INTO Publisher
(PublisherName, Address, Phone)
VALUES
('Pottermore Publishing', 'London, UK', '1-800-888-9087'),
('Bantam Spectra', 'New York, NY', '212-518-9090'),
('Scribner', 'New York, NY', '212-345-9090'),
('Allen & Unwin', 'Crows Nest, AU', '1-800-909-9813'),
('Penguin Random House', 'New York, NY', '1-800-733-3000'),
('Hatchette Livre', 'Paris France', '1-800-759-0190'),
('HarperCollins', 'New York, NY', '1-800-242-7737'),
('MacMillan Publishers', 'New York, NY', '1-800-901-9876'),
('Simon & Schuster', 'New York, NY', '1-866-506-1949'),
('McGraw-Hill Education', 'New York, NY', '1-800-338-3987')
;
10 rows affected
SELECT * FROM Publisher;
| PublisherName | Address | Phone |
|---|---|---|
| Allen & Unwin | Crows Nest, AU | 1-800-909-9813 |
| Bantam Spectra | New York, NY | 212-518-9090 |
| HarperCollins | New York, NY | 1-800-242-7737 |
| Hatchette Livre | Paris France | 1-800-759-0190 |
| MacMillan Publishers | New York, NY | 1-800-901-9876 |
| McGraw-Hill Education | New York, NY | 1-800-338-3987 |
| Penguin Random House | New York, NY | 1-800-733-3000 |
| Pottermore Publishing | London, UK | 1-800-888-9087 |
| Scribner | New York, NY | 212-345-9090 |
| Simon & Schuster | New York, NY | 1-866-506-1949 |
INSERT INTO Books
(Title, PublisherName)
VALUES
('Harry Potter and the Halfblood Prince', 'Pottermore Publishing'),
('Harry Potter and the Prisoner of Azkaban', 'Pottermore Publishing'),
('Fire & Blood', 'Bantam Spectra'),
('A song of Ice and Fire', 'Bantam Spectra'),
('It', 'Scribner'),
('The Outsider', 'Scribner'),
('Different Seasons', 'Scribner'),
('The Hobbit', 'Allen & Unwin'),
('The Lord of the Rings: The Fellowship of the Ring', 'Allen & Unwin'),
('The Lord of the Rings: The Twin Towers', 'Allen & Unwin'),
('The Lord of the Rings: The Return of the King', 'Allen & Unwin'),
('The Guardians', 'Penguin Random House'),
('Talking to Strangers', 'Hachette Livre'),
('On the Come Up', 'HarperCollins'),
('Me', 'MacMillan Publishers'),
('The Institute', 'Simon & Schuster'),
('SAT/ACT Prep Book', 'McGraw-Hill Education'),
('Sleeping Beauties', 'Scribner'),
('Harry Potter and the Sorcerers Stone', 'Pottermore Publishing'),
('Howerd Stern Comes Again', 'Simon & Schuster');
Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_publisher_name_books". The conflict occurred in database "fiddle_03bdfb87cef14353aa2fb5a12e2b0bfc", table "dbo.Publisher", column 'PublisherName'.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.
SELECT* FROM Books
| BookID | Title | PublisherName |
|---|
INSERT INTO Books
(Title, PublisherName)
VALUES
('The Lost Tribe', 'Hatchette Livre');
1 rows affected
SELECT * FROM Book_Authors
| BookID | AuthorName |
|---|
SELECT * FROM Books
| BookID | Title | PublisherName |
|---|---|---|
| 10013 | The Lost Tribe | Hatchette Livre |
-- Here we add the nocheck constraint for the constraint... I will leave the rest for you...
-- Since you want to learn go ahead :)
--disabling the fk_book_id constraint that stops us inserting the values we want...
ALTER TABLE Book_Authors NOCHECK CONSTRAINT fk_book_id
INSERT INTO Book_Authors
(BookID, AuthorName)
VALUES
(10000, 'J.K. Rawling'),
(10001, 'J.K. Rawling'),
(10002, 'George R.R. Martin'),
(10003, 'George R.R. Martin'),
(10004, 'Stephen King'),
(10005, 'Stephen King'),
(10007, 'J. R. R. Tolkein'),
(10008, 'J. R. R. Tolkein'),
(10009, 'J. R. R. Tolkein'),
(10010, 'J. R. R. Tolkein');
10 rows affected
SELECT * FROM Book_Copies
| BookID | BranchID | Number_Of_Copies |
|---|
SELECT* FROM Books
| BookID | Title | PublisherName |
|---|---|---|
| 10013 | The Lost Tribe | Hatchette Livre |
SELECT * FROM Library_Branch
| BranchID | BranchName | BranchAddress |
|---|---|---|
| 1 | Sharpstown | 121 Sharpstown Blvd |
| 2 | Randolph | 17 Tamari Court |
| 3 | Waterbury | 46 Henry Hough Rd |
| 4 | Stowe | 192 Thomas Lane |
| 5 | Burlington | 142 South Winooski Ave |
| 6 | South Burlington | 190 South Willard Ave |
INSERT INTO Book_Copies
(BookID, BranchID, Number_Of_Copies)
VALUES
(10002, 1, 4),
(10004, 2, 2),
(10001, 1, 6),
(10005, 3, 3),
(10001, 2, 3),
(10007, 2, 2),
(10007, 3, 2),
(10007, 4, 2),
(10007, 5, 2),
(10008, 3, 2),
(10009, 4, 2),
(10010, 5, 2),
(10010, 6, 2),
(10007, 1, 2);
Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_book_ID_book_copies". The conflict occurred in database "fiddle_03bdfb87cef14353aa2fb5a12e2b0bfc", table "dbo.Books", column 'BookID'.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.
SELECT * FROM Books
| BookID | Title | PublisherName |
|---|---|---|
| 10013 | The Lost Tribe | Hatchette Livre |
SELECT * FROM Library_Branch
| BranchID | BranchName | BranchAddress |
|---|---|---|
| 1 | Sharpstown | 121 Sharpstown Blvd |
| 2 | Randolph | 17 Tamari Court |
| 3 | Waterbury | 46 Henry Hough Rd |
| 4 | Stowe | 192 Thomas Lane |
| 5 | Burlington | 142 South Winooski Ave |
| 6 | South Burlington | 190 South Willard Ave |
SELECT * FROM Borrower
| CardNo | Name | Address | Phone |
|---|---|---|---|
| 100 | John Doe | 12 Thatplace place | 973-598-5837 |
| 101 | Justin Doughnut | 91 Thisplace place | 973-598-5832 |
| 102 | Jennifer Dee | 64 Programmer ave | 973-598-5981 |
| 103 | Katy Perry | 12 Thisandthatplace rd | 973-598-5837 |
| 104 | Louie Labrador | 75 State Route 109 | 971-528-5997 |
| 105 | Susana Lander | 36 whoknows place | 575-555-9090 |
| 106 | Chee Mann | 90 Whoknows plaza | 802-253-9090 |
| 107 | Johnny Dee | 12 1st ave | 878-889-1010 |
INSERT INTO Book_Loans
(BookID, BranchID, CardNo, DateOut, DateIn)
VALUES
(1000, 1, 101, '2020-04-21', '2020-05-21'),
(1002, 2, 100, '2020-03-12', '2020-04-12'),
(1000, 3, 102, '2020-04-01', '2020-05-01'),
(1000, 1, 101, '2020-05-02', '2020-06-02'),
(1000, 1, 104, '2020-05-20', '2020-06-20'),
(1000, 5, 107, '2020-05-20', '2020-06-20'),
(1000, 6, 105, '2020-04-17', '2020-05-17'),
(1000, 3, 102, '2020-06-01', '2020-07-01'),
(1000, 4, 102, '2020-01-01', '2020-02-01'),
(1000, 3, 107, '2020-05-22', '2020-06-22');
Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_bookid". The conflict occurred in database "fiddle_03bdfb87cef14353aa2fb5a12e2b0bfc", table "dbo.Books", column 'BookID'.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.