msin616 final project
1.0.0
這是一個簡單的資料庫，可用於維護借閱圖書館儲存和處理的資料。該資料庫是透過修改微軟的Pubs資料庫而設計的。 Pubs資料庫提供了一組有關圖書出版商、作者、書名和銷售情況的虛構資訊。現在它被認為已經過時了；自2008 年版本以來， SQL Server不再提供它。表和字段非常明顯。它們有Authors 、 Titles等名稱，反映了它們的內容。這些欄位也有明顯的名稱來解釋它們所包含的內容。
您可以在連結中找到有關原始 Pubs DB 的詳細資訊：Microsoft Pubs DB
下面你可以看到Pubs資料庫修改後的資料庫圖
資料庫模式的連結：DB Schema
Microsoft SQL Server 2019 - 15.0.4198.2 開發人員版（64 位元）（Ubuntu 20.04.3 LTS）Azure Data Studio版本 1.35.1Docker版本 4.4.2 (73305) Gentellela是一個開源管理面板，已用於建立前端 UI。 [TITLES]表格已更改，並添加了prequel_id列來儲存書籍的前傳。 [TITLES]表上編寫和執行遞歸查詢。 [TITLES]表格已更改，並新增了ISBN列。 [TITLES]表中。 [Audit].[Book]表來保存所創建書籍的審計歷史記錄。 [Adventureworks].[Person].[Person]和[AdventureWorks].[HumanResources].[Employee]表格產生虛擬資料。 Jobs表已刪除Roysched表已被刪除Employees表已更改Employee_type表已建立Category表已建立TitleCategory表已建立Degrees表已創建Schools表已創建Branchs表已建立Shift_logs表已建立Paychecks表已創建Borrowers表已建立Bookcopies表已建立Bookcopy_history表已建立Books_borrowed表已建立
前端ui模組連結：查看Demo
建書操作演算法流程圖
用於將書籍新增至TITLES表中的預存程序
/*
Created by Nury Amanmadov
Date created: 10.04.2022
*/
CREATE PROCEDURE [dbo].[USP_InsertBook]
@book_title AS VARCHAR ( 100 )
,@prequel_id AS VARCHAR ( 6 ) = NULL
,@book_price AS MONEY
,@book_advance AS MONEY
,@book_royalty INT
,@book_ytd_sales INT
,@book_notes VARCHAR ( 800 )
,@book_pubdate DATETIME
,@book_isbn VARCHAR ( 17 )
,@pub_id AS CHAR ( 4 ) = NULL
,@pub_name AS VARCHAR ( 40 ) = NULL
,@pub_city AS VARCHAR ( 20 ) = NULL
,@pub_state AS CHAR ( 2 ) = NULL
,@pub_country AS VARCHAR ( 30 ) = NULL
,@author_id VARCHAR ( 11 ) = NULL
,@au_lname VARCHAR ( 40 ) = NULL
,@au_fname VARCHAR ( 20 ) = NULL
,@au_phone CHAR ( 12 ) = NULL
,@au_city VARCHAR ( 20 ) = NULL
,@au_state CHAR ( 2 ) = NULL
,@au_order TINYINT = 1
,@royalty_per INT = 100
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRY
BEGIN TRANSACTION
-- #region Insert into Publisher Table
IF (@pub_id IS NULL )
-- #region Create New Publisher
BEGIN
-- Create pub_id as Max Id + 1
SET @pub_id = ( SELECT CAST( MAX (CAST(pub_id AS INT ) + 1 ) AS VARCHAR ) FROM publishers AS VARCHAR )
INSERT INTO [pubs].[dbo].[publishers]
VALUES (
ISNULL(@pub_id, 1 )
,@pub_name
,@pub_city
,@pub_state
,@pub_country
)
-- #region Insert into Pub_Info Table
DECLARE @pr_info AS VARCHAR ( 255 )
SET @pr_info = (
SELECT ' This is sample text data for '
+ pub_name + ' , publisher '
+ pub_id + ' in the pubs database. '
+ pub_name + ' is located in ' + city + ' ' + country + ' . ' AS pr_info
FROM [pubs].[dbo].[publishers] p
WHERE p . pub_id = @pub_id
)
INSERT INTO [pubs].[dbo].[pub_info]
VALUES (
ISNULL(@pub_id, 1 )
, NULL
,@pr_info
)
-- #endregion
END
-- #endregion
ELSE
BEGIN
-- Just to make sure that selected publisher exists in the database
IF NOT EXISTS ( SELECT TOP 1 1 FROM publishers p WHERE pub_id = @pub_id)
BEGIN
;THROW 50001 , ' Publisher with selected ID does not exist ' , 1
END
ELSE
BEGIN
-- Setting params. Will be inserted into Audit.Book table
SELECT @pub_name = p . pub_name
,@pub_city = p . city
,@pub_state = p.[state]
,@pub_country = p . country
FROM publishers p
WHERE p . pub_id = @pub_id
END
END
-- #endregion
-- #region Insert into Titles Table
DECLARE @random_title_id [dbo].[tid] = [dbo].[fn_GenerateRandomTitleId](RAND())
INSERT INTO titles
VALUES
(
@random_title_id
,@book_title
,ISNULL(@pub_id, 1 )
,@book_price
,@book_advance
,@book_royalty
,@book_ytd_sales
,@book_notes
,@book_pubdate
,@prequel_id
,@book_isbn
)
-- #endregion
-- #region Insert into Authors Table
DECLARE @au_zip CHAR ( 5 )
DECLARE @au_contract BIT
DECLARE @au_address VARCHAR ( 40 )
IF(@author_id IS NULL )
BEGIN
-- Generate Random AuthorID
SET @author_id = dbo . fn_GenerateRandomAuthorId (RAND())
-- Setting Random Zip in the 99xyz format
SET @au_zip = ' 99 ' + ( SELECT (CAST((FLOOR(RAND() * ( 999 - 100 + 1 ) + 100 )) AS CHAR )))
-- Setting @au_contract as random
SET @au_contract = ( SELECT (CAST((FLOOR(RAND() * ( 1 - 0 + 1 ) + 0 )) AS BIT )))
-- Setting Random Address from AdventureWorks DB Person.Address table
SET @au_address = ( SELECT TOP 1 LEFT(AddressLine1, 40 ) FROM AdventureWorks . Person .Address WHERE AddressLine1 IS NOT NULL ORDER BY NEWID())
INSERT INTO authors
VALUES
(
@author_id
,@au_lname
,@au_fname
,@au_phone
,@au_address
,@au_city
,@au_state
,@au_zip
,@au_contract
)
END
ELSE
BEGIN
IF NOT EXISTS ( SELECT TOP 1 1 FROM authors a WHERE a . au_id = @author_id)
BEGIN
;THROW 50002 , ' Author with selected ID does not exist ' , 1
END
ELSE
BEGIN
SELECT @au_lname = a . au_lname
,@au_fname = a . au_fname
,@au_phone = a . phone
,@au_city = a . city
,@au_state = a.[state]
FROM authors a
WHERE a . au_id = @author_id
END
END
-- #endregion
-- #region Insert into TitleAuthor Table
INSERT INTO titleauthor
VALUES
(
@author_id
,@random_title_id
,@au_order
,@royalty_per
)
-- #endregion
-- #region Insert into Audit Table
INSERT INTO Audit . Book
VALUES
(
ISNULL(@pub_id, 1 )
,@pub_name
,@pub_city
,@pub_state
,@pub_country
,@random_title_id
,@prequel_id
,@book_title
, NULL
,@book_price
,@book_advance
,@book_royalty
,@book_ytd_sales
,@book_notes
,@book_pubdate
,@author_id
,@au_lname
,@au_fname
,@au_phone
,@au_address
,@au_city
,@au_state
,@au_zip
,@au_contract
,@royalty_per
,@au_order
,GETDATE()
, SYSTEM_USER
)
-- #endregion
PRINT( ' Book Has Been Sucessfully Added ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END [dbo].[USP_InsertBook]預存程序有點複雜，因為它不只是創建一本書。如果沒有與該新書關聯的出版商或作者，它也可以幫助用戶創建它們。這就是為什麼[dbo].[USP_InsertBook]預存程序的@pub_id和@author_id參數是可選的。如果使用者從前端應用程式的下拉式選單元素中選擇現有發布者或作者，則所選選項的選定值（採用@pub_id或@author_id形式）將傳遞至預存程序。如果在下拉式選單（或資料庫）中找不到新書的作者或出版商，應用程式可以輕鬆建立它們。使用下拉式選單上的最後一個選項Create New Author...和Create New Publisher... ，使用者可以建立新作者或出版商。另外，一本書可能有前傳，也可能沒有。考慮到所有這些，創建一本書有 4 種可能的場景。
注意：下拉式選單項目填入了資料庫中現有的作者和出版商。
這些測試用例是：
+ Create Book for Existing Author and Existing Publisher
+ Create Book for Existing Author and Non-Existent Publisher
+ Create Book for Non-Existent Author and Existing Publisher
+ Create Book for Non-Existent Author and Non-Existent Publisher
測試案例 1：為現有作者和現有出版商創建書籍
-- Publisher Allen & Unwin with pub_id = '9910' and Author J.R.R Tolkien with author_id = '254-26-6712' already exists on the db
EXEC pubs . dbo .USP_InsertBook @author_id = ' 254-26-6712 '
,@pub_id = ' 9910 '
,@au_order = 1
,@royalty_per = 100
,@book_title = ' The Silmarillion '
,@book_type = ' Mythopoeia Fantasy '
,@book_price = 45
,@book_advance = 2500000
,@book_royalty = 40
,@book_ytd_sales = 50000000
,@book_pubdate = ' 1977-09-15 '
,@book_notes = ' The Silmarillion is a collection of mythopoeic stories by the English writer J. R. R. Tolkien. '測試案例 4：為不存在的作者和不存在的出版商創建書籍
EXEC pubs . dbo .USP_InsertBook @pub_name = ' Crown Publishing Group '
,@pub_city = ' New York City '
,@pub_state = ' NY '
,@pub_country = ' US '
,@au_lname = ' Acemoglu '
,@au_fname = ' Daron '
,@au_phone = ' 999 000-0000 '
,@au_city = ' Newton '
,@au_state = ' MA '
,@au_order = 1
,@royalty_per = 50
,@book_title = ' Why Nations Fail '
,@book_type = ' Comparative Politics, Economics '
,@book_price = 70
,@book_advance = 100000
,@book_royalty = 30
,@book_ytd_sales = 500000
,@book_pubdate = ' 2012-03-20 '
,@book_notes = ' Why Nations Fail first published in 2012, is a book by economists Daron Acemoglu and James Robinson... '測試案例 2：為現有作者和不存在的出版商創建書籍
-- A book by existing author Daron Acemoglu with author_id = '408-40-8965' but with different publisher
EXEC pubs . dbo .USP_InsertBook @pub_name = ' Cambridge University Press '
,@pub_city = ' Cambridge '
,@pub_country = ' UK '
,@author_id = ' 408-40-8965 '
,@au_order = 1
,@royalty_per = 50
,@book_title = ' Economic Origins of Dictatorship and Democracy '
,@book_type = ' Economics, Macroeconomics '
,@book_price = 30
,@book_advance = 50000
,@book_royalty = 15
,@book_ytd_sales = 500000
,@book_pubdate = ' 2012-09-01 '
,@book_notes = ' Book develops a framework for analyzing the creation and consolidation of democracy... '
測試案例 3：為不存在的作者和現有的出版商創建書籍
-- Different book by existing 'Cambridge University Press' with pub_id = '9912'
EXEC pubs . dbo .USP_InsertBook @pub_id = ' 9912 '
,@au_lname = ' Von Zur Gathen '
,@au_fname = ' Joachim '
,@au_phone = ' 000 000-0000 '
,@au_city = ' Bonn '
,@au_order = 1
,@royalty_per = 50
,@book_title = ' Modern Computer Algebra '
,@book_type = ' Computer Science '
,@book_price = 100
,@book_advance = 50000
,@book_royalty = 10
,@book_ytd_sales = 10000
,@book_pubdate = ' 2013-01-01 '
,@book_notes = ' Computer algebra systems are now ubiquitous in all areas of science and engineering... '
前端ui模組連結：查看Demo
用於將作者新增至Authors表中的預存程序
/*
Created by Nury Amanmadov
Date created: 17.04.2022
*/
CREATE PROCEDURE [dbo].[USP_CreateAuthor]
@au_lname VARCHAR ( 40 )
,@au_fname VARCHAR ( 20 )
,@au_phone CHAR ( 12 ) = ' 000 000-0000 '
,@au_address VARCHAR ( 250 ) = NULL
,@au_city VARCHAR ( 20 ) = NULL
,@au_state CHAR ( 2 ) = NULL
,@au_zip CHAR ( 5 ) = NULL
,@au_contract BIT = 0
AS
BEGIN
BEGIN TRY
-- Generate Random AuthorID
DECLARE @au_id dbo . tid
SET @au_id = dbo . fn_GenerateRandomAuthorId (RAND())
INSERT INTO authors
VALUES
(
@au_id
,@au_lname
,@au_fname
,@au_phone
,@au_address
,@au_city
,@au_state
,@au_zip
,@au_contract
)
END TRY
BEGIN CATCH
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END由於建立author_id用於多個預存過程，因此我建立了一個建立author_id的函數。
CREATE FUNCTION [dbo].[fn_GenerateRandomAuthorId](
@RAND FLOAT
)
RETURNS VARCHAR ( 11 ) AS
BEGIN
DECLARE @author_id VARCHAR ( 11 )
DECLARE @isFound BIT = 0
WHILE (@isFound = 0 )
BEGIN
DECLARE @a1 AS CHAR ( 3 ) = ( SELECT (CAST((FLOOR(@RAND * ( 999 - 100 + 1 ) + 100 )) AS CHAR )))
DECLARE @a2 AS CHAR ( 2 ) = ( SELECT (CAST((FLOOR(@RAND * ( 99 - 10 + 1 ) + 10 )) AS CHAR )))
DECLARE @a3 AS CHAR ( 4 ) = ( SELECT (CAST((FLOOR(@RAND * ( 9999 - 1000 + 1 ) + 1000 )) AS CHAR )))
SET @author_id = ( SELECT @a1 + ' - ' + @a2 + ' - ' + @a3)
IF EXISTS( SELECT TOP 1 1 FROM [authors] WHERE au_id = @author_id)
BEGIN
CONTINUE
END
ELSE
BEGIN
SET @isFound = 1
END
END
RETURN @author_id
END;
前端ui模組連結：查看Demo
用於新增CoAuthor預存程序。如果下拉式選單中不存在共同作者，我們可以建立一個新作者並新增它。
-- for demonstration purposes some columns (like address) are generated with dummy data
CREATE PROCEDURE USP_InsertCoAuthorForTitle
@title_id dbo . tid
,@au_id VARCHAR ( 11 ) = NULL
,@au_lname VARCHAR ( 40 ) = NULL
,@au_fname VARCHAR ( 20 ) = NULL
,@au_phone CHAR ( 12 ) = ' 000-000-0000 '
,@au_city VARCHAR ( 20 ) = NULL
,@au_state CHAR ( 2 ) = NULL
,@au_order TINYINT = 2
,@royalty_per INT = 0
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
IF NOT EXISTS( SELECT TOP 1 1 FROM titles WHERE title_id = @title_id)
BEGIN
;THROW 50001 , ' Book with provided ID does not exist ' , 1
END
IF(@au_id IS NULL )
BEGIN
DECLARE @au_zip CHAR ( 5 )
DECLARE @au_contract BIT
DECLARE @au_address VARCHAR ( 40 )
-- Generate Random AuthorID
SET @au_id = dbo . fn_GenerateRandomAuthorId (RAND())
-- Setting Random Zip in the 99xyz format
SET @au_zip = ' 99 ' + ( SELECT (CAST((FLOOR(RAND() * ( 999 - 100 + 1 ) + 100 )) AS CHAR )))
-- Setting @au_contract as random
SET @au_contract = ( SELECT (CAST((FLOOR(RAND() * ( 1 - 0 + 1 ) + 0 )) AS BIT )))
-- Setting Random Address from AdventureWorks DB Person.Address table
SET @au_address = (
SELECT TOP 1 LEFT(AddressLine1, 40 )
FROM AdventureWorks . Person .Address
WHERE AddressLine1 IS NOT NULL
ORDER BY NEWID()
)
INSERT INTO authors
VALUES
(
@au_id
,@au_lname
,@au_fname
,@au_phone
,@au_address
,@au_city
,@au_state
,@au_zip
,@au_contract
)
INSERT INTO titleauthor
VALUES
(
@au_id
,@title_id
,@au_order
,@royalty_per
)
END
ELSE
BEGIN
IF NOT EXISTS( SELECT TOP 1 1 FROM authors WHERE au_id = @au_id)
BEGIN
;THROW 50002 , ' Author with provided ID does not exist ' , 1
END
ELSE
BEGIN
INSERT INTO titleauthor
VALUES
(
@au_id
,@title_id
,@au_order
,@royalty_per
)
END
END
PRINT( ' CoAuthor for the Provided Book Has Been Sucessfully Added ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END
前端ui模組連結：查看Demo
用於將員工新增至Employee表的預存程序
/*
Created by Nury Amanmadov
Date created: 11.04.2022
*/
CREATE PROCEDURE [dbo].[USP_CreateEmployee]
(
@firstname AS VARCHAR ( 100 )
,@lastname AS VARCHAR ( 100 )
,@address AS VARCHAR ( 200 )
,@homephone AS CHAR ( 12 )
,@cellphone AS CHAR ( 12 )
,@salary_type AS CHAR ( 1 )
,@salary AS DECIMAL ( 6 , 0 )
,@birthdate DATE
,@degee_id INT
,@school_id INT
,@branch_id INT
,@ishead_librarin BIT
,@emp_type_id INT
,@degreedate DATE
)
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @id INT = ( SELECT MAX (employee_id) + 1 FROM employees)
INSERT INTO [pubs].[dbo].[employees]
VALUES (
@id
,@firstname
,@lastname
,@address
,@homephone
,@cellphone
,@salary_type
,@salary
,@birthdate
,GETDATE()
, 114
,@degee_id
,@school_id
,@branch_id
,@ishead_librarin
,@emp_type_id
,@degreedate
, 1
)
-- #endregion
PRINT( ' Employee Has Been Sucessfully Added ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END 
前端ui模組連結：查看Demo
以HTML table row的形式取得books的預存程序
/*
Created by Nury Amanmadov
Date created: 16.04.2022 ddMMyyyy
Selects TOP 20 Book as in the form of HTML table row records
These records then rendered as an HTML elements on the front-end side
*/
CREATE PROCEDURE [dbo].[USP_GetAllBooks]
AS
BEGIN
SELECT TOP 20
' <tr class="even pointer">
<td class="a-center "><input type="checkbox" class="flat" name="table_records"></td>
<td> ' + t . title_id + ' </td>
<td> ' + title + ' </td>
<td> ' + a . au_fname + ' ' + a . au_lname + ' </td>
<td> ' + p . pub_name + ' </td>
<td> ' + type + ' </td>
<td> ' + ISNULL(CAST(price AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(advance AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(royalty AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(ytd_sales AS VARCHAR ), ' NA ' ) + ' </td>
<td class="last"><a href="#">View</a></td>
</tr> ' AS TableRow
FROM titles t
JOIN publishers p ON t . pub_id = p . pub_id
JOIN titleauthor ta ON ta . title_id = t . title_id
JOIN authors a ON a . au_id = ta . au_id
ORDER BY p . pub_id DESC
END
前端ui模組連結：查看Demo
用於以HTML table row的形式取得authors預存程序
CREATE PROCEDURE [dbo].[USP_GetAllAuthors]
AS
BEGIN
SELECT TOP 20
' <tr class="even pointer">
<td class="a-center "><input type="checkbox" class="flat" name="table_records"></td>
<td> ' + au_id + ' </td>
<td> ' + au_fname + ' </td>
<td> ' + au_lname + ' </td>
<td> ' + ISNULL(CAST(phone AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(address AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(city AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(state AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(zip AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(contract AS VARCHAR ), ' NA ' ) + ' </td>
<td class="last"><a href="#">View</a></td>
</tr> ' AS TableRow
FROM authors a
ORDER BY zip DESC
END
前端ui模組連結：查看Demo
用於以HTML table row的形式取得publishers的預存程序
CREATE PROCEDURE [dbo].[USP_GetAllPublishers]
AS
BEGIN
SELECT TOP 20
' <tr class="even pointer">
<td class="a-center "><input type="checkbox" class="flat" name="table_records"></td>
<td> ' + p . pub_id + ' </td>
<td> ' + p . pub_name + ' </td>
<td> ' + p . city + ' </td>
<td> ' + ISNULL(p.[state], ' NA ' ) + ' </td>
<td> ' + p . country + ' </td>
<td> ' + CAST( COUNT (DISTINCT t . title_id ) AS VARCHAR ) + ' </td>
<td> ' + CAST(ISNULL( SUM ( t . ytd_sales ), 0 ) AS VARCHAR ) + ' </td>
<td> ' + CAST( COUNT (DISTINCT e . emp_id ) AS VARCHAR ) + ' </td>
<td class="last"><a href="#">View</a></td>
</tr> ' AS TableRow
FROM publishers p
LEFT JOIN titles t ON t . pub_id = p . pub_id
LEFT JOIN employee e ON e . pub_id = p . pub_id
GROUP BY p . pub_id
, p . pub_name
, p . city
,p.[state]
, p . country
ORDER BY SUM ( t . ytd_sales ) DESC
END
前端ui模組連結：查看Demo
在前端，我使用 JavaScript 開發了一些邏輯來模擬預存程序功能。這是一個Recursive儲存過程，用於獲取系列中特定書籍的前傳書籍或書籍。在查詢中使用了CTE 。
/*
Created by Nury Amanmadov
Date created: 17.04.2022 ddMMyyyy
Selects all the prequel book series of a given book
*/
CREATE PROCEDURE [dbo].[USP_GetAllPrequelBooksByTitleId]
@title_id dbo . tid
AS
BEGIN
IF NOT EXISTS( SELECT TOP 1 1 FROM titles WHERE title_id = @title_id)
BEGIN
;THROW 50001 , ' Book with provided ID does not exist ' , 1
END
;WITH CTEBooks
AS
(
SELECT title_id AS TitleId
,title AS Book
,prequel_id
,CAST( t1 . pubdate AS DATE ) AS [Publication Date ]
, p . pub_name AS Publisher
FROM titles t1
JOIN publishers p ON p . pub_id = t1 . pub_id
WHERE title_id = @title_id
UNION ALL
SELECT t2 . title_id AS TitleId
, t2 . title AS Book
, t2 . prequel_id
,CAST( t2 . pubdate AS DATE ) AS [Publication Date ]
, p . pub_name AS Publisher
FROM titles t2
JOIN publishers p ON p . pub_id = t2 . pub_id
JOIN CTEBooks ON t2 . title_id = CTEBooks . prequel_id
)
SELECT CTEBooks . TitleId
, CTEBooks . Book
,ISNULL( t . title , ' No Prequel ' ) AS PrequelBook
,[Publication Date ]
, a . au_fname + ' ' + a . au_lname AS Author
, CTEBooks . Publisher
FROM CTEBooks
LEFT JOIN titles t ON CTEBooks . prequel_id = t . title_id
LEFT JOIN titleauthor ta ON ta . title_id = CTEBooks . TitleId
LEFT JOIN authors a ON a . au_id = ta . au_id
END現在，如果我們使用系列中連續書籍的title_id執行此預存過程，我們將獲得該書的前傳書籍。
-- For 'Harry Potter and the Deathly Hallows' in the 'Harry Potter' Series
EXEC USP_GetAllPrequelBooksByTitleId @title_id = ' GU4539 ' 
-- For 'The Return of the King' in the 'Lord of the Rings' Series
EXEC USP_GetAllPrequelBooksByTitleId @title_id = ' ZJ4675 ' 
-- For 'A Dream of Spring' in the 'Game of the Thrones' Series
EXEC USP_GetAllPrequelBooksByTitleId @title_id = ' SA4547 ' 
/*
Created by Nury Amanmadov
Date created: 18.04.2022 ddMMyyyy
Selects all the continuing books in series
*/
CREATE PROCEDURE [dbo].[USP_GetAllContinuingBooksByTitleId]
@title_id dbo . tid
AS
BEGIN
IF NOT EXISTS( SELECT TOP 1 1 FROM titles WHERE title_id = @title_id)
BEGIN
;THROW 50001 , ' Book with provided ID does not exist ' , 1
END
;WITH CTEBooks
AS
(
SELECT title_id AS TitleId
,title AS Book
,prequel_id
,CAST( t1 . pubdate AS DATE ) AS [Publication Date ]
, p . pub_name AS Publisher
,[Order In Series] = 1
FROM titles t1
JOIN publishers p ON p . pub_id = t1 . pub_id
WHERE title_id = @title_id
UNION ALL
SELECT t2 . title_id AS TitleId
, t2 . title AS Book
, t2 . prequel_id
,CAST( t2 . pubdate AS DATE ) AS [Publication Date ]
, p . pub_name AS Publisher
,[Order In Series] + 1
FROM titles t2
JOIN publishers p ON p . pub_id = t2 . pub_id
JOIN CTEBooks ON t2 . prequel_id = CTEBooks . TitleId
)
SELECT CTEBooks . TitleId
, CTEBooks . Book
,ISNULL( t . title , ' No Prequel ' ) AS PrequelBook
,[Publication Date ]
, a . au_fname + ' ' + a . au_lname AS Author
, CTEBooks . Publisher
,[Order In Series]
FROM CTEBooks
LEFT JOIN titles t ON CTEBooks . prequel_id = t . title_id
LEFT JOIN titleauthor ta ON ta . title_id = CTEBooks . TitleId
LEFT JOIN authors a ON a . au_id = ta . au_id
END現在，如果我們使用系列中一本書的title_id執行此預存程序，我們將獲得該系列的後續書籍。
-- For 'The Lord of the Rings' Series
EXEC [dbo].[USP_GetAllContinuingBooksByTitleId] ' EX5727 ' 
-- For 'A Game of Thrones' Series
EXEC [dbo].[USP_GetAllContinuingBooksByTitleId] ' CI5668 ' 
-- For 'Harry Potter' Series
EXEC [dbo].[USP_GetAllContinuingBooksByTitleId] ' VC5136 ' 
刪除書籍的儲存過程
/*
Created by Nury Amanmadov
Date created: 16.04.2022
Rule for deleting a book
- Publisher related to that title will not be deleted
- PublisherInfo related to the Publisher of that title will not be deleted
- Author related to that title will not be deleted
- Records related to that title from Titles and TitleAuthor tables will be deleted
Because there are publishers without any title on the initial database created by Microsoft (ex: pub_id with 1622 and 1756)
SELECT DISTINCT pub_id
FROM publishers
WHERE pub_id NOT IN
(
SELECT DISTINCT pub_id FROM titles
)
*/
CREATE PROCEDURE [dbo].[USP_DeleteBook]
@title_id dbo . tid ,
@au_id dbo . id
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM titles WHERE title_id = @title_id
DELETE FROM titleauthor WHERE title_id = @title_id and au_id = @au_id
PRINT( ' Book Has Been Sucessfully Deleted ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END SELECT *
FROM [pubs].[Audit].[Book][審計].[書籍]記錄文件的連結：查看記錄
圖書館發給希望從圖書館借書的人借書卡。圖書館透過記憶card id 、 borrower name 、 address 、 phone number 、 birthdate 、 date the card was issued和balance due來保存每個借款人的清單。卡片的有效期限為發行日起十年。如果借款人未滿 18 歲，圖書館還將保留借款人父母或法定監護人的信息，例如name 、 address和phone number 。一個人在特定時間只能擁有一張有效的書卡。如果一個人在過期的借書卡上欠錢，則不能獲得新的借書卡。
/*
Rules for creating a card for borrower
- A person can have only one valid library card at a given time.
- A person can’t be issued a new library card, if he owes money on an expired card.
- a person can not be younger than 10.
*/
CREATE PROCEDURE [dbo].[USP_CreateBorrower]
@ssn VARCHAR ( 11 )
,@fname VARCHAR ( 100 )
,@lname VARCHAR ( 100 )
,@address VARCHAR ( 200 )
,@phone CHAR ( 12 )
,@birthdate DATE
,@lg_address VARCHAR ( 200 ) = NULL
,@lg_name VARCHAR ( 100 ) = NULL
,@lg_phone VARCHAR ( 12 ) = NULL
AS
BEGIN
BEGIN TRY
-- Check if borrower has an active card
IF EXISTS ( SELECT TOP 1 1 FROM borrowers WHERE ssn = @ssn AND isexpired = 0 )
BEGIN
;THROW 50001 , ' A person already has an active card. ' , 1
END
-- Check if borrower has an a balancedue on expired card
IF EXISTS ( SELECT TOP 1 1 FROM borrowers WHERE ssn = @ssn AND isexpired = 1 AND balancedue > 0 )
BEGIN
;THROW 50002 , ' A person owes library from previous card. ' , 1
END
-- Check if borrower is older than 10
DECLARE @age INT = DATEDIFF(YY,@birthdate,GETDATE())
IF(@age < 10 )
BEGIN
;THROW 50003 , ' Borrower can not be younger than 10. ' , 1
END
DECLARE @id INT = ( SELECT MAX (id) + 1 FROM borrowers)
DECLARE @cardid INT = ( SELECT MAX (card_id) + 1 FROM borrowers)
-- validation of legal guardian details should be on the front-end
INSERT INTO borrowers
VALUES
(
ISNULL(@id, 1 ) -- if first time
,ISNULL(@cardid, 1 ) -- if first time
,@ssn
,@fname
,@lname
,@address
,@phone
,@birthdate
,GETDATE()
, 0 -- initial balance
, 0 -- default value
,@lg_address
,@lg_name
,@lg_phone
)
END TRY
BEGIN CATCH
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END10 年後過期卡的儲存過程
CREATE PROCEDURE [dbo].[USP_ExpireLibraryCards]
AS
BEGIN
UPDATE borrowers SET isexpired = 1
WHERE DATEDIFF(YY,card_issuedate,GETDATE()) >= 10
END使用下面的預存程序創建了虛擬借款人資料。
CREATE PROCEDURE [dbo].[USP_InsertRandomBorrower]
AS
BEGIN
DECLARE @id INT
DECLARE @ssn VARCHAR ( 11 )
DECLARE @fname VARCHAR ( 100 )
DECLARE @lname VARCHAR ( 100 )
DECLARE @address VARCHAR ( 200 )
DECLARE @phone CHAR ( 12 )
DECLARE @birthdate DATE
DECLARE @cardissuedate DATE
DECLARE @balancedue DECIMAL ( 6 , 2 )
DECLARE @lgname VARCHAR ( 200 ) = NULL
DECLARE @lgaddress VARCHAR ( 200 ) = NULL
DECLARE @lgphone VARCHAR ( 200 ) = NULL
SET @id = ( SELECT MAX (id) + 1 FROM borrowers )
SET @fname = dbo . fn_GenerateFirstName ()
SET @lname = dbo . fn_GenerateLastName ()
SET @ssn = dbo . fn_GenerateRandomSsn (RAND())
SET @address = dbo . fn_GenerateRandomAddress ()
SET @phone = dbo . fn_GenerateRandomPhone (RAND())
SET @birthdate = dbo . fn_GenerateRandomDate ( ' 1999-01-01 ' , ' 2010-01-01 ' ,RAND())
DECLARE @age INT = DATEDIFF(YY,@birthdate,GETDATE())
SET @cardissuedate = DATEADD(year, dbo . fn_GetRandomNumber ( 5 ,@age,RAND()), @birthdate)
SET @balancedue = 0
IF(@age < 17 )
BEGIN
SET @lgname = dbo . fn_GenerateFirstName ()
-- Set randomly address of borrower same as guardians
DECLARE @isSameAddress BIT = CAST(ROUND(RAND(), 0 ) AS BIT )
IF(@isSameAddress = 0 )
BEGIN
SET @lgaddress = @address
END
ELSE
BEGIN
SET @lgaddress = dbo . fn_GenerateRandomAddress ()
END
SET @lgphone = dbo . fn_GenerateRandomPhone (RAND())
END
INSERT INTO borrowers
VALUES
(
ISNULL(@id, 1 ) -- if first time
,ISNULL(@id, 1 ) -- if first time
,@ssn
,@fname
,@lname
,@address
,@phone
,@birthdate
,@cardissuedate
,@balancedue
, 0 -- default value
,@lgaddress
,@lgname
,@lgphone
)
PRINT( ' Borrower sucessfully created. ' )
END使用者定義的函數來建立必要的虛擬數據
-- Since inside user defined functions we cant use NEWID() or RAND()
CREATE View [dbo].[view_NewID] AS SELECT NEWID() AS idCREATE FUNCTION [dbo].[fn_GenerateFirstName]()
RETURNS VARCHAR ( 100 ) AS
BEGIN
DECLARE @fname VARCHAR ( 100 )
SET @fname =
(
SELECT TOP 1 p . FirstName
FROM AdventureWorks . Person .Person p
ORDER BY ( SELECT id FROM dbo . view_NewID )
)
RETURN @fname
END;CREATE FUNCTION [dbo].[fn_GenerateLastName]()
RETURNS VARCHAR ( 100 ) AS
BEGIN
DECLARE @lname VARCHAR ( 100 )
SET @lname =
(
SELECT TOP 1 p . LastName
FROM AdventureWorks . Person .Person p
ORDER BY ( SELECT id FROM dbo . view_NewID )
)
RETURN @lname
END;CREATE FUNCTION [dbo].[fn_GenerateRandomAddress]()
RETURNS VARCHAR ( 200 ) AS
BEGIN
DECLARE @address VARCHAR ( 200 )
SET @address =
(
SELECT TOP 1 ad . AddressLine1
FROM AdventureWorks . Person .Address ad
WHERE AddressLine1 IS NOT NULL
ORDER BY ( SELECT id FROM dbo . view_NewID )
)
RETURN @address
END;CREATE FUNCTION [dbo].[fn_GenerateRandomDate]
(
@DateStart DATE
,@DateEnd DATE
,@RAND FLOAT
)
RETURNS DATE AS
BEGIN
DECLARE @randomDate DATE
SET @randomDate = DateAdd(Day, @RAND * DateDiff(Day, @DateStart, @DateEnd), @DateStart)
RETURN @randomDate
END;CREATE FUNCTION [dbo].[fn_GenerateRandomPhone]
(
@RAND FLOAT
)
RETURNS VARCHAR ( 12 ) AS
BEGIN
DECLARE @phone VARCHAR ( 12 )
DECLARE @p1 AS CHAR ( 3 ) = ( SELECT (CAST((FLOOR(@RAND * ( 999 - 100 + 1 ) + 100 )) AS CHAR )))
DECLARE @p2 AS CHAR ( 3 ) = ( SELECT (CAST((FLOOR(@RAND * ( 999 - 100 + 1 ) + 100 )) AS CHAR )))
DECLARE @p3 AS CHAR ( 4 ) = ( SELECT (CAST((FLOOR(@RAND * ( 9999 - 1000 + 1 ) + 1000 )) AS CHAR )))
SET @phone = ( SELECT @p1 + ' - ' + @p2 + ' - ' + @p3)
RETURN @phone
END;CREATE FUNCTION [dbo].[fn_GenerateRandomSsn]
(
@RAND FLOAT
)
RETURNS VARCHAR ( 11 ) AS
BEGIN
DECLARE @ssn VARCHAR ( 11 )
SET @ssn = (
CAST(CAST( 100 + ( 898 * @RAND) AS INT ) AS VARCHAR ( 3 )) +
' - ' +
CAST(CAST( 10 + ( 88 * @RAND) AS INT ) AS VARCHAR ( 2 )) +
' - ' +
CAST(CAST( 1000 + ( 8998 * @RAND) AS INT ) AS VARCHAR ( 4 ))
)
RETURN @ssn
END;CREATE FUNCTION [dbo].[fn_GenerateRandomTitleId](
@RAND FLOAT
)
RETURNS [dbo].[tid] AS
BEGIN
DECLARE @random_title_id [dbo].[tid]
DECLARE @isFound BIT = 0
WHILE (@isFound = 0 )
BEGIN
DECLARE @randomid1 UNIQUEIDENTIFIER = ( SELECT id FROM dbo . view_NewID )
DECLARE @randomid2 UNIQUEIDENTIFIER = ( SELECT id FROM dbo . view_NewID )
DECLARE @t1 AS CHAR ( 1 ) = ( SELECT SUBSTRING ( ' ABCDEFGHIJKLMNOPQRSTUVWXYZ ' ,(ABS(CHECKSUM(@randomid1)) % 26 ) + 1 , 1 ))
DECLARE @t2 AS CHAR ( 1 ) = ( SELECT SUBSTRING ( ' ABCDEFGHIJKLMNOPQRSTUVWXYZ ' ,(ABS(CHECKSUM(@randomid2)) % 26 ) + 1 , 1 ))
DECLARE @t3 AS CHAR ( 1 ) = ( SELECT (CAST((FLOOR(@RAND * ( 9 - 1 + 1 ) + 1 )) AS CHAR )))
DECLARE @t4 AS CHAR ( 1 ) = ( SELECT (CAST((FLOOR(@RAND * ( 9 - 1 + 1 ) + 1 )) AS CHAR )))
DECLARE @t5 AS CHAR ( 1 ) = ( SELECT (CAST((FLOOR(@RAND * ( 9 - 1 + 1 ) + 1 )) AS CHAR )))
DECLARE @t6 AS CHAR ( 1 ) = ( SELECT (CAST((FLOOR(@RAND * ( 9 - 1 + 1 ) + 1 )) AS CHAR )))
SET @random_title_id = ( SELECT @t1 + @t2 + @t3 + @t4 + @t5 + @t6)
IF EXISTS( SELECT TOP 1 1 FROM [titles] WHERE title_id = @random_title_id)
BEGIN
CONTINUE
END
ELSE
BEGIN
SET @isFound = 1
END
END
RETURN @random_title_id
END;每本借出的圖書，圖書館都會記錄借閱者的copy id和card number 。圖書館記錄date on which it was borrowed並記錄借閱日期兩週後的到期日。當副本返回時，該記錄將更新為return date 。當圖書副本被借出時，該副本會被標記為BORROWED 。當書本歸還時，該書本會被標記為AVAILABLE或NOT BORROWED 。借閱者不能向特定分行借書，除非該分行擁有該書的副本並且當前有庫存（例如，沒有amount owed其他人借閱）。和產生的任何逾期費用將添加到card balance中。如果借書人卡上欠款超過10美元，則不能使用該卡借書。圖書館有一份逾期費用清單。目前，青少年圖書的收費為每天0.05 美元，成人圖書的收費為每天0.10 美元。當圖書逾期歸還時，借閱者需支付歸還圖書時有效的費用。任何被歸類為reference的閱讀物品都不得借閱。
借閱操作的預存程序
/*
Rules for borrowing a book:
- Any reading item that is categorized as reference may not be borrowed.
- Copies that are in POOR condition may not be borrowed.
- When a book copy is borrowed, the copy is marked as BORROWED. BORROWED copies may not be borrowed.
- A borrower can not use a card to borrow books, if he owes more than 10 dollars on that card.
*/
CREATE PROCEDURE [dbo].[USP_BorrowBook]
@copy_id AS INT ,
@card_id AS INT
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRY
BEGIN TRANSACTION
-- #region Check if copy is of type Reference
IF EXISTS(
SELECT TOP 1 1
FROM titlecategory tc
JOIN bookcopies bc ON bc . title_id = tc . title_id
WHERE bc . copy_id = @copy_id AND tc . title_type_id = ( SELECT type_id FROM category c WHERE c.[type] = ' Reference ' )
)
BEGIN
;THROW 50001 , ' Copy of type Reference can not be borrowed ' , 1
END
-- #endregion
-- #region Check if copy is in POOR
IF EXISTS (
SELECT copy_id
FROM bookcopies
WHERE condition = ' POOR ' AND copy_id = @copy_id
)
BEGIN
;THROW 50002 , ' Copies in POOR condition can not be borrowed ' , 1
END
-- #endregion
-- #region Check if copy is BORROWED or Discarded
IF EXISTS (
SELECT copy_id
FROM bookcopies
WHERE (isactive = 0 OR isavailable = 0 ) AND copy_id = @copy_id
)
BEGIN
;THROW 50003 , ' Copies that are Discarded or already Borrowed can not be borrowed. ' , 1
END
-- #endregion
-- #region Check if borrower is available to borrow a book
IF EXISTS (
SELECT *
FROM borrowers
WHERE card_id = @card_id AND (isexpired = 1 OR balancedue >= 10 )
)
BEGIN
;THROW 50004 , ' Either borrowers card expired or borrower owes over 10 dollars. ' , 1
END
-- #endregion
UPDATE bookcopies SET isavailable = 0 WHERE copy_id = @copy_id
DECLARE @id INT = ( SELECT MAX (id) + 1 FROM books_borrowed)
INSERT INTO books_borrowed
VALUES
(
ISNULL(@id, 1 ),
@copy_id,
@card_id,
GETDATE(),
GETDATE() + 14 ,
0 ,
NULL
)
PRINT( ' Book Has Been Sucessfully Borrowed ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END返回操作的預存程序
CREATE PROCEDURE [dbo].[USP_ReturnBook]
@card_id INT ,
@copy_id INT
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRY
BEGIN TRANSACTION
-- #region Check borrowed book exists
IF NOT EXISTS( SELECT TOP 1 1 FROM books_borrowed WHERE card_id = @card_id AND copy_id = @copy_id AND isReturned = 0 )
BEGIN
;THROW 50001 , ' There is no borrowed book with the given details. ' , 1
END
-- #endregion
DECLARE @id INT = ( SELECT id FROM books_borrowed WHERE card_id = @card_id AND copy_id = @copy_id AND isReturned = 0 )
DECLARE @daysElapsed INT = ( SELECT DATEDIFF(DD,borroweddate,GETDATE()) FROM books_borrowed WHERE id = @id)
-- PRINT(CAST(@daysElapsed as varchar))
-- Set book available to borrow
UPDATE bookcopies SET isavailable = 1 WHERE copy_id = @copy_id
-- Set isReturned to true
UPDATE books_borrowed SET isReturned = 1 , returndate = GETDATE() WHERE id = @id
DECLARE @pr DECIMAL ( 6 , 2 )
IF(@daysElapsed > 14 )
BEGIN
DECLARE @typeId INT = ( SELECT tc . title_type_id FROM bookcopies bc JOIN titlecategory tc ON tc . title_id = bc . title_id WHERE copy_id = @copy_id)
-- Check if Juvenile
IF(@typeId = 4 )
BEGIN
SET @pr = . 05
END
ELSE
BEGIN
SET @pr = . 10
END
-- Update card balancedue
UPDATE borrowers SET balancedue = balancedue + (@pr * @daysElapsed) WHERE card_id = @card_id
END
PRINT( ' Book Has Been Sucessfully Returned ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END用於列出所有可藉用圖書的預存流程
CREATE PROCEDURE [dbo].[USP_GetAllAvailableBooks]
AS
BEGIN
SELECT *
FROM bookcopies bc
JOIN titles t ON t . title_id = bc . title_id
JOIN titlecategory tc ON tc . title_id = bc . title_id
JOIN category c ON c . type_id = tc . title_type_id
WHERE bc . isactive = 1 AND isavailable = 1 AND c . type <> ' Reference '
END圖書館為每本圖書分配一個Condition 。範例條件值可以是NEW 、 EXCELLENT 、 GOOD 、 WORN和POOR 。最終，狀況POOR的副本將被丟棄並用新副本替換。借閱者可以承認他遺失了一本書。如果是這樣，該副本將被標記為LOST ，並且該書的費用將添加到卡片餘額中。最終，該副本可以從分支副本的當前清單中移除並儲存在歷史文件中。
在Bookcopies表上為條件列新增了 CHECK CONSTRAINT
ALTER TABLE [dbo].[bookcopies] WITH CHECK ADD CONSTRAINT [CK__bookcopie__condi__09746778]
CHECK (([condition] = ' NEW ' OR [condition] = ' EXCELLENT ' OR [condition] = ' GOOD ' OR [condition] = ' WORN ' OR [condition] = ' POOR ' OR [condition] = ' LOST ' ))用於丟棄狀況POOR的書籍的儲存過程
CREATE PROCEDURE [dbo].[USP_DiscardBook]
@copy_id INT
AS
BEGIN
-- Check if copy_id is valid
IF NOT EXISTS( SELECT TOP 1 1 FROM bookcopies WHERE copy_id = @copy_id)
BEGIN
;THROW 50001 , ' Book copy with provided ID does not exist. ' , 1
END
-- Check if book is returned
IF EXISTS( SELECT TOP 1 1 FROM bookcopies WHERE copy_id = @copy_id AND isavailable = 0 )
BEGIN
;THROW 50002 , ' Book copy with provided ID has not been returned. ' , 1
END
-- Check if book has not been discarded
IF EXISTS( SELECT TOP 1 1 FROM bookcopies WHERE copy_id = @copy_id AND isactive = 0 )
BEGIN
;THROW 50003 , ' Book copy with provided ID has been already discarded. ' , 1
END
UPDATE bookcopies SET isavailable = 0 , isactive = 0 , condition = ' POOR ' WHERE copy_id = @copy_id
END丟棄LOST書的預存程序
CREATE PROCEDURE [dbo].[USP_DiscardLostBook]
@copy_id AS INT ,
@card_id AS INT
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRY
BEGIN TRANSACTION
-- #region Check borrowed book exists
IF NOT EXISTS( SELECT TOP 1 1 FROM books_borrowed WHERE card_id = @card_id AND copy_id = @copy_id AND isReturned = 0 )
BEGIN
;THROW 50001 , ' There is no borrowed book with the given details. ' , 1
END
-- #endregion
DECLARE @id INT = ( SELECT id FROM books_borrowed WHERE card_id = @card_id AND copy_id = @copy_id AND isReturned = 0 )
DECLARE @daysElapsed INT = ( SELECT DATEDIFF(DD,borroweddate,GETDATE()) FROM books_borrowed WHERE id = @id)
-- Set isReturned to true
UPDATE books_borrowed SET isReturned = 1 , returndate = GETDATE() WHERE id = @id
-- Set condition to LOST
UPDATE bookcopies SET condition = ' LOST ' ,isavailable = 0 , isactive = 0 WHERE copy_id = @copy_id
DECLARE @i INT = ( SELECT MAX (id) + 1 FROM bookcopy_history)
-- Insert note to book history table
INSERT INTO bookcopy_history
VALUES
(
ISNULL(@i, 1 ),
@copy_id,
' Book with id: ' + @copy_id + ' has been lost by user with cardid: ' + @card_id,
GETDATE()
)
DECLARE @pr DECIMAL ( 6 , 2 )
IF(@daysElapsed > 14 )
BEGIN
DECLARE @typeId INT = ( SELECT tc . title_type_id FROM bookcopies bc JOIN titlecategory tc ON tc . title_id = bc . title_id WHERE copy_id = @copy_id)
-- Check if Juvenile
IF(@typeId = 4 )
BEGIN
SET @pr = . 05
END
ELSE
BEGIN
SET @pr = . 10
END
-- Update card balancedue
UPDATE borrowers SET balancedue = balancedue + (@pr * @daysElapsed) WHERE card_id = @card_id
END
PRINT( ' Book Has Been Sucessfully discarded as LOST. ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END此借閱圖書館系統內有多個分支機構。每個分館存放分branch id 、 name 、 address 、 telephone number 、 fax number 、 head librarian 。分館可能僱用多名圖書館員，但只能僱用一名館長。對於每個圖書館員，儲存employee id 、 name 、 address 、 telephone number 、 salary和cell phone number 。一名圖書館員只能被分配到一個分館。分支機構有不同類型的員工。一些類型是： Librarian 、 Network Administrator 、 Computer Programmer 、 IT Manager 、 Floor Manager 、 Custodian 、 Accountant 、 Data Analyst 。圖書館員必須獲得圖書館學學位。對於每位員工，圖書館會維護name 、 address 、 phone number 、 birthdate 、 hire date和type of employee 。對於圖書館員，圖書館還維護圖書館員獲得學位的時間salaried hourly員獲得學位的學校。受薪員工的年薪分12 次支付，每月的第一天支付。文員僱員按小時計算工資。所有員工都根據其服務年限獲得vacation time 。最短假期時間為兩週。圖書館保存了一份日誌，記錄了每種文員類型的員工每週工作的時間。此日誌用於在每週結束時為文員產生paychecks 。圖書館員每年的收入在20000 到 70000之間。小時工的工資必須至少為15.00 。圖書館員在獲得圖書館學碩士學位之前不能被聘用。
確保對Employees表進行限制的觸發器
CREATE TRIGGER [dbo].[CheckEmployees]
ON [dbo].[employees]
INSTEAD OF INSERT
AS
BEGIN
DECLARE @branchId INT = ( SELECT branch_id FROM inserted)
DECLARE @isHead BIT = ( SELECT ishead_librarian FROM inserted)
DECLARE @empTypeId INT = ( SELECT employee_type_id FROM inserted)
DECLARE @salaryType CHAR ( 1 ) = ( SELECT salary_type FROM inserted)
DECLARE @salary DECIMAL ( 6 , 0 ) = ( SELECT salary FROM inserted)
DECLARE @degreeid INT = ( SELECT degree_id FROM inserted)
-- phone can be used as unique identifier
DECLARE @phone VARCHAR ( 12 ) = ( SELECT cellphone FROM inserted)
IF(@isHead = 1 )
BEGIN
-- Check if head librarian exists for the branch
IF EXISTS( SELECT TOP 1 1 FROM employees WHERE branch_id = @branchId AND ishead_librarian = 1 )
BEGIN
RAISERROR( ' There is already a head librarian on this branch ' , 10 , 1 )
ROLLBACK
END
-- Check if employee is of type librarian
IF(@empTypeId <> 1 )
BEGIN
RAISERROR( ' Only librarians can be head librarian ' , 10 , 1 )
ROLLBACK
END
END
-- Check if librarian exists
IF EXISTS( SELECT TOP 1 1 FROM employees WHERE cellphone = @phone AND isActive = 1 )
BEGIN
RAISERROR( ' Employee is already assigned to ' , 10 , 1 )
ROLLBACK
END
-- Check if librarian exists
IF(@salaryType = ' C ' AND @salary < 15 )
BEGIN
RAISERROR( ' Minimum hourly pay for clerical employees must be greater than 15 dollars. ' , 10 , 1 )
ROLLBACK
END
IF(@empTypeId = 1 AND (@salary < 20000 OR @salary > 70000 ))
BEGIN
RAISERROR( ' Salary for librarians must be between 20000 and 70000 ' , 10 , 1 )
ROLLBACK
END
IF(@empTypeId = 1 AND @degreeid <> 2 )
BEGIN
RAISERROR( ' Librarian must have earned MS degree in Library Sciences. ' , 10 , 1 )
ROLLBACK
END
PRINT( ' Employee has been sucessfully added. ' )
END
GO
ALTER TABLE [dbo].[employees] ENABLE TRIGGER [CheckEmployees]
用於更新年度員工Vacation Hours的儲存過程
CREATE PROCEDURE [dbo].[USP_UpdateEmployeeVacationHours]
AS
BEGIN
UPDATE employees
SET vacation_hours = CASE
WHEN DATEDIFF(YY,hiredate,GETDATE()) BETWEEN 0 AND 5 THEN 114
WHEN DATEDIFF(YY,hiredate,GETDATE()) BETWEEN 5 AND 10 THEN 154
ELSE 200
END
FROM employees
END列出最受歡迎的借閱書籍的書名。 （即被借量最多的次數）
SELECT t . title
, COUNT (id) AS [Borrowed Count]
FROM books_borrowed bb
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
GROUP BY t . title
HAVING COUNT (id) >= ALL
(
SELECT COUNT (id) AS [Borrowed Count]
FROM books_borrowed bb
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
GROUP BY t . title
)借書總數
SELECT t . title
, COUNT (id) AS [Borrowed Count]
FROM books_borrowed bb
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
GROUP BY t . title
ORDER BY [Borrowed Count] DESC目前哪個圖書館員的薪水第三高？
SELECT *
FROM
(
SELECT e . employee_id
,first_name + ' ' + last_name AS [Full Name]
,salary
,ROW_NUMBER() OVER( ORDER BY salary DESC ) AS RowNo
FROM employees e
WHERE isActive = 1 AND e . employee_type_id =
(
SELECT et . id
FROM employee_type et
WHERE [type] = ' Librarian '
)
) subQ
WHERE subQ . RowNo = 3對於每個員工，列出他/她的姓名、他/她目前工作的分支機構的名稱以及該分支機構有多少員工。
SELECT e . employee_id
, e . first_name + ' ' + e . last_name AS [Fullname]
, b . name
, COUNT ( e . employee_id ) OVER (PARTITION BY b . branch_id ) AS [Branch Employee Count]
FROM employees e
JOIN branchs b ON e . branch_id = b . branch_id
ORDER BY [Branch Employee Count] DESC對於每本書，請列出該書的書名和出版商以及每個分店當前庫存的該書的冊數，無論該書目前是否在外借。
SELECT t . title
, t . title_id
, p . pub_name
, bc . branch_id
, COUNT ( bc . title_id ) AS [Copy Count]
FROM titles t
JOIN publishers p ON t . pub_id = p . pub_id
JOIN bookcopies bc ON bc . title_id = t . title_id
GROUP BY t . title
, t . title_id
, p . pub_name
, bc . branch_id對於當年的每個季度，每個分行都會列出該季度已藉閱的圖書總量。第一季是一月、二月、三月。
SELECT b . name
,(
SELECT COUNT ( bb . copy_id ) AS [Total]
FROM bookcopies bc
JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
WHERE MONTH( bb . borroweddate ) IN ( 1 , 2 , 3 ) AND b . branch_id = bc . branch_id
) AS [Count Of First Quarter]
,(
SELECT COUNT ( bb . copy_id ) AS [Total]
FROM bookcopies bc
JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
WHERE MONTH( bb . borroweddate ) IN ( 4 , 5 , 6 ) AND b . branch_id = bc . branch_id
) AS [Count Of Second Quarter]
,(
SELECT COUNT ( bb . copy_id ) AS [Total]
FROM bookcopies bc
JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
WHERE MONTH( bb . borroweddate ) IN ( 7 , 8 , 9 ) AND b . branch_id = bc . branch_id
) AS [Count Of Third Quarter]
,(
SELECT COUNT ( bb . copy_id ) AS [Total]
FROM bookcopies bc
JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
WHERE MONTH( bb . borroweddate ) IN ( 10 , 11 , 12 ) AND b . branch_id = bc . branch_id
) AS [Count Of Fourth Quarter]
FROM branchs b對於每張卡，列出借閱者的姓名以及卡上目前已藉但尚未歸還的圖書名稱
SELECT b . first_name + ' ' + b . last_name AS [Borrower Fullname]
, t . title
FROM borrowers b
JOIN books_borrowed bb ON b . card_id = bb . card_id
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
WHERE bb . isReturned = 0
ORDER BY [Borrower Fullname]每張卡片列出借書人姓名，並在同一行列出該卡2020年已借閱圖書數量和2021年已借閱圖書數量。
SELECT b . first_name + ' ' + b . last_name AS [Borrower Fullname]
,(
SELECT COUNT ( bb . id )
FROM books_borrowed bb
WHERE YEAR( bb . borroweddate ) = 2020 AND b . card_id = bb . card_id
) AS [Borrowed2020]
,(
SELECT COUNT ( bb . id )
FROM books_borrowed bb
WHERE YEAR( bb . borroweddate ) = 2021 AND b . card_id = bb . card_id
) AS [Borrowed2021]
,(
SELECT COUNT ( bb . id )
FROM books_borrowed bb
WHERE YEAR( bb . borroweddate ) = 2022 AND b . card_id = bb . card_id
) AS [Borrowed2022]
FROM borrowers b 對於特定卡，列出哪些其他卡借用了與使用該卡借用的所有相同的書籍。 （除查詢）。您選擇要匹配的卡片。
SELECT AllBooks . card_id
FROM
(
SELECT DISTINCT bb . card_id
,title
FROM books_borrowed bb
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
) AS AllBooks
JOIN
(
SELECT DISTINCT bb . card_id
,title
FROM books_borrowed bb
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
WHERE bb . card_id = 2
) AS Card1Books ON Card1Books . title = AllBooks . title
GROUP BY AllBooks . card_id
HAVING COUNT ( AllBooks . title ) = (
SELECT COUNT (title)
FROM books_borrowed bb
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
WHERE bb . card_id = 2
)列出在圖書館工作時間最長的員工姓名
SELECT e . first_name + ' ' + e . last_name AS [Employee Fullname]
, e . hiredate
,DATEDIFF(YY, e . hiredate ,GETDATE()) AS [Years of Employment]
FROM employees e
WHERE e . hiredate = ( SELECT MIN (hiredate) FROM employees)對於每本書，如果當前未借出，請列出其標題和所在分支
SELECT t . title
, b . name
FROM titles t
JOIN bookcopies bc ON t . title_id = bc . title_id
LEFT JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
JOIN branchs b ON b . branch_id = bc . branch_id
WHERE bc . isactive = 1 AND isavailable = 1列出借款人的姓名以及他們擁有的卡片 ID（如果卡片 ID 尚未過期）。
SELECT b . first_name + ' ' + b . last_name AS [Borrower]
, b . card_id
FROM borrowers b
WHERE isexpired = 0對於每個作者，列出他的名字和他所寫的書名
SELECT DISTINCT a . au_fname + ' ' + a . au_lname AS [Author]
, t . title
FROM authors a
JOIN titleauthor ta On ta . au_id = a . au_id
JOIN titles t ON t . title_id = ta . title_id對於每個作者，列出他的名字和他所寫的書籍的類別名稱
SELECT DISTINCT a . au_fname + ' ' + a . au_lname AS [Author]
,c.[type]
FROM authors a
JOIN titleauthor ta On ta . au_id = a . au_id
JOIN titles t ON t . title_id = ta . title_id
JOIN titlecategory tc ON tc . title_id = t . title_id
JOIN category c ON c . type_id = tc . title_type_id對於每個員工，根據他記錄的工作時間計算他今年應該賺的錢。
SELECT subQ . employee_id
,subQ.[Hours Total] * e . salary
FROM
(
SELECT e . employee_id
, SUM (sl.[hours]) AS [Hours Total]
FROM employees e
JOIN shift_logs sl ON e . employee_id = sl . employee_id
WHERE YEAR( sl . shiftdate ) = YEAR(GETDATE()) AND e . salary_type = ' C '
GROUP BY e . employee_id
) subQ
JOIN employees e ON e . employee_id = subQ . employee_id列出從未借過的書的書名。
SELECT t . title
FROM titles t
WHERE t . title_id NOT IN
(
SELECT DISTINCT t . title_id
FROM borrowers b
JOIN books_borrowed bb ON b . card_id = bb . card_id
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
)對於每個分支，列出書籍的總數、按類別的總數、按作者的總數
SELECT b . name
,(
SELECT COUNT ( bc . copy_id )
FROM bookcopies bc
WHERE bc . branch_id = b . branch_id
) AS [Book Count]
,(
SELECT COUNT (DISTINCT tc . title_type_id )
FROM bookcopies bc
JOIN titles t ON t . title_id = bc . title_id
JOIN titlecategory tc ON tc . title_id = t . title_id
WHERE bc . branch_id = b . branch_id
) AS [Category Count]
,(
SELECT COUNT (DISTINCT ta . au_id )
FROM bookcopies bc
JOIN titles t ON t . title_id = bc . title_id
JOIN titleauthor ta On t . title_id = ta . title_id
WHERE bc . branch_id = b . branch_id
) AS [Category Count]
FROM branchs b 對於每張卡，列出持卡人姓名、圖書類別以及該卡上借閱的每本書。在同一個查詢中列出了每個類別已藉了多少本書，以及該卡總共借了多少本書。
WITH CTE
AS
(
SELECT b . first_name + ' ' + b . last_name AS [Borrower]
, t . title
,c.[type]
FROM borrowers b
JOIN books_borrowed bb ON b . card_id = bb . card_id
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
JOIN titlecategory tc ON tc . title_id = t . title_id
JOIN category c ON c . type_id = tc . title_type_id
)
SELECT Borrower
,title
,[type]
, COUNT ( c . title ) OVER (PARTITION BY Borrower,TYPE) AS [Category Count]
, COUNT ( c . title ) [Total Books Borrowed]
FROM CTE c
GROUP BY Borrower
,title
,[type]
ORDER BY [Borrower]在一行中列出每種類型員工目前僱用的員工數量。
SELECT et.[type]
, COUNT ( e . employee_id ) AS [Employee Count]
FROM employees e
JOIN employee_type et ON e . employee_type_id = et . id
WHERE e . isActive = 1
GROUP BY et.[type]
ORDER BY [Employee Count] DESC 借書人的名字是什麼，目前借書最多的人。
SELECT b . first_name + ' ' + b . last_name AS [Borrower]
FROM borrowers b
JOIN books_borrowed bb ON bb . card_id = b . card_id
GROUP BY b . first_name + ' ' + b . last_name
HAVING COUNT ( bb . copy_id ) >= ALL
(
SELECT COUNT (copy_id)
FROM books_borrowed
GROUP BY card_id
) 列出同時借閱A書和B書的借閱者姓名（可以選擇具體書名）。
SELECT DISTINCT b . first_name + ' ' + b . last_name AS [Borrower]
FROM titles t
JOIN bookcopies bc ON t . title_id = bc . title_id
JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
JOIN borrowers b ON b . card_id = bb . card_id
WHERE t . title_id = ' UX2157 '
INTERSECT
SELECT DISTINCT b . first_name + ' ' + b . last_name AS [Borrower]
FROM titles t
JOIN bookcopies bc ON t . title_id = bc . title_id
JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
JOIN borrowers b ON b . card_id = bb . card_id
WHERE t . title_id = ' BR5671 ' 整個 Pubs DB 腳本文件的連結：查看文件
整個 Pubs DB bak 文件的連結：查看文件
用於 Pubs 資料庫視圖應用程式的簡單前端 UI 應用程式
努里·阿曼馬多夫 - @amanmadov - [email protected]
專案連結：https://github.com/amanmadov/msin616-final-project
（回到頂部）
